Steve,
Query V$SESSION_EVENT for the SID of that session. If you see the
wait-event "db file scattered read" change during the query, then it truly
is doing I/O, like it or not. It is more likely that the CACHE attribute is
not working as you've apparently assumed it does, rather than there being a
bug...
---
The attribute CACHE is poorly named. It ought to properly have been called
TREAT_FULL_TABLE_SCANS_LIKE_INDEXED_SCANS____PERHAPS, but nobody asked...
...guess you wouldn't want to hear what I named my kids... :-)
Anyway, the CACHE option only instructs Oracle not to place database blocks
read during FULL table scan at the LRU (i.e. least-recently used) end of the
LRU chain (causing them to fall off the chain almost immediately), but
instead place them at the MRU (i.e. most-recently used) end of the LRU chain
(just as with blocks read via indexed scans). So, as you can imagine, this
is a long way from what I imagine you think it means: "pinning" the blocks
for the table into the Buffer Cache. Rather, it only improves the chances
for the table's blocks to remain in the Buffer Cache a little while longer.
There is rather sparse documentation on this feature, but what there is can
be found in the "SQL Reference" manual for CREATE/ALTER TABLE and in the
"Tuning" guide for the CACHE and NOCACHE hints.
>From Oracle8i onward, the operation of the "LRU Chain" in the Buffer Cache
has changed dramatically; Craig Shallahamer has an excellent paper on the
"touch-count" algorithm on his website at http://www.orapub.com. With these
changes to the venerable LRU algorithm, I wouldn't be surprised if the CACHE
attribute has become a complete "no-op", but I haven't tested if that has
happened or not yet. I don't really care, to be honest, because it's never
been a very useful feature...
---
If you are trying to achieve that "pinned" effect in the Buffer Cache, the
best you can do is enable the KEEP buffer pool and direct your table to that
pool using CREATE/ALTER TABLE. If you size the KEEP buffer pool at 10,000
blocks and direct fewer than 10,000 blocks into it, then you can be pretty
sure that you'll "cache" those objects, effectively "pinning" them into the
SGA. If you oversubscribe to the KEEP buffer pool (i.e. assign more objects
and blocks to the pool than buffers available), then you'll start aging
things out and I/O will occur. A query like "SELECT SUM(BLOCKS) FROM
DBA_TABLES WHERE BUFFER_POOL = 'KEEP'" would give you an idea, if you've
analyzed the tables recently...
Hope this helps....
-Tim
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
Sent: Monday, July 01, 2002 7:27 PM
> The degree is 4 and it is cached via alter table, not the KEEP buffer. On
> Oracle 8.1.7.2 on Linux RedHat 7.2
>
> Thanks for clues...
>
>
> -----Original Message-----
> Sent: Monday, July 01, 2002 3:08 PM
> To: Multiple recipients of list ORACLE-L
>
>
> What is the degree of parallelism on this table?
>
> Waleed
>
>
> -----Original Message-----
> Sent: Monday, July 01, 2002 3:33 PM
> To: Multiple recipients of list ORACLE-L
>
>
> What version of Oracle?
>
> > 1) I have a 400,000 row table table which is cached.
>
> How has the table been cached? Alter table XXX cache? Or with a KEEP
> buffer?
>
> -----Original Message-----
> Sent: Monday, July 01, 2002 4:39 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Here's the scene:
>
> 1) I have a 400,000 row table table which is cached.
>
> 2) I have a query against that table and no other with one column
referenced
> in the WHERE clause. (This column is indexed and of course I don't really
> need the index since the table is cached but it's there so ho hum...)
>
> 3) When I run tkprof on the query it shows a full table scan ignoring the
> index... that's what I want 'cause it's cached.
>
> 4) But when I look at V$SQLAREA the DISK_READS column is incremented.
>
> 5) This query is executed very often so my StatsPack report lists it as
one
> of the most expensive queries in terms of physical reads.
>
>
> It appears that Oracle is counting full table scans of cached tables as
> DISK_READS in V$SQLAREA. Seems like this is a bug to me 'cause it's not
> really a physical disk read but is getting data from the buffers.
>
> Don't you think this is a bug? Has anyone seen this before?
>
>
> Steve Orr
> Looking for cache in Bozeman, Montana
> --
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Orr, Steve
> INET: sorr_at_rightnow.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Tim Gorman
INET: Tim_at_SageLogix.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Tue Jul 02 2002 - 00:08:13 CDT
