How to use hinted SQL to fix bad SQL plan
December 30, 2013 1 Comment
As I said in a previously blog Outline, SQL profile and SQL Plan Management(SPM), outline is the oldest approach to fix SQL plan, but it’s the easiest one to use, and works for all oracle version, and do NOT need extra license.
I’d like to elaborate the most frequently used scenario: a SQL is using a bad SQL plan. We can use SQL hints to get a GOOD plan. We want to use outline to fix the SQL plan to the GOOD one. In another word, we want to use a SQL plan with different SQL text(hints) but logically the same.
Let’s create an test scenario:
1. Create a test table
ALEX@ZENG: SQL> create table t (id number, name varchar2(100));
Table created.
ALEX@ZENG: SQL> insert into t select 1, object_name from dba_objects;
74562 rows created.
ALEX@ZENG: SQL> create index t_idx1 on t(id);
Index created.
ALEX@ZENG: SQL> exec dbms_stats.gather_table_stats('ALEX','T');
PL/SQL procedure successfully completed.
ALEX@ZENG: SQL> show parameter optimizer_features_enable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_features_enable string 10.2.0.4
2. Create the bad SQL plan scenario
ALEX@ZENG: SQL> update t set id=rownum;
74563 rows updated.
ALEX@ZENG: SQL> commit;
Commit complete.
ALEX@ZENG: SQL> set autotrace traceonly
ALEX@ZENG: SQL> select * from t where id=1;
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2153619298
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 74556 | 1820K| 74 (5)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 74556 | 1820K| 74 (5)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
319 consistent gets
0 physical reads
0 redo size
593 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
3. Use hint to get a better plan
--it's better to use index scan because there's only one row with id=1 now
--We can get the plan by adding hint
ALEX@ZENG: SQL> explain plan for select /*+ index(t t_idx1) */ * from t where id=1;
Explained.
ALEX@ZENG: SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3292636276
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 74556 | 1820K| 150 (2)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 74556 | 1820K| 150 (2)| 00:00:02 |
|* 2 | INDEX RANGE SCAN | T_IDX1 | 74556 | | 55 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1)
14 rows selected.
Since INDEX RANGE SCAN is better plan, we want to use it to replace the FULL TABLE SCAN plan.
Step 1. Get the bad SQL hash value
ALEX@ZENG: SQL> select address, hash_value, sql_text from v$sql where sql_text like 'select * from t%'; ADDRESS HASH_VALUE SQL_TEXT ---------------- ---------- ------------------------------------------------------------ 000000045F900938 716428968 select * from t where id=1 ...
Step 2. Create a SQL file with hints
$ cat good.sql select /*+ index(t t_idx1) */ * from t where id=1; $
Step 3. Fix the SQL plan using script fix_plan.sh
#!/bin/bash
if [ $# -lt 3 ]; then
cat <<EOF
Fix given SQL plan using given sql in file
usage: fix_plan.sh <hash_value> <hinted_sql_file> <owner>
examples: fix_plan.sh 716428968 good.sql ALEX
EOF
exit 1
fi
HASH_VALUE=$1
SQL_FILE=$2
OL_OWNER=$3
echo "HASH_VALUE : $HASH_VALUE"
echo "SQL_FILE : $SQL_FILE"
echo "OL_OWNER : $OL_OWNER"
echo ""
#Create outline by hash_value
function create_ol_from_hashvalue {
HASH_VALUE=$1
OL_NAME=$2
OL_OWNER=$3
#generate create outline sql
#I didn't use dbms_outln.create_outline, because it cannot create given name outline
# and there's no hash value in V$SQL and DBA_OUTLINES to associate the two
# according to "How To Match a SQL Statement to a Stored Outline (Doc ID 743312.1)"
sqlplus -S "/ as sysdba" > /tmp/tmp_$OL_NAME.sql <<EOF
set feedback off
set serveroutput on size unlimited
declare
v_sqltext varchar2(32000);
begin
--get sql text
select dbms_lob.substr(SQL_FULLTEXT, 30000, 1 ) into v_sqltext from v\$sql where hash_value = $HASH_VALUE and rownum=1;
dbms_output.put_line('alter session set current_schema = $OL_OWNER;');
v_sqltext := 'create or replace outline $OL_NAME for category TEMP_PLAN on ' || chr(10) || v_sqltext || chr(10) ||';';
dbms_output.put_line(v_sqltext);
dbms_output.put_line('exit;');
end;
/
EOF
sqlplus -S "/ as sysdba" @/tmp/tmp_$OL_NAME.sql
}
#Create outline from sql file
function create_ol_from_sqlfile {
SQL_FILE=$1
OL_NAME=$2
OL_OWNER=$3
#generate create outline sql
cat > /tmp/tmp_$OL_NAME.sql <<EOF
alter session set current_schema = $OL_OWNER;
create or replace outline $OL_NAME for category TEMP_PLAN on
`cat $SQL_FILE`
exit;
EOF
sqlplus -S "/ as sysdba" @/tmp/tmp_$OL_NAME.sql
}
#Exchange outlines, make GOOD_SQL plan to GOOD_PLAN category
#Ref: How to Edit a Stored Outline to Use the Plan from Another Stored Outline (Doc ID 730062.1)
function exchange_outline {
OL1=$1
OL2=$2
OL_OWNER=$3
sqlplus -S "/ as sysdba" <<EOF
set feedback off
alter session set current_schema = $OL_OWNER;
create private outline OLFROM from $OL1;
create private outline OLTO from $OL2;
update ol$ set hintcount=(select hintcount from ol$ where ol_name='OLTO') where ol_name='OLFROM';
delete from ol$ where ol_name='OLTO';
update ol$ set ol_name='OLTO' where ol_name='OLFROM';
commit;
execute dbms_outln_edit.refresh_private_outline('OLTO');
create or replace outline $OL1 from private OLTO for category GOOD_PLAN;
drop outline $OL2;
exit;
EOF
}
#display outline
function display_outline {
OL_NAME=$1
OL_OWNER=$2
sqlplus -S "/ as sysdba" <<EOF
set pagesize 1000 linesize 160
set long 32000
col hint format a55
col join_pos format a45
col owner format a12
col name format a18
col ts format a14
col h format 999
col category format a12
col sql_text format a80
col used format a6
select name, sql_text, category, used, to_char(TIMESTAMP, 'YY-mm-dd hh24:MI')
from dba_outlines
where name = '$OL_NAME' and OWNER = '$OL_OWNER';
select ol_name name, category, hint#, stage# stage, hint_text hint, join_pred join_pos
from outln.ol\$hints
where ol_name = '$OL_NAME'
order by ol_name, hint#;
exit;
EOF
}
#main function
echo "1. Create outline OL_$HASH_VALUE for SQL $HASH_VALUE"
create_ol_from_hashvalue $HASH_VALUE OL_$HASH_VALUE $OL_OWNER
echo "2. Create outline OL_TEMP for SQL in $SQL_FILE"
create_ol_from_sqlfile $SQL_FILE OL_TEMP $OL_OWNER
echo "3. Exchange outline OL_$HASH_VALUE with OL_TEMP, and drop OL_TEMP"
exchange_outline OL_$HASH_VALUE OL_TEMP $OL_OWNER
echo "4. Display final outline for SQL $HASH_VALUE : OL_$HASH_VALUE in category GOOD_PLAN "
display_outline OL_$HASH_VALUE $OL_OWNER
Run it:
$./fix_plan.sh 716428968 good.sql ALEX
HASH_VALUE : 716428968
SQL_FILE : good.sql
OL_OWNER : ALEX
1. Create outline OL_716428968 for SQL 716428968
Session altered.
Outline created.
2. Create outline OL_TEMP for SQL in good.sql
Session altered.
Outline created.
3. Exchange outline OL_716428968 with OL_TEMP, and drop OL_TEMP
4. Display final outline for SQL 716428968 : OL_716428968 in category GOOD_PLAN
NAME SQL_TEXT CATEGORY USED TO_CHAR(TIMEST
------------------ -------------------------------------------------------------------------------- ------------ ------ --------------
OL_716428968 select * from t where id=1 GOOD_PLAN UNUSED 13-12-29 19:39
1 row selected.
NAME CATEGORY HINT# STAGE HINT JOIN_POS
------------------ ------------ ---------- ---------- ------------------------------------------------------- ---------------------------------------------
OL_716428968 GOOD_PLAN 1 1 OUTLINE_LEAF(@"SEL$1")
OL_716428968 GOOD_PLAN 2 1 IGNORE_OPTIM_EMBEDDED_HINTS
OL_716428968 GOOD_PLAN 3 1 OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
OL_716428968 GOOD_PLAN 4 1 DB_VERSION('11.2.0.2')
OL_716428968 GOOD_PLAN 5 1 OPT_PARAM('_b_tree_bitmap_plans' 'false')
OL_716428968 GOOD_PLAN 6 1 OPT_PARAM('_index_join_enabled' 'false')
OL_716428968 GOOD_PLAN 7 1 OPT_PARAM('_optim_peek_user_binds' 'false')
OL_716428968 GOOD_PLAN 8 1 OPT_PARAM('_optimizer_skip_scan_enabled' 'false')
OL_716428968 GOOD_PLAN 9 1 OPT_PARAM('optimizer_index_cost_adj' 30)
OL_716428968 GOOD_PLAN 10 1 INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."ID"))
10 rows selected.
Step 4. Flush the bad SQL plan
--if it's 10g, use grant to invalidate the SQL plan
SQL> grant select on ALEX.T to dba;
--if it's 11g, use procedure DBMS_SHARED_POOL.PURGE
SQL> select ADDRESS, HASH_VALUE from V$SQL where hash_value = 716428968;
ADDRESS HASH_VALUE
---------------- ----------
000000045F900938 716428968
1 row selected.
SQL> exec DBMS_SHARED_POOL.PURGE ('000000045F900938, 716428968','C');
PL/SQL procedure successfully completed.
Step 5. Check the new plan
--it's IMPORTANT to set use_stored_outlines to use the stored outline category if not yet
--You can set it at session level and test the plan first if needed
SQL> alter system set use_stored_outlines = GOOD_PLAN;
System altered.
ALEX@ZENG: SQL> set autotrace traceonly
ALEX@ZENG: SQL> select * from t where id=1;
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3292636276
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 27 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 27 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX1 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1)
Note
-----
- outline "OL_716428968" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
185 consistent gets
0 physical reads
0 redo size
597 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
There is a problem caused by “ghost” parameter use_stored_outlines. Why?
You cannot set it in inital file, and you cannot see it after setting it, and it’s lost its value after DB restart.
So we need to create a trigger to set it automatically when DB start:
SYS@ZENG: SQL> show parameter use_stored_outlines
SYS@ZENG: SQL>
SYS@ZENG: SQL> create or replace trigger enable_outlines_trig
--Ref : How to Enable USE_STORED_OUTLINES Permanently (Doc ID 560331.1)
after startup on database
begin
execute immediate('alter system set use_stored_outlines=GOOD_PLAN');
end;
/
Trigger created.
Until then, you can have a good sleep without worry about the bad SQL plan causes DB high session/load.
Alex Zeng's Blog 