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.
So that index hint helped? i see time increased ????