[20251121]生产系统sql语句优化1例2.txt

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

[20251121]生产系统sql语句优化1例2.txt 1.环境： xxx> @ 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.问题语句： xxx> @ sql_id 5vkt1vhz7uvp7 -- SQL_ID = 5vkt1vhz7uvp7 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; --//执行计划如下： 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 | | | | 242K(100)| | | | | | | | 1 | SORT ORDER BY | | 26 | 1456 | 242K (1)| 00:48:36 | | | 4096 | 4096 | 4096 (0)| |* 2 | FILTER | | | | | | | | | | | |* 3 | FILTER | | | | | | | | | | | |* 4 | HASH JOIN | | 26 | 1456 | 242K (1)| 00:48:36 | | | 2408K| 2408K| 1266K (0)| | 5 | REMOTE | LIS_TEST | 2 | 50 | 4 (0)| 00:00:01 | HLXJY | R->S | | | | |* 6 | TABLE ACCESS STORAGE FULL| L_LIS_SQD | 16M| 496M| 242K (1)| 00:48:35 | | | 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.全表扫描L_LIS_SQD。 --//LIS_TEST的扫描时间范围5分钟，数据量不大，应该走nested loop，问题在于连接条件。 --//注意看下划线id=6走全表扫描，仔细看连接条件id=4，"B"."BARCODE"=SYS_OP_C2C("A"."TMBH"). --//因为dblink=hlxjy的数据库，使用字段类型是nvarchar2.导致存在隐式转换。 SYS@yyyyorcl> @ descv lis.lis_test " barcode" Name Null? Type ------------------------------- -------- ---------------------------- 55 BARCODE NOT NULL NVARCHAR2(14) xxx> @ sqlhh 5vkt1vhz7uvp7 1 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 ----------------------- ---------- ------------- --------------- ---------- --------------- --------------- ------------- ------------- -------------- --------------- ----------- --------------- --------------- --------------- 2025-11-20 11:00:15 1 5vkt1vhz7uvp7 166270119 466 4767 4516 55.1 901244 901239 76 0.0 0 2 0 2025-11-20 12:00:35 1 5vkt1vhz7uvp7 166270119 659 4111 3904 54.6 901008 901003 83 0.0 0 1 0 2025-11-20 13:00:15 1 5vkt1vhz7uvp7 166270119 711 3934 3767 23.1 901666 901660 88 0.0 0 1 0 2025-11-20 14:00:14 1 5vkt1vhz7uvp7 166270119 704 4012 3871 12.6 902723 902718 86 0.0 0 1 0 2025-11-20 15:00:34 1 5vkt1vhz7uvp7 166270119 613 4271 4095 20.5 901922 901916 82 0.0 0 2 0 2025-11-20 16:00:15 1 5vkt1vhz7uvp7 166270119 531 4465 4289 11.7 901665 901660 80 0.0 0 3 0 2025-11-20 17:00:13 1 5vkt1vhz7uvp7 166270119 587 4291 4123 15.1 0 0 80 0.0 0 3 0 2025-11-20 18:00:32 1 5vkt1vhz7uvp7 166270119 711 3852 3725 5.2 902155 902146 87 0.0 0 2 0 2025-11-20 19:00:13 1 5vkt1vhz7uvp7 166270119 718 3800 3680 2.8 902442 902434 88 0.0 0 1 0 2025-11-20 20:00:12 1 5vkt1vhz7uvp7 166270119 721 3796 3661 3.7 900417 900409 88 0.0 0 2 0 2025-11-20 21:00:31 1 5vkt1vhz7uvp7 166270119 712 3826 3702 3.5 901665 901660 92 0.0 0 1 0 2025-11-20 22:00:12 1 5vkt1vhz7uvp7 166270119 716 3652 3535 3.7 862967 862962 84 0.0 0 0 0 2025-11-20 23:00:11 1 5vkt1vhz7uvp7 166270119 719 3330 3227 3.9 777175 777171 76 0.0 0 1 0 2025-11-21 00:00:31 1 5vkt1vhz7uvp7 166270119 703 3788 3626 4.4 0 0 119 0.0 0 1 0 2025-11-21 01:00:12 1 5vkt1vhz7uvp7 166270119 718 3488 3371 1.7 819383 819379 79 0.0 0 0 0 2025-11-21 02:00:10 1 5vkt1vhz7uvp7 166270119 720 2831 2735 1.2 656656 656653 62 0.0 0 0 0 2025-11-21 03:00:30 1 5vkt1vhz7uvp7 166270119 714 2309 2218 0.7 543834 543831 49 0.0 0 0 0 2025-11-21 04:00:11 1 5vkt1vhz7uvp7 166270119 715 2305 2234 0.4 544782 544779 54 0.0 0 0 0 2025-11-21 05:00:09 1 5vkt1vhz7uvp7 166270119 721 3567 3461 1.5 847890 847886 85 0.0 0 0 0 2025-11-21 06:00:29 1 5vkt1vhz7uvp7 166270119 715 3772 3655 0.7 901011 901006 92 0.0 0 1 0 2025-11-21 07:00:10 1 5vkt1vhz7uvp7 166270119 709 3823 3701 0.9 899781 899776 86 0.0 0 1 0 2025-11-21 08:00:09 1 5vkt1vhz7uvp7 166270119 576 4342 4178 4.9 0 0 80 0.0 0 2 0 2025-11-21 09:00:29 1 5vkt1vhz7uvp7 166270119 477 4673 4442 35.9 900960 900954 78 0.0 0 3 0 23 rows selected. --//每次的执行都需要4秒。 --//手工执行在打开统计的情况下8秒。 466*4767 659*4111 711*3934 704*4012 613*4271 531*4465 587*4291 711*3852 718*3800 721*3796 712*3826 716*3652 719*3330 703*3788 718*3488 720*2831 714*2309 715*2305 721*3567 715*3772 709*3823 576*4342 477*4673 --//Sum = 57208926,57208926/3600/1000 = 15.89，将近需要16小时。 xxx> @ seg2 pppppp_hhh.l_lis_sqd SEG_MB OWNER SEGMENT_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK ---------- -------------------- ------------------------------ -------------------- ------------------------------ ---------- ---------- ---------- 7054 pppppp_hhh L_LIS_SQD TABLE pppppp_hhh 902912 35 1147425 --//7G, 开发真心应该感谢exadata的强大性能功能。 --//加入如下条件： and A.TMBH=SYS_OP_C2C(B.BARCODE) --//测试函数SYS_OP_C2C两边都可以使用。 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)| | | | 43 |00:00:00.43 | 1368 | 25 | | | | | 1 | SORT ORDER BY | | 1 | 1 | 56 | 15 (7)| 00:00:01 | | | 43 |00:00:00.43 | 1368 | 25 | 4096 | 4096 | 4096 (0)| |* 2 | FILTER | | 1 | | | | | | | 43 |00:00:00.43 | 1368 | 25 | | | | | 3 | NESTED LOOPS | | 1 | 1 | 56 | 12 (0)| 00:00:01 | | | 43 |00:00:00.43 | 1368 | 25 | | | | | 4 | NESTED LOOPS | | 1 | 2 | 56 | 12 (0)| 00:00:01 | | | 283 |00:00:00.43 | 1082 | 25 | | | | | 5 | REMOTE | LIS_TEST | 1 | 2 | 50 | 4 (0)| 00:00:01 | HLXJY | R->S | 359 |00:00:00.01 | 0 | 0 | | | | |* 6 | INDEX RANGE SCAN | I_L_LIS_SQD_TMBH | 359 | 1 | | 3 (0)| 00:00:01 | | | 283 |00:00:00.43 | 1082 | 25 | 1025K| 1025K| | | 7 | REMOTE | ZHFW_SEND_MSG_LOG | 283 | 1 | 47 | 2 (0)| 00:00:01 | RZDB | R->S | 0 |00:00:00.40 | 0 | 0 | | | | |* 8 | TABLE ACCESS BY INDEX ROWID| L_LIS_SQD | 283 | 1 | 31 | 4 (0)| 00:00:01 | | | 43 |00:00:00.01 | 286 | 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' ) --//下划线的过滤条件有点奇怪，自己没看懂。也许是值SYS_OP_C2C("A"."TMBH")非空。 --//一个比较安全的情况是两边都写： and A.TMBH=SYS_OP_C2C(B.BARCODE) and SYS_OP_C2C(A.TMBH)=B.BARCODE --//这样保证没有问题，该函数是双向转换的。例子如下： xxx> select dump(SYS_OP_C2C('SALES'),16) from dual ; DUMP(SYS_OP_C2C('SALES'),16) --------------------------------------- Typ=96 Len=10: 0,53,0,41,0,4c,0,45,0,53 xxx> select dump(SYS_OP_C2C(U'SALES'),16) from dual ; DUMP(SYS_OP_C2C(U'SALES'),16 ---------------------------- Typ=96 Len=5: 53,41,4c,45,53 --//类型都是96. --//建议开发扫描v$sqlarea看看含有@hlxjy 字符串的sql语句，可能都需要按照以上情况修改。