400 likes | 609 Views
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.
E N D
HintsOutlines/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 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
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
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)
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
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
INDEX HINT • INDEX(Table_alias INDEX_NAME) • 10g: • INDEX(table_alias (col1 col2 …)
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
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
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
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/
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/
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');
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/
Adaptive Cursor Sharing • Different plans for different bind variables • Only for statements that have 14 or less variables (?!)
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
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
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
Hints - subqueries • MERGE / NO_MERGE • UNNEST / NO_UNNEST • PUSH_SUBQ / NO_PUSH_SUBQ • NO_QUERY_TRANSFORMATION • Don’t do any of the above
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/
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);
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).
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)
PUSH_SUBQ • Evaluate subqueries as soon as possible • When unnest can’t be done, then subqueries are normally evaluated at the end
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
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)
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
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/
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/
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/
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/
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/
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
Same Plan Hash, different Plan http://oracle-randolf.blogspot.com/2009/07/planhashvalue-how-equal-and-stable-are_26.html