Insert Error on Oracle 11g but Not on 10g

2 06 2011

June 2, 2011

The most popular article on this blog includes a script that demonstrates generating a deadlock on Oracle Database 11g R1 and R2 when the exact same script on Oracle Database 10g R2 does not trigger a deadlock.  I came across another interesting change between Oracle Database 10.2.0.5 and 11.2.0.2 (the change also appears in 11.1.0.7) that affects the results of Insert statements that seemingly worked fine for years.

We will use the following simple script for this blog article:

CREATE TABLE T1(C1 DATE, C2 NUMBER);

CREATE TABLE T2(C1 DATE, C2 NUMBER);

CREATE OR REPLACE TRIGGER T1_AFTER_INSERT AFTER INSERT ON T1
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
  INSERT INTO T2 VALUES (SYSDATE,ROWNUM);
END T1_AFTER_INSERT;
/

INSERT INTO T1 VALUES(SYSDATE-10,-10);

INSERT INTO
  T1
SELECT
  SYSDATE+ROWNUM,
  ROWNUM
FROM
  DUAL
CONNECT BY
  LEVEL<=10;

INSERT INTO T1 VALUES(SYSDATE-ROWNUM,ROWNUM);

SELECT * FROM T2;

First, a pop quiz:

What is the expected output of the select from table T2?

 Answer A:

C1                C2
--------- ----------
02-JUN-11          1
02-JUN-11          2
02-JUN-11          3
02-JUN-11          4
02-JUN-11          5
02-JUN-11          6
02-JUN-11          7
02-JUN-11          8
02-JUN-11          9
02-JUN-11         10

10 rows selected.

Answer B:

C1                C2
--------- ----------
02-JUN-11          0
02-JUN-11          1
02-JUN-11          2
02-JUN-11          3
02-JUN-11          4
02-JUN-11          5
02-JUN-11          6
02-JUN-11          7
02-JUN-11          8
02-JUN-11          9
02-JUN-11         10

11 rows selected.

Answer C:

C1                C2
--------- ----------
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0

12 rows selected.

Answer D:

no rows selected

Answer E:

C1                C2
--------- ----------
02-JUN-11          1
02-JUN-11          2
02-JUN-11          3
02-JUN-11          4
02-JUN-11          5
02-JUN-11          6
02-JUN-11          7
02-JUN-11          8
02-JUN-11          9
02-JUN-11         10
02-JUN-11         11
02-JUN-11         12

12 rows selected.

Answer F:

C1                C2
--------- ----------
02-JUN-11          1
02-JUN-11          1

2 rows selected.

—————————————————–

Think about your answer for a minute… you might even want to try the experiment on an Oracle Database.  Scroll down when you think that you have the answer.

Script Output from Oracle Database 10.2.0.5:

SQL> CREATE TABLE T1(C1 DATE, C2 NUMBER);

Table created.

SQL> CREATE TABLE T2(C1 DATE, C2 NUMBER);

Table created.

SQL> CREATE OR REPLACE TRIGGER T1_AFTER_INSERT AFTER INSERT ON T1
  2  REFERENCING NEW AS NEW
  3  FOR EACH ROW
  4  BEGIN
  5  INSERT INTO T2 VALUES (SYSDATE,ROWNUM);
  6  END T1_AFTER_INSERT;
  7  /

Trigger created.

SQL> INSERT INTO T1 VALUES(SYSDATE-10,-10);

1 row created.

SQL> INSERT INTO
  2    T1
  3  SELECT
  4    SYSDATE+ROWNUM,
  5    ROWNUM
  6  FROM
  7    DUAL
  8  CONNECT BY
  9    LEVEL<=10;

10 rows created.

SQL> INSERT INTO T1 VALUES(SYSDATE-ROWNUM,ROWNUM);

1 row created.

SQL> SELECT * FROM T2;

C1                C2
--------- ----------
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0

12 rows selected.

Script Output from Oracle Database 11.2.0.2:

SQL> CREATE TABLE T1(C1 DATE, C2 NUMBER);

Table created.

SQL> CREATE TABLE T2(C1 DATE, C2 NUMBER);

Table created.

SQL> CREATE OR REPLACE TRIGGER T1_AFTER_INSERT AFTER INSERT ON T1
  2  REFERENCING NEW AS NEW
  3  FOR EACH ROW
  4  BEGIN
  5    INSERT INTO T2 VALUES (SYSDATE,ROWNUM);
  6  END T1_AFTER_INSERT;
  7  /

Trigger created.

SQL> INSERT INTO T1 VALUES(SYSDATE-10,-10);
INSERT INTO T1 VALUES(SYSDATE-10,-10)
            *
ERROR at line 1:
ORA-00976: Specified pseudocolumn or operator not allowed here.
ORA-06512: at "TESTUSER.T1_AFTER_INSERT", line 2
ORA-04088: error during execution of trigger 'TESTUSER.T1_AFTER_INSERT'

SQL> INSERT INTO
  2    T1
  3  SELECT
  4    SYSDATE+ROWNUM,
  5    ROWNUM
  6  FROM
  7    DUAL
  8  CONNECT BY
  9    LEVEL<=10;
  T1
  *
ERROR at line 2:
ORA-00976: Specified pseudocolumn or operator not allowed here.
ORA-06512: at "TESTUSER.T1_AFTER_INSERT", line 2
ORA-04088: error during execution of trigger 'TESTUSER.T1_AFTER_INSERT'

SQL> INSERT INTO T1 VALUES(SYSDATE-ROWNUM,ROWNUM);
INSERT INTO T1 VALUES(SYSDATE-ROWNUM,ROWNUM)
                              *
ERROR at line 1:
ORA-00976: Specified pseudocolumn or operator not allowed here.

SQL> SELECT * FROM T2;

no rows selected

The results for Oracle Database 11.1.0.7 are identical to those of Oracle Database 11.2.0.2.

