1 / 40

Hints Outlines/Profiles/Baselines

Hints Outlines/Profiles/Baselines. Kyle Hailey http://oraclemonitor.com. SQL Plan Stability. When to tune?. Users complain Response times slow Resource usage high AAS of system is high Check OEM LIOs high per row, TCF discrepancies high script. TCF , LIO and Elapsed.

kimn
Download Presentation

Hints Outlines/Profiles/Baselines

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. HintsOutlines/Profiles/Baselines Kyle Hailey http://oraclemonitor.com SQL Plan Stability

  2. When to tune? • Users complain • Response times slow • Resource usage high • AAS of system is high • Check OEM • LIOs high per row, TCF discrepancies high • script

  3. TCF , LIO and Elapsed ELAPSED LIO_RW RATIO1 E_ROWS A_ROWS operation ---------- ------ ------ ------- ------ --------------------------------- 0 0 1 SELECT STATEMENT 5,720,456 0 1 1 HASH GROUP BY 29,711 0 1,909 NESTED LOOPS 0 0 +++ 1 1,909 NESTED LOOPS 1,969,304 0 +++ 1 1,909 NESTED LOOPS 0 0 +++ 1 2,027 NESTED LOOPS 7,939,649 0 +++ 1 1,656 NESTED LOOPS 716,054 0 +++ 1 1,657 NESTED LOOPS 270,201 0 ++ 39 23,171 HASH JOIN 23 0 5 1 JOIN FILTER CREATE :BF00 31 1 5 1 TABLE ACCESS BY INDEX R 14 2 5 1 INDEX RANGE SCAN PS0PA 141,467 0 18,503 23,171 VIEW VW_SQ_1 3,032,120 0 18,503 23,171 HASH GROUP BY 152,564 0 163,420 33,020 JOIN FILTER USE :BF000 407,746 0 163,420 33,020 MERGE JOIN 55 0 5 1 SORT JOIN 12 2 5 1 INDEX RANGE SCAN PS 79,435 0 40,000 33,020 SORT JOIN 119,852 0 40,000 40,000 INDEX FAST FULL SCA 2,959,031 13 - 23,171 1,657 TABLE ACCESS BY INDEX ROW 944,887 1 23,171 23,174 INDEX RANGE SCAN WB_JOB

  4. How to tune • Parameters (init.ora, spfile, session level) • Hacking, global • Rewrites • Hacking, specific • Object Stats (Optimizer cardinality and cost calculations) • Hints (Outlines, Profiles ,Baselines) • Index (good cluster, bad cluster, index only) • Partitions, Materialized views, Hash clusters

  5. HINTS • ORDERED • Leading(tab_alias, tab_alias, …) • USE_NL(table_alias) – Inner Table (not driving) • USE_HASH(table_alias) – 2cd table, probe into • INDEX(tab_alias index_name) • NO_MERGE Oracle first decides join order then join type (example http://www.adp-gmbh.ch/blog/2008/01/17.php)

  6. Table X Table X Filter Index Col A Filter Index Col A Join Index Col B Join Index Col B NL Table Y Driving Table HJ Table Y Filter Index Col C Filter Index Col C Join Index Col D Join Index Col D Drive from Table Y off of set of rows returned from filter on column C Nested loops into Table X on Index on Join Filter results without Index even though index on filter column Create hash result set on Table Y from filter on column C Probe hash result set with rows from filter on table X on column A

  7. NL and HJ hints Nested Loops (start with A probe in B) /*+ leading(A) use_nl(B) */ /*+ ordered use_nl(B) */ • Hash Join (hash A loop up B in A) /*+ leading (A) use_hash(B) */ /*+ ordered use_hash (B) */ Select * from A,B where A.f1=B.f1

  8. INDEX HINT • INDEX(Table_alias INDEX_NAME) • 10g: • INDEX(table_alias (col1 col2 …)

  9. TCF • TCF => optimal execution plan path • How about “Is the access path optimal ? ie indexes, partitions, constraints Christian Antognini p341 TOP • < 5 lio per row • < 10 lio ok • > 15 potentially sub-optimal

  10. Outlines, Profiles, Baselines • Outlines 8i • Apply a set of hints to query to stabilize plan • Profiles 10g • Apply hints to shift statistics to help optimizer • Use for multiple statements that only differ by literals • Baselines 11 • Track new plans and “evolve” them if better

  11. Outline tables ol$hint OL_NAME HINT# CATEGORY HINT_TYPE HINT_TEXT STAGE# NODE# TABLE_NAME TABLE_TIN TABLE_POS REF_ID USER_TABLE_NAME COST CARDINALITY BYTES HINT_TEXTOFF HINT_TEXTLEN JOIN_PRED SPARE1 SPARE2 HINT_STRING ol$ OL_NAME SQL_TEXT TEXTLEN SIGNATURE HASH_VALUE HASH_VALUE2 CATEGORY VERSION CREATOR TIMESTAMP FLAGS HINTCOUNT SPARE1 SPARE2 If you drop the hints in ol$hint for a name then rename another set of hints with the old name then the sql text in ol$ will get the new hints ol$nodes OL_NAME CATEGORY NODE_ID PARENT_ID NODE_TYPE NODE_TEXTLEN NODE_TEXTOFF NODE_NAME

  12. Outline Switching

  13. Profiles

  14. Profile Switching • Get bind variables if necessary • build_bind_vars.sql • Tune query with hints (or any other method) • Create a profile on the new query • create_sql_profile.sql  (calls rg_sqlprof1.sql) • fix the Profile to eliminate any bad index hints - • fix_sql_profile_hint.sql • move the profile over to the original statement • move_sql_profile.sql http://kerryosborne.oracle-guy.com/2009/10/how-to-attach-a-sql-profile-to-a-different-statement-take-2/

  15. SQL Baselines • Outlines are way outdated in 11g • they still take precedence over Baselines • Baselines can be switched between statements even easier than outlines http://kerryosborne.oracle-guy.com/2009/04/oracle-11g-sql-plan-management-sql-plan-baselines/ http://kerryosborne.oracle-guy.com/category/oracle/plan-stability/

  16. SQL Baselines • Turn on with • OPTIMIZER_USE_SQL_PLAN_BASELINE =True • True by default • DBA_SQL_PLAN_BASELINES • Lists baselines that have been created • Create with dbms_spm.load_plans_from_cursor_cache (sql_id=>'&sql_id', - plan_hashvalue=>&plan_hash_value,- fixed=>'&fixed');

  17. SQL Baselines – Switching SQL_ID PLAN_HASH SQL_TEXT ------------- ---------- ---------------------------------------------------- 3trqfzj8yhu6j2709260180 select /*+ index (a col1_idx) */ avg(pk_col) from sk a2h75xrkn1xh33498336203 select avg(pk_col) from skew a where col1 > 0 dbms_spm.load_plans_from_cursor_cache( sql_id => 'a2h75xrkn1xh3', -- Original plan_hashvalue => 3498336203 -- query ); select sql_handle from dba_sql_plan_baselines Where sql_text = 'select avg(pk_col) from kso.little_skew where col1 > 0‘; SQL_HANDLE ------------------------------ SYS_SQL_6560e8852671e3e3 dbms_spm.load_plans_from_cursor_cache(- sql_id => '3trqfzj8yhu6j', -- new query plan_hashvalue => 2709260180 , -- with hint sql_handle => 'SYS_SQL_6560e8852671e3e3' /* old basline’s SQL_HANDLE */ ); http://kerryosborne.oracle-guy.com/2009/04/oracle-11g-sql-plan-management-sql-plan-baselines/

  18. Adaptive Cursor Sharing • Different plans for different bind variables • Only for statements that have 14 or less variables (?!)

  19. Adaptive Cursor Sharing select * from t1 where id < :id; SELECT child_number, is_bind_sensitive, is_bind_aware, is_shareable FROM v$sql WHERE sql_id = '&sql_id' ORDER BY child_number; CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE ------------ ----------------- ------------- ------------ 0 Y N N 1 Y YY 2 Y YY IS_BIND_SENSITIVE - peeking used? IS_BIND_AWARE - adaptive cursor sharing ? IS_SHAREABLE - can be shared

  20. Adaptive Cursor Sharing SELECT child_number, peeked, executions, rows_processed, buffer_gets FROM v$sql_cs_statistics WHERE sql_id = '&sql_id' ORDER BY child_number; CHILD_NUMBER PEEKED EXECUTIONS ROWS_PROCESSED BUFFER_GETS ------------ ------ ---------- -------------- ----------- 0 Y 1 19 3 1 Y 1 990 19 2 Y 1 19 3 SELECT child_number, predicate, low, high FROM v$sql_cs_selectivity WHERE sql_id = ‘&sql_id' ORDER BY child_number; CHILD_NUMBER PREDICATE LOW HIGH ------------ --------- ---------- ---------- 1 <ID 0.890991 1.088989 2 <ID 0.008108 0.009910

  21. Hints

  22. All/First Rows • All_rows • First_rows – 9i+ • First_rows_N – N in 1,10,100,1000 • First_rows(n) – n any positive number • Only analyzes the first join order • Get an estimate on rows returned • Restart optimization • First Rows • deprecated in 9i, but maintained • Avoid merge joins and hash joins unless alternative is nested loop with full tabelscan on inner table • Tends to use indexed access pats

  23. Hints - subqueries • MERGE / NO_MERGE • UNNEST / NO_UNNEST • PUSH_SUBQ / NO_PUSH_SUBQ • NO_QUERY_TRANSFORMATION • Don’t do any of the above

  24. UNNEST • 8i unnested nothing • 9i unnested everything • 10g tries to work out the cost http://www.ardentperf.com/2007/07/18/10g-subquery-unnesting-anomalies/

  25. UNNEST • Choices • NO UNNEST : Filter • scan the driving table • execute the subquery whenever necessary (a filter) • /*+ no_unnest */ • UNNEST • create a result set with the structure (deptno, avg_sal) • join to the driving table • /* unnest */ select outer.* from emp outer where outer.sal > ( select avg(inner.sal) from emp inner where inner.dept_no = outer.dept_no);

  26. UNNEST Manual select outer.* from emp outer where outer.sal > ( select avg(inner.sal) from emp inner where inner.dept_no = outer.dept_no); UNNEST Select /*+ NO_MERGE */ outer.* from emp outer ( select dept_no, avg(inner.sal) avg_sal from emp group by dept_no ) inner Where outer.dept_no = inner.dept_no and outer.sal > inner.avg_sal; alas, if you do this in recent versions of Oracle you might then need to stop the optimizer from doing a cunning – but possibly catastrophically inefficient – piece of complex view merging by including the /*+ no_merge */ hint in what is now the inline view (you could also achieve this through a /*+ no_merge(inner) */ in the main query).

  27. UNNEST VST select outer.* from emp outer where outer.sal > ( select avg(inner.sal) from emp inner where inner.dept_no = outer.dept_no); Emp (outer) Either execute for each row or Calculate avg(sal) for all rows as a view v v Emp (inner)

  28. PUSH_SUBQ • Evaluate subqueries as soon as possible • When unnest can’t be done, then subqueries are normally evaluated at the end

  29. PUSH_SUBQ VST Select par.small_vc1, chi.small_vc1 From parent par, child chi Where par.id1 between 100 and 200 and chi.id1 = par.id1 and exists ( select /*+ no_unnest */ null from subtest sub where sub.small_vc1 = par.small_vc1 and sub.id1 = par.id1 and sub.small_vc2 >= '2'); Child (child) v F F Subtest (sub) Parent (par) N

  30. Subquery Parmeters

  31. Subquery types

  32. SQL Tuning Rules values 1 field = val : SEL = (1/distinct) * nrows 2 field in (val1,val2,...,valn) 8i : SEL = (1/distinct) * n * nrows - n*(1/distinct)^2 + (1/distinct)^n 9i+ : SEL = (1/distinct) * n * nrows, n <= distinct, else n=distinct 3 field = val_out_of_range : SEL = (1/distinct) *nrows (wrong) 10.1.0.4+ : SEL = 1/distinct * ( 1 - max(1,distance out of range/(distinct) ) *nrows 4 field > val_out_of_range : SEL = (1/distinct) *nrows 5 field > val : SEL = (high-val)/(high-low)*nrows 6 field >= val : SEL = (high-val)/(high-low)*nrows + 1/distinct * nrows 7 field > val1 and field < val2 : SEL = (val2-val1)/(high-low)*nrows 8 field >= val1 and field <= val2: SEL = (val2-val1)/(high-low)*nrows + 1/distinct * nrows + 1/distinct * nrows 9 field > val1 and field < val2 : SEL = (1/distinct) *nrows 10.1.0.4+ : SEL = ((1/distinct*(1-max(1,val1 distance out of range/(distinct)) - (1/distinct*(1-max(1,val2 distance out of range/(distinct))* nrows 11 field > val1 OR field < val2 : sel(val1) + sel(val2) - sel(val1 and val2) variables 1 field = :var : SEL = (.05 * nrows) 2 field > :var : SEL = (.05 * nrows) 3 field >= :var : SEL = (.05 * nrows) 4 field like :var : SEL = (.05 * nrows) 5 field > :var and field < :var : SEL = (.05 * .05 * nrows) 6 field >= :var and field <= :var : SEL = (.05 * .05 * nrows)

  33. Most Abused Parameters* • OPTIMIZER_INDEX_CACHING • OPTIMIZER_INDEX_COST_ADJ • DB_FILE_MULTIBLOCK_READ_COUNT • Global band-aid for specific problems, it might help one query and hurt others *Greg Rahn

  34. Solutions* • Data skew: Choose a sample size that yields accurate NDV. Use DBMS_STATS.AUTO_SAMPLE_SIZE in 11g. • Data correlation: Use Extended Stats in 11g. If <= 10.2.0.3 use a CARDINALITY hint if possible. • Out-of-range values: Gather or manually set the statistics. • Use of functions in predicates: Use a CARDINALITY hint where possible. • Stats gathering strategies: Use AUTO_SAMPLE_SIZE. Adjust only where necessary. Be mindful of tables with skewed data. • Greg Rahn - http://structureddata.org/2007/11/21/troubleshooting-bad-execution-plans/

  35. DBA_HIST_SQLSTAT SQL> @whats_changed Enter Days ago: 3 Enter value for min_stddev: Enter value for min_etime: Enter value for faster_slower: SQL_ID EXECS AVG_ETIME_BEFORE AVG_ETIME_AFTER NORM_STDDEV RESULT ------------- ------------ ---------------- --------------- ------------- ------ an9114vtxukz2 17 1.25 5.00 2.1148 Slower 803u6vwz0ah1p 29 0.04 0.18 2.1552 Slower bvf3fxv3hatw7 2,390 0.03 0.14 2.4147 Slower 5sgs7q9pjnzg5 2 2.21 0.42 3.0130 Faster b0zkuv9qtcvnn 48 0.16 0.85 3.1500 Slower 9ws5c6p77d384 1,852 0.57 3.30 3.3609 Slower 05xcf43d9psvm 1,197 0.02 0.14 5.4263 Slower 8wgmc9w5ubtsy 183 0.49 4.32 5.4946 Slower fwfpuf1vfwfu2 6 0.02 0.22 5.6314 Slower 50c18dwvxq4sb 222 0.54 5.29 6.1586 Slower aukanfjd3d8fa 3 0.88 10.00 7.3496 Slower 44bq4r5z2xrsm 54 3.13 43.39 9.0946 Slower 0az7czjdw8z7j 110 0.62 0.02 17.5933 Faster f41agfq9qdhk6 3 0.24 8.06 22.6765 Slower http://kerryosborne.oracle-guy.com/2009/06/what-did-my-new-index-mess-up/

  36. Plan Instability SQL> -- find statements with multiple plans with big differences in elapsed time SQL> @unstable_plans Enter value for min_stddev: Enter value for min_etime: 1 SQL_ID SUM(EXECS) MIN_ETIME MAX_ETIME NORM_STDDEV ------------- ---------- ----------- ----------- ------------- f2pz10qx59awc 2 11.75 45.31 2.0197 5mh2127hkzbtr 70 2.64 18.06 3.0272 0hur96bxr18jn 24 1.65 9.14 3.1981 76gduk3urk91d 6 9.75 57.82 3.4880 cqxkwk9hp8rpf 31 7.18 43.77 3.6015 3u2bxt4y0740a 17 0.49 4.19 4.1316 af6j2dyzawp7w 78 6.83 60.31 4.4492 2mzzy3u2rtgqx 93 4.55 34.13 4.6025 http://kerryosborne.oracle-guy.com/2009/06/oracle-11g-adaptive-cursor-sharing-acs/ http://kerryosborne.oracle-guy.com/2008/10/unstable-plans/

  37. Bind Variable Peeking Solutions: • ONLY create histograms on skewed columns • USE LITERALS on histogram columns • (can use bind vars on "normal" values and litters for irregular) • _OPTIM_PEEK_USER_BINDS = false. • Outlines/Profiles/Baselienes/Hints • 11g Adaptive Cursor Sharing • JDBC 9i doesn’t bind variable peek http://kerryosborne.oracle-guy.com/2009/03/bind-variable-peeking-drives-me-nuts/

  38. ACS - adaptive cursor sharing IBA – is bind aware IBS – is bind sensitive ISH – is shareable • Limited to 14 variables SQL_ID C# PLAN_HASH IBS IBA ISH XECS ROWS_ AVGTM SQL_TEXT ------------- -- ---------- --- --- --- ---- ------ ----- --------------- 0qvgb3dyfg539 1 722236007 Y Y Y 2 0 .15 SELECT row_orde 17uuqnvxmzxhj 1 3038781757 Y Y Y 31 31 .17 SELECT COUNT(*) 3 3038781757 Y Y N 21 21 .02 4 3038781757 Y Y Y 52 52 .23 6 3038781757 Y Y Y 51 51 .00 34x6683rpwtxa 4 722236007 Y Y Y 18 164 .01 SELECT row_orde 3tfx8fzp64vkb 1 3038781757 Y Y Y 2 2 .01 SELECT COUNT(*) 4vb86f36xqc50 1 2983410489 Y Y Y 62 1683 .12 SELECT row_orde 4 2983410489 Y Y Y 7 163 .69 58p0j1q6rmv34 1 1144901783 Y Y Y 2 2 .02 SELECT COUNT(*) 5mxqphz5qfs4d 1 1144901783 Y Y Y 2 2 .02 SELECT COUNT(*) dt1v1cmua9cnq 1 4076066623 Y Y Y 8 37 3.47 ftxa99d89yzz0 1 4289789142 Y Y Y 2 2 .01 SELECT COUNT(*) g375mcpc30dy5 2 1690109023 Y Y N 1 10 .03 SELECT row_orde 3 1690109023 Y Y Y 5 24 .02 http://kerryosborne.oracle-guy.com/2009/06/oracle-11g-adaptive-cursor-sharing-acs/

  39. More ACS • OptimizerMagic Blog - Good basic description of ACSOptimizerMagic Blog - Update on ACSRic Van Dyke - Intelligent Cusor Sharing in 11.1.0.6Ric Van Dyke - Intelligent Cusor Sharing in 11.1.0.7Section on ACS in Troubleshooting Oracle Performance By Christian Antognini

  40. Same Plan Hash, different Plan http://oracle-randolf.blogspot.com/2009/07/planhashvalue-how-equal-and-stable-are_26.html

More Related