Re: KEEP pool and disk reads

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 26 Jun 2004 10:41:00 +0100
Message-ID: <003f01c45b61$b0e15810$7102a8c0@Primary>

Events 10032 give you all the statistics about the sort operation. Event 10033 will list the block addresses of streams begin written to, and read from, the file in the temporary tablespace if you do a sort that overflows to disc.

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated May 1st

Original Message ----- From: <Rajesh.Rao_at_jpmchase.com> To: <oracle-l_at_freelists.org> Sent: Saturday, June 26, 2004 5:00 AM Subject: 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_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 Sat Jun 26 2004 - 04:37:38 CDT