oracle 11g 自己主动调整

it2025-07-24 4

--:自己主动调教计划 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;

转载于:https://www.cnblogs.com/bhlsheji/p/4822109.html

相关资源：数据结构—成绩单生成器

最新回复(0)