KEEP pool and disk reads
- From: Rajesh.Rao@xxxxxxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sat, 26 Jun 2004 00:00:03 -0400
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 //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
