Oracle优化器使用方法.docx

Toad 中使用Oracle 优化器在Toad中使用Oracle 优化器/前提：在优化之前，请对SQL中的表进行统计分析：1 创建调试任务，调试SQL在Editor中输入想要调试的SQL，可以是绑定了变量的服务，点击下拉框，出现Oracle Tuning Advisor，建议器会自动创建任务，不需要手工通过DBMS_SQL_TUNING包里面的存储过程创建2 分析结果，我们使用样例做分析，/*说明*/中是说明：GENERAL INFORMATION SECTION-Tuning Task Name : PC-2011043177740767.3916458681Tuning Task Owner : SING_OPENScope : COMPREHENSIVETime Limit(seconds) : 1800Completion Status : COMPLETEDStarted at : 08/12/2011 09:04:34Completed at : 08/12/2011 09:04:37Number of SQL Profile Findings : 1-Schema Name: SING_OPENSQL ID : 8j9c3zhgpdd0aSQL Text : select count(1) count_s from TTT_TRACK_ORDER a,twf_Task_order b,twf_task_order_rang c where a.seq=0 and b.curr_flag =0 and a.ord_no = b.ord_no and b.task_order_no = c.task_order_no and b.return_flag 3 and b.task_type = 02 and b.task_state = 0 and b.ord_type =11 and c.state = 0 and c.job_id = :JOB_ID and (c.staff_job_id = :STAFF_JOB_ID or c.staff_job_id is null) and b.activity_no = TTPC-FINDINGS SECTION (1 finding)-/*这个便是我们所要调试的SQL*/1- SQL Profile Finding (see explain plans section below)- A potentially better execution plan was found for this statement.Recommendation (estimated benefit: 23.07%)/*这个是优化器给出的建议，样例中优化器建议不修改sql语句，但是需要创建sql Profile给出这个sql的私有的执行计划，这个是一种情况，另外，优化器可能会给出加索引等优化方案，但是请注意，优化器给出的只是建议，所以在开发过程中，不是遇到建议就创建，因为在CBO中，Oracle会预先计算出每个表的数据量，再做分析，但是在实际生产过程中，表中的数据量都是测试环境无法模拟出来的。*/ - - Consider accepting the recommended SQL profile. execute dbms_sqltune.accept_sql_profile(task_name = PC-2011043177740767.3916458681, replace = TRUE);/*这里再给出优化器可能的另一种建议方式，就是索引的建立，下面给出的样例中，索引的创建对SQL性能的提升为100%：Recommendation (estimated benefit: 100%) - - Consider running the Access Advisor to improve the physical schema design or creating the recommended index. create index DEMO.IDX$_06C50001 on DEMO.SMALLTAB(TABLE_NAME); - Consider running the Access Advisor to improve the physical schema design or creating the recommended index. create index DEMO.IDX$_06C50002 on DEMO.BIGTAB(OBJECT_NAME);*/-EXPLAIN PLANS SECTION-/* Original With Adjusted Cost 这一部分是当前的执行计划*/1- Original With Adjusted Cost-Plan hash value: 1061520849-| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-| 0 | SELECT STATEMENT | | 1 | 120 | 9 (0)| 00:00:01 | 1 | SORT AGGREGATE | | 1 | 120 | | |* 2 | TABLE ACCESS BY INDEX ROWID | TWF_TASK_ORDER_RANG | 1 | 34 | 2 (0)| 00:00:01 | 3 | NESTED LOOPS | | 1 | 120 | 9 (0)| 00:00:01 | 4 | NESTED LOOPS | | 1 | 86 | 7 (0)| 00:00:01 |* 5 | TABLE ACCESS FULL | TWF_TASK_ORDER | 1 | 63 | 6 (0)| 00:00:01 |* 6 | TABLE ACCESS BY INDEX ROWID| TTT_TRACK_ORDER | 1 | 23 | 1 (0)| 00:00:01 |* 7 | INDEX RANGE SCAN | IDX_TRACK_ORDER_ORD_NO | 2 | | 0 (0)| 00:00:01 |* 8 | INDEX RANGE SCAN | PK_TWF_TASK_ORDER_RANG | 2 | | 1 (0)| 00:00:01 |- Predicate Information (identified by operation id):- 2 - filter(C.JOB_ID=:JOB_ID AND (C.STAFF_JOB_ID IS NULL OR C.STAFF_JOB_ID=:STAFF_JOB_ID) AND C.STATE=0) 5 - filter(B.ACTIVITY_NO=TTPC AND B.RETURN_FLAG3 AND B.CURR_FLAG=0 AND B.TASK_TYPE=02 AND B.TASK_STATE=0 AND B.ORD_TYPE=11) 6 - filter(A.SEQ=0) 7 - access(A.ORD_NO=B.ORD_NO) 8 - access(B.TASK_ORDER_NO=C.TASK_ORDER_NO)/*这一部分是使用SQL Profile之后的执行计划，我们可以看到CPU Cost减少两个点*/2- Using SQL Profile-Plan hash value: 4129054484-| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-| 0 | SELECT STATEMENT | | 1 | 120 | 7 (0)| 00:00:01 | 1 | SORT AGGREGATE | | 1 | 120 | | |* 2 | TABLE ACCESS BY INDEX ROWID | TWF_TASK_ORDER_RANG | 1 | 34 | 2 (0)| 00:00:01 | 3 | NESTED LOOPS | | 1 | 120 | 7 (0)| 00:00:01 | 4 | NESTED LOOPS | | 1 | 86 | 5 (0)| 00:00:01 |* 5 | TABLE ACCESS BY INDEX ROWID| TWF_TASK_ORDER | 1 | 63 | 4 (0)| 00:00:01 |* 6 | INDEX SKIP SCAN | IDX_TWF_TASK_ORDER_CUSTID | 1 | | 3 (0)| 00:00:01 |* 7 | TABLE ACCESS BY INDEX ROWID| TTT_TRACK_ORDER | 1 | 23 | 1 (0)| 00:00:01 |* 8 | INDEX RANGE SCAN | IDX_TRACK_ORDER_ORD_NO | 2 | | 0 (0)| 00:00:01 |* 9 | INDEX RANGE SCAN | PK_TWF_TASK_ORDER_RANG | 2 | | 1 (0)| 00:00:01 |- Predicate Information (identified by operation id):- 2 - filter(C.JOB_ID=:JOB_ID AND (C.STAFF_JOB_ID IS NULL OR C.STAFF_JOB_ID=:STAFF_JOB_ID) AND C.STATE=0) 5 - filter(B.ACTIVITY_NO=TTPC AND