Tim Quinlan TLQ Consulting Inc.

1
Tim QuinlanTLQ Consulting Inc.
Guaranteeing High Performing SQL through Plan
Stability and Management
2
Plan Stability

• Part 1 Taking advantage of Plan Stability.
• What is Plan Stability and why do we need it?
• Getting started, turning it on and capturing
  outlines.
• Making an outline active and keeping it active
• Monitoring is an outline being used?
• Managing outlines scripts, issues, schema
  maintenance
• Moving outlines
• Part 2 SQL Plan Management, the next generation
• SQL Baselines vs. Stored Outlines
• Baselines fixed and non-fixed capturing moving
• SQL Management Base (SMB)
• Maintenance packages, views, export, import
• Managing Baselines purging config. parms
  displaying migrating to SQL Plan Baselines.

3
Part 1 Taking advantage of Plan Stability

• Overview of Part 1
• What is Plan Stability and why do we need it?
• Getting started, turning it on and capturing
  outlines.
• Categories and how we use them
• Making an outline active and keeping it active
• Monitoring is an outline being used?
• Managing outlines scripts, issues, schema
  maintenance
• Moving outlines

4
Part 1 Plan StabilityWhat is Plan Stability and
why do we need it?

• Have you ever had an SQL statement suddenly run
  much longer for no reason?
• Why do access paths change?
• Change in data volumes and statistics
• Change in objects example index changes
• Optimizer settings e.g. memory settings
• Oracle version or patch
• Deployment of new or changed modules and
  recompiles
• Change to the environment
• e.g. server memory settings or usage.

5
Part 1 Plan StabilityWhat is Plan Stability and
why do we need it?

• What is an outline?
• Method of saving a named execution plan for one
  SQL statement.
• This is done by storing hints to influence the
  access path.
• The hints are saved rather than the actual
  execution plan.
• Future SQL, exactly matching the original SQL,
  can use the stored hints to affect the access
  path.

6
Part 1 Plan StabilityWhat is Plan Stability and
why do we need it?

• Caution the performance of the stored execution
  plan may degrade over time.
• Based on statistics and an environment that may
  no longer be valid.
• Contradicts the purpose of the optimizer.
• Requires management and should only be used where
  necessary and as a temporary fix.
• Go back to the original SQL, application design
  and or object (e.g. index) design.

7
Part 1 Plan StabilityWhat is Plan Stability and
why do we need it?

• How does SQL match an outline?
• One-to-one correlation between an SQL statement
  an outline.
• Similar statements can share an outline using
  literals rather than bind variables.
• When created with create_stored_outline procedure
• not with create_outline statement.
• When the statement is captured AND executed using
  cursor_sharing set to similar.
• If you embed new hints into SQL, that new SQL
  will no longer match the stored outline.
• Outline SQL text and category name are both used
  to see if a plan is in cache.
• Ensures that the proper category is used.

8
Part 1 Plan StabilityWhat is Plan Stability and
why do we need it?

• Is the access path guaranteed?
• No. Hints are stored, but the hints do not cover
  every possible access path permutation and
  combination.
• So, the hints are taken together with the
  database statistics and run-time environment to
  develop the access path.
• Home-built and 3rd party applications
• Can both benefit from stored outlines.

9
Part 1 Plan StabilityWhat is Plan Stability and
why do we need it?

• The outln schema
• The schema where stored outlines are saved in the
  database.
• OL, OLHINTS, OLNODES tables
• These tables cannot be changed directly with
  insert, update, delete statements.
• SYS schema views
• user_outlines, user_outline_hints,
  ku_outline_view views.

10
Part 1 Plan StabilityUsing Categories to Manage
Outlines

• What are Categories and how are they used?
• Simplify plan management
• Create Outline statement and/or the
  create_stored_outlines init parm allow you to
  specify a category.
• Otherwise, goes to category DEFAULT.
• All stored outlines go to that category until
  the category name is reset.

11
Part 1 Plan StabilityUsing Categories to Manage
Outlines

• What are Categories and how are they used?
  (cont.)
• A category can be enabled so outlines can be used
  by SQL.
• An outline can be created stored in a category,
  without being used
• This allows you to keep versions or a history of
  outlines.
• After an outline has been captured, it can remain
  in this category or can be moved to another
  category.
• The new category could be active allowing this
  outline to be a candidate for future SQL.

12
Part 1 Plan StabilityUsing Categories to Manage
Outlines

• Changing categories with alter outline
• You can capture a plan in one category and then
  move it to another to make it active.
• SQLgt select name, owner, category from
  dba_outlines
• where category'Client_CashTxns'
• NAME OWNER CATEGORY
• SYS_OUTLINE_08101715152990267 APPOWNER
  CapturedCat
• SQLgt alter outline SYS_OUTLINE_08101715152990267
  change category to UsePlan
• NAME OWNER CATEGORY
• SYS_OUTLINE_08101715152990267 APPOWNER UsePlan
• Use Alter Outline to enable an individual
  statement rename an outline move an outline to
  another Category.

13
Part 1 Plan StabilityUsing Categories to Manage
Outlines (cont.)

• Private outlines.
• A private outline is only seen in the current
  session with data residing on the parsing schema.
• For a private outline to be used by other
  sessions, they must be explicitly saved to a
  public area.
