SEO

trace file from 10046 level 8:
9.2.0.5:
select /*+index_combine(a)*/ count(*)
from
kana_admin.kc_message a where state<:"SYS_B_0" and type=:"SYS_B_1"


call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.00 0 0 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch 2 0.92 0.89 0 3212 0
1
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 4 0.92 0.89 0 3212 0
1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 5

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

"chao_ping" <zhuchao@gmail.com> wrote in message
news:1121178842.815727.153660@g44g2000cwa.googlegr oups.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

You are right! Thanks very much.
One more question, I checked _rowsource_execution_statistics in both
9203/9205, they are both false. and statistics_level = typical.


So by accident oracle seems enabled rowsource_execution_statistics and
disabled it again in 9205. Is there a known bug for this?

Thanks

"chao_ping" <zhuchao@gmail.com> wrote in message
news:1121226042.040470.16670@g47g2000cwa.googlegro ups.com...
> You are right! Thanks very much.
> One more question, I checked _rowsource_execution_statistics in both
> 9203/9205, they are both false. and statistics_level = typical.
>
>
> So by accident oracle seems enabled rowsource_execution_statistics and
> disabled it again in 9205. Is there a known bug for this?
>
> Thanks
>


The "bug" is that 9.2.0.3 (and various other versions) enables
_rowsource_execution_statistics when you enable sql_trace.
I believe this was intentional. But the overhead did cause
people to complain, which is why I assume this was stopped
in 9.2.0.5


--
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