Hi Tom,
I have POC on my lab about SQL Plan Management and found some thing that not make sense.
My Oracle Database Version is "Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production" with a single node, parameter cursor_sharing = FORCE.
I created table that skew data with below information which have TEST_SPM_IDX1 on ID1 column and gather stats 4 bucket to make sure oracle create histogram and know they are skewed data on this column.
SQL> SELECT ID1,COUNT(*) FROM DEMO.TEST_SPM GROUP BY ID1;
ID1 COUNT(*)
---------- ----------
1 1
2 1000
3 2000
SQL> exec dbms_stats.gather_table_stats(OWNNAME => 'DEMO',TABNAME => 'TEST_SPM',ESTIMATE_PERCENT =>null,METHOD_OPT => 'FOR COLUMNS SIZE 4 ID1');
I simulate query to aspect optimizer create execution plan to use index scan on id1=1
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
SQL> select /*TEST_SPM*/ * from demo.test_spm where id1=1;
SQL> SELECT SQL_TEXT, SQL_ID, CHILD_NUMBER CHILD#, HASH_VALUE,
PLAN_HASH_VALUE FROM V$SQL
WHERE UPPER(SQL_TEXT) LIKE '%/*TEST_SPM*/%'
AND LOWER(SQL_TEXT) NOT LIKE '%HASH%';
SQL_TEXT SQL_ID CHILD# HASH_VALUE PLAN_HASH_VALUE
----------------------------------------------------------------- ------------- ---------- ---------- ---------------
select /*TEST_SPM*/ * from demo.test_spm where id1=:"SYS_B_0" dfb3nvbbw2urx 0 3619777277 863343145
SQL> SET PAGESIZE 0
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('dfb3nvbbw2urx',NULL,'ALL'));
Plan hash value: 863343145
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 241 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_SPM | 1000 | 983K| 241 (0)| 00:00:03 |
|* 2 | INDEX RANGE SCAN | TEST_SPM_IDX1 | 1000 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
I capture these cursor to SQL Plan baseline by use below statement
SQL> variable pls number;
exec :pls := dbms_spm.load_plans_from_cursor_cache(SQL_ID => 'dfb3nvbbw2urx');
-- and then i execute with different bind variable with id1=2 (aspect to use full table scan)
SQL> select /*TEST_SPM*/ * from demo.test_spm where id1=2;
-- view current SQL Plan baseline for 'dfb3nvbbw2urx'
SQL> select t.*
from (select distinct sql_handle
from dba_sql_plan_baselines
where SIGNATURE = '10288309437548151870') pb,
table(dbms_xplan.display_sql_plan_baseline(pb.sql_handle,
null,'basic')) t;
--------------------------------------------------------------------------------
SQL handle: SQL_8ec76aec745b443e
SQL text: select /*TEST_SPM*/ * from demo.test_spm where id1=:"SYS_B_0"
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_8xjvaxju5qj1yeb1890ae Plan id: 3944255662
Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 1145642998
--------------------------------------
| Id | Operation | Name |
--------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| TEST_SPM |
--------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_8xjvaxju5qj1yf5a65c55 Plan id: 4121320533
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
Plan hash value: 863343145
-----------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_SPM |
| 2 | INDEX RANGE SCAN | TEST_SPM_IDX1 |
-----------------------------------------------------
SQL> DECLARE
2 report clob;
3 BEGIN
4 report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle =>'SQL_8ec76aec745b443e',COMMIT=>'NO');
5 DBMS_OUTPUT.PUT_LINE(report);
6 END;
7 /
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SQL_8ec76aec745b443e
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = NO
Plan: SQL_PLAN_8xjvaxju5qj1yeb1890ae
------------------------------------
Plan was verified: Time used .01 seconds.
Plan failed
performance criterion: 333 times worse than baseline plan.
Baseline Plan Test Plan Stats Ratio
------------- --------- -----------
Execution Status: COMPLETE COMPLETE
Rows Processed: 1 1
Elapsed Time(ms): .011 .34 .03
CPU Time(ms): 0 .333 0
Buffer Gets: 442 .01
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Executions: 1 1
-------------------------------------------------------------------------------
Report
Summary
-------------------------------------------------------------------------------
Number of plans verified: 1
Number of plans accepted: 0
I try to use evolve function and aspect that this function will compare performance for sql_id with match bind variable but as upper result they not as my aspect?
so that full table scan execution plan will never accepted plan in this case even full table scan more suit with id1=2 and id1=3 than index scan ? please suggest and thank you very much.
Xia,
Hi Xia,
The performance criteria used by the SPM evolve process is the same as one used by the SQL Tune Advisor and SQL Performance Analyzer. An aggregate performance statistic is calculated for each test execution based the elapse time, CPU time and buffer gets. The performance statistics are then compared and if the new plan shows a performance improvement of 1.5X over the existing accepted plan, then it will be accepted. The goal being not to accept any plan on less it really is going to give better performance. This is a rather conservative approach but it prevents SPM introducing any plan regressions.
In your case, although the full table scan plan was faster in terms of elapse time, it’s combined performance statistics wasn’t 1.5X better than the baseline plan (INDEX ACCESS). Most likely because of the large increase in the number of buffer gets.
The simplest solution in this case would be to manually accept the FULL TABLE SCAN plan if you believe it is the better plan for the bind variable value 2. You manually accept a plan using the DBMS_SPM. EVOLVE_SQL_PLAN_BASELINE Function with the verify attribute set to NO.
DECLARE
2 report clob;
3 BEGIN
4 report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle =>'SQL_8ec76aec745b443e',
plan_name=>‘SQL_PLAN_8xjvaxju5qj1yf5a65c55’,
verify=>'NO');
5 DBMS_OUTPUT.PUT_LINE(report);
6 END;
7 /
In general, when working with SQL statement that benefits from Adaptive Cursor Sharing we highly recommend you manually load all possible plans in to a SQL plan baseline directly from the cursor cache. This ensures that all of the plans will be automatically accepted and available for use. You can use the same approach you took before using the DBMS_SPM.LOAD_PLANS_FROM_CACHE procedure but make sure both execution plans are in the shared pool, so you can capture both plans as accepted plans in the SQL plan baseline. More information on why we recommend this approach can be found in the SQL Plan Management white paper
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-sql-plan-mgmt-12c-1963237.pdf