SQL优化器SQL Tuning Advisor （STA)，是oracle的sql优化补助工具。属于DBMS_SQLTUNE包，它的主要作用是对于sql使用到的表创建正确的索引。使用STA的条件是优化器模式为CBO模式，
查询优化器模式:
show parameter OPTIMIZER_MODE
all_rows /CBO，sql所有返回行都采用基于成本的方式运行/
执行DBMS_SQLTUNE包进行sql优化需要有advisor的权限：
grant advisor to scott;
我是在sys用户下进行测试，所以不需要赋权
过程：
1.首先创建两个练习表obj与ind，仅创建表，无需创建索引：
SQL> create table obj as select * from dba_objects;
表已创建。
SQL> create table ind as select * from dba_indexes;
表已创建。
SQL> insert into obj select * from obj;
86426 rows created.
SQL> insert into ind select * from ind;
4936 rows created.
2.然后对这两个表，obj与ind进行联合查询，并通过autotrace查看其执行计划：
SQL> set timing on
SQL> set autot trace
SQL> select count(*) from obj o, ind i where o.object_name=i.index_name;
通过执行计划可以看到两张表联合查询时走的全表扫描和HASH JOIN
现在使用STA优化：
第一步：创建优化任务
通过调用函数DBMS_SQLTUNE.CREATE_TUNING_TASK来创建优化任务.
SQL> set autot off
SQL> set timing offDECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'select count(*) from obj o, ind i where o.object_name=i.index_name';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
user_name => 'SYS',
scope => 'COMPREHENSIVE',
time_limit => 30,
task_name => 'tuning_sql_test',
description => 'tuning');
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'tuning_sql_test');
END;
/
报错ORA-13607 ORA-06512 之前对另外的表做过优化，用了同样的task_name
处理方法：删掉task_name=tuning_sql_test优化任务名称再执行存储过程
SQL>exec dbms_sqltune.drop_tuning_task('tuning_sql_test');
PL/SQL procedure successfully completed.
再执行优化任务存储过程：
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'select count(*) from obj o, ind i where o.object_name=i.index_name';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
user_name => 'SYS',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'tuning_sql_test',
description => 'tuning');
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'tuning_sql_test');
END;
/
sql_text是需要优化的语句，
user_name是该语句通过哪个用户执行，用户名大写，
scope是优化范围（limited或comprehensive），
time_limit优化过程的时间限制，
task_name优化任务名称，
description优化任务描述。
第二步： 执行优化任务
通过调用dbms_sqltune.execute_tuning_task过程来执行前面创建好的优化任务。
SQL> exec dbms_sqltune.execute_tuning_task('tuning_sql_test');
PL/SQL procedure successfully completed.
第三步：检查优化任务的状态
通过查看user_advisor_tasks/dba_advisor_tasks视图可以查看优化任务的当前状态。
SQL> SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name='tuning_sql_test';
第四步：查看优化结果
通过dbms_sqltune.report_tning_task函数可以获得优化任务的结果。
SQL>set long 999999
SQL>set serveroutput on size 999999
SQL>set line 120
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 : SYS
Workload Type : Single SQL Statement
Execution Count : 2
Current Execution : EXEC_795
Execution Type : TUNE SQL
Scope : COMPREHENSIVE
Time Limit(seconds): 60DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
---------------------------------------------------------------------
Completion Status : COMPLETED
Started at : 06/21/2024 16:37:41
Completed at : 06/21/2024 16:37:43----------------------------------------------------
---------------------------
Schema Name: SYS
SQL ID : 6wruu2mxyu8g3
SQL Text : select count(*) from obj o, ind i w
here
o.object_name=i.index_nameDBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
---------------------------------------------------------------------------------------------------------------------------
-------------------------
FINDINGS SECTION (2 findings)
--------------------------------------------
-----------------------------------1- Statistics Finding
---------------------
Table "SYS"."IND" was not analyzed.DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
---------------------------------------------------------------------
Recommendation
--------------
- Consider collecting optimizer statistics f
or this table.
execute dbms_stats.gather_table_stats(own
name => 'SYS', tabname => 'IND',
estimate_percent => DBMS_STATS.AUTO
_SAMPLE_SIZE, method_opt =>
'FOR ALL COLUMNS SIZE AUTO');Rationale
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
---------------------------------------------------------------------
---------
The optimizer requires up-to-date statist
ics for the table in order to
select a good execution plan.2- Statistics Finding
---------------------
Table "SYS"."OBJ" was not analyzed.Recommendation
--------------DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
---------------------------------------------------------------------
- Consider collecting optimizer statistics for this tabl
e.
execute dbms_stats.gather_table_stats(ownname => 'SYS
', tabname => 'OBJ',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE
, method_opt =>
'FOR ALL COLUMNS SIZE AUTO');Rationale
---------
The optimizer requires up-to-date statistics for theDBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
---------------------------------------------------------------------
table in order to
select a good execution plan.-----------------------------------------------
--------------------------------
EXPLAIN PLANS SECTION
---------------------------------------------
----------------------------------1- Original
-----------DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
---------------------------------------------------------------------
Plan hash value: 380737209----------------------------------------------------
------------------------
| Id | Operation | Name | Rows | Bytes | Co
st (%CPU)| Time |
----------------------------------------------------------
------------------
| 0 | SELECT STATEMENT | |
1 | 83 | 3023 (1)| 00:00:37 |
| 1 | SORT AGGREGATE | | 1 |DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
---------------------------------------------------------------------
83 | | |
|* 2 | HASH JOIN | | 76972 | 6
238K| 3023 (1)| 00:00:37 |
| 3 | TABLE ACCESS FULL| IND | 5735 | 97495
| 356 (0)| 00:00:05 |
| 4 | TABLE ACCESS FULL| OBJ | 76963 | 4960K|
2667 (1)| 00:00:33 |
--------------------------------------------------------
--------------------Predicate Information (identified by operation id):
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
---------------------------------------------------------------------
---------------------------------------------
------2 - access("O"."OBJECT_NAME"="I"."INDEX_NAME")
----------------------------------------
---------------------------------------
报告如上
仔细阅读此报告，给出了收集两张表obj与ind的统计信息的建议
(为什么没有给出其他的优化建议???????)
第五步：删除优化任务
通过调用dbms_sqltuen.drop_tuning_task可以删除已经存在的优化任务，可以释放资源。
SQL>exec dbms_sqltune.drop_tuning_task('tuning_sql_test');
PL/SQL procedure successfully completed.
