This was first published on https://blog.dbi-services.com/oow15-day-4-you-can-auto-evolve-spm-without-tuning-pack (2015-10-29)
Republishing here for new followers. The content is related to the the versions available at the publication date
As I said in a previous post: you can’t complain about plan instability if you didn’t give a try at SQL Baselines. I was discussing about that with developer at the DemoGrounds trying to understand why so few people use SPM… and I learned something I overlooked. Here are the arguments I’m thinking off about why people are reluctant to use it.
Well, it’s easy to capture (just a parameter to set) but the problem is that it can actually capture too much. Do that at a specific period when you know the critical use cases and only those are running. It captures a statement once it is executed a second time and unfortunately there is no way to change that: there are no counter.
We can expect some improvement about it in 12c release 2 where we can filter (service, action, module,…) what is captured.
Yes, I always say that it’s not because something is captured automatically that you don’t have to care about it. I don’t want to capture thousands of statements and evolve them manually one by one.
So the answer from the optimizer team is that there is the auto evolve job, checking the execution time of the new plans, but I raised the fact that it needs Tuning Pack. But I learned that it’s not true anymore. In 12c the auto evolve task has been taking out of the tuning advisor and don’t need any option.
Let’s try it on a database where control_management_pack_access=none:
First I check the feature usage statistics before:
06:46:37 SQL> select name,detected_usages,aux_count,feature_info from dba_feature_usage_statistics where name in ('SQL Tuning Advisor','SQL Plan Management','SPM Evolve Advisor','Automatic SQL Tuning Advisor'); NAME DETECTED_USAGES AUX_COUNT FEATURE_INFO ------------------------------ --------------- ---------- -------------------------------------------------------------------------------- Automatic SQL Tuning Advisor 28 4 Execution count so far: 6, Executions with auto-implement: 0, SQL profiles recom mended so far: 3, Projected DB Time Saved Automatically (s): 0 SQL Tuning Advisor 1 59 <advisor_usage> <reports> <first_report_time>19-mar-2015 16:38:48</first_report_time> <last_report_time>20-mar-2015 23:46:05</last_report_time> <num_db_reports>64</num_db_reports> </reports> </advisor_usage> SPM Evolve Advisor 12 24 <advisor_usage> <reports> <first_report_time>27-oct-2015 21:49:08</first_report_time> <last_report_time>29-oct-2015 00:22:24</last_report_time> <num_db_reports>22</num_db_reports> </reports> </advisor_usage> SQL Plan Management 11 4 Manual-load: 0, Auto-capture: 4, Manual-sqltune: 0, Auto-sqltune: 0, Stored-outl ine: 0, Evolve-advisor: 0, Accepted: 4, Fixed: 0, Reproduced: 4Then capture a statement after setting:
06:46:40 SQL> alter session set optimizer_capture_sql_plan_baselines=true;Session altered.I’ve run several executions with different bind variable value that change the execution plan, and here is how to run the evolve task:
06:46:40 SQL> variable task varchar2(30); 06:46:40 SQL> variable exec varchar2(30); 06:46:40 SQL> exec :task:= dbms_spm.create_evolve_task(sql_handle=>'SQL_ad32be0bc40d1301'); PL/SQL procedure successfully completed. 06:46:41 SQL> exec :exec:=dbms_spm.execute_evolve_task(task_name=>:task); PL/SQL procedure successfully completed. 06:46:41 SQL> select dbms_spm.report_evolve_task(task_name=>:task,execution_name=>:exec) from dual; DBMS_SPM.REPORT_EVOLVE_TASK(TASK_NAME=>:TASK,EXECUTION_NAME=>:EXEC) -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- GENERAL INFORMATION SECTION --------------------------------------------------------------------------------------------- Task Information: --------------------------------------------- Task Name : TASK_5364 Task Owner : DEMO Execution Name : EXEC_5564 Execution Type : SPM EVOLVE Scope : COMPREHENSIVE Status : COMPLETED Started : 10/29/2015 06:45:51 Finished : 10/29/2015 06:45:51 Last Updated : 10/29/2015 06:45:51 Global Time Limit : 2147483646 Per-Plan Time Limit : UNUSED Number of Errors : 0 --------------------------------------------------------------------------------------------- SUMMARY SECTION --------------------------------------------------------------------------------------------- Number of plans processed : 1 Number of findings : 1 Number of recommendations : 1 Number of errors : 0 --------------------------------------------------------------------------------------------- DETAILS SECTION --------------------------------------------------------------------------------------------- Object ID : 2 Test Plan Name : SQL_PLAN_aucpy1g20u4s10c23f8fd Base Plan Name : SQL_PLAN_aucpy1g20u4s1838f84a8 SQL Handle : SQL_ad32be0bc40d1301 Parsing Schema : DEMO Test Plan Creator : DEMO SQL Text : select * from DEMO where n=:n and nn=1 Bind Variables: ----------------------------- 1 - (NUMBER): 1 Execution Statistics: ----------------------------- Base Plan Test Plan ---------------------------- ---------------------------- Elapsed Time (s): .000095 .000004 CPU Time (s): .0001 0 Buffer Gets: 16 0 Optimizer Cost: 51 2 Disk Reads: 0 0 Direct Writes: 0 0 Rows Processed: 0 0 Executions: 10 10 FINDINGS SECTION --------------------------------------------------------------------------------------------- Findings (1): ----------------------------- 1. The plan was verified in 0.06000 seconds. It passed the benefit criterion because its verified performance was 56.36667 times better than that of the baseline plan. Recommendation: ----------------------------- Consider accepting the plan. Execute dbms_spm.accept_sql_plan_baseline(task_name => 'TASK_5364', object_id => 2, task_owner => 'DEMO'); EXPLAIN PLANS SECTION --------------------------------------------------------------------------------------------- Baseline Plan ----------------------------- Plan Id : 17601 Plan Hash Value : 2207220904 --------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | --------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 7 | 51 | 00:00:01 | | * 1 | TABLE ACCESS FULL | DEMO | 1 | 7 | 51 | 00:00:01 | --------------------------------------------------------------------- Predicate Information (identified by operation id): ------------------------------------------ * 1 - filter("N"=:N AND "NN"=1) Test Plan ----------------------------- Plan Id : 17602 Plan Hash Value : 203684093 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 7 | 2 | 00:00:01 | | * 1 | TABLE ACCESS BY INDEX ROWID BATCHED | DEMO | 1 | 7 | 2 | 00:00:01 | | * 2 | INDEX RANGE SCAN | DEMO | 1 | | 1 | 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ------------------------------------------ * 1 - filter("NN"=1) * 2 - access("N"=:N) --------------------------------------------------------------------------------------------- 00:46:12 SQL> exec :exec:=dbms_spm.implement_evolve_task(task_name=>:task); PL/SQL procedure successfully completed.So I’ve two plans, one accepted and one non accepted. The auto evolve task verified that the new plan is ok I implemented the recommendation to accept it.
So I was able to do that without Tuning Pack.
Now having a look at feature usage because I don’t want LMS to think that I’m using non licensed options:
06:46:42 SQL> exec sys.dbms_feature_usage_internal.exec_db_usage_sampling(sysdate); commit; PL/SQL procedure successfully completed.When you compare with the previous result you see that no use of Tuning Advisor has been recorded. ‘SPM Evolve Advisor’ and ‘SQL Plan Management’ are available in Enterprise Edition without option.06:47:09 SQL> select name,detected_usages,aux_count,feature_info from dba_feature_usage_statistics where name in ('SQL Tuning Advisor','SQL Plan Management','SPM Evolve Advisor','Automatic SQL Tuning Advisor'); NAME DETECTED_USAGES AUX_COUNT FEATURE_INFO ------------------------------ --------------- ---------- -------------------------------------------------------------------------------- Automatic SQL Tuning Advisor 28 4 Execution count so far: 6, Executions with auto-implement: 0, SQL profiles recom mended so far: 3, Projected DB Time Saved Automatically (s): 0 SQL Tuning Advisor 1 59 <advisor_usage> <reports> <first_report_time>19-mar-2015 16:38:48</first_report_time> <last_report_time>20-mar-2015 23:46:05</last_report_time> <num_db_reports>64</num_db_reports> </reports> </advisor_usage> SPM Evolve Advisor 13 33 <advisor_usage> <reports> <first_report_time>27-oct-2015 21:49:08</first_report_time> <last_report_time>29-oct-2015 06:45:52</last_report_time> <num_db_reports>31</num_db_reports> </reports> </advisor_usage> SQL Plan Management 12 4 Manual-load: 0, Auto-capture: 4, Manual-sqltune: 0, Auto-sqltune: 0, Stored-outl ine: 0, Evolve-advisor: 0, Accepted: 4, Fixed: 0, Reproduced: 4
This is new in 12c because the SPM evolve task has been made independent of the SQL tuning Advisor.
Conclusion is when you are in Enterprise Edition you have no excuse for not using SQL Baselines.
Here is a link to the Oracle Optimizer Group blog post about discussion about it at OOW Demo Grounds: https://blogs.oracle.com/optimizer/entry/upgrade_to_oracle_database_12c1
Didn’t knew that it isn’t part of the Tuning Pack. Then it should also work if you set “ALTER SYSTEM SET control_management_pack_access=NONE scope=both;”. I’ll try that
Or at least setting control_management_pack_access to diagnostic…
Hi Jörg, Yes, the test above was done while control_management_pack_access set to NONE. I didn’t know either and learned that from the developer that implemented the SPM Evolve Advisor. Regards, Franck.
Thanks Franck for those insights, Regards, Foued