KEEP pool and disk reads

From: <Rajesh.Rao_at_jpmchase.com>
Date: Sat, 26 Jun 2004 00:00:03 -0400
Message-ID: <OF22B5884D.A5E6C98B-ON85256EBF.0013B1B6-85256EBF.0015FA69@chase.com>

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_at_freelists.org 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


-----------------------------------------------------------------

Received on Fri Jun 25 2004 - 22:57:01 CDT