Hints outlines profiles baselines
Download
1 / 40

Hints Outlines/Profiles/Baselines - PowerPoint PPT Presentation


  • 143 Views
  • Uploaded on

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Hints Outlines/Profiles/Baselines' - kimn


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Hints outlines profiles baselines

HintsOutlines/Profiles/Baselines

Kyle Hailey

http://oraclemonitor.com

SQL Plan Stability


When to tune
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
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
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
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
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 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, 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
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
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
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 baselines1
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 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
Adaptive Cursor Sharing

  • Different plans for different bind variables

    • Only for statements that have 14 or less variables (?!)


Adaptive cursor sharing1
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 sharing2
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/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
Hints - subqueries

  • MERGE / NO_MERGE

  • UNNEST / NO_UNNEST

  • PUSH_SUBQ / NO_PUSH_SUBQ

  • NO_QUERY_TRANSFORMATION

    • Don’t do any of the above


Unnest
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/


Unnest1
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
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
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
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
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
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
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
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
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
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
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
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
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
Same Plan Hash, different Plan

http://oracle-randolf.blogspot.com/2009/07/planhashvalue-how-equal-and-stable-are_26.html


ad