PLAN STABILISATION

Transfer outlines from Patched to Original SQL

Check that outlines are used

Procedure to stabilize a query with binds variables:

Introduction:

A query in the system has been found varying its execution plan despite an existing SQL profile on it. The good plan uses index MSG1 while the bad one uses MSG3:

Presentation of the 2 plans

Good plan:

Index MSG1 with gets 11.7 per execution :

Stat Name Statement Per Execution % Snap

---------------------------------------- ---------- -------------- -------

Elapsed Time (ms) 369 0.6 0.1

CPU Time (ms) 354 0.6 0.2

Executions 634 N/A N/A

Buffer Gets 7,441 11.7 0.2

Disk Reads 42 0.1 0.0

Parse Calls 0 0.0 0.0

Rows 634 1.0 N/A

User I/O Wait Time (ms) 209 N/A N/A

Cluster Wait Time (ms) 0 N/A N/A

Application Wait Time (ms) 0 N/A N/A

Concurrency Wait Time (ms) 0 N/A N/A

Invalidations 0 N/A N/A

Version Count 5 N/A N/A

Sharable Mem(KB) 87 N/A N/A

-------------------------------------------------------------

Execution Plan

------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 13 (100)| | | |

| 1 | PARTITION RANGE ITERATOR | | 1 | 421 | 13 (8)| 00:00:01 | 22 | KEY |

| 2 | SORT ORDER BY | | 1 | 421 | 13 (8)| 00:00:01 | | |

| 3 | TABLE ACCESS BY LOCAL INDEX ROWID| MSG | 1 | 421 | 12 (0)| 00:00:01 | 22 | KEY |

| 4 | INDEX RANGE SCAN | MSG1 | 1 | | 12 (0)| 00:00:01 | 22 | KEY |

------------------------------------------------------------------------------------------------------------

Bad plan

Index MSG3 with 14 442 gets per execution:

Stat Name Statement Per Execution % Snap

---------------------------------------- ---------- -------------- -------

Elapsed Time (ms) 685,160 88.9 61.8

CPU Time (ms) 520,984 67.6 63.1

Executions 7,703 N/A N/A

Buffer Gets ########## 14,442.2 98.1

Disk Reads 0 0.0 0.0

Parse Calls 0 0.0 0.0

Rows 2,909 0.4 N/A

User I/O Wait Time (ms) 0 N/A N/A

Cluster Wait Time (ms) 0 N/A N/A

Application Wait Time (ms) 0 N/A N/A

Concurrency Wait Time (ms) 5,441 N/A N/A

Invalidations 0 N/A N/A

Version Count 5 N/A N/A

Sharable Mem(KB) 79 N/A N/A

-------------------------------------------------------------

Execution Plan

-----------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 2 (100)| | | |

| 1 | PARTITION RANGE ITERATOR | | 1 | 419 | 2 (0)| 00:00:01 | 16 | KEY |

| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| MSG | 1 | 419 | 2 (0)| 00:00:01 | 16 | KEY |

| 3 | INDEX RANGE SCAN DESCENDING | MSG3 | 1 | | 1 (0)| 00:00:01 | 16 | KEY |

Stored outlines:

Stored outline is the oracle mean to enforce the execution of an sql without altering the text of the SQL. Usually you do this using Hints but this alter the text of the SQL and above all requires access to the code of the client that will issue the SQL. SQL profile adds only supplemental information for the SQL but does not guaranteed that the execution plan will never change anymore.

While Stored outlines seems to be the solution to enforce execution stability, there are some drawback :

-In order to have all new sessions using stored outlines you need to tell the system explicitly to use stored outlines :

alter system set use_stored_outlines=TRUE;

But this setting is lost on reboot, so you need to reload this after every boot.

-If an index is invalidated, the stored outlines will no work.

That’s normal. But when the index is revalidated, the system will pick a new execution plan.

To have your stored outline used again, you need to flush the shared pool again.

First work:

We start by retrieving the hash_value for the SQL: it is 2071614804. The child cursor for this hash_value is 0