The answer to the quiz question is therefore… C AND D, which is of course logically impossible.  Disappointed?  Maybe there is a bug ID in Metalink (MOS) for this particular problem.  :-)  If you search the web, you can find similar SQL statements in various Internet forums, for example on the OTN forums.





Prepared Quizzes, How Would You Answer that Question? 3

13 01 2011

January 13, 2011

(Back to the Previous Post in the Series)

I found a couple of more Oracle Database related quizzes on the Internet.  These quizzes, I would guess, are designed for Oracle Database 8.1 and earlier.  I missed one question in each of the quizzes, but I suspect that I could have missed many more if someone had simply handed the quizzes to me and asked that I answer the questions.  I think that I would intentionally answer the questions as if the quiz were designed for Oracle Database 11.2, because the database release version for the quiz is not specified.  How well would you do if:

  • You answer the questions knowing that the quizzes were designed for Oracle Database 8.1 and earlier?
  • You answer the questions believing that the quizzes were designed for the latest release version of Oracle Database?

The Quizzes:

Consider providing feedback on the individual questions if you decide to post your results here.





Prepared Quizzes, How Would You Answer that Question? 2

5 01 2011

January 5, 2011

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

Since the readers of this blog produced fantastic answers for the previous multi-part quiz, I thought that it might be an interesting challenge to try another quiz.  The questions from this quiz seems to be aimed at OCP (over-clocked personality, obviously checking performance, old cat people, and maybe a few Oracle Certified Professionals) types of people, or maybe I misunderstood.  Try to answer a couple of the questions from this quiz (with justifications of course), and we will compare notes.  My answers do not match, so I failed this quiz also.

OCP Type Questions:

  1. B
  2. D
  3. A
  4. D
  5. A
  6. (1) A
  7. (2) D
  8. (3) B
  9. (4) A
  10. (1) C
  11. (2) D
  12. (3) C
  13. (4) D
  14. (5) B




Prepared Quizzes, How Would You Answer that Question? 1

4 01 2011

January 4, 2011

(Forward to the Next Post in the Series)

While performing a Google search I encountered a couple of Oracle multiple choice quizzes that also supplied justifications for the answer that is described on the page as being correct.  The web pages I found reminded me a bit of some of the quizzes that I put together for this blog, all of which appeared in my blog’s category “Quiz – Whose Answer is it Anyway”.  I thought that it might be helpful to let you read the quizzes, allow you to mention which of the supplied possible answers is correct (with justification of course), and then allow you to indicate whether or not you agree with the correct answer that is listed on the web page.

Oracle Database 11g Optimizer Mode Quiz:

Questions (with the currently supplied answers):

  1. D
  2. C, D
  3. A
  4. B

————-

Oracle Database 11g Rule Optimizer Mode Quiz:

Questions (with the currently supplied answers):

  1. C, D
  2. A
  3. A, B, C

————-

I think that I might have failed both quizzes if they were handed to me as a test of proficiency, how about you?





Analytic Functions – What is Wrong with this Statement?

1 01 2011

January 1, 2011

I was a bit excited to see the chapter discussing analytic functions in the book “Pro Oracle SQL”, which has a rather extensive coverage of most of Oracle Database’s analytic functions (something that I have not seen from other SQL books).  That chapter is very well assembled, with easier to understand descriptions of the various functions than what is found in the Oracle documentation.

However, there is one particular statement in the chapter that made be stop and think for a moment, and then ask “What, if anything, is wrong with this quote” from page 227 of the book:

“The default windowing clause [of analytic functions that support the windowing clause] is rows between unbounded preceding and current row. If you do not specify a window, you’ll get the default window. It is a good approach to specify this clause explicitly to avoid ambiguities.”

Before you answer, check the Oracle Database 11.2 documentation:

 “If you omit the windowing_clause entirely, then the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.”

The Oracle Database documentation is stating essentially the same thing as the book. 

What, if anything, is wrong with this quote (or the quote from the documentation)?





Hash Joins – What is Wrong with this Statement?

29 12 2010

December 29, 2010

I started reading the book “Pro Oracle SQL” a couple of days ago, and I am having trouble putting it down.  Other than a couple of minor word substitutions and intended, but not specified, qualifying words, the first 100 pages of the book are fantastic (I suspect that many of these cases are intentional since that book is not advertised as a SQL performance tuning guide – too much detail might cause confusion).  The description of how SQL processing works, and how to understand execution plans is on par with (or maybe even slightly better than) the treatment in the “Troubleshooting Oracle Performance” book (that reminds me, I still need to finish the second read through of the “Troubleshooting Oracle Performance” book).

As I have done with the last four books that I reviewed, I selected an interesting couple of sentences from this book.  What, if anything, is wrong with the following quote from page 94:

“Based on the table and index statistics, the table that is determined to return the fewest rows will be hashed in its entirety into memory. This hash table includes all of the row data for that table and is loaded into hash buckets based on a randomizing function that converts the join key to a hash value. As long as there is enough memory available, this hash table will reside in memory. However, if there is not enough memory available, the hash table may be written to temp disk space.”

Not as easy of an assignment as with some of the previously extracted quotes?

How about a hint?  Create two simple tables, and collect the full statistics (do not estimate the statistics) on those tables:

CREATE TABLE
  T1
AS
SELECT
  ROWNUM C1,
  LPAD(TO_CHAR(ROWNUM),10,'0') C2,
  LPAD('A',200,'A') PADDING
FROM
  DUAL
CONNECT BY
  LEVEL<=100000;

CREATE TABLE
  T2
AS
SELECT
  ROWNUM C1,
  LPAD(TO_CHAR(ROWNUM),10,'0') C2,
  LPAD('A',400,'A') PADDING
FROM
  DUAL
CONNECT BY
  LEVEL<=100000;

COMMIT;

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',ESTIMATE_PERCENT=>NULL)
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T2',ESTIMATE_PERCENT=>NULL) 

Now we should probably create a test script that uses the tables:

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT
  *
FROM
  T1,
  T2
WHERE
  T1.C1=T2.C1;

SELECT
  T1.C1,
  T2.C1
FROM
  T1,
  T2
WHERE
  T1.C1=T2.C1;

SELECT
  T1.C1,
  T2.C1
FROM
  T1,
  T2
WHERE
  T1.C1=T2.C1
  AND T1.C2=T2.C2;

SELECT
  T1.C1,
  T2.C1,
  T1.C2
FROM
  T1,
  T2
WHERE
  T1.C1=T2.C1
  AND T1.C2=T2.C2;

SELECT
  T1.C1,
  T2.C1,
  T1.C2,
  T1.PADDING
FROM
  T1,
  T2
WHERE
  T1.C1=T2.C1
  AND T1.C2=T2.C2;

SELECT
  T1.C1,
  T2.C1,
  T1.C2,
  T1.PADDING,
  T2.PADDING
FROM
  T1,
  T2
WHERE
  T1.C1=T2.C1
  AND T1.C2=T2.C2; 

How did you do?  Did you find anything wrong with the quote from the book, or is it completely correct?

-

-

-

I hear someone shouting, “But the above script did not even execute a SQL statement!”.  OK, try this:

ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH SHARED_POOL;
SET AUTOTRACE TRACEONLY STATISTICS
ALTER SESSION SET STATISTICS_LEVEL='ALL';
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=2000M SCOPE=MEMORY;

Edit Dec 29, 2010: Wait 60 seconds before continuing, just to make certain that the changed PGA_AGGREGATE_TARGET had a chance to take effect (to avoid this trap).  Continuing:

SELECT
  *
FROM
  T1,
  T2
WHERE
  T1.C1=T2.C1;

SELECT
  T1.C1,
  T2.C1
FROM
  T1,
  T2
WHERE
  T1.C1=T2.C1;

SELECT
  T1.C1,
  T2.C1
FROM
  T1,
  T2
WHERE
  T1.C1=T2.C1
  AND T1.C2=T2.C2;

SELECT
  T1.C1,
  T2.C1,
  T1.C2
FROM
  T1,
  T2
WHERE
  T1.C1=T2.C1
  AND T1.C2=T2.C2;

SELECT
  T1.C1,
  T2.C1,
  T1.C2,
  T1.PADDING
FROM
  T1,
  T2
WHERE
  T1.C1=T2.C1
  AND T1.C2=T2.C2;

SELECT
  T1.C1,
  T2.C1,
  T1.C2,
  T1.PADDING,
  T2.PADDING
FROM
  T1,
  T2
WHERE
  T1.C1=T2.C1
  AND T1.C2=T2.C2;

SET AUTOTRACE OFF
SET LINESIZE 150
SET PAGESIZE 2000
SET TRIMSPOOL ON
SPOOL TEST_HASH_PLANS.TXT

SELECT /*+ LEADING(S) */
  T.PLAN_TABLE_OUTPUT
FROM
  (SELECT
    SQL_ID,
    CHILD_NUMBER
  FROM
    V$SQL
  WHERE
    SQL_TEXT LIKE 'SELECT%T1.C1=T2.C1%') S,
  TABLE(DBMS_XPLAN.DISPLAY_CURSOR(S.SQL_ID,S.CHILD_NUMBER,'ALLSTATS LAST')) T;

SPOOL OFF 

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

-

-

-

-

-

Show you the output that I received?  OK:

SELECT
  *
FROM
  T1,
  T2
WHERE
  T1.C1=T2.C1;

Execution Plan
----------------------------------------------------------
Plan hash value: 1838229974

-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   100K|    60M|       |  1493   (1)| 00:00:12 |
|*  1 |  HASH JOIN         |      |   100K|    60M|    21M|  1493   (1)| 00:00:12 |
|   2 |   TABLE ACCESS FULL| T1   |   100K|    20M|       |   248   (1)| 00:00:02 |
|   3 |   TABLE ACCESS FULL| T2   |   100K|    39M|       |   464   (1)| 00:00:04 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."C1"="T2"."C1")

-

SELECT
  T1.C1,
  T2.C1
FROM
  T1,
  T2
WHERE
  T1.C1=T2.C1;

Execution Plan
----------------------------------------------------------
Plan hash value: 1838229974

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   100K|   976K|   713   (1)| 00:00:06 |
|*  1 |  HASH JOIN         |      |   100K|   976K|   713   (1)| 00:00:06 |
|   2 |   TABLE ACCESS FULL| T1   |   100K|   488K|   248   (1)| 00:00:02 |
|   3 |   TABLE ACCESS FULL| T2   |   100K|   488K|   464   (1)| 00:00:04 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."C1"="T2"."C1")

-

SELECT
  T1.C1,
  T2.C1
FROM
  T1,
  T2
WHERE
  T1.C1=T2.C1
  AND T1.C2=T2.C2;

Execution Plan
----------------------------------------------------------
Plan hash value: 1838229974

-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   100K|  3125K|       |   781   (1)| 00:00:07 |
|*  1 |  HASH JOIN         |      |   100K|  3125K|  2736K|   781   (1)| 00:00:07 |
|   2 |   TABLE ACCESS FULL| T1   |   100K|  1562K|       |   248   (1)| 00:00:02 |
|   3 |   TABLE ACCESS FULL| T2   |   100K|  1562K|       |   464   (1)| 00:00:04 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."C1"="T2"."C1" AND "T1"."C2"="T2"."C2")

-

SELECT
  T1.C1,
  T2.C1,
  T1.C2
FROM
  T1,
  T2
WHERE
  T1.C1=T2.C1
  AND T1.C2=T2.C2;

Execution Plan
----------------------------------------------------------
Plan hash value: 1838229974

-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   100K|  3125K|       |   781   (1)| 00:00:07 |
|*  1 |  HASH JOIN         |      |   100K|  3125K|  2736K|   781   (1)| 00:00:07 |
|   2 |   TABLE ACCESS FULL| T1   |   100K|  1562K|       |   248   (1)| 00:00:02 |
|   3 |   TABLE ACCESS FULL| T2   |   100K|  1562K|       |   464   (1)| 00:00:04 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."C1"="T2"."C1" AND "T1"."C2"="T2"."C2")

