Breitling - Histograms, Myths And Facts Oracle

Histograms -

Myths and Facts Wolfgang Breitling www.centrexcc.com

Who am I Independent consultant since 1996 specializing in Oracle and Peoplesoft setup, administration, and performance tuning Member of the Oaktable Network 25+ years in database management DL/1, IMS, ADABAS, SQL/DS, DB2, Oracle

OCP certified DBA - 7, 8, 8i, 9i Oracle since 1993 (7.0.12) Mathematics major at University of Stuttgart 2

© Wolfgang Breitling, Centrex Consulting Corporation

Hotsos Symposium, March 6-9, 2005

Agenda ™ Look at two (still) rather widespread misconceptions about histograms ™ Explore the relationship between the number of buckets and the column statistics, particularly for height-balanced histograms ™ Explore the effect of some “auto-options” in statistics gathering ™ Dispense Guidelines for Statistics Gathering 3

© Wolfgang Breitling, Centrex Consulting Corporation

Hotsos Symposium, March 6-9, 2005

Myth or Fact ?

Histograms allow the CBO to make a better choice between index access and table scan and are therefore only useful for indexed columns 4

© Wolfgang Breitling, Centrex Consulting Corporation

Hotsos Symposium, March 6-9, 2005

Histogram on non-indexed Column create table t1 (pk1 number , pk2 number , fk1 number , fk2 number , d1 date , d2 number , d3 number , d4 varchar2(2000) );

select sum(t1.d2*t2.d3*t3.d3) from t1, t2, t3 where t1.fk1 = t2.pk1 and t3.pk1 = t2.fk1 and t3.d2 = 35 and t1.d3 = 0;

create unique index t1p on t1(pk1, pk2); create index t1x on t1(d2);

5

© Wolfgang Breitling, Centrex Consulting Corporation

Hotsos Symposium, March 6-9, 2005

Histogram on non-indexed Column begin dbms_random.seed(67108863); for i in 1..250 loop for j in 1..250 loop insert into t1 values(i, j , mod(trunc(100000*dbms_random.value),10)+1 , mod(trunc(100000*dbms_random.value),10)+1 , trunc(sysdate)+trunc(100*dbms_random.value) , mod(trunc(100000*dbms_random.value),100)+1 , decode(mod(trunc(100000*dbms_random.value), 65535),0,0,1) , mod(trunc(100000*dbms_random.value),1000)+1 , dbms_random.string('A',trunc(abs(2000*dbms_random.value))) ); 6

© Wolfgang Breitling, Centrex Consulting Corporation

Hotsos Symposium, March 6-9, 2005

Without Histogram call

count

cpu

elapsed

disk

query

current

rows

Parse

1

0.01

0.00

0

0

0

0

Execute

1

0.00

0.00

0

0

0

0

Fetch

2

1.18

6.61

32526

34767

0

1

total

4

1.20

6.61

32526

34767

0

1

Row Source Operation

Rows

1 SORT AGGREGATE (cr=34767 r=32526 w=0 time=6615582 us) 479 2 61428 492 492 62500

7

HASH JOIN

(cr=34767 r=32526 w=0 time=6615065 us)

TABLE ACCESS FULL T1 (cr=23391 r=22253 w=0 time=3317822 us) HASH JOIN

(cr=11376 r=10273 w=0 time=3224089 us)

TABLE ACCESS BY INDEX ROWID T3 (cr=486 r=0 w=0 time=2985 us) INDEX RANGE SCAN T3X (cr=4 r=0 w=0 time=471 us)(object id 226985) TABLE ACCESS FULL T2 (cr=10890 r=10273 w=0 time=3105971 us)

© Wolfgang Breitling, Centrex Consulting Corporation

Hotsos Symposium, March 6-9, 2005

With Histogram call

count

cpu

elapsed

disk

query

current

rows

Parse

1

0.00

0.02

0

0

0

0

Execute

1

0.00

0.00

0

0

0

0

Fetch

2

0.71

3.31

22205

23971

0

1

total

4

0.71

3.34

22205

23971

0

1

Row Source Operation

