By Franck Pachot

.
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.

Not easy to capture

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.

Have to be managed

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:

Licensing

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: 4

Then 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.

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

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.

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.

Update Jan. 2016

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