-

SELECT
  T1.C1,
  T2.C1,
  T1.C2,
  T1.PADDING
FROM
  T1,
  T2
WHERE
  T1.C1=T2.C1
  AND T1.C2=T2.C2;

Execution Plan
----------------------------------------------------------
Plan hash value: 2959412835

-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   100K|    22M|       |  1018   (1)| 00:00:09 |
|*  1 |  HASH JOIN         |      |   100K|    22M|  2736K|  1018   (1)| 00:00:09 |
|   2 |   TABLE ACCESS FULL| T2   |   100K|  1562K|       |   464   (1)| 00:00:04 |
|   3 |   TABLE ACCESS FULL| T1   |   100K|    20M|       |   248   (1)| 00:00:02 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."C1"="T2"."C1" AND "T1"."C2"="T2"."C2")

-

SELECT
  T1.C1,
  T2.C1,
  T1.C2,
  T1.PADDING,
  T2.PADDING
FROM
  T1,
  T2
WHERE
  T1.C1=T2.C1
  AND T1.C2=T2.C2;

Execution Plan
----------------------------------------------------------
Plan hash value: 1838229974

-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   100K|    60M|       |  1493   (1)| 00:00:12 |
|*  1 |  HASH JOIN         |      |   100K|    60M|    21M|  1493   (1)| 00:00:12 |
|   2 |   TABLE ACCESS FULL| T1   |   100K|    20M|       |   248   (1)| 00:00:02 |
|   3 |   TABLE ACCESS FULL| T2   |   100K|    39M|       |   464   (1)| 00:00:04 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."C1"="T2"."C1" AND "T1"."C2"="T2"."C2") 

The output from the second script:

SQL_ID  c2nzwfydj9nft, child number 0
-------------------------------------
SELECT   * FROM   T1,   T2 WHERE   T1.C1=T2.C1

Plan hash value: 1838229974

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |    100K|00:00:00.24 |   15298 |       |       |          |
|*  1 |  HASH JOIN         |      |      1 |    100K|    100K|00:00:00.24 |   15298 |    25M|  3830K|   28M (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |    100K|    100K|00:00:00.01 |    3131 |       |       |          |
|   3 |   TABLE ACCESS FULL| T2   |      1 |    100K|    100K|00:00:00.07 |   12167 |       |       |          |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."C1"="T2"."C1")

-

SQL_ID  1r4up8dp9fc00, child number 0
-------------------------------------
SELECT   T1.C1,   T2.C1 FROM   T1,   T2 WHERE   T1.C1=T2.C1

Plan hash value: 1838229974

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |    100K|00:00:00.16 |   15298 |       |       |          |
|*  1 |  HASH JOIN         |      |      1 |    100K|    100K|00:00:00.16 |   15298 |  3749K|  1936K| 5635K (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |    100K|    100K|00:00:00.01 |    3131 |       |       |          |
|   3 |   TABLE ACCESS FULL| T2   |      1 |    100K|    100K|00:00:00.05 |   12167 |       |       |          |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."C1"="T2"."C1")

-

SQL_ID  0rnjh74c5m1dc, child number 0
-------------------------------------
SELECT   T1.C1,   T2.C1 FROM   T1,   T2 WHERE   T1.C1=T2.C1   AND
T1.C2=T2.C2

Plan hash value: 1838229974

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |    100K|00:00:00.25 |   15298 |       |       |          |
|*  1 |  HASH JOIN         |      |      1 |    100K|    100K|00:00:00.25 |   15298 |  4670K|  1438K| 6600K (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |    100K|    100K|00:00:00.01 |    3131 |       |       |          |
|   3 |   TABLE ACCESS FULL| T2   |      1 |    100K|    100K|00:00:00.09 |   12167 |       |       |          |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."C1"="T2"."C1" AND "T1"."C2"="T2"."C2")

-

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
SQL_ID  c691ypk4k096j, child number 0
-------------------------------------
SELECT   T1.C1,   T2.C1,   T1.C2 FROM   T1,   T2 WHERE   T1.C1=T2.C1
AND T1.C2=T2.C2

Plan hash value: 1838229974

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |    100K|00:00:00.23 |   15298 |       |       |          |
|*  1 |  HASH JOIN         |      |      1 |    100K|    100K|00:00:00.23 |   15298 |  4670K|  1438K| 6600K (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |    100K|    100K|00:00:00.01 |    3131 |       |       |          |
|   3 |   TABLE ACCESS FULL| T2   |      1 |    100K|    100K|00:00:00.08 |   12167 |       |       |          |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."C1"="T2"."C1" AND "T1"."C2"="T2"."C2")

-

SQL_ID  dpq7d224rxc6t, child number 0
-------------------------------------
SELECT   T1.C1,   T2.C1,   T1.C2,   T1.PADDING FROM   T1,   T2 WHERE
T1.C1=T2.C1   AND T1.C2=T2.C2

Plan hash value: 2959412835

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |    100K|00:00:00.17 |   15482 |       |       |          |
|*  1 |  HASH JOIN         |      |      1 |    100K|    100K|00:00:00.17 |   15482 |  4670K|  1438K| 6600K (0)|
|   2 |   TABLE ACCESS FULL| T2   |      1 |    100K|    100K|00:00:00.01 |    5892 |       |       |          |
|   3 |   TABLE ACCESS FULL| T1   |      1 |    100K|    100K|00:00:00.05 |    9590 |       |       |          |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."C1"="T2"."C1" AND "T1"."C2"="T2"."C2")

-

SQL_ID  0k4q3ahjzu9nd, child number 0
-------------------------------------
SELECT   T1.C1,   T2.C1,   T1.C2,   T1.PADDING,   T2.PADDING FROM   T1,
  T2 WHERE   T1.C1=T2.C1   AND T1.C2=T2.C2

Plan hash value: 1838229974

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |    100K|00:00:00.24 |   15298 |       |       |          |
|*  1 |  HASH JOIN         |      |      1 |    100K|    100K|00:00:00.24 |   15298 |    25M|  3830K|   28M (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |    100K|    100K|00:00:00.01 |    3131 |       |       |          |
|   3 |   TABLE ACCESS FULL| T2   |      1 |    100K|    100K|00:00:00.06 |   12167 |       |       |          |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."C1"="T2"."C1" AND "T1"."C2"="T2"."C2") 

I think that I forgot the question… was it something about the value of test cases?  :-)





I Didn’t Know That 6 – What is Wrong with this Quote?

14 12 2010

December 14, 2010

(Back to the Previous Post in the Series)

In the interest of interesting quotes, a quote that I found in the “Oracle Tuning the Definitive Reference Second Edition” book on page 541:

“Oracle speeds are very high with SSD, and SSD is also cheap at only $1k/gig USD…  Companies are now offering solid-state disk replacement for the Oracle data buffer cache to speed up I/O at the physical level…”

“Physical disk I/O is measured in milliseconds, an eternity when compared to faster operations within other server components such as network RAM and CPU speeds.  For many years, Oracle shops have been embracing solid-state disks, RAM disks that operate hundreds of time faster than old-fashioned platter technology from the 1960s.  SSD also has no channel contention, and as prices fall, SSD will eventually displace the ancient magnetic spinning platters of the last century.”

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

—————–

While my review of the book only provides an in-depth technical review of the first 200 pages of the book, this blog article series will dig into some of the pages that were not specifically included in the review. 

This blog article marks the last of the blog articles that were scheduled as a component part of the “Oracle Tuning the Definitive Reference Second Edition” book review.  Regular readers will probably recognize that I attempt to encourage people to learn from mistakes, whether your own, or those that you encounter in your day to day duties working with Oracle Database.  If the mistakes are yours, it is your choice whether you continue to make the same mistakes year after year, or if you will seek to learn from the assistance provided by others.  The last four book reviews that I posted have all had a “public opinion” portion (in separate blog articles), and I think that approach adds a new dimension to book reviews – I certainly appreciate the feedback that readers have provided.  If you are a senior DBA, or a person who enjoys digging to find the root meaning of what is stated, take a serious look at buying this book and writing a review. 

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.

Other pages found during a Google search of the phrase:

  • rampant-books.com/t_oracle_ssd_disk_i_o.htm
  • praetoriate.com/t_%20oraclerac_ssd_with_rac.htm
  • dba-oracle.com/t_sun_solaris_solid_state_disk_ssd.htm
  • dba-oracle.com/t_flash_disk_drives_ssd_ram_san.htm
  • dba-oracle.com/art_dbazine_2020_p2.htm




I Didn’t Know That 5 – What is Wrong with this Quote?

13 12 2010

December 13, 2010

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

In the interest of interesting quotes, a quote that I found in the “Oracle Tuning the Definitive Reference Second Edition” book on page 539:

“But what is Oracle’s official position on multiple blocksizes?  For Oracle metal-level customers, there is the Oracle Metalink system which provides the official position of Oracle’s own experts.

Metalink Note: 46757.1, titled Notes on Choosing an Optimal Db Blocksize, says that there are some benefits from having larger blocksizes, but only under specific criteria (paraphrased from Metalink):

  • Large blocks give more data transfer per I/O call.
  • Larger blocksizes provide less fragmentation, i.e. row chaining and row migration, of large objects (LOB, BLOB, CLOB).
  • Indexes like big blocks because index height can be lower and more space exists within the index branch nodes.


Metalink goes on to say that multiple blocksizes may benefit shops that have ‘mixed’ blocksize requirements…”

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

—————–

While my review of the book only provides an in-depth technical review of the first 200 pages of the book, this blog article series will dig into some of the pages that were not specifically included in the review.

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.

Other pages found during a Google search of the phrase:





I Didn’t Know That 4 – What is Wrong with this Quote?

12 12 2010

December 12, 2010

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

In the interest of interesting quotes, a quote that I found in the “Oracle Tuning the Definitive Reference Second Edition” book on page 987:

Row ordering matters!
In some systems where a table is always accessed by the same key sequence, re-ordering the table into the same order as the queries can dramatically reduce I/O and improve SQL performance.

When reorganizing tables to improve SQL performance, keep this in-mind:

  • Only tables that experience multi-block reads (full-table scans) may see an appreciable SQL performance benefit.
  • Some shops will use sorted hash cluster tables to maintain row sequence order (in the same order as the most common indexed retrieval), and you can reorganize a table with an ‘order by’ clause to make the rows in the same sequence as the index.

But it’s not just tables that require periodic maintenance, it’s also indexes.”

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

—————–

While my review of the book only provides an in-depth technical review of the first 200 pages of the book, this blog article series will dig into some of the pages that were not specifically included in the review.

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.

Other pages found during a Google search of the phrase:

  • remote-dba.net/t_oracle_net_reorganize_tables.htm
  • dba-oracle.com/art_disk_io.htm
  • dba-oracle.com/t_table_row_resequencing.htm
  • dba-oracle.com/t_create_table_select_ctas.htm




I Didn’t Know That 3 – What is Wrong with this Quote?

11 12 2010

December 11, 2010

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

In the interest of interesting quotes, a quote that I found in the “Oracle Tuning the Definitive Reference Second Edition” book on page 828:

“When the index can no longer split because the owner block is full, Oracle will spawn a whole new index level, keeping the index tree in perfect logical and phyical balance.  Deletes are a different story.  Physically, Oracle indexes are always balanced because empty blocks stay inside the tree structure after a massive delete. Logically, Oracle indexes are not self-balancing because Oracle does not remove the dead blocks as they become empty.  Figure 16.9 shows an Oracle index before a massive delete…”

