ORACLE SQL调优之统计信息缺失导致的逻辑读暴增

最新推荐文章于 2023-07-06 09:06:54 发布

clm20482

最新推荐文章于 2023-07-06 09:06:54 发布

阅读量358

点赞数

文章标签：数据库网络操作系统

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。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29357786/viewspace-2128108/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29357786/viewspace-2128108/