本文共 5779 字，大约阅读时间需要 19 分钟。
SQL自动调优，是oracle 自带的调优工具，可以提出一些解决方案。
本次我主要介绍下面这些自动SQL调优工具：
自动SQL调优（automatic sql tuning)
SQL调优工具集（SQL tuning sets,STS)
SQL调优顾问（SQL Tuning Advisor）
自动数据库诊断监视器（addm）
显示SQL自动调优建议最快的方法：
SQL> select dbms_auto_sqltune.report_auto_tuning_task from dual;
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : SYS_AUTO_SQL_TUNING_TASK
Tuning Task Owner : SYS
Workload Type : Automatic High-Load SQL Workload
Execution Count : 2
Current Execution : EXEC_41
Execution Type : TUNE SQL
Scope : COMPREHENSIVE
Global Time Limit(seconds) : 3600
Per-SQL Time Limit(seconds) : 1200
Completion Status : COMPLETED
Started at : 09/13/2015 23:19:10
Completed at : 09/13/2015 23:20:50
Number of Candidate SQLs : 2
Cumulative Elapsed Time of SQL (s) : 56
-------------------------------------------------------------------------------
SUMMARY SECTION
-------------------------------------------------------------------------------
Global SQL Tuning Result Statistics
-------------------------------------------------------------------------------
Number of SQLs Analyzed : 2
Number of SQLs in the Report : 2
Number of SQLs with Findings : 2
Number of SQLs with Statistic Findings : 2
Number of SQLs with Alternative Plan Findings: 1
Number of SQLs with SQL profiles recommended : 2
-------------------------------------------------------------------------------
SQLs with Findings Ordered by Maximum (Profile/Index) Benefit, Object ID
-------------------------------------------------------------------------------
object ID SQL ID statistics profile(benefit) index(benefit) restructure
---------- ------------- ---------- ---------------- -------------- -----------
3 5jvf84zg4c49n 2 94.73%
4 fa16465c7pqmd 1 93.52%
-------------------------------------------------------------------------------
Objects with Missing/Stale Statistics (ordered by schema, object, type)
-------------------------------------------------------------------------------
Schema Name Object Name Type State Cascade
---------------------------- ---------------------------- ----- ------- -------
SYS IND$ TABLE STALE NO
USER$ TABLE STALE NO
-------------------------------------------------------------------------------
DETAILS SECTION
-------------------------------------------------------------------------------
Statements with Results Ordered by Maximum (Profile/Index) Benefit, Object ID
-------------------------------------------------------------------------------
Object ID : 3
Schema Name: SYS
SQL ID : 5jvf84zg4c49n
SQL Text : select s.synonym_name as object_name, o.object_type
from sys.all_synonyms s, sys.all_objects o
where s.owner in ('PUBLIC', :schema)
and o.owner = s.table_owner
and o.object_name = s.table_name
and o.object_type in ('TABLE', 'VIEW', 'PACKAGE','TYPE',
'PROCEDURE', 'FUNCTION', 'SEQUENCE')
-------------------------------------------------------------------------------
FINDINGS SECTION (3 findings)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Optimizer statistics for table "SYS"."IND$" and its indices are stale.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname =>
'IND$', 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.
2- Statistics Finding
---------------------
Optimizer statistics for table "SYS"."USER$" and its indices are stale.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname =>
'USER$', 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.
3- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
The SQL profile was not automatically created because its benefit could not
be verified.
Recommendation (estimated benefit: 94.73%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name =>
'SYS_AUTO_SQL_TUNING_TASK', object_id => 3, replace => TRUE);
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
-------------------------------------------------------------------------------
Error: cannot fetch explain plan for object: 3
-------------------------------------------------------------------------------
2- Original With Adjusted Cost
------------------------------
Plan hash value: 1687783800
--------------------------------------------------------------------------------
---------------------------------------------
| Id | Operation | Name | Ro
ws | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------
---------------------------------------------
| 0 | SELECT STATEMENT | |
4 | 464 | | 4999 (1)| 00:01:00 |
|* 1 | TABLE ACCESS BY INDEX ROWID | SUM$ |
1 | 6 | | 0 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | I_SUM$_1 |
1 | | | 0 (0)| 00:00:01 |
|* 3 | HASH JOIN | |
4 | 464 | | 4999 (1)| 00:01:00 |
| 4 | JOIN FILTER CREATE | :BF0000 | 3