March | 2012 | Charles Hooper's Oracle Notes

Which PLAN_HASH_VALUE Appears in V$SQLAREA?

28 03 2012

March 28, 2012

A recent question on the OTN forums asked which PLAN_HASH_VALUE appears in V$SQLAREA when there are multiple child cursors for a single SQL_ID value, when some child cursors have a different execution plan. Certainly, this bit of information must be in the Oracle Database documentation. Let’s check the V$SQLAREA documentation for Oracle Database 11.2:

“Numeric representation of the SQL plan for this cursor. Comparing one PLAN_HASH_VALUE to another easily identifies whether or not two plans are the same (rather than comparing the two plans line by line).”

Well, that was not helpful, but it does remind me of something that I saw when I went out for a drive in the countryside this past weekend (all within about a 50 meter radius – click a picture to see a larger view of the picture):

OK, now that the initial frustration of not obtaining an answer from the documentation has subsided, let’s put together a quick test case to see if we are able to help the OP find an answer to his question. We will borrow a slightly modified version of a test script that generates skewed data which was used in another article:

CREATE TABLE T1 AS
SELECT
  ROWNUM C1,
  DECODE(ROWNUM,1,1,0) C2,
  LPAD('A',255,'A') C3
FROM
  DUAL
CONNECT BY
  LEVEL<=10000;

CREATE UNIQUE INDEX IND_T1_C1 ON T1(C1);
CREATE INDEX IND_T1_C2 ON T1(C2);

ALTER TABLE T1 MODIFY (C1 NOT NULL, C2 NOT NULL);

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE,ESTIMATE_PERCENT=>100,METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE 254')

For the initial test (in Oracle Database 11.2.0.2), I will use the BIND_AWARE hint to save Oracle from having to determine that the execution plan could (should) depend on the bind variable value, rather than having to rely on adaptive cursor sharing to eventually obtain the same effect:

SET LINESIZE 120
SET PAGESIZE 1000

VARIABLE V1 NUMBER
EXEC :V1:=1

SELECT /*+ BIND_AWARE */
  C1,
  C2,
  C3
FROM
  T1
WHERE
  C2=:V1;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));

SQL_ID  7p4yxrzwwuybt, child number 0
-------------------------------------
SELECT /*+ BIND_AWARE */   C1,   C2,   C3 FROM   T1 WHERE   C2=:V1

Plan hash value: 236868917

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |     1 |   136 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T1_C2 |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C2"=:V1)

SELECT
  PLAN_HASH_VALUE
FROM
  V$SQLAREA
WHERE
  SQL_ID='7p4yxrzwwuybt';

PLAN_HASH_VALUE
---------------
      236868917

As shown above, the PLAN_HASH_VALUE has a value of 236868917 in V$SQLAREA, which is the same value (shown in the execution plan) of the most recently executed child number.

Let’s repeat the previous SQL statements, this time with a different bind variable value:

EXEC :V1:=0

SELECT /*+ BIND_AWARE */
  C1,
  C2,
  C3
FROM
  T1
WHERE
  C2=:V1;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));

SQL_ID  7p4yxrzwwuybt, child number 1
-------------------------------------
SELECT /*+ BIND_AWARE */   C1,   C2,   C3 FROM   T1 WHERE   C2=:V1

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    33 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |  9999 |  1327K|    33   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("C2"=:V1)

SELECT
  PLAN_HASH_VALUE
FROM
  V$SQLAREA
WHERE
  SQL_ID='7p4yxrzwwuybt';

PLAN_HASH_VALUE
---------------
     3617692013

As shown above, the execution plan changed, thus the Plan hash value in the execution plan changed to 3617692013, and that change corresponded with the PLAN_HASH_VALUE for the SQL_ID in V$SQLAREA changing to the value 3617692013 - the same value shown in the execution plan for the most recently executed child number.

Let’s trying again without changing the bind variable value to see what happens:

SELECT /*+ BIND_AWARE */
  C1,
  C2,
  C3
FROM
  T1
WHERE
  C2=:V1;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));

SQL_ID  7p4yxrzwwuybt, child number 1
-------------------------------------
SELECT /*+ BIND_AWARE */   C1,   C2,   C3 FROM   T1 WHERE   C2=:V1

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    33 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |  9999 |  1327K|    33   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("C2"=:V1)

SELECT
  PLAN_HASH_VALUE
FROM
  V$SQLAREA
WHERE
  SQL_ID='7p4yxrzwwuybt';

PLAN_HASH_VALUE
---------------
     3617692013

As shown above, the PLAN_HASH_VALUE in V$SQLAREA remained at the value 3617692013, which is the PLAN_HASH_VALUE of the most recently executed child number.

Let’s switch back to the original bind variable value to see what happens in V$SQLAREA:

EXEC :V1:=1

SELECT /*+ BIND_AWARE */
  C1,
  C2,
  C3
FROM
  T1
WHERE
  C2=:V1;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));

SQL_ID  7p4yxrzwwuybt, child number 0
-------------------------------------
SELECT /*+ BIND_AWARE */   C1,   C2,   C3 FROM   T1 WHERE   C2=:V1

Plan hash value: 236868917

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |     1 |   136 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T1_C2 |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C2"=:V1)

SELECT
  PLAN_HASH_VALUE
FROM
  V$SQLAREA
WHERE
  SQL_ID='7p4yxrzwwuybt';

PLAN_HASH_VALUE
---------------
      236868917

As shown above, the PLAN_HASH_VALUE for the SQL_ID found in V$SQLAREA switched back to the original value – it is again showing the PLAN_HASH_VALUE of the most recently executed child cursor.

—

But wait, there’s more information. Pete Finnigan recently reminded me of a problem that I had several years ago when I tried to untangle the string of synonyms and views to see the definition of various Oracle Database performance views. He not only reminded me of the problem that I had years ago, but showed me the process of untangling the (evoke suitable picture from above) that I learned and forgot several times over the subsequent years.

Let’s get started by determining what the V$SQLAREA synonym points at:

SET LONG 9000
COLUMN TABLE_OWNER FORMAT A11

SELECT
  TABLE_OWNER,
  TABLE_NAME
FROM
  DBA_SYNONYMS
WHERE
  SYNONYM_NAME='V$SQLAREA';

TABLE_OWNER TABLE_NAME
----------- ----------
SYS         V_$SQLAREA

A viewed named V_$SQLAREA – now what? Let’s see the definition of that view:

SELECT
  TEXT
FROM
  DBA_VIEWS
WHERE
  VIEW_NAME='V_$SQLAREA';

select "SQL_TEXT","SQL_FULLTEXT","SQL_ID","SHARABLE_MEM","PERSISTENT_MEM","RUNTI
...
D_TOTAL","PINNED_TOTAL","IO_CELL_UNCOMPRESSED_BYTES","IO_CELL_OFFLOAD_RETURNED_B
YTES" from v$sqlarea

So, the synonym V$SQLAREA points to the view V_$SQLAREA which selects from V$SQLAREA … that name seems oddly familiar.

SELECT
  VIEW_DEFINITION
FROM
  V$FIXED_VIEW_DEFINITION
WHERE
  VIEW_NAME='V$SQLAREA';

