oracle11g sql plan management

2009-11-16

Oracle11g SQL Plan Management

Alex Zeng

Agenda What is SQL Plan Management?

SQL Plan Management concept Parameters, Packages and Views involved

Why use SQL Plan Management?When it helpsWhen it can NOT help

How to use SPM?Capture (Load)Evolve (Verify)DisplayModify and DropHow Oracle choose plans

Misc. Q/A

SQL Plan Management (SPM) Concept

SQL Plan Managemen

t

SQL Plan Baseline

Sql Profiles

PackagesParameters

Views

Accepted Plans

Fixed Accepted

Plans

Not Accepted

Plans

Manually verify Plan

Automatic SQL Tuning task

Automatically captureRepeatableSQL

Manually LoadSQL

√

?

Parameters, Packages and Views involved

•optimizer_capture_sql_plan_baselines (FALSE)•optimizer_use_sql_plan_baselines (TRUE)•_sql_plan_management_control (0)

Parameters

•DBMS_SPM: Load, Evolve, Alter, Drop, etc.•DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE

Packages

•DBA_SQL_PLAN_BASELINESViews





Misc. Q/A

Why use SQL Plan Management?• Compare to

reactive tuning technique, such as outline, hint, SQL profile

• Manually load, evolve

• Automatically load, evolve

• Can disable plan

• Compare to hint based technique, SQL Profile etc.

• Only CBO is uncertain.

• Do NOT change when system, session, object or statistics changes

Proactive tuning

Total controllabl

e

Plan based

Stable SQL plan

Why use SQL Plan Management? - contd.

√ Use new optimizer version

√ Change optimizer statistics or optimizer parameters

√ Change schema or metadata definitions

√ Change system settings

√ Create SQL profile

When it helps stabilize SQL

plan

An event has caused irreversible execution plan changes

Example: dropping an index used by the SQL Plan

When it can NOT help

Why use SQL Plan Management? - contd.





Misc. Q/A

How to use SPMEvolve (Verify)

Display

ModifyDrop

Capture (Load)

Capture (Load)

Plan baseline

DBA

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE

Plan history

HJ

GB

HJ

Plan

HJ

GB

HJ

Plan history

HJ

GB

HJ

baseline1

2

3

4

Plan baseline

Plan history

HJ

GB

HJ

load_plans_from_cursor_cache

load_plans_from_sqlset

dbms_spm

Stagingtable

HJ

GB

HJ

*_stgtab_baseline

alter_sql_plan_baseline

Cursorcache

Auto Capture

Manually

Load

Automatically Capture

SQL> Alter session set optimizer_capture_sql_plan_baselines = TRUE

Automatically Capture - what's behind?Physical storage: Oracle will store the SQL

history in physical table. If you run a SQL once and then restart instance, run it again. This SQL will be considered repeatable SQL and the second plan will be captured

Overhead: Compare to disable auto-capture, the first run overhead, are (1 SELECT + 1 INSERT) on system tables; the second run overhead are (3 SELECT + 5 INSERT);

TEXT-baseOracle will capture the SQL plan if the SQL text is the same regardless of whether the visited table are same or not.

Manually load

Load From Cursor Cache

• LOAD_PLANS_FROM_CURSOR_CACHE, Input options to identify SQLS:

• sql_id, (sql_text), (plan_hash_value)• sql_id, sql_handle , (plan_hash_value)• sql_text, parsing_schema_name,

module, action

Load From SQL Tuning Sets(STS)

•LOAD_PLANS_FROM_SQLSET, Input value:•sqlset_name

Load From AWR snapshots

•First Load plans in AWR to STS•Then use DBMS_SPM. LOAD_PLANS_FROM_SQLSET

exec :cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>'9xgjm73');

Manually load contd. -- Behavior with different settingIf a SQL has only non-fixed baseline, new plan

will *ALWAYS* be captured, no matter optimizer_capture_sql_plan_baselines is FALSE or TRUE, but execute the accepted plan.

If a SQL has a fixed plan baseline, new plan will *NEVER* be capture automatically, no matter optimizer_capture_sql_plan_baselines is TRUE or FALSE, also execute the fixed plan.

