1 / 96

Tim Quinlan TLQ Consulting Inc.

Guaranteeing High Performing SQL through Plan Stability and Management. Tim Quinlan TLQ Consulting Inc. 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.

Download Presentation

Tim Quinlan TLQ Consulting Inc.

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related