JavaScript is disabled on your browser. Enable JavaScript to use this site. Learn more

Plan Stability, Baselines and SQL Plan Management

Add to Favourites

Post to:

AIOUG Arup Nanda

Comments

Presentation Transcript

About Me : (c) 2010 Arup Nanda 2 About Me Oracle DBA for 16 years and counting Speak at conferences, write articles, 4 books, provides trainings Brought up the Global Database Group at Starwood Hotels, in White Plains, NY

Meet Raj the DBA : (c) 2010 Arup Nanda 4 Meet Raj the DBA Raj is a DBA at Maharaja Ltd. Honest, hardworking, highly experienced But not politically savvy; doesn’t beat around the bush. straight shooter Let’s see some scenarios he faced in the job

Third Party Tool : (c) 2010 Arup Nanda 5 Third Party Tool Maharaja bought a third party gee-whiz tool The performance was terrible Raj was asked to explain why! He analyzed and determined the cause: bad optimizer plans He suggested putting hints to fix the plans But no, he can’t. The source code is not accessible, remember? Status: still unresolved and Raj is to blame!

Analyzer Gone Wild : (c) 2010 Arup Nanda 6 Analyzer Gone Wild Raj collects optimizer stats every day One day performance went south, apps timed out On analysis he found that the plan of those queries had changed The plan changed because of the new stats Raj got blamed for the fiasco

Stale Stats : (c) 2010 Arup Nanda 7 Stale Stats Raj heard somewhere that stats should not be collected everyday He decided to stop collecting stats did so only occasionally One day performance went south Cause: Optimizer Plan was bad Reason: stale stats He was blamed!

Database Upgrade : (c) 2010 Arup Nanda 8 Database Upgrade Raj wanted to upgrade a DB from 10g to 11g He was asked “can you guarantee that plans will not change” “Of course not”, he responded. “But most likely they will not” Upgrade completed Most plans were OK; some went south. Raj was blamed for that

Plan Changes : (c) 2010 Arup Nanda 9 Plan Changes A developer complains about performance Raj checks the plan and finds a bad plan a full table scan, which should have been index scan or may be vice versa He asks the developer “is the data different”? “No”, comes the reply. “has been the same for 4 years”. Raj has no history of the plan Oracle is misbehaving – was the “root cause” Who do you think was blamed?

Optimizer Misbehaves : (c) 2010 Arup Nanda 10 Optimizer Misbehaves Oracle Cost Based Optimizer sometimes does not produce most optimal plan Difficult to debug Well, Raj takes the blame for that as well!

Stored Outlines : (c) 2010 Arup Nanda 11 Stored Outlines For inefficient plans, Raj does have a solution Outlines make a plan for a query fixed The optimizer will pick up the fixed plan every time Problem: Based on the bind variable value, data distribution, etc. specific plan may change A fixed plan may actually be worse

The Problem : (c) 2010 Arup Nanda 12 The Problem If optimizer calculates execution plans, it may produce inefficient ones If you use stored outlines, a fixed plan may be as inefficient as to be noticeable Can you have the best of both words? Have plan fixed by outlines But calculate the new plan anyway for comparison and use if appropriate Baselines do exactly that … and more

Quick Primer on Parsing : (c) 2010 Arup Nanda 13 Quick Primer on Parsing When a query is submitted, Oracle performs the following: Determines if there is a parsed statement Parses query Determines the objects being accessed e.g. is EMP a table or a synonym Determines if the user has privs on that object Calculates the optimal execution plan Binds the values to the variables Stores the parsed statement in library cache

Statement Versions : (c) 2010 Arup Nanda 14 Statement Versions SELECT * FROM EMP WHERE SAL>1000 SCOTT EMP table in SCOTT schema ARUP SELECT * FROM EMP WHERE SAL>1000 EMP table in ARUP schema No index on SAL column Will Generate a Full table scan Index on SAL column Will probably generate an index scan Demo: cur_test.sql

Slide 15 : (c) 2010 Arup Nanda 15 SELECT * FROM EMP WHERE SAL>1000 SQL Statement SQL_ID = a1b2c3d4 SELECT ACCESS TABLE INDEX SELECT ACCESS TABLE INDEX optimizer_goal = first_rows db_file_multiblock_read_count optimizer_goal = first_rows db_file_multiblock_read_count Plan 1 PLAN_HASH_VALUE = 1a2b3c Plan 2 PLAN_HASH_VALUE = 2a3b4c A single SQL statement may have multiple plans associated with it