Rows

1 SORT AGGREGATE (cr=23971 r=22205 w=0 time=3317664 us) 479 492 492 500 503 2 500

8

HASH JOIN

(cr=23971 r=22205 w=0 time=3317140 us)

TABLE ACCESS BY INDEX ROWID T3 (cr=486 r=0 w=0 time=2895 us) INDEX RANGE SCAN T3X (cr=4 r=0 w=0 time=476 us)(object id 226985) TABLE ACCESS BY INDEX ROWID T2 (cr=23485 r=22205 w=0 time=3310054 us) NESTED LOOPS

(cr=23399 r=22205 w=0 time=3307848 us)

TABLE ACCESS FULL T1 (cr=23391 r=22205 w=0 time=3306483 us) INDEX RANGE SCAN T2P (cr=8 r=0 w=0 time=680 us)(object id 226986)

© Wolfgang Breitling, Centrex Consulting Corporation

Hotsos Symposium, March 6-9, 2005

Histogram on non-indexed Column NAME LATCH.cache buffers chains LATCH.cache buffers lru chain LATCH.enqueue hash chains LATCH.enqueues LATCH.library cache LATCH.library cache pin LATCH.library cache pin allocation LATCH.multiblock read objects LATCH.shared pool STAT..CPU used by this session STAT..buffer is not pinned count STAT..buffer is pinned count STAT..calls to get snapshot scn: kcmgss STAT..consistent gets STAT..db block gets STAT..free buffer requested STAT..index scans kdiixs1 STAT..no work - consistent read gets STAT..physical reads STAT..prefetched blocks STAT..session logical reads STAT..table fetch by rowid STAT..table scan blocks gotten STAT..table scan rows gotten STAT..table scans (long tables) 9

1 102258 32533 20 21 248 90 70 32494 170 134 33099 501 1672 34776 49 32536 1 33098 32526 16245 34825 494 32612 125000 2

© Wolfgang Breitling, Centrex Consulting Corporation

2 102486 32607 39 41 189 75 74 32494 134 107 33095 501 1671 34774 31 32531 1 33096 32526 16245 34805 492 32612 125000 2

3 102166 32467 16 16 286 95 70 32428 179 133 33095 501 1671 34774 33 32467 1 33096 32461 16212 34807 492 32612 125000 2

4 70308 22210 8 8 73 33 18 22182 67 73 22824 1414 1140 23978 41 22216 3 22826 22205 11091 24019 992 22253 62500 1

5 70305 22223 6 6 71 29 18 22192 58 95 22824 1414 1140 23978 33 22222 3 22826 22216 11096 24011 992 22253 62500 1

6 70329 22233 8 8 69 29 18 22202 62 78 22824 1414 1140 23978 31 22231 3 22826 22226 11101 24009 992 22253 62500 1

Hotsos Symposium, March 6-9, 2005

Without Histogram Rows

Card Plan 1

10

1

1

479

7,704,878

2 61,428

31,250 61,639

492

492

492

492

62,500

62,500

SELECT STATEMENT (choose)(Cost 5532) SORT (aggregate) HASH JOIN (Cost 5532) TABLE ACCESS

T1 (full)(Cost 3379)

HASH JOIN (Cost 2061) TABLE ACCESS INDEX

T3 (by index rowid)(Cost 485)

NON-UNIQUE T3X (range scan)(Columns 1)(Cost 3)

TABLE ACCESS

T2 (full)(Cost 1574)

© Wolfgang Breitling, Centrex Consulting Corporation

Hotsos Symposium, March 6-9, 2005

With Histogram Rows

Card Plan 1

11

1

1

479

493

492

492

492

250

500

250

503

500

2

2

500

250

SELECT STATEMENT (choose)(Cost 3955) SORT (aggregate) HASH JOIN (Cost 3955) TABLE ACCESS INDEX

T3 (by index rowid)(Cost 485)

NON-UNIQUE T3X (range scan)(Columns 1)(Cost 3)

TABLE ACCESS

T2 (by index rowid)(Cost 45)

