一、建表（如果不在管理员用户下面实验，则grant advisor to username）
SQL> create table bigtab as select rownum as id,a.* from sys.all_objects a ;
Table created.
SQL> create table smalltab as select rownum as id,a.* from sys.all_objects a ;
Table created.
二插入数据，多执行几次
SQL> insert into bigtab select rownum as id,a.* from sys.all_objects a ;
SQL> insert into smalltab select rownum as id,a.* from sys.all_objects a ;
SQL> commit;
Commit complete
SQL> select count(*) from bigtab a,smalltab b where a.object_name = b.object_name;
COUNT(*)
----------
13749500
Elapsed: 00:00:01.46
Execution Plan
----------------------------------------------------------
Plan hash value: 3089226980
--------------------------------------------------------------------------------
--------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Ti
me |
--------------------------------------------------------------------------------
--------
| 0 | SELECT STATEMENT | | 1 | 34 | | 8829 (5)| 00
:01:46 |
| 1 | SORT AGGREGATE | | 1 | 34 | | |
|
|* 2 | HASH JOIN | | 130M| 4236M| 21M| 8829 (5)| 00
:01:46 |
| 3 | TABLE ACCESS FULL| SMALLTAB | 778K| 12M| | 3020 (1)| 00
:00:37 |
| 4 | TABLE ACCESS FULL| BIGTAB | 954K| 15M| | 3021 (1)| 00
:00:37 |
--------------------------------------------------------------------------------
--------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OBJECT_NAME"="B"."OBJECT_NAME")
Note
-----
- dynamic sampling used for this statement (level=2)
- SQL plan baseline "SQL_PLAN_8sj3kccbpqjru8a567df7" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
29301 consistent gets
13942 physical reads
0 redo size
424 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
三、创建优化任务
SQL> declare
2 my_task_name varchar2(30);
3 my_sqltext clob;
4 begin
5 my_sqltext := 'select count(*) from bigtab a ,smalltab b where a.object_name = b.object_name';
6 my_task_name := dbms_sqltune.create_tuning_task(sql_text => my_sqltext,
7
8 user_name => 'JERRY',
9
10 scope => 'COMPREHENSIVE',
11 time_limit => 60,
12 task_name => 'tuning_sql_test',
13 description => 'Task to tune a query on a specified table');
14 dbms_sqltune.execute_tuning_task(task_name => 'tuning_sql_test');
15 end;
16 /
PL/SQL procedure successfully completed.
在函数 CREATE_TUNING_TASK，sql_text 是需要优化的语句，user_name 是该语句通过哪个用户执行， scope 是优化范围（limited 或 comprehensive）， time_limit
优化过程的时间限制，task_name 优化任务名称，description 优化任务描述。
四、执行优化任务
SQL> exec dbms_sqltune.execute_tuning_task('tuning_sql_test');
PL/SQL procedure successfully completed.
五、查看优化结果
SQL> set long 999999
SQL> set serveroutput on size 9999999
SP2-0547: size option 9999999 out of range (2000 through 1000000)
SQL> set serveroutput on size 999999
SQL> set linesize 200
SQL> select dbms_sqltune.report_tuning_task('tuning_sql_test') from dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : tuning_sql_test
Tuning Task Owner : JERRY
Workload Type : Single SQL Statement
Execution Count : 4
Current Execution : EXEC_1811
Execution Type : TUNE SQL
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
Started at : 01/05/2014 18:26:00
Completed at : 01/05/2014 18:26:11
-------------------------------------------------------------------------------
Schema Name: JERRY
SQL ID : bnrdcq5xawgcn
SQL Text : select count(*) from bigtab a ,smalltab b where a.object_name =
b.object_name
-------------------------------------------------------------------------------
FINDINGS SECTION (3 findings)
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Table "JERRY"."SMALLTAB" was not analyzed.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'JERRY', tabname =>
'SMALLTAB', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
Rationale
---------
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.
2- Statistics Finding
---------------------
Table "JERRY"."BIGTAB" was not analyzed.
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'JERRY', tabname =>
'BIGTAB', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
Rationale
---------
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
3- 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: 99.7%)
-----------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index JERRY.IDX$$_05FD0001 on JERRY.SMALLTAB("OBJECT_NAME");
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index JERRY.IDX$$_05FD0002 on JERRY.BIGTAB("OBJECT_NAME");
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
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
account index maintenance overhead and additional space consumption.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 3089226980
----------------------------------------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 34 | | 8829 (5)| 00:01:46 |
| 1 | SORT AGGREGATE | | 1 | 34 | | |
|
|* 2 | HASH JOIN | | 130M| 4236M| 21M| 8829 (5)| 00:01:46 |
| 3 | TABLE ACCESS FULL| SMALLTAB | 778K| 12M| | 3020 (1)| 00:00:37 |
| 4 | TABLE ACCESS FULL| BIGTAB | 954K| 15M| | 3021 (1)| 00:00:37 |
----------------------------------------------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OBJECT_NAME"="B"."OBJECT_NAME")
2- Using New Indices
--------------------
Plan hash value: 2665054235
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
|
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 34 | 26 (97)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 34 | | |
| 2 | NESTED LOOPS | | 130M| 4236M| 26 (97)| 00:00:01 |
| 3 | INDEX FULL SCAN | IDX$$_05FD0001 | 778K| 12M| 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN| IDX$$_05FD0002 | 168 | 2856 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------
---
Predicate Information (identified by operation id):
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
---------------------------------------------------
4 - access("A"."OBJECT_NAME"="B"."OBJECT_NAME")
-------------------------------------------------------------------------------
Elapsed: 00:00:00.06
由上看出，SQLTUNE建议分析表以及建索引，并把索引建立前后的执行效率进行了比较。
六、删除优化任务
SQL> exec dbms_sqltune.drop_tuning_task('tuning_sql_test');
PL/SQL procedure successfully completed.