The behavior of auto-capture doesn't change when set optimizer_use_sql_plan_baselines to FALSE.





Misc. Q/A

Evolve (verify)•EVOLVE_SQL_PLAN_BASELINEProcedure

•no input: evolve all non-accepted plans•sql_handle: evolve this sql's all non-accepted plans•plan_name: evolve this plan only•plan_list, evolve a list of sql plan

Input variables to identify SQL

Plan

•verify ('YES')•commit ('YES') •time_limit (minutes)

Other variables

SQL> exec :rpt := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE() ;

Evolve (verify) contd. – How oracle evolve?If verified is set to 'YES'(default), Oracle will

execute non-accepted SQL plan and the best accepted SQL plan, then compare the real cost.

When evolve SQL plan with bind variables, oracle will use the bind variable value when the plan was captured.

In my test, each plan executed 10 times





Misc. Q/A

Display• DBA_SQL_PLAN_BASELINES• select sql_handle, plan_name, enabled, accepted,

fixed from dba_sql_plan_baselines;View• DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE• Select * from

table(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE('<sql_handle>');

Procedure

• sql_handle, display all plans of this SQL• plan_name, only display this SQL plan• sql_handle and plan_name

Input variables to identify SQL Plan

SQL> select p.* from (select distinct sql_handle from DBA_SQL_PLAN_BASELINES where sql_text like '%from t1%') spb, table(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(spb.sql_handle, NULL, 'BASIC')) p;

Display contd. - What's behind?The plan baselines are stored in 3 tables in

tablespace SYSAUX sys.SQLOBJ$DATA sys.SQLOBJ$ sys.SQLOBJ$AUXDATA

Modify•ALTER_SQL_PLAN_BASELINEProcedure

•sql_handle•plan_name•sql_handle and plan_name

Input variables to identify SQL

Plan

•enabled, set it to 'NO' if don't want optimizer use it•fixed, set it to 'YES' if want optimizer choose it as a priority

Modify attributes

SQL> exec :cnt:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE(sql_handle=>'SYS_SQL_e1d5d446a7219dcb', PLAN_NAME=>'SQL_PLAN_f3pfn8umk37fb616acf47',attribute_name=>'fixed',attribute_value=>'YES');

Modify cont.It can NOT modify accepted status. Disable or drop it if don't want to use it. Call

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE with verify to 'NO' if want to change it to accepted directly

For a non-accepted plan, even modify it to fixed plan, it will not be used.

Drop

•DROP_SQL_PLAN_BASELINE

Procedure

•sql_handle•plan_name•sql_handle and plan_name

Input variables

SQL> exec :cnt:= DBMS_SPM.DROP_SQL_PLAN_BASELINE (sql_handle=>'SYS_SQL_e1d5d446a7219dcb', PLAN_NAME=>'SQL_PLAN_f3pfn8umk37fb425cd488');





Misc. Q/A

How Oracle choose plans

Plan partof history? No

HJ

GB

HJ

HJ

GB

HJ

HJ

GB

HJ

> HJ

GB

HJ

NoHJ

GB

HJ

Yes

Plan history

Planbaseline

HJ

GB

HJ

HJ

GB

HJ…HJ

GB

HJ

Yes

Plan partof baseline?

Yes

optimizer_use_sql_plan_baselines=true? Yes

HJ

GB

HJ

No

No

dbms_xplan.display(…,'BASIC +NOTE)orplan_table(other_xml)

dbms_xplan.display_sql_plan_baseline

Select baseline planwith lowest best-cost

Any fixed Plan?

Yes

Yes

No

Select fixed baseline planwith lowest best-cost No

How Oracle choose plans – contd.One Sql can have multi-accepted plans.One Sql can have multi-fixed plans.Statistics will influence the choice when

choose SQL Plan between accepted plans. So don’t have too many accepted plan if dislike nightmare.





Misc. Q/A

Misc.Migrate stored outline to SQL plan baselines

Export/Import SQL plan baselines

Implement function like

'ora olexchange'

Maintenance retention

Maintenance retentionStored in SYSAUX, space maximum usage

default 10%

Retention period default 53 weeks, using LAST_EXECUTED timestamp in DBA_SQL_PLAN_BASELINES to get expired plans

Check value:

SQL> exec DBMS_SPM.CONFIGURE('space_budget_percent',30);

SQL> exec DBMS_SPM.CONFIGURE( 'plan_retention_weeks',105);

SQL> select * from DBA_SQL_MANAGEMENT_CONFIG;

Migrate stored outline to Sql plan baselinesUse DBMS_SPM.MIGRATE_STORED_OUTLINE

After verify, drop migrated outline

SQL> exec :rpt:= DBMS_SPM.MIGRATE_STORED_OUTLINE( attribute_name => 'category', attribute_value => good_plan', fixed => 'YES' );

SQL>exec :cnt:= DBMS_SPM.DROP_MIGRATED_STORED_OUTLINE (); (11.2 new)

Export/Import Sql plan baselines

Create stage table

Pack baselines into stage table

Copy it to target db and unpack it

SQL> exec DBMS_SPM.CREATE_STGTAB_BASELINE (table_name => 'TMP1', tablespace_name=>'CR_DATA');

SQL> exec :cnt:= DBMS_SPM.PACK_STGTAB_BASELINE(table_name => 'TMP1');

SQL> exec :cnt:=DBMS_SPM.UNPACK_STGTAB_BASELINE(table_name => 'TMP1');

Bind hinted SQL plan to non-hinted one

Run SQL• select count(*) from t1, t2 where

t1.id=t2.id

GET SQL_ID

• select SQL_ID from V$SQL where sql_text like 'select count(*) from t1, t2 where t1.id=t2.id'

• sql_id chj6q8z7ykbyy

Create baseline

• Using the SQL_ID create a SQL plan baseline

• exec :cnt :=dbms_spm.load_plans_from_cursor_cache(sql_id=>'chj6q8z7ykbyy');

Find it in baseline

• Find SQL_HANDLE & PLAN_NAME• SQL> SELECT sql_handle, plan_name,

enabled FROM dba_sql_plan_baselines where sql_text like '%t1.id=t2.id%';

• SQL_HANDLE: SYS_SQL_1a9420dc6aa256d8

• PLAN_NAME: SQL_PLAN_1p510vjpa4pqsb3e28116 YES

Oracle supported

Process

Bind hinted SQL plan to non-hinted one -contd.

Disable Baseline

• SQL> exec :cnt :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE( SQL_HANDLE => 'SYS_SQL_1a9420dc6aa256d8', PLAN_NAME => 'SQL_PLAN_1p510vjpa4pqsb3e28116', ATTRIBUTE_NAME => 'enabled', ATTRIBUTE_VALUE => 'NO');

Run hinted SQL• select/*+ full(t1) */ count(*) from t1, t2

where t1.id=t2.id;

Get NEW SQL_ID and PLAN_HASH_VALUE

• select * from table(dbms_xplan.display_cursor); -- OR

• select sql_id, plan_hash_value from V$SQL where sql_text like '%/*+full(t1) %';

• SQL_ID: 3v17y0gy90rtw Plan hash value: 906334482

Bind hinted SQL plan to non-hinted one -contd.

new SQL_ID and PLAN_HASH_VALUE

with old SQL_HANDLE

• exec :cnt :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (sql_handle=>'SYS_SQL_1a9420dc6aa256d8', sql_id=>'3v17y0gy90rtw', plan_hash_value=>906334482);

new SQL_ID and PLAN_HASH_VALUE with old SQL_TEXT

• exec :cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (sql_text=> 'select count(*) from t1, t2 where t1.id=t2.id', sql_id=>'3v17y0gy90rtw', plan_hash_value=>906334482');

WORKSBOTH WORKS





Misc. Q/A

oracle11g sql plan management

Technology

sql plans plan

list of sql plan

sql plan wi

use sql plan management

plan onlyplan plan

sql profile

cursor sql

value sql