Re: 900 distinct values, yet density yields 0.5??

Since you have no histogram I would certainly expect to see DENS = 1/NDV.

Is this a repeatable phenomenon, or just a one-off oddity ? Is it possible that someone has been playing with dbms_stats.set_columns_stats ?

If the problem is persistent after running the gather on that table, you might run the gather again with event 10046 set at level 4 to check the bind variables going in and out of the database calls. Possibly something odd is happening where the auto_sample_size starts with a small value that updates the density, then uses a larger size that fails to update the densitry. (Very unlikely, but the result you are seeing is very strange).

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html


<hansdegit_at_hotmail.com> wrote in message 
news:1141381401.641952.33470_at_z34g2000cwc.googlegroups.com...


> Hi,

>

> Ora 9.2.06 AIX

>

> In a Peoplesoft environment a ran a dbms_stats.gather_table_stats on

> the PS_JRNL_LN table. In that table, there is a column called

> process_instance, which holds some kind of batch number.

>

> Now, when I gather stats, Oracle sees 900 distinct values for that

> column. But, and now comes the part I don't understand, when I look in

> DBA_TAB_COLUMNS the column density does not equal 1/900, but .5. So

> according to Oracle, a full table scan would be a viable plan to

> perform an update using only process_instance in the predicate.

>

> I did a 10053 trace and found that Oracle uses the density, rather than

> NDV...

>

> <snip>

> SINGLE TABLE ACCESS PATH

> Column: PROCESS_IN  Col#: 42     Table: PS_JRNL_LN   Alias: PS_JRNL_LN

>    NDV: 900       NULLS: 0         DENS: 5.0000e-01 LO:  0  HI:

> 9000012588

>    NO HISTOGRAM: #BKT: 1 #VAL: 2

>  TABLE: PS_JRNL_LN     ORIG CDN: 4065419  ROUNDED CDN: 2032710  CMPTD

> CDN: 2032710

>  Access path: tsc  Resc:  17020  Resp:  16972

>  Skip scan: ss-sel 0  andv 1539

>    ss cost 9234

>    index io scan cost 0

>  Access path: index (scan)

>      Index: PSDJRNL_LN

>  TABLE: PS_JRNL_LN

>      RSC_CPU: 4644964186   RSC_IO: 322723

>  IX_SEL:  5.0000e-01  TB_SEL:  5.0000e-01

>  BEST_CST: 17021.00  PATH: 2  Degree:  1

>

> </snip>

>

> I ran this command to gather stats:

> begin

>  dbms_stats.gather_table_stats(

>      ownname =>  'SYSADM'

>           , tabname => 'PS_JRNL_LN'

>      , estimate_percent => dbms_stats.auto_sample_size

>      , block_sample => null

>      , method_opt => 'for all columns size 1'

>      , degree => dbms_stats.default_degree

>      , granularity => 'default'

>      , cascade  => true

> );

> end;

>

> Am I hitting a very serious bug, or is this 'expected behaviour'?

>

> Please help.

>

> TIA,

> Hans

>