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.