select    SQL_TEXT,           SQL_FULLTEXT,           SQL_ID,           SHARABLE
_MEM,           PERSISTENT_MEM,           RUNTIME_MEM,           SORTS,
...
SICAL_WRITE_BYTES,            OPTIMIZED_PHY_READ_REQUESTS,            LOCKED_TOT
AL,             PINNED_TOTAL,            IO_CELL_UNCOMPRESSED_BYTES,
IO_CELL_OFFLOAD_RETURNED_BYTES from GV$SQLAREA where inst_id = USERENV('Instance
')

So, the view V_$SQLAREA selects from V$SQLAREA which selects from GV$SQLAREA.

SELECT
  VIEW_DEFINITION
FROM
  V$FIXED_VIEW_DEFINITION
WHERE
  VIEW_NAME='GV$SQLAREA';

select inst_id,kglnaobj,kglfnobj,kglobt03,kglobhs0+kglobhs1+kglobhs2+kglobhs3+kg
lobhs4+kglobhs5+kglobhs6,kglobt08+kglobt11,kglobt10,kglobt01,kglobccc,kglobclc,k
...
t58,kglobt23,kglobt24,kglobt59,kglobt53 - ((kglobt55+kglobt57) - kglobt52)from
x$kglcursor_child_sqlid where kglobt02 != 0

So, the view V_$SQLAREA selects from V$SQLAREA which selects from GV$SQLAREA which selects from X$KGLCURSOR_CHILD_SQLID where KGLOBT02 != 0 (COMMAND_TYPE column in V$SQLAREA). (Note, must be logged in as the SYS user for the following SQL statement.)

SELECT
  KQFDTNAM,
  KQFDTEQU
FROM
  X$KQFDT
ORDER BY
  KQFDTNAM;

KQFDTNAM                       KQFDTEQU
----------------------------- -------------
...
X$KGLBODY                      X$KGLOB
X$KGLCLUSTER                   X$KGLOB
X$KGLCURSOR                    X$KGLOB
X$KGLCURSOR_CHILD              X$KGLOB
X$KGLCURSOR_CHILD_SQLID        X$KGLOB
X$KGLCURSOR_CHILD_SQLIDPH      X$KGLOB
X$KGLINDEX                     X$KGLOB
X$KGLTABLE                     X$KGLOB
X$KGLTRIGGER                   X$KGLOB 
...

So, in summary the synonym V$SQLAREA points to the view V_$SQLAREA which selects from V$SQLAREA which selects from GV$SQLAREA which selects from X$KGLCURSOR_CHILD_SQLID where KGLOBT02 != 0, which has as a base table of X$KGLOB just like 8 other fixed tables. If you repeat the above steps for V$SQL, you will find that it is based on X$KGLCURSOR_CHILD, which also has X$KGLOB as its base table.

Here is a piece of SQL that joins the underlying fixed table for V$SQLAREA with V$SQL to hopefully determine if the PLAN_HASH_VALUE for the most recently executed child cursor for each SQL_ID is always what appears in V$SQLAREA (I do not suggest running this SQL statement in a production environment – the test database instance in my case was bounced a couple of hours ago). The word DIFFERENT is output if the LAST_ACTIVE_TIME from V$SQLAREA does NOT match the LAST_ACTIVE_TIME for a specific child in V$SQL (Note, must be logged in as the SYS user for the following SQL statement.):

SELECT
  X.KGLOBT03 SQL_ID,
  X.KGLOBT30 PLAN_HASH_VALUE,
  TO_CHAR(X.KGLOBCLA,'HH24:MI:SS') LAST_ACTIVE_TIME,
  S.CHILD_NUMBER,
  S.PLAN_HASH_VALUE S_PLAN_HASH_VALUE,
  TO_CHAR(S.LAST_ACTIVE_TIME,'HH24:MI:SS') S_LAST_ACTIVE_TIME,
  DECODE(X.KGLOBCLA,S.LAST_ACTIVE_TIME,NULL,'DIFFERENT') TIMESTAMP_DIFFERS
FROM
  X$KGLCURSOR_CHILD_SQLID X,
  V$SQL S
WHERE
  X.KGLOBT02 != 0
  AND X.KGLOBT03=S.SQL_ID
ORDER BY
  S.SQL_ID,
  S.CHILD_NUMBER;

SQL_ID        PLAN_HASH_VALUE LAST_ACT CHILD_NUMBER S_PLAN_HASH_VALUE S_LAST_A TIMESTAMP
------------- --------------- -------- ------------ ----------------- -------- ---------
00fx7adv5q5gm      2256887934 22:03:44            0        2256887934 22:03:44
00yp7w9j0yqma      3600061239 20:28:45            0        3600061239 20:28:45
...
07pcqtmt58zv9      1964643588 20:44:48            0        1964643588 20:28:45 DIFFERENT
07pcqtmt58zv9      1964643588 20:44:48            1        1964643588 20:44:48
...
0fr8zhn4ymu3v      1231101765 21:03:43            0        3815847153 20:28:45 DIFFERENT
0fr8zhn4ymu3v      1231101765 21:03:43            1        1231101765 21:03:43
...
0kugqg48477gf       643665366 21:03:43            0         828554155 20:28:45 DIFFERENT
0kugqg48477gf       643665366 21:03:43            1         643665366 21:03:43
...
0m78skf1mudnb      3506511888 20:28:47            0        3506511888 20:28:45 DIFFERENT
0m78skf1mudnb      3506511888 20:28:47            1        3506511888 20:28:47
...
15w1t8qpdgg5k      2628186673 21:33:45            0        2628186673 20:28:45 DIFFERENT
15w1t8qpdgg5k      2628186673 21:33:45            1        2628186673 21:33:45
...
1gu8t96d0bdmu      2035254952 21:03:43            0        2035254952 21:03:43
1gu8t96d0bdmu      2035254952 21:03:43            1        3526770254 20:28:45 DIFFERENT
1gu8t96d0bdmu      2035254952 21:03:43            2        2035254952 20:48:45 DIFFERENT
...
2jr8c42qx700h      2445831428 20:28:55            0        3034582372 20:28:45 DIFFERENT
2jr8c42qx700h      2445831428 20:28:55            1        2445831428 20:28:45 DIFFERENT
2jr8c42qx700h      2445831428 20:28:55            2        2445831428 20:28:55
...
2q93zsrvbdw48      2874733959 21:03:43            0        2874733959 21:03:43
2q93zsrvbdw48      2874733959 21:03:43            1        2874733959 20:28:45 DIFFERENT
2q93zsrvbdw48      2874733959 21:03:43            2        2874733959 20:48:45 DIFFERENT
2qqqjzkhmsbgv      1308273333 20:28:45            0        1308273333 20:28:45
2syvqzbxp4k9z      1423211129 20:28:47            0        1423211129 20:28:45 DIFFERENT
2syvqzbxp4k9z      1423211129 20:28:47            1        1423211129 20:28:47
2tkw12w5k68vd      1457651150 21:03:43            0        1457651150 20:28:45 DIFFERENT
2tkw12w5k68vd      1457651150 21:03:43            1        1457651150 21:03:43
2xgubd6ayhyb1      3418045132 21:03:43            0        3418045132 21:03:43
2xyb5d6xg9srh       785096182 20:28:47            0         785096182 20:28:45 DIFFERENT
2xyb5d6xg9srh       785096182 20:28:47            1         785096182 20:28:47
2ysccdanw72pv      3801013801 20:28:55            0        3801013801 20:28:55
32bhha21dkv0v      3765558045 21:03:43            0        3765558045 20:28:45 DIFFERENT
32bhha21dkv0v      3765558045 21:03:43            1        3765558045 21:03:43
...
38fffx897xs0v      1042772069 21:33:45            0        1042772069 20:28:45 DIFFERENT
38fffx897xs0v      1042772069 21:33:45            1        1042772069 21:33:45
...
39m4sx9k63ba2      2317816222 21:03:43            0        2317816222 20:28:45 DIFFERENT
39m4sx9k63ba2      2317816222 21:03:43            1        2317816222 21:03:43
...
3k0c6241uw582      1964643588 20:44:48            0        1964643588 20:28:45 DIFFERENT
3k0c6241uw582      1964643588 20:44:48            1        1964643588 20:44:48
3ktacv9r56b51      4184428695 21:03:43            0        4184428695 20:28:45 DIFFERENT
3ktacv9r56b51      4184428695 21:03:43            1        4184428695 20:28:45 DIFFERENT
3ktacv9r56b51      4184428695 21:03:43            2        4184428695 21:03:43
3nhfzxjzx2btx      1043815174 20:28:45            0        1043815174 20:28:45
3nkd3g3ju5ph1      2853959010 21:03:43            0        2853959010 21:03:43
3nkd3g3ju5ph1      2853959010 21:03:43            1        2853959010 20:28:45 DIFFERENT
3nkd3g3ju5ph1      2853959010 21:03:43            2        2853959010 20:48:45 DIFFERENT
3np8cptn6uzn6      1754305749 20:28:46            0        1754305749 20:28:46
3nzv2smdzzbsf      2731876963 21:33:45            0        2731876963 20:28:45 DIFFERENT
3nzv2smdzzbsf      2731876963 21:33:45            1        2731876963 21:33:45
...
3rw49yhahg984      3808094885 20:28:55            0        3808094885 20:28:45 DIFFERENT
3rw49yhahg984      3808094885 20:28:55            1        3808094885 20:28:55
...
3w4qs0tbpmxr6      1224215794 21:03:43            0        1224215794 21:03:43
3w4qs0tbpmxr6      1224215794 21:03:43            1        1224215794 20:28:45 DIFFERENT
3w4qs0tbpmxr6      1224215794 21:03:43            2        1224215794 20:48:45 DIFFERENT
...
459f3z9u4fb3u       415205717 20:44:48            0         415205717 20:28:45 DIFFERENT
459f3z9u4fb3u       415205717 20:44:48            1         415205717 20:44:48
...
4cvqvs489pd6k      2300756036 20:28:55            0        2300756036 20:28:45 DIFFERENT
4cvqvs489pd6k      2300756036 20:28:55            1        2300756036 20:28:55
...
4zzxr8rvht74z      3368685730 20:44:48            0        3368685730 20:28:45 DIFFERENT
4zzxr8rvht74z      3368685730 20:44:48            1        3368685730 20:44:48
...
53saa2zkr6wc3      3954488388 21:03:43            0        3954488388 21:03:43
53saa2zkr6wc3      3954488388 21:03:43            1        1514015273 20:28:45 DIFFERENT
53saa2zkr6wc3      3954488388 21:03:43            2        3954488388 20:33:45 DIFFERENT
...
5n1fs4m2n2y0r       299250003 21:03:43            0         299250003 20:28:45 DIFFERENT
5n1fs4m2n2y0r       299250003 21:03:43            1         299250003 21:03:43
5n1fs4m2n2y0r       299250003 21:03:43            2         299250003 20:48:45 DIFFERENT
...
5wxyshspv54v4      3009292138 20:44:48            0        3009292138 20:28:45 DIFFERENT
5wxyshspv54v4      3009292138 20:44:48            1        3009292138 20:44:48
...
6aq34nj2zb2n7      2874733959 21:03:43            0        2874733959 21:03:43
6aq34nj2zb2n7      2874733959 21:03:43            1        2874733959 20:28:45 DIFFERENT
6aq34nj2zb2n7      2874733959 21:03:43            2        2874733959 20:48:45 DIFFERENT
6b7hj70p170j1      2605232930 20:28:45            0        2605232930 20:28:45
6c9wx6z8w9qpu       785096182 20:28:47            0         785096182 20:28:45 DIFFERENT
6c9wx6z8w9qpu       785096182 20:28:47            1         785096182 20:28:47
...
6qz82dptj0qr7      2819763574 21:03:43            0        2819763574 20:28:45 DIFFERENT
6qz82dptj0qr7      2819763574 21:03:43            1        2819763574 21:03:43
...
79w2cqu2gmjm8      4145101951 20:28:55            0        2645993454 20:28:45 DIFFERENT
79w2cqu2gmjm8      4145101951 20:28:55            1        4145101951 20:28:45 DIFFERENT
79w2cqu2gmjm8      4145101951 20:28:55            2        4145101951 20:28:55
7akvnu9t168d3      1964643588 20:44:48            0        1964643588 20:28:45 DIFFERENT
7akvnu9t168d3      1964643588 20:44:48            1        1964643588 20:44:48
...
7jpt4cpfvcy1k       284504113 20:28:46            0         284504113 20:28:45 DIFFERENT
7jpt4cpfvcy1k       284504113 20:28:46            1         284504113 20:28:46
7mgr3uwydqq8j       293268181 22:13:45            0         293268181 22:13:45
7ng34ruy5awxq      3992920156 21:03:43            0        3992920156 21:03:43
7ng34ruy5awxq      3992920156 21:03:43            1         306576078 20:28:45 DIFFERENT
7ng34ruy5awxq      3992920156 21:03:43            2        3992920156 20:48:45 DIFFERENT
7nuw4xwrnuwxq      1720483994 21:03:43            0        1720483994 20:28:45 DIFFERENT
7nuw4xwrnuwxq      1720483994 21:03:43            1        1720483994 21:03:43
...
83taa7kaw59c1      3765558045 21:03:43            0        3765558045 21:03:43
83taa7kaw59c1      3765558045 21:03:43            1        3765558045 20:28:45 DIFFERENT
83taa7kaw59c1      3765558045 21:03:43            2        3765558045 20:48:45 DIFFERENT
85sxp7kypwbx6      1395584798 20:28:46            0        1395584798 20:28:46
87gaftwrm2h68      1218588913 21:03:43            0        1218588913 20:28:45 DIFFERENT
87gaftwrm2h68      1218588913 21:03:43            1        1218588913 21:03:43
87gaftwrm2h68      1218588913 21:03:43            2        1218588913 20:48:45 DIFFERENT
...
8swypbbr0m372       893970548 21:03:43            0         893970548 20:28:45 DIFFERENT
8swypbbr0m372       893970548 21:03:43            1         893970548 20:28:45 DIFFERENT
8swypbbr0m372       893970548 21:03:43            2         893970548 21:03:43
...
9g485acn2n30m      2544153582 21:03:43            0        2544153582 20:28:45 DIFFERENT
9g485acn2n30m      2544153582 21:03:43            1        2544153582 21:03:43
9gkq7rruycsjp      3362549386 20:28:46            0        3362549386 20:28:45 DIFFERENT
9gkq7rruycsjp      3362549386 20:28:46            1        3362549386 20:28:46
...
9rfqm06xmuwu0       832500465 21:03:43            0         832500465 20:28:45 DIFFERENT
9rfqm06xmuwu0       832500465 21:03:43            1         832500465 21:03:43
9rvqpun1x1xjd      2760275752 20:28:46            0        2760275752 20:28:46
9tgj4g8y4rwy8      3755742892 21:03:43            0        3755742892 20:28:45 DIFFERENT
9tgj4g8y4rwy8      3755742892 21:03:43            1        3755742892 21:03:43
...
axmdf8vq7k1rh      2203911306 20:44:48            0        2203911306 20:28:45 DIFFERENT
axmdf8vq7k1rh      2203911306 20:44:48            1        2203911306 20:44:48
...
b1wc53ddd6h3p      1637390370 21:03:43            0        1637390370 20:28:45 DIFFERENT
b1wc53ddd6h3p      1637390370 21:03:43            1        1637390370 21:03:43
...
bsa0wjtftg3uw      1512486435 22:23:19            0        2020579421 20:28:45 DIFFERENT
bsa0wjtftg3uw      1512486435 22:23:19            1        1512486435 22:23:19
bsa0wjtftg3uw      1512486435 22:23:19            2        1512486435 20:48:45 DIFFERENT
...
c4nhd1ntptxq7      3477319146 20:28:46            0        3477319146 20:28:46
c4nhd1ntptxq7      3477319146 20:28:46            1        3477319146 20:28:46
...
cb21bacyh3c7d      3452538079 21:03:43            0        3452538079 20:28:45 DIFFERENT
cb21bacyh3c7d      3452538079 21:03:43            1        3452538079 20:28:46 DIFFERENT
cb21bacyh3c7d      3452538079 21:03:43            2        3452538079 21:03:43
...
cjk1ffy5kmm5s      1964104430 20:28:46            0        1964104430 20:28:45 DIFFERENT
cjk1ffy5kmm5s      1964104430 20:28:46            1        1964104430 20:28:46
...
cvn54b7yz0s8u      3246118364 21:03:43            0        3246118364 20:28:45 DIFFERENT
cvn54b7yz0s8u      3246118364 21:03:43            1        3246118364 21:03:43
d00a21h5ybffr       959325123 20:44:48            0        2829621105 20:28:45 DIFFERENT
d00a21h5ybffr       959325123 20:44:48            1         959325123 20:44:48
...
f3g84j69n0tjh      2335623859 21:03:43            0         914792125 20:28:45 DIFFERENT
f3g84j69n0tjh      2335623859 21:03:43            1        2335623859 21:03:43
...
fzrshwabvtwc0      3637245398 22:23:45            0        3637245398 20:28:45 DIFFERENT
fzrshwabvtwc0      3637245398 22:23:45            1        3637245398 22:23:45
...
g3wrkmxkxzhf2       749386351 21:03:43            0         749386351 20:28:45 DIFFERENT
g3wrkmxkxzhf2       749386351 21:03:43            1         749386351 21:03:43
...
g7smmy8ybh3gv        43085360 20:28:55            0          43085360 20:28:46 DIFFERENT
g7smmy8ybh3gv        43085360 20:28:55            1          43085360 20:28:55
...
ga9j9xk5cy9s0      1697022209 21:03:43            0        1697022209 20:28:45 DIFFERENT
ga9j9xk5cy9s0      1697022209 21:03:43            1        1697022209 21:03:43
...
grwydz59pu6mc      3684871272 21:03:43            0        3684871272 20:28:45 DIFFERENT
grwydz59pu6mc      3684871272 21:03:43            1        3684871272 21:03:43
...
gx4mv66pvj3xz      1932954096 21:03:43            0        1932954096 21:03:43
gx4mv66pvj3xz      1932954096 21:03:43            1        2570921597 20:28:45 DIFFERENT
gx4mv66pvj3xz      1932954096 21:03:43            2        1932954096 20:48:45 DIFFERENT
...

I feel that I need some more suitable pictures now. :-)