• Retrieved from session private area with
  use_private_outlines
• as opposed to the public area with
  use_stored_outlines.
• Set parm use_private_outlines to enable this.
• This and use_stored_outlines are system or
  session specific and not initialization parms.

14
Part 1 Plan Stability Getting started, turning
on and capturing outlines

• When do we capture outlines?
• Regular, scheduled capture (example monthly)
• Before upgrades
• A valuable backup plan in case a single access
  path causes problems.
• Capturing outlines in test
• Perform volume or stress testing and capture the
  outlines.
• These can be moved to Prod just in case theyre
  needed.
• Moving from RBO to Cost-Based
• Before the move, capture the outlines from the
  rule-based optimized.
• After the switch, you will have the outlines in
  case they are needed.

15
Part 1 Plan Stability Getting started, turning
on and capturing outlines

• Parameters
• parms that need to be consistent across execution
  environments for outlines to function as
  expected
• optimizer_features_enable, query_rewrite_enable
  and star_transformation_enable
• parm create_stored_outlines set to true will
  create outlines automatically. Set to false to
  turn this off.
• Use dbms_outln.drop_unused proc to remove
  outlines where SQL uses literals and will likely
  not be used.
• Privileges
• create any outline privilege needed on schema
  to create outlines.
• Needed to run the create outline statement.

16
Part 1 Plan Stability Getting started, turning
on and capturing outlines

• Creating Outlines
• Can be granular
• gt for 1 specific SQL statement
• - OR -
• gt for all SQL statements over a period of time.

17
Part 1 Plan StabilityGetting started, turning
on and capturing outlines

• Creating Outlines for all SQL in a Schema
• We capture stored outlines once every month for 2
  hours for a specific application schema
• E.g. Capture outlines in a single category named
  MONTHLY2009SEP10
• GRANT CREATE ANY OUTLINE TO SCHEMANAME
• variable v_sodate varchar2(15)
• begin
• select to_char(sysdate,'YYYYMONDD-DY') into
  v_sodate from dual
• v_sql 'alter system set create_stored_outlines
  MONTHLY' v_category
• execute immediate v_sql
• end

18
Part 1 Plan Stability Getting started, turning
on and capturing outlines

• How can I see if the Stored Outlines are being
  created?
• SQLgt select value from vparameter where name
  'create_stored_outlines'
• VALUE
• MONTHLY2008DEC10
• SQLgt select owner, category, count() from
  dba_outlines
• group by owner, category
• OWNER CATEGORY COUNT()
• SCOTT MONTHLY2008DEC10 235
• let some time go by and run this again
• SQLgt select owner, category, count() from
  dba_outlines
• group by owner, category
• OWNER CATEGORY COUNT()
• SCOTT MONTHLY2008DEC10 478

19
Part 1 Plan Stability Getting started, turning
on and capturing outlines

• Turn off the capture
• Check that the stored outline is being captured
• SQLgt select value from vparameter where name
  'create_stored_outlines'
• VALUE
• MONTHLY2008DEC10
• SQLgt select owner, category, count() from
  dba_outlines
• group by owner, category
• CATEGORY COUNT()
• MONTHLY2008DEC10 593
• Turn off the capture
• SQLgt alter system set create_stored_outlines
  false
• Check that this is turned off
• SQLgt select value from vparameter where name
  'create_stored_outlines'
• VALUE
• FALSE

20
Part 1 Plan StabilityGetting started, turning
on and capturing outlines

• There are other ways to capture outlines.
• Capturing Outlines for specific SQL in cache
• Find the SQL using the sql_text as input
• SQLgt select sql_id, hash_value, child_number,
  sql_text from vsql
• where upper(sql_text) like upper('MERGE
  INTO detailtable')
• SQL_ID HASH_VALUE CHILD_NUMBER
  SQL_TEXT
• 36k6xhn2zmv3q 100265078 0
  MERGE INTO detailtable
• Make sure the user has authority to create
  outlines
• SQLgt GRANT CREATE ANY OUTLINE TO appluser

21
Part 1 Plan StabilityGetting started, turning
on and capturing outlines

• Capturing Outlines for specific SQL in cache
  (cont.)
• Create a stored outline for an SQL cursor
  currently in the Shared Pool.
• You need the HASH_VALUE and CHILD_NUMBER from
  VSQL for the SQL you want to add to a stored
  outline category.
• From the previous slide
• Use hash_value as first parm.
• Put this outline into its own category named
  OneOutlnCat
• exec dbms_outln.create_outline(100265078,
  0,OneOutlnCat')

22
Part 1 Plan StabilityGetting started, turning
on and capturing outlines

• Look at the execution plan using DBMSXPLAN and
  the SQL_ID
• SELECT FROM
• table(DBMS_XPLAN.DISPLAY_CURSOR('36k6xhn2zmv3q',0)
  )
• DBMSXPLAN gives you
• SQL_ID
• Hash_Value
• Execution plan of the SQL that executed.

23
Part 1 Plan StabilityGetting started, turning
on and capturing outlines

• Capture Outlines for specific SQL in cache
  (cont.)
• Export the single outline
• exp '"/ AS SYSDBA"' logDMP_DIR/expOneOutln.lo
  g buffer10485760
• fileDMP_DIR/expOneOutln.dmp
  tables'outln.ol','outln.olhints'
• query\"where category\OneOutlnCat\'\"
• Import the Outline to a new database
• imp '"/ AS SYSDBA"' logDMP_DIR/impOneOutln.lo
  g
• fromuserOUTLN touserOUTLN buffer10485760
  commity ignorey
• fileDMP_DIR/expOneOutln.dmp grantsn
  indexesn rowsy constraintsn

24
Part 1 Plan StabilityUsing a Stored Outline in
your Application

• Keeping an Outline Active
• Can an outline become inactive after its been
  turned on?
• Yes for example, if the database is shutdown
  and started.
• You may want to have a startup script or trigger
  to ensure these are enabled.
• Making outlines active by category or for
  specific SQL
• Making a category active.
• SQLgt alter system set use_stored_outlinesUseThisC
  ategory
• - Use_stored_outlines set to a specific
  category set to TRUE for default category set
  to FALSE to turn off.

25
Part 1 Plan StabilityMonitoring is an outline
being used?

• Check the outline_category column in vsql
• outline_sid column is 0 for a public outline or
  has the session sid for a private outline.
• SQLgt Select outline_category, first_load_time,
  last_load_time,
• last_active_time, sql_text
• From vsql
• Where outline_category is not null
• OUTLINE_CATEGORY SQL_TEXT
• FIRST_LOAD_TIME LAST_LOAD_TIME
  LAST_ACTIVE_TIME
• UseThisCategory Select .. From . Where
• 2009-01-05/092249 2009-01-26/075613
  29-JAN-09 123541

26
Part 1 Plan StabilityManaging Outlines

• Managing outlines scripts, issues, schema
  maintenance
• Use packages DBMS_OUTLN and DBMS_OUTLN_EDIT to
  manage outlines categories.
• execute_catalog_role needed on dbms_outln.
• public can execute dbms_outln_edit.
• Manipulating a stored outline
• Outlines can be edited manually using
  dbms_outln_edit.
• Can be edited with the outline editor in
  enterprise manager.

27
Part 1 Plan StabilityManaging Outlines- edit
Outline steps

• Make sure the schema where the outline is to be
  edited has the create any outline privilege.
• Connect to the schema where the outline is to be
  edited.
• Clone the outline to a private one.
• create private outline new_outln from old_outln
• Use the enterprise manager outline editor or
  dbms_outln_edit to edit the outline.
• For example, to change hints.

28
Part 1 Plan StabilityManaging Outlines- edit
Outline steps (cont.)

• When using dbms_outln_edit
• Use change_join_pos if you want to change the
  join position
• Resync the stored outline definition using one
  of
• exec dbms_outln_edit.refresh_private_outline
  (newname)
• create private outline new_outln from private
  new_outln
• You can also refresh with alter system flush
  shared pool
• Test the new outline with the edits
• Set use_private_outlinestrue and run explain
  plan or run the SQL statement.
• Once youre happy with the edits, publish the new
  outline
• create or replace outline old_outln from private
  new_outln
• Disable private outline use
• Use_private_outlinesfalse

29
Managing Outlines scripts, issues, schema
Maintenance

• Managing outlines
• views
• schema maintenance and purging/removing outlines
• looking at hints
• startup issues
• Moving Outln objects to another tablespace or
  database.
• Capturing outlines on a regular basis

30
Managing Outlines scripts, issues, schema
Maintenance

• Outline Views to query
• alldbauser_outlines
• alldbauser_outline_hints
• Get name, text and to see if an outline is
  enabled for a category
• Select name, sql_text, enabled
• From user_outlines where category CAT1
• To see the hints used by an outline
• Select hint from user_outline_hints where name
  OUTLN_NAME_1

31
Managing Outlines scripts, issues, schema
Maintenance

• To see the hints example of an index being
  used
• SQLgt select sql_text from dba_outlines where name
  'SYS_OUTLINE_08121014000435157
• select specialtaxrate from planaccount where
  accountid B1
• SQLgt Select node, stage, join_pos, hint from
  dba_outline_hints
• Where name 'SYS_OUTLINE_08121014000435157
  order by 1,2,3
• NODE STAGE JOIN_POS SQL_TEXT
• 1 1 0
  OUTLINE_LEAF(_at_"SEL1")
• 1 1 0 ALL_ROWS
• 1 1 0
  OPT_PARAM('optimizer_index_caching' 90)
• 1 1 0
  OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
• 1 1 0
  IGNORE_OPTIM_EMBEDDED_HINTS
• 1 1 0
  OPT_PARAM('optimizer_index_cost_adj' 10)
• 1 1 1 INDEX_RS_ASC(_at_"SEL
  1" planaccount"_at_"SEL1" (planaccount".accoun
  tid"))

32
Managing Outlines scripts, issues, schema
Maintenance

• Removing outlines automate purging of old
  outline categories that are more than
  approximately 6 months old.
• spool StoredOutlineMonthlyDropOldCategories.sql
• SELECT 'spool StoredOutlnMthlyCatDropOld.lst'
  from dual
• SELECT 'set echo on heading on feedback on
  verify on' from dual
• SELECT 'select category, count() from
  dba_outlines',
• 'where owner ''RPMOWN'' and category like
  ''MONTHLY''',
• 'group by category' from dual
• SELECT distinct 'exec dbms_outln.drop_by_cat('''
  category ''')'
• from dba_outlines
• where owner 'RPMOWN'
• and category like 'MONTHLY' and timestamp lt
  sysdate - 180
• SELECT 'spool off' from dual
• spool off

33
Part 1 Plan StabilityMoving Outlines

• Moving outline tables from the System tablespace
• 1. Run checks before the move to ensure Outlines
  are working
• 2. Export outln
• exp '"/ AS SYSDBA"' logMOVETS_DIR/expOutlnMove
  TS.log owneroutln
• buffer10485760 fileMOVETS_DIR/expOutlnMoveTS.
  dmp
• grantsy indexesy rowsy constraintsy
  consistenty triggersy directn
• 3. Make security changes in SQLPlus and drop the
  tables.
• SQLgt revoke unlimited tablespace from outln
• SQLgt alter user outln default tablespace
  sysaux
• SQLgt alter user outln quota unlimited on
  sysaux
• SQLgt drop table outln.ol
• SQLgt drop table outln.olHINTS
• SQLgt drop table outln.olNODES

34
Part 1 Plan StabilityMoving Outlines

• Moving outlines from the System tablespace
  (cont.)
• 4. Run the import
• imp '"/ AS SYSDBA"' logMOVETS_DIR/impOutlnMove
  TS.log
• fromuserOUTLN touserOUTLN
• buffer10485760 commity ignorey
  fileMOVETS_DIR/expOutlnMoveTS.dmp
• grantsy indexesy rowsy constraintsy
• 5. Compile invalid views under SYS.
• 6. Run checking after the move to ensure all
  objects are valid.

35
Part 1 Plan StabilityMoving Outlines

• Moving outlines to another Database
• Outlines of individual SQL statements and
  categories can be moved from one database to
  another.
• Use Categories to help accomplish this
• Steps are as follows
• 1) Get the SQL
• select sql_id, hash_value, child_number,
  sql_text from vsql
• where sql_text like 'INSERTINTO
  tmpSTM_CashTxns'
• SQL_ID HASH_VALUE
  CHILD_NUMBER
• SQL_TEXT
• -----------------------------------------
  ---------------------------------------
• 7jwfufu5zbjp1 2348140193 0
• INSERT / APPEND / INTO .

36
Part 1 Plan StabilityMoving Outlines

• Moving outlines to another Database (cont.)
• 2) Get the Plan using the SQL_ID
• select from table(DBMS_XPLAN.DISPLAY_CURSOR(
  '7jwfufu5zbjp1',0))
• 3) Create an outline in its own new category
  using the hash value
• exec dbms_outln.create_outline(2348140193,
  0,New_Category')
• 4) See if the category exists
• select name, owner, category from dba_outlines
• where categoryNew_Category'
• NAME OWNER
  CATEGORY
• SYS_OUTLINE_08101715152990267 OWNER1
  New_Category
• 5) Change the category, if you wish
• alter outline SYS_OUTLINE_08101715152990267
  change category to MOVEPLAN

37
Part 1 Plan StabilityMoving Outlines

• Moving outlines to another Database (cont.)
• 6) Export the Outline
• exp '"/ AS SYSDBA"' logMOVETS_DIR/expOutln.log
  
• buffer10485760 fileMOVETS_DIR/expOutln.dmp
• tables'outln.ol','outln.olhints'
• query\"where category\MOVEPLAN\'\"
• 7) Import the Outline to the new database
• imp '"/ AS SYSDBA"' logMOVETS_DIR/impOutln.log
  
• fromuserOUTLN touserOUTLN buffer10485760
• commity ignorey
• fileMOVETS_DIR/expOutln.dmp
• grantsn indexesn rowsy constraintsn

38
Part 1 Plan Stability

• Wrap-up of Part 1
• Use categories to manage outlines
• Using the steps shown will start you on your way
  to managing access paths.
• Provides an excellent fallback plan if
  performance degrades.

39
Part 2 SQL Plan Management, The Next
Generation Topics Covered

• SQL Baselines vs. Stored Outlines
• Baselines fixed and non-fixed capturing
  automatically and manually moving baselines.
• Accepting plans to add to a baseline.
• Getting plans from SQL Tuning Sets, AWR snapshots
  cursor cache.
• SQL Management Base (SMB)
• Maintenance
• packages, views, export, import
• Managing Baselines
• SMB space management, purging config. parms
  displaying baselines migrating from Stored
  Outlines to SQL Plan Baselines.

40
SQL Baselines vs. Stored Outlines

• What is an SQL Baseline?
• SQL Baselines vs. Stored Outlines
• SQL Baselines are used for the same reasons that
  we used Outlines.
• SQL Baselines can evolve over time to improve
  performance.
• They are used to prevent problems by storing plan
  baselines that work over a period of time.
• Baselines have improved stability over outlines.
• Outlines will be desupported in a future release
  of SQL plan management.
• Still supported in 11g.

41
SQL Baselines vs. Stored Outlines (cont.)

• SQL Baselines vs. Stored Outlines
• Outlines are manually enabled and disabled.
• gt You can think of them as being fixed.
• Baselines can be automatic or manual.
• Baselines are evaluated for better plans and used
  if that is the case.
• If you have a stored outline and the baseline
  finds a better plan, the evolve process must be
  performed to have the better plan used.
• gt There is no conflict using both.
• gt The query plan of the stored outline is the SQL
  plan baseline for this query.

42
Plan Management in Oracle11g

• Oracle records information about plans over time.
• Plan management behaves differently in 11g
• A history of plans is kept only for repeatable
  SQL.
• A statement log is kept for Oracle to see if SQL
  is repeated.
• If a plan changes due to environmental issues,
  the new plan only is used if the optimizer deems
  it an improvement over the existing one.
• Plans can be captured manually or automatically.
• These are not mutually exclusive.
• Manual capture can complement Automatic capture.

43
Baselines capturing manually and automatically

• Automatic Capture
• Baselines captured automatically with init.ora
  parm optimizer_capture_sql_plan_baselines set to
  true.
• Default is false.
• Plan history is created and kept.
• Info such as sql_text, environment, outline,
  bind variables.
• First plan use is kept as the baseline and
  history.
• All future plans go to plan history.
• Plans deemed to not cause performance degradation
  are added to the Baseline.
• This is part of plan evolution.

44
Baselines capturing manually and automatically

• Manual Capture
• Existing plans for SQL statements can be loaded
  as Baselines.
• To new or existing baselines.
• Not verified by Oracle for performance.
• Plans can be loaded from
• SQL Tuning Sets
• AWR snapshots
• Cursor cache

45
Baselines capturing manually and automatically

• Manual Capture using SQL Tuning Sets (sts)
• Use function dbms_spm.load_plans_from_sqlset
• Loads the plans from a named tuning set.
• e.g. to load plans from sql tuning set
  month_end
• SQLgt declare
• mthend pls_integer
• begin
• mthend dbms_spm.load_plans_from_sqlset
• (sqlset_name gt month_end)
• end
• /

46
Baselines capturing manually and automatically

• Manual Capture using AWR
• To use AWR snapshots, load the plans into a SQL
  Tuning Set and then follow the steps to capture
  plans from the STS.
• Example
• To load plans from a SQL Tuning Set, do the
  following
• gt In OEM, go to Performance
• gt choose the SQL Tuning Sets option
• gt choose create
• gt enter your criteria

47
Manual capture with STS in OEM- choose SQL
Tuning Sets
48
Manual capture with STS in OEM- choose Create
49
Manual capture with STS in OEM- enter the
creation options then choose next
50
Manual capture with STS in OEM- determine the
load method then choose next
51
Manual capture with STS in OEM- enter the filter
options then next
52
Manual capture with STS in OEM- enter job info
and schedule then next
53
Manual capture with STS in OEM- show the SQL and
review then Submit
54
Manual capture with STS in OEM- confirmation
that this was successful
55
Manual capture with STS in OEM- now load the plan

• You have the SQL Tuning Set that can be loaded.
• Use dbms_spm.load_plans_from sqlset

56
Baselines capturing manually and automatically

• Manual Capture using Cursor Cache
• Get the SQL in cache as shown in the previous
  section.
• Use the SQL_Text or the SQL_ID.
• Use function dbms_spm.load_plans_from_cursor_cache
  
• Loads the plans from named SQL.
• e.g. to load a single plan from SQL cursor cache
• Get the sql_id from vsql as shown in part 1.
• SQLgt declare
• csr_plan pls_integer
• begin
• csr_plan dbms_spm.load_plans_from_curso
  r_cache
• (sql_id gt 36k6xhn2zmv3q1 )
• end
• /

57
Baselines Evolving Automatically

• Steps to accept plans and add them to a baseline.
• Set optimizer_use_sql_plan_baselines to true.
• When SQL is compiled, the optimizer finds the
  best cost plan.
• A search is then done for a matching plan in the
  baseline.
• If a match is found, the optimizer continues with
  that plan.
• If no match is found, the optimizer takes the
  plan with the lowest cost from the already
  accepted plans in the SQL plan baseline.
• The best-cost plan found by the optimizer that
  does not match any baseline plans, is added to
  the SQL plan history as a non-accepted plan. This
  is a new plan.
• This is not used unless it can improve
  performance.
• Could be used in the future if a change to the
  system or to an object (e.g. index change) means
  already accepted plans cannot be recreated. In
  this case, Oracle will pick the best plan
  available.

58
Baselines Evolving Automatically (cont.)

• Evolving plans using baselines and manual plan
  loading.
• The plan baseline provides alternative backup
  approaches that can be used by the optimizer.
• Constant improvement the performance of new
  plans is being evaluated continually.
• These are integrated into SQL plan baselines to
  evolve plans with better performance.
• New plans are added to plan history as
  non-accepted plans.
• The plans in plan history that are verified to
  not cause performance regression are accepted.
• If a new plan is not worse than the SQL plan
  baseline performance, it becomes an accepted plan
  and integrates into the SQL baseline.

59
Baselines Evolving Automatically (cont.)

• Evolving plans using baselines and manual plan
  loading.
• New Plan
• Add to History (non-accepted plan)
• Verify cost is the same or better than baseline
• If true, then an accepted plan and added to the
  baseline
• Successful verification comes when the new plan
  performs better than the baseline.

60
Baselines Evolving Manually

• Evolving plans manually.
• Plans can be evolved manually from cursor cache,
  sql tuning sets and the awr repository.
• Specify a specific SQL plan to add to plan
  history of the baseline and accept it if it
  performs better.
• exec dbms_spm.evolve_sql_plan_baselines function
  
• If the new plan will perform better than the plan
  chosen from the baseline, its then accepted.

61
Baselines Evolving Manually (cont.)

• DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE (
• sql_handle IN VARCHAR2 NULL, -- ? can use
  a handle
• plan_name IN VARCHAR2 NULL, -- ? or, can
  use a plan name
• time_limit IN INTEGER DBMS_SPM.AUTO_LIMIT,
  -- ? in minutes
• verify IN VARCHAR2 'YES', -- ?
  perform a verify test on non-
• 
  accepted plans.
• commit IN VARCHAR2 'YES') -- ? accept
  it if plan is better than the baseline
• RETURN CLOB
• sql_handlesql statement id. Null to consider all
  statements with non-accepted plans in their SQL
  plan baselines (in the sql management base)
• plan_name Plan id. Default null considers all
  non-accepted plans in the sql plan baseline of
  either the identified sql statement or all sql
  statements if sql_handle is null
• time_limit In minutes. Applies if verifyYES
  (default). Used as total time limit for all
  non-accepted plan verification. Can use
  dbms_spm_auto_limit (default) to allow the system
  to choose based on number of plan verifications
  to be done. Can also use dbms_spm_no_limit or
  you can also set a number of minutes.

62
Baselines Evolving Manually (cont.)

• Second form below is used for a list of plans.
• DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE (
• plan_list IN DBMS_SPM.NAME_LIST,
  -- list of plan names
• time_limit IN INTEGER DBMS_SPM.AUTO_LIMIT,
  -- default system chooses
• verify IN VARCHAR2 'YES',
• commit IN VARCHAR2 'YES')
• RETURN CLOB
• Plan_list Each plan can be the same or different
  sql statements
• DBMS_SPM.NAME_LIST is a defined data type as
  table varchar2(30)
• Verify execute a plan baseline and run a
  non-accepted plan. If better, change non-accepted
  to accepted. VerifyNO does not execute the
  plan but changes non-accepted to accepted if it
  is deemed better.
• Commit update accepted status of non-accepted
  plans from No to Yes

63
Baselines fixed and non-fixed

• Fixed plans
• A baseline is fixed if it has one or more plans
  with the fixed attribute set to yes.
• Can be used to fix a plan for an SQL statement.
  
• Can also migrate a stored outline and load it as
  a fixed plan.
• The optimizer will choose a fixed plan with the
  lowest cost over a non-fixed plan
• Even if the non-fixed plan has a lower cost
  estimate.
• If the fixed plan cannot be reproduced, then the
  best non-fixed plan will be used.

64
Baselines fixed and non-fixed

• Fixed plans
• New plans will not be added to a fixed plan
  baseline.
• Not evolved when dbms_spm.evolve_sql_plan_baseline
  run.
• But, manually evolved when plans loaded from
  cursor cache or STS
• When using the sql tuning advisor
• When an sql profile is accepted and added to a
  fixed baseline, the plan is added as non-fixed.
• To use the plan, manually alter the plan and set
  the fixed attribute to yes.

65
Baselines Viewing Baselines

• Use proc. dbms_xplan.display_sql_plan_baseline
• Can get the sql_handle from dba_sql_plan_baselines
  or sqltext.
• select from table(dbms_xplan.display_sql_
  plan_baseline(
• sql_handlegtSYS_SQL_......., -- ? SQL
  whose plans will be displayed
• plan_namegt .. -- ? specify a
  specific plan name
• formatgtbasic) -- ? basic or
  typical or all
• Example to display all plans for an sql handle
• Select p. from table(dbms_xplan.display_sql_plan_
  baselines(
• SYS_SQL_123abc456defg789)) p
• Example to display all plans based on sql_text
• Select p. from (select distinct sql_handle from
  dba_sql_plan_baselines
• where sql_text like CUSTOMER) b,
• table(dbms_xplan.display_sql_plan_baseline(b.sql_h
  andle, null, BASIC)) p

66
Baselines Viewing BaselinesExample

• SQLgt select p. from table (dbms_xplan.display_sql
  _plan_baseline
• ('SYS_SQL_8bbad99f1c0631b0')) p
• PLAN_TABLE_OUTPUT
• SQL handle SYS_SQL_8bbad99f1c0631b0
• SQL text select empno from scott.emp where
  empno7369
• Plan name SYS_SQL_PLAN_1c0631b08bbcf177
• Enabled YES Fixed NO Accepted YES
  Origin AUTO-CAPTURE
• Plan hash value 56244932
• Id Operation Name Rows
  Bytes Cost (CPU) Time
• 0 SELECT STATEMENT
  1 4 0 (0) 000001
  
• 1 INDEX UNIQUE SCAN PK_EMP 1
  4 0 (0) 000001
• Predicate Information (identified by operation
  id)
• 1 - access("EMPNO"7369)
• 24 rows selected.

67
Baselines Viewing BaselinesExample

• Alternatively, you can get a similar result
• but will not include the predicate information
  using the following
• SQL gt Select p. from
• (select distinct sql_handle
• from dba_sql_plan_baselines
• where lower(sql_text) like
• 'select empno from scott.emp where
  empno7369') b,
• table(dbms_xplan.display_sql_plan_baseline(b.s
  ql_handle, null, 'BASIC')) p

68
Baselines moving to other DBs(can also use
this for an Upgrade)

• To upgrade from 10g to 11g high level overview
• In 10g, create an SQL Tuning Set (STS)
• Export the STS
• Upgrade to 11g
• Import the file
• Use dbms_spm.unpack_stgtab_baseline
• The old execution plans are now stored as
  baselines

69
Baselines moving to other DBs (cont.)Example

• Baselines can be moved using Data Pump or
  Export/Import.
• Use the dbms_spm package
• Overview of the steps
• Create a staging table
• Pack the baselines that you want from the SQL
  Management Base (SMB) to the staging table.
• Export the data from the staging table.
• Transfer the file to the receiving system
• Import the staging table.
• Unpack the data from the staging table into the
  SMB at the new database.

70
Baselines moving to other DBs (cont.)

• 1. First create a staging table
• use package dbms_spm.create_stgtab_baseline that
  will be used to to pack and unpack the SQL plan
  baselines.
• DBMS_SPM.CREATE_STGTAB_BASELINE (
• table_name IN VARCHAR2,
• table_owner IN VARCHAR2 NULL,
• tablespace_name IN VARCHAR2 NULL)

71
Baselines moving to other DBs (cont.)

• For Example create staging table blstage
• begin
• dbms_spm. create_stgtab_baseline(
• table_name gt blstage,
• table_owner gt OWN1,
• tablespace_name gt TS1)
• end
• /

72
Baselines moving to other DBs (cont.)

• 2. Pack the baselines with many possible
  different criteria.
• DBMS_SPM.PACK_STGTAB_BASELINE (
• table_name IN VARCHAR2,
• table_owner IN VARCHAR2 NULL,
• sql_handle IN VARCHAR2 NULL,
• plan_name IN VARCHAR2 NULL,
• sql_text IN CLOB NULL,
• creator IN VARCHAR2 NULL,
• origin IN VARCHAR2 NULL,
• enabled IN VARCHAR2 NULL,
• accepted IN VARCHAR2 NULL,
• fixed IN VARCHAR2 NULL,
• module IN VARCHAR2 NULL,
• action IN VARCHAR2 NULL)
• RETURN NUMBER

73
Baselines moving to other DBs (cont.)

• 2. Pack all enabled plans from user Scott to the
  staging table.
• Example
• declare
• stgplans number
• begin
• stgplans
• dbms_spm.pack_stgtab_baseline(
• table_name gt blstage,
• enabled gt yes,
• creator gt SCOTT)
• end
• /

74
Baselines moving to other DBs (cont.)

• 3. Use Data Pump to exp table blstage.
• - You can also use the older export utility
• 4. Move the exported file to the other server (if
  needed) and import it to another DB
• 5. Use Data Pump to imp the blstage table to the
  new database.
• - You can also use the older import utility

75
Baselines moving to other DBs (cont.)

• 6. Unpack the staging table blstage into the
  SMB using package dbms_spm.unpack_stgtab_baseline.
  
• Package syntax is below
• DBMS_SPM.UNPACK_STGTAB_BASELINE (
• table_name IN VARCHAR2, table_owner IN VARCHAR2
  NULL,
• sql_handle IN VARCHAR2 NULL, plan_name IN
  VARCHAR2 NULL,
• sql_text IN CLOB NULL, creator IN VARCHAR2
  NULL,
• origin IN VARCHAR2 NULL, enabled IN VARCHAR2
  NULL,
• accepted IN VARCHAR2 NULL, fixed IN VARCHAR2
  NULL,
• module IN VARCHAR2 NULL, action IN VARCHAR2
  NULL)
• RETURN NUMBER

76
Baselines moving to other DBs (cont.)

• 6. Unpack the baseline (cont.)
• Example unpack all fixed baselines in staging
  table blstage
• declare
• stgplans number
• begin
• stgplans
• dbms_spm.unpack_stgtab_baseline(
• table_name gt blstage,
• fixed gt yes)
• end
• /
• You are now done!

77
SQL Baseline ExampleShow and set parameters

• SQLgt sho parameter baseline
• NAME TYPE
  VALUE
• -------------------------------------------------
  -- ---------- ----------------------------
• optimizer_capture_sql_plan_baselines
  boolean FALSE
• optimizer_use_sql_plan_baselines boolean
  TRUE
• SQLgt alter system set optimizer_capture_sql_plan_b
  aselines true scope both
• System altered.
• SQLgt sho parameter baseline
• NAME TYPE
  VALUE
• -------------------------------------------------
  -- ----------- ------------------------------
• optimizer_capture_sql_plan_baselines boolean
  TRUE
• optimizer_use_sql_plan_baselines boolean
  TRUE

78
SQL Baseline Example (cont.)Showing baselines
captured

• To view the baselines
• SQLgt select count() from dba_sql_plan_baselines
• COUNT()
• 6
• SQLgt /
• COUNT()
• 8
• SQLgt /
• COUNT()
• 8
• SQLgt /
• COUNT()
• 9

79
Use OEM, go to Server choose SQL Plan Control
80
Select the SQL Plan Baseline tab at the top of
page.See below that plans not used for 53 weeks
will be purged.Enter your SQL into the search
string and press Go.
81
SQL Baseline example

• On the previous slide you will see the following
• Parms Capture SQL Plan Baselines and Use SQL
  Plan Baselines are set to true.
• Retention weeks are set to 53 the default.
• Ive entered search text into the SQL Text
  field.
• All baselined plans shown have been Enabled are
  can be considered.
• All plans have been Accepted and are therefore
  acceptable plans for a query.
• None of the plans are marked as Fixed, so there
  are no fixed plans that the optimizer would only
  consider.
• All plans will be Auto Purged after 53 days.

82
Your SQL shows up based on entered sql
textSQLgt Select count() from
dba_sql_plan_baselines
83
Click on the SQL to get the plan details. Hit
Return once youve viewed the plan
84
You now have options to deal with this
baseline Select the plan (bottom left) and click
Evolve (if the plan is not yet accepted).
85
Choose verify performance to compare this to
the existing baseline for the query. Click OK
86
We see that this is already an accepted plan and
that no SQL Plan Baselines required processing.
87
You can change the status with package dbms_spm

• If the plan shown on the previous slide was not
  accepted, you can change it as follows
• declare
• spb binary_integer
• begin
• spb dbms.spm.alter_sql_plan_baseline (
• sql_handle gt SYS_SQL_a0b12c34d5678901,
• plan_name gt SYS_SQL_PLAN_7b33b11994d46ac6,
  
• attribute_name gt ACCEPTED,
• attribute_value gt NO )
• end
• /

88
DBMS_SPM Subprograms

• The DBMS_SPM package contains 9 subprograms to
• manage baselines.
• alter_sql_plan_baseline function
• Change an attribute of a plan or plans associated
  with an SQL statement.
• configure procedure
• SQL Management Base (SMB) configuration options
• create_stgtab_baseline procedure
• Create a staging table to move baselines to
  another system.
• drop_sql_plan_baseline function
• evolve_sql_plan_baseline function

89
DBMS_SPM Subprograms (cont.)

• load_plans_from_cursor_cache function
• Loads one or more plans in the cursor cache for a
  SQL statement.
• load_plans_from_sqlset function
• Loads 1 or more plans from a SQL Tuning Set (STS)
  into SQL plan baselines.
• pack_stgtab_baseline function
• Packs (exports) sql plan baselines from SQL
  management Base (SMB) to a staging table.
• unpack_stgtab_baseline function
• Imports the plans from the staging table to the
  SMB.

90
SQL Management Base (SMB)

• SQL Management Base (SMB) overview
• Contains
• Statement logs
• Plan histories
• Plan baselines
• SQL profiles
• Stored in sysaux tablespace (this TS must be
  available)
• Configured with auto space management (asm)
  enabled.
• Allows weekly purge of unused plans and logs.
• Useful when upgrading to 11g to prevent plan
  regression.

91
SMB Maintenance

• Storage
• Limit is set to 10 the size of sysaux by
  default.
• Can range from 1 to 50
• Weekly process checks this and writes a warning
  to the alert log when this is exceeded.
• If full, either increase SMB space limit, or
  sysaux size or perform a purge of plan baselines
  or profiles.
• To change the percent limit
• begin
• dbms_spm.configure(space_budget_percent,2
  5)
• end

92
SMB Maintenance (cont.)

• Storage and Purge policy
• Can set retention weeks from 5 to 523 with
  default 53
• begin dbms_spm.configure(plan_
  retention_weeks,125)
• end
• Any plan unused for 53 weeks will be purged by
  default.
• Uses last_executed value in SMB
• Check the config in view dba_sql_management_config
  
• SQLgt Select parameter_name,
  parameter_value
• from dba_sql_management_config
  

93
SMB Maintenance (cont.)

• Storage and Purge policy
• Check the config in view dba_sql_management_config
  
• SQLgt Select from dba_sql_management_config
• parameter_name parameter_value last_modified
  modified_by
• space_budget_percent 10
• Plan_retention_weeks 53

94
SMB Management

• Migrating your Stored Outlines to SQL Plan
  baselines
• If you are using stored outlines and want to
  migrate to SQL plan baselines
• You can do this with the using the DBMS_SPM
  package.
• Migrate stored outlines to baselines using one
  of
• DBMS_SPM.load_plans_from_cursor_cache
• DBMS_SPM.load_plans_from_sqlset
• After migration has been completed, disable or
  remove the stored outlines.

95
Part 2 SQL Plan ManagementSummary

• Wrap-up of Part 2
• Plan management behaves differently in 11g
• Oracle records information about plans over time.
• SQL Baselines are an enhancement to stored
  outlines
• Automated approaches simplify maintenance
• Manual approaches may be used to complement
  automation
• Baselines are evaluated for better plans and used
  if that is the case.
• SQL baselines can evolve over time.

96
Conclusion

• Storing historical plans and statistics can be
  used initially as a backup plan when problems
  occur.
• useful with Oracle upgrades as well as
  application upgrades and for environment or
  object changes.
• Start with stored outlines in 10g
• Upgrade to baselines in 11g

Tim Quinlan TLQ Consulting Inc.