NESTED LOOPS (Cost 3469) TABLE ACCESS INDEX

T1 (full)(Cost 3379)

UNIQUE T2P (range scan)(Columns 1)(Cost 3)

© Wolfgang Breitling, Centrex Consulting Corporation

Hotsos Symposium, March 6-9, 2005

Myth or Fact ?

If I have the time window, there is no harm in collecting histograms on all columns. (so that I don’t miss any that I really need)

12

© Wolfgang Breitling, Centrex Consulting Corporation

Hotsos Symposium, March 6-9, 2005

Histograms on all Indexed Columns? SELECT B1.SETID, B3.EMPLID , B3.EMPL_RCD, B3.EFFSEQ, B3.EFFDT b3_effdt, B3.CURRENCY_CD, ... From PS_GRP_DTL B1 , PS_JOB B3 , PS_JOBCODE_TBL B4 WHERE B1.SETID = rtrim(:b1,' ') AND B3.EMPLID = rtrim(:b2, ' ') AND B1.SETID = B4.SETID AND B1.BUSINESS_UNIT IN (B3.BUSINESS_UNIT, ' ') AND B3.BUSINESS_UNIT IN ('BU001', 'BU007', 'BU017', 'BU018', 'BU502', 'BU101', ' ') AND B1.DEPTID IN (B3.DEPTID, ' ') AND B1.JOB_FAMILY IN (B4.JOB_FAMILY, ' ') AND B1.LOCATION IN (B3.LOCATION, ' ') AND B3.JOBCODE = B4.JOBCODE AND B4.EFF_STATUS = 'A' AND B1.EFFDT = (SELECT MAX(A1.EFFDT) FROM PS_GRP_DTL A1 WHERE B1.SETID = A1.SETID AND B1.JOB_FAMILY = A1.JOB_FAMILY AND B1.LOCATION = A1.LOCATION AND B1.DEPTID = A1.DEPTID AND A1.EFFDT <= to_date(:b3, 'yyyy-mm-dd')) AND B3.EFFDT = (SELECT MAX(A2.EFFDT) FROM PS_JOB A2 WHERE B3.EMPLID = A2.EMPLID AND B3.EMPL_RCD = A2.EMPL_RCD AND A2.EFFDT <= to_date(:b3, 'yyyy-mm-dd')) AND B3.EFFSEQ = (SELECT MAX(A3.EFFSEQ) FROM PS_JOB A3 WHERE B3.EMPLID = A3.EMPLID AND B3.EMPL_RCD = A3.EMPL_RCD AND B3.EFFDT = A3.EFFDT) AND B4.EFFDT = (SELECT MAX(A4.EFFDT) FROM PS_JOBCODE_TBL A4 WHERE B4.JOBCODE = A4.JOBCODE AND A4.EFFDT <= to_date(:b3, 'yyyy-mm-dd')) ORDER BY B1.SETID desc, B3.EMPLID desc, B1.BUSINESS_UNIT desc , B1.DEPTID desc, B1.JOB_FAMILY desc, B1.LOCATION desc

13

© Wolfgang Breitling, Centrex Consulting Corporation

Hotsos Symposium, March 6-9, 2005

Histograms on all Indexed Columns? without histograms call Parse Execute Fetch total

count 1 1 2 4

cpu 0.01 0.00 0.01 0.02

elapsed 0.00 0.00 0.00 0.01

disk 0 0 0 0

query 0 0 68 68

current 0 0 0 0

rows 0 0 2 2

query 0 0 770842 770842

current 0 0 0 0

rows 0 0 2 2

with histograms on all indexed columns call Parse Execute Fetch total

14

count 1 1 2 4

cpu 0.01 0.00 80.11 80.12

elapsed 0.00 0.00 78.23 78.24

disk 0 0 0 0

© Wolfgang Breitling, Centrex Consulting Corporation

Hotsos Symposium, March 6-9, 2005

Without Histograms Rows 2 2 4 1 1 1 1 1 1 1 1 1 1 1 1 1 4 2 4

15