We ask now to Smenu to create now an outline for SQL hash value 2071614804, child number 0 and we will put this into category BPADEV_OL

SQL> sx –cro 2071614804 –c 0 -cat BPADEV_OL

We can now see the outline using the ‘sx’ shortcut:

[LinuxT01:ORA10G3]:> sx -lc

MACHINE LinuxT01.unix.b - ORACLE_SID : ORA10G3

Date - Wednesday 05th December 2007 12:33:35

Username - SYS

how explain plan for query hash_value :

OWNER NAME CATEGORY ENABLED USED HASH_VALUE Sql Text

------ ------------------------------ --------- ------- ------ ---------- -----------------------------

BPADEV SYS_OUTLINE_07120410332582236 BPADEV_OL ENABLED UNUSED 2071614804 SELECT MSG.MSG_id, MSG.version_nbr, MSG

The outlines can be seen using the outline name that was given by the previous command:

Outlines for the Original SQL:

[LinuxT01:ORA10G3]:> sx -ln SYS_OUTLINE_07120410332582236

MACHINE LinuxT01.unix.b - ORACLE_SID : ORA10G3

Date - Wednesday 05th December 2007 12:26:32

Username - SYS

HINT# HINT_TEXT STAGE# TABLE_NAME pos COST CARDINALITY BYTES

----- -------------------------------------------------- ------ ------------ ----- ---------- ----------- ----

1 OUTLINE_LEAF(@"SEL$1") 1 0 0 0 0

2 ALL_ROWS 1 0 0 0 0

3 OPT_PARAM('optimizer_index_caching' 50) 1 0 0 0 0

4 OPT_PARAM('star_transformation_enabled' 'true') 1 0 0 0 0

5 IGNORE_OPTIM_EMBEDDED_HINTS 1 0 0 0 0

6 OPTIMIZER_FEATURES_ENABLE('10.2.0.3') 1 0 0 0 0

7 OPT_PARAM('optimizer_index_cost_adj' 50) 1 0 0 0 0

8 INDEX_RS_ASC(@"SEL$1" "MSG"@"SEL$1" "MSG"."END_TIME" 1 MSG 1 1.5 1 126

"MSG"."END_TIME_MS"))

Line 8 shows the index alias (SEL$1) and the index column signature is: "MSG"."END_TIME” "MSG"."END_TIME_MS"

A quick look at the indexes of table MSG reveal that this column signature is index MSG3:

Table Index Column Clust Distinct

Name Name U Name Factor cf(%) Keys NUM_ROWS Size(m) S P

---------------------- ------------ - -------------------- ---------- ------ ---------- ---------- -------- - --

MSG MSG1 N EXT_MSG_ID 472753 90.4 522857 522947 48 N 1

END_TIME 472753 90.4 522857 522947 48 N 1

END_TIME_MS 472753 90.4 522857 522947 48 N 1

MSG1_ACQ1 N ACQ_CNTRCT_NBR 350878 72.0 351 487043 15 N 1

MSG1_ACQ2 N EXT_TERM_ID 19578 3.7 2 526399 18 N 1

PARAM23 19578 3.7 2 526399 18 N 1

PARAM22 19578 3.7 2 526399 18 N 1

MSG1_CLEAR N CLEAR_STATUS 24794 6.7 3 370314 10 N 1

CLEAR_PROF_ID 24794 6.7 3 370314 10 N 1

MSG1_IPS N CON_CNTRCT_NBR 0 0.0 0 0 0 N 1

MSG1_ISS N PLASTIC_ID 0 0.0 0 0 0 N 1

MSG1_REPLI N REPLI_STATUS 459173 80.4 564191 571330 16 N 1

END_TIME 459173 80.4 564191 571330 16 N 1

END_TIME_MS 459173 80.4 564191 571330 16 N 1

MSG1_ZVT N EXT_TERM_ID 441132 76.2 571552 578660 29 N 1

BOOKING_NBR 441132 76.2 571552 578660 29 N 1

