Jump to content
nongxia1

SPM accepted plan with different bind variable

Recommended Posts

Hi Burleson,

    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,

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×