What, if anything, is wrong with the above quote?  Please keep in mind that the focus of this blog is on the technical content, and learning from that technical content.  Please stay positive in your responses (before answering, first take a look at page 727 to see if these two sections of the book are related – note that Richard Foote’s PDF file listed below seems to address page 727 of this book).

—————–

While my review of the book only provides an in-depth technical review of the first 200 pages of the book, this blog article series will dig into some of the pages that were not specifically included in the review.

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.

Other pages found during a Google search of the phrase:





I Didn’t Know That 2 – What is Wrong with this Quote?

10 12 2010

December 10, 2010

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

In the interest of interesting quotes, a quote that I found in the “Oracle Tuning the Definitive Reference Second Edition” book on page 988:

“Production DBA’s spend weekends reorganizing their data structures, returning them back into their original, pristine state, in preparation for the return of the end-users on Monday morning.

Rebuilding high-DML indexes in a schedule can be a DBA best practice under certain conditions:

  • You can schedule a job to rebuild and index (and address errors) in just a few minutes.  Because most DBA’s are salaried professionals, the DBA cost is negligible.
  • During a weekly maintenance window when the server sits idle.  Because hardware depreciates rapidly, regardless of use, the cost of rebuilding indexes is essentially zero.”

What, if anything, is wrong with the above quote?  Please keep in mind that the focus of this blog is on the technical content, and learning from that technical content.  Please stay positive in your responses (before answering, first take a look at page 727 to see if we really need to first determine the candidate indexes for a rebuild).

—————–

While my review of the book only provides an in-depth technical review of the first 200 pages of the book, this blog article series will dig into some of the pages that were not specifically included in the review.

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.

Other pages found during a Google search of the phrase:





I Didn’t Know That 1 – What is Wrong with this Quote?

9 12 2010

December 9, 2010

(Forward to the Next Post in the Series)

In the interest of interesting quotes, a quote that I found in the “Oracle Tuning the Definitive Reference Second Edition” book on page 995:

“In the 1970s, Moore’s law was introduced, stating that processor costs were always falling while speed continued to improve.  However, as Oracle professionals, we must understand that Moore’s law does not apply to RAM.  While RAM costs continue to fall every year, the speed of RAM access is constrained by silicon technology and did not improve over at least three decades as shown in Figure 17.1.”

What, if anything, is wrong with the above quote?  While my review of the book only provides an in-depth technical review of the first 200 pages of the book, this blog article series will dig into some of the pages that were not specifically included in the review.

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.

Other pages found during a Google search of the phrase:

  • dba-oracle.com/art_dbazine_oracle_10g_data_warehouse.htm
  • dba-oracle.com/oracle_tips_hardware_oracle_performance.htm
  • dba-oracle.com/t_history_ram.htm

Helpful References:





Wait Events 3 – What is Wrong with this Quote?

9 12 2010

December 9, 2010

(Back to the Previous Post in the Series)

While reading the “Oracle Tuning the Definitive Reference Second Edition” book I found a handful of interesting suggestions regarding Oracle wait events.  Previous articles on this blog have described the contents of 10046 trace files, and leveraged the contents of those files to explain various types of problems and/or unexpected behavior (for example: three part series reading 10046 trace files, EXPLAIN PLAN/AUTOTRACE/TKPROF liesORDERED hint not followedfour part series: Enterprise Edition and Standard Edition perform differently11.2.0.1 ODBC bug, etc.)  Take three minutes to analyze the following quote from page 451 of the book that describes Oracle Database wait events found in 10046 trace files:

“Those Evil Wait Events in the 10046 Trace File

The trace file contains lots of details and it is important to seek out the wait event notes as the wait events are interspersed throughout the 10046 trace file.

WAIT #2: nam='SQL*Net message to client' ela= 10 p1=1111838976 p2=1 p3=0

This wait event record shows that the wait event (nam) is a SQL*Net message to client.  These wait events are the same wait events that can be found in the database in the v$ views like v$session_wait or v$event_name.

The elapsed time (ela) is in microseconds since this database is Oracle 10g, so this wait was a whole 10 microseconds.  This is nothing to worry about because 1 second = 1,000,000 microseconds.  Please note the P1, P2 and P3 variables are specific to each event.”

Keeping in mind that the book is printed after the release of Oracle Database 11.2.0.1 (and possibly 11.2.0.2 for some operating system platforms), what, if anything, is wrong with the above quote?

While my review of the book only provides an in-depth technical review of the first 200 pages of the book, this blog article series will dig into some of the pages that were not specifically included in the review.

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.

Other pages found during a Google search of the phrase:

  • dba-oracle.com/t_10046_trace_file_parse_execute.htm




Wait Events 2 – What is Wrong with this Quote?

8 12 2010

December 8, 2010

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

While reading the “Oracle Tuning the Definitive Reference Second Edition” book I found a handful of interesting suggestions regarding Oracle wait events.  Take 60 seconds to analyze the following quote from page 656:

“There are three columns of the v$session_wait view that are of particular interest for a buffer busy wait event: …  The following is an Oracle data dictionary query for these values:

select
  p1 "File #".
  p2 "Block #",
  p3 "Reason Code"
from
  v$session_wait
where
  event = 'buffer busy waits'; 

Keeping in mind that the book is printed after the release of Oracle Database 11.2.0.1 (and possibly 11.2.0.2 for some operating system platforms), what, if anything, is wrong with the above quote?

While my review of the book only provides an in-depth technical review of the first 200 pages of the book, this blog article series will dig into some of the pages that were not specifically included in the review.

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.

Other pages found during a Google search of the phrase:

  • Previous edition of the book
  • dba-oracle.com/art_builder_bbw.htm
  • praetoriate.com/t_oracle_wait_events_v$system_event.htm
  • dba-oracle.com/t_open_file_waits_event_tuning.htm
  • remote-dba.net/oracle_10g_tuning/t_buffer_busy_waits.htm




Wait Events 1 – What is Wrong with this Quote?

8 12 2010