Row Source Operation SORT ORDER BY FILTER NESTED LOOPS NESTED LOOPS TABLE ACCESS BY INDEX ROWID PS_JOB INDEX RANGE SCAN PSAJOB SORT AGGREGATE FIRST ROW INDEX RANGE SCAN (MIN/MAX) PSAJOB SORT AGGREGATE FIRST ROW INDEX RANGE SCAN (MIN/MAX) PSAJOB TABLE ACCESS BY INDEX ROWID PS_JOBCODE_TBL INDEX RANGE SCAN PS_JOBCODE_TBL SORT AGGREGATE INDEX FAST FULL SCAN PS1JOBCODE_TBL TABLE ACCESS FULL PS_GRP_DTL SORT AGGREGATE INDEX RANGE SCAN PS_GRP_DTL

© Wolfgang Breitling, Centrex Consulting Corporation

Hotsos Symposium, March 6-9, 2005

With Histograms Rows 2 2 49225 49033 237 1313 1321 3635 49033 136275 156161 194112 191 1 1 1 1 1 1

16

Row Source Operation SORT ORDER BY TABLE ACCESS BY INDEX ROWID PS_JOB NESTED LOOPS NESTED LOOPS TABLE ACCESS BY INDEX ROWID PS_JOBCODE_TBL INDEX RANGE SCAN PS_JOBCODE_TBL SORT AGGREGATE INDEX FAST FULL SCAN PS1JOBCODE_TBL TABLE ACCESS BY INDEX ROWID PS_GRP_DTL INDEX RANGE SCAN PS_GRP_DTL SORT AGGREGATE INDEX RANGE SCAN PS_GRP_DTL INDEX RANGE SCAN PS1JOB SORT AGGREGATE FIRST ROW INDEX RANGE SCAN (MIN/MAX) PSAJOB SORT AGGREGATE FIRST ROW INDEX RANGE SCAN (MIN/MAX) PSAJOB

© Wolfgang Breitling, Centrex Consulting Corporation

Hotsos Symposium, March 6-9, 2005

Misplaced Histogram ¾ If that scenario is correct, i.e. histogram on xxx, then I’d test what happens if you get rid of the histogram. How many distinct values of xxx are in the table? ½ thx; good idea; checkin into it... ½ I dropped the histogram ...did help thx a lot ....(hope the rest will still work ..) 17

© Wolfgang Breitling, Centrex Consulting Corporation

Hotsos Symposium, March 6-9, 2005

Myth or Fact ?

When collecting a histogram, the greater the number of buckets the higher the accuracy

18

© Wolfgang Breitling, Centrex Consulting Corporation

Hotsos Symposium, March 6-9, 2005

Histograms and Density without histogram density = 1/num_distinct with height balanced histogram density = Σ cnt*2 / ( num_rows * Σ cnt ) with frequency histogram density = 1/(2*num_rows) 19

© Wolfgang Breitling, Centrex Consulting Corporation

Hotsos Symposium, March 6-9, 2005

Height Balanced Histogram select min(minbkt), maxbkt , min(val) minval, max(val) maxval , sum(rep) sumrep, sumrep sum(repsq) sumrepsq , max(rep) maxrep, count(*) bktndv from ( select val , min(bkt) minbkt , max(bkt) maxbkt , count(val) rep , count(val)*count(val) repsq from ( select val , ntile(<size>) over (order by ) bkt from . [partition (<partition>)] t where is not null ) group by val ) group by maxbkt order by maxbkt

20

© Wolfgang Breitling, Centrex Consulting Corporation

Hotsos Symposium, March 6-9, 2005

Height Balanced Histogram Buckets = 2 val 1 2 3 4 5 6 7 8 9 10 11 12

21

minbkt 1 1 1 1 1 1 1 2 2 2 2 2

maxbkt 1 1 1 1 1 1 2 2 2 2 2 2

rep 7 7 20 7 7 7 7 7 30 7 7 7

repsq 49 49 400 49 49 49 49 49 900 49 49 49

120

1790

© Wolfgang Breitling, Centrex Consulting Corporation

density = 1790 / 120 / 120 = 1.2431e-1

Hotsos Symposium, March 6-9, 2005

Height Balanced Histogram Buckets = 5 val 1 2 3 4 5 6 7 8 9 10 11 12

22

minbkt 1 1 1 2 2 3 3 3 3 5 5 5

maxbkt 1 1 2 2 2 3 3 3 5 5 5 5

rep 7 7 20 7 7 7 7 7 30 7 7 7

repsq 49 49 400 49 49 49 49 49 900 49 49 49

90

890

© Wolfgang Breitling, Centrex Consulting Corporation

density = 890 / 90 / 120 = 8.2407e-2

Hotsos Symposium, March 6-9, 2005

Height Balanced Histogram Buckets = 6 val 1 2 3 4 5 6 7 8 9 10 11 12

23

minbkt 1 1 1 2 3 3 3 4 4 5 6 6

maxbkt 1 1 2 3 3 3 4 4 5 6 6 6

rep 7 7 20 7 7 7 7 7 30 7 7 7

repsq 49 49 400 49 49 49 49 49 900 49 49 49

120

1790

© Wolfgang Breitling, Centrex Consulting Corporation

density = 1790 / 120 / 120 = 1.2431e-1

Hotsos Symposium, March 6-9, 2005

Height Balanced Histogram Buckets = 8 val 1 2 3 4 5 6 7 8 9 10 11 12

24

minbkt 1 1 1 3 3 4 4 5 5 7 8 8

maxbkt 1 1 3 3 4 4 5 5 7 8 8 8

rep 7 7 20 7 7 7 7 7 30 7 7 7

repsq 49 49 400 49 49 49 49 49 900 49 49 49

70

490

© Wolfgang Breitling, Centrex Consulting Corporation

density = 490 / 70 / 120 = 5.8333e-2

Hotsos Symposium, March 6-9, 2005

Height Balanced Histogram Buckets = 12 val 1 2 3 4 5 6 7 8 9 10 11 12

25

minbkt 1 1 2 4 5 5 6 7 7 10 11 12

maxbkt 1 2 4 5 5 6 7 7 10 11 12 12

rep 7 7 20 7 7 7 7 7 30 7 7 7

repsq 49 49 400 49 49 49 49 49 900 49 49 49

70

490

© Wolfgang Breitling, Centrex Consulting Corporation

density = 490 / 70 / 120 = 5.8333e-2

Hotsos Symposium, March 6-9, 2005

Height Balanced Histogram Buckets = 17 val 1 2 3 4 5 6 7 8 9 10 11 12

26

minbkt 1 1 2 5 6 7 8 9 10 15 16 17

maxbkt 1 2 5 6 7 8 9 10 14 15 16 17

rep 7 7 20 7 7 7 7 7 30 7 7 7

repsq 49 49 400 49 49 49 49 49 900 49 49 49

70

490

© Wolfgang Breitling, Centrex Consulting Corporation

density = 1 / (2 *120) = 4.1667e-3

Hotsos Symposium, March 6-9, 2005

Histograms “A histogram approximates the distribution of data values in attributes of the database relations by grouping the data values into a group of buckets. This grouping into buckets loses information. It is important to choose bucket placement wisely, to minimize this loss of information for any chosen level of data summarization. Poor quality histograms might lead the optimizers to choose suboptimal query execution plans that may degrade the system performance dramatically.” dramatically Jagadish, H. V., Hui Jin, Beng Chin Ooi, and Kian-Lee Tan. "Global Optimization of Histograms.“ Paper presented at the ACM SIGMOD International Conference on Management of Data, Santa Barbara, California, United States 2001.

27

© Wolfgang Breitling, Centrex Consulting Corporation

Hotsos Symposium, March 6-9, 2005

Size SKEWONLY ? So how are you supposed to gather histograms and set # of buckets (size) SKEWONLY ? “I highly recommend that all Oracle DBA's use the method_opt=skewonly option to automatically identify skewed column values and generate histograms.”* * http://www.dba-oracle.com/oracle_tips_skewonly.htm

28

© Wolfgang Breitling, Centrex Consulting Corporation

