oracle11g自動調優
--:自動調優計劃
begin
dbms_workload_repository.create_snapshot();
end;
select /*+ result_cache */ count(*) from (select * from HJ.dbtan where object_type='TABLE' ORDER BY 1 DESC ,2 ASC ,3 DESC ,4 ASC);
begin
dbms_workload_repository.create_snapshot();
end;
--ft8s1pfmz9ph0
SET SERVEROUTPUT ON
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
begin_snap => 21,
end_snap => 22,
sql_id => 'ft8s1pfmz9ph0',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 300,
task_name => 'ft8s1pfmz9ph0d_AWR_tuning_task',
description => 'Tuning task for statement f3hc7r4trnn1d in AWR.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
begin DBMS_SQLTUNE.execute_tuning_task(task_name => 'ft8s1pfmz9ph0d_AWR_tuning_task');end;
SELECT DBMS_SQLTUNE.report_tuning_task('ft8s1pfmz9ph0d_AWR_tuning_task') AS recommendations FROM dual;
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : ft8s1pfmz9ph0d_AWR_tuning_task
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 300
Completion Status : COMPLETED
Started at : 09/01/2014 14:55:32
Completed at : 09/01/2014 14:55:44
-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID : ft8s1pfmz9ph0
SQL Text : select /*+ result_cache */ count(*) from (select * from
HJ.dbtan where object_type='TABLE' ORDER BY 1 DESC ,2 ASC ,3
DESC ,4 ASC)
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
尚未分析表 "HJ"."DBTAN"。
Recommendation
--------------
- 考慮收集此表的優化程序統計信息。
execute dbms_stats.gather_table_stats(ownname => 'HJ', tabname =>
'DBTAN', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
Rationale
---------
為了選擇好的執行計劃, 優化程序需要此表的最新統計信息。
2- Index Finding (see explain plans section below)
--------------------------------------------------
通過創建一個或多個索引可以改進此語句的執行計劃。
Recommendation (estimated benefit: 95.98%)
------------------------------------------
- 考慮運行可以改進物理方案設計的訪問指導或者創建推薦的索引。
create index HJ.IDX$$_00360001 on HJ.DBTAN("OBJECT_TYPE");
Rationale
---------
創建推薦的索引可以顯著地改進此語句的執行計劃。但是, 使用典型的 SQL 工作量運行 "訪問指導"
可能比單個語句更可取。通過這種方法可以獲得全面的索引建議案, 包括計算索引維護的開銷和附加的空間消耗。
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 1782547706
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 4706 (1)| 00:00:57 |
| 1 | RESULT CACHE | 81z3k6zbauk9s2c83c03s270ja | | | | |
| 2 | SORT AGGREGATE | | 1 | 11 | | |
|* 3 | TABLE ACCESS FULL| DBTAN | 40006 | 429K| 4706 (1)| 00:00:57 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("OBJECT_TYPE"='TABLE')
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(HJ.DBTAN); attributes=(single-row); parameters=(nls); name="select /*+ result_cache */ count(*) from (select * from HJ.dbtan where object_type='TABLE' ORDER BY 1 DESC ,2 ASC ,3 DESC ,4 AS
2- Using New Indices
--------------------
Plan hash value: 2810514733
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 189 (1)| 00:00:03 |
| 1 | RESULT CACHE | 27yjysxpdun18b2utun82bynny | | | | |
| 2 | SORT AGGREGATE | | 1 | 11 | | |
|* 3 | INDEX RANGE SCAN| IDX$$_00360001 | 40006 | 429K| 189 (1)| 00:00:03 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_TYPE"='TABLE')
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(HJ.DBTAN); attributes=(single-row, ordered); parameters=(nls); name="select /*+ result_cache */ count(*) from (select * from HJ.dbtan where object_type='TABLE' ORDER BY 1 DESC ,2 ASC ,3 DESC ,4 AS"
-------------------------------------------------------------------------------
begin DBMS_SQLTUNE.drop_tuning_task (task_name => 'ft8s1pfmz9ph0d_AWR_tuning_task');end;
