SQL自动调优

wtuyss

　　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>　　Number of SQLs with SQL profiles recommended : 2
　　-------------------------------------------------------------------------------

　　SQLs with Findings Ordered by Maximum (Profile/Index) Benefit, Object>　　-------------------------------------------------------------------------------

　　object>　　---------- ------------- ---------- ---------------- -------------- -----------
　　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>　　-------------------------------------------------------------------------------

　　Object>　　Schema Name: SYS

　　SQL>　　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>　　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>　　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
　　--------------------------------------------------------------------------------
　　---------------------------------------------

　　|>　　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