Comments : 10 Comments »

Categories : Performance

Monitoring Changes to Table Data

22 03 2012

March 22, 2012

Suppose that you receive a request stating that a particular table in one of your databases must be monitored for changes. One such table might be the one that lists the ERP system’s suppliers and their addresses – you would not want someone to be able to set up a legitimate supplier, and later have the billing remit to name and address changed without being detected.

What approach would you take to solve the above problem? A question similar to the above arrived recently in an ERP mailing list email - the original poster (OP) is using a SQL Server RDBMS, so that might change the proposed solution just a bit.

Here is the “How simple is too simple?” suggestion that I offered:

Periodically, create a VENDOR_SAVE table (drop it if it already exists, or delete all rows and re-insert from the original source table):
CREATE TABLE VENDOR_SAVE AS
SELECT
  *
FROM
  VENDOR;
Now, just wait a while.

The question then becomes, how do we detect:

A new row (record) added to the original source (VENDOR) table.

An old row (record) deleted from the original source (VENDOR) table.

A change to any column (program field) in the original source (VENDOR) table since the last time the VENDOR_SAVE table was created/refreshed.

Let’s start with the first two bullet points. I will write the SQL statements so that the statements should work with Oracle Database 9.0.1 and above, and SQL Server with very few changes, even if I feel a bit ANSI about doing so. I will use the COALESCE function, rather than the NVL function, and CASE syntax rather than the equivalent DECODE syntax.

To identify cases where a row has appeared in, or disappeared from the original source (VENDOR) table, we can simply perform a full outer join between the original source table and the historical mirror image of the original table (VENDOR_SAVE). We are only interested in cases where the primary key column (ID) is found in exactly one of the two tables:
SELECT
  COALESCE(V.ID,V2.ID) AS ID,
  COALESCE(V.NAME,V2.NAME) AS NAME,
  COALESCE(V.ADDR_1,V2.ADDR_1) AS ADDR_1,
  CASE WHEN V.ID IS NULL THEN 'VENDOR ADDED' ELSE 'VENDOR DELETED' END AS PROBLEM
FROM
  VENDOR V
FULL OUTER JOIN
  VENDOR_SAVE V2
ON (V.ID=V2.ID)
WHERE
  V.ID IS NULL
  OR V2.ID IS NULL;
So, the above SQL statement satisfies the first two bullet points. The third bullet point is a little more challenging to accomplish… unless of course we employ UNION labor. If we have two row sources with identical columns, and UNION the row sources together, the resulting row source will be absent of any entirely duplicated rows from the two original row sources (two rows will be reduced to a single row). If there were no changes to any of the column values (or if the row was added to or deleted from the original source table), there will be a single row for the primary key column value. If any columns were changed, there will be two rows containing the primary key column value.

Let’s build a SQL statement that UNIONs the rows from the two tables together, and counts the number of rows for each primary key value:
SELECT
  COUNT(*) OVER (PARTITION BY VL.ID) AS CNT,
  VL.ID,
  VL.NAME,
  VL.ADDR_1
FROM
  (SELECT
    *
  FROM
    VENDOR
  UNION
  SELECT
    *
  FROM
    VENDOR_SAVE) VL;
To complete the requirement for bullet point 3 above, we need to eliminate all rows from the result set where there is a single row for the primary key value:
SELECT DISTINCT
  VL2.ID,
  VL2.NAME,
  VL2.ADDR_1,
  'VALUE CHANGE' PROBLEM
FROM
  (SELECT
    COUNT(*) OVER (PARTITION BY VL.ID) AS CNT,
    VL.ID,
    VL.NAME,
    VL.ADDR_1
  FROM
    (SELECT
      *
    FROM
      VENDOR
    UNION
    SELECT
      *
    FROM
      VENDOR_SAVE) VL) VL2
WHERE
  VL2.CNT>1;
As a final step, we should join the two resultsets into a single resultset using UNION ALL:
SELECT
  COALESCE(V.ID,V2.ID) AS ID,
  COALESCE(V.NAME,V2.NAME) AS NAME,
  COALESCE(V.ADDR_1,V2.ADDR_1) AS ADDR_1,
  CASE WHEN V.ID IS NULL THEN 'VENDOR ADDED' ELSE 'VENDOR DELETED' END AS PROBLEM
FROM
  VENDOR V
FULL OUTER JOIN
  VENDOR_SAVE V2
ON (V.ID=V2.ID)
WHERE
  V.ID IS NULL
  OR V2.ID IS NULL
UNION ALL
SELECT DISTINCT
  VL2.ID,
  VL2.NAME,
  VL2.ADDR_1,
  'VALUE CHANGE' PROBLEM
FROM
  (SELECT
    COUNT(*) OVER (PARTITION BY VL.ID) AS CNT,
    VL.ID,
    VL.NAME,
    VL.ADDR_1
  FROM
    (SELECT
      *
    FROM
      VENDOR
    UNION
    SELECT
      *
    FROM
      VENDOR_SAVE) VL) VL2
WHERE
  VL2.CNT>1
ORDER BY
  ID;

While somewhat limited in usefulness, the above approach will indicate which rows in the original source table should be examined because the column values in those rows changed (or were added or deleted).

Another, potentially more useful approach involves setting up a logging trigger and logging table. I previously shared a VBS script that helps to automate and standardize the process of creating the logging trigger and logging table. A very similar feature is built into my Hyper-Extended Oracle Performance Monitor program – but, much like the VBS script, this solution is useless for the OP who uses a SQL Server backend.

