[20220125]生产系统怪异的查询语句.txt
--//生产系统有一些编程工具会执行一些怪异的查询语句,检查生产系统时发现一些奇怪的语句.
1.环境:
> @ prxx
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 19.0.0.0.0
BANNER : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0
BANNER_LEGACY : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
> @ashtop sql_id,event,module,machine sql_id='9rzcqu8p5uu97' sysdate-1 sysdate
Total Distinct Distinct
Seconds AAS %This SQL_ID EVENT MODULE MACHINE FIRST_SEEN LAST_SEEN Execs Seen Tstamps
--------- ------- ------- ------------- --------------------------- -------------------- ---------------------- ------------------- ------------------- ---------- --------
388 .0 100% | 9rzcqu8p5uu97 JDBC Thin Client localhost.localdomain 2022-01-24 09:23:42 2022-01-25 09:18:28 388 388
> @ sqlhh 9rzcqu8p5uu97
BEGIN_INTERVAL_TIME INST_ID SQL_ID PLAN_HASH_VALUE EXECUTIONS ELA_MS_PER_EXEC CPU_MS_PER_EXEC ROWS_PER_EXEC LIOS_PER_EXEC BLKRD_PER_EXEC IOW_MS_PER_EXEC AVG_IOW_MS CLW_MS_PER_EXEC APW_MS_PER_EXEC CCW_MS_PER_EXEC
------------------- ---------- ------------- --------------- ---------- --------------- --------------- ------------- ------------- -------------- --------------- ----------- --------------- --------------- ---------------
2021-11-25 22:00:32 1 9rzcqu8p5uu97 1279168831 715 29 28 6.0 1445 0 0 0.0 0 0 0
2021-11-25 23:00:04 1 9rzcqu8p5uu97 1279168831 721 29 28 6.0 1445 0 0 0.0 0 0 0
...
2022-01-25 07:00:01 2 9rzcqu8p5uu97 1279168831 724 29 28 6.0 1445 0 0 0.0 0 0 0
2022-01-25 08:00:20 2 9rzcqu8p5uu97 1279168831 724 29 29 6.0 1445 0 0 0.0 0 0 0
1563 rows selected.
--//基本每个小时执行72X次.3600/720 = 5.每5秒执行1次.
SYS@127.0.0.1:17102/dyhis> @ sql_id 9rzcqu8p5uu97
--SQL_ID = 9rzcqu8p5uu97
SELECT column_name as Field , data_type as Type FROM all_tab_cols WHERE lower(table_name) = 'view_lb_doctor_login';
--//使用谓词lower(table_name) = 'view_lb_doctor_login',不知道为什么使用小写函数,直接导致每次扫描sys.obj$.
Plan hash value: 1279168831
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 541 (100)| | 6 |00:00:00.03 | 1445 | | | |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 0 | 1 | 38 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 2 | INDEX RANGE SCAN | I_OBJ1 | 0 | 1 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 0 | 1 | 38 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 4 | INDEX RANGE SCAN | I_OBJ1 | 0 | 1 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 0 | 1 | 38 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 6 | INDEX RANGE SCAN | I_OBJ1 | 0 | 1 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
| 7 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 0 | 1 | 38 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 8 | INDEX RANGE SCAN | I_OBJ1 | 0 | 1 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
| 9 | TABLE ACCESS BY INDEX ROWID BATCHED| OBJ$ | 0 | 1 | 38 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 10 | INDEX RANGE SCAN | I_OBJ1 | 0 | 1 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 11 | FILTER | | 1 | | | | | 6 |00:00:00.03 | 1445 | | | |
|* 12 | HASH JOIN OUTER | | 1 | 27 | 5427 | 526 (1)| 00:00:01 | 6 |00:00:00.03 | 1441 | 970K| 970K| 463K (0)|
| 13 | NESTED LOOPS OUTER | | 1 | 27 | 5319 | 525 (1)| 00:00:01 | 6 |00:00:00.03 | 1440 | | | |
| 14 | NESTED LOOPS OUTER | | 1 | 27 | 5049 | 498 (1)| 00:00:01 | 6 |00:00:00.03 | 1431 | | | |
| 15 | NESTED LOOPS OUTER | | 1 | 27 | 3915 | 390 (1)| 00:00:01 | 6 |00:00:00.03 | 1431 | | | |
| 16 | NESTED LOOPS | | 1 | 27 | 3159 | 389 (1)| 00:00:01 | 6 |00:00:00.03 | 1423 | | | |
|* 17 | HASH JOIN | | 1 | 2 | 174 | 388 (1)| 00:00:01 | 2 |00:00:00.03 | 1418 | 1209K| 1209K| 858K (0)|
|* 18 | HASH JOIN | | 1 | 2 | 166 | 387 (1)| 00:00:01 | 2 |00:00:00.03 | 1417 | 1265K| 1265K| 881K (0)|
| 19 | NESTED LOOPS OUTER | | 1 | 2 | 118 | 386 (1)| 00:00:01 | 2 |00:00:00.03 | 1416 | | | |
|* 20 | TABLE ACCESS FULL | OBJ$ | 1 | 2 | 108 | 385 (1)| 00:00:01 | 2 |00:00:00.03 | 1411 | | | |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| 21 | TABLE ACCESS CLUSTER | TAB$ | 2 | 1 | 5 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 5 | | | |
|* 22 | INDEX UNIQUE SCAN | I_OBJ# | 2 | 1 | | 0 (0)| | 1 |00:00:00.01 | 4 | | | |
| 23 | INDEX FULL SCAN | I_USER2 | 1 | 143 | 3432 | 1 (0)| 00:00:01 | 143 |00:00:00.01 | 1 | | | |
| 24 | INDEX FULL SCAN | I_USER2 | 1 | 143 | 572 | 1 (0)| 00:00:01 | 143 |00:00:00.01 | 1 | | | |
| 25 | TABLE ACCESS CLUSTER | COL$ | 2 | 14 | 420 | 1 (0)| 00:00:01 | 6 |00:00:00.01 | 5 | | | |
|* 26 | INDEX UNIQUE SCAN | I_OBJ# | 2 | 1 | | 0 (0)| | 1 |00:00:00.01 | 4 | | | |
| 27 | TABLE ACCESS BY INDEX ROWID | COLTYPE$ | 6 | 1 | 28 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 8 | | | |
|* 28 | INDEX UNIQUE SCAN | I_COLTYPE2 | 6 | 1 | | 0 (0)| | 0 |00:00:00.01 | 8 | | | |
|* 29 | TABLE ACCESS BY INDEX ROWID BATCHED| OBJ$ | 6 | 1 | 42 | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 30 | INDEX RANGE SCAN | I_OBJ3 | 6 | 24 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 31 | INDEX RANGE SCAN | I_HH_OBJ#_INTCOL# | 6 | 1 | 10 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 9 | | | |
| 32 | INDEX FULL SCAN | I_USER2 | 1 | 143 | 572 | 1 (0)| 00:00:01 | 143 |00:00:00.01 | 1 | | | |
|* 33 | TABLE ACCESS CLUSTER | TAB$ | 0 | 1 | 13 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 34 | INDEX UNIQUE SCAN | I_OBJ# | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
| 35 | NESTED LOOPS SEMI | | 1 | 1 | 12 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 4 | | | |
| 36 | FIXED TABLE FULL | X$KZSRO | 1 | 2 | 6 | 0 (0)| | 2 |00:00:00.01 | 0 | | | |
|* 37 | INDEX RANGE SCAN | I_OBJAUTH2 | 2 | 1 | 9 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 4 | | | |
|* 38 | FIXED TABLE FULL | X$KZSPR | 1 | 1 | 7 | 0 (0)| | 1 |00:00:00.01 | 0 | | | |
|* 39 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 0 | 1 | 6 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 40 | INDEX RANGE SCAN | I_USER_EDITIONING | 0 | 2 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 41 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 0 | 1 | 6 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 42 | INDEX RANGE SCAN | I_USER_EDITIONING | 0 | 2 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
| 43 | NESTED LOOPS SEMI | | 0 | 1 | 29 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 44 | INDEX SKIP SCAN | I_USER2 | 0 | 1 | 20 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 45 | INDEX RANGE SCAN | I_OBJ4 | 0 | 1 | 9 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--//ID=20全表扫描OBJ$,逻辑读最大贡献来之这里.如果修改如下执行:
SELECT column_name as Field , data_type as Type FROM all_tab_cols WHERE table_name = 'VIEW_LB_DOCTOR_LOGIN';
Plan hash value: 1626446879
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 136 (100)| | 6 |00:00:00.01 | 72 | | | |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 0 | 1 | 38 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 2 | INDEX RANGE SCAN | I_OBJ1 | 0 | 1 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 0 | 1 | 38 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 4 | INDEX RANGE SCAN | I_OBJ1 | 0 | 1 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 0 | 1 | 38 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 6 | INDEX RANGE SCAN | I_OBJ1 | 0 | 1 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
| 7 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 0 | 1 | 38 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 8 | INDEX RANGE SCAN | I_OBJ1 | 0 | 1 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
| 9 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 0 | 1 | 38 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 10 | INDEX RANGE SCAN | I_OBJ1 | 0 | 1 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 11 | FILTER | | 1 | | | | | 6 |00:00:00.01 | 72 | | | |
|* 12 | HASH JOIN OUTER | | 1 | 16 | 3216 | 121 (0)| 00:00:01 | 6 |00:00:00.01 | 68 | 970K| 970K| 506K (0)|
| 13 | NESTED LOOPS OUTER | | 1 | 16 | 3152 | 120 (0)| 00:00:01 | 6 |00:00:00.01 | 67 | | | |
| 14 | NESTED LOOPS OUTER | | 1 | 16 | 2480 | 56 (0)| 00:00:01 | 6 |00:00:00.01 | 67 | | | |
| 15 | NESTED LOOPS OUTER | | 1 | 16 | 2320 | 40 (0)| 00:00:01 | 6 |00:00:00.01 | 58 | | | |
| 16 | NESTED LOOPS | | 1 | 16 | 1872 | 39 (0)| 00:00:01 | 6 |00:00:00.01 | 50 | | | |
|* 17 | HASH JOIN | | 1 | 1 | 87 | 38 (0)| 00:00:01 | 2 |00:00:00.01 | 45 | 1209K| 1209K| 617K (0)|
| 18 | NESTED LOOPS | | 1 | 1 | 83 | 37 (0)| 00:00:01 | 2 |00:00:00.01 | 44 | | | |
| 19 | NESTED LOOPS OUTER | | 1 | 1 | 59 | 36 (0)| 00:00:01 | 2 |00:00:00.01 | 42 | | | |
|* 20 | TABLE ACCESS BY INDEX ROWID BATCHED| OBJ$ | 1 | 1 | 54 | 35 (0)| 00:00:01 | 2 |00:00:00.01 | 37 | | | |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|* 21 | INDEX SKIP SCAN | I_OBJ2 | 1 | 1 | | 34 (0)| 00:00:01 | 2 |00:00:00.01 | 35 | | | |
| 22 | TABLE ACCESS CLUSTER | TAB$ | 2 | 1 | 5 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 5 | | | |
|* 23 | INDEX UNIQUE SCAN | I_OBJ# | 2 | 1 | | 0 (0)| | 1 |00:00:00.01 | 4 | | | |
|* 24 | INDEX RANGE SCAN | I_USER2 | 2 | 1 | 24 | 1 (0)| 00:00:01 | 2 |00:00:00.01 | 2 | | | |
| 25 | INDEX FULL SCAN | I_USER2 | 1 | 1 | 4 | 1 (0)| 00:00:01 | 143 |00:00:00.01 | 1 | | | |
| 26 | TABLE ACCESS CLUSTER | COL$ | 2 | 14 | 420 | 1 (0)| 00:00:01 | 6 |00:00:00.01 | 5 | | | |
|* 27 | INDEX UNIQUE SCAN | I_OBJ# | 2 | 1 | | 0 (0)| | 1 |00:00:00.01 | 4 | | | |
| 28 | TABLE ACCESS BY INDEX ROWID | COLTYPE$ | 6 | 1 | 28 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 8 | | | |
|* 29 | INDEX UNIQUE SCAN | I_COLTYPE2 | 6 | 1 | | 0 (0)| | 0 |00:00:00.01 | 8 | | | |
|* 30 | INDEX RANGE SCAN | I_HH_OBJ#_INTCOL# | 6 | 1 | 10 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 9 | | | |
|* 31 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 6 | 1 | 42 | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 32 | INDEX RANGE SCAN | I_OBJ3 | 6 | 24 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
| 33 | INDEX FULL SCAN | I_USER2 | 1 | 143 | 572 | 1 (0)| 00:00:01 | 143 |00:00:00.01 | 1 | | | |
|* 34 | TABLE ACCESS CLUSTER | TAB$ | 0 | 1 | 13 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 35 | INDEX UNIQUE SCAN | I_OBJ# | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
| 36 | NESTED LOOPS SEMI | | 1 | 1 | 12 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 4 | | | |
| 37 | FIXED TABLE FULL | X$KZSRO | 1 | 2 | 6 | 0 (0)| | 2 |00:00:00.01 | 0 | | | |
|* 38 | INDEX RANGE SCAN | I_OBJAUTH2 | 2 | 1 | 9 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 4 | | | |
|* 39 | FIXED TABLE FULL | X$KZSPR | 1 | 1 | 7 | 0 (0)| | 1 |00:00:00.01 | 0 | | | |
|* 40 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 0 | 1 | 6 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 41 | INDEX RANGE SCAN | I_USER_EDITIONING | 0 | 2 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 42 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 0 | 1 | 6 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 43 | INDEX RANGE SCAN | I_USER_EDITIONING | 0 | 2 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
| 44 | NESTED LOOPS SEMI | | 0 | 1 | 29 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 45 | INDEX SKIP SCAN | I_USER2 | 0 | 1 | 20 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 46 | INDEX RANGE SCAN | I_OBJ4 | 0 | 1 | 9 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--//虽然使用skip 索引,导致逻辑读已经大大降低,这种开发工具导致的问题出来起来非常麻烦,你不可能在sys.obj$上建立索引.
--//试想一下如果对象很多,会导致扫描obj$逻辑读很大,而且我这里仅仅列举一个对象的查询,实际的生产系统可能出现大量的类似的查
--//询语句.最终的结果导致运行"缓慢".
SYS@127.0.0.1:17102/dyhis> @ d_bufferx 9rzcqu8p5uu97 60 1,2
SQL_ID INST_ID MODULE EXECUTIONS CPU_TIME ELAPSED_TIME BUFFER_GETS ROWS_PROCESSED CPU_PER_EXEC ELAPSED_TIME_EXEC BUFFER_GETS_EXEC ROWS_PROCESSED_EXEC
------------- --------------- ------------ --------------- --------------- --------------- --------------- --------------- --------------- ----------------- ---------------- -------------------
9rzcqu8p5uu97 2 JDBC Thin Cl 12 341455 351409 17340 72 28454.583333333 29284.083333333 1445 6
ient
--//与前面预测一致.
SYS@127.0.0.1:17102/dyhis> @ d_bufferx awa8nszb02q6p 60 1,2
SQL_ID INST_ID MODULE EXECUTIONS CPU_TIME ELAPSED_TIME BUFFER_GETS ROWS_PROCESSED CPU_PER_EXEC ELAPSED_TIME_EXEC BUFFER_GETS_EXEC ROWS_PROCESSED_EXEC
------------- --------------- ------------ --------------- --------------- --------------- --------------- --------------- --------------- ----------------- ---------------- -------------------
awa8nszb02q6p 1 JDBC Thin Cl 0 0 0 0 0
ient
awa8nszb02q6p 2 JDBC Thin Cl 6 244427 248116 11460 0 40737.833333333 41352.666666667 1910 0
ient
--//这个仅仅是其中1个,平均10秒出现1次.
--//如果真的很多,只能冒很大的风险改名视图,建立一个真实的表all_tab_cols以及相关索引来欺骗系统,但是带来维护的问题.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2853888/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
