Tim Quinlan TLQ Consulting Inc.

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

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$, OL$HINTS, OL$NODES 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. SQL> select name, owner, category from dba_outlines where category='Client_CashTxns'; NAME OWNER CATEGORY SYS_OUTLINE_08101715152990267 APPOWNER CapturedCat SQL> 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 they’re 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 > for 1 specific SQL statement - OR - > 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? SQL> select value from v$parameter where name = 'create_stored_outlines'; VALUE MONTHLY2008DEC10 SQL> 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 … SQL> 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 SQL> select value from v$parameter where name = 'create_stored_outlines'; VALUE MONTHLY2008DEC10 SQL> select owner, category, count(*) from dba_outlines group by owner, category; CATEGORY COUNT(*) MONTHLY2008DEC10 593 • Turn off the capture SQL> alter system set create_stored_outlines = false; • Check that this is turned off: SQL> select value from v$parameter 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: SQL> select sql_id, hash_value, child_number, sql_text from v$sql where upper(sql_text) like upper('MERGE INTO detailtable%'); SQL_ID HASH_VALUECHILD_NUMBERSQL_TEXT 36k6xhn2zmv3q 100265078 0 MERGE INTO detailtable… • Make sure the user has authority to create outlines: SQL> 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 V$SQL 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"' log=${DMP_DIR}/expOneOutln.log buffer=10485760 file=${DMP_DIR}/expOneOutln.dmp tables='outln.ol$','outln.ol$hints' query=\"where category=\‘OneOutlnCat\'\" • Import the Outline to a new database imp '"/ AS SYSDBA"' log=${DMP_DIR}/impOneOutln.log fromuser=OUTLN touser=OUTLN buffer=10485760 commit=y ignore=y file=${DMP_DIR}/expOneOutln.dmp grants=n indexes=n rows=y constraints=n

24. Part 1: Plan StabilityUsing a Stored Outline in your Application Keeping an Outline Active • Can an outline become inactive after it’s 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. SQL> alter system set use_stored_outlines=UseThisCategory; - 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 v$sql • outline_sid column is “0” for a public outline or has the session sid for a private outline. SQL> Select outline_category, first_load_time, last_load_time, last_active_time, sql_text From v$sql 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/09:22:49 2009-01-26/07:56:13 29-JAN-09 12:35:41

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_outlines=true and run “explain plan” or run the SQL statement. • Once you’re happy with the edits, publish the new outline: • create or replace outline old_outln from private new_outln; • Disable private outline use: • Use_private_outlines=false;

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 [all|dba|user]_outlines [all|dba|user]_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: SQL> select sql_text from dba_outlines where name = 'SYS_OUTLINE_08121014000435157‘ select specialtaxrate from planaccount where accountid = :B1 SQL> Select node, stage, join_pos, hint from dba_outline_hints Where name = 'SYS_OUTLINE_08121014000435157 ‘ order by 1,2,3; NODESTAGEJOIN_POSSQL_TEXT 1 1 0 OUTLINE_LEAF(@"SEL$1") 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(@"SEL$1" “planaccount"@"SEL$1" (“planaccount".“accountid"))

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 < 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"' log=${MOVETS_DIR}/expOutlnMoveTS.log owner=outln buffer=10485760 file=${MOVETS_DIR}/expOutlnMoveTS.dmp grants=y indexes=y rows=y constraints=y consistent=y triggers=y direct=n 3. Make security changes in SQL*Plus and drop the tables. SQL> revoke unlimited tablespace from outln; SQL> alter user outln default tablespace sysaux; SQL> alter user outln quota unlimited on sysaux; SQL> drop table outln.ol$; SQL> drop table outln.ol$HINTS; SQL> drop table outln.ol$NODES;

34. Part 1: Plan StabilityMoving Outlines Moving outlines from the System tablespace (cont.) 4. Run the import imp '"/ AS SYSDBA"' log=${MOVETS_DIR}/impOutlnMoveTS.log fromuser=OUTLN touser=OUTLN buffer=10485760 commit=y ignore=y file=${MOVETS_DIR}/expOutlnMoveTS.dmp grants=y indexes=y rows=y constraints=y 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 v$sql where sql_text like 'INSERT%INTO 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 category=‘New_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"' log=${MOVETS_DIR}/expOutln.log buffer=10485760 file=${MOVETS_DIR}/expOutln.dmp tables='outln.ol$','outln.ol$hints' query=\"where category=\‘MOVEPLAN\'\" 7) Import the Outline to the new database imp '"/ AS SYSDBA"' log=${MOVETS_DIR}/impOutln.log fromuser=OUTLN touser=OUTLN buffer=10485760 commit=y ignore=y file=${MOVETS_DIR}/expOutln.dmp grants=n indexes=n rows=y constraints=n

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. > 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. > There is no conflict using both. > 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” SQL> declare mthend pls_integer; begin mthend := dbms_spm.load_plans_from_sqlset (sqlset_name => ‘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: > In OEM, go to Performance > choose the SQL Tuning Sets option > choose “create” > 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