Hotsos Symposium, March 6-9, 2005

Size SKEWONLY SKEWONLY - Oracle determines the columns to collect histograms based on the data distribution of the columns. Supplied PL-SQL Packages and Types Reference – dbms_stats.gather_table_stats

SIZE SKEWONLY when you collect histograms with the SIZE option set to SKEWONLY, we collect histogram data in memory for all specified columns (if you do not specify any, all columns are used). Once an "in-memory" histogram is computed for a column, it is stored inside the data dictionary only if it has "popular" values (multiple end-points with the same value which is what we define by "there is skew in the data"). http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:5792247321358#7417227783861

29

© Wolfgang Breitling, Centrex Consulting Corporation

Hotsos Symposium, March 6-9, 2005

Size SKEWONLY ? create table test (n1 number not null); insert into test (n1) select mod(rownum,5)+1 from dba_objects where rownum <= 100; exec DBMS_STATS.GATHER_TABLE_STATS (null, 'test' , method_opt => 'FOR ALL COLUMNS SIZE 1'); @colstats test table TEST

column N1

NDV 5

density 2.0000E-01

EP 0 1

value 1 5

nulls 0

lo 1

hi 5

@histogram test n1 table TEST TEST

30

column N1 N1

© Wolfgang Breitling, Centrex Consulting Corporation

Hotsos Symposium, March 6-9, 2005

