Optimizer Costing 4 – What is Wrong with this Quote?

7 12 2010

December 7, 2010

(Back to the Previous Post in the Series)

I recently reviewed the book “Oracle Tuning the Definitive Reference Second Edition”, and did not provide an in-depth technical review of the entire book.  This blog article series will dig into some of the pages that were not specifically included in the review.  What is wrong with the following quote from page 744 of the book?

“Oracle 10g enhancements

Oracle 10g greatly aided the optimization of large SQL workloads with the introduction of dynamic sampling and root-cause optimization with dbms_stats.  Oracle acknowledged that the root cause of sub-optimal SQL execution plans related to the quality of CBO statistics, and they introduced enhancements to dbms_stats to allow for automatic histogram creation and the gather_system_stats procedure to collecting all-important external information, most notably the average disk access times for index access (sequential reads) and full-scan access (scattered reads).”

What, if anything, is wrong with the above quote from the book?

The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true.  It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past).  If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.

As I stated in a comment in the earlier blog article, I would like to clarify that my review on the Amazon site is not intended to suggest that people should not buy the book. As the publisher’s website states that the book is written for senior Oracle DBAs, I suggest that senior DBAs, Oracle Certified Masters, and other people who are able to research the book’s contents *should* buy the book and post reviews of the book that highlight specific content of the book indicating whether or not that content is correctly stated. Such feedback will be a benefit to the Oracle community, and could help to improve Oracle Database books that are published in the future. I think that we need to keep a positive outlook in order to see things improve.

Other pages found during a Google search of the phrase:

  • dba-oracle.com/t_global_sql_optimization.htm
  • oraclezine.blogspot.com/2009/03/important-notes-for-global-sql.html

Related Oracle Database documentation:

« »


Actions

Information

6 responses

7 12 2010
Martin Berger (02:43:54) :

That time let’s start at the end:
* it’s simply wrong to suggest indices are accessed (only) by sequential reads. Also the link between FTS and scattered reads is not that 100% as it could be assumed after reading this sentence.
* the quality of statistics is not THE root cause for bad execution plans. Probably it’s A root cause, but not the only. I’d like to give you at least 2 other possible root causes:
(not yet) implemented features in CBO
complex skewed data: If working with statistics (as CBO is doing) there will always be place for a tuple of data which is not represented by the statistics well and therefore the particular execution plan will be suboptimal.
Just a little starter 😉

Reply
7 12 2010
Charles Hooper (05:41:00) :

Martin,

Impressive answer. Reading from the start of the quote I identified a couple of other problems with the quote – I will let someone else mention those problems (hint: are those items Oracle Database 10g enhancements?).

Adding to your root cause list, another root cause might be using a different Database edition (or release version) in production than what was used in test (see the four part series https://hoopercharles.wordpress.com/2010/11/21/different-performance-from-standard-edition-and-enterprise-edition-1/ for more information).

Reply
7 12 2010
Centinul (06:34:14) :

“Oracle 10g greatly aided the optimization of large SQL workloads with the introduction of dynamic sampling and root-cause optimization with dbms_stats”

The way I interpret that quote is that it is as if dynamic sampling and dbms_stats were new to 10g.

However, I found references to dynamic sampling as far back as 9.2:

http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/sql_1016.htm#33071

I also found references to dbms_stats as far back as 8.1.7:

http://download.oracle.com/docs/cd/A87860_01/doc/appdev.817/a76936/toc.htm

It doesn’t look like Oracle added the gather_system_stats procedure until 9.0.1:

http://download.oracle.com/docs/cd/A91202_01/901_doc/appdev.901/a89852/dbms_sta.htm#1000574

Reply
7 12 2010
Charles Hooper (10:44:53) :

Centinul,

Very nice research.

One final thought. Is dynamic sampling only helpful in cases involving “large SQL workloads”, or can it also be helpful if during the last statistics collection a table contained 0 rows, and that table (at the time of a SQL statement execution) now contains 220 rows (such a problem was mentioned in the “Expert Oracle Practices” book)? For that matter, what is a “large SQL workload”? SQL is a language, so would a large SQL workload be a SQL statement that is 10,000 characters long?

Reply
7 12 2010
Centinul (14:55:56) :

– Is dynamic sampling only helpful in cases involving “large SQL workloads”

I would say no. I say it’s helpful in the cases as defined by Oracle:

“Dynamic sampling augments missing or insufficient optimizer statistics. The optimizer can improve plans by making better estimates for predicate selectivity. Dynamic sampling can supplement statistics such as table block counts, applicable index block counts, table cardinalities (estimated number of rows), and relevant join column statistics.”

Source: http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/stats.htm#CHDBIGII

– can it also be helpful if during the last statistics collection a table contained 0 rows, and that table (at the time of a SQL statement execution) now contains 220 rows (such a problem was mentioned in the “Expert Oracle Practices” book)

I would say it is not helpful in this case either. The documentation states:

“In both the serial and parallel cases, the database performs dynamic sampling when existing statistics are not sufficient:

* Missing statistics

When one or more of the tables in the query do not have statistics, the optimizer gathers basic statistics on these tables before optimization. In this case, the statistics are not as high-quality or as complete as the statistics gathered using the DBMS_STATS package. This tradeoff is made to limit the impact on the compile time of the statement.

* Collected statistics cannot be used or are likely to lead to poor estimates

For example, a statement may contain a complex predicate expression, but extended statistics are not available (see “Extended Statistics”). Extended statistics help the optimizer get good quality cardinality estimates for complex predicate expressions. Dynamic sampling can compensate for the lack of extended statistics.”

In this case the table has statistics, even though they are not correct, therefore dynamic sampling won’t kick in in the general case. I imagine there are some exceptions depending on query complexity and such. I did a single table test that agreed with the Oracle documentation:

SQL > SELECT * FROM V$VERSION;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> CREATE TABLE T AS SELECT * FROM DUAL WHERE 1=0;

Table created.

SQL> SELECT /*+ gather_plan_statistics FINDME1 */ * FROM T;

no rows selected

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------

SQL_ID  9k684tyna45bn, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics FINDME1 */ * FROM T

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |      0 |00:00:00.01 |
|   1 |  TABLE ACCESS FULL| T    |      1 |      1 |      0 |00:00:00.01 |
--------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


17 rows selected.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T');

PL/SQL procedure successfully completed.

SQL> INSERT INTO T SELECT DUMMY FROM DUAL CONNECT BY LEVEL <= 200;

200 rows created.

SQL> COMMIT;

Commit complete.

SQL> SELECT /*+ gather_plan_statistics FINDME2 */ * FROM T;

<snipped>

200 rows selected.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------

SQL_ID  7kc7qr4satk2n, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics FINDME2 */ * FROM T

Plan hash value: 1601196873

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |    200 |00:00:00.01 |      21 |
|   1 |  TABLE ACCESS FULL| T    |      1 |      1 |    200 |00:00:00.01 |      21 |
------------------------------------------------------------------------------------


13 rows selected.

SQL>

SQL> SELECT /*+ gather_plan_statistics dynamic_sampling(t 10) FINDME3 */ * FROM T;

<snipped>

200 rows selected.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------

SQL_ID  dcg54c4fmdh9h, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics dynamic_sampling(t 10) FINDME3 */ *
FROM T

Plan hash value: 1601196873

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |    200 |00:00:00.01 |      21 |
|   1 |  TABLE ACCESS FULL| T    |      1 |      1 |    200 |00:00:00.01 |      21 |
------------------------------------------------------------------------------------


14 rows selected.

SQL>

– what is a “large SQL workload”? SQL is a language, so would a large SQL workload be a SQL statement that is 10,000 characters long?

I think a “large SQL workload” is a relative term that is dependent on the hardware capabilities, software, as well as the user application running.

Reply
8 12 2010
Charles Hooper (11:20:21) :

Centinul,

Very nice test case!

I think that I need to better clarify this statement:
“or can it also be helpful if during the last statistics collection a table contained 0 rows, and that table (at the time of a SQL statement execution) now contains 220 rows (such a problem was mentioned in the “Expert Oracle Practices” book)?”

What I meant by the above is that statistics were collected on the table when it last contained 0 rows (a victim of the automatic nightly stale statistics collection procedure). If statistics exist for a table, the default level of dynamic sampling will not cause a dynamic sample of the table to take place when parsing a SQL statement that accesses the table, and that could be a significant problem when the table contains 220 rows later in the day (there is a Metalink article that describes this behavior, but I cannot find it at the moment – the referenced problem/case study starts on page 306 of the “Expert Oracle Practices” book). Based on tests that I performed, it is still possible that dynamic sampling might take place if parallel query is enabled for the SQL statement and the query is executed in a recent Oracle Database release (see https://hoopercharles.wordpress.com/2010/11/12/dynamic-sampling-changes/ and Metalink ID 1102413.1) – and that agrees with your comment statement.

You supplied a very helpful comment, Centinul.

Reply

Leave a reply to Martin Berger Cancel reply