This article is about fixing a plan for SQL statement in a hard way.
Till now we have seen that profiles can be created using DBMS_SQLTUNE package where
- we create a SQL tuning set
- Pack sql_ids into SQL tuning sets
- we create tuning task and provide SQL tuning set as inputs
- Finally after running tuning task we accept the profiles created.
Profiles consists of hints that a SQL can use it to arrive at a plan.
We can use same DBMS_SQLTUNE package and IMPORT_SQL_PROFILE procedure to import the required hints and apply to the SQL where we have to fix the plan.
Where are these hints stored ?
If you are taking about a sql, check V$SQL_PLAN, you will see a column OTHER_XML (CLOB). This has all the hints stored in XML form.
So logically if we have a good plan in any other database, we can just get the hints from that plan and use it in our database so that same plan comes in our database (provided we have all the objects used by that plan exists in our database).
This scenario happens many times. We have several similar production databases and sometimes plan is good in some DBs where as in few DBs plan is entirely different (may be because of different stats or different environment or some parameter difference).
Making changes to stats or parameters or DB environment has a very big impact as it affects other SQLs. So if you have 1 or 2 SQLs which are having bad plans, its better to gett a better plan from some other DB and just apply to the affected DBs.
Here is another way to create a profile.
If we have a better plan in some other DB, we need to parse the OTHER_XML column for the SQL and extract the hints from that
This can be done using following SQL
select CHR(9)||''''
||regexp_replace(extractvalue(value(d),'/hint'),'''','''''')
|| ''','
from xmltable('/*/outline_data/hint'
passing (select xmltype(other_xml) as xmlval
from v$sql_plan
where sql_id = '&GOOD_SQL_ID'
and CHILD_NUMBER = &CHILD_NO
and other_xml is not null)) d;
In my case sql_id = 33fndgzsas09k and child_no = 0
I got following output
'IGNORE_OPTIM_EMBEDDED_HINTS',
'OPTIMIZER_FEATURES_ENABLE(''11.2.0.2'')',
'DB_VERSION(''11.2.0.2'')',
'OPT_PARAM(''_b_tree_bitmap_plans'' ''false'')',
'OPT_PARAM(''_optim_peek_user_binds'' ''false'')',
'OPT_PARAM(''_optimizer_connect_by_cost_based'' ''false'')',
'OPT_PARAM(''optimizer_index_cost_adj'' 3)',
'OPT_PARAM(''optimizer_index_caching'' 80)',
'OUTLINE_LEAF(@"SEL$1")',
'INDEX_RS_ASC(@"SEL$1" "BI"@"SEL$1" ("TEST_ITEMS"."ISBN" "TEST_ITEMS"."TEST_ORDER_ID" "TEST_ITEMS"."OWNER"))',
'INDEX_RS_ASC(@"SEL$1" "PCS"@"SEL$1" ("TEST_CUSTS"."TEST_WORK_ID"))',
'INDEX(@"SEL$1" "SR"@"SEL$1" ("CUST_REQUESTS"."TEST_WORK_ID"))',
'LEADING(@"SEL$1" "BI"@"SEL$1" "PCS"@"SEL$1" "SR"@"SEL$1")',
'USE_NL(@"SEL$1" "PCS"@"SEL$1")',
'USE_NL(@"SEL$1" "SR"@"SEL$1")',
'NLJ_BATCHING(@"SEL$1" "SR"@"SEL$1")',
Above are the hints that used in the SQL. Note that these are not the sql hints that user supply. CBO internally creates a tree of hints (which we call a plan) and uses them to get the required data.
How do we make use of these hints ?
We will define a variable of type sys.sqlprof_attr (This is a VARRAY defined already in database) and put all the hints into this VARRAY
Once we get this VARRAY with all hints, we can apply the same to all SQLs that matches our criteria
Following PLSQL code will do that
declare
ar_profile_hints sys.sqlprof_attr;
begin
ar_profile_hints := sys.sqlprof_attr(
'BEGIN_OUTLINE_DATA',
'IGNORE_OPTIM_EMBEDDED_HINTS',
'OPTIMIZER_FEATURES_ENABLE(''11.2.0.2'')',
'DB_VERSION(''11.2.0.2'')',
'OPT_PARAM(''_b_tree_bitmap_plans'' ''false'')',
'OPT_PARAM(''_optim_peek_user_binds'' ''false'')',
'OPT_PARAM(''_optimizer_connect_by_cost_based'' ''false'')',
'OPT_PARAM(''optimizer_index_cost_adj'' 3)',
'OPT_PARAM(''optimizer_index_caching'' 80)',
'OUTLINE_LEAF(@"SEL$1")',
'INDEX_RS_ASC(@"SEL$1" "BI"@"SEL$1" ("TEST_ITEMS"."ISBN" "TEST_ITEMS"."TEST_ORDER_ID" "TEST_ITEMS"."OWNER"))',
'INDEX_RS_ASC(@"SEL$1" "PCS"@"SEL$1" ("TEST_CUSTS"."TEST_WORK_ID"))',
'INDEX(@"SEL$1" "SR"@"SEL$1" ("CUST_REQUESTS"."TEST_WORK_ID"))',
'LEADING(@"SEL$1" "BI"@"SEL$1" "PCS"@"SEL$1" "SR"@"SEL$1")',
'USE_NL(@"SEL$1" "PCS"@"SEL$1")',
'USE_NL(@"SEL$1" "SR"@"SEL$1")',
'NLJ_BATCHING(@"SEL$1" "SR"@"SEL$1")',
'END_OUTLINE_DATA');
for sql_rec in (
select t.sql_id, t.sql_text
from dba_hist_sqltext t, dba_hist_sql_plan p
where t.sql_id = p.sql_id
and p.sql_id = '&SQL_ID_TO_FIX'
and p.plan_hash_value = &BAD_PLAN_HASH_VALUE
and p.parent_id is null
)
loop
DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
sql_text => sql_rec.sql_text,
profile => ar_profile_hints,
name => 'PROFILE_33fndgzsas09k');
end loop;
end;
/
You can run above PLSQL code in a database where plan is bad and it should create a profile for that SQL. Once profile is created you can purge that SQL from shared_pool so that existing plan will be wiped off and run the SQL again.
This will create a new plan (good plan) same as the one we copied from other DB.
Above PLSQL code can generated directly using following SQL
Run the following SQL in database where plan is good and provide required inputs. This will output PLSQL code same as above (with all values in place). You can just copy and paste that PLSQL code on affected DB where plan is bad.
*** NOTE :- You need to run below SQL code in DB where plan is good.
accept HINTED_SQL_ID prompt 'Enter good SQL ID:- '
accept CHILD_NO prompt 'Enter child number of good SQL:- '
accept BAD_SQL_ID prompt 'Enter bad SQL ID to be fixed:- '
accept PLAN_HASH_VALUE prompt 'Enter bad SQL plan_hash_value:- '
set pagesize 0
set line 9999
set verify off;
set heading off;
set feedback off;
set echo off;
set pagesize 0
prompt '======================= OUTPUT ======================='
select CHR(10) from dual;
select 'declare'
||CHR(10)||CHR(9)
||'ar_profile_hints sys.sqlprof_attr;'
||CHR(10)
||'begin'||CHR(10)||CHR(9)
||'ar_profile_hints := sys.sqlprof_attr('||CHR(10)||CHR(9)
||'''BEGIN_OUTLINE_DATA'','
from dual;
select CHR(9)||''''
||regexp_replace(extractvalue(value(d),'/hint'),'''','''''')
|| ''','
from xmltable('/*/outline_data/hint'
passing (select xmltype(other_xml) as xmlval
from v$sql_plan
where sql_id = '33fndgzsas09k'
and CHILD_NUMBER = 0
and other_xml is not null)) d;
select CHR(9)
||'''END_OUTLINE_DATA'');'||CHR(10)||CHR(9)
||'for sql_rec in ('||CHR(10)||CHR(9)
||'select t.sql_id, t.sql_text'||CHR(10)||CHR(9)
||'from dba_hist_sqltext t, dba_hist_sql_plan p'||CHR(10)||CHR(9)
||'where t.sql_id = p.sql_id'||CHR(10)||CHR(9)
||'and p.sql_id = '''||'&BAD_SQL_ID'||'''' ||CHR(10)||CHR(9)
||'and p.plan_hash_value = '||&PLAN_HASH_VALUE ||CHR(10)||CHR(9)
||'and p.parent_id is null'||CHR(10)||') loop' ||CHR(10)
||'DBMS_SQLTUNE.IMPORT_SQL_PROFILE(' ||CHR(10)||CHR(9)
||'sql_text => sql_rec.sql_text,'||CHR(10)||CHR(9)
||'profile => ar_profile_hints,' ||CHR(10)||CHR(9)
||'name => ''PROFILE_'||'&BAD_SQL_ID'||'''); '||CHR(10)||CHR(9)
|| 'end loop;'||CHR(10)|| 'end;'|| CHR(10)||'/'
from dual;
select CHR(10) from dual;
prompt '======================= OUTPUT ======================='
Enter good SQL ID:- <This should be the SQL_ID with good plan. In our case its the same SQL ID but in different DB>
Enter child number of hinted SQL:- <This is the child number of the SQL in DB where plan is good>
Enter bad SQL ID to be fixed:- <This is same SQL ID as input 1 since we are checking plan for same SQL ID in other DB>
Enter bad SQL plan_hash_value:- <This is the plan hash value of SQL ID where plan is bad>
Example Run:
SQL>@create_profile_code.sql
Enter good SQL ID:- 33fndgzsas09k
Enter child number of good SQL:- 0
Enter bad SQL ID to be fixed:- 33fndgzsas09k
Enter bad SQL plan_hash_value:- 3225275398
'======================= OUTPUT ======================='
declare
ar_profile_hints sys.sqlprof_attr;
begin
ar_profile_hints := sys.sqlprof_attr(
'BEGIN_OUTLINE_DATA',
'IGNORE_OPTIM_EMBEDDED_HINTS',
'OPTIMIZER_FEATURES_ENABLE(''11.2.0.2'')',
'DB_VERSION(''11.2.0.2'')',
'OPT_PARAM(''_b_tree_bitmap_plans'' ''false'')',
'OPT_PARAM(''_optim_peek_user_binds'' ''false'')',
'OPT_PARAM(''_optimizer_connect_by_cost_based'' ''false'')',
'OPT_PARAM(''optimizer_index_cost_adj'' 3)',
'OPT_PARAM(''optimizer_index_caching'' 80)',
'OUTLINE_LEAF(@"SEL$1")',
'INDEX_RS_ASC(@"SEL$1" "BI"@"SEL$1" ("TEST_ITEMS"."ISBN" "TEST_ITEMS"."TEST_ORDER_ID" "TEST_ITEMS"."OWNER"))',
'INDEX_RS_ASC(@"SEL$1" "PCS"@"SEL$1" ("TEST_CUSTS"."TEST_WORK_ID"))',
'INDEX(@"SEL$1" "SR"@"SEL$1" ("CUST_REQUESTS"."TEST_WORK_ID"))',
'LEADING(@"SEL$1" "BI"@"SEL$1" "PCS"@"SEL$1" "SR"@"SEL$1")',
'USE_NL(@"SEL$1" "PCS"@"SEL$1")',
'USE_NL(@"SEL$1" "SR"@"SEL$1")',
'NLJ_BATCHING(@"SEL$1" "SR"@"SEL$1")',
'END_OUTLINE_DATA');
for sql_rec in (
select t.sql_id, t.sql_text
from dba_hist_sqltext t, dba_hist_sql_plan p
where t.sql_id = p.sql_id
and p.sql_id = '33fndgzsas09k'
and p.plan_hash_value = 3225275398
and p.parent_id is null
) loop
DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
sql_text => sql_rec.sql_text,
profile => ar_profile_hints,
name => 'PROFILE_33fndgzsas09k');
end loop;
end;
/
'======================= OUTPUT ======================='
Hope this helps !!
Persistent Storage Solutions 