What are the other options? Oracle Database’s built-in auditing capabilities. Auditing built into the ERP system (this particular ERP system writes all audit/change records to a single table that uses a VARCHAR2 column to capture the before/after images of the values stored in columns). Any other options? (The mess that someone can create with a bit of idle time on their hands…)

Comments : 25 Comments »

Categories : General Administration, SQL

Hyper-Extended Oracle Performance Monitor 6.0 Beta

15 03 2012

March 15, 2012 (Modified March 16, 2012)

Several people expressed an interest in using the Beta version of my Hyper-Extended Oracle Performance Monitor 6.0 that has been under development for about a decade. Rather than trying to find a way to deliver the Beta version of the program to those people who left comments in the earlier thread, it seemed to be much easier to just post the Beta version to this blog.

The Hyper-Extended Oracle Performance Monitor tool runs from a Windows client PC (XP with ADO 2.8+ installed, Vista, Windows 7 32/64 bit, Server 2003, Server 2008) and for some tasks, such as report generation, requires Microsoft Excel (2000, XP, 2003, 2007, or 2010) to be present on the PC. Everything that is logged is written to a C:\OracleLog folder on the client computer, and unfortunately that likely means that User Access Control (UAC) in Vista and Windows 7 will either need to be turned down or turned off completely (UAC will prevent programs from writing in folders that are located directly in the root of the C:\ drive). It is important to make certain that the Windows interfaces (all except MTS) are installed with the Oracle Client software, which should add the OraOLEDB functionality that is used by the program for connectivity to the databases.

An unfortunate side effect of using OraOLEDB functionality rather than ODBC is that the SYS user is not able to log in AS SYSDBA for certain tasks such as accessing the X$ structures (specifically X$BH, and the X$ structures (X$KSPPI, X$KSPPSV) needed for viewing the hidden initialization parameters). Setting the O7_DICTIONARY_ACCESSIBILITY initialization parameter to TRUE will allow the program to connect as the SYS user (without AS SYSDBA), but doing so may represent a security risk for the database.

The program writes nothing to the Oracle database that is monitored, although it might try to automatically adjust the MAX_DUMP_FILE_SIZE parameter if the user attempts to enable a 10046 trace using the program, and the program determines that the MAX_DUMP_FILE_SIZE parameter is set far too small. The user that logs into the program will need proper permissions to access the various V$ views (GV$ views are not accessed) and also access the various packages that enable 10046/10053 traces (enabling a 10046 (or other trace) within the program’s interface requires that the user logging into the program have EXECUTE permission on the DBMS_SYSTEM and DBMS_MONITOR packages).

The 10046 trace file parser is still a bit stuck in the land of Oracle Database 8.1 – it still expects to find p1, p2, and p3 on WAIT event lines if the Table and Object Lookup option is selected for trace file parsing (and for certain wait event analysis). Later versions of Oracle Database emit obj parameters on the WAIT lines, and the program should use the obj value rather than trying to look up the OBJECT_ID value using the p1, p2, and p3 parameters. The 10046 trace file parser performs a trick to handle the extremely long cursor numbers found in Oracle Database 11.2.0.2 and later. The Hyper-Extended Oracle Performance Monitor is intended to work fully on Oracle Database 10.2, and (hopefully) gracefully degrade when an older version of Oracle Database is encountered.

The program supports several command line parameters, most of which are used to configure performance logging capabilities:

-D      The Database instance SID to which the program should connect.
-U      The user name to be used for connecting to the database instance.
-P      The password to be used for connecting to the database instance.

-LC 20  Specifies Force a Log Capture when CPU Usage Exceeds value to 20%
-LI 30  Specifies Force a Log Capture if No Log Captured in Minutes value to 30 minutes
-LB     Specifies the Force a Log Capture when a Blocking Lock is Detected value to checked
-LW     Specifies the Force a Log Capture when a Wait Reason is Detected value to checked
-LR     Specifies the Capture SQL Execution Statistics for Wait Reasons value to checked
-LD     Specifies the Capture Segment Change Statistics value to checked
-LO     Specifies the Capture Operating System and Time Model Statistics value to checked
-LH     Specifies the Capture High Load SQL Statement Statistics value to checked
-LT     Specifies the Capture High Load SQL Statement Text value to checked
-LP     Specifies the Capture High Load SQL Statement Plan value to checked
-LHC 60 Species the minimum CPU time that is considered high load to 60 seconds accum.
-LHE 90 Species the minimum elapsed time that is considered high load to 90 seconds accum.
-LS     Specifies that Smart Logging should begin as soon as the login completes
-LE 240 Specifies that Smart Logging should end after 240 minutes
-LQ     Specifies that the program should quit (end) when logging ends

Important: Keep in mind that there is an overhead, primarily server CPU utilization, associated with performance monitoring. This overhead will be greatest when the program’s performance logging feature is utilized. This overhead, while typically minor, might negatively impact the performance of other database sessions. Under no circumstances should this program run directly on a Windows-based Oracle Database server’s console - doing so with performance logging enabled will significantly impact the performance of other database sessions.

This program does not phone home, nor does it collect any information that is not found in the C:\OracleLog folder on the client computer. The C:\OracleLog folder could prove to provide additional information that is not presented directly in the program interface. For example, when real-time performance is monitored, every 30 minutes the program will write one or more text files into the C:\OracleLog folder that show a crosstab style report of statistics and wait events (open the file with Microsoft Excel to aid readability). The performance logging feature creates a Microsoft Access compatible database (named to correspond to the logging date and time) in the C:\OracleLog folder – various information, such as in-effect initialization parameters, are written in that Access database, even though that information is not displayed in the program’s user interface.

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

DOWNLOAD:

The documentation for the program is at least four years out of date. You may download the program instructions for the Hyper-Extended Oracle Performance Monitor 3.0 Beta here: Hyper-ExtendedOraclePerformanceMonitor3Docs

The Beta version of the program is time limited, however it should continue functioning for the next 12 months. You may download the program by right clicking the file and saving it as “Hyper-ExtendedOraclePerformanceMonitor6.zip” (the .zip extension must be specified): Hyper-ExtendedOraclePerformanceMonitor6.zip

The program is compressed using WinZip - Windows XP and later are able to directly open .zip files. To install the program, simply extract the two files into the same folder; to uninstall, delete that folder and the C:\OracleLog folder.

If you find the program useful, feel free to leave a comment here. If you find that this program is the biggest waste of a decade’s worth of free time, I would be happy to hear that too. The program has a couple of known bugs – I know that they exist, but I do not know where they are in the program, nor do I yet know what the bugs affect.

Added March 16, 2012:

I thought that I would show a couple of screen captures from my program that are not necessarily performance tuning specific.

The Advanced Initialization Parameters Viewer (currently only works if the O7_DICTIONARY_ACCESSIBILITY initialization parameter is set to TRUE , but I am considering a couple of work around methods – note that the program’s description of the CURSOR_SHARING parameter does not yet mention that the SIMILAR value for the CURSOR_SHARING parameter is deprecated):

Keyword Search Viewer:

Lock/Wait Monitor:

Configure Data Change Log (showing one of the logging tables that was created by the program’s script generator):

DBMS_XPLAN and Trace:

Comments : 5 Comments »

Categories : Performance

Thoughts on a Hyper-Extended Oracle Performance Monitor Beta

12 03 2012

March 12, 2012

As long time readers of this blog might know, in my free time during roughly the last 10 years I have been working on a program named “Hyper-Extended Oracle Performance Monitor”. Since 2005 or 2006 I have permitted a few people to try the beta versions of the program, thinking that I might obtain a bit of feedback about what works well, and what needs a lot of work. I was recently informed of a couple of situations where one or two features in the program were extremely useful – I would much rather hear that kind of feedback, rather than “I forgot about that program.” :-)

What started as a simple 10046 trace file parser, easy method to execute a handful of scripts, and a V$ performance view logger has certainly grown over the years. I have not updated the documentation for the program in almost four years, and some suggestions offered by the program seem to be Oracle Database 8.1 specific… one of these days I might have some time to address those issues.

Over the last couple of days I found a couple of unplanned features (bugs) in the program – some of those features have been in the program for a couple of years, others were added just last week. I am currently debating whether or not to open up the beta of the program to a wider audience. Are any readers of this blog interested?

The main screen in the program probably looks unlike any program that you have seen in the past – menus, who needs menus:

If you drag and drop an Oracle 10046 trace file on the picture in the main screen, you will see a daunting list of options:

If you have followed along with the six part series on building an Oracle Database Time Model Viewer, you might recognize this screen in my program:

One of the original purposes of the program was to log the various statistics found in certain V$ performance views. Over the years I added additional information that the program is able to optionally capture, and set up the logging capabilities so that certain events will force more frequent logging of statistics:

With logging enabled, statistics are written to an dynamically created Microsoft Access compatible database, and as the statistics are captured, a summary of the statistics is written to the main program window:

Once you have logged something interesting, you can go back and review the information using a variety of interfaces in the program (or just stare blankly at the Microsoft Access database that was created). Among other things, the below screen capture shows that one session spent roughly 24 seconds of the roughly 60 second time period in the wait event enq: TX – row lock contention.

We can easily take a look at the system level wait events and statistics for this time period:

Or drill-down to the session level waits and statistics from the table at the bottom of the Review Time Model Statistics window. There is the session and its wait event, but what caused the wait event?

Maybe we should investigate… there’s a button for that. Blocker and Blocked near the bottom left of the window – I wonder if that is a clue?

Let’s double-click one of those rows to see what happens:

Nice start, but let’s ask for more information by clicking Yes.

We now have the SQL statement the blocked session was attempting to execute, and possibly the SQL statement that the blocker executed which caused the enq: TX – row lock contention wait event (the SQL statement is actually the most recent SQL statement executed by the blocker in the time period).

We are also able to take a quick tour of some of the SQL statements executed in the capture period and an extended version of the execution plans for those SQL statements:

There are another eight year’s worth of development in the program…

Comments : 14 Comments »

Categories : Performance

Book Review: Oracle Database 11gR2 Performance Tuning Cookbook (Part 1)

3 03 2012

March 3, 2012

Oracle Database Performance Tuning Test Cases without Many “Why”, “When”, and “How Much” Filler Details
http://www.amazon.com/Oracle-Database-Performance-Tuning-Cookbook/dp/1849682607

