????????????????????????
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.