Method to build and fix your own SQL plans in Oracle and then you want to fix it for all next executions of the SQL by your application (thanks to SPM).

In this post I show how fix a plan you have created by yourself.

First we need to identified the query

SQL> select /* INDEX_ACCESS */ line_item_id, product_id, unit_price, quantity from ORDER_ITEMS where order_id = 234098 ;

LINE_ITEM_ID PRODUCT_ID UNIT_PRICE   QUANTITY
------------ ---------- ---------- ----------
           1        414        851          5
           2        499        818          3

Execution Plan
----------------------------------------------------------
Plan hash value: 4159986352

-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |     3 |    57 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| ORDER_ITEMS   |     3 |    57 |     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | ITEM_ORDER_IX |     3 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

In that example I’ll take an application query against the ORDER_ITEMS table. I find hat query too fast because it is using the index ITEM_ORDER_IX which is based on the primary key. So we are going to force that query to be executed by accessing the whole table without using any index. Here I’ll use the hint FULL to do the job.

SQL> select /* FULL_ACCESS */ /*+ full(ORDER_ITEMS) */ line_item_id, product_id, unit_price, quantity from ORDER_ITEMS where order_id = 234098 ;

LINE_ITEM_ID PRODUCT_ID UNIT_PRICE   QUANTITY
------------ ---------- ---------- ----------
           1        414        851          5
           2        499        818          3

Execution Plan
----------------------------------------------------------
Plan hash value: 456270211

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |     3 |    57 |  2022   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| ORDER_ITEMS |     3 |    57 |  2022   (1)| 00:00:01 |
---------------------------------------------------------------------------------

 

On both queries I added a comment to make it easier to retrieve information in the SQL views from Oracle. Now I can get the statistic about my queries:

SQL> @sql

SQL_ID          PLAN_HASH_VALUE SQL_PLAN_BASELINE              EXECUTIONS ELAPSED_TIME EXACT_MATCHING_SIGNATURE SQL_TEXT
-------------- ---------------- ------------------------------ ---------- ------------ ------------------------ --------------------------------------------------
8ms87fhrq01xh        4159986352                                        25         5199     18199500880047668241 select /* INDEX_ACCESS */ line_item_id, product_id
55x955b31npwq         456270211                                         3       155947     10822814485518112755 select /* FULL_ACCESS */ /*+ full(ORDER_ITEMS) */

 

Plan control

So my goal is to force the application query “8ms87fhrq01xh” to use the plan from my manual modified query “55x955b31npwq”. To do so, I’m going to use the  “SQL Plan Management” from Oracle which is embedded from the release 11 and can be used with the DBMS_SPM package.

First I need to load the plan from my application query into SPM baseline:

SQL> set serveroutput on
declare
  plans_loaded pls_integer ;
begin
  plans_loaded := dbms_spm.load_plans_from_cursor_cache( sql_id => '8ms87fhrq01xh' ) ;
  dbms_output.put_line( 'plans loaded: '||plans_loaded ) ;
end ;
/
plans loaded: 1

PL/SQL procedure successfully completed.

 

I have now a new cursor in the SQL view with the SQL_PLAN_BASELINE identifier:

SQL> @sql

SQL_ID          PLAN_HASH_VALUE SQL_PLAN_BASELINE              EXECUTIONS ELAPSED_TIME EXACT_MATCHING_SIGNATURE SQL_TEXT
-------------- ---------------- ------------------------------ ---------- ------------ ------------------------ --------------------------------------------------
8ms87fhrq01xh        4159986352                                        25         5199     18199500880047668241 select /* INDEX_ACCESS */ line_item_id, product_id
8ms87fhrq01xh        4159986352 SQL_PLAN_gt4cxn0aacz0j91520601          1        21703     18199500880047668241 select /* INDEX_ACCESS */ line_item_id, product_id
55x955b31npwq         456270211                                         3       155947     10822814485518112755 select /* FULL_ACCESS */ /*+ full(ORDER_ITEMS) */

 

I can now find the SPM content for my SQL:

SQL> @spm
Enter value for signature: 18199500880047668241
old   9: where signature = '&signature.'
new   9: where signature = '18199500880047668241'

            SIGNATURE SQL_HANDLE                     PLAN_NAME                      ENABL ACCEP FIXED
--------------------- ------------------------------ ------------------------------ ----- ----- -----
 18199500880047668241 SQL_fc919da014a67c11           SQL_PLAN_gt4cxn0aacz0j91520601 YES   YES   NO

 

What we need to do now is to inject into the SPM baseline the plan from my modified query. To do so, I need the SQL_HANDLE of my application query and the couple of SQL_ID+PLAN_HASH_VALUE of the modified query to inject its plan into the plan baseline of my application query:

SQL> set serveroutput on
declare
  plans_loaded pls_integer ;
