RE: 54 is less than 4 for CBO ?

Hi

>INHO, Oracle makes wrong assumption with all_rows hint
>as default. Most queries are never interested in all
>rows. I executed the bellow query on my production system:
>
>select round(100*count((case when fetches/executions<2 then 1
>else null end ))/count(1)) pct_first_1_rows from v$sqlarea
>where command_type=3 and executions>0

Be careful here.

1. Some queries don't retrieve rows.
2. Many applications do prefetching, i.e. a single fetch may retrieve dozen of rows!

To do such computations the column ROWS_PROCESSED it's better...  

HTH
Chris

--
http://www.freelists.org/webpage/oracle-l