(Forward to the Next Post in the Series)

I ordered the “Oracle Database 11gR2 Performance Cookbook” book shortly after it became available for purchase. I was very curious to see how the book compared with the similarly titled “Oracle Database 11g Performance Tuning Recipes” book, as well as some of the other Oracle Database performance books that are on the market. Packt is a fairly new book publisher, and this book marks the first Packt book in my collection.

The author of this book does not appear to be widely known in the international Oracle Database community, although it does appear that the author is an active reviewer of SQL Server and programming books on an Italian programming focused website. The author’s LinkedIn page indicates that he obtained OCA and OCP certification in 2002 and 2003, respectively, has a variety of programming experience, and currently is an IT Manager.

One important characteristic of this book that is missing from some of the other Oracle Database performance focused books on the market is the extensive use of test case scripts throughout most of the book that allow the reader to reproduce the performance changes mentioned in the book, in the reader’s Oracle Database environments. The test case scripts, related screen captures, and author’s explanations of the results are both a blessing and a curse for this book. It appears that the author used a single Amazon Elastic Compute Cloud hosted database instance with only one set of instance parameters and system statistics for the various test case results and the author’s descriptions of the expected outcome when the inputs in the test case script are provided. Had the author re-executed the test case scripts in another Oracle Database environment, the author probably would have written quite differently the explanations that follow the test case scripts. It is not uncommon for 80% of some of the book pages to be consumed by one or two SQL*Plus screen captures; combined with the slightly larger font sizes, double-spacing between paragraphs, and apparent one and a half spacing between lines in code sections, the technical content in the book is a bit more limited than the page count might suggest.

So, how well did the book’s contents meet the level of expectations provided by the book’s front cover and the publisher’s description of the book? One of the bullet pointed descriptions of the book reads, “Avoid common myths and pitfalls that slow down the database.” Unfortunately, the book reintroduces several myths and inaccurate conclusions about Oracle Database that have diminished in frequency during the last 10+ years. Some of the information in the book is of good quality. However, the significant number of inaccurate, vague, misleading, and/or over-generalized facts in this book suggests that the author of this book may have not received sufficient guidance from Packt and the four technical reviewers of the book. The book publisher’s site currently lists no errata for the book, even though I personally submitted 21 errata items to the publisher’s errata reporting site.

The author’s native language is obviously not English, so it is probably to be expected that some of the sentences in the book are incomprehensible. Yet, there are also sentences in the book that use completely different phrasing, close to that of a person who double-majored in English and computer science with a focus on Oracle Database. The consistent usage of the term “fields” in some sections of the book, with the consistent usage of the term “columns” in other sections of the book is but one example of the style shift that is present in the book. Some of the sentences found in the book are oddly familiar, and although I was not able to identify the original sources of all of the oddly familiar sentences, I did manage to locate a few. What constitutes plagiarism in an Oracle Database book, and how much change is required to the original material to avoid the plagiarism label? Would slightly reformatting a section of text to replace dashes with colons be sufficient to avoid the label? Would changing the order of some sentences and eliminating other sentences be sufficient to avoid the label? Would performing simple word substitutions here and there, or shortening sentences be sufficient to avoid the label? I am not suggesting that there is rampant plagiarism in the book, but one does need to question when that plateau is reached in a book about Oracle Database.

While in some respects this book is more useful to the reader than the “Oracle Database 11g Performance Tuning Recipes” book due to the inclusion of test cases, both books seem to omit the reasoning behind why and when someone might consider performing the 80 or so tasks/recipes mentioned in the books. Vague, inaccurate, over-generalized, and out of date descriptions of Oracle Database behavior are limiting factors of both books. This review is quite long, and likely will not appear in full on Amazon – see my blog for the full review.

Data Dictionary Views:

DBA_VIEWS (page 20)
V$FIXED_TABLE (page 21)
V$LIBRARYCACHE (page 52)
V$STATNAME, V$MYSTAT (page 53)
SYS.SEQ$ (page 65)
DBA_MVIEWS, USER_MVIEWS, ALL_MVIEWS (page 69)
INDEX_STATS (pages 127, 128)
V$SYSSTAT (page 160)
V$SESSION (page 205)

Parameters:

CURSOR_SHARING (pages 9, 38)
TIMED_STATISTICS (pages 20, 201)
LOG_CHECKPOINTS_TO_ALERT, BACKGROUND_DUMP_DEST (page 28)
STATISTICS_LEVEL (pages 29, 32)
CONTROL_MANAGEMENT_PACK_ACCESS (page 32)
QUERY_REWRITE_ENABLED, QUERY_REWRITE_INTEGRITY (page 70)
DB_16K_CACHE_SIZE (page 84)
MAX_DUMP_FILE_SIZE, TRACEFILE_IDENTIFIER (page 201)
SQL_TRACE (page 202)

Hints:

APPEND (page 72)
INDEX (page 121)

Comments, Corrections, and Problems:

The book states, “The first rule in writing applications which connect to an Oracle Database is to always use bind variables, which means not to include parameters in SQL statements as literals.” The statement should be clarified that this is a general recommendation. There are times when literals should be used rather than bind variables, for instance if there are very popular and unpopular values in a column, it might be wise to prevent the sharing of execution plans when a very popular or very unpopular value is used in the WHERE clause. A correction/clarification is provided on page 51 (page 8).
Steps for creating a database with the Oracle Database Configuration Assistant seem to be out of place in a performance tuning book (pages 17-19)
Uses the term “fields” where the term “columns” should be used (page 21).
The book demonstrates the use of ANALYZE TABLE … COMPUTE STATISTICS, and DBMS_UTILITY.ANALYZE_SCHEMA to collect object statistics. The book states that ANALYZE is retained for backward compatibility, but the book provides no warning that using ANALYZE to collect statistics could be problematic since the release of Oracle Database 8.1 (reference page 21).
The book uses the word “elaborate” rather than “create” or “generate” (pages 24, 26, 27, 31, 37)
The book demonstrates the use of AWR without first mentioning the licensing requirements of that feature (pages 30-31).
Word substitution error: “… and we experiment a lack of performance in another period, we can elaborate two reports…” (page 31)
The book demonstrates the use of ADDM without first mentioning the licensing requirements of that feature. The book also states, “ADDM is enabled by default in Oracle Database 11g; it depends on two configuration parameters…” Unlike with Oracle Database 10.1 and 10.2, ADDM is not enabled by default in the Standard Edition of Oracle Database 11.1 or 11.2, nor can it be legally enabled on the Standard Edition. While ADDM is enabled by default in the Enterprise Edition 11.1 and 11.2, it cannot be legally used without a Diagnostic Pack license (pages 32-35).
The book suggests the system-wide use of the deprecated SIMILAR value for the CURSOR_SHARING parameter as one of two solutions to address a hard parsing problem in a test case script (page 38).
The book states, “Now the Soft Parse is 97.84 percent.” The output shown in the book actually indicates a Soft Parse percent of 99.20. The instance efficiency numbers in the output are identical to those found on page 40, so this might be an indication of a copy-paste error (page 39).
The book states, “If the PreparedStatement is not closed, it can be executed multiple times – changing the value assigned to bind variables – and only a ‘light’ soft-parse will occur, with no syntax and semantic check.” If the SQL statement is held open – there will NOT be a “light” soft-parse (session cached cursors are not discussed in this section of the book, which would allow a “light” soft-parse if the cursor is NOT held open) (page 52).
The elapsed time comparison between the directly executed SELECT statement, and the REFCURSOR that is returned by the SH.SALES_BY_PRODUCT procedure is not valid for a couple of reasons: 1) The script is executed by the internal user rather than a normal user, which can lead to unexpected performance differences; 2) The SELECT statement method displays its rows to the screen, so it is subject to delays caused by formatting the output for the SQL*Plus window (SET AUTOTRACE TRACEONLY STATISTICS may be used to reduce the impact of the formatting delays, but that change had little effect); 3) The REFCURSOR method, because it involves PL/SQL, will be subject to a context switch while the normal SELECT will not be subject to the context switch – the associated delay is operating system dependent and the timing should suggest that something is wrong with the test result; 4) While the normal SELECT statement test actually fetches the rows, the REFCURSOR method does not, as can be seen within an enabled 10046 trace (the normal SELECT will show a FETCH line that is preceded by WAIT lines, while the REFCURSOR method will not show a FETCH line in the trace file) (pages 54-55).
The output of the Java version of the SQL*Plus test script found on pages 54-55 conflicts with the author’s intended result. Directly executing the SQL statement required 1.438 seconds, while using the REFCURSOR in the Java code required 1.722 seconds. The performance difference may be more significant than shown, because the direct execution of the SQL statement test was performed first, and the timing results include the time to flush the shared pool and the buffer cache (the first call will almost certainly take longer than the second call) (pages 56-58).
The book uses a test case script to demonstrate the negative effects of using a “COUNTER” table rather than using a sequence to provide the same counter value. The test case script uses a trigger on the table to populate the counter column in the table, and the test case script does show that performance improves with the use of the Oracle sequence. The test case script, however, should have also included a test that completely eliminates the trigger on the table, populating the TRAVELID column by including TRAVEL_SEQ.NEXTVAL directly in the SQL statement that populates the table. My timing results show that the counter trigger-table method completes in 0.45 seconds, the trigger-sequence method completes in 0.14 seconds, and the select-sequence method completes in 0.03 seconds (reference pages 60-62).
Accidental word substitution, “… and if the high watermark is reached, it caches other X numbers in the same manner.” “other” should be “another” (page 65).
The author incorrectly read the AUTOTRACE generated execution plan. The book states “We can see that in the execution plan, there is full table access to the SALES table examining 918K rows and reading 8075 KB.” An AUTOTRACE generated execution plan shows an estimated execution plan that may differ from the actual execution plan in some situations, such as cases where bind variables are involved. Additionally, an AUTOTRACE generated execution plan shows the predicted number of rows that will be returned (not examined), and the predicted volume of data that will be returned (not read) based on the existing statistics for the objects (page 67).
The book states, “However, from the execution plan, the number of rows processed is 72, and each row is 648 bytes long.” Once again it is important to stress that the execution plan is a predicted execution plan generated by AUTOTRACE. The estimated 72 rows returned by the operation in the execution plan does agree with the “72 rows processed” displayed in the actual statistics for the execution, but that will not always be the case for an AUTOTRACE generated execution plan (it happens to be the case because statistics were collected for the materialized view with a 100% sample rate). The statement that each row is 648 bytes long appears to be the result of misreading the previous execution plan, which estimated that 72 rows consuming 648 bytes total would be returned from operation 0 in the execution plan. The AUTOTRACE generated execution plan for the materialized view predicts that 72 rows consuming 1872 bytes will be returned from operation 0 in the execution plan, which shows a predicted row length of 1872/72 = 26 bytes per row (pages 67-68).
The book states, “In the latter case [after flushing the buffer cache], we have 4047 consistent gets and 240 physical reads…” There are a couple of issues with this test case, found in the source code library file 2602_02_Materialized Views.sql. First, the script in the source code library uses “ANALYZE TABLE SH.MV_SALES_BY_PRODUCT COMPUTE STATISTICS” to collect the statistics on the materialized view, while the book shows the use of “EXEC DBMS_STATS.GATHER_TABLE_STATS” to collect the statistics – the collected statistics from the ANALYZE table command could very easily be different from the collected statistics from the DBMS_STATS.GATHER_TABLE_STATS command. The screen capture shown after flushing the buffer cache and re-executing the select from the materialized view does show 4,047 consistent gets and 240 physical block reads, as stated in the book, but it also shows 20,544 recursive calls where 0 recursive calls were shown prior to flushing the buffer cache – this recursive call count figure indicates that something else happened beyond the author flushing the buffer cache. My test results with just flushing the buffer cache show 8 consistent gets, 6 physical reads, and 0 recursive calls. The author also apparently flushed the shared pool, which triggered the recursive calls and the majority of the consistent gets and physical block reads (15,296, 2,978, and 177 respectively). The author probably should mention that the test case and advice will not work in a Standard Edition database, and should also state that the decision whether or not the materialized view is used is a cost-based optimizer decision (page 68).
The book lists “QUERY REWRITE” as a required privilege to create materialized views. The Oracle Database 11.2 (and 10.1) documentation state that the QUERY REWRITE privilege is deprecated, and thus not needed (reference page 69).
The book states, “The same parameters [QUERY_REWRITE_ENABLED, and QUERY_REWRITE_INTEGRITY] have to be enabled to use another functionality, function-based indexes.” QUERY_REWRITE_ENABLED must be set to TRUE in Oracle Database 9.2 to use function-based indexes, but that requirement disappeared in Oracle Database 10.1 (page 70).
The book states, “We encounter row chaining when the size of the row data is larger than the size of the database block used to store it.” While this statement is correct, the book omits a secondary cause of chained rows – Oracle database supports a maximum of 255 columns in a row piece, so tables with more than 255 columns will necessarily have chained rows (page 84).
The book casually demonstrates setting up a 16KB block size tablespace in a database that has a default 8KB block size. The book provides a list of several advantages for including smaller or larger than default block sizes in a single database including, “Faster scans: tables and indexes that require full scans can see faster performance when placed in a large block size.” This justification is incorrect for several reasons including the fact that the DB_FILE_MULTIBLOCK_READ_COUNT parameter is scaled up for tablespaces that use a smaller than database default block size, and scales the parameter down for tablespaces that use a larger than database default block size. All of the justifications found on page 88 appear to be copied verbatim from a commercial website page. The book does not discuss the bugs and unexpected optimizer cost changes that might result from using multiple block sizes in a single database (reference reference2 pages 84-88).
Step 5 contains two typos: using angle brackets (less than and greater than signs) rather than single quotes, and a spurious 3 after the semicolon (page 89).
Step 7 and 9 contain typos: using angle brackets (less than and greater than signs) rather than single quotes (page 90).
Steps 4 and 5 contain typos: using angle brackets (less than and greater than signs) rather than single quotes (page 97).
Step 14 contains a corrupted SQL statement: “CREATE.5* FROM HR.BIG_ROWS WHERE 1=0;”. Steps 15, 16, and 19 contain typos: using angle brackets (less than and greater than signs) rather than single quotes. The author should have mentioned at least one of the possible problems with this approach, which might include triggers on the table, foreign keys that point to the table, and the potential statistics problems caused by the use of the ANALYZE TABLE command (page 92).
The book states about the DBMS_SPACE.CREATE_TABLE_COST example, “In this procedure we have set the tablespace to use the average row size and the row count…” The purpose of this function is to estimate space usage, not to make changes to a tablespace (page 95).
Step 1 contains an extraneous “.5” in the command.
Pages 96-112 are present in the book, but omitted from this review.
Steps 11 and 13 use angle brackets (less than and greater than signs) rather than single quotes (pages 116-117)
The book states, “We can also create a function-based descending index.” This is a strange statement – all descending indexes in Oracle Database are function-based indexes (page 119).
The book states, “… this test allows us to dispel a myth. Oracle uses the indexes even if the leading columns are not referenced in the WHERE predicate of the query. We can see that in such a case, the operation will be an INDEX FAST FULL SCAN.” In this case, the author is incorrectly attempting to generalize a special case into a general rule. Firstly, there is no myth to dispel – Oracle’s query optimizer has had the ability to use INDEX SKIP SCAN operations when the leading column of an index is not specified in the WHERE clause, since the release of Oracle Database 9.0.1 a decade ago – but that access path is usually only advisable when there are few distinct values in the leading column of the index. The author’s test case is a special case because all of the columns selected from the table are present in the index structure (page 119).
The book states, “If we use a regular index to access the data, Oracle is unable to do the sort in a mixed way, in a query like this.” The author then shows a SQL statement with the first column in the ORDER BY clause sorted in descending order and the second column in the ORDER BY clause sorted in ascending order. At this point in the book, the author has not yet stated that Oracle Database is able to read index entries in an ascending or descending order through a normal (ascending sorted) b*tree index, so this sentence in the book is confusing – almost to say that Oracle Database is not able to sort one column in ascending sequence and a second column in descending sequence – that concept is obviously false. It would have been more accurate for the book to state that, “Oracle Database is unable to _avoid_ a sort operation when accessing the rows through a concatenated index if both of the columns in the index are sorted in ascending sequence, the ORDER BY clause of the SQL statement specifies that one and only one column contained in the index should be ordered in descending sequence, and the second column in the concatenated index is included in the WHERE clause.” (page 120)
A self-contradicting sentence, “In the first case, we have a full table scan, because we cannot retrieve all of the data from the index, so we have to do a TABLE ACCESS BY ROWID operation for each row, which satisfies the predicate.” Full table scan probably does not belong in that sentence (page 121).
The book states, “In the next screenshot, we can see that Oracle knows (from the table statistics) that only 43 rows satisfy the where condition.” It is important to stress that the autotrace generated execution plan only shows the estimated number of rows that will be returned by an operation – the author’s query, in fact, retrieves a single row. The index that the author specified in the index hint was created on the columns CUST_LAST_NAME and CUST_YEAR_OF_BIRTH (in descending order), yet the author’s query only included the CUST_FIRST_NAME column in the WHERE clause – it is ridiculous to force the optimizer to use this index with a hint (page 121).
The index’s clustering factor was not mentioned in the discussion of what determines the point at which it is more efficient to access a table through an index access path, rather than a full table scan – only the average row length was described as a consideration and the percentage of the rows that need to be retrieved. It could very well be the case that with a very poor clustering factor, that it is more efficient to retrieve less than 1% of the table’s rows through a full table scan, rather than an index lookup (page 122).
The book should define “intra-block fragmentation” which is the benefit that the book lists as resulting from rebuilding indexes (page 123).
The two session example of one session rebuilding an index while a second session executes a SELECT and INSERT seems to be pointless. The second session does not use the index that the first session attempts to rebuild, instead a full table scan is performed on the BIG_CUSTOMERS table, followed by an index unique scan of the CUSTOMERS_PK index. An index named IX1_BIG_CUSTOMERS was created in the script, yet the script attempts to rebuild a non-existent index named IX1_MYCUSTOMERS. The test case only shows an example of efficiency gains due to blocks being buffered in the buffer cache. The book should have mentioned that an online rebuild and parallel rebuild are only possible in the Enterprise Edition of Oracle Database (pages 123-125).
Step 10 uses angle brackets (less than and greater than signs) rather than single quotes (page 126).
The book states, “We have used the PARALLEL option too, to speed up the rebuild process.” While specifying PARALLEL during an index rebuild may speed up the rebuild, it is important to note that this results in an index with a parallel degree that should be manually reset to the original value, once the rebuild completed (page 127).
The book states, “However, when we have a table on which there are many INSERTs and DELETEs, we could schedule an index rebuild, because when deleting an index entry, the space is not freed in the index leaf, but just marked as deleted. If we have massive DELETE and INSERT operations, we could have a skewed index structure, which could slow performance due to intra-block fragmentation.” The book should have defined what is meant by “skewed index structure” – does the book mean, for instance, that one portion of the index could have a BLEVEL of 2 while another portion of the index could have a BLEVEL of 3 – if that is the case, the book’s statement is incorrect. If the book’s definition of “skewed index structure” is that some leaf blocks of the index will be more densely packed than other leaf blocks in the same index structure, then that should be considered normal behavior for Oracle indexes – an occasional coalesce might be used to combine index entries in logically adjacent leaf blocks, but scheduling index rebuilds is neither required, nor recommended. Depending on the order of the inserted values in relation to the order of the entries in the index leaf blocks, an index leaf block split operation could evenly divide the existing index entries between two leaf blocks (a 50-50 split, resulting in both index blocks being 50% utilized, if the inserted value is not the highest value that would be inserted into the leaf block), or all of the existing entries will remain in the existing leaf block and the new entry will be placed by itself into a new leaf block (a 90-10 split). A deleted index entry will remain in the block at least until that transaction is committed, but any post-transaction insert into the block will clear out all deleted index entries in the block. Deleting all table rows with index entries at the low end of the index (the values were populated by a sequence, for example, and are deleted in the same sequential order) could leave many blocks in the index structure with nothing but deleted index entries, but that situation should only result in a performance problem if SQL statements attempt to determine the minimum value for the indexed column, or to some extent, fast full index scans and full index scans (reference reference2 page 127).
The book states, “If the value for DEL_LF_ROWS/LF_ROWS is greater than 2, or LF_ROWS is lower than LF_BLKS, or HEIGHT is 4 then the index should be rebuilt.” Some of the advice found on the Internet suggests that if DEL_LF_ROWS is 20% of LF_ROWS, then the index should be rebuilt – did the author of this book intend to write “If the value for DEL_LF_ROWS/LF_ROWS is greater than 0.2”? Why should the result of DEL_LF_ROWS/LF_ROWS be a consideration of whether or not an index should be rebuilt – is it supposed to measure the amount of wasted/unused space in the index leaf blocks? The next INSERT/UPDATE DML operation in a given leaf block will clear out the index rows that are flagged as deleted, but then does that imply that the space is not wasted (or is the space wasted)? What if there are many index blocks that are roughly 50% utilized due to a large number of 50-50 leaf block splits, is that space not wasted (or is the space wasted)? Since the formula DEL_LF_ROWS/LF_ROWS really does not describe the percent of used space in the index, it is probably best to just ignore the result of that formula. DEL_LF_ROWS/LF_ROWS can never be greater than 1 because the statistic found in the LF_ROWS column includes the DEL_LF_ROWS statistic. The second criteria suggests comparing LF_ROWS to LF_BLKS, such that if on average there is less than one index entry per leaf block, that the index should be rebuilt – there can never be less than one index entry per leaf block, because the leaf block will be detached from the index structure when all rows are removed from that leaf block. The final criteria suggests rebuilding the index when the height is exactly 4 – does that mean that an index with a height of 5, 6, 7, etc. does not need to be rebuilt? What if after rebuilding the index it still has a height of 4 – will it help to rebuild a second time? (page 127)
The book states, “When we rebuild an index, we can add the COMPUTE STATISTICS option to that statement.” Since the release of Oracle Database 10.1, statistics are automatically collected when indexes are created and/or rebuilt, so the COMPUTE STATISTICS clause is unnecessary (page 127).
Steps 6 and 9 uses angle brackets (less than and greater than signs) rather than single quotes (page 128-129).
Steps 8 and 15 uses angle brackets (less than and greater than signs) rather than single quotes (page 131-132).
The book should mention that bitmap indexes are not available in the Standard Edition of Oracle Database (page 136).
Step 3 uses angle brackets (less than and greater than signs) rather than single quotes (page 137).
The author created a composite bitmap index with three columns to demonstrate the use of bitmap indexes. Composite bitmap indexes are rare – one of the strengths in using bitmap indexes is the ability to create multiple single column bitmap indexes, and as needed the optimizer will select to bitmap join two or more bitmap indexes in an attempt to significantly reduce the number of rows visited in the table (page 138).
The book states, “This time the execution plan uses the newly created bitmap index, … using the INDEX RANGE SCAN or INDEX FAST FULL SCAN operation, depending on whether we are filtering on the first key column of the index – CUST_GENDER – or not. This result is obtained thanks to the structure of bitmap indexes.” With the index definition found in the book, the operations that should be present in the execution plan are BITMAP INDEX RANGE SCAN and BITMAP INDEX FAST FULL SCAN, while you might expect to find INDEX RANGE SCAN or INDEX FAST FULL SCAN operations associated with normal b*tree indexes. However, it is a cost-based decision for the optimizer to use or not use an index, so there is no guarantee that index will be used as indicated in the book if the leading column in the index is either specified or not specified. Additionally, it is not the structure of bitmap indexes that permits INDEX RANGE SCAN or INDEX FAST FULL SCAN operation, depending on whether we are filtering on the first key column of the index – creating a normal b*tree index in the script rather than a composite bitmap index could (will) actually allow the optimizer to take advantage of INDEX RANGE SCAN or INDEX FAST FULL SCAN operations (page 139).
The book states, “Bitmap indexes offer very fast performance when we have a low cardinality field indexed on a table containing many rows.” This statement could have several different interpretations, but I believe that the author’s intended meaning is “Bitmap indexes offer significantly faster performance than b*tree indexes when columns with few distinct values are indexed in tables containing a significant number of rows.” This fixed statement still requires additional clarification – if the bitmap index does not help to further reduce the number of table rows that are accessed through the index, the end result may be performance that is roughly the same as that of an equivalent b*tree index. One way to accomplish the task of further reducing the number of table rows accessed is through the utilization of multiple bitmap indexes with bitmap combine operations to significantly reduce the number of rowids that are used to fetch table rows (page 139).
The book states, “When rows are frequently inserted, deleted, and updated, there is a performance bottleneck if we use a bitmap index. When the index is updated, all the bitmap segments are locked.” This statement requires a bit of clarification. I do not believe that the author is stating that updating an entry in a bitmap index will lock all of the bitmap indexes in the database (a segment could be a table, table partition, index, etc.). Instead, I think that the author is intending to state that updating an entry in a bitmap index will lock all of the index entries in that index, effectively preventing any other session from inserting, updating (the column covered by the index), or deleting rows in the table. For very small bitmap indexes, this statement could very well be true. However, for larger bitmap indexes, built for tables with many rows, the number of index rows that will be locked during an update is determined by the number of rows covered by the index block(s) that update changed, possibly 20,000 to 50,000 rows per index block. (reference slide 46, reference2 page 2, reference3 comments section; page 139).
The book states, “This [bitmap join index] is a bitmap index which represents the join between two tables, and can be used instead of a materialized view in certain conditions.” The book did not offer any suggestions or describe any conditions that permit a bitmap join index to take the place of a materialized view. The statement in the book needs additional clarification (reference reference2 page 140).
The book states about index organized tables, “If the row size exceeds the size indicated by this parameter [PCTTHRESHOLD], the fields not indicated by the INCLUDING option are stored in the OVERFLOW – if indicated, otherwise the row is not accepted.” This is a confusing sentence – it is not clear what the author is attempting to state. The Oracle documentation states, “In addition to specifying PCTTHRESHOLD, you can use the INCLUDING clause to control which nonkey columns are stored with the key columns. The database accommodates all nonkey columns up to and including the column specified in the INCLUDING clause in the index leaf block, provided it does not exceed the specified threshold. All nonkey columns beyond the column specified in the INCLUDING clause are stored in the overflow segment. If the INCLUDING and PCTTHRESHOLD clauses conflict, PCTTHRESHOLD takes precedence.” (reference page 146).
The book demonstrates partitioning without mentioning that partitioning is an extra cost option that may only be purchased for the Enterprise Edition (page 146).
The book states, “To obtain the best uniform data distribution, it’s better to choose a number of partitions which is a power of 2, having a unique or near partition key.” The Oracle Database documentation states, “For optimal data distribution, the following requirements should be satisfied: Choose a column or combination of columns that is unique or almost unique. Create multiple partitions and subpartitions for each partition that is a power of two.” It appears that the author of the book incorrectly stated the first requirement that is mentioned in the documentation (reference page 149).
The script is run by the SYS user rather than a normal user, which can lead to unexpected performance differences (page 157).
The “ALTER TABLE SH.MY_SALES_2 ENABLE ROW MOVEMENT” and “SHRINK SPACE” commands are only applicable if the MY_SALES_2 table is stored in an ASSM tablespace – the book did not mention that limitation (page 165).
The book states, “If we do an FTS [full table scan], database buffers are used to read all the table data, and this situation may lead to flushing the buffer cache data to make room for the FTS data. To avoid this situation and to limit the consequences on the database buffer cache, the database blocks from FTS operations are put on the top of the LRU (Least Recently Used) list.” This statement requires significant adjustment before it is an accurate statement. Is the author describing the behavior of Oracle Database 8.1 or Oracle Database 11.2 as indicated on the front cover of the book? What is meant by the “top of the LRU list” – is that the MRU (most recently used) end? If the author meant that the blocks were placed on the least recently used end of the LRU list, then the author agrees with the Oracle Database 10.2 documentation, but that documentation is incorrect (the behavior changed around the time 9.0.1 was released). The Oracle Database 11.2 documentation states that blocks read by full table scan are placed at the mid-point of the LRU list (if the CACHE keyword is specified when the table is created or altered, then the table blocks will be placed on the MRU end of the list). Since the book is specifically about Oracle Database 11.2, it is worth pointing out that since the release of Oracle Database 11.1, the Oracle RDBMS often makes use of serial direct path read when performing full table scans, and that type of access completely avoids reading the table blocks into the buffer cache (the blocks are read into the PGA). Oracle event 10949 may be used to disable serial direct path read. What about parallel full table scans of larger tables? Those too will avoid flooding the buffer cache with blocks that may only be accessed once. Smaller tables will certainly have their blocks read into the buffer cache, but the touch count associated with each of the table’s blocks will limit the problems that those blocks will cause in the buffer cache (reference page 170).
The book states, “This is because when we have a larger database block, we can read many rows in a block and even subsequent database blocks – in one operation – by setting the parameter DB_FILE_MULTIBLOCK_READ_COUNT (at the instance or session level).” While the book does indicate that the maximum number of bytes read is operating system dependent, the book provides no hint regarding the Oracle limit for the parameter, or anything else that might cause fewer blocks to be read in a single read request (extent size, pre-existing blocks already in the buffer cache, etc.) Since this book is about Oracle Database 11.2, it is worth mentioning that as of Oracle Database 10.2 the Oracle RDBMS will automatically derive a value for the DB_FILE_MULTIBLOCK_READ_COUNT parameter based on the SGA size and the value of the SESSIONS parameter – so nothing actually needs to be set to take advantage of multi-block reads. Stating the obvious, but the DB_FILE_MULTIBLOCK_READ_COUNT parameter has no influence over Oracle reading multiple rows from the same table block (page 171).
The book states, “The use of this parameter [ DB_FILE_MULTIBLOCK_READ_COUNT] influences even the optimizer – if it’s less expensive to read all the rows in a table than using an index, the optimizer will use an FTS even if there are usable indexes in place.” The accuracy of this statement is Oracle Database version dependent (prior to Oracle Database 9.0.1 the statement was true, as of Oracle Database 9.0.1 the statement is false when workload system statistics have been collected (reference page 171).
The recipe titled “Avoiding full table scans” showed how to trigger full table scans, but did not show how to avoid full table scans, nor did it provide any advice about when full table scans should be avoided (pages 164-172).
The book states, “The effectiveness of an index depends on the number of rows selected out of the total number of rows in the table… In the real world, an index with a selectivity of less than 10 percent [of the table’s rows] is considered suitable enough [for the index to be used].” The 10% figure is a very rough figure – the suitability of an index is dependent on many items beyond the percentage of rows that will be selected. Quoting from the documentation, “The cost of an index scan depends on the levels in the B-tree, the number of index leaf blocks to be scanned, and the number of rows to be fetched using the rowid in the index keys. The cost of fetching rows using rowids depends on the index clustering factor.” “Therefore, the optimizer’s decision to use full table scans is influenced by the percentage of blocks accessed, not rows. This is called the index clustering factor. If blocks contain single rows, then rows accessed and blocks accessed are the same.” The calculated cost determines whether or not an index will be used – the cost for an index range scan is index blevel + ceil(index selectivity * leaf blocks) + ceil(table selectivity * clustering factor). While the optimizer’s calculated cost for a particular index access path may not accurately represent the effectiveness of the index, the cost is the final deciding factor for the optimizer when determining the effectiveness of the index for a specific SQL statement (reference reference2 reference3 page 176).
The book states, “The answer is simple – indexes cannot be used when we compare values with a not equal operator.” To clarify, the book is describing a situation involving a single table, when the only predicate on the indexed column is the inequality comparison. Adding an INDEX hint to the author’s sample SQL statement results in an INDEX FULL SCAN operation – the hint allows the SQL statement containing the not equal predicate to use the MY_CUSTOMERS_IXVALID index. It is not the case that the index cannot be used with an inequality comparison, however, the query optimizer does not automatically consider an access path involving the index due to the general assumption that many rows will be returned when all except one value is selected. To avoid the INDEX FULL SCAN operation, where the index structure is read one block at a time, the inequality could be converted to a less than predicate and a greater than predicate with a logical OR between the two predicates (reference page 176).
The book states, “NULL values are not stored in indexes, so when we query for records with a NULL value in field X, even if the X column is indexed, the index will not be used.” The book’s description is incomplete. NULL values are not stored in single column b*tree indexes. There are at least four methods to work around this issue and allow indexes to be used to identify rows with a NULL value in the indexed column: 1) Define a composite index with at least one other column that has a NOT NULL constraint – ideally, the column in which the NULL values might appear would be the leading column in the composite index. 2) Define a composite index with a numeric constant (such as 1) as the second column in the composite index. 3) Bitmap indexes always store NULL values – if appropriate (column experiences few updates, deletes, inserts, and an Enterprise Edition database), create a bitmap index for the column. 4) If the number of NULL values in a column will be relatively small, and the original SQL statement may be modified, create a function based index that converts NULL values to 1 and non-NULL values to NULL: DECODE(C3,NULL,1), or (CASE WHEN C3 IS NULL THEN 1 ELSE NULL END), or (NVL2(C3,NULL,1)). The DECODE syntax is subject to NLS related translations – be certain to check the view USER_IND_EXPRESSIONS for the index to determine the syntax required in the WHERE clause of the SQL statements to take advantage of the function based index (page 177).
The book states, “If direct path load would be the fastest method to insert data in tables, without constraints, the optimizer would use it by default.” This sentence does not make sense. Is the word “constraints” in this sentence to be read as a table without primary key/foreign key/unique key constraints, or is “constraints” to be read as serialized operations/space usage “limitations” of direct path insert operations – it is these limitations that allow the direct path inserts to function without corrupting tables and indexes (page 185).
In step 5, the book states, “Rewrite the query using the NOT IN construct:”. The example shown in the book shows a non-correlated (invalid) NOT EXISTS SQL statement rather than the NOT IN construction that is found in the source code library (page 193).
The book states, “Even in the previous case we can see the substantial equivalence of the NOT IN and NOT EXISTS operations, related to gets/reads.” One of the problems with this test case is that the output is not showing the equivalence of the NOT IN and NOT EXISTS forms of the SQL statement – there can be a difference between the two when NULL values enter the picture (that possibility is described in pages 199-200). The issue with the test case is that the (11.2) Oracle query optimizer has transformed (as seen in a 10053 trace) both the NOT IN and NOT EXISTS queries into the following query that uses a regular join “SELECT S.AMOUNT_SOLD AMOUNT_SOLD FROM SH.CUSTOMERS C,SH.SALES S WHERE S.CUST_ID=C.CUST_ID AND (C.CUST_CREDIT_LIMIT=10000 OR C.CUST_CREDIT_LIMIT=11000 OR C.CUST_CREDIT_LIMIT=15000)” (page 197).
When describing the output of the ANSI left outer join version of the NOT IN/NOT EXISTS SQL statement, the book states, “Even if we have the same statistics and almost the same execution plan, the meaning of the last query isn’t as intuitive as in the previous case… and we have seen that there is no performance improvement (or detriment) in doing so.” The problem here is that the (11.2) Oracle query optimizer has transformed the ANSI left outer join SQL statement into a SQL statement that is very similar to the transformed NOT IN/NOT EXISTS version of the query. In older Oracle Database versions, where these automatic transformations would not take place, the left outer join syntax is often much faster than the equivalent NOT IN or NOT EXISTS syntax. The (11.2) optimizer transformed version of the SQL statement follows, “SELECT S.AMOUNT_SOLD AMOUNT_SOLD FROM SH.SALES S,SH.CUSTOMERS C WHERE C.CUST_ID IS NULL AND S.CUST_ID=C.CUST_ID(+) AND (C.CUST_CREDIT_LIMIT(+)=10000 OR C.CUST_CREDIT_LIMIT(+)=11000 OR C.CUST_CREDIT_LIMIT(+)=15000)” (page 198).
The book states, “We have to set the destination for our trace files also. When using dedicated servers, the parameter is USER_DUMP_DEST. In the multi-threaded server environment the parameter is BACKGROUND_DUMP_DEST…” This recipe is oddly reminiscent of pages 452 through 458 of the book “Expert One-On-One Oracle”, and seems to be more applicable to Oracle Database 8.1 than to Oracle Database 11.2. In Oracle Database 11.1 and 11.2, by default the USER_DUMP_DEST and BACKGROUND_DUMP_DEST parameters both point at the same directory named “trace”. The TIMED_STATISTICS parameter defaults to TRUE because the STATISTICS_LEVEL parameter defaults to TYPICAL in Oracle Database 10.1 and above, so it is not necessary to modify the TIMED_STATISTICS parameter. The use of “ALTER SESSION SET SQL_TRACE=TRUE;”, as shown in the recipe, is deprecated as of Oracle Database 10.2 (see Metalink Doc ID 30820.1). “Multi-threaded server” was renamed to “shared server” with the release of Oracle Database 9.0.1. (pages 201-205)
Appendix A and Appendix B include a somewhat random sampling of various Oracle Database performance views and database packages. The depth of coverage of the various views and packages rarely extends beyond a brief summary of a couple of view columns or procedures in the package. Think of these appendixes as a condensed and reformatted version of the Oracle Database documentation book contents.
The descriptions of all of the columns in the V$SESSION dynamic performance view are shortened verbatim copies or slightly reworded copies of the descriptions found in the Oracle Database 11.2 Reference book in the Oracle Database documentation library – below are comments about three randomly selected entries in the appendixes (page 490).
The descriptions of all of the procedures listed for the DBMS_OUTLN package are copied verbatim from the descriptions found in the Oracle Database 11.2 Supplied PL/SQL Packages book in the Oracle Database documentation library (page 505).
The descriptions of the REFRESH_PRIVATE_OUTLINES and DROP_EDIT_TABLES procedures of the DBMS_OUTLN_EDIT package are verbatim copies of the descriptions found in the Oracle Database 9.2 Supplied PL/SQL Packages book in the Oracle Database documentation library (page 504).
The descriptions of the LOAD_PLANS_FROM_CURSOR_CACHE, LOAD_PLANS_FROM_SQLSET, and DROP_SQL_PLAN_BASELINE procedures of the DBMS_SPM package are verbatim copies of the descriptions found in the Oracle Database 11.2 Supplied PL/SQL Packages book in the Oracle Database documentation library (page 505).

