[20220125]生产系统怪异的查询语句.txt

来源：这里教程网时间：2026-03-03 17:25:56 作者：

[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以及相关索引来欺骗系统,但是带来维护的问题.