In the example in Part 2, we saw that the optimizer used an accepted plan instead of a brand new plan. The statement has two plans in its plan history, but only one is accepted and thus in the SQL plan baseline:
SQL> select sql_text, plan_name, enabled, accepted from dba_sql_plan_baselines; SQL_TEXT PLAN_NAME ENA ACC ---------------------------------------- ------------------------------ --- --- select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b08cbcb825 YES NO endar_year from sales s, products p, times t where s.prod_id = p.prod_id and s.time_id = t.time_id and p.prod_id < :pid select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b0a62d0f4d YES YES endar_year from sales s, products p, times t where s.prod_id = p.prod_id and s.time_id = t.time_id and p.prod_id < :pid
Non-accepted plans can be verified by executing the evolve_sql_plan_baseline function. This function will execute the non-accepted plan and compare its performance to the best accepted plan. The execution is performed using the conditions (e.g., bind values, parameters, etc.) in effect at the time the non-accepted plan was added to the plan history. If the non-accepted plan's performance is better, the function will make it accepted, thus adding it to the SQL plan baseline. Let's see what happens when we execute this function:
SQL> var report clob; SQL> exec :report := dbms_spm.evolve_sql_plan_baseline(); PL/SQL procedure successfully completed. SQL> print :report REPORT ------------------------------------------------------------------------------- ------------------------------------------------------------------------------- Evolve SQL Plan Baseline Report ------------------------------------------------------------------------------- Inputs: ------- SQL_HANDLE = PLAN_NAME = TIME_LIMIT = DBMS_SPM.AUTO_LIMIT VERIFY = YES COMMIT = YES Plan: SYS_SQL_PLAN_fcc170b08cbcb825 ----------------------------------- Plan was verified: Time used .1 seconds. Passed performance criterion: Compound improvement ratio >= 10.13 Plan was changed to an accepted plan. Baseline Plan Test Plan Improv. Ratio ------------- --------- ------------- Execution Status: COMPLETE COMPLETE Rows Processed: 960 960 Elapsed Time(ms): 19 15 1.27 CPU Time(ms): 18 15 1.2 Buffer Gets: 1188 116 10.24 Disk Reads: 0 0 Direct Writes: 0 0 Fetches: 0 0 Executions: 1 1 ------------------------------------------------------------------------------- Report Summary ------------------------------------------------------------------------------- Number of SQL plan baselines verified: 1.
The plan verification report shows that the new plan's performance was better and so it was made accepted and became part of the SQL plan baseline. We can confirm it by looking in the dba_sql_plan_baselines view:
SQL> select sql_text, plan_name, enabled, accepted from dba_sql_plan_baselines; SQL_TEXT PLAN_NAME ENA ACC ---------------------------------------- ------------------------------ --- --- select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b08cbcb825 YES YES endar_year from sales s, products p, times t where s.prod_id = p.prod_id and s.time_id = t.time_id and p.prod_id < :pid select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b0a62d0f4d YES YES endar_year from sales s, products p, times t where s.prod_id = p.prod_id and s.time_id = t.time_id and p.prod_id < :pid
The SQL plan baseline now has two accepted plans: SYS_SQL_PLAN_fcc170b08cbcb825 is now accepted.
You can either execute the evolve_sql_plan_baseline() function manually or schedule it to run automatically in a maintenance window.
You can either execute the evolve_sql_plan_baseline() function manually or schedule it to run automatically in a maintenance window.
Another way of evolving a SQL plan baseline is to use the SQL Tuning Advisor. Instead of executing evolve_sql_plan_baseline, suppose we start from the original state where we have one accepted and one non-accepted plan:
SQL> select sql_text, plan_name, enabled, accepted from dba_sql_plan_baselines; SQL_TEXT PLAN_NAME ENA ACC ---------------------------------------- ------------------------------ --- --- select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b08cbcb825 YES NO endar_year from sales s, products p, times t where s.prod_id = p.prod_id and s.time_id = t.time_id and p.prod_id < :pid select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b0a62d0f4d YES YES endar_year from sales s, products p, times t where s.prod_id = p.prod_id and s.time_id = t.time_id and p.prod_id < :pid
You can execute the SQL Tuning Advisor on the cursor in the cursor cache:
SQL> var tname varchar2(30); SQL> exec :tname := dbms_sqltune.create_tuning_task(sql_id => 'bfbr3zrg9d5cc'); PL/SQL procedure successfully completed. SQL> exec dbms_sqltune.execute_tuning_task(task_name => :tname); PL/SQL procedure successfully completed. SQL> select dbms_sqltune.report_tuning_task(:tname, 'TEXT', 'BASIC') FROM dual; DBMS_SQLTUNE.REPORT_TUNING_TASK(:TNAME,'TEXT','BASIC') ------------------------------------------------------------------------------- GENERAL INFORMATION SECTION ------------------------------------------------------------------------------- Tuning Task Name : TASK_505 Tuning Task Owner : SH Workload Type : Single SQL Statement Scope : COMPREHENSIVE Time Limit(seconds): 1800 Completion Status : COMPLETED Started at : 11/11/2008 16:43:12 Completed at : 11/11/2008 16:43:13 ------------------------------------------------------------------------------- Schema Name: SH SQL ID : bfbr3zrg9d5cc SQL Text : select p.prod_name, s.amount_sold, t.calendar_year from sales s, products p, times t where s.prod_id = p.prod_id and s.time_id = t.time_id and p.prod_id < :pid ------------------------------------------------------------------------------- FINDINGS SECTION (1 finding) ------------------------------------------------------------------------------- 1- A potentially better execution plan was found for this statement. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original With Adjusted Cost ------------------------------ Plan hash value: 2787970893 ---------------------------------------------------------------- | Id | Operation | Name | ---------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | NESTED LOOPS | | | 2 | NESTED LOOPS | | | 3 | HASH JOIN | | | 4 | TABLE ACCESS BY INDEX ROWID | PRODUCTS | | 5 | INDEX RANGE SCAN | PRODUCTS_PK | | 6 | PARTITION RANGE ALL | | | 7 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | | 8 | BITMAP CONVERSION TO ROWIDS | | | 9 | BITMAP INDEX RANGE SCAN | SALES_PROD_BIX | | 10 | INDEX UNIQUE SCAN | TIME_PK | | 11 | TABLE ACCESS BY INDEX ROWID | TIMES | ---------------------------------------------------------------- 2- Original With Adjusted Cost ------------------------------ Plan hash value: 2787970893 ---------------------------------------------------------------- | Id | Operation | Name | ---------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | NESTED LOOPS | | | 2 | NESTED LOOPS | | | 3 | HASH JOIN | | | 4 | TABLE ACCESS BY INDEX ROWID | PRODUCTS | | 5 | INDEX RANGE SCAN | PRODUCTS_PK | | 6 | PARTITION RANGE ALL | | | 7 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | | 8 | BITMAP CONVERSION TO ROWIDS | | | 9 | BITMAP INDEX RANGE SCAN | SALES_PROD_BIX | | 10 | INDEX UNIQUE SCAN | TIME_PK | | 11 | TABLE ACCESS BY INDEX ROWID | TIMES | ---------------------------------------------------------------- 3- Using SQL Profile -------------------- Plan hash value: 2361178149 ------------------------------------------ | Id | Operation | Name | ------------------------------------------ | 0 | SELECT STATEMENT | | | 1 | HASH JOIN | | | 2 | HASH JOIN | | | 3 | PARTITION RANGE ALL| | | 4 | TABLE ACCESS FULL | SALES | | 5 | TABLE ACCESS FULL | TIMES | | 6 | TABLE ACCESS FULL | PRODUCTS | ------------------------------------------ ------------------------------------------------------------------------------- SQL> exec dbms_sqltune.accept_sql_profile(task_name => :tname); PL/SQL procedure successfully completed. SQL> select sql_text, plan_name, enabled, accepted from dba_sql_plan_baselines; SQL_TEXT PLAN_NAME ENA ACC ---------------------------------------- ------------------------------ --- --- select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b08cbcb825 YES YES endar_year from sales s, products p, times t where s.prod_id = p.prod_id and s.time_id = t.time_id and p.prod_id < :pid select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b0a62d0f4d YES YES endar_year from sales s, products p, times t where s.prod_id = p.prod_id and s.time_id = t.time_id and p.prod_id < :pid SQL> select sql_text, type, status from dba_sql_profiles; SQL_TEXT TYPE STATUS ---------------------------------------- ------- -------- select p.prod_name, s.amount_sold, t.cal MANUAL ENABLED endar_year from sales s, products p, times t where s.prod_id = p.prod_id and s.time_id = t.time_id and p.prod_id < :pid
What we see here is that SQL Tuning Advisor found a tuned plan (that coincidentally happened to be the non-accepted plan in our plan history). When we accepted the recommended SQL profile, the SQL Tuning Advisor created a SQL profile and also changed the non-accepted plan to accepted status, thus evolving the SQL plan baseline to two plans.
Note that the SQL Tuning Advisor may also find a completely new tuned plan, one that is not in the plan history. If you then accept the recommended SQL profile, the SQL Tuning Advisor will create a SQL profile and also add the tuned plan to the SQL plan baseline.
Thus, you can evolve a SQL plan baseline either by executing the evolve_sql_plan_baseline function or by using the SQL Tuning Advisor. New and provably better plans will be added by either of these methods to the SQL plan baseline.
No comments:
Post a Comment