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 !