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.

« »


Actions

Information

13 responses

21 04 2010
Narendra (03:45:42) :

Charles,

Apologies as I have not read the articles before answering questions (just did as an exercise in break). So here is my best try:

1.Direct path read waits will only appear when parallel query is used, for example, a full table scan executed in parallel.
False
2. Parallel query is enabled at the session and instance level by setting a table’s parallel degree to 32.
False
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.
Not sure
4. Physical block reads performed during parallel query operations are always brought directly into the program global area (PGA), completely avoiding the buffer cache. *
False
5. The hidden parameter _SERIAL_DIRECT_READ should be set to FALSE to prevent problems caused by excessive direct path reads.
Difficult to say. Are you asking if this is the first or only solution? Then my answer is FALSE.
6. The hidden parameter _SMALL_TABLE_THRESHOLD affects Oracle’s decision to perform direct path reads.
Difficult to say. Are you asking if this is the first or only solution? Then my answer is FALSE.

Reply
21 04 2010
Fidelinho (07:01:38) :

1. FALSE, they also appear when oracle needs to read data from the temporal tablespace (like sorts and hash join operations …) or non cached LOB reads.
2. FALSE, why 32? it could any other value. It can aslo be enabled at statement level with the hint PARALLEL(, )
3. FALSE, The CBO will perform its calculations and the FTS could be cheaper or not. That will depend on the query, the conditions, the statistics and the index composition.
4. FALSE. Blocks that are read via Parallel Execution are usually read directly from disk bypassing the Oracle buffer pool. For smaller segments Oracle may decide to switch back to a buffered access as an exception.
5. FALSE. _SERIAL_DIRECT_READ, If I understand properly, the parameter will make “serial” FTS behave as parallel ones. You will be deactivating this but the “other” causes for direct path reads will remain.
6. TRUE. Again, if my understanding is correct. This parameter indicates to oracle “what” tables are small. For small tables no direct path reads is done

Reply
21 04 2010
Charles Hooper (12:44:38) :

Narendra,

I must appologize – this blog article, as originally posted, did not request justification for the answer to the question being either true or false. This request was included in the previous true and false articles and was intended to be included in this blog article. I added the request to this blog article roughly 4 hours after this blog article originally appeared, while your comment was submitted just less than 3 hours after this blog article originally appeared.

—

Fidelinho, your justifications differ a bit from those that I had in mind when I wrote the questions. That is not to say that your justifications are more or less correct than the justifications I had in mind. I will, however, say “nice improvement to what I had in mind” for your answer to question #2. That is one of the benefits of these types of blog articles – input from readers of the blog articles adds value to the blog articles.

—

These questions remind me a bit of the exam that I had to take for certification as a computer instructor. I recall having to repeatedly check the copyright date that was marked on the test before marking down answers to the questions on the test. Question #1 above is one of those types of questions, but fortunately you do not just have to pick one of A, B, C, D, or E for an answer.

Reply
21 04 2010
Fidelinho (12:59:56) :

Hi,
Thanks for the answers. I do not pretend to say that my answers are “better” or “worse” than yours. It is what came to my mind at that point in time. Nice because It made me think a little

Reply
21 04 2010
Charles Hooper (17:45:45) :

I want to first say that my answers are *not* the only correct answers – I might even be wrong from time to time. I must compliment WordPress for the possibly related blog article topics of “anti-garlic”, “Hee!”, and “Too Many Crooks Spoil the Broth” – something might be leaving a bad taste in my mouth (this is a figure of speech for the non-native English readers).

For question #1 the keyword “waits” is critical in the phrasing of the question (for non-native English readers, this distinction may have been lost in translation). If the word “waits” did not appear, then this question becomes one of when are direct path reads used. Most of the possibilities are listed in the linked article #6, a book with a copyright date of 2004 if I recall correctly, and I believe that Fidelinho covered this possibility also. Oracle 11g R1, introduced in August 2007 based on the dates I listed in this blog article, enabled the capability for non-parallel (serial) operations to perform direct path reads, and these direct path reads appear as direct path waits in 10046 trace files. Articles #3, #4 and #5 above describe this behavior, and article #2 shows that the feature may be enabled in Oracle 10g by modifying a hidden initialization parameter.

Once again, the keyword “waits” is key for answering this question as it was originally intended. Prior to Oracle Database 10g R1 direct path reads that accessed the TEMP tablespace were lumped into the same ‘direct path read’ wait event that appears when parallel query operations were used. With Oracle Database 10g R1 reads of the TEMP tablespace appear as ‘direct path read temp’ waits in a 10046 trace file. With Oracle Database 11g R1 direct path read waits may appear automatically during a full table scan without the use of parallel query.

