????????????????????????
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")
-------------------------------------------------------------------------------