Oracle 11g baseline

If a query is "hard" parsed, oracle produces several execution plan and compares it with the accepted plan in a baseline. If a match is found, oracle picks that plan.



Two Parameter for baseline

optimizer_capture_sql_plan_baseline if set to true, let oracle automatically capture sql plan baselines. The default value is false

Optimizer_use_sql_plan_baseline, if set to true, force oracle to use the sql plan baseline (if present). The default is true.




SQL> drop table test;

Table dropped.

SQL> create table test as select * from dba_objects ;

Table created.

SQL> select /*+withoutindex */ * from test where GENERATED='ANUJ';

no rows selected

SQL> select sql_id,sql_text from v$sqlarea where sql_text like '%withoutindex%' ;

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
b5dgh4v84xjdq
select /*+withoutindex */ * from test where GENERATED='ANUJ'

3yq0rqy2zrx3v
select sql_id,sql_text from v$sqlarea where sql_text like '%withoutindex%'


SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('b5dgh4v84xjdq'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID b5dgh4v84xjdq, child number 0
-------------------------------------
select /*+withoutindex */ * from test where GENERATED='ANUJ'

Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 282 (100)| |
|* 1 | TABLE ACCESS FULL| TEST | 11 | 2277 | 282 (1)| 00:00:04 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("GENERATED"='ANUJ')

Note
-----
- dynamic sampling used for this statement (level=2)


22 rows selected.

SQL> select sql_text, sql_handle, plan_name, enabled, accepted from dba_sql_plan_baselines;

no rows selected

SQL> var v_num number;
SQL> exec :v_num:=dbms_spm.load_plans_from_cursor_cache (sql_id => 'b5dgh4v84xjdq',plan_hash_value => 1357081020 );

PL/SQL procedure successfully completed.

SQL> select sql_text, sql_handle, plan_name, enabled, accepted from dba_sql_plan_baselines;

SQL_TEXT SQL_HANDLE PLAN_NAME ENA ACC
-------------------------------------------------------------------------------- ------------------------------ ------------------------------ --- ---
select /*+withoutindex */ * from test where GENERATED='ANUJ' SYS_SQL_c7f6b95762b4c6a9 SQL_PLAN_cgxptaxjb9jp997bbe3d0 YES YES

SQL> create index test_idx on test(GENERATED) parallel 8;

Index created.

SQL> select /*+withoutindex */ * from test where GENERATED='ANUJ';

no rows selected



SQL> select /*+withoutindex */ * from test where GENERATED='ANUJ';

no rows selected

SQL> select sql_id,sql_text from v$sqlarea where sql_text like '%withoutindex%' ;

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
b5dgh4v84xjdq
select /*+withoutindex */ * from test where GENERATED='ANUJ'

3yq0rqy2zrx3v
select sql_id,sql_text from v$sqlarea where sql_text like '%withoutindex%'





SQL> select sql_id,sql_text from v$sqlarea where sql_text like '%GENERATED=%';

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
b5dgh4v84xjdq
select /*+withoutindex */ * from test where GENERATED='ANUJ'

2s1gzn4d03qts
select sql_id,sql_text from v$sqlarea where sql_text like '%GENERATED=%'






SQL> select * from table(dbms_xplan.display_cursor('b5dgh4v84xjdq', 1, 'basic note'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /*+withoutindex */ * from test where GENERATED='ANUJ'

Plan hash value: 1357081020

----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| TEST |
----------------------------------

Note
-----
- SQL plan baseline SQL_PLAN_cgxptaxjb9jp997bbe3d0 used for this statement


17 rows selected.



SQL> select /*+index(test_idx test) */ * from test where GENERATED='ANUJ';

no rows selected

SQL> select sql_id,sql_text from v$sqlarea where sql_text like '%GENERATED=%';

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
b5dgh4v84xjdq
select /*+withoutindex */ * from test where GENERATED='ANUJ'

2s1gzn4d03qts
select sql_id,sql_text from v$sqlarea where sql_text like '%GENERATED=%'

8wgw4b0agmvv8
select /*+index(test_idx test) */ * from test where GENERATED='ANUJ'


SQL> select * from table(dbms_xplan.display_cursor('b5dgh4v84xjdq', 0, 'basic note'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID: b5dgh4v84xjdq, child number: 0 cannot be found


SQL> select * from table(dbms_xplan.display_cursor('8wgw4b0agmvv8',0, 'basic note'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /*+index(test_idx test) */ * from test where GENERATED='ANUJ'

Plan hash value: 1389866015

------------------------------------------------
| Id | Operation | Name |
------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST |
| 2 | INDEX RANGE SCAN | TEST_IDX |
------------------------------------------------

Note
-----
- dynamic sampling used for this statement (level=2)


18 rows selected.

SQL> exec :v_num:=dbms_spm.load_plans_from_cursor_cache(sql_id => '8wgw4b0agmvv8',plan_hash_value => '1389866015' );

PL/SQL procedure successfully completed.

SQL> select sql_text, sql_handle, plan_name, enabled, accepted from dba_sql_plan_baselines;

SQL_TEXT SQL_HANDLE PLAN_NAME ENA ACC
-------------------------------------------------------------------------------- ------------------------------ ------------------------------ --- ---
select /*+index(test_idx test) */ * from test where GENERATED='ANUJ' SYS_SQL_22655d99f2ac5a60 SQL_PLAN_24taxm7tasqm0a5063692 YES YES
select /*+withoutindex */ * from test where GENERATED='ANUJ' SYS_SQL_c7f6b95762b4c6a9 SQL_PLAN_cgxptaxjb9jp997bbe3d0 YES YES
select /*+withoutindex */ * from test where GENERATED='ANUJ' SYS_SQL_c7f6b95762b4c6a9 SQL_PLAN_cgxptaxjb9jp9a5063692 YES NO

SQL> exec :v_num:=dbms_spm.load_plans_from_cursor_cache(sql_id => '8wgw4b0agmvv8',plan_hash_value => 1389866015 ,sql_handle => 'SYS_SQL_c7f6b95762b4c6a9');

PL/SQL procedure successfully completed.

SQL> select sql_text, sql_handle, plan_name, enabled, accepted from dba_sql_plan_baselines;

SQL_TEXT SQL_HANDLE PLAN_NAME ENA ACC
-------------------------------------------------------------------------------- ------------------------------ ------------------------------ --- ---
select /*+index(test_idx test) */ * from test where GENERATED='ANUJ' SYS_SQL_22655d99f2ac5a60 SQL_PLAN_24taxm7tasqm0a5063692 YES YES
select /*+withoutindex */ * from test where GENERATED='ANUJ' SYS_SQL_c7f6b95762b4c6a9 SQL_PLAN_cgxptaxjb9jp997bbe3d0 YES YES
select /*+withoutindex */ * from test where GENERATED='ANUJ' SYS_SQL_c7f6b95762b4c6a9 SQL_PLAN_cgxptaxjb9jp9a5063692 YES YES

SQL>










#1

Step1)

Disable affecting plan and moniter for 2 hours.In case if the error has not been stopped or baseline plan is not available then go to step2.



DECLARE
v_number pls_integer;
BEGIN
v_number:=dbms_spm.alter_sql_plan_baseline(
sql_handle=>'SYS_SQL_357e8c14c551f65b',
plan_name=>'SYS_SQL_PLAN_a55df45c695bc033',
attribute_name=>'ACCECPTED',
attribute_value=>'NO');
END;
/


Step2)

If the baseline plan is not available for the affected SQL then just load it using the below SQL.


DECLARE

my_plans pls_integer;


BEGIN

my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => 'b5dgh4v84xjdq');

END;
/

#2

1) Drop the affecting plan and moniter for 2 hours.In case if the error has not been stopped or baseline plan is not available then go to step2.


DECLARE
v_plans_dropped pls_integer;
BEGIN
v_plans_dropped:=dbms_spm.drop_sql_plan_baseline(
sql_handle=>'SYS_SQL_22655d99f2ac5a60',
plan_name=>'SQL_PLAN_24taxm7tasqm0a5063692');
END;
/




2) If the baseline plan is not available for the affected SQL then just load it using the below SQL.

DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id => '99twu5t2dn5xd');
END;
/



SQL_ID is available in top section of the trace file or you can find it using the v$sql view.
sql_handle and plan_name can be found using the below SQL.

select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines WHERE SQL_TEXT LIKE '%&SQL_TEXT%';