Slide 17 : (c) 2010 Arup Nanda 17 SQL Statement S1 Plan P1 Plan P2 Plan P3 Baseline Plan P4 A new plan was generated as a result of some change, e.g. the optimizer parameters were changed. This plan is added to the baseline

Slide 18 : (c) 2010 Arup Nanda 18 SQL Statement S1 Plan P1 Plan P2 Plan P3 Baseline Plan P4 When a SQL is reparsed, the optimizer compares the plan to the list of plans in the baseline, but not the newly generated plan as it is not “accepted”.

Slide 19 : (c) 2010 Arup Nanda 19 SQL Statement S1 Plan P1 Plan P2 Plan P3 Baseline Plan P3 A plan is no longer valid, e.g. it had an index scan; but the index was later dropped. It is marked as such.

New Plan is Worse : (c) 2010 Arup Nanda 20 New Plan is Worse Baselines contain the history of plans for an SQL statement If there was a good plan ever, it will be there in the baseline So the optimizer can choose the plan with the lowest cost Plan P1 Plan P2 Plan P3 Baseline Plan P4 Cost = 10 Cost = 12 Cost = 9 New plan. Cost = 15 Optimizer will choose P3 even though the new plan generated was P4

New Plan is the Best : (c) 2010 Arup Nanda 21 New Plan is the Best Even if the new plan is the best, it will be not be immediately used The DBA can later made the plan fit for consideration by “evolving” it! Plan P1 Plan P2 Plan P3 Baseline Plan P4 Cost = 10 Cost = 12 Cost = 9 New plan. Cost = 6 Optimizer will choose P3 since it is the best in the list of “accepted” plans

Slide 22 : (c) 2010 Arup Nanda 22 SQL Statement New Plan Generated any other accepted plans in baseline? Add this plan to the SMB Baseline this plan but set to Not Accepted Choose the best accepted plan Use this best plan, not the new plan yes no

Configuring SMB : (c) 2010 Arup Nanda 24 Configuring SMB To Check select parameter_name, parameter_value from dba_sql_management_config; PARAMETER_NAME PAMETER_VALUE ----------------------- ------------- SPACE_BUDGET_PERCENT 10 PLAN_RETENTION_WEEKS 53 To Change: BEGIN DBMS_SPM.CONFIGURE( 'PLAN_RETENTION_WEEKS',100); END;

Baselines Demo : (c) 2010 Arup Nanda 28 Baselines Demo Setup: spm_test1 Table: SQL> select status, temporary, count(1) 2 from accounts 3 group by status, temporary; STATUS T COUNT(1) ------- - ---------- VALID N 68416 INVALID N 1 VALID Y 138 Query: select /* SPM_TEST */ * from accounts where status = 'INVALID' and temporary = 'Y'

To check for Plans in the baseline : (c) 2010 Arup Nanda 29 To check for Plans in the baseline select SQL_HANDLE, PLAN_NAME from dba_sql_plan_baselines where SQL_TEXT like '%SPM_TEST%' / SQL_HANDLE PLAN_NAME ------------------------------ ----------------------------- SYS_SQL_4602aed1563f4540 SYS_SQL_PLAN_563f454011df68d0 SYS_SQL_4602aed1563f4540 SYS_SQL_PLAN_563f454054bc8843 SQL Handle is the same since it’s the same SQL; but there are two plans

Checking Plans Being Used : (c) 2010 Arup Nanda 30 Checking Plans Being Used Execution Plan ---------------------------------------------------------- Plan hash value: 2329019749 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 17139 | 1690K| 588 (1)| 00:00:08 | |* 1 | TABLE ACCESS BY INDEX ROWID| ACCOUNTS | 17139 | 1690K| 588 (1)| 00:00:08 | |* 2 | INDEX RANGE SCAN | IN_ACCOUNTS_01 | 34278 | | 82 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("TEMPORARY"='Y') 2 - access("STATUS"='INVALID') Note ----- - SQL plan baseline "SYS_SQL_PLAN_51f8575d04eca402" used for this statement This shows that a SQL Plan Baseline is being used.

To See Plan Steps in Baseline : (c) 2010 Arup Nanda 31 To See Plan Steps in Baseline Package DBMS_XPLAN has a new function called display_sql_plan_baseline: select * from table ( dbms_xplan.display_sql_plan_baseline ( sql_handle=>'SYS_SQL_4602aed1563f4540', format=>'basic note') )

