Oracle & MySQL Support

InfraStack-Labs Oracle & MySQL DBA Services help you manage, maintain, and optimize your critical Oracle systems. We deliver 24/7, year-round support with flexible monthly contracts that don’t lock you in.

Please contact me :- ajith.narayanan@infrastack-labs.in

Tuesday, January 22, 2013

Oracle 11g Baseline - Try This To Be Crystal On Baselining



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> create table sqlplantest as select * from dba_objects ;

Table created.

SQL> select /*+withoutindex */ * from sqlplantest where GENERATED='AJITH';

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 sqlplantest where GENERATED='AJITH'
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 sqlplantest where GENERATED='AJITH'

Plan hash value: 1357081020

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

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

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

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 sqlplantest where GENERATED='AJITH' SYS_SQL_c7f6b95762b4c6a9 SQL_PLAN_cgxptaxjb9jp997bbe3d0  YES YES

SQL> create index sqlplantest_idx on sqlplantest(GENERATED) parallel 8;

Index created.

SQL> select /*+withoutindex */ * from sqlplantest where GENERATED='AJITH';

no rows selected

SQL> select /*+withoutindex */ * from sqlplantest where GENERATED='AJITH';

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 sqlplantest where GENERATED='AJITH'
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 sqlplantest where GENERATED='AJITH'
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 sqlplantest where GENERATED='AJITH'

Plan hash value: 1357081020

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

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


17 rows selected.



SQL> select /*+index(sqlplantest_idx sqlplantest) */ * from sqlplantest where GENERATED='AJITH';

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 sqlplantest where GENERATED='AJITH'
2s1gzn4d03qts   select sql_id,sql_text from v$sqlarea where sql_text like '%GENERATED=%'
8wgw4b0agmvv8 select /*+index(sqlplantest_idx sqlplantest) */ * from sqlplantest where GENERATED='AJITH'


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(sqlplantest_idx sqlplantest) */ * from sqlplantest where GENERATED='AJITH'

Plan hash value: 1389866015

---------------------------------------------------------
| Id | Operation | Name     |
---------------------------------------------------------
| 0 | SELECT STATEMENT |   |
| 1 | TABLE ACCESS BY INDEX ROWID | sqlplantest    |
| 2 | INDEX RANGE SCAN | sqlplantest_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(sqlplantest_idx sqlplantest) */ * from sqlplantest where GENERATED='AJITH'    SYS_SQL_22655d99f2ac5a60 SQL_PLAN_24taxm7tasqm0a5063692 YES YES
select /*+withoutindex */ * from sqlplantest where GENERATED='AJITH'      SYS_SQL_c7f6b95762b4c6a9 SQL_PLAN_cgxptaxjb9jp997bbe3d0  YES YES
select /*+withoutindex */ * from sqlplantest where GENERATED='AJITH'  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(sqlplantest_idx sqlplantest) */ * from sqlplantest where GENERATED='AJITH' SYS_SQL_22655d99f2ac5a60 SQL_PLAN_24taxm7tasqm0a5063692  YES YES
select /*+withoutindex */ * from sqlplantest where GENERATED='AJITH'    SYS_SQL_c7f6b95762b4c6a9 SQL_PLAN_cgxptaxjb9jp997bbe3d0  YES YES
select /*+withoutindex */ * from sqlplantest where GENERATED='AJITH'    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

Step 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;
/




Step 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%'; 


HAPPY LEARNING!

No comments:

Post a Comment

Thanks for you valuable comments !