1.创建测试表，并收集统计信息

sys@ORCL>create table big_table as select rownum as id,t.* from all_objects t;

Table created.

sys@ORCL>create index idx_big_table_id on big_table(id);

Index created.

sys@ORCL>exec dbms_stats.gather_table_stats(user,'big_table')

PL/SQL procedure successfully completed.

 

2.执行查询测试

sys@ORCL>set linesize 100

sys@ORCL>set autotrace trace exp

sys@ORCL>select count(*) from big_table where id<100;

Execution Plan

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

Plan hash value: 2068851779

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

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

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

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

|   1 |  SORT AGGREGATE   |                  |     1 |     5 |            |          |

|*  2 |   INDEX RANGE SCAN| IDX_BIG_TABLE_ID |    99 |   495 |     2   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - access("ID"<100)

 

3.强行加入full hints使其变为全表扫描

sys@ORCL>select /*+full(t)*/ count(*) from big_table t where id<100;

Execution Plan

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

Plan hash value: 599409829

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

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

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

|   0 | SELECT STATEMENT   |           |     1 |     5 |   292   (1)| 00:00:04 |

|   1 |  SORT AGGREGATE    |           |     1 |     5 |            |          |

|*  2 |   TABLE ACCESS FULL| BIG_TABLE |    99 |   495 |   292   (1)| 00:00:04 |

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

Predicate Information (identified by operation id):

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

   2 - filter("ID"<100)

 

4.调用STA调优顾问

sys@ORCL>set autotrace off

 

declare

   my_task_name varchar2(30);

    my_sqltext clob;

begin

   my_sqltext:=' select /*+full(t)*/ count(*) from big_table t where id<100';   

    my_task_name:=DBMS_SQLTUNE.create_tuning_task(

                   sql_text => my_sqltext,

                    user_name => 'SYS',

                   scope => 'COMPREHENSIVE',

                    time_limit => 60,

                    task_name => 'tuning_sql_bigtable',

                    description => 'Task to tune a query on a specified table');

 end;

 /

PL/SQL procedure successfully completed

 

sys@ORCL> exec DBMS_SQLTUNE.execute_tuning_task('tuning_sql_bigtable');

PL/SQL procedure successfully completed

 

sys@ORCL>select task_name,advisor_name,status   from user_advisor_tasks;

TASK_NAME                      ADVISOR_NAME                   STATUS

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

SYS_AUTO_SQL_TUNING_TASK       SQL Tuning Advisor             COMPLETED

TASK_36                        SQL Tuning Advisor             COMPLETED

tuning_sql_bigtable            SQL Tuning Advisor             COMPLETED

 

 set long 999999

 set LONGCHUNKSIZE 999999

 set serveroutput on size 999999

 set linesize 200

sys@ORCL> select dbms_sqltune.report_tuning_task('tuning_sql_bigtable') from dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_BIGTABLE')

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

GENERAL INFORMATION SECTION

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

Tuning Task Name   : tuning_sql_bigtable

Tuning Task Owner  : SYS

Workload Type      : Single SQL Statement

Scope              : COMPREHENSIVE

Time Limit(seconds): 60

Completion Status  : COMPLETED

Started at         : 06/26/2016 09:34:54

Completed at       : 06/26/2016 09:34:54

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

Schema Name: SYS

SQL ID     : bbww055upfvbs

SQL Text   :  select /*+full(t)*/ count(*) from big_table t where id<100

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

FINDINGS SECTION (1 finding)

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

1- SQL Profile Finding (see explain plans section below)

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

  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 99.89%)

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

  - Consider accepting the recommended SQL profile.

    execute dbms_sqltune.accept_sql_profile(task_name =>

            'tuning_sql_bigtable', task_owner => 'SYS', replace => TRUE);

  Validation results

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

  The SQL profile was tested by executing both its plan and the original plan

  and measuring their respective execution statistics. A plan may have been

  only partially executed if the other could be run to completion in less time.

                           Original Plan  With SQL Profile  % Improved

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

  Completion Status:            COMPLETE          COMPLETE

  Elapsed Time(us):                6030                45      99.25 %

  CPU Time(us):                    5799                 0        100 %

  User I/O Time(us):                  0                 0

  Buffer Gets:                      965                 1      99.89 %

  Physical Read Requests:             0                 0

  Physical Write Requests:            0                 0

  Physical Read Bytes:                0                 0

  Physical Write Bytes:               0                 0

  Rows Processed:                     1                 1

  Fetches:                            1                 1

  Executions:                         1                 1

  Notes

  -----

  1. The original plan was first executed to warm the buffer cache.

  2. Statistics for original plan were averaged over next 9 executions.

  3. The SQL profile plan was first executed to warm the buffer cache.

  4. Statistics for the SQL profile plan were averaged over next 9 executions.

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

