版本：Oracle 11.2.0.4.0
操作系统：linux redhat 6.4
场景：
最近前台报表组反馈，某些报表查询基本跑不出数据。经过优化和改写最终实现 最初跑不出数据 到20分钟 到24秒。
优化思路：
将视图转换成事实表，并且对SQL中调用的函数，等价改写成多表关联语句。避免产生大量的递归调用。
需要掌握：学会等价改写Sql，Sql中尽量避免调用函数，可以采用表链接方式来避免递归调用，如果不能避免，就尽量减少调用。
以下内容摘自 Oracle高性能SQL引擎剖析-SQL优化与调优机制详解
本案例主要关注了递归调用这一统计数据，一般该数据并不能直接反应当前语句性能，但是在我们对语句进行调优过程中，不能忽略该数据。它反映了在执行该语句时，Oracle还执行了多次对其他语句或内部过程的调用，并且这些调用同时会导致其他性能统计数据相应增加。因此，当我们以其他性能统计数据（例如逻辑读）作为调优参数值时，需要尽量避免递归调用导致的性能数据不确定。当无法避免时，则需要考虑递归调用对当前语句的性能影响。
递归调用有可能是由于系统的内部规则或过程所产生，也有可能是由于用户对象所产生的。
包括但不限于：
1.硬解析语句执行计划；
2.执行DDL语句；
3.触发器被激活；
4.在语句中执行了用户自定义函数。
5.在修改表数据时对外键关系做完整性检查；
6.存在位图关联索引，且修改引用表数据时，需要维护索引；
7.递归调用语句导致再次递归调用。
原始代码：
SELECT A12.SECUCODE SECUCODE,
A12.SECUMARKET SECUMARKET,
A12.SECUCODE SECUNAME,
A12.SECUMARKET SECUMARKET0,
A12.SECUNAME SECUNAME0,
A12.SECUMARKET CUSTCOL_16,
CHECK_DESC(A12.SECUMARKET, 'SECUMARKET') CUSTCOL_19,
A14.PROJ_ID PROJ_ID,
A15.PROJ_NAME PROJ_NAME,
A15.PROJ_SHORT_NAME PROJ_SHORT_NAME,
A16.ID,
A16.NAME
FROM DIM_SECUMAIN A12,
FCT_POSITION A13,
DIM_SEC_PROJECT A14,
DIM_PROJECT A15,
V_IC_PRIVILEGE A16
WHERE A12.SECUCODE = A13.SECUCODE --1233199
AND A12.SECUMARKET = A13.SECUMARKET --1233199
AND A13.SEC_PROJ_ID = A14.SEC_PROJ_ID --1233199
AND A14.PROJ_ID = A15.PROJ_ID --1206493
AND A15.PROJ_ID = A16.PRIMARYKEY
GROUP BY A12.SECUCODE ,
A12.SECUMARKET ,
A12.SECUCODE ,
A12.SECUMARKET ,
A12.SECUNAME ,
A12.SECUMARKET ,
CHECK_DESC(A12.SECUMARKET, 'SECUMARKET') ,
A14.PROJ_ID ,
A15.PROJ_NAME ,
A15.PROJ_SHORT_NAME ,
A16.ID,
A16.NAME
其中V_IC_PRIVILEGE为视图代码如下：
CREATE OR REPLACE VIEW V_IC_PRIVILEGE AS
SELECT DISTINCT a.id, a.cooperativeorgtype, w.primarykey, a.name
FROM ia.IC_PRIVILEDGE_DETAILS_VIEW@pydb1 A
JOIN (SELECT br.*
FROM (SELECT *
FROM FDM.F_ZQ_SEC_PROJECTINFOMAPPING B
WHERE B.BIZSYSNAME = 'hsgzSys'
AND B.CHANGE_CODE IN (1, 3)
UNION
SELECT *
FROM FDM.F_ZQ_SEC_PROJECTINFOMAPPING B2
WHERE B2.BIZSYSNAME = 'mcgzSys'
AND B2.CHANGE_CODE IN (1, 3)
) B
INNER JOIN (select *
from FDM.F_ZQ_SEC_PROJECTINFOMAPPING
where BIZSYSNAME = 'rtSys'
AND CHANGE_CODE IN (1, 3)) Br
ON BR.SECPROJECTID = B.SECPROJECTID
) w
ON A.SECPROJECTID = W.SECPROJECTID
JOIN (SELECT *
FROM mdm.DIM_PROJECT
WHERE PROJ_STATE IN ('运行中', '已结束')) Z
ON W.PRIMARYKEY = Z.PROJ_ID
;
考虑到视图用到DBLINK，直接转成成事实表 DM_IC_PRIVILEGE
执行计划分析：
产生了39703274次递归调用！！！
10046事件跟踪，获取递归调用产生性能数据
TKPROF: Release 11.2.0.4.0 - Development on Mon Mar 14 17:41:49 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Trace file: /oracle/diag/rdbms/dwdb/dwdb/trace/dwdb_ora_22311.trc
Sort options: prsela exeela fchela
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
SQL ID: 02b8by6dk1zf8 Plan Hash: 3720327543
SELECT DICT_NAME
FROM
ADM.FCT_DICT WHERE DICT_ID=:B2 AND DICT_TAG_ID=:B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 26854 1.53 1.55 0 0 0 0
Fetch 26854 0.12 0.14 0 80562 0 26854
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 53709 1.66 1.69 0 80562 0 26854
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 5 (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS BY INDEX ROWID FCT_DICT (cr=3 pr=0 pw=0 time=17 us cost=1 size=61 card=1)
1 1 1 INDEX UNIQUE SCAN FCT_DICT_PK (cr=2 pr=0 pw=0 time=11 us cost=0 size=0 card=1)(object id 152132)
********************************************************************************
SELECT A12.SECUCODE SECUCODE,
A12.SECUMARKET SECUMARKET,
A12.SECUCODE SECUNAME,
A12.SECUMARKET SECUMARKET0,
A12.SECUNAME SECUNAME0,
A12.SECUMARKET CUSTCOL_16,
CHECK_DESC(A12.SECUMARKET, 'SECUMARKET') CUSTCOL_19,
A14.PROJ_ID PROJ_ID,
A15.PROJ_NAME PROJ_NAME,
A15.PROJ_SHORT_NAME PROJ_SHORT_NAME,
A16.ID,
A16.NAME
FROM ADM.DIM_SECUMAIN A12,
ADM.FCT_POSITION A13,
ADM.DIM_SEC_PROJECT A14,
ADM.DIM_PROJECT A15,
ADM.DM_IC_PRIVILEGE A16
WHERE A12.SECUCODE = A13.SECUCODE --1233199
AND A12.SECUMARKET = A13.SECUMARKET --1233199
AND A13.SEC_PROJ_ID = A14.SEC_PROJ_ID --1233199
AND A14.PROJ_ID = A15.PROJ_ID --1206493
AND A15.PROJ_ID = A16.PRIMARYKEY
and A12.SECUCODE in ('000831')
GROUP BY A12.SECUCODE ,
A12.SECUMARKET ,
A12.SECUCODE ,
A12.SECUMARKET ,
A12.SECUNAME ,
A12.SECUMARKET ,
CHECK_DESC(A12.SECUMARKET, 'SECUMARKET') ,
A14.PROJ_ID ,
A15.PROJ_NAME ,
A15.PROJ_SHORT_NAME ,
A16.ID,
A16.NAME
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 172 1.48 1.45 0 8244 0 2558
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 174 1.48 1.46 0 8244 0 2558
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 5
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
2558 2558 2558 HASH GROUP BY (cr=88806 pr=0 pw=0 time=3151657 us cost=2266 size=2603208 card=4119)
26854 26854 26854 HASH JOIN (cr=8244 pr=0 pw=0 time=97937 us cost=2265 size=2603208 card=4119)
829 829 829 HASH JOIN (cr=8040 pr=0 pw=0 time=61947 us cost=2197 size=250914 card=589)
833 833 833 HASH JOIN (cr=7691 pr=0 pw=0 time=57571 us cost=2095 size=221331 card=1059)
2 2 2 TABLE ACCESS BY INDEX ROWID DIM_SECUMAIN (cr=4 pr=0 pw=0 time=51 us cost=3 size=272 card=2)
2 2 2 INDEX RANGE SCAN PK_DIM_SECUMAIN (cr=2 pr=0 pw=0 time=32 us cost=2 size=0 card=2)(object id 150632)
833 833 833 HASH JOIN (cr=7687 pr=0 pw=0 time=56941 us cost=2092 size=115924 card=1588)
833 833 833 INDEX FAST FULL SCAN PK_FCT_POSITION (cr=7656 pr=0 pw=0 time=54714 us cost=2082 size=74636 card=1588)(object id 151078)
2952 2952 2952 TABLE ACCESS FULL DIM_SEC_PROJECT (cr=31 pr=0 pw=0 time=350 us cost=9 size=76752 card=2952)
8279 8279 8279 TABLE ACCESS FULL DIM_PROJECT (cr=349 pr=0 pw=0 time=1511 us cost=102 size=1643992 card=7576)
42319 42319 42319 TABLE ACCESS FULL DM_IC_PRIVILEGE (cr=204 pr=0 pw=0 time=5018 us cost=68 size=10921708 card=53018)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 172 0.00 0.00
SQL*Net message from client 172 151.85 548.16
********************************************************************************
SQL ID: 06nvwn223659v Plan Hash: 0
alter session set events '10046 trace name context off'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Parsing user id: 5
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 172 1.48 1.45 0 8244 0 2558
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 176 1.48 1.46 0 8244 0 2558
Misses in library cache during parse: 0
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 173 0.00 0.00
SQL*Net message from client 173 151.85 566.40
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 26854 1.53 1.55 0 0 0 0
Fetch 26854 0.12 0.14 0 80562 0 26854
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 53709 1.66 1.69 0 80562 0 26854
Misses in library cache during parse: 0
3 user SQL statements in session.
0 internal SQL statements in session.
3 SQL statements in session.
********************************************************************************
Trace file: /oracle/diag/rdbms/dwdb/dwdb/trace/dwdb_ora_22311.trc
Trace file compatibility: 11.1.0.7
Sort options: prsela exeela fchela
1 session in tracefile.
3 user SQL statements in trace file.
0 internal SQL statements in trace file.
3 SQL statements in trace file.
3 unique SQL statements in trace file.
376912 lines in trace file.
551 elapsed seconds in trace file.
其中 CHECK_DESC(A12.SECUMARKET, 'SECUMARKET') CUSTCOL_19, 是调用一下函数获取数据
SELECT DICT_NAME
FROM
ADM.FCT_DICT WHERE DICT_ID=:B2 AND DICT_TAG_ID=:B1 代码等价改写，把函数改成多表关联
SELECT A12.SECUCODE SECUCODE,
A12.SECUMARKET SECUMARKET,
A12.SECUCODE SECUNAME,
A12.SECUMARKET SECUMARKET0,
A12.SECUNAME SECUNAME0,
A12.SECUMARKET CUSTCOL_16,
A17.DICT_NAME CUSTCOL_19, --
A14.PROJ_ID PROJ_ID,
A15.PROJ_NAME PROJ_NAME,
A15.PROJ_SHORT_NAME PROJ_SHORT_NAME,
A16.ID,
A16.NAME
FROM ADM.DIM_SECUMAIN A12,
ADM.FCT_POSITION A13,
ADM.DIM_SEC_PROJECT A14,
ADM.DIM_PROJECT A15,
ADM.DM_IC_PRIVILEGE A16,
ADM.FCT_DICT A17
WHERE A12.SECUCODE = A13.SECUCODE --1233199
AND A12.SECUMARKET = A13.SECUMARKET --1233199
AND A13.SEC_PROJ_ID = A14.SEC_PROJ_ID --1233199
AND A14.PROJ_ID = A15.PROJ_ID --1206493
AND A12.SECUMARKET = A17.Dict_Id(+)
AND A17.DICT_TAG_ID(+) = 'SECUMARKET'
AND A15.PROJ_ID = A16.PRIMARYKEY
GROUP BY A12.SECUCODE,
A12.SECUMARKET,
A12.SECUCODE,
A12.SECUMARKET,
A12.SECUNAME,
A12.SECUMARKET,
A17.DICT_NAME,
A14.PROJ_ID,
A15.PROJ_NAME,
A15.PROJ_SHORT_NAME,
A16.ID,
A16.NAME
最终执行计划如下：
2374372 rows selected.
Elapsed: 00:01:07.91
Execution Plan
----------------------------------------------------------
Plan hash value: 4172279296
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6777K| 1654M| | 378K (1)| 01:15:39 |
| 1 | HASH GROUP BY | | 6777K| 1654M| 1765M| 378K (1)| 01:15:39 |
|* 2 | HASH JOIN | | 6777K| 1654M| | 4325 (1)| 00:00:52 |
| 3 | TABLE ACCESS FULL | DM_IC_PRIVILEGE | 42319 | 1033K| | 68 (0)| 00:00:01 |
|* 4 | HASH JOIN RIGHT OUTER | | 1304K| 287M| | 4238 (1)| 00:00:51 |
|* 5 | TABLE ACCESS FULL | FCT_DICT | 73 | 4453 | | 3 (0)| 00:00:01 |
|* 6 | HASH JOIN | | 1233K| 199M| | 4231 (1)| 00:00:51 |
| 7 | TABLE ACCESS FULL | DIM_PROJECT | 8279 | 614K| | 102 (0)| 00:00:02 |
|* 8 | HASH JOIN | | 1233K| 110M| | 4126 (1)| 00:00:50 |
|* 9 | TABLE ACCESS FULL | DIM_SEC_PROJECT | 1641 | 13128 | | 9 (0)| 00:00:01 |
|* 10 | HASH JOIN | | 1233K| 101M| 5024K| 4113 (1)| 00:00:50 |
| 11 | TABLE ACCESS FULL | DIM_SECUMAIN | 61922 | 4293K| | 238 (0)| 00:00:03 |
| 12 | INDEX FAST FULL SCAN| PK_FCT_POSITION | 1233K| 17M| | 2050 (1)| 00:00:25 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A15"."PROJ_ID"=TO_NUMBER("A16"."PRIMARYKEY"))
4 - access("A12"."SECUMARKET"="A17"."DICT_ID"(+))
5 - filter("A17"."DICT_TAG_ID"(+)='SECUMARKET')
6 - access("A14"."PROJ_ID"="A15"."PROJ_ID")
8 - access("A13"."SEC_PROJ_ID"="A14"."SEC_PROJ_ID")
9 - filter("A14"."PROJ_ID" IS NOT NULL)
10 - access("A12"."SECUCODE"="A13"."SECUCODE" AND "A12"."SECUMARKET"="A13"."SECUMARKET")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9109 consistent gets
0 physical reads
0 redo size
240063895 bytes sent via SQL*Net to client
1741721 bytes received via SQL*Net from client
158293 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2374372 rows processed
总结：
学会等价改写Sql，Sql中尽量避免调用函数，可以采用表链接方式，如果不能避免，就尽量减少调用。
本站文章版权归原作者及原出处所有 。内容为作者个人观点， 并不代表本站赞同其观点和对其真实性负责。本站是一个个人学习交流的平台，并不用于任何商业目的，如果有任何问题，请及时联系我们，我们将根据著作权人的要求，立即更正或者删除有关内容。本站拥有对此声明的最终解释权。
1 个评论
学习了