Correctly answering question #1 therefore requires either the specification of a specific Oracle Database release (11.1.0.6, 10.2.0.4, 9.2.0.8, etc.) or a specific “as of” date. Article #1 has a date that falls between the release of Oracle Database 11.1.0.7 and 11.2.0.1. Question #1 is a restatement of an assertion that appears in article #1. The short answer for question #1 is False.

I encourage readers to provide their answers to the above questions. Even if the answers are not 100% what I had in mind, the answers might provide additional detail that allows the message of the blog article to expand into additional, new directions as happened with Fidelinho’s justification for question #1. The hope is that blog articles like this one cause people to stop, think, and understand (even if my answers are not completely correct due to translation problems or a mistake that I might have made).

Reply
22 04 2010
Charles Hooper (10:38:47) :

#2 False – the first thing that I thought about when I saw the suggestion to set the table’s parallel degree to 32 was the same as what was mentioned by Fidelinho – why 32? What was the logical process to arrive at the number 32, why is that number better or worse than the number 2, 10, or 1000 (https://hoopercharles.wordpress.com/2010/02/17/parallel-reasoning/). Simply setting a table’s parallel degree to a value greater than 1 does not force the use of parallel query at the session or instance level. What if the table is never queried by a session, is parallel query still enabled for the session? What if, as described in my Parallel Reasoning article, an index access path still offers a lower calculated cost than would the parallel access path?

I like Fidelinho’s suggestion that a hint is also able to cause a parallel execution – something that I did not consider while writing the question.

#3 False, setting the degree of parallel will decrease the calculated cost of the full table scan, but will not necessarily decrease the calculated cost so that it is lower than the calculated cost for an index range access path.

Reply
23 04 2010
Donatello Settembrino (08:33:29) :

Hi Charles,
I state that I don’t read the link that you have suggested 😦 but I know the discussion Christian Antognini’s blog because I was involved too

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

False, direct reads can be performed with a serial table scan

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

False. I had too the same question, why 32? just use
hint / * + PARALLEL (table-name integer) * /

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.

False. Even for me is false, I’m fully agree with your assertion
“False, setting the degree of parallel will decrease the calculated cost of the full table scan, but will not necessarily decrease the calculated cost so that it is lower than the calculated cost for an index range access path. ”

SQL> create table t1 as select rownum as id , 'x' as c from dual connect by level  commit;

SQL> create index ix on t1(id);

SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname => user, tabname => 'T1');

SQL> set autotrace traceonly;
 
SQL> select /*+ index(t1 ix) */ count(*) from t1 where id between 5000 and 70000;

---------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |    82   (2)|
|   1 |  SORT AGGREGATE   |      |     1 |    13 |            |
|   2 |   INDEX RANGE SCAN| IX   | 82653 |  1049K|    82   (2)|
---------------------------------------------------------------

SQL> select /*+ parallel(t1 4) */ count(*) from t1 where id between 5000  and 70000;

---------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |    82   (2)|
|   1 |  SORT AGGREGATE   |      |     1 |    13 |            |
|   2 |   INDEX RANGE SCAN| IX   | 82653 |  1049K|    82   (2)|
---------------------------------------------------------------

forcing parallels the CBO doesn’t consider to use it. The cost by using index is less. In fact, if I make the index unusable …

SQL> alter index ix unusable;

SQL> select /*+ parallel(t1 4) */ count(*) from t1 where id between 5000  and 70000;

----------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)|    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     1 |    13 |    84   (8)|        |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |    13 |            |        |      |            |
|   2 |   PX COORDINATOR       |          |       |       |            |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |    13 |            |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |    13 |            |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          | 82653 |  1049K|    84   (8)|  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| T1       | 82653 |  1049K|    84   (8)|  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------

…the cost achieved by the parallel is greater than that obtained using the index

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

False. “completely avoiding the buffer cache”..hmmm, teorically, the blocks are taken and brought to the PGA, but if some of these blocks are dirty and are therefore in the buffer cache, Oracle will perform a checkpoint (so will be downloaded to disk) and retrieve the blocks from disk

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

False (in some cases). In 10g _SERIAL_DIRECT_READ setting to FALSE causes Oracle to not perform direct reads (even if the number of blocks requested exceeds the threshold defined on the hidden parameter _small_table_threshold).
In 11g, with _serial_direct_read = FALSE and if the number of blocks exceeds 5 times the value of _small_table_threshold the direct reads are applied
You can see the test case that I have done the following link

http://antognini.ch/2009/07/impact-of-direct-reads-on-delayed-block-cleanouts/#comment-6068

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

True or False 🙂 depends by versions .My interpretation is always at the following link

http://antognini.ch/2009/07/impact-of-direct-reads-on-delayed-block-cleanouts/#comment-6068

Regards

Reply
23 04 2010
Charles Hooper (10:38:44) :

