Re: Physical Read Direct, how to force it?

Forget cache/nocache. That has nothing to do with it. Direct reads are taking place for a number of reasons, but probably the majority of them are sorts using direct reads (which means that the merge phase of a sort is by-passing the buffer cache... and quite right too!). The other thing that can do direct reads is parallel query.

The business about Direct=Y for exports and so on is entirely missing the point.

*Any* physical read is bad news, on the whole. Direct or otherwise (and there is no way to control that, short of forcing parallel query... all data blocks read for normal SQL statements will be non-direct). The purpose of a cache is to allow the requested data to be found already in memory. You want *logical* reads, therefore. Otherwise, you are interacting with a spinning lump of metal (or ceramic) called a hard disk, and that's s-l-o-w. Obviously, there are times when you can't avoid physical reads, and logical reads incur CPU costs in any case, so it's not as if they are free.

But I think the essential point is that there is sod all you can do to force Oracle to perform a direct read per se, unless your code forces vast numbers of parallel queries. It does it for sorts. It does it for export. It will not do it for 'select * from emp'.

The real issue is: are you performing too many physical reads of whatever sort? Look at increasing your buffer cache to avoid them. Look at your sql statements to see if you can filter them earlier. Look at using the multiple buffer pool feature to allow frequently-accessed table data to be held in memory for longer in the KEEP pool. Don't bother using CACHE unless you've got Oracle 7... it's effect is negligable at the best of times.

Up-to-date statistics, at best, will help the Optimizer use index access to the data, rather than full table scans, which is obviously a "good thing", since then you won't be performing so many physical reads in the first place.

Regards
HJR

--
----------------------------------------------
Resources for Oracle: http://www.hjrdba.com
===============================


"Dan Bikle" <dbikle_at_rahul.net> wrote in message
news:a73fmv$ncc$1_at_samba.rahul.net...


> Hello,

>

> I just ran the query displayed below:

>

> 00:02:13 SQL> SELECT name, value from v$sysstat where name like '%physical

read%';


>

> NAME

VALUE


> ---------------------------------------------------------------- ---------

-


> physical reads

184055010


> physical reads direct

116919057


> physical reads direct (lob)

0


>

> 00:04:20 SQL>

>

> I'd like to have more control over direct physical reads on my DB when I

> run SQL statements.

>

> I do get a small amount of control when I use DIRECT=Y with the export

utility.


>

> Also, when I use sql loader I only need to add the key value pair:

> DIRECT=Y to force a direct physical write.

>

> I looked through available hints in the 8i SQL Reference and no hints

> caught my eye.  The NOCACHE keyword in the ALTER TABLE syntax looks

promising


> but their description dampens my hopes:

>

> "For data that is not accessed frequently, this clause indicates that

> the blocks retrieved for this table are placed at the least recently

> used end of the LRU list in the buffer cache when a full table scan

> is performed."

>

> Perhaps I should just make sure that my tables' statistics are up to date

> and then I should rely upon the kernel to to direct physical reads when

> it sees fit.

>

> Let me know if you have any syntax demo which will cause a direct physical

read.


>

> Thanks,

> -Dan

> ---------------------------------------------------------------------

> Daniel B. Bikle/Independent Oracle Consultant

> bikle_at_bikle.com | 650/941-6276 | P.O. BOX AG LOS ALTOS CA 94023

> http://www.bikle.com

> ---------------------------------------------------------------------

>

>