[20241125]sql语句优化1例.txt

来源：这里教程网时间：2026-03-03 20:55:44 作者：

[20241125]sql语句优化1例.txt --//很久没有做sql语句,今天看看一条sql语句优化. 1.环境: XXXX> @ ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 2.问题语句: XXXX> @ sqlhh 8m896sztssj3n 0.3 time unit : millisecond 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 ------------------- ---------- ------------- --------------- ---------- --------------- --------------- ------------- ------------- -------------- --------------- ----------- --------------- --------------- --------------- 2024-11-25 03:00:04 1 8m896sztssj3n 166270119 855 1609 1559 0.0 380593 380583 39 33190.6 0 0 0 2024-11-25 04:00:22 1 8m896sztssj3n 166270119 859 1564 1516 0.0 361985 361974 36 30738.0 0 0 0 2024-11-25 05:00:09 1 8m896sztssj3n 166270119 724 2330 2259 0.0 551557 551541 55 0.0 0 0 0 2024-11-25 06:00:01 1 8m896sztssj3n 166270119 599 3394 3289 0.0 803475 803452 84 0.0 0 0 0 2024-11-25 07:00:20 1 8m896sztssj3n 166270119 622 3117 3017 0.0 733720 733699 74 0.0 0 1 0 2024-11-25 08:00:06 1 8m896sztssj3n 166270119 412 3651 3506 0.2 0 0 79 0.0 0 4 0 2024-11-25 09:00:25 1 8m896sztssj3n 166270119 272 3907 3734 1.1 806430 806406 74 0.0 0 2 0 7 rows selected. --//看多了基本知道这个是一个死循环程序,不断从里面取信息.执行1次基本在3秒以上,基本没有返回行. --//在1个小时最多执行859次(临晨4-5点). 3600/859 = 4.19091967403958090803, XXXX> @ sql_id 8m896sztssj3n -- SQL_ID = 8m896sztssj3n come from shared pool SELECT a.patientid mzhm, a.patientname brxm, b.audit_time, a.tmbh sbxh FROM l_lis_sqd a, lis_test@hlxjy b where a.tmbh = b.barcode and b.audit_time between SYSDATE - INTERVAL '15' MINUTE and SYSDATE - INTERVAL '10' MINUTE and a.stayhospitalmode = 1 and not exists (SELECT 1 FROM zhfw_send_msg_log@rzdb t where t.sbxh = a.tmbh and t.msgtype = 13) order by b.audit_time ; --//基本可以看出连接过程,先查询 lis_test@hlxjy b表,b.audit_time between SYSDATE - INTERVAL '15' MINUTE and SYSDATE - INTERVAL '10' MINUTE, --//然后连接l_lis_sqd a. a.tmbh = b.barcode. XXXX> @ ind2 %.i_l_lis_sqd%tmbh% Display indexes where table or index name matches %.i_l_lis_sqd%tmbh%... TABLE_OWNER TABLE_NAME INDEX_NAME POS# COLUMN_NAME DSC -------------------- ------------------------------ ------------------------------ ---- ------------------------------ ---- PPPPPP_HHH L_LIS_SQD I_L_LIS_SQD_TMBH 1 TMBH INDEX_OWNER TABLE_NAME INDEX_NAME IDXTYPE UNIQ STATUS PART TEMP H LFBLKS NDK NUM_ROWS CLUF LAST_ANALYZED DEGREE VISIBILIT -------------------- ------------------------------ ------------------------------ ---------- ---- -------- ---- ---- -- ---------- ------------- ---------- ---------- ------------------- ------ --------- PPPPPP_HHH L_LIS_SQD I_L_LIS_SQD_TMBH NORMAL NO VALID NO N 4 126385 26716659 26716659 22979843 2024-11-01 00:04:54 1 VISIBLE --//字段TMBH索引存在. --//执行计划如下: Plan hash value: 166270119 ----------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Inst |IN-OUT| OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 216K(100)| | | | | | | | 1 | SORT ORDER BY | | 23 | 1265 | 216K (1)| 00:43:16 | | | 2048 | 2048 | 2048 (0)| |* 2 | FILTER | | | | | | | | | | | |* 3 | FILTER | | | | | | | | | | | |* 4 | HASH JOIN | | 23 | 1265 | 216K (1)| 00:43:15 | | | 2386K| 2386K| 1277K (0)| | 5 | REMOTE | LIS_TEST | 2 | 50 | 4 (0)| 00:00:01 | HLXJY | R->S | | | | |* 6 | TABLE ACCESS STORAGE FULL| L_LIS_SQD | 15M| 429M| 216K (1)| 00:43:15 | | | 1025K| 1025K| 3085K (0)| | 7 | REMOTE | ZHFW_SEND_MSG_LOG | 1 | 47 | 2 (0)| 00:00:01 | RZDB | R->S | | | | ----------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 5 - SEL$1 / B@SEL$1 6 - SEL$1 / A@SEL$1 7 - SEL$2 / T@SEL$2 Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter( IS NULL) 3 - filter(SYSDATE@!-INTERVAL'+00 00:10:00' DAY(2) TO SECOND(0)>=SYSDATE@!-INTERVAL'+00 00:15:00' DAY(2) TO SECOND(0)) 4 - access("B"."BARCODE"=SYS_OP_C2C("A"."TMBH")) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 6 - storage("A"."STAYHOSPITALMODE"=1) filter("A"."STAYHOSPITALMODE"=1) Remote SQL Information (identified by operation id): ---------------------------------------------------- 5 - SELECT "AUDIT_TIME","BARCODE" FROM "LIS_TEST" "B" WHERE "AUDIT_TIME">=:1-INTERVAL'+00 00:15:00' DAY(2) TO SECOND(0) AND "AUDIT_TIME"<=:2-INTERVAL'+00 00:10:00' DAY(2) TO SECOND(0) (accessing 'HLXJY' ) 7 - SELECT "MSGTYPE","SBXH" FROM "ZHFW_SEND_MSG_LOG" "T" WHERE "SBXH"=:1 AND "MSGTYPE"=13 (accessing 'RZDB' ) --//问题出在id=6,做了全表扫描,没有利用tmbh的索引.仔细看id=4 4 - access("B"."BARCODE"=SYS_OP_C2C("A"."TMBH")) --//两种字段类型不一致.前面的BARCODE nvarchar2类型.而TMBH 为varchar2类型,nvarchar2类型优先级比varchar2高,转换发生在 --//tmbh端,导致tmbh的索引无法使用,出现隐式转换. --//修改语句加入如下连接条件. and a.tmbh=to_char( b.barcode) Plan hash value: 3404436610 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Inst |IN-OUT| A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 15 (100)| | | | 0 |00:00:00.11 | 872 | 3 | | | | | 1 | SORT ORDER BY | | 1 | 1 | 55 | 15 (7)| 00:00:01 | | | 0 |00:00:00.11 | 872 | 3 | 1024 | 1024 | | |* 2 | FILTER | | 1 | | | | | | | 0 |00:00:00.11 | 872 | 3 | | | | | 3 | NESTED LOOPS | | 1 | 1 | 55 | 12 (0)| 00:00:01 | | | 0 |00:00:00.11 | 872 | 3 | | | | | 4 | NESTED LOOPS | | 1 | 2 | 55 | 12 (0)| 00:00:01 | | | 139 |00:00:00.11 | 736 | 3 | | | | | 5 | REMOTE | LIS_TEST | 1 | 2 | 50 | 4 (0)| 00:00:01 | HLXJY | R->S | 244 |00:00:00.01 | 0 | 0 | | | | |* 6 | INDEX RANGE SCAN | I_L_LIS_SQD_TMBH | 244 | 1 | | 3 (0)| 00:00:01 | | | 139 |00:00:00.11 | 736 | 3 | 1025K| 1025K| | | 7 | REMOTE | ZHFW_SEND_MSG_LOG | 179 | 1 | 47 | 2 (0)| 00:00:01 | RZDB | R->S | 40 |00:00:00.10 | 0 | 0 | | | | |* 8 | TABLE ACCESS BY INDEX ROWID| L_LIS_SQD | 139 | 1 | 30 | 4 (0)| 00:00:01 | | | 0 |00:00:00.01 | 136 | 0 | | | | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 5 - SEL$1 / B@SEL$1 6 - SEL$1 / A@SEL$1 7 - SEL$2 / T@SEL$2 8 - SEL$1 / A@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.4') DB_VERSION('11.2.0.4') ALL_ROWS OUTLINE_LEAF(@"SEL$2") OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "B"@"SEL$1") INDEX(@"SEL$1" "A"@"SEL$1" ("L_LIS_SQD"."TMBH")) LEADING(@"SEL$1" "B"@"SEL$1" "A"@"SEL$1") USE_NL(@"SEL$1" "A"@"SEL$1") NLJ_BATCHING(@"SEL$1" "A"@"SEL$1") PUSH_SUBQ(@"SEL$2") FULL(@"SEL$2" "T"@"SEL$2") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(SYSDATE@!-INTERVAL'+00 00:10:00' DAY(2) TO SECOND(0)>=SYSDATE@!-INTERVAL'+00 00:15:00' DAY(2) TO SECOND(0)) 6 - access("A"."TMBH"=SYS_OP_C2C("B"."BARCODE")) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ filter(("B"."BARCODE"=SYS_OP_C2C("A"."TMBH") AND IS NULL)) 8 - filter("A"."STAYHOSPITALMODE"=1) Remote SQL Information (identified by operation id): ---------------------------------------------------- 5 - SELECT "AUDIT_TIME","BARCODE" FROM "LIS_TEST" "B" WHERE "AUDIT_TIME">=:1-INTERVAL'+00 00:15:00' DAY(2) TO SECOND(0) AND "AUDIT_TIME"<=:2-INTERVAL'+00 00:10:00' DAY(2) TO SECOND(0) (accessing 'HLXJY' ) 7 - SELECT "MSGTYPE","SBXH" FROM "ZHFW_SEND_MSG_LOG" "T" WHERE "SBXH"=:1 AND "MSGTYPE"=13 (accessing 'RZDB' ) 79 rows selected. --//实际上准确的加入条件是A.TMBH=SYS_OP_C2C(B.BARCODE) --//SYS_OP_C2C 函数正反向都是可以用,主要开始不知道反过来使用什么函数,所以使用to_char. --//不过这样改写后,过滤条件 filter(("B"."BARCODE"=SYS_OP_C2C("A"."TMBH") AND IS NULL)) --//怎么意思不懂.好像与后面的not exists有关。 --//当然还有1种优化就是建立函数索引. --//这种就是引入nvarchar2带来的问题,开发还是应该学习了解这方面知识,不要乱用这种数据类型. --//有一种保险的写法就是两边都使用函数： and A.TMBH=SYS_OP_C2C(B.BARCODE) and SYS_OP_C2C(A.TMBH)=B.BARCODE --//dblink=HLXJY数据库凡是字符类型全部是nvarchar2类型,已经跟开发讲了多次,使用该库的dblink时注意连接条件的写法,实际上看看 --//执行计划就知道怎么回事了. --//我的同事应该好好感谢exadata,它才是整个应用的劳模,如果普通的服务器早就崩了. XXXX> @ seg2 PPPPPP_HHH.L_LIS_SQD SEG_MB OWNER SEGMENT_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK ---------- -------------------- ------------------------------ -------------------- ------------------------------ ---------- ---------- ---------- 6286 PPPPPP_HHH L_LIS_SQD TABLE PPPPPP_HHH 804608 35 1147425 --//3秒基本扫描6G.没有exadata的特殊技术,系统早就瘫痪了.