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

清風艾艾發表於2016-11-09
    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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章