Parallel query and direct reads – advanced knowledge article for the new oracle dba




Below article explains about the behaviour of parallel queries. Summary is that parallel queries directly read from disk instead of reading from the oracle buffer cache and in case you are using parallel inserts then the data is inserted after the high water mark of the table. The new oracle dba can find here in detail about the behaviour of oracle parallel queries.

Parallel Execution and Direct Reads [ID 237328.1]  


 

  Modified 24-APR-2004     Type BULLETIN     Status PUBLISHED  
       
 
PURPOSE
-------
 
I give a short overview about the use of direct reads
when we run a operation in parallel.
 
SCOPE & APPLICATION
-------------------
DBA's and support analysts
 
 
Direct read and Parallel Execution
----------------------------------
 
We use Parallel Execution when we would like to read , change
or create larges tables or indexes. In this situation we do not
read the data from the buffer cache we read this directly
from the disk. You should see waits on 'direct path read' when
you query v$session_wait for this session or you use a
10046 level 8 event to trace the PX sessions. 
 
You can monitor the amount of direct reads with the command:
 
SELECT name, value
  FROM v$sysstat
  WHERE name like '%direct%'
 
on instance level.
 
Please have in mind that other operations like sorts can
also use direct reads.
 
For large tables the direct read method is faster than the read
from the buffer cache and it avoids contention on the latches
that protect the buffer cache.
 
We must force any buffers belonging to that object (table/index) to
disk since a buffer may exist in the buffer cache but not yet
written to disk. PX would try to read the non-existent block
and get an error. The force to disk is implemented as a 
checkpoint tablespace, where we checkpoint the tablespace in
which the block resides. In special cases for RAC, since we must
ask all instances to force out dirty buffers, it was quicker to
do a checkpoint database on all instances, but this is no longer
the case in 9.X.
 
For small table we can skip the direct read and read objects from
the  buffer cache.
 

 

Author: admin