ORACLE概要文件--sqlprofile(一） - 数据库编程

----------------------------------------------------------------------------------------------------------------- GENERAL INFORMATION SECTION ------------------------------------------------------------------------------- Tuning Task Name : 任务_309 Tuning Task Owner : EASY Workload Type : Single SQL Statement Scope : COMPREHENSIVE Time Limit(seconds): 1800 Completion Status : COMPLETED Started at : 01/06/2014 23:36:02 Completed at : 01/06/2014 23:36:02 ------------------------------------------------------------------------------- Schema Name: EASY SQL ID : 31hpx02tsna89 SQL Text : select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' AND T1.object_id=t2.object_id ------------------------------------------------------------------------------- FINDINGS SECTION (2 findings) ------------------------------------------------------------------------------- 1- SQL Profile Finding (see explain plans section below) -------------------------------------------------------- 为此语句找到了性能更好的执行计划。 Recommendation (estimated benefit: 76.77%) ------------------------------------------ - 考虑接受推荐的 SQL 概要文件。 execute dbms_sqltune.accept_sql_profile(task_name => '任务_309', task_owner => 'EASY', replace => TRUE); Validation results ------------------ 已对 SQL profile 进行测试, 方法为执行其计划和原始计划并测量与计划相对应的执行统计信息。如果其中一个计划运行在很短的时间内就完成, 则另一计划可能只执行了一部分。 Original Plan With SQL Profile % Improved ------------- ---------------- ---------- Completion Status: COMPLETE COMPLETE Elapsed Time (s): .022829 .013985 38.74 % CPU Time (s): .022696 .013897 38.76 % User I/O Time (s): 0 0 Buffer Gets: 1330 308 76.84 % Physical Read Requests: 0 0 Physical Write Requests: 0 0 Physical Read Bytes: 0 0 Physical Write Bytes: 0 0 Rows Processed: 36 36 Fetches: 36 36 Executions: 1 1 Notes ----- 1. the original plan 的统计信息是 10 执行的平均值。 2. the SQL profile plan 的统计信息是 10 执行的平均值。 2- Alternative Plan Finding --------------------------- 通过搜索系统的实时和历史性能数据找到了此语句的某些替代执行计划。 The following table lists these plans ranked by their average elapsed time. See section "ALTERNATIVE PLANS SECTION" for detailed information on each plan. id plan hash last seen elapsed (s) origin note -- ---------- -------------------- ------------ --------------- ---------------- 1 1022743391 2014-01-06/22:56:07 0.022 Cursor Cache Information ----------- - 因为找不到原始计划的任何执行历史记录, SQL 优化指导无法确定这些执行计划中是否有一些执行计划优于原始计划。但是, 如果您知道某个替代计划优于原始计划, 可以为该替代计划创建 SQL 计划基线。这将指示

Oracle 优化程序在将来优先于任何其他选择来选取它。 execute dbms_sqltune.create_sql_plan_baseline(task_name => '任务_309', owner_name => 'EASY', plan_hash_value => xxxxxxxx); ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original With Adjusted Cost ------------------------------ Plan hash value: 1838229974 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 36 |

ORACLE概要文件--sqlprofile(一）(二)