Physical Read Direct, how to force it?

From: Dan Bikle <dbikle_at_rahul.net>
Date: 18 Mar 2002 01:25:51 GMT
Message-ID: <a73fmv$ncc$1@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

Received on Sun Mar 17 2002 - 19:25:51 CST