END_TIME 441132 76.2 571552 578660 29 N 1

END_TIME_MS 441132 76.2 571552 578660 29 N 1

MSG2 N CARD_NBR 88340 15.5 25 570465 24 N 1

MSG3 N END_TIME 456081 80.2 561309 568439 15 N 1

END_TIME_MS 456081 80.2 561309 568439 15 N 1

MSG4 N RB_STATUS 18924 3.3 3 568439 11 N 1

MSG7 N BSNS_CASE_ID 454070 79.9 394649 568468 18 N 1

MSG_PK U MSG_ID 580399 100.0 580410 580410 29 N 1

END_TIME 580399 100.0 580410 580410 29 N 1

Outlines forthe Patched SQL:

Until now we have only identified the hash_value and child number of our production query and generate an stored outline for the current (bad) execution plan. Now we will add a hint to the SQL to that to reflect the execution plan we want to see. Next we run this patched SQL, generates a stored outline for this new SQL. At this point we will transfer the stored outlines from the patched SQL into the original SQL. The system will use this injected execution plan in place of the original execution plan.

Now let’s generate this new execution plan.

First difficulty, our query contains bind variables and among them bind of type date. Our goal is to not alter the text of the SQL so we will create a wrapper around the SQL to set the bind variables. Note : We could have directly replace the binds with fixed values, submit this to the optimizer and retrieve the plan to inject into the original SQL. But as first example we opt to change as few as possible:

Here is out SQL text :

SELECT

MSG.MSG_id, MSG.version_nbr, MSG.rb_status, MSG.folup_status, MSG.voice_status, MSG.conf_status,

MSG.rev_status, MSG.capture_loc, MSG.clear_status, MSG.partner_MSG_id2, MSG.partner_MSG_id4,

MSG.amount, MSG.bill_amount, MSG.object, MSG.object_modifier, MSG.pci_crypto_key_id,

MSG.storage_result

FROM MSG

WHERE

((((((MSG.storage_result = 'F') AND (MSG.ext_MSG_id = :f1 )) AND (MSG.rb_status <> 'S')) AND (MSG.retry_counter IS NULL)) AND (MSG.rev_status IS NOT NULL)) AND (MSG.end_time >= :f2 )) ORDER BY

MSG.end_time

DESC, MSG.end_time_ms DESC

We add an hint to force usage of index MSG1 :

SELECT /*+ index(MSG MSG1) */

MSG.MSG_id, MSG.version_nbr, MSG.rb_status, MSG.folup_status, MSG.voice_status, MSG.conf_status,

MSG.rev_status, MSG.capture_loc, MSG.clear_status, MSG.partner_MSG_id2, MSG.partner_MSG_id4,

MSG.amount, MSG.bill_amount, MSG.object, MSG.object_modifier, MSG.pci_crypto_key_id,

MSG.storage_result

FROM MSG

WHERE

((((((MSG.storage_result = 'F') AND (MSG.ext_MSG_id = :f1 )) AND (MSG.rb_status <> 'S')) AND (MSG.retry_counter IS NULL)) AND (MSG.rev_status IS NOT NULL)) AND (MSG.end_time >= :f2 )) ORDER BY

MSG.end_time

DESC, MSG.end_time_ms DESC

And now our wrapper that fills environment variables without altering the SQL text :

(content of wrapper.sql)

alter session set create_stored_outlines = Smenu_sw ;

declare

b1 MSG.MSG_id%TYPE;

b2 MSG.version_nbr%TYPE ;

b3 MSG.rb_status%TYPE ;

b4 MSG.folup_status%TYPE ;

b5 MSG.voice_status%TYPE ;

b6 MSG.conf_status%TYPE ;

b7 MSG.rev_status%TYPE ;

b8 MSG.capture_loc%TYPE ;

b9 MSG.clear_status%TYPE ;

b10 MSG.partner_MSG_id2%TYPE ;

b11 MSG.partner_MSG_id4%TYPE ;

b12 MSG.amount%TYPE ;