Useful Information:

Seven steps for solving a performance problem – might lead to compulsive tuning disorder if followed to the letter, but still a good general practice (page 12).
Book seems to use a lot of forward and backward references.
Good (simple) example of SQL injection risks (pages 161-163).

Part 2 of this book review is expected to cover the second half of the book, including the freely downloadable chapter 10.

Blog articles that reference the “Oracle Database 11gR2 Performance Tuning Cookbook” book:

Tables and Their Indexes Should be Located on Different Disks – What is Wrong with this Quote?

Directly Executing a Query is Slower than Returning a REFCURSOR from a PL/SQL Procedure – What is Wrong with this Quote?

Interesting Index “Facts” – What is Wrong with these Quotes?

Full Table Scans and the Buffer Cache in 11.2 – What is Wrong with this Quote?

Repeat After Me: NULL Values are Not Stored in Indexes?

Comments : Comments Off

Categories : Book Review

	Tablespaces and part… on Tables and Their Indexes Shoul…
	Dynamic sampling NOT… on Dynamic Sampling Changes
	Charles Hooper on Install Nagios on a Synology D…
	Markus on Install Nagios on a Synology D…
	Charles Hooper on Connecting to an Oracle Databa…
	akila on Connecting to an Oracle Databa…
	Dird on Book Review: Oracle Database 1…
	Charles Hooper on Book Review: Oracle Database 1…
	Dird on Book Review: Oracle Database 1…
	Charles Hooper on Book Review: Oracle Database 1…
	Dird on Book Review: Oracle Database 1…
	jkstill on Brain Teaser: 10046 Extended S…
	Scrabble Word Finder… on The New Order Oracle Coding Ch…
	Charles Hooper on SQL – DENSE_RANK, PERCENT_RANK…
	Irshad on SQL – DENSE_RANK, PERCENT_RANK…

Charles Hooper's Oracle Notes

Which PLAN_HASH_VALUE Appears in V$SQLAREA?

Monitoring Changes to Table Data

Hyper-Extended Oracle Performance Monitor 6.0 Beta

DOWNLOAD:

Thoughts on a Hyper-Extended Oracle Performance Monitor Beta

Book Review: Oracle Database 11gR2 Performance Tuning Cookbook (Part 1)

Categories

Recent Articles

Misc. Articles

Popular Articles

Recent Comments

Blogroll

Hints for Posting Code Sections in Comments

Follow “Charles Hooper's Oracle Notes”

March 2012
M	T	W	T	F	S	S
« Feb				Apr »
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30	31