December 8, 2010

(Forward to the Next Post in the Series)

While reading the “Oracle Tuning the Definitive Reference Second Edition” book I found a handful of interesting suggestions regarding Oracle wait events.  For instance, page 406 of the book states that:

“Excessive db file sequential read waits on an OLTP database might indicate a suboptimal disk subsystem or configuration issues with the disk array.  High I/O waits on the db file scatter read waits associated with large-table full-table scans may not always indicate a problem, but they are most commonly found on these kinds of databases:

  • Data warehouse and Decision Support applications
  • 32-bit Oracle systems with SGAs of less than 1.5 gigabytes
  • Databases that do not have enough buffer cache space to cache their working set of frequently referenced objects”

Keeping in mind that the book is printed after the release of Oracle Database 11.2.0.1 (and possibly 11.2.0.2 for some operating system platforms), what, if anything, is wrong with the above quote?  See example 1 on this blog article for a “Top 5 Timed Events” report section that is similar to what is found on page 406 of the book.

While my review of the book only provides an in-depth technical review of the first 200 pages of the book, this blog article series will dig into some of the pages that were not specifically included in the review.

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:





Waiting for a Long Time – What is Going On?

7 12 2010

December 7, 2010

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.

Over the years I have seen several “Top 5 Timed Events” sections from Statspack and AWR reports.  I recall after reading the “Oracle Performance Tuning 101” book several years ago, the feeling of being quite confused.  Why?  No, it was not a problem with the book – the book made a lot of sense.  After reading the book I searched the Internet looking for more information, for example, for clear indicators that the LOG_BUFFER parameter was undersized.  Is 512KB enough, is 1MB enough, or maybe I have an extreme case that needs a 2MB value for the LOG_BUFFER parameter?  I found a number of web pages in a Google search, and some of those pages included “Top 5″ wait event output.  On one of those web pages the author indicated that the “Top 5″ wait event output indicated an undersized value for the LOG_BUFFER parameter, so I cross-referenced the wait event names with my notes from the “Oracle Performance Tuning 101″ book, and then became very confused.  No wonder Oracle Database performance tuning is so hard – the “Oracle Performance Tuning 101″ book notes caused me to arrive at a completely different problem point than what the author of the web page stated.  After examining more “Top 5″ wait event output sections, and digesting the “Optimizing Oracle Performance” book, I decided that my original opinion about the “Top 5″ sections were more than likely correct.

Let’s try an experiment (or a quiz, if you prefer).  I will show you a slightly modified version of a “Top 5″ wait event section from the “Oracle Tuning the Definitive Reference Second Edition” book, and you tell me your thoughts about that “Top 5″ section.  For example: are there any consistency problems, what does the “Top 5″ section indicate, how does your interpretation of the “Top 5″ section compare with that of the book author?  For an example of what I am trying to uncover, take a look at this blog article.  The last four “Top 5″ sections are from the AskTom website – any opinions on those “Top 5″ sections?

————————–

1 (pages 27 and 406):

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                % Total
Event                          Waits   Time (s)  Ela Time
-------------------------- --------- ---------- --------
db file sequential read        5,196    14,292     48.53
db file scattered read        51,038     6,492     22.05
library cache load lock        1,346     2,726      9.25
CPU time                                 2,308      7.84
log file parallel write       38,314     1,674      5.67 

————————–

2 (pages 28, 323, and 405):

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                            % Total
Event                      Waits    Time (s) Ela Time
-------------------------------- ----------- --------
CPU time                   9,702       8,084    55.75
db file sequential read    3,936       3,994    27.56
log file sync            598,194         738     5.07
db file scattered read   106,062         660     4.56
log file parallel write  605,360         380     2.61 

————————–

3 (pages 28 and 405):

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                               % Total
Event                        Waits     Time (cs) Wt Time
--------------------------   --------  -------   --------
SQL*Net more data to client  1,957,467 4,737,686   99.77
db file sequential read        683,830     3,065     .07
db file parallel write           3,791     2,500     .05
rdbms ipc reply                     13     2,306     .04
db file scattered read           8,443     1,223     .02 

————————–

4 (page 105):

Top 5 Wait Events
~~~~~~~~~~~~~~~~~~                                  % Total
Event                          Waits     Time (cs)  Wt Time
--------------------------     --------  ---------  -------
enqueue                          51,802    959,308    46.70
db file scattered read       21,114,884    394,410    29.21
db file sequential read       1,448,650    393,166     9.13
latch free                    2,301,958    102,168     4.98
log file parallel write         297,864     79,644     3.87 

————————–

5 (page 107):

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                        % Total
Event                         Waits        Time (s)     Ela Time
--------------------------- ------------ ---------- ------------
db file scattered read           651,038      6,492        82.03
library cache load lock            9,346      2,726         9.27
db file sequential read        1,069,196     14,292         4.53
CPU time                           2,308      1,290         3.84
log file parallel write           38,314      1,674         1.67 

————————–

6 (page 158):

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                       % Total
Event                         Waits        Time (s)     Ela Time
--------------------------- ------------ ----------- -----------
CPU time                                     326,364       88.22
db file sequential read        3,085,708      17,102        4.63
log file sync                  3,648,938      16,804        4.53
log file parallel write        3,621,256       7,239        1.31
SQL*Net more data to client   30,842,404       1,314         .36 

————————–

7 (page 158):

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                       % Total
Event                         Waits        Time (s)      DB Time
--------------------------- ------------ ----------- -----------
log file parallel write           19,340         582       55.66
log file sync                     18,586         556       53.13
CPU time                                         450       43.11
db file parallel write             9,844         402       38.54
control file parallel write        2,564         130       12.41 

————————–

8 (page 324):

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                              % Total
Event                        Waits    Time (s) Ela Time
------------------------ --------- ----------- --------
db file sequential read  1,137,896       8,750    66.14
CPU time                                 3,966    30.00
db file scattered read     212,574         130      .98
log file sync               14,106         100      .76
log buffer space             3,434          94      .70 

