2016年11月8日，接到廣西負責人申告，說決策系統一條SQL平時執行2s左右能執行完，現在卻要執行2:30多才能出結果，請求對其做處理。
作業系統：RHEL LINUX 6.4
資料庫版本：11.2.0.4
首先，登入伺服器檢視資料庫伺服器的狀態：
[oracle@orcl ~]$ free -m
total used free shared buffers cached
Mem: 129153 123613 5539 0 343 117864
-/+ buffers/cache: 5405 123747
Swap: 191999 467 191532
透過檢視伺服器記憶體，可以看到記憶體一共130G，已使用123G，剩餘5.5G，換頁空間使用467MB，可以確定伺服器記憶體使用正常。
檢視資料庫監聽狀態：
[oracle@orcl ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 08-NOV-2016 09:48:51
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 08-JUN-2016 17:47:49
Uptime 152 days 16 hr. 1 min. 2 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File /u01/app/oracle/diag/tnslsnr/orcl/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orcl.db.localdomain)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
透過資料庫監聽狀態檢視，可以確定資料庫及監聽已結連續開啟時間為152天16小時。
[oracle@orcl ~]$ top
top - 09:49:02 up 152 days, 18:32, 2 users, load average: 2.21, 1.62, 1.04
Tasks: 478 total, 1 running, 477 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.9%us, 0.2%sy, 0.0%ni, 94.2%id, 4.6%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 132253592k total, 126913664k used, 5339928k free, 352264k buffers
Swap: 196607996k total, 479148k used, 196128848k free, 121026748k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
3953 oracle 20 0 62.7g 2.8g 2.8g D 17.0 2.2 2:00.50 oracle
3955 oracle 20 0 62.7g 2.8g 2.8g S 10.8 2.2 1:50.42 oracle
3517 oracle 20 0 62.8g 2.4g 2.4g S 2.3 1.9 2:08.83 oracle
3712 oracle 20 0 62.8g 2.6g 2.6g S 2.3 2.1 1:16.29 oracle
3714 oracle 20 0 62.8g 2.9g 2.8g S 2.3 2.3 1:34.68 oracle
3708 oracle 20 0 62.8g 2.9g 2.9g S 2.0 2.3 1:32.83 oracle
4112 oracle 20 0 15352 1448 860 R 1.3 0.0 0:00.19 top
2452 root 20 0 10980 580 404 S 0.3 0.0 76:47.26 irqbalance
3639 root 20 0 40384 256 184 S 0.3 0.0 12:51.61 udisks-daemon
3851 root 20 0 97908 3832 2908 S 0.3 0.0 0:00.25 sshd
9872 oracle 20 0 62.7g 189m 185m S 0.3 0.1 385:33.41 oracle
9878 oracle 20 0 62.7g 6.4g 6.4g S 0.3 5.1 312:32.43 oracle
13010 root 20 0 0 0 0 S 0.3 0.0 0:04.48 kworker/4:2
16002 oracle 20 0 62.7g 104m 101m S 0.3 0.1 3:09.09 oracle
16260 root 20 0 0 0 0 S 0.3 0.0 0:16.74 kworker/24:2
透過top命令檢視，CPU比較空閒，CPU使用正常。
其次，檢視資料庫活動中的會話，發現有46個，正常：
[oracle@orcl ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 8 09:49:26 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select count(*) from v$session where status!='INACTIVE';
COUNT(*)
----------
46
再次，檢視執行緩慢的sql的執行計劃：
SQL>set autot on
SQL>set timing on
SQL> WITH T1 AS
2 (SELECT MGFD.DRUG_CODE_17 AS ITEM_ID,
3 MGFD.DRUG_NAME_CH AS ITEM_NAME,
4 SUM(MGFD.FREQUENCY) AS ORDER_COUNT,
5 SUM(MGFD.TOTAL_COSTS) AS TOTAL_COSTS
6 FROM hcorclda.MV_GATHER_FACT_DRUG MGFD
7 WHERE CALENDAR_MONTH_ID >= '201609'
8 AND CALENDAR_MONTH_ID <= '201609'
9 AND INSUR_TYPE_ID IN ('0', '1', '2', '3', '4', '5', '6', '7', '9')
10 AND AREA_ID IN ('00000076',
11 '00000081',
12 '00000082',
13 '00000083',
14 '00000084',
15 '00000086',
16 '00000087',
17 '00000088',
18 '00000089',
19 '00000090',
20 '00000091',
21 '00000092')
22 AND ptype = '1'
23 GROUP BY MGFD.DRUG_NAME_CH, MGFD.DRUG_CODE_17),
24 T2 AS
25 (SELECT *
26 FROM ( /*+parallel(a,4)*/
27 select n.DRUG_NAME_CH AS ITEM_NAME,
28 n.DRUG_CODE_17 AS ITEM_ID,
29 count(distinct HISID) EXP_ORD_COUNT,
30 sum(TOTAL_COSTS) as EXP_COSTS
31 from hcorclda.fact_exception_rule a,
32 hcorclda.dim_area b,
33 hcorclda.dim_medical_category c,
34 hcorclda.dim_insur_property d,
35 hcorclda.DIM_HOSPITAL e,
36 hcorclda.Dim_Rule h,
37 hcorclda.CATALOG_CATEGORY_MAPPING m,
38 hcorclda.DRUG_CATEGORY_NORMAL n
39 where a.area_key = b.area_key
40 and a.medical_key = c.medical_key
41 and a.insur_key = d.insur_key
42 and a.hospital_id = e.hospital_id
43 and a.rule_key = h.rule_key
44 and a.ITEM_ID = m.CLIENT_CATEGORY_CODE
45 AND m.NORMAL_CATEGORY_CODE = n.DRUG_CODE_17
46 and h.ismain = '0'
47 and a.time_key between to_date('201609' || '01', 'yyyy-mm-dd') and
48 last_day(to_date('201609' || '01', 'yyyy-mm-dd'))
49 AND d.TYPE_ID IN ('0', '1', '2', '3', '4', '5', '6', '7', '9')
50 AND b.AREA_ID IN ('00000076',
51 '00000081',
52 '00000082',
53 '00000083',
54 '00000084',
55 '00000086',
56 '00000087',
57 '00000088',
58 '00000089',
59 '00000090',
60 '00000091',
61 '00000092')
62 group by n.DRUG_NAME_CH, n.DRUG_CODE_17) A)
63 SELECT *
64 FROM (SELECT T1.ITEM_ID,
65 T1.ITEM_NAME,
66 T1.ORDER_COUNT AS ORD_COUNT,
67 T1.TOTAL_COSTS AS ORD_COSTS,
68 DECODE(T2.EXP_ORD_COUNT, '', 0, T2.EXP_ORD_COUNT) AS EXP_ORD_COUNT,
69 DECODE(T2.EXP_COSTS, '', 0, T2.EXP_COSTS) AS EXP_COSTS,
70 DECODE(T1.ORDER_COUNT,
71 0,
72 0,
73 ROUND(DECODE(T2.EXP_ORD_COUNT, '', 0, T2.EXP_ORD_COUNT) /
74 T1.ORDER_COUNT * 100,
75 2)) AS EXP_ORD_COUNT_PROPORTION,
76 DECODE(T1.TOTAL_COSTS,
77 0,
78 0,
79 ROUND(DECODE(T2.EXP_COSTS, '', 0, T2.EXP_COSTS) /
80 T1.TOTAL_COSTS * 100,
81 2)) AS EXP_COSTS_PROPORTION
82 FROM T1, T2
83 WHERE T1.ITEM_ID = T2.ITEM_ID(+)
84 ORDER BY ORD_COUNT asc) T
85 WHERE ROWNUM <= '10'
86 ;
Elapsed: 00:02:24.88
Execution Plan
----------------------------------------------------------
Plan hash value: 4245738290
----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 382 | 82 (5)| 00:00:01 | | |
|* 1 | COUNT STOPKEY | | | | | | | |
| 2 | VIEW | | 1 | 382 | 82 (5)| 00:00:01 | | |
|* 3 | SORT ORDER BY STOPKEY | | 1 | 320 | 82 (5)| 00:00:01 | | |
| 4 | NESTED LOOPS OUTER | | 1 | 320 | 81 (4)| 00:00:01 | | |
| 5 | VIEW | | 1 | 292 | 53 (2)| 00:00:01 | | |
| 6 | HASH GROUP BY | | 1 | 55 | 53 (2)| 00:00:01 | | |
| 7 | PARTITION RANGE SINGLE | | 1 | 55 | 52 (0)| 00:00:01 | 45 | 45 |
|* 8 | TABLE ACCESS FULL | MV_GATHER_FACT_DRUG | 1 | 55 | 52 (0)| 00:00:01 | 45 | 45 |
| 9 | VIEW PUSHED PREDICATE | | 1 | 28 | 27 (4)| 00:00:01 | | |
| 10 | SORT GROUP BY | | 1 | 109 | 27 (4)| 00:00:01 | | |
| 11 | VIEW | VW_DAG_0 | 1 | 109 | 27 (4)| 00:00:01 | | |
| 12 | SORT GROUP BY | | 1 | 367 | 27 (4)| 00:00:01 | | |
|* 13 | FILTER | | | | | | | |
| 14 | NESTED LOOPS | | 4 | 1468 | 26 (0)| 00:00:01 | | |
| 15 | NESTED LOOPS | | 4 | 1444 | 26 (0)| 00:00:01 | | |
|* 16 | HASH JOIN | | 4 | 1416 | 26 (0)| 00:00:01 | | |
|* 17 | TABLE ACCESS FULL | DIM_RULE | 2 | 16 | 3 (0)| 00:00:01 | | |
|* 18 | HASH JOIN | | 25 | 8650 | 23 (0)| 00:00:01 | | |
|* 19 | TABLE ACCESS FULL | DIM_AREA | 1 | 12 | 3 (0)| 00:00:01 | | |
|* 20 | HASH JOIN | | 300 | 97K| 20 (0)| 00:00:01 | | |
|* 21 | TABLE ACCESS FULL | DIM_INSUR_PROPERTY | 1 | 8 | 3 (0)| 00:00:01 | | |
| 22 | NESTED LOOPS | | 1200 | 382K| 17 (0)| 00:00:01 | | |
| 23 | NESTED LOOPS | | 728K| 382K| 17 (0)| 00:00:01 | | |
| 24 | NESTED LOOPS | | 1 | 54 | 17 (0)| 00:00:01 | | |
|* 25 | TABLE ACCESS FULL | CATALOG_CATEGORY_MAPPING | 1 | 24 | 15 (0)| 00:00:01 | | |
| 26 | TABLE ACCESS BY INDEX ROWID | DRUG_CATEGORY_NORMAL | 1 | 30 | 2 (0)| 00:00:01 | | |
|* 27 | INDEX RANGE SCAN | IDX_DRUG_CODE_17 | 1 | | 1 (0)| 00:00:01 | | |
|* 28 | INDEX RANGE SCAN | IDX_TIME_RULE | 728K| | 0 (0)| 00:00:01 | | |
|* 29 | TABLE ACCESS BY GLOBAL INDEX ROWID| FACT_EXCEPTION_RULE | 2161 | 574K| 0 (0)| 00:00:01 | ROWID | ROWID |
|* 30 | INDEX UNIQUE SCAN | IND_HOSPITAL_ID | 1 | 7 | 0 (0)| 00:00:01 | | |
|* 31 | INDEX UNIQUE SCAN | PK_MEDICAL_CATEGORY | 1 | 6 | 0 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
3 - filter(ROWNUM<=10)
8 - filter("CALENDAR_MONTH_ID"='201609' AND "PTYPE"='1' AND ("AREA_ID"='00000076' OR "AREA_ID"='00000081' OR "AREA_ID"='00000082'
OR "AREA_ID"='00000083' OR "AREA_ID"='00000084' OR "AREA_ID"='00000086' OR "AREA_ID"='00000087' OR "AREA_ID"='00000088' OR
"AREA_ID"='00000089' OR "AREA_ID"='00000090' OR "AREA_ID"='00000091' OR "AREA_ID"='00000092') AND ("INSUR_TYPE_ID"='0' OR
"INSUR_TYPE_ID"='1' OR "INSUR_TYPE_ID"='2' OR "INSUR_TYPE_ID"='3' OR "INSUR_TYPE_ID"='4' OR "INSUR_TYPE_ID"='5' OR
"INSUR_TYPE_ID"='6' OR "INSUR_TYPE_ID"='7' OR "INSUR_TYPE_ID"='9'))
13 - filter(LAST_DAY(TO_DATE('20160901','yyyy-mm-dd'))>=TO_DATE('20160901','yyyy-mm-dd'))
16 - access("A"."RULE_KEY"="H"."RULE_KEY")
17 - filter("H"."ISMAIN"='0')
18 - access("A"."AREA_KEY"="B"."AREA_KEY")
19 - filter("B"."AREA_ID"='00000076' OR "B"."AREA_ID"='00000081' OR "B"."AREA_ID"='00000082' OR "B"."AREA_ID"='00000083' OR
"B"."AREA_ID"='00000084' OR "B"."AREA_ID"='00000086' OR "B"."AREA_ID"='00000087' OR "B"."AREA_ID"='00000088' OR
"B"."AREA_ID"='00000089' OR "B"."AREA_ID"='00000090' OR "B"."AREA_ID"='00000091' OR "B"."AREA_ID"='00000092')
20 - access("A"."INSUR_KEY"="D"."INSUR_KEY")
21 - filter("D"."TYPE_ID"='0' OR "D"."TYPE_ID"='1' OR "D"."TYPE_ID"='2' OR "D"."TYPE_ID"='3' OR "D"."TYPE_ID"='4' OR
"D"."TYPE_ID"='5' OR "D"."TYPE_ID"='6' OR "D"."TYPE_ID"='7' OR "D"."TYPE_ID"='9')
25 - filter("M"."NORMAL_CATEGORY_CODE"="T1"."ITEM_ID")
27 - access("N"."DRUG_CODE_17"="T1"."ITEM_ID")
filter("M"."NORMAL_CATEGORY_CODE"="N"."DRUG_CODE_17")
28 - access("A"."TIME_KEY">=TO_DATE('20160901','yyyy-mm-dd') AND "A"."TIME_KEY"<=LAST_DAY(TO_DATE('20160901','yyyy-mm-dd')))
29 - filter("A"."ITEM_ID"="M"."CLIENT_CATEGORY_CODE")
30 - access("A"."HOSPITAL_ID"="E"."HOSPITAL_ID")
31 - access("A"."MEDICAL_KEY"="C"."MEDICAL_KEY")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
12835272 consistent gets
0 physical reads
0 redo size
1523 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2683 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> conn hcorclda
Enter password:
Connected.
SQL> set timing on
SQL> set autot on
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
SQL> WITH T1 AS
2 (SELECT MGFD.DRUG_CODE_17 AS ITEM_ID,
3 MGFD.DRUG_NAME_CH AS ITEM_NAME,
4 SUM(MGFD.FREQUENCY) AS ORDER_COUNT,
5 SUM(MGFD.TOTAL_COSTS) AS TOTAL_COSTS
6 FROM MV_GATHER_FACT_DRUG MGFD
7 WHERE CALENDAR_MONTH_ID >= '201609'
8 AND CALENDAR_MONTH_ID <= '201609'
9 AND INSUR_TYPE_ID IN ('0', '1', '2', '3', '4', '5', '6', '7', '9')
10 AND AREA_ID IN ('00000076',
11 '00000081',
12 '00000082',
13 '00000083',
14 '00000084',
15 '00000086',
16 '00000087',
17 '00000088',
18 '00000089',
19 '00000090',
20 '00000091',
21 '00000092')
22 AND ptype = '1'
23 GROUP BY MGFD.DRUG_NAME_CH, MGFD.DRUG_CODE_17),
24 T2 AS
25 (SELECT *
26 FROM ( /*+parallel(a,4)*/
27 select n.DRUG_NAME_CH AS ITEM_NAME,
28 n.DRUG_CODE_17 AS ITEM_ID,
29 count(distinct HISID) EXP_ORD_COUNT,
30 sum(TOTAL_COSTS) as EXP_COSTS
31 from fact_exception_rule a,
32 dim_area b,
33 dim_medical_category c,
34 dim_insur_property d,
35 DIM_HOSPITAL e,
36 Dim_Rule h,
37 CATALOG_CATEGORY_MAPPING m,
38 DRUG_CATEGORY_NORMAL n
39 where a.area_key = b.area_key
40 and a.medical_key = c.medical_key
41 and a.insur_key = d.insur_key
42 and a.hospital_id = e.hospital_id
43 and a.rule_key = h.rule_key
44 and a.ITEM_ID = m.CLIENT_CATEGORY_CODE
45 AND m.NORMAL_CATEGORY_CODE = n.DRUG_CODE_17
46 and h.ismain = '0'
47 and a.time_key between to_date('201609' || '01', 'yyyy-mm-dd') and
48 last_day(to_date('201609' || '01', 'yyyy-mm-dd'))
49 AND d.TYPE_ID IN ('0', '1', '2', '3', '4', '5', '6', '7', '9')
50 AND b.AREA_ID IN ('00000076',
51 '00000081',
52 '00000082',
53 '00000083',
54 '00000084',
55 '00000086',
56 '00000087',
57 '00000088',
58 '00000089',
59 '00000090',
60 '00000091',
61 '00000092')
62 group by n.DRUG_NAME_CH, n.DRUG_CODE_17) A)
63 SELECT *
64 FROM (SELECT T1.ITEM_ID,
65 T1.ITEM_NAME,
66 T1.ORDER_COUNT AS ORD_COUNT,
67 T1.TOTAL_COSTS AS ORD_COSTS,
68 DECODE(T2.EXP_ORD_COUNT, '', 0, T2.EXP_ORD_COUNT) AS EXP_ORD_COUNT,
69 DECODE(T2.EXP_COSTS, '', 0, T2.EXP_COSTS) AS EXP_COSTS,
70 DECODE(T1.ORDER_COUNT,
71 0,
72 0,
73 ROUND(DECODE(T2.EXP_ORD_COUNT, '', 0, T2.EXP_ORD_COUNT) /
74 T1.ORDER_COUNT * 100,
75 2)) AS EXP_ORD_COUNT_PROPORTION,
76 DECODE(T1.TOTAL_COSTS,
77 0,
78 0,
79 ROUND(DECODE(T2.EXP_COSTS, '', 0, T2.EXP_COSTS) /
80 T1.TOTAL_COSTS * 100,
81 2)) AS EXP_COSTS_PROPORTION
82 FROM T1, T2
83 WHERE T1.ITEM_ID = T2.ITEM_ID(+)
84 ORDER BY ORD_COUNT asc) T
85 WHERE ROWNUM <= '10'
86 ;
10 rows selected.
Elapsed: 00:02:35.92
Execution Plan
----------------------------------------------------------
Plan hash value: 2041173827
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 382 | 69 (6)| 00:00:01 | | |
|* 1 | COUNT STOPKEY | | | | | | | |
| 2 | VIEW | | 1 | 382 | 69 (6)| 00:00:01 | | |
|* 3 | SORT ORDER BY STOPKEY | | 1 | 320 | 69 (6)| 00:00:01 | | |
| 4 | NESTED LOOPS OUTER | | 1 | 320 | 68 (5)| 00:00:01 | | |
| 5 | VIEW | | 1 | 292 | 53 (2)| 00:00:01 | | |
| 6 | HASH GROUP BY | | 1 | 55 | 53 (2)| 00:00:01 | | |
| 7 | PARTITION RANGE SINGLE | | 1 | 55 | 52 (0)| 00:00:01 | 45 | 45 |
|* 8 | TABLE ACCESS FULL | MV_GATHER_FACT_DRUG | 1 | 55 | 52 (0)| 00:00:01 | 45 | 45 |
| 9 | VIEW PUSHED PREDICATE | | 1 | 28 | 14 (8)| 00:00:01 | | |
| 10 | SORT GROUP BY | | 1 | 109 | 14 (8)| 00:00:01 | | |
| 11 | VIEW | VW_DAG_0 | 1 | 109 | 14 (8)| 00:00:01 | | |
| 12 | SORT GROUP BY | | 1 | 159 | 14 (8)| 00:00:01 | | |
|* 13 | FILTER | | | | | | | |
| 14 | NESTED LOOPS | | 1 | 159 | 13 (0)| 00:00:01 | | |
| 15 | NESTED LOOPS | | 1 | 152 | 13 (0)| 00:00:01 | | |
| 16 | NESTED LOOPS | | 1 | 146 | 13 (0)| 00:00:01 | | |
| 17 | NESTED LOOPS | | 1 | 116 | 11 (0)| 00:00:01 | | |
|* 18 | HASH JOIN | | 1 | 92 | 10 (0)| 00:00:01 | | |
| 19 | NESTED LOOPS | | 1 | 80 | 7 (0)| 00:00:01 | | |
| 20 | NESTED LOOPS | | 1 | 80 | 7 (0)| 00:00:01 | | |
| 21 | NESTED LOOPS | | 1 | 72 | 7 (0)| 00:00:01 | | |
|* 22 | TABLE ACCESS FULL | DIM_RULE | 2 | 16 | 3 (0)| 00:00:01 | | |
| 23 | TABLE ACCESS BY GLOBAL INDEX ROWID| FACT_EXCEPTION_RULE | 1 | 64 | 2 (0)| 00:00:01 | ROWID | ROWID |
|* 24 | INDEX RANGE SCAN | IDX_TIME_RULE | 3645 | | 2 (0)| 00:00:01 | | |
|* 25 | INDEX UNIQUE SCAN | P_INSUR_KEY | 1 | | 0 (0)| 00:00:01 | | |
|* 26 | TABLE ACCESS BY INDEX ROWID | DIM_INSUR_PROPERTY | 1 | 8 | 0 (0)| 00:00:01 | | |
|* 27 | TABLE ACCESS FULL | DIM_AREA | 1 | 12 | 3 (0)| 00:00:01 | | |
|* 28 | INDEX RANGE SCAN | IDX_CLIENT_CODE | 1 | 24 | 1 (0)| 00:00:01 | | |
| 29 | TABLE ACCESS BY INDEX ROWID | DRUG_CATEGORY_NORMAL | 1 | 30 | 2 (0)| 00:00:01 | | |
|* 30 | INDEX RANGE SCAN | IDX_DRUG_CODE_17 | 1 | | 1 (0)| 00:00:01 | | |
|* 31 | INDEX UNIQUE SCAN | PK_MEDICAL_CATEGORY | 1 | 6 | 0 (0)| 00:00:01 | | |
|* 32 | INDEX UNIQUE SCAN | IND_HOSPITAL_ID | 1 | 7 | 0 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
3 - filter(ROWNUM<=10)
8 - filter("CALENDAR_MONTH_ID"='201609' AND "PTYPE"='1' AND ("AREA_ID"='00000076' OR "AREA_ID"='00000081' OR
"AREA_ID"='00000082' OR "AREA_ID"='00000083' OR "AREA_ID"='00000084' OR "AREA_ID"='00000086' OR "AREA_ID"='00000087' OR
"AREA_ID"='00000088' OR "AREA_ID"='00000089' OR "AREA_ID"='00000090' OR "AREA_ID"='00000091' OR "AREA_ID"='00000092') AND
("INSUR_TYPE_ID"='0' OR "INSUR_TYPE_ID"='1' OR "INSUR_TYPE_ID"='2' OR "INSUR_TYPE_ID"='3' OR "INSUR_TYPE_ID"='4' OR
"INSUR_TYPE_ID"='5' OR "INSUR_TYPE_ID"='6' OR "INSUR_TYPE_ID"='7' OR "INSUR_TYPE_ID"='9'))
13 - filter(LAST_DAY(TO_DATE('20160901','yyyy-mm-dd'))>=TO_DATE('20160901','yyyy-mm-dd'))
18 - access("A"."AREA_KEY"="B"."AREA_KEY")
22 - filter("H"."ISMAIN"='0')
24 - access("A"."TIME_KEY">=TO_DATE('20160901','yyyy-mm-dd') AND "A"."RULE_KEY"="H"."RULE_KEY" AND
"A"."TIME_KEY"<=LAST_DAY(TO_DATE('20160901','yyyy-mm-dd')))
filter("A"."RULE_KEY"="H"."RULE_KEY")
25 - access("A"."INSUR_KEY"="D"."INSUR_KEY")
26 - filter("D"."TYPE_ID"='0' OR "D"."TYPE_ID"='1' OR "D"."TYPE_ID"='2' OR "D"."TYPE_ID"='3' OR "D"."TYPE_ID"='4' OR
"D"."TYPE_ID"='5' OR "D"."TYPE_ID"='6' OR "D"."TYPE_ID"='7' OR "D"."TYPE_ID"='9')
27 - filter("B"."AREA_ID"='00000076' OR "B"."AREA_ID"='00000081' OR "B"."AREA_ID"='00000082' OR "B"."AREA_ID"='00000083' OR
"B"."AREA_ID"='00000084' OR "B"."AREA_ID"='00000086' OR "B"."AREA_ID"='00000087' OR "B"."AREA_ID"='00000088' OR
"B"."AREA_ID"='00000089' OR "B"."AREA_ID"='00000090' OR "B"."AREA_ID"='00000091' OR "B"."AREA_ID"='00000092')
28 - access("A"."ITEM_ID"="M"."CLIENT_CATEGORY_CODE" AND "M"."NORMAL_CATEGORY_CODE"="T1"."ITEM_ID")
30 - access("N"."DRUG_CODE_17"="T1"."ITEM_ID")
filter("M"."NORMAL_CATEGORY_CODE"="N"."DRUG_CODE_17")
31 - access("A"."MEDICAL_KEY"="C"."MEDICAL_KEY")
32 - access("A"."HOSPITAL_ID"="E"."HOSPITAL_ID")
透過sys和業務使用者hcorclda分別執行慢的sql語句，可以發現sys執行時其邏輯讀有12835272之多，業務使用者由於許可權問題無法獲取sql執行統計資訊，另外，比較詭
異的是業務使用者及SYS執行時，sql的執行計劃的TIME列均是00:00:01，可以肯定的是一定有問題。
查詢sql訪問的物件，發現其中有2個比較大的物件MV_GATHER_FACT_DRUG、fact_exception_rule，其餘物件的資料量都是不超過2萬的小表：
SQL> select count(*) from hcorclda.MV_GATHER_FACT_DRUG;
COUNT(*)
----------
1833954
SQL> select count(*) from hcorclda.fact_exception_rule;
COUNT(*)
----------
532991
SQL> select count(*) from hcorclda.dim_area;
COUNT(*)
----------
12
SQL> select count(*) from hcorclda.dim_medical_category;
COUNT(*)
----------
6
SQL> select count(*) from hcorclda.dim_insur_property;
COUNT(*)
----------
54
SQL> select count(*) from hcorclda. DIM_HOSPITAL;
COUNT(*)
----------
7136
SQL> select count(*) from hcorclda.Dim_Rule;
COUNT(*)
----------
28
SQL> select count(*) from hcorclda.CATALOG_CATEGORY_MAPPING;
COUNT(*)
----------
16195
SQL> select count(*) from hcorclda.DRUG_CATEGORY_NORMAL;
COUNT(*)
----------
11520
由此，可以發現，ORACLE CBO先構建T1內嵌檢視並作為with查詢中主查詢的驅動表，並且是全掃描的大分割槽表MV_GATHER_FACT_DRUG的第45分割槽，對於T2內嵌檢視，
CBO做了謂詞推入，至於謂詞推入是否合理，這裡先不下定論。對with查詢語句的拆分如下，由拆分後單獨執行發現T1檢視1341：
T1 AS (SELECT MGFD.DRUG_CODE_17 AS ITEM_ID,
MGFD.DRUG_NAME_CH AS ITEM_NAME,
SUM(MGFD.FREQUENCY) AS ORDER_COUNT,
SUM(MGFD.TOTAL_COSTS) AS TOTAL_COSTS
FROM MV_GATHER_FACT_DRUG MGFD
WHERE CALENDAR_MONTH_ID >= '201609'
AND CALENDAR_MONTH_ID <= '201609'
AND INSUR_TYPE_ID IN ('0', '1', '2', '3', '4', '5', '6', '7', '9')
AND AREA_ID IN ('00000076',
'00000081',
'00000082',
'00000083',
'00000084',
'00000086',
'00000087',
'00000088',
'00000089',
'00000090',
'00000091',
'00000092')
AND ptype = '1'
GROUP BY MGFD.DRUG_NAME_CH, MGFD.DRUG_CODE_17)
--單獨執行返回1341條資料
T2 AS (SELECT *
FROM ( /*+parallel(a,4)*/
select n.DRUG_NAME_CH AS ITEM_NAME,
n.DRUG_CODE_17 AS ITEM_ID,
count(distinct HISID) EXP_ORD_COUNT,
sum(TOTAL_COSTS) as EXP_COSTS
from fact_exception_rule a,
dim_area b,
dim_medical_category c,
dim_insur_property d,
DIM_HOSPITAL e,
Dim_Rule h,
CATALOG_CATEGORY_MAPPING m,
DRUG_CATEGORY_NORMAL n
where a.area_key = b.area_key
and a.medical_key = c.medical_key
and a.insur_key = d.insur_key
and a.hospital_id = e.hospital_id
and a.rule_key = h.rule_key
and a.ITEM_ID = m.CLIENT_CATEGORY_CODE
AND m.NORMAL_CATEGORY_CODE = n.DRUG_CODE_17
and h.ismain = '0'
and a.time_key between to_date('201609' || '01', 'yyyy-mm-dd') and
last_day(to_date('201609' || '01', 'yyyy-mm-dd'))
AND d.TYPE_ID IN ('0', '1', '2', '3', '4', '5', '6', '7', '9')
AND b.AREA_ID IN ('00000076',
'00000081',
'00000082',
'00000083',
'00000084',
'00000086',
'00000087',
'00000088',
'00000089',
'00000090',
'00000091',
'00000092')
group by n.DRUG_NAME_CH, n.DRUG_CODE_17) A)
單獨執行返回394條資料
with查詢的主查詢
SELECT *
FROM (SELECT T1.ITEM_ID,
T1.ITEM_NAME,
T1.ORDER_COUNT AS ORD_COUNT,
T1.TOTAL_COSTS AS ORD_COSTS,
DECODE(T2.EXP_ORD_COUNT, '', 0, T2.EXP_ORD_COUNT) AS EXP_ORD_COUNT,
DECODE(T2.EXP_COSTS, '', 0, T2.EXP_COSTS) AS EXP_COSTS,
DECODE(T1.ORDER_COUNT,
0,
0,
ROUND(DECODE(T2.EXP_ORD_COUNT, '', 0, T2.EXP_ORD_COUNT) /
T1.ORDER_COUNT * 100,
2)) AS EXP_ORD_COUNT_PROPORTION,
DECODE(T1.TOTAL_COSTS,
0,
0,
ROUND(DECODE(T2.EXP_COSTS, '', 0, T2.EXP_COSTS) /
T1.TOTAL_COSTS * 100,
2)) AS EXP_COSTS_PROPORTION
FROM T1, T2
WHERE T1.ITEM_ID = T2.ITEM_ID(+)
ORDER BY ORD_COUNT asc) T
WHERE ROWNUM <= '10'
由SYS執行SQL執行計劃的28步謂詞及業務使用者執行SQL執行計劃的24步，發現SQL中別名對應的A表返回的資料量比較大，分別是2161、3645。
8 - access("A"."TIME_KEY">=TO_DATE('20160901','yyyy-mm-dd') AND "A"."TIME_KEY"<=LAST_DAY(TO_DATE('20160901','yyyy-mm-dd')))
24 - access("A"."TIME_KEY">=TO_DATE('20160901','yyyy-mm-dd') AND "A"."RULE_KEY"="H"."RULE_KEY" AND
"A"."TIME_KEY"<=LAST_DAY(TO_DATE('20160901','yyyy-mm-dd')))
filter("A"."RULE_KEY"="H"."RULE_KEY")
SYS執行sql時CBO提示dynamic sampling used for this statement (level=2)，這裡猜測可能是sql訪問的物件部分沒有統計資訊或統計資訊不準確。
檢視別名a對應的表fact_exception_rule的統計資訊，發現表FACT_EXCEPTION_RULE沒有統計資訊。
SQL> set linesize 1000
SQL> set pagesize 1000
SQL> select savtime,rowcnt,blkcnt,avgrln,analyzetime
2 from sys.wri$_optstat_tab_history
3 where obj# in(select object_id from dba_objects where object_name='FACT_EXCEPTION_RULE');
SAVTIME ROWCNT BLKCNT AVGRLN ANALYZETIME
---------------------------------------- ---------- ---------- ---------- ------------
25-JAN-16 04.00.24.444202 PM +08:00
25-JAN-16 04.00.26.749209 PM +08:00
25-JAN-16 04.00.27.496555 PM +08:00
資料錄入時間均是2016年1月份25日的，省略顯示其餘648分割槽統計資訊。
再檢視大表MV_GATHER_FACT_DRUG，發現大分割槽表MV_GATHER_FACT_DRUG也沒有統計資訊。
SQL> select savtime,rowcnt,blkcnt,avgrln,analyzetime
2 from sys.wri$_optstat_tab_history
3 where obj# in(select object_id from dba_objects where object_name='MV_GATHER_FACT_DRUG');
SAVTIME ROWCNT BLKCNT AVGRLN ANALYZETIME
---------------------------------------- ---------- ---------- ---------- ------------
25-JAN-16 04.01.42.140492 PM +08:00
資料錄入時間均是2016年1月25日的，省略顯示其餘182分割槽統計資訊。
檢視關鍵表FACT_EXCEPTION_RULE的列直方圖資訊：
SQL> set linesize 1000
SQL> col low_value for a30
SQL> col high_value for a30
SQL>
SQL> select table_name,column_name,low_value,high_value,density,num_buckets
2 from dba_tab_col_statistics
3 where table_name='&tab_name'
4 and column_name='&col_name';
TABLE_NAME COLUMN_NAME LOW_VALUE HIGH_VALUE DENSITY NUM_BUCKETS
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------- -----------
FACT_EXCEPTION_RULE TIME_KEY 78700101010101 7872061E010101 .001560062 254
FACT_EXCEPTION_RULE TIME_KEY 78720103010101 78730A0E010101 .002079002 254
FACT_EXCEPTION_RULE TIME_KEY 78720801010101 7873041E010101 .00390625 254
FACT_EXCEPTION_RULE TIME_KEY 78720801010101 7873041E010101 .00390625 254
FACT_EXCEPTION_RULE TIME_KEY   0 0
FACT_EXCEPTION_RULE TIME_KEY       0 0
FACT_EXCEPTION_RULE TIME_KEY    0 0
7 rows selected.
發現查詢謂詞中使用到的TIME_KEY只有[78700101010101,7872061E010101]、[78720103010101，78730A0E010101]、[78720801010101，7873041E010101]三個區間，使用如下語句
轉換成日期格式就是：[2012:01:01 00:00:00,2014:06:30 00:00:00]、[2014:01:03 00:00:00,2015:10:14 00:00:00]、[2014:08:01 00:00:00,2015:04:30 00:00:00]
set serveroutput on
declare
v_low_value date;
v_high_value date;
begin
dbms_stats.convert_raw_value('&low_value',v_low_value);
dbms_stats.convert_raw_value('&high_value',v_high_value);
dbms_output.put_line('['||v_low_value||','||v_high_value||']');
end;
而查詢謂詞的時間"A"."TIME_KEY">=TO_DATE('20160901','yyyy-mm-dd') AND "A"."TIME_KEY"<=LAST_DAY(TO_DATE('20160901','yyyy-mm-dd')) 不在表FACT_EXCEPTION_RULE
列TIME_KEY直方圖中的時間段內[2012:01:01 00:00:00,2014:06:30 00:00:00]、[2014:01:03 00:00:00,2015:10:14 00:00:00]、[2014:08:01 00:00:00,2015:04:30 00:00:00]。
到此，可以確定是由於SQL訪問的物件缺失統計資訊，導致sql訪問謂詞越界，引起邏輯讀暴增，而sql語句執行效率低下。
處理方法是對sql訪問的物件進行統計資訊收集：
exec dbms_stats.gather_table_stats(ownname=>'hcorclda',tabname=>'MV_GATHER_FACT_DRUG',estimate_percent=>100，degree=>5);
exec dbms_stats.gather_table_stats(ownname=>'hcorclda',tabname=>'fact_exception_rule',estimate_percent=>100，degree=>5);
exec dbms_stats.gather_table_stats(ownname=>'hcorclda',tabname=>'dim_area',estimate_percent=>100，degree=>5);
exec dbms_stats.gather_table_stats(ownname=>'hcorclda',tabname=>'dim_medical_category',estimate_percent=>100，degree=>5);
exec dbms_stats.gather_table_stats(ownname=>'hcorclda',tabname=>'dim_insur_property',estimate_percent=>100，degree=>5);
exec dbms_stats.gather_table_stats(ownname=>'hcorclda',tabname=>'DIM_HOSPITAL',estimate_percent=>100，degree=>5);
exec dbms_stats.gather_table_stats(ownname=>'hcorclda',tabname=>'Dim_Rule',estimate_percent=>100，degree=>5);
exec dbms_stats.gather_table_stats(ownname=>'hcorclda',tabname=>'CATALOG_CATEGORY_MAPPING',estimate_percent=>100，degree=>5);
exec dbms_stats.gather_table_stats(ownname=>'hcorclda',tabname=>'DRUG_CATEGORY_NORMAL',estimate_percent=>100，degree=>5);
收集完統計資訊，再次使用無論是sys使用者還是業務使用者執行相關的sql語句，其速度均在秒級別完成。
業務使用者執行
SQL> WITH T1 AS
2 (SELECT MGFD.DRUG_CODE_17 AS ITEM_ID,
3 MGFD.DRUG_NAME_CH AS ITEM_NAME,
4 SUM(MGFD.FREQUENCY) AS ORDER_COUNT,
5 SUM(MGFD.TOTAL_COSTS) AS TOTAL_COSTS
6 FROM MV_GATHER_FACT_DRUG MGFD
7 WHERE CALENDAR_MONTH_ID >= '201609'
8 AND CALENDAR_MONTH_ID <= '201609'
9 AND INSUR_TYPE_ID IN ('0', '1', '2', '3', '4', '5', '6', '7', '9')
10 AND AREA_ID IN ('00000076',
11 '00000081',
12 '00000082',
13 '00000083',
14 '00000084',
15 '00000086',
16 '00000087',
17 '00000088',
18 '00000089',
19 '00000090',
20 '00000091',
21 '00000092')
22 AND ptype = '1'
23 GROUP BY MGFD.DRUG_NAME_CH, MGFD.DRUG_CODE_17),
24 T2 AS
25 (SELECT *
26 FROM ( /*+parallel(a,4)*/
27 select n.DRUG_NAME_CH AS ITEM_NAME,
28 n.DRUG_CODE_17 AS ITEM_ID,
29 count(distinct HISID) EXP_ORD_COUNT,
30 sum(TOTAL_COSTS) as EXP_COSTS
31 from fact_exception_rule a,
32 dim_area b,
33 dim_medical_category c,
34 dim_insur_property d,
35 DIM_HOSPITAL e,
36 Dim_Rule h,
37 CATALOG_CATEGORY_MAPPING m,
38 DRUG_CATEGORY_NORMAL n
39 where a.area_key = b.area_key
40 and a.medical_key = c.medical_key
41 and a.insur_key = d.insur_key
42 and a.hospital_id = e.hospital_id
43 and a.rule_key = h.rule_key
44 and a.ITEM_ID = m.CLIENT_CATEGORY_CODE
45 AND m.NORMAL_CATEGORY_CODE = n.DRUG_CODE_17
46 and h.ismain = '0'
47 and a.time_key between to_date('201609' || '01', 'yyyy-mm-dd') and
48 last_day(to_date('201609' || '01', 'yyyy-mm-dd'))
49 AND d.TYPE_ID IN ('0', '1', '2', '3', '4', '5', '6', '7', '9')
50 AND b.AREA_ID IN ('00000076',
51 '00000081',
52 '00000082',
53 '00000083',
54 '00000084',
55 '00000086',
56 '00000087',
57 '00000088',
58 '00000089',
59 '00000090',
60 '00000091',
61 '00000092')
62 group by n.DRUG_NAME_CH, n.DRUG_CODE_17) A)
63 SELECT *
64 FROM (SELECT /*+ gather_plan_statistics */ T1.ITEM_ID,
65 T1.ITEM_NAME,
66 T1.ORDER_COUNT AS ORD_COUNT,
67 T1.TOTAL_COSTS AS ORD_COSTS,
68 DECODE(T2.EXP_ORD_COUNT, '', 0, T2.EXP_ORD_COUNT) AS EXP_ORD_COUNT,
69 DECODE(T2.EXP_COSTS, '', 0, T2.EXP_COSTS) AS EXP_COSTS,
70 DECODE(T1.ORDER_COUNT,
71 0,
72 0,
73 ROUND(DECODE(T2.EXP_ORD_COUNT, '', 0, T2.EXP_ORD_COUNT) /
74 T1.ORDER_COUNT * 100,
75 2)) AS EXP_ORD_COUNT_PROPORTION,
76 DECODE(T1.TOTAL_COSTS,
77 0,
78 0,
79 ROUND(DECODE(T2.EXP_COSTS, '', 0, T2.EXP_COSTS) /
80 T1.TOTAL_COSTS * 100,
81 2)) AS EXP_COSTS_PROPORTION
82 FROM T1, T2
83 WHERE T1.ITEM_ID = T2.ITEM_ID(+)
84 ORDER BY ORD_COUNT asc) T
85 WHERE ROWNUM <= '10'
86 ;
Elapsed: 00:00:01.23
Execution Plan
----------------------------------------------------------
Plan hash value: 923371125
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 3820 | | 8748 (1)| 00:01:45 | | |
|* 1 | COUNT STOPKEY | | | | | | | | |
| 2 | VIEW | | 79571 | 28M| | 8748 (1)| 00:01:45 | | |
|* 3 | SORT ORDER BY STOPKEY | | 79571 | 25M| 27M| 8748 (1)| 00:01:45 | | |
|* 4 | HASH JOIN RIGHT OUTER | | 79571 | 25M| | 3041 (2)| 00:00:37 | | |
| 5 | VIEW | | 1 | 42 | | 14 (8)| 00:00:01 | | |
| 6 | HASH GROUP BY | | 1 | 95 | | 14 (8)| 00:00:01 | | |
| 7 | VIEW | VW_DAG_0 | 1 | 95 | | 14 (8)| 00:00:01 | | |
| 8 | HASH GROUP BY | | 1 | 159 | | 14 (8)| 00:00:01 | | |
|* 9 | FILTER | | | | | | | | |
|* 10 | HASH JOIN | | 1 | 159 | | 13 (0)| 00:00:01 | | |
| 11 | NESTED LOOPS | | 1 | 147 | | 10 (0)| 00:00:01 | | |
| 12 | NESTED LOOPS | | 1 | 147 | | 10 (0)| 00:00:01 | | |
| 13 | NESTED LOOPS | | 1 | 117 | | 8 (0)| 00:00:01 | | |
| 14 | NESTED LOOPS | | 1 | 93 | | 7 (0)| 00:00:01 | | |
| 15 | NESTED LOOPS | | 1 | 86 | | 7 (0)| 00:00:01 | | |
| 16 | NESTED LOOPS | | 1 | 80 | | 7 (0)| 00:00:01 | | |
| 17 | NESTED LOOPS | | 1 | 72 | | 7 (0)| 00:00:01 | | |
|* 18 | TABLE ACCESS FULL | DIM_RULE | 2 | 16 | | 3 (0)| 00:00:01 | | |
| 19 | TABLE ACCESS BY GLOBAL INDEX ROWID| FACT_EXCEPTION_RULE | 1 | 64 | | 2 (0)| 00:00:01 | ROWID | ROWID |
|* 20 | INDEX RANGE SCAN | IDX_TIME_RULE | 3645 | | | 2 (0)| 00:00:01 | | |
|* 21 | TABLE ACCESS BY INDEX ROWID | DIM_INSUR_PROPERTY | 1 | 8 | | 0 (0)| 00:00:01 | | |
|* 22 | INDEX UNIQUE SCAN | P_INSUR_KEY | 1 | | | 0 (0)| 00:00:01 | | |
|* 23 | INDEX UNIQUE SCAN | PK_MEDICAL_CATEGORY | 1 | 6 | | 0 (0)| 00:00:01 | | |
|* 24 | INDEX UNIQUE SCAN | IND_HOSPITAL_ID | 1 | 7 | | 0 (0)| 00:00:01 | | |
|* 25 | INDEX RANGE SCAN | IDX_CLIENT_CODE | 1 | 24 | | 1 (0)| 00:00:01 | | |
|* 26 | INDEX RANGE SCAN | IDX_DRUG_CODE_17 | 1 | | | 1 (0)| 00:00:01 | | |
| 27 | TABLE ACCESS BY INDEX ROWID | DRUG_CATEGORY_NORMAL | 1 | 30 | | 2 (0)| 00:00:01 | | |
|* 28 | TABLE ACCESS FULL | DIM_AREA | 1 | 12 | | 3 (0)| 00:00:01 | | |
| 29 | VIEW | | 79571 | 22M| | 3026 (1)| 00:00:37 | | |
| 30 | HASH GROUP BY | | 79571 | 4351K| 5960K| 3026 (1)| 00:00:37 | | |
| 31 | PARTITION RANGE SINGLE | | 79571 | 4351K| | 1935 (2)| 00:00:24 | 45 | 45 |
|* 32 | TABLE ACCESS FULL | MV_GATHER_FACT_DRUG | 79571 | 4351K| | 1935 (2)| 00:00:24 | 45 | 45 |
----------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
3 - filter(ROWNUM<=10)
4 - access("T1"."ITEM_ID"="T2"."ITEM_ID"(+))
9 - filter(LAST_DAY(TO_DATE('20160901','yyyy-mm-dd'))>=TO_DATE('20160901','yyyy-mm-dd'))
10 - access("A"."AREA_KEY"="B"."AREA_KEY")
18 - filter("H"."ISMAIN"='0')
20 - access("A"."TIME_KEY">=TO_DATE('20160901','yyyy-mm-dd') AND "A"."RULE_KEY"="H"."RULE_KEY" AND
"A"."TIME_KEY"<=LAST_DAY(TO_DATE('20160901','yyyy-mm-dd')))
filter("A"."RULE_KEY"="H"."RULE_KEY")
21 - filter("D"."TYPE_ID"='0' OR "D"."TYPE_ID"='1' OR "D"."TYPE_ID"='2' OR "D"."TYPE_ID"='3' OR "D"."TYPE_ID"='4' OR "D"."TYPE_ID"='5' OR
"D"."TYPE_ID"='6' OR "D"."TYPE_ID"='7' OR "D"."TYPE_ID"='9')
22 - access("A"."INSUR_KEY"="D"."INSUR_KEY")
23 - access("A"."MEDICAL_KEY"="C"."MEDICAL_KEY")
24 - access("A"."HOSPITAL_ID"="E"."HOSPITAL_ID")
25 - access("A"."ITEM_ID"="M"."CLIENT_CATEGORY_CODE")
26 - access("M"."NORMAL_CATEGORY_CODE"="N"."DRUG_CODE_17")
28 - filter("B"."AREA_ID"='00000076' OR "B"."AREA_ID"='00000081' OR "B"."AREA_ID"='00000082' OR "B"."AREA_ID"='00000083' OR
"B"."AREA_ID"='00000084' OR "B"."AREA_ID"='00000086' OR "B"."AREA_ID"='00000087' OR "B"."AREA_ID"='00000088' OR "B"."AREA_ID"='00000089' OR
"B"."AREA_ID"='00000090' OR "B"."AREA_ID"='00000091' OR "B"."AREA_ID"='00000092')
32 - filter("PTYPE"='1' AND "CALENDAR_MONTH_ID"='201609' AND ("AREA_ID"='00000076' OR "AREA_ID"='00000081' OR "AREA_ID"='00000082' OR
"AREA_ID"='00000083' OR "AREA_ID"='00000084' OR "AREA_ID"='00000086' OR "AREA_ID"='00000087' OR "AREA_ID"='00000088' OR
"AREA_ID"='00000089' OR "AREA_ID"='00000090' OR "AREA_ID"='00000091' OR "AREA_ID"='00000092') AND ("INSUR_TYPE_ID"='0' OR
"INSUR_TYPE_ID"='1' OR "INSUR_TYPE_ID"='2' OR "INSUR_TYPE_ID"='3' OR "INSUR_TYPE_ID"='4' OR "INSUR_TYPE_ID"='5' OR "INSUR_TYPE_ID"='6' OR
"INSUR_TYPE_ID"='7' OR "INSUR_TYPE_ID"='9'))
SYS使用者執行
WITH T1 AS
(SELECT MGFD.DRUG_CODE_17 AS ITEM_ID,
MGFD.DRUG_NAME_CH AS ITEM_NAME,
SUM(MGFD.FREQUENCY) AS ORDER_COUNT,
SUM(MGFD.TOTAL_COSTS) AS TOTAL_COSTS
FROM hcorclda.MV_GATHER_FACT_DRUG MGFD
WHERE CALENDAR_MONTH_ID >= '201609'
AND CALENDAR_MONTH_ID <= '201609'
AND INSUR_TYPE_ID IN ('0', '1', '2', '3', '4', '5', '6', '7', '9')
AND AREA_ID IN ('00000076',
'00000081',
'00000082',
'00000083',
'00000084',
'00000086',
'00000087',
'00000088',
'00000089',
'00000090',
'00000091',
'00000092')
AND ptype = '1'
GROUP BY MGFD.DRUG_NAME_CH, MGFD.DRUG_CODE_17),
T2 AS
(SELECT *
FROM ( /*+parallel(a,4)*/
select n.DRUG_NAME_CH AS ITEM_NAME,
n.DRUG_CODE_17 AS ITEM_ID,
count(distinct HISID) EXP_ORD_COUNT,
sum(TOTAL_COSTS) as EXP_COSTS
from hcorclda.fact_exception_rule a,
hcorclda.dim_area b,
hcorclda.dim_medical_category c,
hcorclda.dim_insur_property d,
hcorclda.DIM_HOSPITAL e,
hcorclda.Dim_Rule h,
hcorclda.CATALOG_CATEGORY_MAPPING m,
hcorclda.DRUG_CATEGORY_NORMAL n
where a.area_key = b.area_key
and a.medical_key = c.medical_key
and a.insur_key = d.insur_key
and a.hospital_id = e.hospital_id
and a.rule_key = h.rule_key
and a.ITEM_ID = m.CLIENT_CATEGORY_CODE
AND m.NORMAL_CATEGORY_CODE = n.DRUG_CODE_17
and h.ismain = '0'
and a.time_key between to_date('201609' || '01', 'yyyy-mm-dd') and
last_day(to_date('201609' || '01', 'yyyy-mm-dd'))
AND d.TYPE_ID IN ('0', '1', '2', '3', '4', '5', '6', '7', '9')
AND b.AREA_ID IN ('00000076',
'00000081',
'00000082',
'00000083',
'00000084',
'00000086',
'00000087',
'00000088',
'00000089',
'00000090',
'00000091',
'00000092')
group by n.DRUG_NAME_CH, n.DRUG_CODE_17) A)
SELECT *
FROM (SELECT T1.ITEM_ID,
T1.ITEM_NAME,
T1.ORDER_COUNT AS ORD_COUNT,
T1.TOTAL_COSTS AS ORD_COSTS,
DECODE(T2.EXP_ORD_COUNT, '', 0, T2.EXP_ORD_COUNT) AS EXP_ORD_COUNT,
DECODE(T2.EXP_COSTS, '', 0, T2.EXP_COSTS) AS EXP_COSTS,
DECODE(T1.ORDER_COUNT,
0,
0,
ROUND(DECODE(T2.EXP_ORD_COUNT, '', 0, T2.EXP_ORD_COUNT) /
T1.ORDER_COUNT * 100,
2)) AS EXP_ORD_COUNT_PROPORTION,
DECODE(T1.TOTAL_COSTS,
0,
0,
ROUND(DECODE(T2.EXP_COSTS, '', 0, T2.EXP_COSTS) /
T1.TOTAL_COSTS * 100,
2)) AS EXP_COSTS_PROPORTION
FROM T1, T2
WHERE T1.ITEM_ID = T2.ITEM_ID(+)
ORDER BY ORD_COUNT asc) T
WHERE ROWNUM <= '10';
Elapsed: 00:00:02.23
Execution Plan
----------------------------------------------------------
Plan hash value: 3897396963
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 3820 | | 13045 (1)| 00:02:37 | | |
|* 1 | COUNT STOPKEY | | | | | | | | |
| 2 | VIEW | | 79571 | 28M| | 13045 (1)| 00:02:37 | | |
|* 3 | SORT ORDER BY STOPKEY | | 79571 | 25M| 27M| 13045 (1)| 00:02:37 | | |
|* 4 | HASH JOIN RIGHT OUTER | | 79571 | 25M| | 7338 (2)| 00:01:29 | | |
| 5 | VIEW | | 1 | 42 | | 4311 (1)| 00:00:52 | | |
| 6 | HASH GROUP BY | | 1 | 95 | | 4311 (1)| 00:00:52 | | |
| 7 | VIEW | VW_DAG_0 | 1 | 95 | | 4311 (1)| 00:00:52 | | |
| 8 | HASH GROUP BY | | 1 | 159 | | 4311 (1)| 00:00:52 | | |
|* 9 | FILTER | | | | | | | | |
| 10 | NESTED LOOPS | | 1 | 159 | | 4310 (1)| 00:00:52 | | |
| 11 | NESTED LOOPS | | 1 | 159 | | 4310 (1)| 00:00:52 | | |
| 12 | NESTED LOOPS | | 1 | 129 | | 4308 (1)| 00:00:52 | | |
| 13 | NESTED LOOPS | | 1 | 105 | | 4307 (1)| 00:00:52 | | |
| 14 | NESTED LOOPS | | 1 | 98 | | 4307 (1)| 00:00:52 | | |
|* 15 | HASH JOIN | | 1 | 90 | | 4306 (1)| 00:00:52 | | |
|* 16 | HASH JOIN | | 1 | 82 | | 4303 (1)| 00:00:52 | | |
|* 17 | HASH JOIN | | 1 | 70 | | 4300 (1)| 00:00:52 | | |
| 18 | INDEX FULL SCAN | PK_MEDICAL_CATEGORY | 77 | 462 | | 1 (0)| 00:00:01 | | |
| 19 | PARTITION RANGE ITERATOR| | 69247 | 4327K| | 4298 (1)| 00:00:52 | KEY | KEY |
| 20 | PARTITION LIST ALL | | 69247 | 4327K| | 4298 (1)| 00:00:52 | 1 | 5 |
|* 21 | TABLE ACCESS FULL | FACT_EXCEPTION_RULE | 69247 | 4327K| | 4298 (1)| 00:00:52 | KEY | KEY |
|* 22 | TABLE ACCESS FULL | DIM_AREA | 12 | 144 | | 3 (0)| 00:00:01 | | |
|* 23 | TABLE ACCESS FULL | DIM_RULE | 22 | 176 | | 3 (0)| 00:00:01 | | |
|* 24 | TABLE ACCESS BY INDEX ROWID| DIM_INSUR_PROPERTY | 1 | 8 | | 1 (0)| 00:00:01 | | |
|* 25 | INDEX UNIQUE SCAN | P_INSUR_KEY | 1 | | | 0 (0)| 00:00:01 | | |
|* 26 | INDEX UNIQUE SCAN | IND_HOSPITAL_ID | 1 | 7 | | 0 (0)| 00:00:01 | | |
|* 27 | INDEX RANGE SCAN | IDX_CLIENT_CODE | 1 | 24 | | 1 (0)| 00:00:01 | | |
|* 28 | INDEX RANGE SCAN | IDX_DRUG_CODE_17 | 1 | | | 1 (0)| 00:00:01 | | |
| 29 | TABLE ACCESS BY INDEX ROWID | DRUG_CATEGORY_NORMAL | 1 | 30 | | 2 (0)| 00:00:01 | | |
| 30 | VIEW | | 79571 | 22M| | 3026 (1)| 00:00:37 | | |
| 31 | HASH GROUP BY | | 79571 | 4351K| 5960K| 3026 (1)| 00:00:37 | | |
| 32 | PARTITION RANGE SINGLE | | 79571 | 4351K| | 1935 (2)| 00:00:24 | 45 | 45 |
|* 33 | TABLE ACCESS FULL | MV_GATHER_FACT_DRUG | 79571 | 4351K| | 1935 (2)| 00:00:24 | 45 | 45 |
------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
3 - filter(ROWNUM<=10)
4 - access("T1"."ITEM_ID"="T2"."ITEM_ID"(+))
9 - filter(LAST_DAY(TO_DATE('20160901','yyyy-mm-dd'))>=TO_DATE('20160901','yyyy-mm-dd'))
15 - access("A"."RULE_KEY"="H"."RULE_KEY")
16 - access("A"."AREA_KEY"="B"."AREA_KEY")
17 - access("A"."MEDICAL_KEY"="C"."MEDICAL_KEY")
21 - filter("A"."TIME_KEY">=TO_DATE('20160901','yyyy-mm-dd') AND "A"."TIME_KEY"<=LAST_DAY(TO_DATE('20160901','yyyy-mm-dd')))
22 - filter("B"."AREA_ID"='00000076' OR "B"."AREA_ID"='00000081' OR "B"."AREA_ID"='00000082' OR "B"."AREA_ID"='00000083' OR
"B"."AREA_ID"='00000084' OR "B"."AREA_ID"='00000086' OR "B"."AREA_ID"='00000087' OR "B"."AREA_ID"='00000088' OR
"B"."AREA_ID"='00000089' OR "B"."AREA_ID"='00000090' OR "B"."AREA_ID"='00000091' OR "B"."AREA_ID"='00000092')
23 - filter("H"."ISMAIN"='0')
24 - filter("D"."TYPE_ID"='0' OR "D"."TYPE_ID"='1' OR "D"."TYPE_ID"='2' OR "D"."TYPE_ID"='3' OR "D"."TYPE_ID"='4' OR
"D"."TYPE_ID"='5' OR "D"."TYPE_ID"='6' OR "D"."TYPE_ID"='7' OR "D"."TYPE_ID"='9')
25 - access("A"."INSUR_KEY"="D"."INSUR_KEY")
26 - access("A"."HOSPITAL_ID"="E"."HOSPITAL_ID")
27 - access("A"."ITEM_ID"="M"."CLIENT_CATEGORY_CODE")
28 - access("M"."NORMAL_CATEGORY_CODE"="N"."DRUG_CODE_17")
33 - filter("PTYPE"='1' AND "CALENDAR_MONTH_ID"='201609' AND ("AREA_ID"='00000076' OR "AREA_ID"='00000081' OR
"AREA_ID"='00000082' OR "AREA_ID"='00000083' OR "AREA_ID"='00000084' OR "AREA_ID"='00000086' OR "AREA_ID"='00000087' OR
"AREA_ID"='00000088' OR "AREA_ID"='00000089' OR "AREA_ID"='00000090' OR "AREA_ID"='00000091' OR "AREA_ID"='00000092') AND
("INSUR_TYPE_ID"='0' OR "INSUR_TYPE_ID"='1' OR "INSUR_TYPE_ID"='2' OR "INSUR_TYPE_ID"='3' OR "INSUR_TYPE_ID"='4' OR
"INSUR_TYPE_ID"='5' OR "INSUR_TYPE_ID"='6' OR "INSUR_TYPE_ID"='7'OR "INSUR_TYPE_ID"='9'))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
45137 consistent gets
1550 physical reads
0 redo size
1523 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed

收集完統計資訊，SQL執行的邏輯讀從12835272降低到45137，執行時間從2:35降低到2秒左右;同時，可以發現，ORACLE CBO優先構造T2內嵌檢視且作為with主查詢塊的驅動表，
有效降低驅動表的資料量，然後才去構造作為被驅動表的T1內嵌檢視，並且主查詢中T1、T2內嵌檢視做的是HASH JOIN RIGHT OUTER，不再是無統計資訊時的NESTED LOOPS OUTER。

ORACLE SQL調優之統計資訊缺失導致的邏輯讀暴增

相關文章