Re: Index Rebuilds

exactly that's what makes me go nuts. tkprof snippet was taken after
coalesce. I get the preferred execution plan but the query does 65484
disk reads to run that query.

Asif,

I did gather stats after coalesce.

Will you be using the output number of rows for further processing ?
or is it only to see the existences of type = 'live' ?


Both. I imported the table from production to test and ran the same
query. It took only couple of millisecs with index_ffs scan.

On 7/30/06, Wolfgang Breitling <breitliw@xxxxxxxxxxxxx> wrote: 
If the number of leaf block is 11,000 then how can you get 65484 disk
reads for a fast full scan? Or is that tkprof snippet from before the
coalesce when there were LBLKS: 66653 ?

At 05:08 PM 7/28/2006, Stalin wrote:

>LBLKS: 66653
>BlVL: 3
>CF: 679426
>NROWS: 829734
>AVG_DATA_BLK/KEY: 2
>
>Coalescing the index got the lblks to 11k and the desired plan
>however, the performance is still under water. 10046 trace on the sql
>is
>
>select count(*)
>from
>objects where type = 'live'
>
>
>call     count       cpu    elapsed       disk      query    current
>        rows
>------- ------  -------- ---------- ---------- ----------
>----------  ----------
>Parse        1      0.01       0.00          0          0          0
>           0
>Execute      1      0.00       0.00          0          0          0
>           0
>Fetch        2     10.39      31.07      65484      69693          0
>           1
>------- ------  -------- ---------- ---------- ----------
>----------  ----------
>total        4     10.40      31.08      65484      69693          0
>           1

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com



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


Other related posts: