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.





True or False – Buffer Busy Waits

19 04 2010

April 19, 2010

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

An earlier blog article provided a couple of links that I found to be helpful when encountering buffer busy waits.  There are certainly other helpful web pages within reach of the nearest search engine.  I thought for today’s True or False quiz I would reference some of the pages found by the Google search engine.

Please read the following articles, and try to answer the questions that follow with either a True or False answer, followed by justification for your answer:

  1. http://dba-oracle.com/art_builder_bbw.htm (updated February 2008)
  2. rafioracledba.blogspot.com/2010/03/buffer-busy-waits-and-remedies.html
  3. yanggq.itpub.net/post/1594/111558
  4. remote-dba.net/oracle_10g_tuning/t_buffer_busy_waits.htm
  5. http://www.jlcomp.demon.co.uk/bbw.html
  6. http://books.google.com/books?id=bxHDtttb0ZAC&pg=PA284
  7. http://books.google.com/books?id=tdRes4IdLiIC&pg=PA1176
  8. http://books.google.com/books?id=TmPoYfpeJAUC&pg=PA434
  9. http://books.google.com/books?id=b3DIkYO2gBQC&pg=PA539

The quiz:

1.  Referencing the Top 5 Timed Events at the top of article #1, a Statspack report that indicates 2,154 waits for the CPU is a possible sign of a problem with buffer busy waits.

2. Referencing the Top 5 Timed Events at the top of article #1, a Statspack report that indicates 2,598 waits on the ‘db file sequential read’ wait event and 25,519 waits on the ‘db file scattered read’ wait event provides significant evidence of buffer busy waits when 7,146 seconds and 3,246 seconds, respectively, were reported for those wait events.

3.  I/O bound Oracle database instances frequently experience buffer busy waits.

4.  ‘db file sequential read’ waits and ‘db file scattered read’ waits appearing in the top 5 list in a Statspack report indicate that the database instance is I/O bound.

5.  Eliminating buffer busy waits in the database instance will also reduce the I/O load in the instance.

6.  To reduce the frequency of buffer busy waits, missing indexes should be created, the freelists for the affected tables and indexes should be increased, and then those objects should be moved to an ASSM tablespace.

7.  Referencing article #1, assume that you query V$SYSTEM_EVENT and find that there were a total of 636,528 buffer busy waits.  The reported number of waits on this event indicate that there were 636,528 distinct waits for a block, and this number of buffer busy waits is an indication of a severe problem.

8.  As of February 2008, a block that is in the process of being read into the buffer cache will trigger a buffer busy wait in another session if that other session requires access to that same block.

9.  As of February 2008, the P3 column of V$SESSION_WAIT indicates the reason code for a buffer busy wait, with a value of 0 indicating that a block is in the process of being read into the buffer cache.

10. Freelist groups should be used to reduce segment header contention.

Tip: Think carefully about the questions – some of the questions might not have obvious answers.





True or False – Autotrace

8 04 2010

April 8, 2010

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

I recently encountered a discussion thread (dbaforums.org/oracle/index.php?showtopic=19435) that asked about how to determine whether or not a SQL statement executed in a program uses indexes or full table scans – the database in question is an unspecified release version of Oracle 10g.  One of the responders in that thread pointed to an undated article (praetoriate.com/teas_prae_util11.htm) about the AUTOTRACE functionality of SQL*Plus, while other responders suggested tracing the program’s execution and then using TKPROF.

Please read the article, keeping in mind that the question concerns some release version of Oracle 10g, and see if you are able to answer the following true or false questions.  State why you believe that the question is true, or why you believe that the question is false.  Any answers that attempt to utilize logical fallacies will be scored as incorrect.

1. SQL*Plus’ AUTOTRACE feature performs statement tracing.

2. Using SQL*Plus’ AUTOTRACE feature requires a PLAN_TABLE in the schema of the user using AUTOTRACE.

3. AUTOTRACE retrieves the actual execution plan, along with the associated statistics for the execution.

4. When a large number of rows will be returned by SQL*Plus, the AUTOTRACE TRACEONLY feature should be used.

5. For SQL performance issues, AUTOTRACE is the first tool of choice for investigating the SQL performance issues.

6. Performance issues that are present when SQL is executed in an application will also be present when the SQL statement is executed in SQL*Plus with AUTOTRACE enabled.

-

——————————————————————————-
——————————————————————————-

May 25, 2010

The following screen capture is completely unrelated to this blog article, but is relevant to the comments about the blog redesign.  This is how the redesigned blog appears on a 16:9 netbook, with 1024 horizontal pixels of resolution:

I had to scroll the window slightly to the right to see the entire content portion of the blog, while the “floating” navigation section is hidden from view.  Internet Explorer 8.0 offers a zoom feature near the bottom right of the IE window – that feature (or a similar one in other browsers) might be a solution if your monitor does not offer 1024 pixels of horizontal resolution.

————————

Original Layout (4:3 monitor):
 
Modified Layout (4:3 monitor):





True or False – Wait Events

31 03 2010

March 31, 2010

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

A continuation of the series of true or false quizzes for the week – maybe this one is too easy.  Today’s quiz is on the topic of wait events.   Assuming that the questions apply to a release of Oracle that has been available for up to five years, state why you believe that the answer to the question is true, or why you believe that the answer to the question is false.  Any answers that attempt to utilize logical fallacies will be scored as incorrect.

1. Assume that a database instance is being prepared for 1,500 to 2,000 sessions.  Given an unlimited budget, it is possible to configure the database instance to completely eliminate wait events.

2. Consider two identical Oracle databases with a single session connected to each database instance.  Each of the two sessions submits exactly the same SQL statement, with the first session experiencing no wait events while executing the query, and the second session experiencing nearly continuous I/O wait events.  The lack of wait events implies that the query execution for the first session is optimal, and should be the performance target for the second session.

3. Idle wait events are insignificant in performance tuning exercises, and should be ignored.

4. For every one minute of elapsed time, each CPU in the server is capable of accumulating 60 seconds of CPU wait time.

Did I mention that I dislike true/false type questions?  But then these are not simple true/false questions.





True or False – Improving Performance of SQL Statements

30 03 2010

March 30, 2010

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

A continuation of the concept of yesterday’s true or false quiz – this one is a little easier, with new reading material.  Today’s quiz is on the topic of improving performance of SQL statements.  The reading material:
Article 1
Article 2
Article 3

Article 4
Article 5
Article 6
Article 7
Article 8 (actually a book)
Article 9 (actually a book)
Article 10 (actually a book)
Article 11

Please read the above articles and see if you are able to answer the following true or false questions, assuming that the questions apply to a release of Oracle that has been available for up to five years.  State why you believe that the answer to the question is true, or why you believe that the answer to the question is false.  Any answers that attempt to utilize logical fallacies will be scored as incorrect.  (Most of these questions can be answered with the help of the first couple of links.)

1. Queries containing subqueries should be rewritten as a logical step in improving query performance.

2. Complex queries should be decomposed into multiple queries using global temp tables and/or WITH clauses as a step toward improving query performance.

3. Significant performance improvements, possibly 20 fold, are possible by replacing some or all of a SQL statement with PL/SQL.  If true, provide an example showing a significant performance improvement with such a replacement.  If not true, provide an example that shows that performance did not improve significantly.

4. There are cases were performance improvements are possible by modifying a SQL statement containing a three table equijoin, which accesses primary and foreign keys columns of the tables, into a SQL statement which accesses a single table with two PL/SQL functions (each containing a SELECT) in column positions.





True or False – Oracle Sorting

29 03 2010

March 29, 2010 (Updated April 6, 2010 with a test table)

(Forward to the Next Post in the Series)

I recently encountered a discussion thread (dbaforums.org/oracle/index.php?s=eacd9ff86b358b4a14ecd3fd7653a9fd&showtopic=19407) that pointed to a news article about the internals of Oracle sorting.  The news article (dba-oracle.com/t_oracle_sorting.htm) has a date of October 15, 2007, so it is probably reasonable to assume that the article describes the behavior of Oracle Database 10g R2, and possibly Oracle Database 11g R1.

Please read the news article and see if you are able to answer the following true or false questions.  State why you believe that the question is true, or why you believe that the question is false.  Any answers that attempt to utilize logical fallacies will be scored as incorrect.

1. Sequencing of database output first started in the 1960s.

2. One of the very important components of Oracle tuning is Oracle sorting, yet that process is often overlooked.

3. SSD is a synonym for a super-fast RAM disk.

4. Oracle Database always performs an automatic sorting operation when a GROUP BY clause is used in a SQL statement, when an index is created, and when an ORDER BY clause is used in a SQL statement.

5. The cheapest method is always used by Oracle Database when ordering a resultset.

6. A hinted execution plan involving a single table, with a /*+ index */ hint, will always retrieve the rows in the sorted order of the index.

7. If a SQL statement requires a single sort operation that completes in memory, that SQL statement will not use any space in the TEMP tablespace when the rows are retrieved – with the assumption that a hash join did not spill to disk.

8. The CPU_COST parameter causes Oracle Database to favor the pre-sorted ordering of an index over a discrete sorting operation.

9. The value of the SORT_AREA_SIZE parameter or the PGA_AGGREGATE_TARGET parameter if used, influences Oracle Database’s decision to prefer the pre-sorted ordering of an index over a discrete sorting operation.

10. The clustering factor of an index influences Oracle Database’s decision to prefer the pre-sorted ordering of an index over a discrete sorting operation.

11. The default database block size in use by the database influences Oracle Database’s decision to prefer the pre-sorted ordering of an index over a discrete sorting operation.

12. A sort operation will only spill to disk when RAM is exhausted.

13. “At the time a session is established with Oracle, a private sort area is allocated in memory for use by the session for sorting, based on the value of the sort_area_size initialization parameter.”  Supporting evidence:
http://books.google.com/books?id=gsFC1D1LmvQC&pg=PA306&lpg=PA306#v=onepage&q=&f=false
http://www.articles.freemegazone.com/oracle-sorting.php
oracle-training.cc/oracle_tips_sort_operations.htm

14. For sort intensive tasks it is not possible to adjust the amount of memory allocated to those tasks by adjusting the SORT_AREA_SIZE parameter at the session level.

15. The entire database can be slowed down due to a disk sort in the TEMP tablespace because sorts to disk are I/O intensive.

16. A good general rule is that the SORT_AREA_SIZE parameter should be adjusted to eliminate sorts to disk caused by GROUP BY operations.

17. Buffer pool blocks are allocated to hold or manage the blocks that are in the TEMP tablespace.

18. An optimal workarea execution, completed entirely in memory, is always preferred over a one-pass or multi-pass workarea execution.

19. Free buffer waits can be caused by excessive sorts to disk, which cause data blocks needed by other sessions to be paged out of the buffer.

20. One percent is an acceptable ratio of disk sorts to the total number of sorts.

21. When the PGA_AGGREGATE_TARGET parameter is specified, the total work area size cannot exceed 200MB.

22. No task may use more than 10MB for sorting.

23. A DBA should modify two hidden (underscore) parameters to permit up to 50MB of memory to be used for an in-memory sort operation for a SQL statement.

Have you ever read an article in an attempt to find the answer to a very specific question, only to find that by the time the end of the article is reached, you now have a whole new set of questions?  Try to answer the above questions using something beyond true or false – tell me why in detail it is true or why it is false.  Are there any other questions that could be asked about the article?

——-

Test table for question #10, added April 6, 2010:

CREATE TABLE T1 (
  C1 NUMBER,
  C2 NUMBER,
  C3 VARCHAR2(100),
  PRIMARY KEY (C1));

INSERT INTO T1
SELECT
  ROWNUM,
  DECODE(MOD(ROWNUM,100),0,NULL,ROWNUM),
  RPAD('A',100,'A')
FROM
  DUAL
CONNECT BY
  LEVEL<=10000;

COMMIT;

ALTER TABLE T1 MODIFY (C2 NOT NULL);

CREATE INDEX IND_T1_C2 ON T1(C2);

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE)







Follow

Get every new post delivered to your Inbox.

Join 137 other followers