KEEP pool and disk reads

Say, I do keep a table, and its indexes, in the KEEP pool. x$bh confirms
that all the blocks are indeed there. Now, I run a fast full index scan. 0
disk reads for 62316 rows processed. Then I run the FFI scan again, with an
order by. Autotrace shows 628 disk reads .  I increased the sort_area_size
to a large value, and autotrace moved the 1 sorts(disk) to sorts(memory).

Now, how do I verify where the disk reads are coming from? If its a sort,
how can I verify this? Any traces or events that I could set/enable?  I
tried to check the stats in v$filestat but those never changed (as
expected), before and after the scan with the order by.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=978 Card=62316
Bytes=4486752)
   1    0   SORT (ORDER BY) (Cost=978 Card=62316 Bytes=4486752)
   2    1     INDEX (FAST FULL SCAN) OF 'PK_CFG_APP_OPTION' (UNIQUE)
(Cost=205 Card=62316 Bytes=4486752)

Statistics
----------------------------------------------------------
          0  recursive calls
          2  db block gets
        924  consistent gets
        628  physical reads
          0  redo size
    5263697  bytes sent via SQL*Net to client
      46345  bytes received via SQL*Net from client
       4156  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      62316  rows processed

Thanks
Raj


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: