[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语句，可能都需要按照以上情况修改。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-3104562/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [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
