1.首先收集表的统计信息。

SQL> exec dbms_stats.gather_table_stats(user,'EMP',cascade=>true);

PL/SQL procedure successfully completed.

2.插入大量数据，并更新表里的数据，使val列都是唯一值，造成统计信息过旧。

SQL> insert into emp select * from emp;

16 rows created.

SQL> /

32 rows created.

SQL> /

64 rows created.

SQL> /

128 rows created.

SQL>
SQL> /

256 rows created.

SQL> /

512 rows created.

SQL> /

1024 rows created.

SQL> /

2048 rows created.

SQL> /

4096 rows created.

SQL> /

8192 rows created.

SQL> /

16384 rows created.

SQL> /

32768 rows created.

SQL> /

65536 rows created.

SQL> /

131072 rows created.

SQL> /

262144 rows created.

SQL> /

524288 rows created.

SQL> commit;

SQL> update emp set val=rownum;

1048576 rows updated.

3.使用advisor来对sql进行tuning

SQL> exec dbms_sqltune.drop_tuning_task ('my_sql_tuning_task');

PL/SQL procedure successfully completed.

SQL> DECLARE
  2   my_task_name VARCHAR2(30);
  3   my_sqltext   CLOB;
  4  BEGIN
  5   my_sqltext := 'select * from emp where val=100';
  6 
  7   my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
  8           sql_text    => my_sqltext,
  9           user_name   => 'SYS',
 10           scope       => 'COMPREHENSIVE',
 11           time_limit  => 60,
 12           task_name   => 'my_sql_tuning_task',
 13           description => 'Task to tune a query on a specified employee');
 14  END;
 15  /

PL/SQL procedure successfully completed.

SQL> BEGIN
  2    DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task' );
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'my_sql_tuning_task';

STATUS
-----------
COMPLETED

SQL> SET LONG 1000
SQL> SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task')
  FROM DUAL;SQL> SQL>   2 

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : my_sql_tuning_task
Tuning Task Owner                 : SYS
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 60
Completion Status                 : COMPLETED
Started at                        : 07/19/2010 04:53:13
Completed at                      : 07/19/2010 04:53:18
Number of Statistic Findings      : 1

-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID     : f6d0q07sc3d80
SQL Text   : select * from emp where val=100

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

1- Statistics Finding
---------------------
  Optimizer statistics for table "SYS"."EMP" and its indices are stale.

  Recommendation
  --------------

SQL Tuning Advisor 已经发现统计信息是陈旧的，需要重新收集统计信息，但由于val上没有索引，即使重新收集统计信息，还是没有效果的。

SQL> exec dbms_stats.gather_table_stats(user,'EMP',cascade=>true);

PL/SQL procedure successfully completed.

SQL> select * from emp where val=100;

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    19 |   491   (4)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    19 |   491   (4)| 00:00:05 |
--------------------------------------------------------------------------

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

   1 - filter("VAL"=100)

再使用SQL Tuning Advisor来对sql进行分析：

SQL> exec dbms_sqltune.drop_tuning_task ('my_sql_tuning_task');

PL/SQL procedure successfully completed.

SQL> DECLARE
  2   my_task_name VARCHAR2(30);
  3   my_sqltext   CLOB;
  4  BEGIN
  5   my_sqltext := 'select * from emp where val=100';
  6 
  7   my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
  8           sql_text    => my_sqltext,
  9           user_name   => 'SYS',
 10           scope       => 'COMPREHENSIVE',
 11           time_limit  => 60,
 12           task_name   => 'my_sql_tuning_task',
 13           description => 'Task to tune a query on a specified employee');
 14  END;
 15  /

PL/SQL procedure successfully completed.

SQL> BEGIN
  2    DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task' );
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'my_sql_tuning_task';

STATUS
-----------
COMPLETED

SQL> SET LONG 1000
SQL> SET LONGCHUNKSIZE 1000
SQL> SET LINESIZE 100
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task')
  2    FROM DUAL;

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : my_sql_tuning_task
Tuning Task Owner  : SYS
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 07/19/2010 04:56:36
Completed at       : 07/19/2010 04:56:36

-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID     : f6d0q07sc3d80
SQL Text   : select * from emp where val=100

-------------------------------------------------------------------------------
There are no recommendations to improve the statement.

-------------------------------------------------------------------------------
这一次，SQL Tuning Advisor居然没有给出任何建议，虽然我们知道在val上创建索引肯定是有效的。

SQL> create index idx_emp on emp(val) tablespace idx_2k;

Index created.

SQL> set autot traceonly explain
SQL> select * from emp where val=100;

Execution Plan
----------------------------------------------------------
Plan hash value: 306890541

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |    19 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP     |     1 |    19 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_EMP |     1 |       |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   2 - access("VAL"=100)