Re: 9.2.0.5 seems does not have the same statistics as 9.2.0.3?

"chao_ping" <zhuchao_at_gmail.com> wrote in message news:1121178842.815727.153660_at_g44g2000cwa.googlegroups.com...
>
> Rows Row Source Operation
> ------- ---------------------------------------------------
> 1 SORT AGGREGATE
> 1 BITMAP CONVERSION COUNT
> 1 BITMAP AND
> 1 BITMAP CONVERSION FROM ROWIDS
> 6901 SORT ORDER BY
> 6901 INDEX RANGE SCAN OBJ#(14132) (object id 14132)
> 16 BITMAP CONVERSION FROM ROWIDS
> 1543070 INDEX RANGE SCAN OBJ#(14140) (object id 14140)
>
> 9.2.0.3:
> Rows Row Source Operation
> ------- ---------------------------------------------------
> 1 SORT AGGREGATE (cr=11088 r=0 w=0 time=5093329 us)
> 2 BITMAP CONVERSION COUNT (cr=11088 r=0 w=0 time=5093309 us)
> 2 BITMAP AND (cr=11088 r=0 w=0 time=5092618 us)
> 2 BITMAP CONVERSION FROM ROWIDS (cr=782 r=0 w=0 time=123631
> us)
> 27581 SORT ORDER BY (cr=782 r=0 w=0 time=111182 us)
> 27581 INDEX RANGE SCAN KC_MESSAGE_COMBO_I (cr=782 r=0 w=0
> time=39206 us)(object id 6468)
> 40 BITMAP CONVERSION FROM ROWIDS (cr=10306 r=0 w=0
> time=4947923 us)
> 4980475 INDEX RANGE SCAN KC_MESSAGE_TYPE_I (cr=10306 r=0 w=0
> time=3217864 us)(object id 6469)
>
>
> Same timed_statitics and statistics_level=typical;
>
> Anyone noticed this? Is it by design? (Someone report enable 10046 in
> 9201-9204 slow down oracle pretty much, so oracle reduced the
> information detail?)
>
> Thanks
>

I believe it is by design.
If you want to get the extra statistics, the official option is probably to do:

    alter session set statistics_level = all;

the unofficial option is:

    alter session set "_rowsource_execution_statistics"=true;

The overhead is quite expensive if you have nested loop operations with a large number of iterations.

The basic cost in all cases is that you generate a second child cursor for each statement, that includes the hidden rowsource operations that collect the statistics.

-- 
Regards

Jonathan Lewis

Now waiting on the publishers:    Cost Based Oracle - Volume 1

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

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 8th July 2005