Donatello,

Thank you for adding value to this blog article with your answers. Just as was the case with Fidelinho’s answers, your answers were a little different from what I had in mind when I wrote the questions – but that is an improvement, I think. Your answers included information that I had hoped that someone would include in their responses. Article #1 attempted to answer questions #1 through #4 – I think 🙂 , while questions 5 and 6 require much more investigation to answer.

The answers that I put together continue:
#4 The * at the end of the question links to the documentation for Oracle Database 11.2.0.1 that describes the PARALLEL_DEGREE_POLICY parameter: “When the parameter PARALLEL_DEGREE_POLICY is set to AUTO, Oracle Database decides if an object that is accessed using parallel execution would benefit from being cached in the SGA (buffer cache)… If the size of the object is larger than the size of the buffer cache (single instance) or the size of the buffer cache multiplied by the number of active instances in an Oracle RAC cluster, then it is read using direct path reads.” So, as of 11.2.0.1 parallel query is able to automatically take advantage of the buffer cache – therefore this question is false.

Donatello mentioned that a checkpoint (object check point?) is performed to write the dirty blocks to disk before the parallel operation is able to start, and therefore parallel query does not completely avoid the buffer cache. This is not something that I planned to include in my answer, but I am happy that someone mentioned this behavior.

—

#5 I initially planned to cheat on the answer to this question. _SERIAL_DIRECT_READ is a hidden parameter, and should NOT be modified without the consent of Oracle support. So, the answer is FALSE, this parameter should not be modified. (The excellent answers provided by the other responders demonstrate why I should not be answering all of the questions 🙂 ).

#6 To an extent the _SMALL_TABLE_THRESHOLD parameter value controls whether or not recent releases of Oracle Database perform direct path reads when the _SERIAL_DIRECT_READ parameter is set to TRUE. The linked blog article that was written by Dion Cho indicates that the tipping point is reached when the number of block exceeds 5 times the value of _SMALL_TABLE_THRESHOLD. I would probably need to perform first-hand tests to confirm that this is always the tipping point in 11.1.0.6, 11.1.0.7, and 11.2.0.1. So, this is the one and only question that I could answer with TRUE, although Donatello makes a good point that the answer could also be false.

Reply
1 06 2011
Roni Vered (06:19:12) :

Fascinating !
Thank you so much for you post.
I spent some time on this post, as it was so interesting.
I stamped on the Direct read path issue today and you and your comments gave me a lot to think of.

Cheers 🙂
Roni.

PS – I didn’t find anywhere a link for general RSS for your blog, only for the specific posts’ comments.
Can you please send me the rss feed?
I don’t like notifications by mails.

Reply
1 06 2011
Charles Hooper (06:54:51) :

Hi Roni,

Thank you for the comment.

My blog only sends out shortened blog articles in the RSS feed. I searched WordPress and found the following article that shows how to subscribe to an RSS feed for a WordPress blog:
http://en.support.wordpress.com/feeds/

Based on the above article, it appears that there are several variations of RSS feeds for WordPress blogs. The main RSS feed for this blog appears to be:
https://hoopercharles.wordpress.com/feed/

You might also take a look at the OakTable Network site, where my blog articles are listed, along with the blog articles of other (much more knowledgeable) OakTable Network members:
http://www.oaktable.net/

And the RSS feed for the OakTable Network site:
http://www.oaktable.net/feed/blog-rss.xml

Reply
1 06 2011
Roni Vered (07:43:21) :

Thanks, I’ve subscribed.
Keep posting interesting challenges 🙂
We publish every month a database challenge in our website (ilDBA.co.il), and the May challenge was about the aforementioned behavior (what happens with FTS).
The site is in Hebrew, so it probably won’t be helpful to you, but your post helped me with answering this month’s challenge.

Cheers,
Roni.

Reply
1 06 2011
Charles Hooper (08:14:44) :

It appears that computers with the Google toolbar installed are offered an automatic translation from Hebrew to another language.
http://www.ildba.co.il/challange-of-the-month/challange-of-the-month-may-2011/

Google’s Translate site also makes it possible to read the page with a left justified text, although code sections are problematic.

Reply
1 06 2011
Roni Vered (08:33:41) :

I actually tried translating it before I’ve sent the comment, but I found the translation far from satisfying, so I’ve omitted this option 🙂
(although some times I read threads in Chinese – Oracle forums, when searching for a solution for various of issues, and then Google Translate is more than enough for the these needs).

We do have another database oriented site – http://www.dbsnaps.com, but as we’re now concentrating with building the Israeli one, it wasn’t updated much since the beginning of the year (It has many database HOW-TOs clips).

Anyway, thanks again,
You have now a new follower for you site 🙂
Roni.

Reply

Leave a comment