Posts Tagged ‘dbms_spm’
How to tune SQL statement without changing text – Plan baseline version
One of the common requests that embarasses DBAs or developers is as following
I need to tune the SQL statement. But I can’t modify the SQL text because it’s embedded in the software
Quite familiar, huh?
Many people had shared their techniques, and now we have a couple of proven solutions.
- Controlling the statistics – gathering or setting
- Editing or switching the stored outlines
- Importing the SQL profile (10g)
Outline switching and SQL profile importing are very powerful techniqiues and well described here.
http://sites.google.com/site/ukja/books-cbo-in-depth/chapter7/06–stored-outlines-vs-sql-profile-sql
Anyway, this blog post helped me realize that Oracle 11’s proud-new-feature SQL plan management(a.k.a SPM) also has a similiar mechanism – manually changing plan without chaing SQL text.
Following simple test case would help you to understand what I mean.
— Oracle version is 11.1.0.6
UKJA@ukja116> select * from v$version; BANNER ------------------------------------------------------------------------ Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production PL/SQL Release 11.1.0.6.0 - Production CORE 11.1.0.6.0 Production TNS for 32-bit Windows: Version 11.1.0.6.0 - Production NLSRTL Version 11.1.0.6.0 - Production Elapsed: 00:00:00.04
— Create tables and indexes
UKJA@ukja116> create table t1(c1, c2) 2 as 3 select level, level from dual connect by level <= 100000 4 ; Table created. Elapsed: 00:00:00.45 UKJA@ukja116> UKJA@ukja116> create index t1_n1 on t1(c1) 2 ; Index created. Elapsed: 00:00:00.21 UKJA@ukja116> UKJA@ukja116> @gather t1 UKJA@ukja116> exec dbms_stats.gather_table_stats(user, '&1', no_invalidate=>false); PL/SQL procedure successfully completed. Elapsed: 00:00:00.70 UKJA@ukja116>
— Capture the plan baseline
— Note the ugly FULL hint
UKJA@ukja116> alter session set optimizer_capture_sql_plan_baselines = true; Session altered. Elapsed: 00:00:00.00 UKJA@ukja116> UKJA@ukja116> select /*+ full(t1) */ * from t1 where c1 = 1; C1 C2 ---------- ---------- 1 1 Elapsed: 00:00:00.04 UKJA@ukja116> select /*+ full(t1) */ * from t1 where c1 = 1; C1 C2 ---------- ---------- 1 1 Elapsed: 00:00:00.01 UKJA@ukja116> UKJA@ukja116> alter session set optimizer_capture_sql_plan_baselines = false; Session altered. Elapsed: 00:00:00.00 UKJA@ukja116>
— We have SQL plan baseline captured and accepted
UKJA@ukja116> col sql_handle new_value v_sql_handle UKJA@ukja116> UKJA@ukja116> select sql_handle from dba_sql_plan_baselines 2 where sql_text like 'select /*+ full(t1) */ * from t1 where c1 = 1%' 3 ; SQL_HANDLE ------------------------------------------------------------------------------------------ SYS_SQL_8bab62f0bc1db3bc Elapsed: 00:00:00.00 UKJA@ukja116> UKJA@ukja116> UKJA@ukja116> set serveroutput on UKJA@ukja116> exec print_table('select * from dba_sql_plan_baselines where sql_handle = ''&v_sql_handle'''); SIGNATURE : 10064246578377241532 SQL_HANDLE : SYS_SQL_8bab62f0bc1db3bc SQL_TEXT : select /*+ full(t1) */ * from t1 where c1 = 1 PLAN_NAME : SYS_SQL_PLAN_bc1db3bcdbd90e8e CREATOR : UKJA ORIGIN : AUTO-CAPTURE PARSING_SCHEMA_NAME : UKJA DESCRIPTION : VERSION : 11.1.0.6.0 CREATED : 03-FEB-09 03.34.12.000000 PM LAST_MODIFIED : 03-FEB-09 03.34.12.000000 PM LAST_EXECUTED : LAST_VERIFIED : ENABLED : YES ACCEPTED : YES FIXED : NO AUTOPURGE : YES OPTIMIZER_COST : 58 MODULE : SQL*Plus ACTION : EXECUTIONS : 0 ELAPSED_TIME : 0 CPU_TIME : 0 BUFFER_GETS : 0 DISK_READS : 0 DIRECT_WRITES : 0 ROWS_PROCESSED : 0 FETCHES : 0 END_OF_FETCH_COUNT : 0 ----------------- PL/SQL procedure successfully completed. Elapsed: 00:00:00.03 UKJA@ukja116> set serveroutput off UKJA@ukja116> UKJA@ukja116>
–– Now I want to the change the original SQL to following one
UKJA@ukja116> col sql_id new_value v_sql_id
UKJA@ukja116> col plan_hash_value new_value v_plan_hash_value
UKJA@ukja116>
UKJA@ukja116> select /*+ index(t1) */ * from t1 where c1 = 1
2 ;
C1 C2
---------- ----------
1 1
Elapsed: 00:00:00.01
— But How? How can we change the “select /+ full(t1) */ …” sql to scan index not table?
— dbms_spm.load_plans_from_cursor_cache is the key
UKJA@ukja116> select sql_id, plan_hash_value from v$sql 2 where sql_text like 'select /*+ index(t1) */ * from t1 where c1 = 1%' 3 ; SQL_ID PLAN_HASH_VALUE --------------------------------------- --------------- ftm2rdbpvw96f 1420382924 Elapsed: 00:00:00.01 UKJA@ukja116> UKJA@ukja116> UKJA@ukja116> var ret number; UKJA@ukja116>
— We can load the plan of “select /*+ index(t1) */ …” statement
— to the plan baseline of the “select /*+ full(t1) */ …” statement.
— This is the key trick
UKJA@ukja116> exec :ret := dbms_spm.load_plans_from_cursor_cache(- sql_id=>'&v_sql_id', - plan_hash_value=>&v_plan_hash_value, - sql_handle=>'&v_sql_handle'); PL/SQL procedure successfully completed. Elapsed: 00:00:00.01 UKJA@ukja116> UKJA@ukja116>
— Now, Oracle has 2 plan baseline
UKJA@ukja116> select sql_handle, sql_text from dba_sql_plan_baselines 2 where sql_text like 'select /*+ full(t1) */ * from t1 where c1 = 1%' 3 ; SQL_HANDLE ------------------------------------------------------------------------------------------ SQL_TEXT -------------------------------------------------------------------------------- SYS_SQL_8bab62f0bc1db3bc select /*+ full(t1) */ * from t1 where c1 = 1 SYS_SQL_8bab62f0bc1db3bc select /*+ full(t1) */ * from t1 where c1 = 1 Elapsed: 00:00:00.01 UKJA@ukja116> UKJA@ukja116>
— We can display the registered plans for plan history using dbms_xplan.display_sql_plan_baseline function
UKJA@ukja116> select * from table(dbms_xplan.display_sql_plan_baseline('&v_sql_handle')); old 1: select * from table(dbms_xplan.display_sql_plan_baseline('&v_sql_handle')) new 1: select * from table(dbms_xplan.display_sql_plan_baseline('SYS_SQL_8bab62f0bc1db3bc')) PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------- SQL handle: SYS_SQL_8bab62f0bc1db3bc SQL text: select /*+ full(t1) */ * from t1 where c1 = 1 -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- Plan name: SYS_SQL_PLAN_bc1db3bc750635c2 Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD -------------------------------------------------------------------------------- Plan hash value: 1420382924 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 9 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 9 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | T1_N1 | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("C1"=1) -------------------------------------------------------------------------------- Plan name: SYS_SQL_PLAN_bc1db3bcdbd90e8e Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE -------------------------------------------------------------------------------- Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 9 | 59 (6)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T1 | 1 | 9 | 59 (6)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("C1"=1) 44 rows selected. Elapsed: 00:00:00.15 UKJA@ukja116> UKJA@ukja116>
— How about the plan? With the plan baseline off, we have following plan.
UKJA@ukja116> explain plan for 2 select /*+ full(t1) */ * from t1 where c1 = 1 3 ; Explained. Elapsed: 00:00:00.01 UKJA@ukja116> UKJA@ukja116> @plan UKJA@ukja116> select * from table(dbms_xplan.display) 2 / PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 9 | 59 (6)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T1 | 1 | 9 | 59 (6)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("C1"=1) 13 rows selected. Elapsed: 00:00:00.04 UKJA@ukja116> UKJA@ukja116>
— What if sql plan baseline is used?
UKJA@ukja116> alter session set optimizer_use_sql_plan_baselines = true; Session altered. Elapsed: 00:00:00.00 UKJA@ukja116> UKJA@ukja116> explain plan for 2 select /*+ full(t1) */ * from t1 where c1 = 1 3 ; Explained. Elapsed: 00:00:00.00 UKJA@ukja116> UKJA@ukja116> UKJA@ukja116> @plan UKJA@ukja116> select * from table(dbms_xplan.display) 2 / PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 9 | 59 (6)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T1 | 1 | 9 | 59 (6)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("C1"=1) Note ----- - SQL plan baseline "SYS_SQL_PLAN_bc1db3bcdbd90e8e" used for this statement 17 rows selected. Elapsed: 00:00:00.06 UKJA@ukja116> UKJA@ukja116> alter session set optimizer_use_sql_plan_baselines = false; Session altered. Elapsed: 00:00:00.00
— Hm… the original plan is still chosen even if there is a better plan with lower cost
— Now I’m going to disable the original plan using dbms_spm.alter_sql_plan_baseline
UKJA@ukja116> UKJA@ukja116> -- disable the current plan UKJA@ukja116> exec :ret := dbms_spm.alter_sql_plan_baseline('&v_sql_handle', '&org_plan_name', 'enabled', 'NO'); Enter value for org_plan_name: SYS_SQL_PLAN_bc1db3bcdbd90e8e PL/SQL procedure successfully completed. Elapsed: 00:00:00.01
— Now, new plan is chosen.
UKJA@ukja116> alter session set optimizer_use_sql_plan_baselines = true; Session altered. Elapsed: 00:00:00.00 UKJA@ukja116> UKJA@ukja116> explain plan for 2 select /*+ full(t1) */ * from t1 where c1 = 1 3 ; Explained. Elapsed: 00:00:00.09 UKJA@ukja116> UKJA@ukja116> @plan UKJA@ukja116> select * from table(dbms_xplan.display) 2 / PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- Plan hash value: 1420382924 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 9 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 9 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | T1_N1 | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("C1"=1) Note ----- - SQL plan baseline "SYS_SQL_PLAN_bc1db3bc750635c2" used for this statement 18 rows selected. Elapsed: 00:00:00.07 UKJA@ukja116> UKJA@ukja116> alter session set optimizer_use_sql_plan_baselines = false; Session altered. Elapsed: 00:00:00.01 UKJA@ukja116> UKJA@ukja116> UKJA@ukja116> -- drop sql plan baseline UKJA@ukja116> exec :ret := dbms_spm.drop_sql_plan_baseline('&v_sql_handle'); PL/SQL procedure successfully completed. Elapsed: 00:00:00.01
Above test case cleary shows that manual loading of plan baseline from cursor cache is another powerful techique which enables us to tune the unmodifiable SQL.
I hope that this post could be one of the valuable tips for those who plan to adopt SPM on Oracle 11g database.