Demo: Adding Baselined Plans : (c) 2010 Arup Nanda 32 Demo: Adding Baselined Plans Demo: spm_test2 alter session set optimizer_capture_sql_plan_baselines = true / … execute the query at least 2 times alter session set optimizer_capture_sql_plan_baselines = false / A plan is baselined when a SQL is executed more than once

Adding more plans : (c) 2010 Arup Nanda 33 Adding more plans Demo: spm_test3 Change the optimizer parameter so that a new plan is generated alter session set optimizer_mode=first_rows Capture the plans for the baseline The new plan is stored in baseline but not “accepted”; so it will not be used by the optimizer

Evolve a Plan : (c) 2010 Arup Nanda 34 Evolve a Plan Make a plan as acceptable (only if it is better) variable rep CLOB begin :rep := dbms_spm.evolve_sql_plan_baseline ( sql_handle => 'SYS_SQL_5a8b6da051f8575d' , verify => ‘YES' ); end; / Variable REP shows the analysis. Demo: spm_test4.sql

Check the use of new plan : (c) 2010 Arup Nanda 35 Check the use of new plan Demo: spm_test5 alter session set optimizer_use_sql_plan_baselines = false Check plan alter session set optimizer_use_sql_plan_baselines = true Check plan

Fixing a Plan : (c) 2010 Arup Nanda 36 Fixing a Plan A plan can be fixed by: dbms_spm.alter_sql_plan_baseline ( sql_handle => ‘SYS_SQL_5a8b6da051f8575d', plan_name => 'SYS_SQL_PLAN_51f8575d04eca402', attribute_name => 'fixed', attribute_value => 'YES' ) Once fixed, the plan will be given priority More than one plan can be fixed In that case optimizer chooses the best from them To “unfix”, use attribute_value => ‘NO' spm_test6.sql

Capturing Baselines in Bulk : (c) 2010 Arup Nanda 37 Capturing Baselines in Bulk Setting system parameter alter system set optimizer_capture_sql_plan_baselines = true Capture from Cursor Cache Capture form SQL Tuning Set (STS) Convert from Stored Outlines (11gR2)

Capturing from Cursor Cache : (c) 2010 Arup Nanda 38 Capturing from Cursor Cache declare cnt number; begin cnt := dbms_spm.load_plans_from_cursor_cache (sql_id => '003vmga5rcrs4');cnt := dbms_spm.load_plans_from_cursor_cache (sql_id => '005nuc1nd7u93');cnt := dbms_spm.load_plans_from_cursor_cache (sql_id => '009su850aqyha'); end;

Capturing from Cursor Cache : (c) 2010 Arup Nanda 39 Capturing from Cursor Cache declare cnt number; begin cnt := dbms_spm.load_plans_from_cursor_cache( attribute_name => 'sql_text', attribute_value => '%SPM_TEST%' ); end;

Capturing from STS : (c) 2010 Arup Nanda 40 Capturing from STS declare cnt number; begin cnt := dbms_spm.load_plans_from_sqlset( sqlset_owner => 'SYS', sqlset_name => 'TEST_STS', basic_filter => '%SPM_TEST%' ); end;

How else can you tune a query : (c) 2010 Arup Nanda 42 How else can you tune a query You can also use SQL Tuning Advisor Create a tuning task variable l_task varchar2(2000) exec :l_task := dbms_sqltune.create_tuning_task( sql_id => 'cbynbmssqudbx'); Execute the task exec dbms_sqltune.execute_tuning_task( task_name => :l_task)

Slide 43 : (c) 2010 Arup Nanda 43 Check for recommendations select dbms_sqltune.report_tuning_task( :l_task, 'TEXT', 'BASIC') FROM dual; If there is a SQL Profile, accept it exec dbms_sqltune.accept_sql_profile( task_name => :l_task); This will add the tuned plan as per SQL Profile to the baseline as well. So you can use either Evolve or STA for creating baselined plans

Use of Baselines : (c) 2010 Arup Nanda 44 Use of Baselines Fixing Plan for Third Party Applications Database Upgrades Both within 11g and 10g->11g Capture SQLs into STS then move the STS to 11g Database Changes Parameters, Tablespace layout, etc. Fix first; then gradually unfix them