begin
  plans_loaded := dbms_spm.load_plans_from_cursor_cache(
    sql_id => '55x955b31npwq',
    plan_hash_value => 456270211,
    sql_handle => 'SQL_fc919da014a67c11'
  ) ;
  dbms_output.put_line( 'plans loaded: '||plans_loaded ) ;
end ;
/
plans loaded: 1

PL/SQL procedure successfully completed.

 

Now, let’s seen what’s in the baseline of our application query:

SQL> @spm
Enter value for signature: 18199500880047668241
old   9: where signature = '&signature.'
new   9: where signature = '18199500880047668241'

            SIGNATURE SQL_HANDLE                     PLAN_NAME                      ENABL ACCEP FIXED
--------------------- ------------------------------ ------------------------------ ----- ----- -----
 18199500880047668241 SQL_fc919da014a67c11           SQL_PLAN_gt4cxn0aacz0j91520601 YES   YES   NO
 18199500880047668241 SQL_fc919da014a67c11           SQL_PLAN_gt4cxn0aacz0jf91228bb YES   YES   NO

 

A new plan called “SQL_PLAN_gt4cxn0aacz0jf91228bb” has been generated and I know want to be sure it is the only one that are goin gto be used. Thus we need to fix it:

SQL> set serveroutput on
declare
  plans_loaded pls_integer ;
begin
  plans_loaded := dbms_spm.alter_sql_plan_baseline(
    sql_handle => 'SQL_fc919da014a67c11',
plan_name => 'SQL_PLAN_gt4cxn0aacz0jf91228bb',
    attribute_name => 'fixed',
    attribute_value => 'YES'
  ) ;
  dbms_output.put_line( 'plans modified: '||plans_loaded ) ;
end ;
/
plans modified: 1

PL/SQL procedure successfully completed.

SQL> @spm
Enter value for signature: 18199500880047668241
old   9: where signature = '&signature.'
new   9: where signature = '18199500880047668241'

            SIGNATURE SQL_HANDLE                     PLAN_NAME                      ENABL ACCEP FIXED
--------------------- ------------------------------ ------------------------------ ----- ----- -----
 18199500880047668241 SQL_fc919da014a67c11           SQL_PLAN_gt4cxn0aacz0j91520601 YES   YES   NO
 18199500880047668241 SQL_fc919da014a67c11           SQL_PLAN_gt4cxn0aacz0jf91228bb YES   YES   YES

 

Then, my Full access plan is fixed and I can check if the index on the primary key is still in used:

SQL> select /* INDEX_ACCESS */ line_item_id, product_id, unit_price, quantity from ORDER_ITEMS where order_id = 234098

LINE_ITEM_ID PRODUCT_ID UNIT_PRICE   QUANTITY
------------ ---------- ---------- ----------
           1        414        851          5
           2        499        818          3

Execution Plan
----------------------------------------------------------
Plan hash value: 456270211

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |     3 |    57 |  2022   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| ORDER_ITEMS |     3 |    57 |  2022   (1)| 00:00:01 |
---------------------------------------------------------------------------------

 

I can monitor the plan usage from the SQL view to check if the application has is executing the query with the new plan:

SQL> @sql

SQL_ID          PLAN_HASH_VALUE SQL_PLAN_BASELINE              EXECUTIONS ELAPSED_TIME EXACT_MATCHING_SIGNATURE SQL_TEXT
-------------- ---------------- ------------------------------ ---------- ------------ ------------------------ --------------------------------------------------
8ms87fhrq01xh         456270211 SQL_PLAN_gt4cxn0aacz0jf91228bb          6       145687     18199500880047668241 select /* INDEX_ACCESS */ line_item_id, product_id
55x955b31npwq         456270211                                         3       155947     10822814485518112755 select /* FULL_ACCESS */ /*+ full(ORDER_ITEMS) */

 

As I see that both queries are currently using the same plan, I know that my application is now using the new plan with the full access to the ORDER_ITEMS table.

I hope this demonstration may help and please do not hesitate to contact us if you have any further questions or observations.

Scripts used in this article:

-- script sql.sql
set lines 180 pages 500
col sql_id format a14
col sql_plan_baseline format a30
col plan_hash_value format 999999999999999
col exact_matching_signature format 99999999999999999999
col sql_text format a50
select sql_id,
plan_hash_value,
sql_plan_baseline,
executions,
elapsed_time,
exact_matching_signature,
substr(sql_text,0,50) sql_text
from v$sql
where parsing_schema_name != 'SYS'
and sql_text like '%_ACCESS%' ;
-- script spm.sql
set lines 200
set pages 500
col signature format 99999999999999999999
col sql_handle format a30
col plan_name format a30
col enabled format a5
col accepted format a5
col fixed format a5
select
signature,
sql_handle,
plan_name,
enabled,
accepted,
fixed
from dba_sql_plan_baselines
where signature = '&signature.'
Post Views: 8,586
Thumbnail [60x60]
by
Nicolas Penot