April 21, 2010
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.
- http://www.dba-oracle.com/t_direct_path_read_waits.htm (dated June 5, 2009)
- http://oracledoug.com/serendipity/index.php?/archives/774-Direct-Path-Reads.html (dated May 22, 2006)
- http://oracledoug.com/serendipity/index.php?/archives/1321-11g-and-direct-path-reads.html (dated September 24, 2007)
- http://dioncho.wordpress.com/2009/07/21/disabling-direct-path-read-for-the-serial-full-table-scan-11g/ (dated July 21, 2009)
- http://antognini.ch/2009/07/impact-of-direct-reads-on-delayed-block-cleanouts/ (dated July 23, 2009)
- 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:
Metalink Doc ID 742060.1
8 June 1997 8.1.5 February 1999 8.1.7 September 2000 9.0.1 June 2001 184.108.40.206 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 220.127.116.11 August 2007 18.104.22.168 September 2008 22.214.171.124 September 2009
When answering the quiz, please provide justification for your answers – answer the question “why is the question true or false”.
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.