9.2.0.5 seems does not have the same statistics as 9.2.0.3?

From: chao_ping <zhuchao_at_gmail.com>
Date: 12 Jul 2005 07:34:02 -0700
Message-ID: <1121178842.815727.153660@g44g2000cwa.googlegroups.com>

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 Received on Tue Jul 12 2005 - 09:34:02 CDT