Size SKEWONLY ?? exec DBMS_STATS.DELETE_TABLE_STATS (null, 'test‘); exec DBMS_STATS.GATHER_TABLE_STATS (null, 'test' , method_opt => 'FOR COLUMNS N1 SIZE SKEWONLY'); @colstats test table TEST

column N1

NDV 5

density 5.0000E-03

EP 20 40 60 80 100

value 1 2 3 4 5

nulls 0

lo 1

hi 5

@histogram test n1 table TEST TEST TEST TEST TEST

31

column N1 N1 N1 N1 N1

© Wolfgang Breitling, Centrex Consulting Corporation

Hotsos Symposium, March 6-9, 2005

Size SKEWONLY ? create table test (n1 number not null); insert into test (n1) select 1 from dba_objects where rownum <= 100;

exec DBMS_STATS.GATHER_TABLE_STATS (null, 'test' , method_opt => 'FOR ALL COLUMNS SIZE 1'); @colstats test table TEST

column N1

NDV 1

density 1.0000E+00

nulls 0

lo 1

hi 1

@histogram test n1 table TEST TEST

32

column N1 N1

EP 0 1

value 1 1

© Wolfgang Breitling, Centrex Consulting Corporation

Hotsos Symposium, March 6-9, 2005

Size SKEWONLY ??? exec DBMS_STATS.DELETE_TABLE_STATS (null, 'test‘);

exec DBMS_STATS.GATHER_TABLE_STATS (null, 'test' , method_opt => 'FOR COLUMNS N1 SIZE SKEWONLY'); @colstats test table TEST

column N1

NDV 1

density 5.0000E-03

EP 100

value 1

nulls 0

lo 1

hi 1

@histogram test n1 table TEST

33

column N1

© Wolfgang Breitling, Centrex Consulting Corporation

Hotsos Symposium, March 6-9, 2005

Size REPEAT ? create table test( n1 number not null, n2 number not null , n3 number not null, filler varchar2(4000)); exec dbms_random.seed(134217727); insert into test select 100+trunc(20*dbms_random.normal), 100+trunc(20*dbms_random.normal), decode(mod(trunc(10000*dbms_random.normal),16383),0,0,1), dbms_random.string('a',2000) from dba_objects where rownum <= 5000; insert into test select * from test; insert into test select * from test; insert into test select * from test;

34

© Wolfgang Breitling, Centrex Consulting Corporation

Hotsos Symposium, March 6-9, 2005

Size REPEAT ? dbms_stats.gather_table_stats (null, 'test' , estimate_percent => dbms_stats.auto_sample_size); table TEST

column N1 N2 N3 FILLER

NDV 63 21 2 5000

density 1.5873E-02 4.7619E-02 5.0000E-01 1.9976E-04

lo 66 90 0

hi 133 110 1

bkts 1 1 1 1

sample 5.5K

dbms_stats.gather_table_stats (null, 'test', estimate_percent => null , method_opt => 'for columns size skewonly n1,n2,n3'); table TEST

35

column N1 N2 N3 FILLER

NDV 64 21 2 5000

density 1.2500E-05 1.2500E-05 1.2500E-05 1.9976E-04

© Wolfgang Breitling, Centrex Consulting Corporation

lo 66 90 0

hi 133 110 1

bkts 63 20 1 1

sample 40.0K

5.5K

Hotsos Symposium, March 6-9, 2005

Size REPEAT ? dbms_stats.gather_table_stats (null, 'test' , estimate_percent => dbms_stats.auto_sample_size , method_opt => 'for all columns size repeat');

36

table TEST

column N1 N2 N3 FILLER

NDV 61 21 2 5000

table TEST TEST

column EP N3 8 N3 40000

density 1.2701E-05 1.2701E-05 1.2701E-05 2.0016E-04

value 0 1

© Wolfgang Breitling, Centrex Consulting Corporation

table TEST TEST

lo 66 90 0

hi 130 110 1

bkts 60 20 1 1

sample 5.0K

column N3 N3

EP 2 4973

value 0 1

Hotsos Symposium, March 6-9, 2005

Size REPEAT ?

37

© Wolfgang Breitling, Centrex Consulting Corporation

Hotsos Symposium, March 6-9, 2005

Statistics Gathering ™ Use individual statistic gathering commands for more control ™ Gather statistics on tables with a 5% sample ™ Gather statistics on indexes with compute ™ Add histograms where column data is known to be skewed Statistics Gathering: Frequency and Strategy Guidelines Metalink Note 44961.1

38

© Wolfgang Breitling, Centrex Consulting Corporation

Hotsos Symposium, March 6-9, 2005

Statistics Gathering “Column statistics in the form of histograms are only appropriate for columns whose distribution deviates from the expected uniform distribution.” “Given a production system with predictable, known queries, the ‘best’ execution plan for each statement is not likely to vary over time” “Given the ‘best’ plan is unlikely to change, frequent gathering statistics has no benefit.” “It is actually unusual for a plan to change wildly or for stats to be finely balanced. Unusual does NOT mean it never happens.” Statistics Gathering: Frequency and Strategy Guidelines Metalink Note 44961.1

39

© Wolfgang Breitling, Centrex Consulting Corporation

Hotsos Symposium, March 6-9, 2005

References http://www.ixora.com.au/newsletter/2001_04.htm 44961.1

Statistics Gathering: Frequency and Strategy Guidelines

72539.1

Interpreting Histogram Information

100229.1 Indexes - Selectivity 175258.1 How to Compute Statistics on Partitioned Tables and Indexes 1031826.6 Histograms: An Overview

40

© Wolfgang Breitling, Centrex Consulting Corporation

Hotsos Symposium, March 6-9, 2005

My favorite websites asktom.oracle.com www.evdbt.com www.ixora.com.au www.jlcomp.demon.co.uk www.hotsos.com www.miracleas.dk www.oracledba.co.uk www.oraperf.com www.orapub.com

41

© Wolfgang Breitling, Centrex Consulting Corporation

(Thomas Kyte) (Tim Gorman) (Steve Adams) (Jonathan Lewis) (Cary Millsap) (Mogens Nørgaard) (Connor McDonald) (Anjo Kolk) (Craig Shallahamer)

Hotsos Symposium, March 6-9, 2005

Wolfgang Breitling [email protected]

Centrex Consulting Corp. www.centrexcc.com

---

Related Documents

Breitling - Histograms, Myths And Facts Oracle

May 2020 8

Histograms - Myths And Facts

November 2019 40

Csom Myths And Facts

November 2019 37

Quantum Mechanics Myths And Facts

July 2020 18

Myths And Facts On Diet

November 2019 39

Facts And Myths About Gre

October 2019 50

Copyright © 2024 PDFCOKE.