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.
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
-- //www.freelists.org/webpage/oracle-l