使用SQL Profile進行SQL最佳化案例

dbhelper發表於2014-11-27

一個社保系統的自助查詢系統查詢個人醫療費用明細的查詢語句要用一分多鐘還沒查詢出來,語句如下:

select * from  v_zzzd_ylbx_ylfymxcx where aac002='430703198202280017'

從上面的語句可知是從檢視 v_zzzd_ylbx_ylfymxcx中查詢資料。v_zzzd_ylbx_ylfymxcx檢視的建立語句如下:

create or replace view v_zzzd_ylbx_ylfymxcx as
select a.indi_id aac001,a.idcard aac002,'' aof008,a.center_id aab301,
       a.name aac003,a.hospital_id akf008,d.hospital_name akf009,a.serial_no akf010,
       f.biz_name akf011,
       nvl(round(sum(b.real_pay),2),0) akf012,
       nvl(round(sum(case when b.fund_id = '003' then b.real_pay else 0 end),2),0) akf013, 0 akf014,0 akf015,0 akf016,
       nvl(round(sum(case when b.fund_id = '001' then b.real_pay else 0 end),2),0) ak093,
       nvl(round(sum(b.real_pay),2),0) - nvl(round(sum(case when b.fund_id in ('999','003') and b.policy_item_code in ( 'S00','S01','C001','C004''C006') then b.real_pay else 0 end),2),0) ak092,
       nvl(round(sum(case when b.fund_id in ('999','003') and b.policy_item_code in ( 'S00','S01','C001','C004''C006') then b.real_pay else 0 end),2),0) ak094,
       nvl(round(sum(case when b.fund_id in('003', '999') then b.real_pay else 0 end),2),0) ak095,
       a.fin_date akf017,to_char(nvl(a.in_days,0)) akf018,
       nvl(round(sum(case when b.fund_id = '003' then b.real_pay else 0 end),2),0) akf019,
       nvl(round(sum(case when b.fund_id in( '001','201','301' ) then b.real_pay else 0 end),2),0) akf020
from  bs_insured h,mt_biz_fin a ,mt_pay_record_fin b,bs_disease c,bs_hospital d ,bs_hosp_level e ,bs_biztype f,bs_corp g
where h.indi_id=a.indi_id
  and a.hospital_id = b.hospital_id
  and a.serial_no = b.serial_no
  and a.biz_type = f.biz_type
  and a.center_id = f.center_id
  and a.center_id=c.center_id
  and a.fin_disease=c.icd
  and a.hospital_id = d.hospital_id
  and d.hosp_level=e.hosp_level
  and a.biz_type in ('10','11','12','13','16','17')
  and a.valid_flag = 1
  and b.valid_flag = 1
  and a.pers_type in ('1','2')
  and a.corp_id = g.corp_id
group by a.indi_id ,a.idcard ,a.center_id,a.name ,a.hospital_id ,d.hospital_name,a.serial_no , f.biz_name,a.fin_date,a.in_days;

生成SQL Profile有兩種方式:自動和手動方式,這裡使用自動方式來生成SQL Profile.
下面建立一個SQL自動調整最佳化任務:

SQL> declare 
  2   my_task_name varchar2(30);
  3   my_sqltext clob;
  4  begin
  5   my_sqltext :='select * from  v_zzzd_ylbx_ylfymxcx where aac002=''430703198202280017''';
  6   my_task_name :=dbms_sqltune.create_tuning_task(
  7           sql_text => my_sqltext,
  8           user_name => 'INSUR_CHANGDE',
  9           scope=>'COMPREHENSIVE',
 10          time_limit=>60,
 11          task_name => 'my_sql_tuning_task_2014080803',
 12          description=>'Task to tune a query on table v_zzzd_ylbx_ylfymxcx');
 13  end;
 14  /

PL/SQL procedure successfully completed.

SQL> 
SQL> begin
  2  dbms_sqltune.execute_tuning_task(task_name=>'my_sql_tuning_task_2014080803');
  3  end;
  4  /

PL/SQL procedure successfully completed.


透過下面的語句查詢最佳化建議

SQL>select dbms_sqltune.report_tuning_task('my_sql_tuning_task_2014080803') from dual;


GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : my_sql_tuning_task_2014080803
Tuning Task Owner                 : INSUR_CHANGDE
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 60
Completion Status                 : COMPLETED
Started at                        : 08/08/2014 19:42:47
Completed at                      : 08/08/2014 19:43:49
Number of Index Findings          : 1
Number of SQL Restructure Findings: 1
Number of Errors                  : 1

-------------------------------------------------------------------------------
Schema Name: INSUR_CHANGDE
SQL ID     : 0rpt6bzp60cjm
SQL Text   : select * from  v_zzzd_ylbx_ylfymxcx where
             aac002='430703198202280017'

-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------

1- Index Finding (see explain plans section below)
--------------------------------------------------
  透過建立一個或多個索引可以改進此語句的執行計劃。

  Recommendation (estimated benefit: 99.98%)
  ------------------------------------------
  - 考慮執行可以改進物理方案設計的 Access Advisor 或者建立推薦的索引。
    create index INSUR_CHANGDE.IDX$$_429C0001 on
    INSUR_CHANGDE.MT_BIZ_FIN("IDCARD",TO_NUMBER("VALID_FLAG"),"PERS_TYPE","BIZ_
    TYPE");
    這裡在建立IDX$$_429C0001索引時,TO_NUMBER("VALID_FLAG")這是因為表MT_BIZ_FIN中的valid_flag是varchar2而檢視定義中寫成了valid_flag=1的原因
     
  - 考慮執行可以改進物理方案設計的 Access Advisor 或者建立推薦的索引。
    create index INSUR_CHANGDE.IDX$$_429C0002 on
    INSUR_CHANGDE.MT_PAY_RECORD_FIN("HOSPITAL_ID","SERIAL_NO");

  Rationale
  ---------
    建立推薦的索引可以顯著地改進此語句的執行計劃。但是, 使用典型的 SQL 工作量執行 "Access Advisor"
    可能比單個語句更可取。透過這種方法可以獲得全面的索引建議案, 包括計算索引維護的開銷和附加的空間消耗。

2- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
  謂詞 TO_NUMBER("A"."VALID_FLAG")=1 (在執行計劃的行 ID 9 處使用) 包含索引列 "VALID_FLAG"
  的隱式資料型別轉換。此隱式資料型別轉換使最佳化程式無法有效地使用表 "INSUR_CHANGDE"."MT_BIZ_FIN" 的索引。
  這是因為表MT_BIZ_FIN中的valid_flag是varchar2而檢視定義中寫成了valid_flag=1的原因
  Recommendation
  --------------
  - 將謂詞重寫為等價型以便利用索引。

  Rationale
  ---------
    如果謂詞是不等式條件或者如果存在關於索引列的表示式或隱式資料型別轉換, 則最佳化程式無法使用索引。

-------------------------------------------------------------------------------
ERRORS SECTION
-------------------------------------------------------------------------------
- 當前操作因超時而中斷。這是因為最佳化任務設定的超時時間為60秒的原因
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 3562745886

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                         |     7 |  1505 |   127K  (2)| 00:25:25 |
|   1 |  HASH GROUP BY                      |                         |     7 |  1505 |   127K  (2)| 00:25:25 |
|   2 |   NESTED LOOPS                      |                         |     7 |  1505 |   127K  (2)| 00:25:25 |
|   3 |    NESTED LOOPS                     |                         |     7 |  1491 |   127K  (2)| 00:25:25 |
|   4 |     NESTED LOOPS                    |                         |     7 |  1253 |   127K  (2)| 00:25:25 |
|   5 |      NESTED LOOPS                   |                         |     7 |  1127 |   127K  (2)| 00:25:25 |
|   6 |       NESTED LOOPS                  |                         |     7 |  1085 |   127K  (2)| 00:25:25 |
|   7 |        NESTED LOOPS                 |                         |    14 |  1554 |   127K  (2)| 00:25:25 |
|   8 |         NESTED LOOPS                |                         |    14 |  1484 |   127K  (2)| 00:25:25 |
|*  9 |          TABLE ACCESS FULL          | MT_BIZ_FIN              |    14 |  1232 |   127K  (2)| 00:25:25 |
|  10 |          TABLE ACCESS BY INDEX ROWID| BS_BIZTYPE              |     1 |    18 |     1   (0)| 00:00:01 |
|* 11 |           INDEX UNIQUE SCAN         | PK_BS_BIZTYPE           |     1 |       |     1   (0)| 00:00:01 |
|* 12 |         INDEX UNIQUE SCAN           | PK_BS_CORP              |     1 |     5 |     1   (0)| 00:00:01 |
|* 13 |        TABLE ACCESS BY INDEX ROWID  | MT_PAY_RECORD_FIN       |     1 |    44 |     1   (0)| 00:00:01 |
|* 14 |         INDEX RANGE SCAN            | IDX_MT_PAY_RECORD_FIN_1 |     1 |       |     1   (0)| 00:00:01 |
|* 15 |       INDEX UNIQUE SCAN             | PK_BS_INSURED           |     1 |     6 |     1   (0)| 00:00:01 |
|* 16 |      INDEX RANGE SCAN               | INX_BS_DISEASE_01       |     1 |    18 |     1   (0)| 00:00:01 |
|  17 |     TABLE ACCESS BY INDEX ROWID     | BS_HOSPITAL             |     1 |    34 |     1   (0)| 00:00:01 |
|* 18 |      INDEX UNIQUE SCAN              | PK_BS_HOSPITAL          |     1 |       |     1   (0)| 00:00:01 |
|* 19 |    INDEX UNIQUE SCAN                | PK_BS_HOSP_LEVEL        |     1 |     2 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   9 - filter("A"."IDCARD"='430703198202280017' AND TO_NUMBER("A"."VALID_FLAG")=1 AND
              ("A"."PERS_TYPE"='1' OR "A"."PERS_TYPE"='2') AND ("A"."BIZ_TYPE"='10' OR "A"."BIZ_TYPE"='11' OR
              "A"."BIZ_TYPE"='12' OR "A"."BIZ_TYPE"='13' OR "A"."BIZ_TYPE"='16' OR "A"."BIZ_TYPE"='17'))
  11 - access("A"."CENTER_ID"="F"."CENTER_ID" AND "A"."BIZ_TYPE"="F"."BIZ_TYPE")
  12 - access("A"."CORP_ID"="G"."CORP_ID")
  13 - filter(TO_NUMBER("B"."VALID_FLAG")=1)
  14 - access("A"."HOSPITAL_ID"="B"."HOSPITAL_ID" AND "A"."SERIAL_NO"="B"."SERIAL_NO")
  15 - access("H"."INDI_ID"="A"."INDI_ID")
  16 - access("A"."CENTER_ID"="C"."CENTER_ID" AND "A"."FIN_DISEASE"="C"."ICD")
  18 - access("A"."HOSPITAL_ID"="D"."HOSPITAL_ID")
  19 - access("D"."HOSP_LEVEL"="E"."HOSP_LEVEL")

這是按最佳化建議建立兩個索引後的執行計劃

2- Using New Indices  
--------------------
Plan hash value: 2373509962

----------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                   |     7 |  1505 |    14   (8)| 00:00:01 |
|   1 |  HASH GROUP BY                       |                   |     7 |  1505 |    14   (8)| 00:00:01 |
|   2 |   NESTED LOOPS                       |                   |     7 |  1505 |    13   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                      |                   |     7 |  1470 |    12   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                     |                   |     7 |  1428 |    11   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                    |                   |     7 |  1302 |    10   (0)| 00:00:01 |
|   6 |       NESTED LOOPS                   |                   |     7 |  1288 |     9   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                  |                   |     7 |  1050 |     7   (0)| 00:00:01 |
|   8 |         NESTED LOOPS                 |                   |    14 |  1484 |     4   (0)| 00:00:01 |
|   9 |          INLIST ITERATOR             |                   |       |       |            |          |
|  10 |           TABLE ACCESS BY INDEX ROWID| MT_BIZ_FIN        |    14 |  1232 |     2   (0)| 00:00:01 |
|* 11 |            INDEX RANGE SCAN          | IDX$$_429C0001    |    14 |       |     1   (0)| 00:00:01 |
|  12 |          TABLE ACCESS BY INDEX ROWID | BS_BIZTYPE        |     1 |    18 |     1   (0)| 00:00:01 |
|* 13 |           INDEX UNIQUE SCAN          | PK_BS_BIZTYPE     |     1 |       |     1   (0)| 00:00:01 |
|* 14 |         TABLE ACCESS BY INDEX ROWID  | MT_PAY_RECORD_FIN |     1 |    44 |     1   (0)| 00:00:01 |
|* 15 |          INDEX RANGE SCAN            | IDX$$_429C0002    |     1 |       |     1   (0)| 00:00:01 |
|  16 |        TABLE ACCESS BY INDEX ROWID   | BS_HOSPITAL       |     1 |    34 |     1   (0)| 00:00:01 |
|* 17 |         INDEX UNIQUE SCAN            | PK_BS_HOSPITAL    |     1 |       |     1   (0)| 00:00:01 |
|* 18 |       INDEX UNIQUE SCAN              | PK_BS_HOSP_LEVEL  |     1 |     2 |     1   (0)| 00:00:01 |
|* 19 |      INDEX RANGE SCAN                | INX_BS_DISEASE_01 |     1 |    18 |     1   (0)| 00:00:01 |
|* 20 |     INDEX UNIQUE SCAN                | PK_BS_INSURED     |     1 |     6 |     1   (0)| 00:00:01 |
|* 21 |    INDEX UNIQUE SCAN                 | PK_BS_CORP        |     1 |     5 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  11 - access("A"."IDCARD"='430703198202280017' AND "MT_BIZ_FIN".???)
       filter("A"."BIZ_TYPE"='10' OR "A"."BIZ_TYPE"='11' OR "A"."BIZ_TYPE"='12' OR
              "A"."BIZ_TYPE"='13' OR "A"."BIZ_TYPE"='16' OR "A"."BIZ_TYPE"='17')
  13 - access("A"."CENTER_ID"="F"."CENTER_ID" AND "A"."BIZ_TYPE"="F"."BIZ_TYPE")
  14 - filter(TO_NUMBER("B"."VALID_FLAG")=1)
  15 - access("A"."HOSPITAL_ID"="B"."HOSPITAL_ID" AND "A"."SERIAL_NO"="B"."SERIAL_NO")
  17 - access("A"."HOSPITAL_ID"="D"."HOSPITAL_ID")
  18 - access("D"."HOSP_LEVEL"="E"."HOSP_LEVEL")
  19 - access("A"."CENTER_ID"="C"."CENTER_ID" AND "A"."FIN_DISEASE"="C"."ICD")
  20 - access("H"."INDI_ID"="A"."INDI_ID")
  21 - access("A"."CORP_ID"="G"."CORP_ID")

-------------------------------------------------------------------------------

因為前一次最佳化任務因為超時中斷了所以再次進行SQL自動最佳化任務,並將超時時間設定為600秒

SQL> declare 
  2   my_task_name varchar2(30);
  3   my_sqltext clob;
  4  begin
  5   my_sqltext :='select * from  v_zzzd_ylbx_ylfymxcx where aac002=''430703198202280017''';
  6   my_task_name :=dbms_sqltune.create_tuning_task(
  7           sql_text => my_sqltext,
  8           user_name => 'INSUR_CHANGDE',
  9           scope=>'COMPREHENSIVE',
 10          time_limit=>600,
 11          task_name => 'my_sql_tuning_task_2014080804',
 12          description=>'Task to tune a query on table v_zzzd_ylbx_ylfymxcx');
 13  end;
 14  /

PL/SQL procedure successfully completed.

SQL> 
SQL> begin
  2  dbms_sqltune.execute_tuning_task(task_name=>'my_sql_tuning_task_2014080804');
  3  end;
  4  /

PL/SQL procedure successfully completed.

透過下面的語句查詢最佳化建議

SQL>select dbms_sqltune.report_tuning_task('my_sql_tuning_task_2014080806') from dual;

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : my_sql_tuning_task_2014080804
Tuning Task Owner                 : INSUR_CHANGDE
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 600
Completion Status                 : COMPLETED
Started at                        : 08/08/2014 20:03:46
Completed at                      : 08/08/2014 20:04:27
Number of SQL Profile Findings    : 1

-------------------------------------------------------------------------------
Schema Name: INSUR_CHANGDE
SQL ID     : 0rpt6bzp60cjm
SQL Text   : select * from  v_zzzd_ylbx_ylfymxcx where
             aac002='430703198202280017'

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  為此語句找到了效能更好的執行計劃。

  Recommendation (estimated benefit: 28.75%)
  ------------------------------------------
  - 考慮接受推薦的 SQL 概要檔案。
    execute dbms_sqltune.accept_sql_profile(task_name =>
            'my_sql_tuning_task_2014080804', replace => TRUE);

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 3514293130

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                   |   251 | 53965 |    36   (6)| 00:00:01 |
|   1 |  HASH GROUP BY                        |                   |   251 | 53965 |    36   (6)| 00:00:01 |
|   2 |   NESTED LOOPS                        |                   |   251 | 53965 |    35   (3)| 00:00:01 |
|   3 |    NESTED LOOPS                       |                   |   252 | 52920 |    34   (3)| 00:00:01 |
|   4 |     NESTED LOOPS                      |                   |   252 | 51408 |    33   (4)| 00:00:01 |
|*  5 |      HASH JOIN                        |                   |   251 | 46686 |    32   (4)| 00:00:01 |
|*  6 |       TABLE ACCESS BY INDEX ROWID     | MT_PAY_RECORD_FIN |     1 |    44 |     1   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                   |                   |    28 |  4704 |    28   (0)| 00:00:01 |
|   8 |         NESTED LOOPS                  |                   |    28 |  3472 |    22   (0)| 00:00:01 |
|   9 |          NESTED LOOPS                 |                   |    79 |  9638 |    21   (0)| 00:00:01 |
|  10 |           INLIST ITERATOR             |                   |       |       |            |          |
|  11 |            TABLE ACCESS BY INDEX ROWID| MT_BIZ_FIN        |    79 |  6952 |     6   (0)| 00:00:01 |
|* 12 |             INDEX RANGE SCAN          | IDX$$_429C0001    |    27 |       |     1   (0)| 00:00:01 |
|  13 |           TABLE ACCESS BY INDEX ROWID | BS_HOSPITAL       |     1 |    34 |     1   (0)| 00:00:01 |
|* 14 |            INDEX UNIQUE SCAN          | PK_BS_HOSPITAL    |     1 |       |     1   (0)| 00:00:01 |
|* 15 |          INDEX UNIQUE SCAN            | PK_BS_HOSP_LEVEL  |     1 |     2 |     1   (0)| 00:00:01 |
|* 16 |         INDEX RANGE SCAN              | IDX$$_429C0002    |     1 |       |     1   (0)| 00:00:01 |
|  17 |       TABLE ACCESS FULL               | BS_BIZTYPE        |    96 |  1728 |     3   (0)| 00:00:01 |
|* 18 |      INDEX RANGE SCAN                 | INX_BS_DISEASE_01 |     1 |    18 |     1   (0)| 00:00:01 |
|* 19 |     INDEX UNIQUE SCAN                 | PK_BS_INSURED     |     1 |     6 |     1   (0)| 00:00:01 |
|* 20 |    INDEX UNIQUE SCAN                  | PK_BS_CORP        |     1 |     5 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("A"."BIZ_TYPE"="F"."BIZ_TYPE" AND "A"."CENTER_ID"="F"."CENTER_ID")
   6 - filter("B"."VALID_FLAG"='1')
  12 - access("A"."IDCARD"='430703198202280017' AND "A"."VALID_FLAG"='1' AND ("A"."PERS_TYPE"='1'
              OR "A"."PERS_TYPE"='2'))
       filter("A"."BIZ_TYPE"='10' OR "A"."BIZ_TYPE"='11' OR "A"."BIZ_TYPE"='12' OR
              "A"."BIZ_TYPE"='13' OR "A"."BIZ_TYPE"='16' OR "A"."BIZ_TYPE"='17')
  14 - access("A"."HOSPITAL_ID"="D"."HOSPITAL_ID")
  15 - access("D"."HOSP_LEVEL"="E"."HOSP_LEVEL")
  16 - access("A"."HOSPITAL_ID"="B"."HOSPITAL_ID" AND "A"."SERIAL_NO"="B"."SERIAL_NO")
  18 - access("A"."CENTER_ID"="C"."CENTER_ID" AND "A"."FIN_DISEASE"="C"."ICD")
  19 - access("H"."INDI_ID"="A"."INDI_ID")
  20 - access("A"."CORP_ID"="G"."CORP_ID")

2- Using SQL Profile
--------------------
Plan hash value: 484693682

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                   |   251 | 53965 |    25   (4)| 00:00:01 |
|   1 |  HASH GROUP BY                        |                   |   251 | 53965 |    25   (4)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID         | MT_PAY_RECORD_FIN |     1 |    44 |     1   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                       |                   |   251 | 53965 |    24   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                      |                   |    28 |  4788 |    19   (6)| 00:00:01 |
|   5 |      NESTED LOOPS                     |                   |    28 |  4284 |    18   (6)| 00:00:01 |
|   6 |       NESTED LOOPS                    |                   |    28 |  4116 |    17   (6)| 00:00:01 |
|   7 |        NESTED LOOPS                   |                   |    28 |  4060 |    16   (7)| 00:00:01 |
|   8 |         NESTED LOOPS                  |                   |    28 |  3108 |    10   (0)| 00:00:01 |
|*  9 |          HASH JOIN                    |                   |    28 |  2968 |     9   (0)| 00:00:01 |
|  10 |           TABLE ACCESS FULL           | BS_BIZTYPE        |    96 |  1728 |     3   (0)| 00:00:01 |
|  11 |           INLIST ITERATOR             |                   |       |       |            |          |
|  12 |            TABLE ACCESS BY INDEX ROWID| MT_BIZ_FIN        |    79 |  6952 |     6   (0)| 00:00:01 |
|* 13 |             INDEX RANGE SCAN          | IDX$$_429C0001    |    27 |       |     1   (0)| 00:00:01 |
|* 14 |          INDEX UNIQUE SCAN            | PK_BS_CORP        |     1 |     5 |     1   (0)| 00:00:01 |
|  15 |         TABLE ACCESS BY INDEX ROWID   | BS_HOSPITAL       |     1 |    34 |     1   (0)| 00:00:01 |
|* 16 |          INDEX UNIQUE SCAN            | PK_BS_HOSPITAL    |     1 |       |     1   (0)| 00:00:01 |
|* 17 |        INDEX UNIQUE SCAN              | PK_BS_HOSP_LEVEL  |     1 |     2 |     1   (0)| 00:00:01 |
|* 18 |       INDEX UNIQUE SCAN               | PK_BS_INSURED     |     1 |     6 |     1   (0)| 00:00:01 |
|* 19 |      INDEX RANGE SCAN                 | INX_BS_DISEASE_01 |     1 |    18 |     1   (0)| 00:00:01 |
|* 20 |     INDEX RANGE SCAN                  | IDX$$_429C0002    |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("B"."VALID_FLAG"='1')
   9 - access("A"."BIZ_TYPE"="F"."BIZ_TYPE" AND "A"."CENTER_ID"="F"."CENTER_ID")
  13 - access("A"."IDCARD"='430703198202280017' AND "A"."VALID_FLAG"='1' AND ("A"."PERS_TYPE"='1'
              OR "A"."PERS_TYPE"='2'))
       filter("A"."BIZ_TYPE"='10' OR "A"."BIZ_TYPE"='11' OR "A"."BIZ_TYPE"='12' OR
              "A"."BIZ_TYPE"='13' OR "A"."BIZ_TYPE"='16' OR "A"."BIZ_TYPE"='17')
  14 - access("A"."CORP_ID"="G"."CORP_ID")
  16 - access("A"."HOSPITAL_ID"="D"."HOSPITAL_ID")
  17 - access("D"."HOSP_LEVEL"="E"."HOSP_LEVEL")
  18 - access("H"."INDI_ID"="A"."INDI_ID")
  19 - access("A"."CENTER_ID"="C"."CENTER_ID" AND "A"."FIN_DISEASE"="C"."ICD")
  20 - access("A"."HOSPITAL_ID"="B"."HOSPITAL_ID" AND "A"."SERIAL_NO"="B"."SERIAL_NO")

-------------------------------------------------------------------------------

執行下面的語句來接受SQL 概要檔案

SQL>  execute dbms_sqltune.accept_sql_profile(task_name =>'my_sql_tuning_task_2014080804', replace => TRUE,force_match => TRUE);

PL/SQL procedure successfully completed.

再來測試該語句

SQL> select * from  v_zzzd_ylbx_ylfymxcx where aac002='430703198202280017';

 AAC001 AAC002               AAB301  AKF008       AKF010   AKF011    AKF012   AKF013  AKF014 
------- -------------------- ------- -----------  -------- ------- -------- -------- ------- 
  44499 430703198202280017   430701  4307000305   18000304 購藥          19       19       0 
  44499 430703198202280017   430701  4307030186   14200513 購藥          34       34       0 
  44499 430703198202280017   430701  4307000070   11535710 購藥           7        7       0 
  44499 430703198202280017   430701  4307000211   13157523 購藥          10       10       0 
  44499 430703198202280017   430701  4307000178   10504509 購藥        37.2     37.2       0 
  44499 430703198202280017   430701  4307000025   14186783 購藥         6.5      6.5       0 
  44499 430703198202280017   430701  4307000211   18855092 購藥          51       51       0 
  44499 430703198202280017   430701  4307000025   23298689 購藥          32       32       0 
  44499 430703198202280017   430701  4307000305   17251025 購藥          20       20       0 
  44499 430703198202280017   430701  4307000211   11246538 購藥        10.5     10.5       0 
  44499 430703198202280017   430701  4307000011   20015343 門診          20       20       0 
  44499 430703198202280017   430701  4307000135   13248044 購藥       103.2    103.2       0 
  44499 430703198202280017   430701  4307000070   17745955 購藥          20       20       0 
  44499 430703198202280017   430701  4307000011   23548511 門診        94.2     94.2       0 
  44499 430703198202280017   430701  4307000305   18000319 購藥          16       16       0 
  44499 430703198202280017   430701  4307000025   20291585 購藥         374      374       0 
  44499 430703198202280017   430701  4307000075   11425923 購藥        11.8     11.8       0 
  44499 430703198202280017   430701  4307000089   23298593 購藥       170.8    170.8       0 
  44499 430703198202280017   430701  4307000110   11548588 購藥        28.5     28.5       0 
  44499 430703198202280017   430701  4307000011   18454938 門診       105.8    105.8       0 
  44499 430703198202280017   430701  4307000075   11757756 購藥       282.7    282.7       0 
  44499 430703198202280017   430701  4307000025   10545113 購藥       340.8    340.8       0 
  44499 430703198202280017   430701  4307000285   17325032 購藥        67.5     67.5       0 
  44499 430703198202280017   430701  4307000070   17341126 購藥          87       87       0 
  44499 430703198202280017   430701  4307000211   17655418 購藥          20       20       0 
  44499 430703198202280017   430701  4307000011   19042114 門診       127.2    127.2       0 
  44499 430703198202280017   430701  4307000211   18070864 購藥           6        6       0 
  44499 430703198202280017   430701  4307000011   23547574 門診          36       36       0 

28 rows selected.

Elapsed: 00:00:00.01

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------
SQL_ID  1n2t3u0q0gmhz, child number 0
-------------------------------------
select * from  v_zzzd_ylbx_ylfymxcx where aac002='430703198202280017'

Plan hash value: 484693682

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                   |       |       |    25 (100)|          |
|   1 |  HASH GROUP BY                        |                   |   251 | 53965 |    25   (4)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID         | MT_PAY_RECORD_FIN |     1 |    44 |     1   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                       |                   |   251 | 53965 |    24   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                      |                   |    28 |  4788 |    19   (6)| 00:00:01 |
|   5 |      NESTED LOOPS                     |                   |    28 |  4284 |    18   (6)| 00:00:01 |
|   6 |       NESTED LOOPS                    |                   |    28 |  4116 |    17   (6)| 00:00:01 |
|   7 |        NESTED LOOPS                   |                   |    28 |  4060 |    16   (7)| 00:00:01 |
|   8 |         NESTED LOOPS                  |                   |    28 |  3108 |    10   (0)| 00:00:01 |
|*  9 |          HASH JOIN                    |                   |    28 |  2968 |     9   (0)| 00:00:01 |
|  10 |           TABLE ACCESS FULL           | BS_BIZTYPE        |    96 |  1728 |     3   (0)| 00:00:01 |
|  11 |           INLIST ITERATOR             |                   |       |       |            |          |
|  12 |            TABLE ACCESS BY INDEX ROWID| MT_BIZ_FIN        |    79 |  6952 |     6   (0)| 00:00:01 |
|* 13 |             INDEX RANGE SCAN          | IDX$$_429C0001    |    27 |       |     1   (0)| 00:00:01 |
|* 14 |          INDEX UNIQUE SCAN            | PK_BS_CORP        |     1 |     5 |     1   (0)| 00:00:01 |
|  15 |         TABLE ACCESS BY INDEX ROWID   | BS_HOSPITAL       |     1 |    34 |     1   (0)| 00:00:01 |
|* 16 |          INDEX UNIQUE SCAN            | PK_BS_HOSPITAL    |     1 |       |     1   (0)| 00:00:01 |
|* 17 |        INDEX UNIQUE SCAN              | PK_BS_HOSP_LEVEL  |     1 |     2 |     1   (0)| 00:00:01 |
|* 18 |       INDEX UNIQUE SCAN               | PK_BS_INSURED     |     1 |     6 |     1   (0)| 00:00:01 |
|* 19 |      INDEX RANGE SCAN                 | INX_BS_DISEASE_01 |     1 |    18 |     1   (0)| 00:00:01 |
|* 20 |     INDEX RANGE SCAN                  | IDX$$_429C0002    |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$F5BB74E1
   2 - SEL$F5BB74E1 / B@SEL$2
  10 - SEL$F5BB74E1 / F@SEL$2
  12 - SEL$F5BB74E1 / A@SEL$2
  13 - SEL$F5BB74E1 / A@SEL$2
  14 - SEL$F5BB74E1 / G@SEL$2
  15 - SEL$F5BB74E1 / D@SEL$2
  16 - SEL$F5BB74E1 / D@SEL$2
  17 - SEL$F5BB74E1 / E@SEL$2
  18 - SEL$F5BB74E1 / H@SEL$2
  19 - SEL$F5BB74E1 / C@SEL$2
  20 - SEL$F5BB74E1 / B@SEL$2

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
      OPT_PARAM('optimizer_index_cost_adj' 20)
      OPT_PARAM('optimizer_index_caching' 90)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$F5BB74E1")
      MERGE(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      FULL(@"SEL$F5BB74E1" "F"@"SEL$2")
      INDEX_RS_ASC(@"SEL$F5BB74E1" "A"@"SEL$2" ("MT_BIZ_FIN"."IDCARD" "MT_BIZ_FIN"."VALID_FLAG"
              "MT_BIZ_FIN"."PERS_TYPE" "MT_BIZ_FIN"."BIZ_TYPE"))
      NUM_INDEX_KEYS(@"SEL$F5BB74E1" "A"@"SEL$2" "IDX$$_429C0001" 3)
      INDEX(@"SEL$F5BB74E1" "G"@"SEL$2" ("BS_CORP"."CORP_ID"))
      INDEX_RS_ASC(@"SEL$F5BB74E1" "D"@"SEL$2" ("BS_HOSPITAL"."HOSPITAL_ID"))
      INDEX(@"SEL$F5BB74E1" "E"@"SEL$2" ("BS_HOSP_LEVEL"."HOSP_LEVEL"))
      INDEX(@"SEL$F5BB74E1" "H"@"SEL$2" ("BS_INSURED"."INDI_ID"))
      INDEX(@"SEL$F5BB74E1" "C"@"SEL$2" ("BS_DISEASE"."CENTER_ID" "BS_DISEASE"."ICD"))
      INDEX(@"SEL$F5BB74E1" "B"@"SEL$2" ("MT_PAY_RECORD_FIN"."HOSPITAL_ID"
              "MT_PAY_RECORD_FIN"."SERIAL_NO"))
      LEADING(@"SEL$F5BB74E1" "F"@"SEL$2" "A"@"SEL$2" "G"@"SEL$2" "D"@"SEL$2" "E"@"SEL$2" "H"@"SEL$2"
              "C"@"SEL$2" "B"@"SEL$2")
      USE_HASH(@"SEL$F5BB74E1" "A"@"SEL$2")
      USE_NL(@"SEL$F5BB74E1" "G"@"SEL$2")
      USE_NL(@"SEL$F5BB74E1" "D"@"SEL$2")
      USE_NL(@"SEL$F5BB74E1" "E"@"SEL$2")
      USE_NL(@"SEL$F5BB74E1" "H"@"SEL$2")
      USE_NL(@"SEL$F5BB74E1" "C"@"SEL$2")
      USE_NL(@"SEL$F5BB74E1" "B"@"SEL$2")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("B"."VALID_FLAG"='1')
   9 - access("A"."BIZ_TYPE"="F"."BIZ_TYPE" AND "A"."CENTER_ID"="F"."CENTER_ID")
  13 - access("A"."IDCARD"='430703198202280017' AND "A"."VALID_FLAG"='1' AND (("A"."PERS_TYPE"='1'
              OR "A"."PERS_TYPE"='2')))
       filter(("A"."BIZ_TYPE"='10' OR "A"."BIZ_TYPE"='11' OR "A"."BIZ_TYPE"='12' OR
              "A"."BIZ_TYPE"='13' OR "A"."BIZ_TYPE"='16' OR "A"."BIZ_TYPE"='17'))
  14 - access("A"."CORP_ID"="G"."CORP_ID")
  16 - access("A"."HOSPITAL_ID"="D"."HOSPITAL_ID")
  17 - access("D"."HOSP_LEVEL"="E"."HOSP_LEVEL")
  18 - access("H"."INDI_ID"="A"."INDI_ID")
  19 - access("A"."CENTER_ID"="C"."CENTER_ID" AND "A"."FIN_DISEASE"="C"."ICD")
  20 - access("A"."HOSPITAL_ID"="B"."HOSPITAL_ID" AND "A"."SERIAL_NO"="B"."SERIAL_NO")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "A"."INDI_ID"[NUMBER,22], "A"."IDCARD"[VARCHAR2,25], "A"."CENTER_ID"[VARCHAR2,10],
       "A"."NAME"[VARCHAR2,20], "A"."HOSPITAL_ID"[VARCHAR2,20], "D"."HOSPITAL_NAME"[VARCHAR2,70],
       "A"."SERIAL_NO"[VARCHAR2,16], "F"."BIZ_NAME"[VARCHAR2,20], "A"."FIN_DATE"[DATE,7],
       "A"."IN_DAYS"[NUMBER,22], SUM("B"."REAL_PAY")[22], SUM(CASE "B"."FUND_ID" WHEN '003' THEN
       "B"."REAL_PAY" ELSE 0 END )[22], SUM(CASE "B"."FUND_ID" WHEN '001' THEN "B"."REAL_PAY" ELSE 0 END
       )[22], SUM(CASE  WHEN (("B"."FUND_ID"='999' OR "B"."FUND_ID"='003') AND
       ("B"."POLICY_ITEM_CODE"='S00' OR "B"."POLICY_ITEM_CODE"='S01' OR "B"."POLICY_ITEM_CODE"='C001' OR
       "B"."POLICY_ITEM_CODE"='C004''C006')) THEN "B"."REAL_PAY" ELSE 0 END )[22], SUM(CASE "B"."FUND_ID"
       WHEN '003' THEN "B"."REAL_PAY" WHEN '999' THEN "B"."REAL_PAY" ELSE 0 END )[22], SUM(CASE
       "B"."FUND_ID" WHEN '001' THEN "B"."REAL_PAY" WHEN '201' THEN "B"."REAL_PAY" WHEN '301' THEN
       "B"."REAL_PAY" ELSE 0 END )[22]
   2 - "B"."POLICY_ITEM_CODE"[VARCHAR2,20], "B"."FUND_ID"[VARCHAR2,3], "B"."REAL_PAY"[NUMBER,22]
   3 - "A"."CENTER_ID"[VARCHAR2,10], "F"."BIZ_NAME"[VARCHAR2,20], "A"."HOSPITAL_ID"[VARCHAR2,20],
       "A"."SERIAL_NO"[VARCHAR2,16], "A"."INDI_ID"[NUMBER,22], "A"."NAME"[VARCHAR2,20],
       "A"."IDCARD"[VARCHAR2,25], "A"."IN_DAYS"[NUMBER,22], "A"."FIN_DATE"[DATE,7],
       "D"."HOSPITAL_NAME"[VARCHAR2,70], "B".ROWID[ROWID,10]
   4 - "A"."CENTER_ID"[VARCHAR2,10], "F"."BIZ_NAME"[VARCHAR2,20], "A"."HOSPITAL_ID"[VARCHAR2,20],
       "A"."SERIAL_NO"[VARCHAR2,16], "A"."INDI_ID"[NUMBER,22], "A"."NAME"[VARCHAR2,20],
       "A"."IDCARD"[VARCHAR2,25], "A"."IN_DAYS"[NUMBER,22], "A"."FIN_DATE"[DATE,7],
       "D"."HOSPITAL_NAME"[VARCHAR2,70]
   5 - "A"."CENTER_ID"[VARCHAR2,10], "F"."BIZ_NAME"[VARCHAR2,20], "A"."HOSPITAL_ID"[VARCHAR2,20],
       "A"."SERIAL_NO"[VARCHAR2,16], "A"."INDI_ID"[NUMBER,22], "A"."NAME"[VARCHAR2,20],
       "A"."IDCARD"[VARCHAR2,25], "A"."IN_DAYS"[NUMBER,22], "A"."FIN_DISEASE"[VARCHAR2,20],
       "A"."FIN_DATE"[DATE,7], "D"."HOSPITAL_NAME"[VARCHAR2,70]
   6 - "A"."CENTER_ID"[VARCHAR2,10], "F"."BIZ_NAME"[VARCHAR2,20], "A"."HOSPITAL_ID"[VARCHAR2,20],
       "A"."SERIAL_NO"[VARCHAR2,16], "A"."INDI_ID"[NUMBER,22], "A"."NAME"[VARCHAR2,20],
       "A"."IDCARD"[VARCHAR2,25], "A"."IN_DAYS"[NUMBER,22], "A"."FIN_DISEASE"[VARCHAR2,20],
       "A"."FIN_DATE"[DATE,7], "D"."HOSPITAL_NAME"[VARCHAR2,70]
   7 - "A"."CENTER_ID"[VARCHAR2,10], "F"."BIZ_NAME"[VARCHAR2,20], "A"."HOSPITAL_ID"[VARCHAR2,20],
       "A"."SERIAL_NO"[VARCHAR2,16], "A"."INDI_ID"[NUMBER,22], "A"."NAME"[VARCHAR2,20],
       "A"."IDCARD"[VARCHAR2,25], "A"."IN_DAYS"[NUMBER,22], "A"."FIN_DISEASE"[VARCHAR2,20],
       "A"."FIN_DATE"[DATE,7], "D"."HOSPITAL_NAME"[VARCHAR2,70], "D"."HOSP_LEVEL"[CHARACTER,1]
   8 - "A"."CENTER_ID"[VARCHAR2,10], "F"."BIZ_NAME"[VARCHAR2,20], "A"."HOSPITAL_ID"[VARCHAR2,20],
       "A"."SERIAL_NO"[VARCHAR2,16], "A"."INDI_ID"[NUMBER,22], "A"."NAME"[VARCHAR2,20],
       "A"."IDCARD"[VARCHAR2,25], "A"."IN_DAYS"[NUMBER,22], "A"."FIN_DISEASE"[VARCHAR2,20],
       "A"."FIN_DATE"[DATE,7]
   9 - (#keys=2) "A"."CENTER_ID"[VARCHAR2,10], "F"."BIZ_NAME"[VARCHAR2,20],
       "A"."HOSPITAL_ID"[VARCHAR2,20], "A"."SERIAL_NO"[VARCHAR2,16], "A"."INDI_ID"[NUMBER,22],
       "A"."NAME"[VARCHAR2,20], "A"."IDCARD"[VARCHAR2,25], "A"."CORP_ID"[NUMBER,22],
       "A"."IN_DAYS"[NUMBER,22], "A"."FIN_DISEASE"[VARCHAR2,20], "A"."FIN_DATE"[DATE,7]
  10 - "F"."CENTER_ID"[VARCHAR2,10], "F"."BIZ_TYPE"[CHARACTER,2], "F"."BIZ_NAME"[VARCHAR2,20]
  11 - "A"."HOSPITAL_ID"[VARCHAR2,20], "A"."SERIAL_NO"[VARCHAR2,16], "A"."BIZ_TYPE"[VARCHAR2,2],
       "A"."INDI_ID"[NUMBER,22], "A"."NAME"[VARCHAR2,20], "A"."IDCARD"[VARCHAR2,25],
       "A"."CORP_ID"[NUMBER,22], "A"."IN_DAYS"[NUMBER,22], "A"."FIN_DISEASE"[VARCHAR2,20],
       "A"."FIN_DATE"[DATE,7], "A"."CENTER_ID"[VARCHAR2,10]
  12 - "A"."HOSPITAL_ID"[VARCHAR2,20], "A"."SERIAL_NO"[VARCHAR2,16], "A"."BIZ_TYPE"[VARCHAR2,2],
       "A"."INDI_ID"[NUMBER,22], "A"."NAME"[VARCHAR2,20], "A"."PERS_TYPE"[VARCHAR2,3],
       "A"."IDCARD"[VARCHAR2,25], "A"."CORP_ID"[NUMBER,22], "A"."IN_DAYS"[NUMBER,22],
       "A"."FIN_DISEASE"[VARCHAR2,20], "A"."FIN_DATE"[DATE,7], "A"."CENTER_ID"[VARCHAR2,10]
  13 - "A".ROWID[ROWID,10], "A"."IDCARD"[VARCHAR2,25], "A"."PERS_TYPE"[VARCHAR2,3],
       "A"."BIZ_TYPE"[VARCHAR2,2]
  15 - "D"."HOSPITAL_NAME"[VARCHAR2,70], "D"."HOSP_LEVEL"[CHARACTER,1]
  16 - "D".ROWID[ROWID,10]
  20 - "B".ROWID[ROWID,10]

Note
-----
   - SQL profile "SYS_SQLPROF_0151ed60f3d28000" used for this statement


163 rows selected.

從SQL profile "SYS_SQLPROF_0151ed60f3d28000" used for this statement 這個資訊就是知道已經使用了SQL概要檔案
現在語句執行只要0.1毫秒

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/8494287/viewspace-1349468/,如需轉載,請註明出處,否則將追究法律責任。

相關文章