b13 MSG.bill_amount%TYPE ;

b14 MSG.object%TYPE ;

b15 MSG.object_modifier%TYPE ;

b16 MSG.pci_crypto_key_id%TYPE ;

b17 MSG.storage_result%TYPE ;

fsql varchar2(4000);

f1 varchar2(124) := '591|279815|071127130222|826|grp1merch1LOC';

f2 date := sysdate ;

begin

fsql :=’ SELECT /*+ index(MSG MSG1) */

MSG.MSG_id, MSG.version_nbr, MSG.rb_status, MSG.folup_status, MSG.voice_status, MSG.conf_status,

MSG.rev_status, MSG.capture_loc, MSG.clear_status, MSG.partner_MSG_id2, MSG.partner_MSG_id4,

MSG.amount, MSG.bill_amount, MSG.object, MSG.object_modifier, MSG.pci_crypto_key_id,

MSG.storage_result

FROM MSG

WHERE

((((((MSG.storage_result = ''F'') AND (MSG.ext_MSG_id = :f1 )) AND (MSG.rb_status <> ''S'')) AND (MSG.retry_counter IS NULL)) AND (MSG.rev_status IS NOT NULL)) AND (MSG.end_time >= :f2 )) ORDER BY

MSG.end_time

DESC, MSG.end_time_ms DESC' ;

execute immediate fsql into b1,b2,b3,b4,b5,b6,b7,b8,b9,b10,b11,b12,b13,b14,b15,b16,b17 using f1,f2 ;

exception when no_data_found then null ;

end;

We run this:

SQL> @wrapper

Session altered.

PL/SQL procedure successfully completed.

We look now the content of outln.OL$ using ‘sx’ shortcut.

[LinuxT01:ORA10G3]:> sx -lc

MACHINE LinuxT01.unix.b - ORACLE_SID : ORA10G3 Date - Wednesday 05th December 2007 12:33:35

Username - SYS

how explain plan for query hash_value :

OWNER NAME CATEGORY ENABLED USED HASH_VALUE Sql Text

------ ------------------------------ --------- ------- ------ ---------- ----------------------------------------

BPADEV SYS_OUTLINE_07120313083698728 SMENU_SW ENABLED UNUSED 2071614804 SELECT /*+ index(MSG MSG1)*/ MSG.MSG_id

BPADEV SYS_OUTLINE_07120410332582236 BPADEV_OL ENABLED UNUSED 2071614804 SELECT MSG.MSG_id, MSG.version_nbr, MSG

[LinuxT01:ORA10G3]: > sx –ln SYS_OUTLINE_07120313083698728

MACHINE LinuxT01.unix.b - ORACLE_SID : ORA10G3

Date - Wednesday 05th December 2007 14:13:11

Username - SYS

HINT# HINT_TEXT STAGE# TABLE_NAME pos COST CARDINALITY BYTES

----- -------------------------------------------------- ------ ------------ ----- ---------- ----------- ----

1 OUTLINE_LEAF(@"SEL$1") 1 0 0 0 0

2 ALL_ROWS 1 0 0 0 0

3 OPT_PARAM('optimizer_index_caching' 50) 1 0 0 0 0

4 OPT_PARAM('star_transformation_enabled' 'true') 1 0 0 0 0

5 IGNORE_OPTIM_EMBEDDED_HINTS 1 0 0 0 0

6 OPTIMIZER_FEATURES_ENABLE('10.2.0.3') 1 0 0 0 0

7 OPT_PARAM('optimizer_index_cost_adj' 50) 1 0 0 0 0

8 INDEX_RS_ASC(@"SEL$1" "MSG"@"SEL$1" "MSG"."END_TIME" 1 MSG 1 1 1 380

("MSG"."EXT_TX N_ID" "MSG"."END_TIME_MS"))

Line 8 index column signature corresponds to MSG1, which is what we wanted.

We need no to transfer the contents of stored hints of the patched SQL in place of stored hint of the original query. Smenu can do this quickly:

TRANSFER of OUTLINES FROM PATCHED TO ORIGINAL SQL:

/tmp> sx –tr SYS_OUTLINE_07120313083698728 SYS_OUTLINE_07120410332582236

Now the content of stored hints for stored outline ‘SYS_OUTLINE_07120410332582236’ is:

[LinuxT01:ORA10G3]:> sx –ln SYS_OUTLINE_07120410332582236

MACHINE LinuxT01.unix.b - ORACLE_SID : ORA10G3

Date - Wednesday 05th December 2007 14:13:11

Username - SYS

HINT# HINT_TEXT STAGE# TABLE_NAME pos COST CARDINALITY BYTES

----- -------------------------------------------------- ------ ------------ ----- ---------- ----------- ----

1 OUTLINE_LEAF(@"SEL$1") 1 0 0 0 0

2 ALL_ROWS 1 0 0 0 0

3 OPT_PARAM('optimizer_index_caching' 50) 1 0 0 0 0

4 OPT_PARAM('star_transformation_enabled' 'true') 1 0 0 0 0

5 IGNORE_OPTIM_EMBEDDED_HINTS 1 0 0 0 0

6 OPTIMIZER_FEATURES_ENABLE('10.2.0.3') 1 0 0 0 0

7 OPT_PARAM('optimizer_index_cost_adj' 50) 1 0 0 0 0

8 INDEX_RS_ASC(@"SEL$1" "MSG"@"SEL$1" "MSG"."END_TIME" 1 MSG 1 1 1 380

("MSG"."EXT_TX N_ID" "MSG"."END_TIME_MS"))

This command transfers all hints that belongs to ‘SYS_OUTLINE_07120313083698728’ to ‘SYS_OUTLINE_07120410332582236’.

During the process, the original hints are destroyed. Now in order to force the system to use the stored outlines we flush the shared_pool and run the original SQL.

CHECKING THAT STORED OUTLINES ARE USED:

When it comes to see if our stored outline is used, there are two useful command into the shortcut ‘sx’

‘sx –lso’ list all SQL from v$sql whose column ‘outline_category’ is not null. When an SQL is executed using a stored outline plan, it fills this column.

Which SQL uses stored outlines ?

[LinuxT01:ORA10G3]:> sx -lso

MACHINE LinuxT01.unix.b - ORACLE_SID : ORA10G3 Page: 1

Date - Wednesday 05th December 2007 14:38:13

Username - SYS

l Plan hash Old hash Opt

SQL_ID HASH_VALUE d Value Value Execs Cost LAST_ACTIVE_TIME Sql Text

------------- ---------- - ---------- ---------- ----- ----- ------------------- -------------------------------

badkksm0qq6s9 3245021961 1 2820765636 198020183 0 22 2007-12-05 14:35:52 SELECT MSG.MSG_id, MSG.version_nb

badkksm0qq6s9 3245021961 2 2820765636 198020183 0 22 2007-12-05 08:31:34 SELECT MSG.MSG_id, MSG.version_nb

badkksm0qq6s9 3245021961 3 2820765636 198020183 692 22 2007-12-05 14:08:36 SELECT MSG.MSG_id, MSG.version_nb

Show Stored outlines:

[LinuxT01:ORA10G3]:> sx -lc

MACHINE LinuxT01.unix.b - ORACLE_SID : ORA10G3

Date - Wednesday 05th December 2007 12:43:35

Username - SYS

how explain plan for query hash_value :

OWNER NAME CATEGORY ENABLED USED HASH_VALUE Sql Text

------ ------------------------------ --------- ------- ------ ---------- ----------------------------------------

BPADEV SYS_OUTLINE_07120313083698728 SMENU_SW ENABLED UNUSED 2071614804 SELECT /*+ index(MSG MSG1)*/ MSG.MSG_id

BPADEV SYS_OUTLINE_07120410332582236 BPADEV_OL ENABLED USED 2071614804 SELECT MSG.MSG_id, MSG.version_nbr, MSG

bpolarsk@yahoo.com Last Update : 05-December-2007