Outlines, Profiles and SQL Plan Baselines

Frank Bommarito

Overview
Optimizer Ranges Outlines and Profiles SQL Plan Baselines Conclusion

Introduction
Profiles and Outlines are used to correct specific SQL statement Profiles and Outlines work on SQL that has already run and is deemed to be poor performing SQL Plan Baselines capture good times to prevent poor performing SQL. All are used to make a good predictable system.

Optimizer Ranges
The Extremes Optimizer Flexibility Plan Stability

The Extremes
Optimizer Flexibility Plan Stability Default optimizer does a pretty good job on most SQL. There are always some exceptions and often these are not measured in seconds sometimes in days Setup the database to where most SQL is good OLTP lean toward Plan Stability DW lean toward Optimizer Flexibility

Optimizer Flexibility
Give the optimizer as much information as possible Use histograms where data skews are possible Generate statistics frequently Use dynamic sampling on load tables Start with default settings and adjust only if required

Plan Stability
Implement more stringent initialization parameters Limit or eliminate histograms Avoid bind variable peeking issues Port statistics with objects through the development cycle. Reduce the impact of larger production machines (CPU and Memory) Execute infrequent statistics gathering and always store base lines statistics

Plan Stability
Plan stability prevents certain database environment changes from affecting the performance characteristics of applications. Such changes include changes in optimizer statistics, changes to the optimizer mode settings, and changes to parameters affecting the sizes of memory structures, such as SORT_AREA_SIZE and BITMAP_MERGE_AREA_SIZE. Plan stability is most useful when you cannot risk any performance changes in an application.

What is an Outline and Profile?

Outlines:
An outline is a stored execution path for a specific SQL statement.

Profiles
Enhanced database statistics for a specific SQL statement.

SQL Plan Baselines are shown later

In effect a combination of outlines and profiles with intelligence.

When to use Outlines and Profiles?

Plan Stability Ensure that performance stays consistent between upgrades (Database and/or application) Benefits of cost based optimizer with predictability of rule based optimizer. Allows for tuning without changing SQL or init.ora parameters. Tuning of 3rd party applications

When not to use Outlines and Profiles?

When Oracle parameters prevent usage When too many SQL statements need manual tuning When a needed hint is not supported When it is easy to access the application This is a reactive tuning method do not use if trying to be proactive.

How to use?
Must have privileges
CREATE ANY OUTLINE (DROP/ALTER) CREATE ANY PROFILE (DROP/ALTER)

Need to active usage

ALTER SYSTEM/SESSION SET
USE_STORED_OUTLINES=Category SQLTUNE_CATEGORY=Category

How to use?
Need to clear active SQL memory
ALTER SYSTEM FLUSH SHARED POOL;

Need to persist usage on reboot

Create trigger <trgname> after startup on database

How to use?
create or replace trigger use_outln_<cat> after startup on database begin execute immediate 'alter system set use_stored_outlines=<cat>'; end; /

Where are these stored?

Outlines
Public outln user ol$, ol$hints, ol$nodes tables Private Pre-10g personal schema 10g and above system schema DBA_OUTLINES

Profiles
SQL$ and SQL$OBJ tables DBA_SQL_PROFILES

How to create one? - Outline

alter system flush shared_pool; alter system/session set create_stored_outlines=<cat>; -- Run SQL through the application. alter system/session set create_stored_outlines=FALSE; alter system set use_stored_outlines=<cat>;

How to create one? - Outline

Create outline for category <x> on Select * from dual; Often created within a temporary environment Alter session set optimizer_mode=rule; Create outline <name> for category AOUG on Select ;

What if I need a hint?

dbms_outln.edit Outln_switch.sql Both work by manipulating the base tables. Outln_switch can work on private or public tables.

What if I need a hint?

Create outline AOUG_base for category AOUG on Select * from dual; Create outline AOUG_hinted for category AOUG on Select /*+ full(dual) */ from dual; Call this script (put in a SQL called outln_switch.sql) @@outln_switch AOUG_HINTED AOUG_BASE

Outln_switch.sql
This is also in the paper along with dbms_outln.edit examples:
define HINTSQL=&1 define ORIGINALSQL=&2 UPDATE OUTLN.OL$HINTS SET OL_NAME= DECODE(OL_NAME,'&&HINTSQL','&&ORIGINALSQL','&&ORIGINAL SQL','&&HINTSQL') WHERE OL_NAME IN ('&&ORIGINALSQL','&&HINTSQL'); DROP OUTLINE &&HINTSQL;

How to create one? Profile

Oracle OEM DBMS_SQLTUNE From an actual SQL statement From an AWR report Paper for this presentation has specific examples Running the DBMS_SQLTUNE does not guarantee a fix but is very easy to use and often fixes.

Is it used? - Outline
At the system level: select count(*),used from dba_outlines group by used; COUNT(*) USED --------------18 UNUSED 109 USED

Is it used? - Outline
Exec dbms_outln.clear_used(x); Where X is the name of an outline. V$SQL Outline_catgory, Outline_SID

