Consistant gets and the cost..

[alison]

As requested slimdave

SYSTEM-ORCL->alter session set optimizer_mode = all_rows;
Session altered.

1 select * from customers c, sales s
2 where c.cust_id = s.cust_id
3 and cust_last_name = 'Smith'
4* and s.time_id <= '31-DEC-98'
SYSTEM-ORCL->/

359 rows selected.

Elapsed: 00:00:24.01

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7707 Card=17377 Bytes=2641304)
1 0 MERGE JOIN (Cost=7707 Card=17377 Bytes=2641304)
2 1 SORT (JOIN) (Cost=86 Card=78 Bytes=9594)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMERS' (Cost=82 Card=78 Bytes=9594)
4 3 INDEX (RANGE SCAN) OF 'CUST_CUST_LAST_NAME_IDX' (NON-UNIQUE) (Cost=3 Card=78)
5 1 SORT (JOIN) (Cost=7621 Card=348985 Bytes=10120565)
6 5 PARTITION RANGE (ITERATOR)
7 6 TABLE ACCESS (FULL) OF 'SALES' (Cost=1501 Card=348985 Bytes=10120565)




Statistics
----------------------------------------------------------
0 recursive calls
46 db block gets
2841 consistent gets
13299 physical reads
60 redo size
20110 bytes sent via SQL*Net to client
756 bytes received via SQL*Net from client
25 SQL*Net roundtrips to/from client
1 sorts (memory)
1 sorts (disk)
359 rows processed


As you can see a way way higher cost, also the consistant gets has fallen but the physical reads are way up high...

A very different execution plan and I would expect this one to be more expensive, it's doing sort in both memory and disk and it's also using redo due to the join condition...

[abhaysk]

First_Rows_N the factor N is what i was talking about..

and as far as ur cost comparison is concerned (22 vs 61)

its jus a number..in respect to the context its in..

so doesn't really make any sense..by comparing costs..

Abhay.

[slimdave]

Another question -- do you have recently-gathered statistics on these tables? In particular, do you gather column statistics for s.time_id and cust_last_name?

[abhaysk]

Originally posted by slimdave
In particular, do you gather column statistics for s.time_id and cust_last_name?

Dave :

Some times Col Stats sucks the exec plan to the core (IMHO) especially when CARD of the column is >> 254...

So thumb rule will be to take Col stats only when its Card < 254..


Abhay.

[slimdave]

Originally posted by abhaysk
Dave :

Some times Col Stats sucks the exec plan to the core (IMHO) especially when CARD of the column is >> 254...

So thumb rule will be to take Col stats only when its Card < 254..


Abhay.

Documented anywhere?

[abhaysk]

Originally posted by slimdave
Documented anywhere?

U want me to show a test case when all it sucks?

Abhay.

[slimdave]

Originally posted by abhaysk
U want me to show a test case when all it sucks?

Abhay.

If it sucks with your data, on your version, on your o/s, then raise a tar. Unless it's a documented problem (through fornmal docs, metalink, or authoratative consensus) then don't extrapolate to everyone's situation.

Question still stands.

[jmodic]

Originally posted by abhaysk
So thumb rule will be to take Col stats only when its Card < 254..

You can't be serious about that....

P.S.
Here we might be witnesses of a historical moment when yet another Oracle mith has been giving birth.

[abhaysk]

Originally posted by slimdave
Unless it's a documented problem (through fornmal docs, metalink, or authoratative consensus) then don't extrapolate to everyone's situation.

Question still stands.

Okk, if u really want some docs to support my observations, then jus see this link Limitations

Or jus search for the doc id = 212809.1 ( metalink )

Originally posted by jmodic
You can't be serious about that....

P.S.
Here we might be witnesses of a historical moment when yet another Oracle mith has been giving birth.

Oh, indeed you can be serious..especially if u look for the quote

Histograms are limited to 250 buckets so if there are more than 250 distinct values and there is no single value that dominates the column's dataset then histograms may not provide helpful statistics. With large numbers of distinct values, histogram usage can be further compromised because differences in non popular values cannot be recorded. The choice of 250 buckets per column histogram was a balance between accuracy of the statistics and the speed of histogram collection/amount of space required to store the information.

in the above link, when Oracle Admits it why not we?

Rgds
Abhay.

[jmodic]

But this doesn't change anything about what I thing of this rule of thumb of yours - like most rule of thumbs it is dumb.

Originally posted by abhaysk
So thumb rule will be to take Col stats only when its Card < 254..

I might have 1000s of distinct values in my table, but with highly skewed distribution (where for example one or two values represent the majority of all the rows, while others values are uniformly distributed) the 250 buckets could perfectly reflect the distribution. I might even not need 250 buckets for that, maybe even 20 or 50 might be enough...