EXPLAIN PLANS SECTION

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

1- Original With Adjusted Cost

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

Plan hash value: 599409829

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

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

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

|   0 | SELECT STATEMENT   |           |     1 |     5 |   292   (1)| 00:00:04 |

|   1 |  SORT AGGREGATE    |           |     1 |     5 |            |          |

|*  2 |   TABLE ACCESS FULL| BIG_TABLE |    99 |   495 |   292   (1)| 00:00:04 |

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

 

Predicate Information (identified by operation id):

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

   2 - filter("ID"<100)

2- Using SQL Profile

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

Plan hash value: 2068851779

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

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

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

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

|   1 |  SORT AGGREGATE   |                  |     1 |     5 |            |          |

|*  2 |   INDEX RANGE SCAN| IDX_BIG_TABLE_ID |    99 |   495 |     2   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

   2 - access("ID"<100)

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

1 row selected.

 

5.接受STA建议的SQL Profile

sys@ORCL>execute dbms_sqltune.accept_sql_profile(task_name => 'tuning_sql_bigtable', task_owner => 'SYS', replace => TRUE);

PL/SQL procedure successfully completed.

 

sys@ORCL>set autot trace exp

sys@ORCL>select /*+full(t)*/ count(*) from big_table t where id<100;

Execution Plan

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

Plan hash value: 2068851779

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

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

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

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

|   1 |  SORT AGGREGATE   |                  |     1 |     5 |            |          |

|*  2 |   INDEX RANGE SCAN| IDX_BIG_TABLE_ID |    99 |   495 |     2   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - access("ID"<100)

Note

-----

   - SQL profile "SYS_SQLPROF_01558a6ecfde0000" used for this statement

 

    所以，当无法改变应用程序的sql语句时，可以利用sql profiles来影响执行计划。

 

6.更改sql profiles名字

----先删除之前的sql profile

sys@ORCL>set auto off

sys@ORCL>execute dbms_sqltune.alter_sql_profile(name => 'SYS_SQLPROF_01558a6ecfble', task_owner => 'SYS', replace => TRUE);de0000', attribute_name => 'STATUS', value => 'DISABLED');

PL/SQL procedure successfully completed.

 

sys@ORCL>execute dbms_sqltune.drop_sql_profile(name => 'SYS_SQLPROF_01558a6ecfde0000');

PL/SQL procedure successfully completed.

 

sys@ORCL>set autot trace exp

sys@ORCL>select /*+full(t)*/ count(*) from big_table t where id<100;

Execution Plan

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

Plan hash value: 599409829

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

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

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

|   0 | SELECT STATEMENT   |           |     1 |     5 |   292   (1)| 00:00:04 |

|   1 |  SORT AGGREGATE    |           |     1 |     5 |            |          |

|*  2 |   TABLE ACCESS FULL| BIG_TABLE |    99 |   495 |   292   (1)| 00:00:04 |

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

Predicate Information (identified by operation id):

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

   2 - filter("ID"<100)

 

sys@ORCL>set auto off

 

sys@ORCL>execute dbms_sqltune.accept_sql_profile(task_name => 'tuning_sql_bigtable', task_owner => 'SYS', replace => TRUE, name => 'sys_sqlpro_big_table');

PL/SQL procedure successfully completed.

 

sys@ORCL>set autot trace exp

sys@ORCL>select /*+full(t)*/ count(*) from big_table t where id<100;

Execution Plan

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

Plan hash value: 2068851779

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

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

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

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

|   1 |  SORT AGGREGATE   |                  |     1 |     5 |            |          |

|*  2 |   INDEX RANGE SCAN| IDX_BIG_TABLE_ID |    99 |   495 |     2   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - access("ID"<100)

Note

-----

   - SQL profile "sys_sqlpro_big_table" used for this statement

 

 

 -----------------------reference                            DBA手记2