Is it used? - Outline
Explain Plan Results: select * from table(dbms_xplan.display()); Blah Note ----- outline "AOUG_OUTLINE" used for this statement

Is it used? - Profile
Explain Plan Results: select * from table(dbms_xplan.display()); Blah Blah - Blah Note ----- SQL profile "SYS_SQLPROF_0146077cbd7f0000" used for this statement

Activate/De-Activate - Outlines
alter system/session set use_stored_outlines=FALSE; Drop outline xyz; Alter system flush shared_pool; Cursor_sharing=force Change SQL statement Select * from dual Is the same as Select * from dual But Select dummy from dual is different

Activate/De-Activate - Profile
dbms_outln.EXACT_TEXT_SIGNATURES can ensure that an exact match is required

ALTER OUTLINE outline_name CHANGE CATEGORY TO not_used_now;

alter system/session set sqltune_category=FALSE;

Activate/De-Activate - Profile
declare begin dbms_sqltune.drop_sql_profile(name => 'SYS_SQLPROF_070515143928038'); end; /

Migrate - Outlines
$ exp owner=outln file=outln tables=ol$,ol$hints,ol$nodes query=where category=<cat> username=outln@test Prod> delete outln.ol$; Prod> delete outln.ol$hints; Prod> delete outln.ol$nodes;

Migrate - Outlines
Prod$ imp full=y ignore=y file=outln Prod> exec dbms_outln.clear_used; Prod> alter system flush shared_pool;

Migrate - Profiles
# In test dbms_sqltune.create_stgtab_sqlprof('AOUG_PROFILES','AOUG '); dbms_sqltune.pack_stgtab_sqlprof(STAGING_TABLE_NAME=> 'AOUG_PROFILES', STAGING_SCHEMA_OWNER=>'AOUG'); $ exp tables=AOUG.AOUG_profiles $ scp expdat.dmp Prod_Host:expdat.dmp $ imp full=y ignore=y

Migrate - Profiles
dbms_sqltune.unpack_stgtab_sqlprof(PROFILE_NAME=>'%', PROFILE_CATEGORY=>'%', REPLACE=>TRUE, STAGING_TABLE_NAME=>'AOUG_PROFILES', STAGING_SCHEMA_OWNER=>'AOUG');

Pros/Cons
Pros
Allows tuning of SQL that is not changeable Provides plan stability Allows development to production to be predictable Gives all of the cost-based optimizer advantages with the rule-based optimizer predictability. Can reduce or eliminate statistics jobs Multiple outlines categories can be used to separate OLTP and BATCH.

Pros/Cons
Pros
Easy to use Easy to implement Supported by all known third-party applications

Pros/Cons
Cons
Does not allow for column level variations for plans. Requires maintenance when the application changes Can be disabled without knowledge Requires trespassing into the application Outlines require sophisticated tuning knowledge

SQL Plan Baselines What is it?

SQL Plan Baselines is a new feature with release 11 of Oracle. Used safe or trusted plans Captures new plans for later analysis Captures can be manual or automatic Single repository for Profiles and Outlines

How to enable?
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
For automatic capturing

Usage of DBMS_SPM package

For manual capturing and validation

OPTIMIZER_USE_SQL_PLAN_BASELINES
To enable usage

Manual Loading
Plans can be loaded from AWR reports and/or the SQL cursor cache. DECLARE v_sql_plan pls_integer; BEGIN v_sql_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(sqlset_name => 'AOUG_SET'); v_sql_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id => '99twu5t2dn5xd'); END;

Validating Plans
DBA_SQL_PLAN_BASELINES (SQL_HANDLE) SET SERVEROUTPUT ON SET LONG 10000 DECLARE v_report clob; BEGIN v_report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle => 'SYS_SQL_593bc74fca8e6738'); DBMS_OUTPUT.PUT_LINE(v_report); END;

View an explain plan from a baseline

select * from table( dbms_xplan.display_sql_plan_baseline( sql_handle=>'SYS_SQL_209d10fabbedc741', format=>'basic'));

Migrate
DBMS_SPM.CREATE_STGTAB_BASELINE(table_name => 'AOUG_SPM'); DECLARE v_plans number; BEGIN v_plans := DBMS_SPM.PACK_STGTAB_BASELINE(table_name => 'AOUG_SPM', enabled => 'yes'); END; /

Migrate
$ exp tables=AOUG_SPM $ scp expdat.dmp Prod_Host: $ On Prod Host imp full=y DECLARE v_plans number; BEGIN v_plans := DBMS_SPM.UNPACK_STGTAB_BASELINE(table_name => 'AOUG_SPM'); END; /

Conclusion
Usage of optimizer features for plan stability provides a powerful and effective means to take control of an application. As with any other database utility, ensure that there is a complete understanding and setting of expectations prior to implementation.

Questions
Frank Bommarito DBA Knowledge, Inc. frank@dbaknow.com http://www.dbaknow.com