————————–

9 (page 511):

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                      % Total
Event                          Waits   Ela Time
-------------------------- ---------   --------
db file sequential read        5,196      48.53
db file scattered read        51,038      22.05
library cache load lock        1,346       9.25
CPU time                          88       7.84
log file parallel write       38,314       5.67 

————————–

10 (page 512):

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                      % Total
Event                          Waits   Ela Time
-------------------------- ---------   --------
db file sequential read        5,196      48.53
db file scattered read        51,038      22.05
library cache load lock        1,346       9.25
CPU time                       4,308       7.84
log file parallel write       38,314       5.67 

————————–

11 (page 1002):

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                            % Total
Event                      Waits    Time (s) Ela Time
-------------------------------- ----------- --------
db file scattered read     5,196      14,292    58.53   
db file sequential read   51,038       6,492    12.05
library cache load lock    1,346       2,726     9.25
CPU time                               2,308     7.84
log file parallel write   38,314       1,674     5.67 

————————–

12 (AskTom):

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     % Total
Event                                               Waits    Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time                                                       19,612    35.04
latch free                                        899,688      14,371    25.68
db file scattered read                         16,333,411      13,105    23.42
SQL*Net message from dblink                       439,535       4,440     7.93
direct path write                                 405,226       1,509     2.70 

————————–

13 (AskTom):

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     % Total
Event                                               Waits    Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time                                                        2,446    75.27
log file sync                                      12,883         212     6.52
latch free                                         17,869         182     5.60
log file parallel write                            35,538         174     5.34
SQL*Net break/reset to client                       5,544          80     2.46 

————————–

14 (AskTom):

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     % Total
Event                                               Waits    Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time                                                        3,747    66.52
enqueue                                             2,129       1,350    23.97
db file sequential read                            37,842         286     5.07
log file sync                                       9,743          65     1.15
LGWR wait for redo copy                            58,067          61     1.08 

————————–

15 (AskTom):

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     % Total
Event                                               Waits    Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time                                                       19,612    35.04
latch free                                        899,688      14,371    25.68
db file scattered read                         16,333,411      13,105    23.42
SQL*Net message from dblink                       439,535       4,440     7.93
direct path write                                 405,226       1,509     2.70 

————————–

Confused?  Or did you do well in this quiz?





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:





Optimizer Costing 3 – What is Wrong with this Quote?

6 12 2010

December 6, 2010

(Back to the Previous Post in the Series) (Forward to the Next 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 922 of the book?

Try a rule hint!

For testing unnecessary large table full table scans, try a rule hint (select /*+ RULE */ col1).  If the query uses the index with a rule hint, you have an issue with the CBO.”

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:

Related Oracle Database documentation:





Optimizer Costing 2 – What is Wrong with this Quote?

6 12 2010

December 6, 2010

(Back to the Previous Post in the Series) (Forward to the Next 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 527 of the book (this is an atypically long quote – the context would be lost if either of the two paragraphs were removed):

“In general, the Oracle CBO is unaware of buffer details, except when the optimizer_index_caching parameter is set where using multiple data buffers will not impact SQL execution plans.  When data using the new cpu_cost reported metric, the Oracle SQL optimizer builds the SQL plan decision tree based on the execution plan that will have the lowest estimated CPU cost.  For example, if a 32k data buffer is implemented for the index tablespace, the DBA can ensure that the indexes are cached for optimal performance and minimal logical I/O in range scans.

For example, if a database has 50 gigabytes of index space, a 60-gigabyte db_32k_cache_size can be defined and then the optimizer_index_caching parameter can be set to 100, telling the SQL optimizer that all of the Oracle indexes reside in RAM.  When Oracle makes the index versus table scan decision, knowing that the index nodes are in RAM will greatly influence the optimizer because the CBO knows that a logical I/O is often 100 times faster than a physical read.”

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.

A Google book search indicates that a slightly modified version of the statement appeared in a couple of  other books:

Other pages found during a Google search of the phrase:

  • dba-oracle.com/oracle_tips_multiple_blocksizes.htm
  • rampant-books.com/t_oracle_improve_sql_execution_speed.htm

Related Oracle Database documentation:





Optimizer Costing 1 – What is Wrong with this Quote?

5 12 2010

December 5, 2010 (Modified December 6, 2010)

(Forward to the Next 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.  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

With that said, what is wrong with the following quote from page 498 of the book (you might need to view the page directly in the book to fully determine the context of the quote)?

“CPU Based Optimizer Costing

The recently added CPU costing feature, controlled by the _optimizer_cost_model = hidden parameter, enhances the CBO’s capabilities by allowing it to estimate the number of machine cycles necessary for an operation…  Generally, CPU costs are not considered significant unless the entire Oracle instance is using excessive CPU resources.

I/O Costing

… The I/O cost is proportional to the number of physical data blocks read by the operation.  However, the CBO has no prior information on the data buffer contents and cannot distinguish between a logical read (in-buffer) and a physical read.  Due to this shortcoming, the CBO cannot know if the data blocks are already in the RAM data buffers.  The best environment for using CPU costing is for all_rows execution plans, where cost is more noteworthy than with first_rows optimization.”

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.

A Google book search indicates that a slightly modified version of the statement appeared in one other book:

Other pages found during a Google search of the phrase:

  • rampant-books.com/t_oracle_cpu_costing.htm
  • dba-oracle.com/art_builder_cpu_io.htm
  • dba-oracle.com/art_otn_cbo_p6.htm
  • Using Explain Plan from the Oracle documentation library

I am reminded of a quote from page 7 of the book, quite literally the first sentence in the book:

“Oracle tuning is a complex endeavor, and it does not help that Oracle databases are changing constantly.”

Edit: December 6, 2010: Added a couple of more quoted sentences under the heading “CPU Based Optimizer Costing” to reduce the risk of misunderstanding the context of the quote.








Follow

Get every new post delivered to your Inbox.

Join 139 other followers