SQL Plan Management - SPM

Sumit Kumar

How to use Oracle SPM ?
The performance of any SQL and database applications heavily depends on query plan and its run time execution path.
A SQL execution plan can change for a variety of reasons including: regathering optimizer statistics, changing optimizer parameters or schema/metadata definitions. The changes in SQL plan during execution sometime leads to drastic performance degradation in critical applicaitons. SQL Plan Management (SPM) provides a framework for completely transparent controlled execution plan evolution. With SPM the optimizer automatically manages execution plans and ensures only known or verified plans are used. When a new plan is found for a SQL statement it will not be used until it has been verified by the database to have comparable or better performance than the current plan.
The SQL Plan Management has three main components:

  • SQL plan baseline capture
  • SQL plan baseline selection
  • SQL plan baseline evolution



    • A. Automatic plan capture - on the fly : In the database init file set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to TRUE (default is FALSE).

    B. Using DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE or through Oracle Enterprise Manager :

    1. Identify the SQL-ID for your SQL.

    select s.sid, S.USERNAME, s.sql_id, p.spid FROM v$session s, v$process p WHERE s.paddr = p.addr and s.username='APPS' and s.sid='&1';

    SID USERNAME SQL_ID SPID
    ---------- ------------------------------ ------------- ------------------------
    266 APPS 1nc15w35wgbyf 8138

    2. Using the SQL_ID create a SQL plan baseline for the statement

    sqlplus "/ as sysdba"
    SQL> variable cnt number;
    SQL> EXECUTE :cnt :=DBMS_SPM.LOAD_PLAN_FROM_CURSOR_CACHE(sql_id=>'1nc15w35wgbyf');

    3. Disable the sub-optimal plan - The SQL_HANDLE & PLAN_NAME are required to disable the plan.

    SQL> SELECT sql_handle, sql_text, plan_name, enabled FROM dba_sql_plan_baselines;
    Using DBMS_SPM.ALTER_SQL_PLAN_BASELINE disable the bad plan

    SQL> variable cnt number;
    SQL> exec :cnt :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE(SQL_HANDLE => 'SYS_SQL_bf5c9b0gt72bde3e',PLAN_NAME=> 'SQL_PLAN_byr4v13lllrjy42949306', ATTRIBUTE_NAME => 'enabled', ATTRIBUTE_VALUE => 'NO');

    SQL> SELECT sql_handle, sql_text, plan_name, enabled FROM dba_sql_plan_baselines;

    4. Now you need to choose which plan is better and apply the better SQL plan

    SQL> SELECT sql_id, plan_hash_value, fulltext, FROM V$SQL WHERE sql_text LIKE '%SELECT /*+ INDEX(p) */sql_text LIKE '%SELECT /*+ INDEX(p) */ prod_na%';

    5. Using the SQL_ID and PLAN_HASH_VALUE for the modified plan, create a new accepted plan for original SQL statement by associating the modified plan to the original statement's SQL_HANDLE.

    exec :cnt:=dbms_spm.load_plans_from_cursor_cache(
    sql_id => '1nc15w35wgbyf',
    plan_hash_value => 3074205472,
    sql_handle => 'SYS_SQL_bf5c9b08f72bd23x');