True or False – Direct Path Reads

21 04 2010

 April 21, 2010

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

Time for another true or false pop quiz.  Based on the number of views the previous true or false quizzes have experienced, this is a popular series.  However, the questions might be a little too hard to answer correctly - just what is correct is not always easy to determine?  Today’s quiz is on the topic of direct path reads, sparked by one of the test cases that we set up for the Expert Oracle Practices book.

Articles:

  1. http://www.dba-oracle.com/t_direct_path_read_waits.htm (dated June 5, 2009)
  2. http://oracledoug.com/serendipity/index.php?/archives/774-Direct-Path-Reads.html (dated May 22, 2006)
  3. http://oracledoug.com/serendipity/index.php?/archives/1321-11g-and-direct-path-reads.html (dated September 24, 2007)
  4. http://dioncho.wordpress.com/2009/07/21/disabling-direct-path-read-for-the-serial-full-table-scan-11g/ (dated July 21, 2009)
  5. http://antognini.ch/2009/07/impact-of-direct-reads-on-delayed-block-cleanouts/ (dated July 23, 2009)
  6. http://books.google.com/books?id=14OmJzfCfXMC&pg=PA121#v=onepage&q&f=false (dated 2004)

Answering the true or false questions probably requires examining the publication date of the article, if a specific Oracle release is not indicated in the article.  I have had a little bit of difficulty finding the exact (or even approximate) release dates of Oracle’s Database products in the past, so I performed a couple of searches and posted the results below.  The release dates listed below are from the following articles:
http://www.oracle.com/support/library/brochure/lifetime-support-technology.pdf
Metalink Doc ID 742060.1
http://www.orafaq.com/wiki/Oracle_8
http://www.orafaq.com/wiki/Oracle_8i

8         June 1997
8.1.5     February 1999
8.1.7     September 2000
9.0.1     June 2001
9.2.0.1   July 2002
10.1.0.1  January 2004
10.1.0.5  January 2006
10.2.0.1  July 2005
10.2.0.4  February 2008
11.1.0.6  August 2007
11.1.0.7  September 2008
11.2.0.1  September 2009

When answering the quiz, please provide justification for your answers – answer the question “why is the question true or false”.

The quiz:

1.  Direct path read waits will only appear when parallel query is used, for example, a full table scan executed in parallel. 

2.  Parallel query is enabled at the session and instance level by setting a table’s parallel degree to 32.

3.  Setting a parallel degree for a table or index forces the cost-based optimizer to calculate the cost of a full scan of the table (or index) with a lower value than the cost of the corresponding index’s range scan access path.

4.  Physical block reads performed during parallel query operations are always brought directly into the program global area (PGA), completely avoiding the buffer cache. *

5.  The hidden parameter _SERIAL_DIRECT_READ should be set to FALSE to prevent problems caused by excessive direct path reads.

6.  The hidden parameter _SMALL_TABLE_THRESHOLD affects Oracle’s decision to perform direct path reads.








Follow

Get every new post delivered to your Inbox.

Join 148 other followers