SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for 64-bit Windows: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

--创建测试表

SQL> create table test_sql_tuning(id_ number);

Table created.

SQL> declare

  2  begin

  3    for i in 1..100000 loop

  4      insert into test_sql_tuning values(i)

  5    end loop;

  6    commit;

  7  end;

  8  /

PL/SQL procedure successfully completed.

SQL> set autotrace on exp

SQL> select * from test_sql_tuning t where t.id_=333;

       ID_

----------

       333

Execution Plan

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

Plan hash value: 3855182387

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

| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |                 |     3 |    39 |    69   (2)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| TEST_SQL_TUNING |     3 |    39 |    69   (2)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - filter("T"."ID_"=333)

Note

-----

   - dynamic sampling used for this statement (level=2)

SQL> declare

  2  my_task_name varchar2(50);

  3  begin

  4    my_task_name := dbms_sqltune.create_tuning_task(

  5    SQL_ID => 'g72kdvcacxvtf',

  6    scope => 'COMPREHENSIVE',

  7    task_name => 'ZEN_TEST_SQLTUNE');

  8    DBMS_SQLTUNE.execute_tuning_task(my_task_name);

  9  end;

 10  /

PL/SQL procedure successfully completed.

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

DBMS_SQLTUNE.REPORT_TUNING_TASK('ZEN_TEST_SQLTUNE')

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

GENERAL INFORMATION SECTION

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

Tuning Task Name   : ZEN_TEST_SQLTUNE

Tuning Task Owner  : HR

Workload Type      : Single SQL Statement

Scope              : COMPREHENSIVE

Time Limit(seconds): 1800

Completion Status  : COMPLETED

Started at         : 07/02/2017 16:31:20

Completed at       : 07/02/2017 16:31:20

DBMS_SQLTUNE.REPORT_TUNING_TASK('ZEN_TEST_SQLTUNE')

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

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

Schema Name: HR

SQL ID     : cnvs1fb15pqb4

SQL Text   : select * from test_sql_tuning t where t.id_=333

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

FINDINGS SECTION (2 findings)

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

1- Statistics Finding

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

DBMS_SQLTUNE.REPORT_TUNING_TASK('ZEN_TEST_SQLTUNE')

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

  Table "HR"."TEST_SQL_TUNING" was not analyzed.

  Recommendation

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

  - Consider collecting optimizer statistics for this table.

    execute dbms_stats.gather_table_stats(ownname => 'HR', tabname =>

            'TEST_SQL_TUNING', estimate_percent =>

            DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE

            AUTO');

  Rationale

DBMS_SQLTUNE.REPORT_TUNING_TASK('ZEN_TEST_SQLTUNE')

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

  ---------

    The optimizer requires up-to-date statistics for the table in order to

    select a good execution plan.

2- Index Finding (see explain plans section below)

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

  The execution plan of this statement can be improved by creating one or more

  indices.

  Recommendation (estimated benefit: 98.55%)

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

DBMS_SQLTUNE.REPORT_TUNING_TASK('ZEN_TEST_SQLTUNE')

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

  - Consider running the Access Advisor to improve the physical schema design

    or creating the recommended index.

    create index HR.IDX$$_06650001 on HR.TEST_SQL_TUNING("ID_");

  Rationale

  ---------

    Creating the recommended indices significantly improves the execution plan

    of this statement. However, it might be preferable to run "Access Advisor"

    using a representative SQL workload as opposed to a single statement. This

    will allow to get comprehensive index recommendations which takes into

    account index maintenance overhead and additional space consumption.

DBMS_SQLTUNE.REPORT_TUNING_TASK('ZEN_TEST_SQLTUNE')

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

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

EXPLAIN PLANS SECTION

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

1- Original

-----------

Plan hash value: 3855182387

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

| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |

DBMS_SQLTUNE.REPORT_TUNING_TASK('ZEN_TEST_SQLTUNE')

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

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

|   0 | SELECT STATEMENT  |                 |     3 |    39 |    69   (2)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| TEST_SQL_TUNING |     3 |    39 |    69   (2)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - filter("T"."ID_"=333)

DBMS_SQLTUNE.REPORT_TUNING_TASK('ZEN_TEST_SQLTUNE')

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

2- Using New Indices

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

Plan hash value: 1603088386

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

| Id  | Operation        | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT |                |     1 |    13 |     1   (0)| 00:00:01 |

|*  1 |  INDEX RANGE SCAN| IDX$$_06650001 |     1 |    13 |     1   (0)| 00:00:01

|

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

DBMS_SQLTUNE.REPORT_TUNING_TASK('ZEN_TEST_SQLTUNE')

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

Predicate Information (identified by operation id):

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

   1 - access("T"."ID_"=333)

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

1 row selected.

Execution Plan

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

Plan hash value: 1388734953

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

| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |

|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |

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

SQL>