True or False – Direct Path Reads

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.

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.

Actions

Information

13 responses

21042010

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.

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

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.

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).

#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.

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. ”

…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

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.

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.

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/

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/

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.

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.

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:
<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: