[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的特殊技术,系统早就瘫痪了.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-3051404/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [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
