Non-Specific Index Hints

January 24, 2012 (Modified January 25, 2012)

As I read the “Troubleshooting Oracle Performance” book for the second time a couple of months ago, I made note on page 385 that it was possible to specify table columns in an index hint, rather than specifying specific index names (or just specifying the table name).  This might be useful, for example, if you have tables in your database with primary key columns with system assigned names for the supporting indexes, and an index hint is needed to correct specific performance issues.  I was again reminded that it was possible to create non-specific index hints that specify table columns when a recent quiz was posted that asked to find specific cases where the behavior is other than expected with the newer index hint syntax.

As an example of the newer syntax, I put together a brief demonstration.  First, the test table and index creation script:

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

INSERT INTO
  T2
SELECT
  ROWNUM C1,
  MOD(ROWNUM-1,20)+1 C2,
  RPAD('A',100,'A')
FROM
  DUAL
CONNECT BY
  LEVEL<=100000;

COMMIT;

CREATE INDEX IND_T2_C2 ON T2(C2);

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

SET AUTOTRACE TRACEONLY EXPLAIN
SET LINESIZE 120
SET PAGESIZE 1000 

Let’s try a simple query that specifies columns C1 and C2 in the WHERE clause:

SELECT
  C1,
  C2,
  C3
FROM
  T2
WHERE
  C1 BETWEEN 1 AND 10000
  AND C2=1;

Plan hash value: 906133967

------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |             |   500 | 54500 |   121   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID     | T2          |   500 | 54500 |   121   (1)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS    |             |       |       |            |          |
|   3 |    BITMAP AND                    |             |       |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS|             |       |       |            |          |
|*  5 |      INDEX RANGE SCAN            | IND_T2_C2   |       |       |    10   (0)| 00:00:01 |
|   6 |     BITMAP CONVERSION FROM ROWIDS|             |       |       |            |          |
|   7 |      SORT ORDER BY               |             |       |       |            |          |
|*  8 |       INDEX RANGE SCAN           | SYS_C008661 |       |       |    20   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("C2"=1)
   8 - access("C1">=1 AND "C1"<=10000) 

The above shows that if this SQL statement were actually executed, the index IND_T2_C2, and the index SYS_C008661 (that is used to help enforce the primary key) would be used when executing the SQL statement.  You will only see the above execution plan in the Enterprise Edition of Oracle Database.  Let’s try again with a hint that prohibits the BITMAP CONVERSION TO FROM ROWIDS operation:

SELECT /*+ OPT_PARAM('_B_TREE_BITMAP_PLANS','FALSE') */
  C1,
  C2,
  C3
FROM
  T2
WHERE
  C1 BETWEEN 1 AND 10000
  AND C2=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   500 | 54500 |   131   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T2   |   500 | 54500 |   131   (2)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("C2"=1 AND "C1"<=10000 AND "C1">=1) 

The above execution plan shows that without the ability to perform the  the BITMAP CONVERSION TO FROM ROWIDS operation to allow the BITMAP AND operation, a full table scan was selected, so this is a case where the Standard Edition of Oracle Database and the Enterprise Edition might exhibit different execution performance.

Let’s use the previous SQL statement with hint as a starting point, and use a non-specific index hint to instruct the optimizer to use an index on the T2 table:

SELECT /*+ INDEX(T2) OPT_PARAM('_B_TREE_BITMAP_PLANS','FALSE') */
  C1,
  C2,
  C3
FROM
  T2
WHERE
  C1 BETWEEN 1 AND 10000
  AND C2=1;

Plan hash value: 3350885058

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |   500 | 54500 |   179   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T2          |   500 | 54500 |   179   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | SYS_C008661 | 10000 |       |    20   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("C2"=1)
   2 - access("C1">=1 AND "C1"<=10000) 

As shown by the above, the optimizer selected to use the SYS_C008661 primary key index, which would require it to retrieve 10,000 ROWID values from the index, rather than using the index on the T2 column which would have retrieved 5,000 (100,000 * 1/20) ROWIDs from the IND_T2_C2 index (the clustering factor of the IND_T2_C2 index was likely the deciding factor).  Let’s specifically request (demand) that the optimizer use the IND_T2_C2 index by adjusting the hint:

SELECT /*+ INDEX(T2 IND_T2_C2) OPT_PARAM('_B_TREE_BITMAP_PLANS','FALSE') */
  C1,
  C2,
  C3
FROM
  T2
WHERE
  C1 BETWEEN 1 AND 10000
  AND C2=1;

Plan hash value: 174424276

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |   500 | 54500 |  1596   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T2        |   500 | 54500 |  1596   (1)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T2_C2 |  5000 |       |    10   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("C1"<=10000 AND "C1">=1)
   2 - access("C2"=1) 

Notice in the above that the IND_T2_C2 index was selected, is expected to return 5,000 ROWID values from the index, and the execution plan now has a calculated cost of 1,596.  The calculated cost is a simple explanation why the optimizer did not select to use this index automatically.

Now, consider a situation where the optimizer insists on using the IND_T2_C2 index, rather than the SYS_C008661 primary key index for this SQL statement, which could happen if the CLUSTERING_FACTOR of the indexes are not set correctly:

SET AUTOTRACE OFF

SELECT
  INDEX_NAME,
  CLUSTERING_FACTOR
FROM
  USER_INDEXES
WHERE
  TABLE_NAME='T2';

INDEX_NAME   CLUSTERING_FACTOR
------------ -----------------
SYS_C008661               1585
IND_T2_C2                31700

EXEC DBMS_STATS.SET_INDEX_STATS (OWNNAME=>USER,INDNAME=>'IND_T2_C2',CLSTFCT=>1585,NO_INVALIDATE=>FALSE)

SELECT
  INDEX_NAME,
  CLUSTERING_FACTOR
FROM
  USER_INDEXES
WHERE
  TABLE_NAME='T2';

INDEX_NAME   CLUSTERING_FACTOR
------------ -----------------
SYS_C008661               1585
IND_T2_C2                 1585 

We started with the primary key index having a clustering factor of 1,585 and the IND_T2_C2 index having a clustering factor of 31,700.  After using DBMS_STATS.SET_INDEX_STATS, the optimizer is convinced that the IND_T2_C2 index also has a clusting factor of 1,585.  Now both of the indexes have the same CLUSTERING_FACTOR statistic value, what happens if we execute the SQL statement again that specifies an index should be used to access table T1, but does not specify the exact index name?:

SELECT /*+ INDEX(T2) OPT_PARAM('_B_TREE_BITMAP_PLANS','FALSE') */
  C1,
  C2,
  C3
FROM
  T2
WHERE
  C1 BETWEEN 1 AND 10000
  AND C2=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 174424276

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |   500 | 54500 |    90   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T2        |   500 | 54500 |    90   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T2_C2 |  5000 |       |    10   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("C1"<=10000 AND "C1">=1)
   2 - access("C2"=1) 

So, now that the CLUSTERING_FACTOR values are the same for both indexes, the optimizer is selecting the IND_T2_C2 index for the SQL statement.  We have successfully painted ourselves into a corner, telling the optimizer that the IND_T2_C2 index really is helpful for this SQL statement.  We are fairly certain that the IND_T2_C2 index is not ideal, and that the the SYS_C008661 primary key index is a better choice if an index access path is determined to be better than a full table scan, based on the order in which the data was inserted into the table’s blocks.  The problem now is that the SYS_C008661 index name is not consistent from one database to the next, or even in the same database if you drop table T2 and repeat the test.  How do we tell the optimizer to pick the index that is on the primary key column, column C1?  Oracle Database 10.1 introduced new syntax for index hints, which allows us to specify the column name(s), rather than the index name in the hint:

SELECT /*+ INDEX(T2 (C1)) OPT_PARAM('_B_TREE_BITMAP_PLANS','FALSE') */
  C1,
  C2,
  C3
FROM
  T2
WHERE
  C1 BETWEEN 1 AND 10000
  AND C2=1;

Plan hash value: 3350885058

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |   500 | 54500 |   179   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T2          |   500 | 54500 |   179   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | SYS_C008661 | 10000 |       |    20   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("C2"=1)
   2 - access("C1">=1 AND "C1"<=10000) 

As can be seen by the above, the index on column C1, which happens to be the primary key index, was selected by the optimizer at the request of the index hint.

Let’s take a look at the documentation for Oracle Database 10.2:

“Hints that specify an index can use either a simple index name or a parenthesized list of columns as follows:

index (table (column))

• table specifies the name
• columnspecifies the name of a column in the specified table
  • The columns can optionally be prefixed with table qualifiers allowing the hint to specify bitmap join indexes where the index columns are on a different table than the indexed table. If tables qualifiers are present, they must be base tables, not aliases in the query.
  • Each column in an index specification must be a base column in the specified table, not an expression. Function-based indexes cannot be hinted using a column specification unless the columns specified in the index specification form the prefix of a function-based index.
• index specifies an index name

The hint is resolved as follows:

• If an index name is specified, only that index is considered.
• If a column list is specified and an index exists whose columns match the specified columns in number and order, only that index is considered. If no such index exists, then any index on the table with the specified columns as the prefix in the order specified is considered. In either case, the behavior is exactly as if the user had specified the same hint individually on all the matching indexes.

For example, in Example 16-3 the job_history table has a single-column index on the employee_id column and a concatenated index on employee_id and start_date columns. To specifically instruct the optimizer on index use, the query can be hinted as follows:

SELECT /*+ INDEX(v.j jhist_employee_ix (employee_id start_date)) */ * FROM v;

That is almost crystal clear (I probably poorly translated the diagram), even though it seems that the cost calculation might not have an impact (“If no such index exists, then any index on the table with the specified columns as the prefix in the order specified is considered“).  If you have the sample database schema loaded into a database, jump to the view definition in Example 16-3, create the view, and then see if the index hint in the documentation (at the bottom of the above quote block) actually works – it did not work for me.  The same hint example is found in the Oracle Database 11.2 documentation.

However, in the example above, where I used the hint syntax INDEX (TABLE (COLUMN)), the hint worked as expected.  Interestingly, the hint reference for Oracle Database 11.2 does not specifically mention this new syntax, and that might be why I was slow to recognize the new hint syntax.

—

Now that the basics are covered, let’s see if we are able to confuse the optimizer with index hints, taking up the challenge proposed by the blog article mentioned at the start of this article.  Below is a slightly modified version of the test script that I posted as a comment in the other blog article (with an additional column in the table, an additional index, and an enabled 10053 trace).  First, we will create the table, a couple of indexes on the table, change a couple of SQL*Plus parameters, and then enable a 10053 trace:

CREATE TABLE T1 (
  C1 NUMBER NOT NULL,
  C2 NUMBER NOT NULL,
  C3 VARCHAR2(30) NOT NULL,
  C4 VARCHAR2(200),
  C5 VARCHAR2(10));

INSERT INTO T1
SELECT
  MOD(ROWNUM-1, 90) * 4 C1,
  ROWNUM - 1 C2,
  TO_CHAR(ROWNUM - 1, 'RN') C3,
  LPAD('A',200,'A') C4,
  LPAD('B',10,'B') C5
FROM
  DUAL
CONNECT BY
  LEVEL<=1000000;

CREATE INDEX IND_T1_C1_C2 ON T1(C1,C2);
CREATE INDEX IND_T1_C1_C2_C5 ON T1(C1,C2,C5);
CREATE INDEX IND_T1_C2_C1_C3 ON T1(C2,C1,C3);
CREATE INDEX IND_T1_C3_C1_C2 ON T1(C3,C1,C2);

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

SET AUTOTRACE TRACEONLY EXPLAIN
SET LINESIZE 120
SET PAGESIZE 1000
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';

Let’s begin the process of trying to confuse the Oracle Database 11.2.0.2 optimizer.  Unhinted, the following query accesses the index on columns C2, C1, and C3 to avoid accessing the table, and uses an INDEX FAST FULL SCAN operation that employs multi-block reads :

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'UNHINTED';

SELECT
  C1,
  C2,
  C3
FROM
  T1;

Plan hash value: 2374279026

----------------------------------------------------------------------------------------
| Id  | Operation            | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                 |  1000K|    23M|   387   (2)| 00:00:01 |
|   1 |  INDEX FAST FULL SCAN| IND_T1_C2_C1_C3 |  1000K|    23M|   387   (2)| 00:00:01 |
----------------------------------------------------------------------------------------

Let’s hint the optimizer to use the index on the columns C1 and C2:

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'HINT_C1_C2';

SELECT /*+ INDEX(T1 (C1 C2)) */
  C1,
  C2,
  C3
FROM
  T1;

Plan hash value: 3388050039
--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |  1000K|    23M|  1012K  (1)| 00:06:46 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1           |  1000K|    23M|  1012K  (1)| 00:06:46 |
|   2 |   INDEX FULL SCAN           | IND_T1_C1_C2 |  1000K|       |  3026  (10)| 00:00:02 |
--------------------------------------------------------------------------------------------

In the above, the optimizer obeyed the hint, even though the calculated cost from the unhinted plan increased from 387 to 1,012,000, and the optimizer selected to perform an INDEX FULL SCAN operation.

Let’s reverse the order of the columns in the index hint:

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'HINT_C2_C1';

SELECT /*+ INDEX(T1 (C2 C1)) */
  C1,
  C2,
  C3
FROM
  T1;

Execution Plan
----------------------------------------------------------
Plan hash value: 1746297295

------------------------------------------------------------------------------------
| Id  | Operation        | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                 |  1000K|    23M|  4851   (1)| 00:00:01 |
|   1 |  INDEX FULL SCAN | IND_T1_C2_C1_C3 |  1000K|    23M|  4851   (1)| 00:00:01 |
------------------------------------------------------------------------------------

In the above, note that the index on columns C2, C1, and C3 was used, but the cost is now calculated at 4,851 rather than 387 as it was in the unhinted plan. The INDEX FAST FULL SCAN operation is now shown as an INDEX FULL SCAN operation.  So, we have now convinced the optimizer to use an access path that employs single block reads of an index rather than multi-block reads of an index, simply by telling the optimizer to use the index that it would have used without the hint.  Have we confused the optimizer already?  Someone should probably take a look at the 10053 trace files. 

We have an index on columns C2, C1, and C3, but we also have an index on columns C3, C1, and C2. What happens when we specify the columns C3, C1, and C2 in the index hint in that order?

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'HINT_C3_C1_C2';

SELECT /*+ INDEX(T1 (C3 C1 C2)) */
  C1,
  C2,
  C3
FROM
  T1;

Plan hash value: 2273443829

------------------------------------------------------------------------------------
| Id  | Operation        | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                 |  1000K|    23M|  4943   (1)| 00:00:01 |
|   1 |  INDEX FULL SCAN | IND_T1_C3_C1_C2 |  1000K|    23M|  4943   (1)| 00:00:01 |
------------------------------------------------------------------------------------

An index full scan was selected to be performed on the IND_T1_C3_C1_C2 index with a calculated cost of 4,943, rather than using the IND_T1_C2_C1_C3 index that previously resulted in a cost of 4,851 – so the optimizer will not alter the order of the columns in the index hint to reduce the calculated cost.

Let’s try another example where we select all of the columns that are present in the IND_T1_C1_C2_C5 index – will the optimizer use that index, or will it obey the expected behavior of the index hint?:

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'HINT_C1_C2_WC5';

SELECT /*+ INDEX(T1 (C1 C2)) */
  C1,
  C2,
  C5
FROM
  T1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3388050039

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |  1000K|    19M|  1003K  (1)| 00:00:23 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1           |  1000K|    19M|  1003K  (1)| 00:00:23 |
|   2 |   INDEX FULL SCAN           | IND_T1_C1_C2 |  1000K|       |  2750   (1)| 00:00:01 |
--------------------------------------------------------------------------------------------

The index that exactly included the columns specified in the index hint was selected, with a calculated cost of 1,003,000.

So, what happens if we are less specific in the index hint, and just list the first column, where there are two composite indexes that start with the specified column:

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'HINT_C1_WC2_C5';

SELECT /*+ INDEX(T1 (C1)) */
  C1,
  C2,
  C5
FROM
  T1;

Execution Plan
----------------------------------------------------------
Plan hash value: 2942389535

------------------------------------------------------------------------------------
| Id  | Operation        | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                 |  1000K|    19M|  4293   (1)| 00:00:01 |
|   1 |  INDEX FULL SCAN | IND_T1_C1_C2_C5 |  1000K|    19M|  4293   (1)| 00:00:01 |
------------------------------------------------------------------------------------

As shown in the above execution plan, the optimizer arrived at a cost of 4,293 by selecting to use the IND_T1_C1_C2_C5 index, which avoided the access to the T1 table.  So, sometimes the performance will be better with slightly less specific hints.

Out of curiosity, what do you think will happen if we completely eliminate the index hint?  Let’s test:

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'UNHINTED_C1_C2_C5';

SELECT
  C1,
  C2,
  C5
FROM
  T1;

Execution Plan
----------------------------------------------------------
Plan hash value: 2722951733

----------------------------------------------------------------------------------------
| Id  | Operation            | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                 |  1000K|    19M|   343   (3)| 00:00:01 |
|   1 |  INDEX FAST FULL SCAN| IND_T1_C1_C2_C5 |  1000K|    19M|   343   (3)| 00:00:01 |
----------------------------------------------------------------------------------------

In the above execution plan, the IND_T1_C1_C2_C5 index is still selected for use as it was in the previous execution plan, but notice that the access path has changed to an INDEX FAST FULL SCAN operation and the calculated cost dropped from 4,293 to 343.  So, the above output implies that sometimes the performance will be better if we simply do not hint an index access path, if the index access path would have been selected otherwise.  Someone want to take a look at the 10053 trace files and explain why?

If we add a WHERE clause that places a restriction on column C2 to be less than 10, the optimizer could use a couple of different access paths. Let’s specify the columns C1 and C2 in the index hint to see which index is selected:

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'HINT_C1_C2_C2_WHERE';

SELECT /*+ INDEX(T1 (C1 C2)) */
  C1,
  C2,
  C3
FROM
  T1
WHERE
   C2<10;

Plan hash value: 1883798457

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |    10 |   250 |   103   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1           |    10 |   250 |   103   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | IND_T1_C1_C2 |    10 |       |    92   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C2"<10)
       filter("C2"<10)

In the above, an INDEX SKIP SCAN operation was selected because the index with columns C1 and C2 was specified in the hint.

Let’s try another example that possibly might be considered a case where the optimizer disobeys the hint or is free to change the order of the columns specified in the index hint (this might be incorrectly considered an edge case):

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'HINT_C1_C2_C3_C2_WHERE';

SELECT /*+ INDEX(T1 (C1 C2 C3)) */
  C1,
  C2,
  C3
FROM
  T1
WHERE
  C2<10;

Plan hash value: 4150417361

------------------------------------------------------------------------------------
| Id  | Operation        | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                 |    10 |   250 |     3   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IND_T1_C2_C1_C3 |    10 |   250 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("C2"<10)

A quick peek at the above output might suggest that the optimizer could potentially decide to locate an index with columns C1, C2, and C3 in any order – but I do not believe that this is the case. I believe that the optimizer considered the index hint specified in the SQL statement as being invalid (a check of the 10053 trace might confirm).

Let’s create another index and then repeat the above SQL statement:

CREATE INDEX IND_T1_C1_C2_C3 ON T1(C1,C2,C3);

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'HINT_C1_C2_C3_C2_WHERE2';

SELECT /*+ INDEX(T1 (C1 C2 C3)) */
  C1,
  C2,
  C3
FROM
  T1
WHERE
  C2<10;

Plan hash value: 212907557

------------------------------------------------------------------------------------
| Id  | Operation        | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                 |    10 |   250 |    92   (0)| 00:00:01 |
|*  1 |  INDEX SKIP SCAN | IND_T1_C1_C2_C3 |    10 |   250 |    92   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("C2"<10)
       filter("C2"<10)

The index with the columns that matched the order of the columns in the index hint was selected, even though the calculated cost would have been lower if the optimizer were permitted to select any index with the columns listed in the index hint.

What about a case where there is an exact match between an index definition and an index hint, and there is also another index with one additional column which would avoid the table access:

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'HINT_C1_C2_C2_WHERE2';

SELECT /*+ INDEX(T1 (C1 C2)) */
  C1,
  C2,
  C3
FROM
  T1
WHERE
  C2<10;

Plan hash value: 1883798457

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |    10 |   250 |   103   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1           |    10 |   250 |   103   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | IND_T1_C1_C2 |    10 |       |    92   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C2"<10)
       filter("C2"<10)

The index that exactly matched the index hint was selected by the optimizer.

What if we only specify in the index hint a leading column, when there are two indexes with that leading column, one of which allows the optimizer to avoid the table access:

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'HINT_C1_C2_WHERE';

SELECT /*+ INDEX(T1 (C1)) */
  C1,
  C2,
  C3
FROM
  T1
WHERE
  C2<10;

Plan hash value: 212907557

------------------------------------------------------------------------------------
| Id  | Operation        | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                 |    10 |   250 |    92   (0)| 00:00:01 |
|*  1 |  INDEX SKIP SCAN | IND_T1_C1_C2_C3 |    10 |   250 |    92   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("C2"<10)
       filter("C2"<10)

The optimizer selected the lowest cost access path from the two indexes that matched the hint.

What if we specify a column in the index hint that is not listed in the SELECT or WHERE clauses?

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'HINT_C3_C2_WHERE';

SELECT /*+ INDEX(T1 (C3)) */
  C1,
  C2
FROM
  T1
WHERE
  C2<10;

Plan hash value: 1328421701

------------------------------------------------------------------------------------
| Id  | Operation        | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                 |    10 |    90 |  4039   (1)| 00:00:02 |
|*  1 |  INDEX SKIP SCAN | IND_T1_C3_C1_C2 |    10 |    90 |  4039   (1)| 00:00:02 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("C2"<10)
       filter("C2"<10)

The above shows the original execution plan (captured a couple of days ago without column C5 in the table definition, without index IND_T1_C1_C2_C5, and without an enabled 10053 trace), which shows that the optimizer still obeyed the intention of the hint – it found an index that started with the specified column and selected to perform an INDEX SKIP SCAN operation even though column C2, specified in the WHERE clause, is the third column in the index definition.

As luck would have it, with the slightly altered table definition (and possibly different statistics estimates) the execution plan has changed to use an INDEX FULL SCAN operation rather than an INDEX SKIP SCAN operation, and now the execution plan has a higher calculated cost.  So, why did the execution plan change from an INDEX SKIP SCAN?  Here is the current execution plan:

Execution Plan
----------------------------------------------------------
Plan hash value: 2273443829

------------------------------------------------------------------------------------
| Id  | Operation        | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                 |    10 |    90 |  4945   (1)| 00:00:01 |
|*  1 |  INDEX FULL SCAN | IND_T1_C3_C1_C2 |    10 |    90 |  4945   (1)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("C2"<10)
       filter("C2"<10)

So, for the challenge, try to explain the oddities that I pointed out above.  I have not yet reviewed the 10053 trace files, but I will later.

Finding the Next Primary Key Value, a Pre-fixed Solution

January 20, 2012

A request for assistance came in from an ERP mailing list.  The original poster (OP) is running an unspecified version of Oracle Database 9i, and is in need of a solution to generate new part numbers with prefixed characters that describe the type of part, followed by a sequential number that is unique to the prefix.  The prefixes might be PAINT, BAR, BEARING, DRILL, etc.  Sample part numbers might include BAR0599, PAINT012, BEARING012345, etc.

When I first saw the request, my first thought was to create sequences for the different prefixes, similar to the following:

CREATE SEQUENCE PART_PAINT_ID START WITH 13 NOCACHE;
CREATE SEQUENCE PART_BAR_ID START WITH 600 NOCACHE;
CREATE SEQUENCE PART_BEARING_ID START WITH 12346 NOCACHE;
CREATE SEQUENCE PART_DRILL_ID START WITH 999 NOCACHE;
...

Once the above sequences are created, we could then find the next part number with a SQL statement similar to the following:

SELECT
  'PAINT'||PART_PAINT_ID.NEXTVAL NEXT_PART_ID
FROM
  DUAL;

NEXT_PART_ID
------------
PAINT13 

I suspected that there was a catch – for some reason the sample part numbers included a 0 before the sequence number, and I assumed that there could be a variable number of 0 digits before that sequence number for the different prefixes.  To fix the above, we might try working with the LPAD function to add leading zeros to the sequence number:

SELECT
  'PAINT'||LPAD(TO_CHAR(PART_PAINT_ID.NEXTVAL), 3, '0') NEXT_PART_ID
FROM
  DUAL;

NEXT_PAR
--------
PAINT014 

Need more leading zeros?  No problem, just adjust the number in the LPAD function:

SELECT
  'PAINT'||LPAD(TO_CHAR(PART_PAINT_ID.NEXTVAL), 6, '0') NEXT_PART_ID
FROM
  DUAL;

NEXT_PART_I
-----------
PAINT000015 

Need fewer zeros?:

SELECT
  'PAINT'||LPAD(TO_CHAR(PART_PAINT_ID.NEXTVAL), 1, '0') NEXT_PART_ID
FROM
  DUAL;

NEXT_P
------
PAINT1 

The above shows the first of several potential problems with this approach.  What else could go wrong?  What if a smart person decides that he does not need this “crutch” solution and creates 30 part numbers using the method of best guess or cheat sheet in the side drawer?  Because these are primary key values, the smart person might cause a number of problems that might not be detected for some time… until the Oracle sequence reaches one of the unexpected sequence numbers that were already used.

In addition to the suggestion of Oracle sequences, I offered a couple of other suggestions.  The ERP system offers a macro language that is a lot like VBScript.  The OP could create a VBScript that builds a HTML web page in real time, or possibly pull the next sequence number from a ASP (or similar) web page.  As an example of a HTML web page built in real time, this is an example that I created roughly three years ago:

Dim objIE
Dim objShell
Dim strHTML
Dim intFlag

On Error Resume Next

Set objShell = CreateObject("WScript.Shell")
strHTML = strHTML & "<form name=""Visual"">" & vbCrLf
strHTML = strHTML & "<input type=hidden id=txtOK value="" "">" & vbCrLf

strHTML = strHTML & "<table>" & vbCrLf
strHTML = strHTML & "<tr><td>Component<td><select size=""1"" id=""cboComponent"" name=""cboComponent"" style=""width:150"">" & vbCrLf
strHTML = strHTML & "<option value=""ACTIVATOR"">ACTIVATOR</option>" & vbCrLf
strHTML = strHTML & "<option value=""ACCELERATOR"">ACCELERATOR</option>" & vbCrLf
strHTML = strHTML & "<option value=""CATALYST"">CATALYST</option>" & vbCrLf
strHTML = strHTML & "<option value=""EPOXY PRIMER"">EPOXY PRIMER</option>" & vbCrLf
strHTML = strHTML & "<option value=""PRIMER"">PRIMER</option>" & vbCrLf
strHTML = strHTML & "<option value=""REDUCER"">REDUCER</option>" & vbCrLf
strHTML = strHTML & "<option value=""TOP COAT"">TOP COAT</option>" & vbCrLf
strHTML = strHTML & "</select></td></tr>" & vbCrLf

strHTML = strHTML & "<tr><td>Vendor<td><select size=""1"" id=""cboVendor"" name=""cboVendor"" style=""width:150"">" & vbCrLf
strHTML = strHTML & "<option value=""DUPONT"">DUPONT</option>" & vbCrLf
strHTML = strHTML & "<option value=""LILLY"">LILLY</option>" & vbCrLf
strHTML = strHTML & "<option value=""NILES CHEMICAL"">NILES CHEMICAL</option>" & vbCrLf
strHTML = strHTML & "<option value=""MANITOWOC"">MANITOWOC</option>" & vbCrLf
strHTML = strHTML & "<option value=""MAUTZ"">MAUTZ</option>" & vbCrLf
strHTML = strHTML & "<option value=""PAINTS AND SOLVENTS"">PAINTS AND SOLVENTS</option>" & vbCrLf
strHTML = strHTML & "<option value=""SHEBOYGAN"">SHEBOYGAN</option>" & vbCrLf
strHTML = strHTML & "</select></td></tr>" & vbCrLf

strHTML = strHTML & "<tr><td>Type<td><select size=""1"" id=""cboType"" name=""cboType"" style=""width:150"">" & vbCrLf
strHTML = strHTML & "<option value=""FLAT"">FLAT</option>" & vbCrLf
strHTML = strHTML & "<option value=""GLOSS"">GLOSS</option>" & vbCrLf
strHTML = strHTML & "<option value=""MED. GLOSS"">MED. GLOSS</option>" & vbCrLf
strHTML = strHTML & "</select></td></tr>" & vbCrLf

strHTML = strHTML & "<tr><td>Color<td><select size=""1"" id=""cboColor"" name=""cboColor"" style=""width:150"">" & vbCrLf
strHTML = strHTML & "<option value=""RED"">RED</option>" & vbCrLf
strHTML = strHTML & "<option value=""YELLOW"">YELLOW</option>" & vbCrLf
strHTML = strHTML & "<option value=""GREEN"">GREEN</option>" & vbCrLf
strHTML = strHTML & "<option value=""BLUE"">BLUE</option>" & vbCrLf
strHTML = strHTML & "<option value=""BLACK"">BLACK</option>" & vbCrLf
strHTML = strHTML & "<option value=""WHITE"">WHITE</option>" & vbCrLf
strHTML = strHTML & "<option value=""GRAY"">GRAY</option>" & vbCrLf
strHTML = strHTML & "</select></td></tr>" & vbCrLf
strHTML = strHTML & "</table>" & vbCrLf

strHTML = strHTML & "<p><center><input type=button value=""OK"" id=cmdOK onclick=""document.getElementById('txtOK').value='OK';""></center>" & vbCrLf
strHTML = strHTML & "</form>" & vbCrLf

Set objIE = CreateObject("InternetExplorer.Application")
objIE.Navigate "about:blank"
objIE.Document.Body.InnerHTML = strHTML
objIE.Document.Title="Get Part Info"
objIE.Left = 0
objIE.Top = 0
objIE.Width = 400
objIE.Height = 400
objIE.Statusbar = False
objIE.Menubar = False
objIE.Toolbar = False

objIE.Visible = True

Do While objIE.Busy <> False
    objShell.Sleep 200
Loop

intFlag = 0

'loop until the button is clicked
Do While intFlag = 0
    If Err <> 0 Then
        IntFlag = -1
    End If   
    If objIE is Nothing Then
        'User closed ID
        intFlag = -1
    Else
        If objIE.Document.All.txtOK.Value <> " " Then
            intFlag = 1
        End If
    End If
    'objShell.Sleep 250 'Throws an error?
Loop

If intFlag = 1 Then
    'Copy in the values from the web page
    USER_1 = objIE.Document.Body.All.cboComponent.Value
    USER_2 = objIE.Document.Body.All.cboVendor.Value
    USER_3 = objIE.Document.Body.All.cboType.Value
    USER_4 = objIE.Document.Body.All.cboColor.Value

    objIE.Quit
End If

Set objIE = Nothing
Set objShell = Nothing
 

The result of the above VBScript is an interactive interface that appears similar to the following:

Another option that I suggested to the OP is to use Excel to keep track of the last sequence number for each prefix – and use an Excel dialog displayed from a VBScript macro.  Roughly three years ago I created a sample macro with the following code:

Dim objExcel
Dim objForm
Dim objShell

On Error Resume Next

Set objExcel = CreateObject("Excel.Application")

'with the help of custom program, set a 1 second delay, then force the window to the top
Set objShell = CreateObject("WScript.Shell")
objShell.Run("C:\BringToTop.exe " & Chr(34) & "Paint Naming" & Chr(34) & " 2")
Set objShell = Nothing

With objExcel
    .Workbooks.Open "C:\ExcelMacroDialog.xls"

    If .Sheets("CalculateArea").Cells(1, 1).Value <> "" Then
        ID = .Sheets("CalculateArea").Cells(1, 1).Value
        DESCRIPTION = .Sheets("CalculateArea").Cells(2, 1).Value
        PRODUCT_CODE = .Sheets("CalculateArea").Cells(3, 1).Value
        COMMODITY_CODE = .Sheets("CalculateArea").Cells(4, 1).Value
        USER_5 = .Sheets("CalculateArea").Cells(5, 1).Value
        PURCHASED = True
        FABRICATED = False
    End If
End With

objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.Saved = True
objExcel.Quit
Set objExcel = Nothing  

The above macro is quite short, because most of the work is performed in the Excel spreadsheet.  The resulting Excel dialog appeared similar to this:

My first suggestion to the OP, not knowing the full scope of the problem, was to try coding a VBScript macro similar to the following:

Dim strPartID
Dim strPartIDNew
Dim strNumberOld
Dim strNumberNew
Dim i

strPartID = PART_ID
strNumberOld = ""

'strPartID = "PAINT0599"  'Remove this line after testing
strPartID = "PAINT0089"  'Remove this line after testing

For i = Len(strPartID) to 1 Step -1
  If (Mid(strPartID, i, 1) >= "0") And (Mid(strPartID, i, 1) <= "9") Then
    strNumberOld = Mid(strPartID, i, 1) & strNumberOld
  Else
    strNumberNew = cStr(cInt(strNumberOld) + 1)

    strPartIDNew = Left(strPartID, i)
    If Len(strNumberOld) > Len(strNumberNew) Then
      'Add Padding 0s
      strPartIDNew = strPartIDNew & String((Len(strNumberOld) - Len(strNumberNew)), "0")
    End If

    strPartIDNew = strPartIDNew & strNumberNew

    Exit For
  End If
Next

If strPartIDNew <> "" Then
  Msgbox "The New Part ID is " & strPartIDNew
Else
  Msgbox "Not a Valid Starting Point" & strPartID
End If 

The intention of the above macro is to locate the number 89 in the supplied strPartID variable, recognize that a 4 digit serial number is expected, and output:

The New Part ID is PAINT0090 

Nice, but that is not what the OP needs.  The highest currently sequenced number will not be provided – that value must be looked up in the database.  So close…

Let’s try a different approach, starting by creating a sample table with three sequences of part numbers with different prefixes:

CREATE TABLE T1 (
  ID VARCHAR2(30),
  DESCRIPTION VARCHAR2(40),
  PRIMARY KEY(ID));

INSERT INTO
  T1
SELECT
  'PAINT'||LPAD(TO_CHAR(ROWNUM), 6, '0') ID,
  'TESTING ' || TO_CHAR(ROWNUM) DESCRIPTION
FROM
  DUAL
CONNECT BY
  LEVEL<=35000;

INSERT INTO
  T1
SELECT
  'BAR'||LPAD(TO_CHAR(ROWNUM), 6, '0') ID,
  'TESTING ' || TO_CHAR(ROWNUM) DESCRIPTION
FROM
  DUAL
CONNECT BY
  LEVEL<=45000;

INSERT INTO
  T1
SELECT
  'BEARING'||LPAD(TO_CHAR(ROWNUM), 6, '0') ID,
  'TESTING ' || TO_CHAR(ROWNUM) DESCRIPTION
FROM
  DUAL
CONNECT BY
  LEVEL<=888;

COMMIT; 

Let’s find the next sequence number for the BAR prefix:

SELECT
  MAX(TO_NUMBER(SUBSTR(ID,4))) + 1 NN
FROM
  T1
WHERE
  ID BETWEEN 'BAR0' AND 'BAR99999999';

        NN
----------
     45001 

As long as ALL of the characters after the BAR keyword prefix are numbers, the above would tell us that the next number with BAR as the prefix is 45001.  On Oracle Database 10.1 and higher it would be a good idea to add an additional predicate to the WHERE clause that uses regular expressions to avoid potential problems where some unrelated ID column values start with the letters BAR, a number character, and then at some position to the right contain a letter character (that condition would cause the above SQL statement to fail).

Building onto the above SQL statement, we could just retrieve the next part number in the sequence from the database, when provided any existing prefixed part number as the starting point:

SELECT
  'BAR' || LPAD(TO_CHAR(MAX(TO_NUMBER(SUBSTR(ID,4))) + 1), 6, '0') NEXT_PART_ID
FROM
  T1
WHERE
  ID BETWEEN 'BAR0' AND 'BAR99999999';

NEXT_PART
---------
BAR045001 

We are able to take the above SQL statement and incorporate it into a VBScript macro to find the next prefixed sequential number for the primary key column:

Dim strPartID
Dim strPartIDNew
Dim strNumberOld
Dim strNumberNew
Dim i
Dim strUserName
Dim strPassword
Dim strDatabase
Dim strSQL
Dim dbDatabase
Dim snpData

On Error Resume Next

strUsername = "MyUserID"
strPassword = "MyPassword"
strDatabase = "MyDatabase"

Set snpData = CreateObject("ADODB.Recordset")
Set dbDatabase = CreateObject("ADODB.Connection")

dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUserName & ";Password=" & strPassword & ";"
dbDatabase.Open

strNumberOld = ""

'strPartID = PART_ID
strPartID = "BEARING0599"  'Remove when finished testing
'strPartID = "BAR0089"  'Remove when finished testing
'strPartID = "PAINT0089"  'Remove when finished testing

For i = Len(strPartID) to 1 Step -1
  If (Mid(strPartID, i, 1) >= "0") And (Mid(strPartID, i, 1) <= "9") Then
    strNumberOld = Mid(strPartID, i, 1) & strNumberOld
  Else
    strPartIDNew = Left(strPartID, i)

    strSQL = "SELECT" & VBCrLf
    strSQL = strSQL & "  '" & strPartIDNew & "' || LPAD(TO_CHAR(MAX(TO_NUMBER(SUBSTR(ID," & (i+1) &"))) + 1), 6, '0') NEXT_PART_ID" & VBCrLf
    strSQL = strSQL & "FROM" & VBCrLf
    strSQL = strSQL & "  T1" & VBCrLf
    strSQL = strSQL & "WHERE" & VBCrLf
    strSQL = strSQL & "  ID BETWEEN '" & strPartIDNew & "0' AND '" & strPartIDNew & "99999999'"

    snpData.Open strSQL, dbDatabase

    If snpData.State = 1 Then
      If Not(snpData.EOF) Then
        strPartIDNew = snpData("next_part_id")
      End If
      snpData.Close
    End If
    Exit For
  End If
Next

If strPartIDNew <> "" Then
  Msgbox "The New Part ID is " & strPartIDNew
Else
  Msgbox "Not a Valid Starting Point" & strPartID
End If

dbDatabase.Close
Set snpData = Nothing
Set dbDatabase = Nothing 

The OP put together a parallel solution that also used a VBScript macro.  The macro sent a SQL statement very similar to the following to the database:

SELECT
  ID
FROM
  T1
WHERE
  ID LIKE 'BAR%'; 

In the macro code the OP parsed each of the returned ID values to determine the highest sequence number, added 1 to that value, padded the new highest sequence number with “0′ digits and output the result.  Most likely due to curiosity, the OP asked why I did not simply use his VBScript macro as part of my proposed solution.  What reasons do you think that I gave to the OP?

What Number Immediately Follows 3,999? Oracle Database Refuses to Answer

January 16, 2012

I put together a test case to demonstrate how the physical reads autotrace statistic could exceed the consistent gets autotrace statistic if a single-pass or multi-pass workarea execution were performed during the execution of the SQL statement.  If you are interested, you can see the test case in this recent OTN thread.  If I recall correctly, index pre-fetching could also result in a similar situation where the physical reads autotrace statistic could exceed the consistent gets autotrace statistic.

The result in the OTN test case left me a little concerned.  It might not be well known, however it is possible to instruct Oracle Database to format normal base 10 numbers as Roman numerals with the RN format specification.  For example:

SELECT
  TO_CHAR(1, 'RN') ROMAN_NUMBER
FROM
  DUAL;

ROMAN_NUMBER
---------------
              I

SELECT
  TO_CHAR(12, 'RN') ROMAN_NUMBER
FROM
  DUAL;

ROMAN_NUMBER
---------------
            XII

SELECT
  TO_CHAR(123, 'RN') ROMAN_NUMBER
FROM
  DUAL;

ROMAN_NUMBER
---------------
         CXXIII

SELECT
  TO_CHAR(1234, 'RN') ROMAN_NUMBER
FROM
  DUAL;

ROMAN_NUMBER
---------------
       MCCXXXIV 

I personally think that is a neat feature.  In the OTN test case I created a table that would hopefully contain the first 9,999,999 Roman numbers:

CREATE TABLE T1 (
  C1 NUMBER NOT NULL,
  C2 NUMBER NOT NULL,
  C3 VARCHAR2(30) NOT NULL,
  C4 VARCHAR2(200));

INSERT INTO T1
SELECT
  MOD(ROWNUM-1, 90) * 4 C1,
  ROWNUM - 1 C2,
  TO_CHAR(ROWNUM - 1, 'RN') C3,
  LPAD('A',200,'A') C4
FROM
  (SELECT
     ROWNUM RN
   FROM
     DUAL
   CONNECT BY
     LEVEL<=1000000),
  (SELECT
     ROWNUM RN
   FROM
     DUAL
   CONNECT BY
     LEVEL<=10);

COMMIT;

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

As part of the OTN test case, I thought that I would demonstrate a non-optimal work area execution by determining how many unique Roman numbers made it into the T1 test table:

SELECT
  COUNT(DISTINCT C3)
FROM
  T1;

COUNT(DISTINCTC3)
-----------------
             4000 

Just 4,000?  There are 10,000,000 rows in that test table.  So, maybe we should ask Oracle Database what number immediately follows 3,999 by querying the table:

SELECT
  C2,
  C3
FROM
  T1
WHERE
  C2 BETWEEN 3950 AND 4050
ORDER BY
  C2;

---------- ------------------------------
      3950          MMMCML
      3951         MMMCMLI
      3952        MMMCMLII
      3953       MMMCMLIII
      3954        MMMCMLIV
      3955         MMMCMLV
      3956        MMMCMLVI
      3957       MMMCMLVII
      3958      MMMCMLVIII
      3959        MMMCMLIX
      3960         MMMCMLX
      3961        MMMCMLXI
      3962       MMMCMLXII
      3963      MMMCMLXIII
      3964       MMMCMLXIV
      3965        MMMCMLXV
      3966       MMMCMLXVI
      3967      MMMCMLXVII
      3968     MMMCMLXVIII
      3969       MMMCMLXIX
      3970        MMMCMLXX
      3971       MMMCMLXXI
      3972      MMMCMLXXII
      3973     MMMCMLXXIII
      3974      MMMCMLXXIV
      3975       MMMCMLXXV
      3976      MMMCMLXXVI
      3977     MMMCMLXXVII
      3978    MMMCMLXXVIII
      3979      MMMCMLXXIX
      3980       MMMCMLXXX
      3981      MMMCMLXXXI
      3982     MMMCMLXXXII
      3983    MMMCMLXXXIII
      3984     MMMCMLXXXIV
      3985      MMMCMLXXXV
      3986     MMMCMLXXXVI
      3987    MMMCMLXXXVII
      3988   MMMCMLXXXVIII
      3989     MMMCMLXXXIX
      3990         MMMCMXC
      3991        MMMCMXCI
      3992       MMMCMXCII
      3993      MMMCMXCIII
      3994       MMMCMXCIV
      3995        MMMCMXCV
      3996       MMMCMXCVI
      3997      MMMCMXCVII
      3998     MMMCMXCVIII
      3999       MMMCMXCIX
      4000 ###############
      4001 ###############
      4002 ###############
      4003 ###############
      4004 ###############
      4005 ###############
      4006 ###############
      4007 ###############
      4008 ###############
      4009 ###############
      4010 ###############
      4011 ###############
      4012 ###############
      4013 ###############
      4014 ###############
      4015 ###############
      4016 ###############
      4017 ###############
      4018 ###############
      4019 ###############
      4020 ###############
      4021 ###############
      4022 ###############
      4023 ###############
      4024 ###############
      4025 ###############
      4026 ###############
      4027 ###############
      4028 ###############
      4029 ###############
      4030 ###############
      4031 ###############
      4032 ###############
      4033 ###############
      4034 ###############
      4035 ###############
      4036 ###############
      4037 ###############
      4038 ###############
      4039 ###############
      4040 ###############
      4041 ###############
      4042 ###############
      4043 ###############
      4044 ###############
      4045 ###############
      4046 ###############
      4047 ###############
      4048 ###############
      4049 ###############
      4050 ############### 

You heard it here first, in Roman times 4,000 is equivalent to infinity.  An obvious extension to this rule is that in Roman times the value of PI had exactly 3999 digits to the right of the decimal point.  

OT: Do Search Terms Describe the Visitor?

January 12, 2012

I thought that I would start this slightly off topic blog article with a bit of humor.  Seven months ago I wrote a blog article that refuses to move from the first position in the most visited articles on this blog.  In the process of trying to understand why a mathematics focused article is the most popular article on an Oracle Database specific blog, I regularly review the search keywords that bring people to my blog.  Yesterday alone, the search keywords that apparently pointed to the popular blog article included (not a complete list):

• finding distance between two points lat long (2)
• distance spherical points coordinates oracle (2)
• find distance between two longitude latitude points (1)
• calculate distance between points in a feature (1)
• calculate speed longitude latitude (1)
• distance between two lattitude points (1)
• how to get distance between two latitudes in meter (1)
• access table that converts latitude and longitude (1)
• calculate time and distance between two longitudes (1)
• distance between two points latitude and longitude transact sql (1)
• converting to oracle degree minutes (1)
• distance between latitude (1)
• select sdo_geom.sdo_distance (1)
• oracle sdo_geom.dos_distance (1)
• calculate distance longitude latitude (1)
• method of measuring distance using plain (1)
• oracle spatial calculate distance (1)
• how to calculate distance between two latitude and longitude (1)
• to calculate distance between two points (1)
• oracle spatial sort lat lon coordinates (1)
• longitude and latitude distance between 2 points (1)
• solve this:find distance between (35 degrees 21′) and (29 degrees 57′) and given that the radius is 3960 (1)
• distance between two points wordpress (1)
• how to get distance between two longitudes (1)
• calculate distance between two lat long points (1)
• how to find distance between points of longitude (1)
• sql function calculate distance gps kilometers (1)
• calculate distance between lattitute longitude (1)
• excel calculate distance from lat long (1)
• calculate distance from lat long sql (1) 

I think that the above keywords point to one and only one conclusion: the majority of the visitors to this blog are lost, know only their latitude and longitude coordinates (and the coordinates of where they are supposed to be), and are able to VPN in to their Oracle Database at work to determine how long it will take to become unlost. 

While on the topic of interesting search keywords, I thought that I would mention a favorite search keyword that appeared in the WordPress statistics.  My all time favorite interesting search keyword that was used to access this blog appeared shortly after I reviewed a red covered Oracle related book – the search keywords were “Charles Hooper is getting sued”.  Needless to say, it is interesting that the search keywords found an article on my blog.

—

Moving on to the point of this blog article, I have also noticed a curious trend in some of the search keywords.  WordPress makes it a bit diffcult to go back in time to tabulate somewhat popular search keywords when there are a lot of commonly used keywords that access this site.  However, with a bit of effort, I attempted to locate search keywords that share a common pattern.  Those search keywords that I located (with the minimum number of hits indicated) follow: 

• oracle core essential internals for dbas and developers pdf (8)
• oracle core essential internals for dbas and developers download (6)
• oracle core: essential internals for dbas and developers pdf (4)
• oracle core: essential internals for dbas and developers download (1)
• oracle tuning the definitive reference second edition pdf (99)
• oracle tuning: the definitive reference pdf (3)
• oracle tuning the definitive reference pdf (1)
• oracle tuning the definitive reference 2nd edition pdf download (2)
• oracle performance firefighting pdf (18)
• expert oracle practices download (1)
• apress.oracle.core.essential.internals.for.dbas.and.developers.nov.2011.rar (1)

Undoubtedly, in the above cases the searcher likely found my review of a particular book.  The above list has me wondering about a couple of items.  I was a full time college student for five years to obtain my degree.  I remember the pain of signing a check for what seemed to be (U.S.) $500 every semester – it did not seem to matter much if I was buying mathematics books (a $204 Calculus book from Amazon oddly seems approproate here) , computer books, or psychology books – the total was always about the same.  Was that money for books a waste?  Maybe I could have just found the book out on the Internet somewhere… they had that neat Gopher tool way back then, but not the WYSIWYG interface that is common now.

I am left wondering what the people searching for the above keywords might have been thinking.  I assume that some of the searchers have spent some time in college, and might even be Oracle developers or DBAs.  So, do you think that search terms describe vistors?  If so, how do you think the above search keywords might describe the searchers?

Dumping Trace Events – What You See is Not Necessarily What You Get

January 9, 2012

A recent thread in the comp.databases.oracle.server Usenet group brought back memories of when Randolf Geist and I worked on the two chapters for the “Expert Oracle Practices” book.  In the chapters, among other things, we demonstrated how to enable 10046 trace files for sessions using various approaches (some of the approaches change the SQL_TRACE, SQL_TRACE_WAITS, and SQL_TRACE_BINDS columns in V$SESSION to indicate that a 10046 trace is active, and other methods do not), and how to use ORADEBUG to see which events are enabled in other sessions.

The OP in the thread mentioned that the ORADEBUG EVENTDUMP session command, when executed in SQL*Plus by a SYSDBA user, showed no events enabled for another session when the DBMS_MONITOR package was used to enable a 10046 trace at level 12 for the other session.  I have a vague memory of reading (somewhere) that the ORADEBUG EVENTDUMP session command will only show newly enabled events for another session after that other session issues another call to the database (parse, execute, fetch, etc.).  In the Usenet group thread I stated something that is slightly less complete: “I believe that the session with the trace enabled through DBMS_MONITOR must execute at least one SQL statement after tracing is enabled for the session, before ORADEBUG will report that the trace is enabled.”

In the Usenet thread I put together a quick test case script that demonstrated that after the second session (Session 1) issued a SELECT statement, the first session (Session 2) was able to issue a ORADEBUG EVENTDUMP session command and determine that a 10046 trace at level 12 was enabled for the other session.  I believe that the OP answered his own question after a bit of experimentation and searching.  I thought that I would provide an extended version of the test case script in case anyone would like to experiment a bit.  In this test case, Session 1 will be the target of the tracing – that session must be able to query V$SESSION and V$PROCESS during the script execution, while Session 2 will be connected as the SYS user.  In the test case script I have removed the SQL> prompts to make it easier for you to copy and paste the scripts into SQL*Plus, and when the returned output is important, that information also appears in the test case script (all commands executed begin with UPPERCASE keywords).

In Session 1, execute the following SQL statement to pick up the SID, SERIAL# and PID for Session 1, along with the columns in V$SESSION that indicate whether or not a 10046 trace at level 1, 4, 8, or 12 is enabled.  The SID and SERIAL# will be used in Session 2 to enable a trace in Session 1, and the PID will be used with ORADEBUG in Session 2:

SELECT
  S.SID,
  S.SERIAL#,
  P.PID,
  S.SQL_TRACE,
  S.SQL_TRACE_WAITS,
  S.SQL_TRACE_BINDS
FROM
  V$SESSION S,
  V$PROCESS P
WHERE
  S.SID=(SELECT SID FROM V$MYSTAT WHERE ROWNUM=1)
  AND S.PADDR=P.ADDR;

SID    SERIAL#        PID SQL_TRAC SQL_T SQL_T
--- ---------- ---------- -------- ----- -----
  4        143         24 DISABLED FALSE FALSE 

As can be determined by the above output, the SID is 4, PID is 24, and a 10046 trace is not enabled for the session (fair warning, the SQL_TRACE column can be misleading).

In Session 2, enable a 10046 trace using the DBMS_MONITOR package for Session 1 at level 12, and dump the events for Session 1 (note that this test case script is being executed on a Windows server with Oracle Database 11.2.0.2):

EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(SESSION_ID=>4,SERIAL_NUM=>143,WAITS=>TRUE,BINDS=>TRUE)

ORADEBUG SETORAPID 24

ORADEBUG EVENTDUMP session
Statement processed. 

The output of the ORADEBUG EVENTDUMP session command is significant – the Statement processed. note indicates that the statement executed successfully with no return output. 

In Session 1, let’s execute the same SQL statement as was executed earlier, to not only confirm that the DBMS_MONITOR.SESSION_TRACE_ENABLE call executed by Session 2 was successful, but also to issue another database call in the session (the significance will be seen later):

SELECT
  S.SID,
  S.SERIAL#,
  P.PID,
  S.SQL_TRACE,
  S.SQL_TRACE_WAITS,
  S.SQL_TRACE_BINDS
FROM
  V$SESSION S,
  V$PROCESS P
WHERE
  S.SID=(SELECT SID FROM V$MYSTAT WHERE ROWNUM=1)
  AND S.PADDR=P.ADDR;

SID    SERIAL#        PID SQL_TRAC SQL_T SQL_T
--- ---------- ---------- -------- ----- -----
  4        143         24 ENABLED  TRUE  TRUE

The above output shows that the DBMS_MONITOR.SESSION_TRACE_ENABLE call was successful, as indicated by the changed values in the SQL_TRACE, SQL_TRACE_WAITS, and SQL_TRACE_BINDS columns – a level 12 10046 trace is enabled.

In Session 2, let’s check again which events are enabled for Session 1:

ORADEBUG EVENTDUMP session
sql_trace level=12 

Notice in the above output that ORADEBUG now indicates that a 10046 trace at level 12 is enabled for Session 1, because another database call (initiated when the SQL statement was executed) was performed in Session 1 after the trace was enabled.

—

Let’s try a bit more experimentation.  In Session 2, let’s disable the trace in Session 1 using the DBMS_MONITOR package, and verify whether or not tracing is enabled in Session 1 by using ORADEBUG:

EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE(SESSION_ID=>4,SERIAL_NUM=>143)
PL/SQL procedure successfully completed.

ORADEBUG EVENTDUMP session
sql_trace level=12 

The above output shows that ORADEBUG still indicates that a 10046 trace at level 12 is enabled for Session 1.  In Session 2, let’s check the SQL_TRACE, SQL_TRACE_WAITS, and SQL_TRACE_BINDS columns for Session 1:

SELECT
  S.SID,
  S.SERIAL#,
  P.PID,
  S.SQL_TRACE,
  S.SQL_TRACE_WAITS,
  S.SQL_TRACE_BINDS
FROM
  V$SESSION S,
  V$PROCESS P
WHERE
  S.SID=4
  AND S.PADDR=P.ADDR;

SID    SERIAL#        PID SQL_TRAC SQL_T SQL_T
--- ---------- ---------- -------- ----- -----
  4        143         24 DISABLED FALSE FALSE

ORADEBUG EVENTDUMP session
sql_trace level=12 

So, the query of V$SESSION indicates that a 10046 trace for Session 1 is disabled, yet ORADEBUG still insists that a 10046 trace at level 12 is enabled.  WYSIWYG (What You See Is What You Get) is taking a bit of a vacation.  Let’s issue another database call in Session 1 so that ORADEBUG provides correct output about whether or not a 10046 trace is enabled.  In Session 1:

SELECT
  S.SID,
  S.SERIAL#,
  P.PID,
  S.SQL_TRACE,
  S.SQL_TRACE_WAITS,
  S.SQL_TRACE_BINDS
FROM
  V$SESSION S,
  V$PROCESS P
WHERE
  S.SID=(SELECT SID FROM V$MYSTAT WHERE ROWNUM=1)
  AND S.PADDR=P.ADDR;

SID    SERIAL#        PID SQL_TRAC SQL_T SQL_T
--- ---------- ---------- -------- ----- -----
  4        143         24 DISABLED FALSE FALSE 

Let’s confirm in Session 2 that ORADEBUG now reports that a 10046 trace is not enabled in Session 1:

ORADEBUG EVENTDUMP session
Statement processed.  

OK, but what happens if we use ORADEBUG in Session 2 to enable a 10046 trace is Session 1?  In Session 2:

ORADEBUG session_event 10046 trace name context forever,level 12
Statement processed.

ORADEBUG EVENTDUMP session
sql_trace level=12 

The above shows that if ORADEBUG enables a 10046 trace in Session 1, the ORADEBUG EVENTDUMP session command will correctly indicate that a 10046 trace is enabled for Session 1.  Nice, but what about the SQL_TRACE, SQL_TRACE_WAITS, and SQL_TRACE_BINDS columns in V$SESSION for Session 1?  Let’s check using Session 2:

SELECT
  S.SID,
  S.SERIAL#,
  P.PID,
  S.SQL_TRACE,
  S.SQL_TRACE_WAITS,
  S.SQL_TRACE_BINDS
FROM
  V$SESSION S,
  V$PROCESS P
WHERE
  S.SID=4
  AND S.PADDR=P.ADDR;

SID    SERIAL#        PID SQL_TRAC SQL_T SQL_T
--- ---------- ---------- -------- ----- -----
  4        143         24 DISABLED FALSE FALSE 

The above output suggests that a 10046 trace at level 12 is not enabled for Session 1.  Let’s check again from within Session 1 and then determine today’s date (if that second SQL statement appears in the 10046 trace file, then we know that the 10046 trace was in fact enabled for Session 1):

SELECT
  S.SID,
  S.SERIAL#,
  P.PID,
  S.SQL_TRACE,
  S.SQL_TRACE_WAITS,
  S.SQL_TRACE_BINDS
FROM
  V$SESSION S,
  V$PROCESS P
WHERE
  S.SID=(SELECT SID FROM V$MYSTAT WHERE ROWNUM=1)
  AND S.PADDR=P.ADDR;

SID    SERIAL#        PID SQL_TRAC SQL_T SQL_T
--- ---------- ---------- -------- ----- -----
  4        143         24 DISABLED FALSE FALSE

SELECT SYSDATE FROM DUAL;

SYSDATE
---------
08-JAN-12  

The above still suggests that a 10046 trace is not enabled for Session 1 and that today’s date is January 8, 2012.  Let’s jump back to Session 2 and disable the 10046 trace using ORADEBUG:

ORADEBUG session_event 10046 trace name context off
Statement processed. 

Out of curiosity, let’s repeat a portion of the initial test, just to confirm that the initial test results were not a fluke:

EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(SESSION_ID=>4,SERIAL_NUM=>143,WAITS=>TRUE,BINDS=>TRUE)

ORADEBUG EVENTDUMP session
Statement processed.

SELECT
  S.SID,
  S.SERIAL#,
  P.PID,
  S.SQL_TRACE,
  S.SQL_TRACE_WAITS,
  S.SQL_TRACE_BINDS
FROM
  V$SESSION S,
  V$PROCESS P
WHERE
  S.SID=4
  AND S.PADDR=P.ADDR;

SID    SERIAL#        PID SQL_TRAC SQL_T SQL_T
--- ---------- ---------- -------- ----- -----
  4        143         24 ENABLED  TRUE  TRUE

ORADEBUG EVENTDUMP session
Statement processed. 

Once again, the above shows that ORADEBUG does not initially report that a 10046 trace is enabled in Session 1 when the officially supported method for enabling a 10046 trace in another session is used.  Let’s disable the 10046 trace using the DBMS_MONITOR package and then confirm that ORADEBUG still indicates that a 10046 trace is not enabled in Session 1:

EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE(SESSION_ID=>4,SERIAL_NUM=>143)

ORADEBUG EVENTDUMP session
Statement processed. 

So, what about the unsupported method of enabling a 10046 trace in another session, using SYS.DBMS_SYSTEM.SET_EV?  In Session 2:

EXEC SYS.DBMS_SYSTEM.SET_EV(4, 143, 10046, 12, '')

SELECT
  S.SID,
  S.SERIAL#,
  P.PID,
  S.SQL_TRACE,
  S.SQL_TRACE_WAITS,
  S.SQL_TRACE_BINDS
FROM
  V$SESSION S,
  V$PROCESS P
WHERE
  S.SID=4
  AND S.PADDR=P.ADDR;

SID    SERIAL#        PID SQL_TRAC SQL_T SQL_T
--- ---------- ---------- -------- ----- -----
  4        143         24 ENABLED  TRUE  TRUE

ORADEBUG EVENTDUMP session
Statement processed. 

The SYS.DBMS_SYSTEM.SET_EV call changed the value of the columns in V$SESSION for Session 1, but still did not alter the output of ORADEBUG EVENTDUMP session.

One final test in Session 2:

ORADEBUG session_event 10046 trace name context forever,level 12
Statement processed.

ORADEBUG EVENTDUMP session
sql_trace level=12 

Once again, if we enable the 10046 trace using ORADEBUG, then ORADEBUG is able to show that the trace is enabled for Session 1.

———————————————

At this point you might be curious why I mentioned that the above test case script was performed on a Windows server running Oracle Database 11.2.0.2.  Does the Oracle Database version make a difference in the results?  Does the operating system (Windows, Linux, or Unix) make a difference in the results?  It appears that a part 2 is required for this blog article, where I will compare the above results with the results obtained with 10.2.0.5 on a Windows server and 11.1.0.6 on a Linux server.

———————————————

You are probably curious to see the contents of the trace file for Session 1.  Here is the trace file (if you need help in reading the trace file, see this three part article series):

*** 2012-01-08 09:47:51.686
*** SESSION ID:(4.143) 2012-01-08 09:47:51.686
*** CLIENT ID:() 2012-01-08 09:47:51.686
*** SERVICE NAME:(OR1122) 2012-01-08 09:47:51.686
*** MODULE NAME:(SQL*Plus) 2012-01-08 09:47:51.686
*** ACTION NAME:() 2012-01-08 09:47:51.686

Received ORADEBUG command (#1) 'EVENTDUMP session' from process 'Windows thread id: 12292, image: <none>'
Dumping Event (group=SESSION)

*** 2012-01-08 09:47:51.686
Finished processing ORADEBUG command (#1) 'EVENTDUMP session'

*** 2012-01-08 09:48:18.426
Received ORADEBUG command (#2) 'EVENTDUMP session' from process 'Windows thread id: 12292, image: <none>'
Dumping Event (group=SESSION)

*** 2012-01-08 09:48:18.426
Finished processing ORADEBUG command (#2) 'EVENTDUMP session'

*** 2012-01-08 09:49:16.415
CLOSE #3:c=0,e=20,dep=0,type=0,tim=488596106412
=====================
PARSING IN CURSOR #2 len=37 dep=1 uid=0 oct=3 lid=0 tim=488596107091 hv=1398610540 ad='7ffb8e4b250' sqlid='grwydz59pu6mc'
select text from view$ where rowid=:1
END OF STMT
PARSE #2:c=0,e=264,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=488596107088
BINDS #2:
 Bind#0
  oacdty=11 mxl=16(16) mxlc=00 mal=00 scl=00 pre=00
  oacflg=18 fl2=0001 frm=00 csi=00 siz=16 off=0
  kxsbbbfp=1dbe86d8  bln=16  avl=16  flg=05
  value=00000273.0010.0001
EXEC #2:c=0,e=550,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=3684871272,tim=488596107744
FETCH #2:c=0,e=25,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=3684871272,tim=488596107790
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=69 op='TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=0 pw=0 time=0 us cost=1 size=15 card=1)'
CLOSE #2:c=0,e=501,dep=1,type=0,tim=488596108309
=====================
PARSING IN CURSOR #5 len=37 dep=1 uid=0 oct=3 lid=0 tim=488596108602 hv=1398610540 ad='7ffb8e4b250' sqlid='grwydz59pu6mc'
select text from view$ where rowid=:1
END OF STMT
PARSE #5:c=0,e=23,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=488596108602
BINDS #5:
 Bind#0
  oacdty=11 mxl=16(16) mxlc=00 mal=00 scl=00 pre=00
  oacflg=18 fl2=0001 frm=00 csi=00 siz=16 off=0
  kxsbbbfp=1dbe86d8  bln=16  avl=16  flg=05
  value=00000273.0012.0001
EXEC #5:c=0,e=47,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=488596108701
FETCH #5:c=0,e=11,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=3684871272,tim=488596108727
STAT #5 id=1 cnt=1 pid=0 pos=1 obj=69 op='TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=0 pw=0 time=0 us cost=1 size=15 card=1)'
CLOSE #5:c=0,e=23,dep=1,type=0,tim=488596108766
=====================
PARSING IN CURSOR #3 len=37 dep=1 uid=0 oct=3 lid=0 tim=488596109217 hv=1398610540 ad='7ffb8e4b250' sqlid='grwydz59pu6mc'
select text from view$ where rowid=:1
END OF STMT
PARSE #3:c=0,e=14,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=488596109217
BINDS #3:
 Bind#0
  oacdty=11 mxl=16(16) mxlc=00 mal=00 scl=00 pre=00
  oacflg=18 fl2=0001 frm=00 csi=00 siz=16 off=0
  kxsbbbfp=1dbe86d8  bln=16  avl=16  flg=05
  value=00000274.0010.0001
EXEC #3:c=0,e=50,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=488596109318
FETCH #3:c=0,e=11,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=3684871272,tim=488596109344
STAT #3 id=1 cnt=1 pid=0 pos=1 obj=69 op='TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=0 pw=0 time=0 us cost=1 size=15 card=1)'
CLOSE #3:c=0,e=21,dep=1,type=0,tim=488596109385
=====================
PARSING IN CURSOR #4 len=204 dep=0 uid=91 oct=3 lid=91 tim=488596111910 hv=1585533233 ad='7ffb7578ec0' sqlid='dk42yjjg82n9j'
SELECT
  S.SID,
  S.SERIAL#,
  P.PID,
  S.SQL_TRACE,
  S.SQL_TRACE_WAITS,
  S.SQL_TRACE_BINDS
FROM
  V$SESSION S,
  V$PROCESS P
WHERE
  S.SID=(SELECT SID FROM V$MYSTAT WHERE ROWNUM=1)
  AND S.PADDR=P.ADDR
END OF STMT
PARSE #4:c=0,e=5410,p=0,cr=6,cu=0,mis=1,r=0,dep=0,og=1,plh=1985757361,tim=488596111909
EXEC #4:c=0,e=23,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1985757361,tim=488596112094
WAIT #4: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=488596112181
FETCH #4:c=0,e=1190,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=1985757361,tim=488596113404
WAIT #4: nam='SQL*Net message from client' ela= 232 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=488596113665
FETCH #4:c=0,e=397,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1985757361,tim=488596114102
STAT #4 id=1 cnt=1 pid=0 pos=1 obj=0 op='NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=0 size=53 card=1)'
STAT #4 id=2 cnt=1 pid=1 pos=1 obj=0 op='NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=0 size=49 card=1)'
STAT #4 id=3 cnt=810 pid=2 pos=1 obj=0 op='MERGE JOIN CARTESIAN (cr=0 pr=0 pw=0 time=346 us cost=0 size=16146 card=598)'
STAT #4 id=4 cnt=30 pid=3 pos=1 obj=0 op='FIXED TABLE FULL X$KSUPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=494 card=26)'
STAT #4 id=5 cnt=810 pid=3 pos=2 obj=0 op='BUFFER SORT (cr=0 pr=0 pw=0 time=130 us cost=0 size=184 card=23)'
STAT #4 id=6 cnt=27 pid=5 pos=1 obj=0 op='FIXED TABLE FULL X$KSLWT (cr=0 pr=0 pw=0 time=26 us cost=0 size=184 card=23)'
STAT #4 id=7 cnt=1 pid=2 pos=2 obj=0 op='FIXED TABLE FIXED INDEX X$KSUSE (ind:1) (cr=0 pr=0 pw=0 time=0 us cost=0 size=22 card=1)'
STAT #4 id=8 cnt=1 pid=7 pos=1 obj=0 op='COUNT STOPKEY (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=9 cnt=1 pid=8 pos=1 obj=0 op='FIXED TABLE FULL X$KSUMYSTA (cr=0 pr=0 pw=0 time=0 us cost=0 size=40 card=2)'
STAT #4 id=10 cnt=1 pid=9 pos=1 obj=0 op='FIXED TABLE FULL X$KSUSGIF (cr=0 pr=0 pw=0 time=0 us cost=0 size=4 card=1)'
STAT #4 id=11 cnt=1 pid=1 pos=2 obj=0 op='FIXED TABLE FIXED INDEX X$KSLED (ind:2) (cr=0 pr=0 pw=0 time=0 us cost=0 size=4 card=1)'
WAIT #4: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=488596114237

*** 2012-01-08 09:50:22.069
Received ORADEBUG command (#3) 'EVENTDUMP session' from process 'Windows thread id: 12292, image: <none>'
Dumping Event (group=SESSION)

*** 2012-01-08 09:50:22.069
Finished processing ORADEBUG command (#3) 'EVENTDUMP session'

*** 2012-01-08 09:51:24.578
Received ORADEBUG command (#4) 'EVENTDUMP session' from process 'Windows thread id: 12292, image: <none>'
Dumping Event (group=SESSION)

*** 2012-01-08 09:51:24.578
Finished processing ORADEBUG command (#4) 'EVENTDUMP session'

*** 2012-01-08 09:54:05.768
Received ORADEBUG command (#5) 'EVENTDUMP session' from process 'Windows thread id: 12292, image: <none>'
Dumping Event (group=SESSION)

*** 2012-01-08 09:54:05.768
Finished processing ORADEBUG command (#5) 'EVENTDUMP session'

*** 2012-01-08 09:55:01.068
WAIT #4: nam='SQL*Net message from client' ela= 344636055 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=488940750307

*** 2012-01-08 09:55:48.744
Received ORADEBUG command (#6) 'EVENTDUMP session' from process 'Windows thread id: 12292, image: <none>'
Dumping Event (group=SESSION)

*** 2012-01-08 09:55:48.744
Finished processing ORADEBUG command (#6) 'EVENTDUMP session'

*** 2012-01-08 09:56:50.964
Received ORADEBUG command (#7) 'session_event 10046 trace name context forever,level 12' from process 'Windows thread id: 12292, image: <none>'

*** 2012-01-08 09:56:50.964
Finished processing ORADEBUG command (#7) 'session_event 10046 trace name context forever,level 12'

*** 2012-01-08 09:57:18.634
Received ORADEBUG command (#8) 'EVENTDUMP session' from process 'Windows thread id: 12292, image: <none>'
Dumping Event (group=SESSION)

*** 2012-01-08 09:57:18.634
Finished processing ORADEBUG command (#8) 'EVENTDUMP session'

*** 2012-01-08 10:00:01.094
WAIT #2: nam='SQL*Net message from client' ela= 300017366 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=489240770346
CLOSE #2:c=0,e=19,dep=0,type=0,tim=489240770476
=====================
PARSING IN CURSOR #5 len=204 dep=0 uid=91 oct=3 lid=91 tim=489240770687 hv=1585533233 ad='7ffb7578ec0' sqlid='dk42yjjg82n9j'
SELECT
  S.SID,
  S.SERIAL#,
  P.PID,
  S.SQL_TRACE,
  S.SQL_TRACE_WAITS,
  S.SQL_TRACE_BINDS
FROM
  V$SESSION S,
  V$PROCESS P
WHERE
  S.SID=(SELECT SID FROM V$MYSTAT WHERE ROWNUM=1)
  AND S.PADDR=P.ADDR
END OF STMT
PARSE #5:c=0,e=130,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1985757361,tim=489240770686
EXEC #5:c=0,e=31,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1985757361,tim=489240770814
WAIT #5: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=489240770862
FETCH #5:c=0,e=7007,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=1985757361,tim=489240777893
WAIT #5: nam='SQL*Net message from client' ela= 192 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=489240778675
FETCH #5:c=0,e=336,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1985757361,tim=489240779049
STAT #5 id=1 cnt=1 pid=0 pos=1 obj=0 op='NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=0 size=53 card=1)'
STAT #5 id=2 cnt=1 pid=1 pos=1 obj=0 op='NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=0 size=49 card=1)'
STAT #5 id=3 cnt=700 pid=2 pos=1 obj=0 op='MERGE JOIN CARTESIAN (cr=0 pr=0 pw=0 time=349 us cost=0 size=16146 card=598)'
STAT #5 id=4 cnt=28 pid=3 pos=1 obj=0 op='FIXED TABLE FULL X$KSUPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=494 card=26)'
STAT #5 id=5 cnt=700 pid=3 pos=2 obj=0 op='BUFFER SORT (cr=0 pr=0 pw=0 time=81 us cost=0 size=184 card=23)'
STAT #5 id=6 cnt=25 pid=5 pos=1 obj=0 op='FIXED TABLE FULL X$KSLWT (cr=0 pr=0 pw=0 time=0 us cost=0 size=184 card=23)'
STAT #5 id=7 cnt=1 pid=2 pos=2 obj=0 op='FIXED TABLE FIXED INDEX X$KSUSE (ind:1) (cr=0 pr=0 pw=0 time=0 us cost=0 size=22 card=1)'
STAT #5 id=8 cnt=1 pid=7 pos=1 obj=0 op='COUNT STOPKEY (cr=0 pr=0 pw=0 time=0 us)'
STAT #5 id=9 cnt=1 pid=8 pos=1 obj=0 op='FIXED TABLE FULL X$KSUMYSTA (cr=0 pr=0 pw=0 time=0 us cost=0 size=40 card=2)'
STAT #5 id=10 cnt=1 pid=9 pos=1 obj=0 op='FIXED TABLE FULL X$KSUSGIF (cr=0 pr=0 pw=0 time=0 us cost=0 size=4 card=1)'
STAT #5 id=11 cnt=1 pid=1 pos=2 obj=0 op='FIXED TABLE FIXED INDEX X$KSLED (ind:2) (cr=0 pr=0 pw=0 time=0 us cost=0 size=4 card=1)'
WAIT #5: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=489240779190

*** 2012-01-08 10:00:49.694
WAIT #5: nam='SQL*Net message from client' ela= 48590415 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=489289369620
CLOSE #5:c=0,e=16,dep=0,type=0,tim=489289369744
=====================
PARSING IN CURSOR #3 len=24 dep=0 uid=91 oct=3 lid=91 tim=489289370485 hv=124468195 ad='7ffb78cd410' sqlid='c749bc43qqfz3'
SELECT SYSDATE FROM DUAL
END OF STMT
PARSE #3:c=0,e=704,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=1388734953,tim=489289370484
EXEC #3:c=0,e=24,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1388734953,tim=489289370563
WAIT #3: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=489289370620
FETCH #3:c=0,e=13,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=1388734953,tim=489289370661
STAT #3 id=1 cnt=1 pid=0 pos=1 obj=0 op='FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)'
WAIT #3: nam='SQL*Net message from client' ela= 189 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=489289370905
FETCH #3:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=1388734953,tim=489289370957
WAIT #3: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=489289370997

*** 2012-01-08 10:09:13.997
Received ORADEBUG command (#9) 'session_event 10046 trace name context off' from process 'Windows thread id: 12292, image: <none>'

*** 2012-01-08 10:09:13.997
Finished processing ORADEBUG command (#9) 'session_event 10046 trace name context off'

*** 2012-01-08 10:11:16.960
Received ORADEBUG command (#10) 'EVENTDUMP session' from process 'Windows thread id: 12292, image: <none>'
Dumping Event (group=SESSION)

*** 2012-01-08 10:11:16.960
Finished processing ORADEBUG command (#10) 'EVENTDUMP session'

*** 2012-01-08 10:12:42.528
Received ORADEBUG command (#11) 'EVENTDUMP session' from process 'Windows thread id: 12292, image: <none>'
Dumping Event (group=SESSION)

*** 2012-01-08 10:12:42.528
Finished processing ORADEBUG command (#11) 'EVENTDUMP session'

*** 2012-01-08 10:15:29.621
Received ORADEBUG command (#12) 'EVENTDUMP session' from process 'Windows thread id: 12292, image: <none>'
Dumping Event (group=SESSION)

*** 2012-01-08 10:15:29.621
Finished processing ORADEBUG command (#12) 'EVENTDUMP session'

*** 2012-01-08 10:19:54.941
Received ORADEBUG command (#13) 'EVENTDUMP session' from process 'Windows thread id: 12292, image: <none>'
Dumping Event (group=SESSION)

*** 2012-01-08 10:19:54.941
Finished processing ORADEBUG command (#13) 'EVENTDUMP session'

*** 2012-01-08 10:21:25.459
Received ORADEBUG command (#14) 'session_event 10046 trace name context forever,level 12' from process 'Windows thread id: 12292, image: <none>'

*** 2012-01-08 10:21:25.459
Finished processing ORADEBUG command (#14) 'session_event 10046 trace name context forever,level 12'

*** 2012-01-08 10:21:40.549
Received ORADEBUG command (#15) 'EVENTDUMP session' from process 'Windows thread id: 12292, image: <none>'
Dumping Event (group=SESSION)

*** 2012-01-08 10:21:40.549
Finished processing ORADEBUG command (#15) 'EVENTDUMP session' 

Faulty Quotes 7 – Deadlock Kills Sessions?

January 4, 2012

(Back to the Previous Post in the Series)

I thought that I would begin this blog article with a bit of a test case that demonstrates a very simple deadlock between two sessions.

In Session 1 (create the test table with a primary key and insert a single row):

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

INSERT INTO T1 VALUES (1,'1');

In Session 2 (insert a row that does not conflict with session 1 and then insert a row with the same primary key value as the uncommitted row in Session 1):

INSERT INTO T1 VALUES (2,'2');

INSERT INTO T1 VALUES (1,'3');

(Session 2 hangs while waiting for Session 1 to COMMIT or ROLLBACK)

In Session 1 (insert a row with the same primary key value as the uncommitted row in Session 2):

INSERT INTO T1 VALUES (2,'2');

(Session 1 hangs while waiting for Session 2 to COMMIT or ROLLBACK)

In Session 2 (an ORA-00060 is returned, deadlock between the two sessions ends):

INSERT INTO T1 VALUES (1,'3')
            *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

(Session 1 still hangs while waiting for Session 2 to COMMIT or ROLLBACK the first row that was inserted)

In Session 2 (confirming that the session was not killed/terminated and that the first row inserted still exists – indicates that the current TRANSACTION was not rolled back, just the statement that was involved in the deadlock):

SELECT
  *
FROM
  T1;

        C1 C2
---------- ----------
         2 2

While recently reading an OTN thread I was reminded of a bit of information that several sources simply miss-state about Oracle deadlocks.  The incorrect information is repeated, seemingly quite frequently, in various books and forum threads.  As luck would have it, several of the books that I have read about Oracle Database also repeat this incorrect information.  For instance, a quote from page 352 of the book “Expert Oracle Database 11g Administration” (a book that I reviewed and gave 4 out of 5 stars, maybe I need to re-evaluate that rating?):

“This is a catch-22 situation, because the stalemate can’t be broken by either session unilaterally. In such circumstances, Oracle steps in, terminates one of the sessions, and rolls back its statement. Oracle quickly recognizes that two sessions are deadlocked and terminates the session that issued the most recent request for a lock. This will release the object locks that the other session is waiting for.”

A second book, ”Secrets of the Oracle Database” (another book that I reviewed and gave 4 out of 5 stars, actually it was probably worth about 4.4 stars), states the following on page 57:

“Hence, the ORACLE DBMS detects circular chains pertaining to interdependent locks, signals the error ‘ORA-00060: deadlock detected while waiting for resource’, and rolls back one of the sessions involved in the would-be deadlock.”

A third book, ”Oracle SQL Recipes” (another book that I reviewed and gave 4 out of 5 stars), states the following on page 217:

“The immediate solution to the problem requires no user intervention: Oracle detects the deadlock and automatically rolls back one of the transactions, releasing one of the sets of row locks so that the other session’s DML will complete.”

A fourth book, “Oracle Database 11g Performance Tuning Recipes” (another book that I reviewed and gave 2 out of 5 stars), states on page 227:

“While the database can automatically resolve deadlocks between sessions (by killing one of the sessions holding a needed lock)…”

The Google book search found that the above four books were not alone in their statements of deadlocks that do not match the test case results at the start of this blog article.  One of the books found by Google is one that I have not read yet (and probably never will), “Oracle PL/SQL for Dummies”, which states the following on page 298:

“This is a deadlock. Oracle resolves it by raising a deadlock exception (ORA-00060: deadlock detected while waiting for resource) in user 1′s session. This terminates the procedure and allows user 2′s procedure to successfully complete.”

A second book found by the Google search is a bit old, “Oracle 9i DBA 101″, which on page 194 states:

“A deadlock occurs when two or more users wait for data that is locked by each other. Oracle will kill the first server process that detects the deadlock, so you should always look for and fix code that can cause deadlocks.”

There are certainly many other statements in other books regarding deadlocks, both correct and incorrect.  A quick search through the OTN forums found several threads in addition to the one at the start of this blog article that also drifted a bit from an accurate picture of what happens when a deadlock is broken in Oracle Database.

https://forums.oracle.com/forums/thread.jspa?threadID=2196282

“That is the deadlock. Oracle will then immediately choose arbitrarlly a victim session (542 or 548) and kill its process letting the remaining session continuing its work.”

https://forums.oracle.com/forums/thread.jspa?threadID=2152646

“There should be a simple wait, not a deadlock which would killed one of the session.”

https://forums.oracle.com/forums/thread.jspa?threadID=2261367

“If two sessions are actually deadlocked, Oracle should always kill one of the blocked sessions, raising an ORA-00060 error. It sounds like you’re saying this is not the expected behavior.”

https://forums.oracle.com/forums/thread.jspa?threadID=2227420

“The two session are inserting records in the same table with a unique index on it. With the same database version on another server and the same treatment, the deadlocks are correctly handled (one session is killed with ora-00060) and the process can continue.”

https://forums.oracle.com/forums/thread.jspa?threadID=1072665

“In oracle, when session1 and session2 make a deadlock, the oracle will kill one session , but I want know how to let oracle kill session1 and session2 both, is there way to do that?”

https://forums.oracle.com/forums/thread.jspa?threadID=2169563

“NO. Oracle already broke the deadlock by terminating one of these sessions. By the time trace file was closed, the deadlock no longer existed.”

https://forums.oracle.com/forums/thread.jspa?threadID=953308

“Oracle itself resolve the deadlock by killing the session.”

—

While reading the book “Oracle Core Essential Internals for DBAs and Developers” I almost flagged a sentence in a discussion of deadlocks as potentially containing an error.  That statement is found on page 82 near the end of the third to the last paragraph on the page.  After reading that paragraph about 10 times I decided that the statement is probably specific to TM locks, and probably is correct (the thought of TX locks maintained at the individual block level that contains the table rows kept running through my head the first nine times I read the paragraph).  This section of the book is interesting, as it not only avoids the mistakes mentioned in the above books, but it also highlights an inaccurate statement or two found in the Oracle Database documentation.

What is the Meaning of the %CPU Column in an Explain Plan? 2

January 1, 2012

(Back to the Previous Post in the Series)

Nearly two years ago I posted the following execution plan in a blog article and asked whether or not there was anything strange about the %CPU column:

--------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |       |       |   247 (100)|          |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 | 10000 |  2236K|   247   (1)| 00:00:03 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          | 10000 |  2236K|   247   (1)| 00:00:03 |  Q1,00 | PCWC |            |
|*  4 |     TABLE ACCESS FULL| T1       | 10000 |  2236K|   247   (1)| 00:00:03 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access(:Z>=:Z AND :Z<=:Z)
       filter(("C1"<=10000 AND "C1">=1)) 

For nearly two years no one mentioned that it is strange that the row in the execution plan with ID 0 can have a Cost of 247 with a %CPU of 100, while that operation’s child operations can have the same Cost of 247 with a %CPU of 1.  I had long forgotten about the challenge that I posed in the blog article, until someone mentioned the execution plan in an OTN forum thread and asked about that particular oddity.

In the earlier article I demonstrated querying the PLAN_TABLE after using EXPLAIN PLAN FOR to determine how the %CPU column is calculated.  Unfortunately, I did not perform that step two years ago for the SQL statement that was used to generate the above execution plan, so the challenge remains.

As best as I am able to determine, the following is the table creation script:

DROP TABLE T1 PURGE;

CREATE TABLE T1 (
  C1 NUMBER NOT NULL,
  C2 NUMBER NOT NULL,
  C3 NUMBER NOT NULL,
  C4 NUMBER NOT NULL,
  C5 VARCHAR2(30) NOT NULL,
  C6 VARCHAR2(30) NOT NULL,
  FILLER VARCHAR2(200),
  PRIMARY KEY (C1));

INSERT INTO T1
SELECT
  ROWNUM,
  ROWNUM,
  TRUNC(ROWNUM/100+1),
  TRUNC(ROWNUM/100+1),
  CHR(65+TRUNC(ROWNUM/10000))||TRUNC(ROWNUM/100+1),
  CHR(65+TRUNC(ROWNUM/10000))||TRUNC(ROWNUM/100+1),
  LPAD('A',200,'A')
FROM
  DUAL
CONNECT BY
  LEVEL<=100000;

COMMIT;

CREATE INDEX IND_T1_C3 ON T1(C3);
CREATE INDEX IND_T1_C4 ON T1(C4);
CREATE INDEX IND_T1_C5 ON T1(C5);

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

I originally created the execution plan on an unpatched version of Oracle Database 11.2.0.1 running on 64 bit Linux.  The following is the SQL statement that was used to generate the execution plan:

SELECT /*+ PARALLEL(4) */
  *
FROM
  T1
WHERE
  C1 BETWEEN 1 AND 10000; 

Unfortunately, the above SQL statement generates a bit of a different looking execution plan when not hinted to perform a full table scan.  In an Oracle Database 11.2.0.2 database, the following execution plan appeared (although a similar one also appears for 11.2.0.1):

Plan hash value: 2275811211

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |   250 | 39750 |    17   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1           |   250 | 39750 |    17   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | SYS_C0041509 |   450 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C1">=1 AND "C1"<=10000)

Note
-----
   - Degree of Parallelism is 1 because of hint 

The execution plan did not employ parallel execution even though it was hinted, because the calculated cost for the index access was less than the calculated cost for the parallel full table scan.  Note also that the cardinality estimate is incorrect  – this query will actually return 10,000 rows (for a side challenge, how is it possible that the index is projected to return 450 rows, when the parent operation is expected to return just 250 rows without a filter predicate applied at the parent operation?).

Let’s add a couple of hints to fix the cardinality issue (note that this particular hint is undocumented), and force the parallel table scan:

DELETE FROM PLAN_TABLE;

EXPLAIN PLAN FOR
SELECT /*+ PARALLEL(4) FULL(T1) CARDINALITY(T1 10000) */
  *
FROM
  T1
WHERE
  C1 BETWEEN 1 AND 10000;

SELECT
  ID,
  COST,
  IO_COST,
  COST-IO_COST DIFF,
  CEIL(DECODE(COST,0,0,(COST-IO_COST)/COST)*100) PER_CPU,
  CPU_COST
FROM
  PLAN_TABLE;

SET LINESIZE 120
SET PAGESIZE 1000

SELECT
  *
FROM
  TABLE(DBMS_XPLAN.DISPLAY); 

On the unpatched 11.2.0.1 database, the above script produced the following output:

ID       COST    IO_COST       DIFF    PER_CPU   CPU_COST
-- ---------- ---------- ---------- ---------- ----------
 0        247        246          1          1    5958333
 1
 2        247        246          1          1    5958333
 3        247        246          1          1    5958333
 4        247        246          1          1    5958333

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2494645258

--------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          | 10000 |  1552K|   247   (1)| 00:00:03 |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 | 10000 |  1552K|   247   (1)| 00:00:03 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          | 10000 |  1552K|   247   (1)| 00:00:03 |  Q1,00 | PCWC |            |
|*  4 |     TABLE ACCESS FULL| T1       | 10000 |  1552K|   247   (1)| 00:00:03 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("C1">=1 AND "C1"<=10000)

Note
-----
   - Degree of Parallelism is 4 because of hint 

The above execution plan is similar to the execution plan at the start of this article (even the costs are identical), yet not exactly the same.  The Predicate Information section of the execution plan is missing access(:Z>=:Z AND :Z<=:Z) and the %CPU column shows a value of 1 from bottom to top, rather than 100 for the top row in the plan.  The output from the query of PLAN_TABLE exactly matches the above output from DBMS_XPLAN.DISPLAY function.  So, what happened, how did I generate the execution plan that appears at the start of this blog article?

You might be curious – are we able to force the first line in the execution plan to show 100 in the %CPU column?  Let’s try an experiment where we manually change the IO_COST value for the row in the PLAN_TABLE where the ID column value is equal to 0:

UPDATE
  PLAN_TABLE
SET
  IO_COST=0
WHERE
  ID=0;

SELECT
  *
FROM
  TABLE(DBMS_XPLAN.DISPLAY); 

With the above change, this is the execution plan that I obtained:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2494645258

--------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          | 10000 |  1552K|   247 (100)| 00:00:03 |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 | 10000 |  1552K|   247   (1)| 00:00:03 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          | 10000 |  1552K|   247   (1)| 00:00:03 |  Q1,00 | PCWC |            |
|*  4 |     TABLE ACCESS FULL| T1       | 10000 |  1552K|   247   (1)| 00:00:03 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("C1">=1 AND "C1"<=10000)

Note
-----
   - Degree of Parallelism is 4 because of hint 

The first line in the above execution plan now shows 100 in the %CPU column – so if a 0 were present in that row/column combination in the PLAN_TABLE when I generated the execution plan at the start of this article, that might be one explanation for the strange output.  Note, however, that the Predicate Information section still does not match.  So, did I fake the execution plan through manipulation of PLAN_TABLE, or did I actually execute the SQL statement and pull from memory the actual execution plan:

SELECT /*+ PARALLEL(4) FULL(T1) CARDINALITY(T1 10000) */
  *
FROM
  T1
WHERE
  C1 BETWEEN 1 AND 10000;

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

When I executed the above, this is the execution plan that appeared:

SQL_ID  4r3zujtat35jb, child number 0
-------------------------------------
SELECT /*+ PARALLEL(4) FULL(T1) CARDINALITY(T1 10000) */   * FROM   T1
WHERE   C1 BETWEEN 1 AND 10000

Plan hash value: 2494645258

--------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |       |       |   247 (100)|          |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 | 10000 |  1552K|   247   (1)| 00:00:03 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          | 10000 |  1552K|   247   (1)| 00:00:03 |  Q1,00 | PCWC |            |
|*  4 |     TABLE ACCESS FULL| T1       | 10000 |  1552K|   247   (1)| 00:00:03 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access(:Z>=:Z AND :Z<=:Z)
       filter(("C1">=1 AND "C1"<=10000))

Note
-----
   - Degree of Parallelism is 4 because of hint 

Notice in the above execution plan that the %CPU column matches the values in the execution plan that appears at the start of this blog article.  Also note that the missing access(:Z>=:Z AND :Z<=:Z) now appears in the Predicate Information section.  Another possible case of explain plan lies?  I wonder what is the point of the :Z >= :Z and :Z <= :Z access predicate?  That is the equivalent of :Z BETWEEN :Z AND :Z, or 1 BETWEEN 1 AND 1 (assuming that the value of the :Z bind variable is not NULL), if you prefer.

Now that I have reproduced the execution plan at the start of this blog article (ignoring the predicted Bytes column), why did the DBMS_XPLAN function display a value of 100 in the %CPU column of the first line in the execution plan?  Is it caused by a bug in DBMS_XPLAN, a bug in the 11.2.0.1 optimizer, or something else?

Recall earlier in this blog article that I tried an experiment of changing the IO_COST for a row in PLAN_TABLE to a value of 0, and that change resulted in a value of 100 appearing in the %CPU column – does that mean that the IO_COST for the first row in the execution plan is 0?  In an execution plan, the costs shown for parent operations includes the cost shown for child operations…

In this case we cannot query the PLAN_TABLE, but we are able to query V$SQL_PLAN for the same type of information using the SQL_ID and Plan hash value that appeared in the above DBMS_XPLAN output:

SELECT
  ID,
  COST,
  IO_COST,
  COST-IO_COST DIFF,
  CPU_COST
FROM
  V$SQL_PLAN
WHERE
  SQL_ID='4r3zujtat35jb'
  AND PLAN_HASH_VALUE=2494645258
ORDER BY
  ID;

        ID       COST    IO_COST       DIFF   CPU_COST
---------- ---------- ---------- ---------- ----------
         0        247
         1
         2        247        246          1    5958333
         3        247        246          1    5958333
         4        247        246          1    5958333 

A NULL value for the IO_COST in execution plan line ID 0 (the first line in the execution plan)…

In the previous article, I proposed that the %CPU column is calculated with the following formula:

CEIL(DECODE(COST,0,0,(COST-IO_COST)/COST)*100) PER_CPU 

If the above were true, then a NULL would appear in the %CPU column for the first row.  It appears that I need to adjust the formula slightly to account for the possibility of a NULL value in the IO_COST column and handle it as if the value were 0 (who says that NULL is not equal to 0?).

CEIL(DECODE(COST,0,0,(COST-NVL(IO_COST,0))/COST)*100) PER_CPU

Later in the OTN thread the OP expressed concern about two SQL statements and their execution plans, where the “the cost per se of a single execution is low, but since the query is executed many number of times, the cpu consumption is hitting a high.”  Feel free to read the OTN thread to see my comments related to this concern.

Book Review: Oracle Core Essential Internals for DBAs and Developers

December 25, 2011 (Modified December 29, 2011)

Digging Deeply into Oracle Internal Processing when the Oracle Wait Interface is Insufficient
http://www.amazon.com/Oracle-Core-Essential-Internals-Developers/dp/1430239549

I pre-ordered a paperback copy of this book three months ago from Amazon and also purchased a PDF copy of the book from Apress.  It was a long, nearly six year wait since the publication of the author’s “Cost-Based Oracle Fundamentals” book, and I am fairly certain that many of those who read the “Cost-Based Oracle Fundamentals” book were looking forward to reading volume two in the anticipated three part series.

The author of this book is a well known Oracle Ace Director who has written at least three books since the year 2000 and contributed to a handful of other books.  In addition to writing books, the author has also maintained a technical Oracle Database blog since 2006, and contributed to a number of Oracle focused Internet forums (Usenet, Oracle-L, AskTom, Oracle OTN) dating back to at least 1994.  The book’s primary technical reviewer is also a well known Oracle Ace Director and Oracle Certified Master who also maintains a technical Oracle Database blog and “living” Oracle reference site with deeply technical articles.

Did the book’s contents meet the level of expectations provided by the book’s cover and the publisher’s description of the book?  Shortly before the book arrived, I assumed that the book was targeted at people who might have struggled with the “Cost-Based Oracle Fundamentals” book.  The back cover of this book states that the book targets readers with knowledge ranging from beginners to intermediate.  I was surprised to find that chapter one lasted all of four pages, and that page seven introduced the reader to the first of many symbolic block dumps.  It was at this point that the obvious becomes obvious – this book is intended to take the reader on a journey that is far deeper, more intense, and more densely packaged than pretty much any other Oracle Database book published in the last five or ten years.  Reading the book twice might be required for full comprehension of the material, and a third read-through a year or two later might be a welcome reminder of how Oracle Database works under the covers to produce the pretty pictures painted by Enterprise Manager.   In short, before reading this book, be certain to understand the Oracle Database concepts, and have a reasonable understanding of Oracle performance troubleshooting (read either the Performance Tuning Guide from the Oracle documentation library or the book “Troubleshooting Oracle Performance”).

This book fills a key void in the Performance Tuning Guide from the Oracle documentation library: what is the next step when the Oracle wait interface fails to identify the source of a particular performance problem?  There is no recipe for that solution; the solution is to understand what triggers Oracle Database to behave as it does.  This book pieces together the under-the-hood understanding through the detailed inter-mixing of many Oracle statistics, performance views, X$ structures, latches, parameters, wait events, and Oracle error messages.

While there are a handful of problems/errors in the book, the vast majority of those problems are simple word substitutions or keystroke errors (for example, putting in an extra underscore or removing an underscore from an Oracle keyword on one page, while correctly specifying the keyword elsewhere in the book) that are fairly easy to identify and work around.  The author devoted a section of his blog to quickly address potential errors found in the book, and to expand the book’s contents as additional information becomes available.

In short, if you need to drill into Oracle Database performance problems beyond what is provided by the Oracle wait interface, this is the key book that glues together the bits and pieces of information that Oracle Database exposes (and selectively hides).

Comments on the Book’s Contents:

• The test scripts used in the book show evidence that those scripts were often run on different Oracle Database versions, and the differences found in the output from those versions are often described in the scripts.
• While mostly avoiding Oracle features that require additional cost licenses, features that require an extra cost license, such as partitioning, state that an extra cost license is required.
• Description of symbolic block dump: starting byte position (follows @ sign), lock byte (lb:).  (page 7)
• Description of change vector dump: operation code (KDO Op code), update row piece (URP), block address being updated (bdba:), segment header block (hdba:), interested transaction list (itli:), table number (tabn:), row number in the block (slot:), number of columns in the table (ncol:), number of columns updated (nnew:), increase in the row length (size:). (page 8)
• Description of ACID (pages 11-13)
• Due to an optimization (private redo and in-memory undo) in 10g, a session only needs to obtain the public redo allocation latch once per transaction, rather than once per change. (page 15)
• Points out an error in the Oracle Documentation, and in some books about LGWR writing a commit record to the online redo log. (page 28)
• Plenty of forward and backward references in the book.
• Undo block dump: transaction ID (xid:), block renewed incarnation number (seq:) (pages 32-33)
• A single undo block may contain undo records from multiple transactions, but only from a single active transaction. (page 34)
• Data block dump: interested transaction list index (Itl), transaction ID of a transaction that modified the block in the format of undo segment.undo slot.undo sequence number (Xid), undo record address in the format of absolute block address.block sequence number.record in the block (Uba), bit flag indicating state of the transaction (Flag), rows locked by the transaction (Lck), commit SCN or space available if the transaction committed (Scn/Fsc), cleanout SCN (csc:), last change SCN (scn:), number of times the block has changed at the SCN (seq:), row locked by transaction number (lb:)  (page 37-38)
• For an index, the initrans parameter only applies to leaf blocks. (page 38)
• Helpful scripts (snap_9_buffer, snap_9_kcbsw, snap_11_kcbsw, snap_myst, snap_rollstat, snap_stat) in chapter 3’s script library that create packages used for calculating the delta values of various statistics from Oracle’s various performance views.  The scripts often describe previously achieved results from Oracle Database versions ranging from 8.1.7.4 through 11.2.0.2.  The script libraries for chapters 2, 6, and 7 include packages for monitoring the delta values of statistics from additional performance views.
• Parallel query execution, serial direct path read scans, and accesses to read-only tablespaces can result in repeated delayed block cleanout related work.  In the cases of parallel query execution and serial direct path read scans, the cleaned out version of the block is not copied from the PGA to the SGA as a “dirty” block. (page 50)
• The spin and sleep approach to acquiring latches changed in recent Oracle Database releases.  Rather than sleeping progressively longer times after each spin when attempting to acquire a latch, the process simply goes to sleep and waits for the process holding the latch to notify the process at the top of the list that is waiting for the latch. (page 72)
• Decoding TX lock ID1 and ID2 values into undo segment, slot, and wrap number. (page 77)
• The book tries to be specific regarding changes made in Oracle database versions, such as the change in 9.2.0.5 when the SESSION_CACHED_CURSORS parameter started controlling the number of PL/SQL cursors that were automatically held open, rather than the OPEN_CURSORS parameter. (page 89)
• The book states: “There is one particularly interesting difference between latches and mutexes: latches are held by processes, while mutexes are held by sessions…” (page 91)
• Default block sizes other than powers of 2 (12KB, 5KB, 4.5KB, etc.) are possible, but may be viewed as unsupported by Oracle support.  The book makes a good case for using (only) 8KB block sizes, providing an exception for a 4KB block size as a secondary choice on some Linux platforms. (page 98)
• The book frequently addresses topics that are incorrectly described in other resources.  For example, referencing the touch count of blocks to determine which block is the source of latch contention. (page 104)
• Recently discovered potential ACID durability problem described in detail. (page 129)
• Describes problems related to measuring LGWR performance through examination of LOG FILE SYNC wait event durations. (page 134)
• One of the threats of newer hard drives with larger sector sizes (4 KB rather than 512 bytes) is that redo wastage will increase. (page 136)
• The book mentions setting event 10120 to trigger relative file numbers to differ from absolute file numbers when new datafiles are created.  Some information on the Internet incorrectly describes this event number as disabling index fast full scans.  An interesting side-effect of experimenting with this event is that the database can contain multiple datafiles with the same relative file number (in different tablespaces), even when there are few datafiles in the database. (page 143)
• Oracle checkpoints described: Instance recovery checkpoint, Media recovery checkpoint, Thread checkpoint, Interval checkpoint, Tablespace checkpoint, PQ tablespace checkpoint, Close database checkpoint, Incremental checkpoint, Local database checkpoint, Global database checkpoint, Object reuse checkpoint, Object checkpoint, RBR checkpoint, Multiple object checkpoint (pages 148-149)
• Serial direct path read in 11.1 and later will perform a PQ tablespace checkpoint before the direct path read begins. (page 149)
• The process of allowing space at the end of a redo log file for potential redo data in the public and private redo threads is one explanation why archived redo log files become a couple of megabytes smaller than the online redo logs. (page 151)
• Four different definitions of the term CURSOR, as related to Oracle Database. (page 162)
• Demonstration of the use of bind variables significantly decreasing the number of dictionary cache accesses. (pages 171-172)
• The CURSOR_SHARING parameter value of SIMILAR is deprecated as of Oracle Database 11.1 due to the arrival of adaptive cursor sharing. (page 173)
• Lengthy discussion of the types of problems that might be intensified when moving a database from a single instance to a multi-instance RAC environment. (pages 202-229)
• Detailed ORADEBUG examples in the appendix. (pages 231-238)
• Detailed list of Oracle Database terms and their definitions. (pages 245-253)

 Suggestions, Problems, and Errors:

• A potential area for improvement: explain how the author determined that block 0x008009a found in a redo header dump was in fact referencing datafile 2, block 154.  The following SQL statement: SELECT TO_CHAR(DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS(2,154), ‘XXXXXXX’) DBA FROM DUAL; produces a value of 80009A which confirms that the author is correct.  The approach for decoding the block printed in the dump might be covered later in the book. (page 9)  (Edit Dec 29, 2011: the author has provided an extended explanation describing how to decode the datafile number and block number in a comment found on his “OC 2 Undo and Redo” blog page)
• The book states, “(as can be seen in the dynamic performance view v$latch_holder, which is underpinned by the structure x$ksuprlatch).”  There should not be an underscore character in V$LATCHHOLDER, and the X$KSUPRLATCH structure does not seem to exist in 11.2.0.2 (confirmed by a response on the author’s errata page on his blog that the structure name is X$KSUPRLAT). (page 73)
• The book states, “… if you query the dynamic performance view v$lock, you are querying the structure defined by the parameter enqueues.”  It appears that the author intended to state, “defined by the parameter _ENQUEUE_LOCKS”.  (Confirmed by a response on the author’s errata page.) (page 77)
• The book states, “…and a column x$ksqlres, which is the address of the resource it’s locking, exposed indirectly through the type, id1, and id2.” The actual column name is KSQLKRES, without the embedded $ character.  (Confirmed by a response on the author’s errata page.) (page 78)
• Missing word in steps 1 and 2 that describe the sequence of events that lead to the V$LOCK output shown in the book.  (Confirmed by a response on the author’s errata page – the missing word is delete.) (page 79)
• The book expands the abbreviation ASMM as “automatic system memory management”, while the documentation and most other sources expand this abbreviation as “automatic shared memory management”.  (Confirmed by a response on the author’s errata page.) (page 94)
• The book states, “If you want to see how memory allocations change with the number of CPUs, you can adjust parameter cpu_count and restart the instance; however, in 11.2 you also need to set parameter _disable_cpu_check to false.”  The _DISABLE_CPU_CHECK parameter defaults to FALSE, so the author probably intended to write TRUE.  (Confirmed by a response on the author’s errata page.) (page 101)
• The book dropped the “s” following the word “get” in the statistic name “CONSISTENT GETS – EXAMINATION”.  The statistic name is spelled correctly on pages 44 and 51.  (Confirmed by a response on the author’s errata page.) (page 114)
• The book states, “If the optimizer thought the table was larger than the 2 percent limit, then the buffers used to read the table were immediately dropped to the end of the LRU list as the blocks were read…”  It appears that the author intended to write “runtime engine” (as stated in the previous paragraph) rather “than optimizer”.  (Confirmed by a response on the author’s errata page.) (page 118)
• The book states, “11.2.0.2 takes this a little further with two new statistics: redo synch write time (usec), which is the time in microseconds…”  It appears that the actual statistic name does not contain the word “write”.  (Confirmed by a response on the author’s errata page.) (page 129)
• The book states: “If we check x$qrst (the X$ structure underlying v$rowcache), we find that it contains an interesting column, as follows”.  It appears that the X$ structure is actually X$KQRST – the DESC command that follows the sentence in the book shows the correct X$ structure name. (page 166)  (Edit Dec 29, 2011: Confirmed by a response on the author’s “OC 7 Parsing and Optimising” errata blog page)
• The book states: “(If you needed an argument why you should select only the columns you need in a query, rather than using select *, the extra cost of accessing dc_histogram_defs should be enough to convince you.)”  This sentence immediately follows a sentence that described how adding a second predicate in the WHERE clause referencing a second column would double the number of gets from the dictionary cache; thus it seems that the comment about the threat of “select *” causing more visits to dc_histogram_defs is only significant if those columns are also specified in the WHERE clause. (page 171)  (Edit Dec 29, 2011: a response from the author suggested experimenting with the core_dc_activity_01.sql script in the book’s script library;  experimentation with that script indicates that the statement in the book is correct)
• The book states: “There are probably a number of sites that could benefit from increasing the session_cache_cursor parameter,…”  The parameter name is SESSION_CACHED_CURSORS – that parameter is correctly spelled on the previous page and further down the same page.  (Confirmed by a response on the author’s errata page.) (page 176)
• The book states, “However, you may recall all those latches relating to the library cache that appeared in 10g—like the library cache pin allocation latch…”.  That particular latch appears to have been removed in Oracle Database 11.1.0.6, however it is not clear if this section of the book is only describing behavior prior to Oracle Database 11.1. (page 194)  (Edit Dec 29, 2011: Confirmed by a response on the author’s “OC 7 Parsing and Optimising” errata blog page, with additional detail provided related to the statement in the book)
• The book states, “In 10.2 they introduced code to allow KGL pins to be cached by sessions (hidden parameter _session_kept_cursor_pins) with similar intent…”.  The _SESSION_KEPT_CURSOR_PINS hidden parameter does not exist in Oracle Database 11.2.0.2 (10.2.0.x not checked). (page 195)  (Edit Dec 29, 2011: Confirmed by a response on the author’s “OC 7 Parsing and Optimising” errata blog page, with additional detail provided related to the statement in the book)
• The book states, “However, the parameter cursor_spare_for_time is deprecated in 11g…”  The CURSOR_SPACE_FOR_TIME parameter is spelled correctly in the previous sentence. (page 195)  (Edit Dec 29, 2011: Confirmed by an errata entry on the author’s “OC 7 Parsing and Optimising” errata blog page)
• The book states, “Let’s start with the memory structures—we have v$dlm_ress that is analogous to v$resources — it lists the things that are lockable…”  It appears that V$RESOURCE should not have a trailing S. (page 209)  (Edit Dec 29, 2011: Confirmed by an errata entry on the author’s “OC 8 RAC and Ruin” errata blog page)
• The glossary indicates that the possible granule sizes are one of 4MB, 8MB, 16MB, and 64MB depending on the Oracle Database version and the size of the SGA.  The statement in the book is more accurate than what is provided by the Oracle Database documentation for 11.2 which states that the granule size is either 4MB or 16MB depending on the size of the SGA.  However, limited testing in Oracle Database 11.2.0.2 indicates that the granule size increases from 64MB to 128MB when the SGA_TARGET parameter is set to 1 byte greater than 32G, and jumps to 256MB when the SGA_TARGET parameter is set to 1 byte greater than 64G.  A granule size of 32MB is possible when the SGA_TARGET was set to a value between 8G + 1 byte to 16G. (page 247)  (Edit Dec 29, 2011: Confirmed by an errata entry and follow up comments on the author’s “OC Glossary” errata blog page)

 Data Dictionary Views/Structures (the index at the back of the book misses most of these entries):

• V$SESSTAT (page 15)
• V$LATCH (pages 15, 69, 116, 138, 175)
• V$LATCH_CHILDREN (pages 16, 69, 138, 166, 170)
• X$KTIFP (in-memory undo pool, undo change vectors) (page 16)
• V$TRANSACTION (pages 16, 29)
• X$KCRFSTRAND (private pool, redo change vectors) (pages 16, 125, 237)
• X$KTIFF (pages 19, 152)
• DBA_ROLLBACK_SEGS (page 28)
• V$LOCK (pages 29, 77, 78, 226)
• X$KTUXE (transaction table information) (page 30)
• V$BH (page 47)
• X$KSUSE (base structure for V$SESSION) (page 59)
• X$KTATL, V$RESOURCE_LIMIT, X$KSQEQ (page 60)
• X$KSMFSV (pages 60, 236)
• X$KSQRS (enqueue resources) (pages 60, 77, 78, 79, 88)
• V$SQL (pages 63, 234)
• V$LATCH_PARENT (page 69)
• V$SYSTEM_EVENT (page 70)
• X$KSUPRLATCH (page 73)
• V$LATCHHOLDER (pages 73, 133)
• V$RESOURCES (page 77)
• X$KSQEQ (generic enqueues) (pages 77, 78)
• X$KTADM (table/DML locks), X$KDNSSF, X$KTATRFIL, X$KTATRFSL, X$KTATL, X$KTSTUSC, X$KTSTUSG, X$KTSTUSS, X$KSQEQ (page 78)
• X$KTCXB (transactions) (pages 78, 252)
• X$KGLLK (library cache lock) (pages 89, 195)
• V$OPEN_CURSOR (pages 89, 195, 248)
• V$SGAINFO, V$SGA_DYNAMIC_COMPONENTS, X$KSMGE, X$KSMGV (page 94)
• X$BH (pages 95, 102, 113, 219, 220)
• V$BUFFER_POOL (pages 96, 99)
• V$BUFFER_POOL_STATISTICS (pages 99, 100)
• V$SGA_RESIZE_OPS, V$MEMORY_RESIZE_OPS (page 100)
• X$KCBWDS (pages 100, 102, 138, 144, 179, 180)
• X$KCBBF (page 113)
• V$SYSSTAT (page 118)
• V$SESSION (page 126)
• V$SESSION_WAIT (pages 126, 226)
• V$LOG, V$LOG_HISTORY (page 139)
• V$CONTROLFILE_RECORD_SECTION (page 146)
• X$KCBOQH (kernel cache buffers object queue header), X$KCBOBH (kernel cache buffers object buffer headers) (page 151)
• OBJ$,TAB$, COL$, IND$, ICOL$, TRIGGER$ (page 162)
• V$ROWCACHE (dictionary cache) (pages 164, 166, 169, 170, 225, 227)
• V$ROWCACHE_PARENT (pages 164, 166)
• X$KQRPD (pages 164, 168, 169)
• DBA_OBJECTS, ALL_OBJECTS, DBA_DEPENDENCIES (page 165)
• SYS.BOOTSTRAP$ (pages 165, 233)
• USER_OBJECTS, USER_DEPENDENCIES, V$ROWCACHE_SUBORDINATE, X$QRST (page 166)
• X$KQRSD (pages 168, 169)
• V$SGASTAT (page 169)
• X$KGHLU (pages 179, 180, 188, 189, 190)
• X$KSMSP (page 188)
• V$LIBRARYCACHE (pages 194, 234)
• X$KGLPN (library cache pin) (page 195)
• X$KGLOB (pages 196, 234)
• V$GES_ENQUEUE (page 208, 209)
• V$DLM_RESS (pages 208, 209)
• V$RESOURCE, V$RESOURCE_LIMIT, V$SGASTAT (page 209)
• V$LOCK (pages 209, 233)
• V$RESOURCE_LIMIT (pages 209, 210)
• V$SGASTAT (page 209, 210)
• SEQ$ (pages 223, 224, 225, 226)
• V$ENQUEUE_STAT (page 225)
• V$LOCK_TYPE, V$SESSION_EVENT, V$EVENT_NAME (page 226)
• V$PROCESS, V$BGPROCESS (page 231)
• SYS.AUD$ (page 232)
• X$KSMMEM (page 237)

 Parameters (the index at the back of the book misses most of these entries):

• LOG_PARALLELISM (page 15)
• TRANSACTIONS, SESSIONS, PROCESSES, CPU_COUNT (page 16)
• UNDO_RETENTION (page 56)
• _ENABLE_RELIABLE_LATCH_WAITS (page 72)
• CPU_COUNT (pages 72, 75, 101)
• _ENQUEUE_RESOURCES (page 77)
• SESSION_CACHED_CURSORS (pages 89, 175)
• OPEN_CURSORS (pages 89, 176)
• SHARED_POOL_SIZE (page 94)
• DB_CACHE_SIZE (pages 94, 97)
• SGA_TARGET, MEMORY_TARGET, PGA_AGGREGATE_TARGET (page 95)
• DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE (PAGE 97)
• DB_2K_CACHE_SIZE, DB_4K_CACHE_SIZE, DB_8K_CACHE_SIZE, DB_16K_CACHE_SIZE, DB_32K_CACHE_SIZE (page 98)
• DB_BLOCK_BUFFERS, BUFFER_POOL_KEEP, BUFFER_POOL_RECYCLE (page 99)
• DB_WRITER_PROCESSES, _DISABLE_CPU_CHECK (page 101)
• _DB_HOT_BLOCK_TRACKING (page 104)
• _DB_PERCENT_HOT_DEFAULT (page 108)
• _DB_BLOCK_MAX_CR_DBA (pages 108, 115)
• _DB_BLOCK_HASH_BUCKETS (page 111)
• _BUFFER_BUSY_WAIT_TIMEOUT (page 113)
• _DB_HANDLES, _DB_HANDLES_CACHED (page 115)
• DB_FILE_MULTIBLOCK_READ_COUNT (page 117)
• _SMALL_TABLE_THRESHOLD (page 118)
• LOG_BUFFER (pages 123, 125)
• COMMIT_WRITE, COMMIT_LOGGING, COMMIT_WAIT (page 130)
• TRANSACTIONS (page 136)
• FAST_START_MTTR_TARGET, FAST_START_IO_TARGET,  _TARGET_RBA_MAX_LAG_PERCENTAGE (page 140)
• LOG_CHECKPOINT_INTERVAL, LOG_CHECKPOINT_TIMEOUT (pages 140, 148)
• _DB_BLOCK_MAX_SCAN_PCT (page 144)
• ARCHIVE_LAG_TARGET (page 151)
• _DEFER_LOG_BOUNDARY_CKPT, _DEFER_LOG_COUNT (page 153)
• _MORE_ROWCACHE_LATCHES (page 164)
• CURSOR_SHARING (page 172, 194)
• SHARED_POOL_RESERVED_SIZE (pages 179, 183)
• _SHARED_POOL_RESERVED_PCT (pages 179, 183)
• _SHARED_POOL_RESERVED_MIN_ALLOC (pages 183, 191)
• CURSOR_SPACE_FOR_TIME (page 195)
• PARALLEL_MAX_SERVERS (page 212)
• TRACEFILE_IDENTIFIER (page 232)
• USE_STORED_OUTLINES (page 236)

 Statistics (the index at the back of the book misses most of these entries):

• ROLLBACK CHANGES – UNDO RECORDS APPLIED (page 33)
• PHYSICAL READS FOR FLASHBACK NEW, USER ROLLBACKS, TRANSACTION ROLLBACKS, ROLLBACK CHANGES – UNDO RECORDS APPLIED (page 34)
• CR BLOCKS CREATED, DATA BLOCKS CONSISTENT READS – UNDO RECORDS APPLIED (page 44)
• CONSISTENT GETS – EXAMINATION (pages 44, 51, 114)
• CONSISTENT CHANGES, NO WORK – CONSISTENT READ GETS, CONSISTENT GETS, CR BLOCKS CREATED (page 45)
• FUSION WRITES, COMMIT CLEANOUTS (page 46)
• DB BLOCK CHANGES, REDO ENTRIES, COMMIT CLEANOUT FAILURES: BLOCK LOST (page 47)
• CALLS TO KCMGRS, COMMIT TXN COUNT DURING CLEANOUT, CLEANOUT – NUMBER OF KTUGCT CALLS, DB BLOCK GETS (page 49)
• REDO SYNCH WRITES (pages 49, 126, 128, 131, 132)
• TRANSACTION TABLES CONSISTENT READS – UNDO RECORDS APPLIED (pages 52, 55)
• TRANSACTION TABLES CONSISTENT READ ROLLBACKS (page 55)
• LATCH FREE (page 70)
• BUFFER IS PINNED COUNT (pages 114, 115)
• SWITCH CURRENT TO NEW BUFFER (pages 115, 116, 139)
• CR BLOCKS CREATED (page 116)
• TABLE SCANS (SHORT TABLES), TABLE SCANS (LONG TABLES) (page 118)
• MESSAGES RECEIVED (page 125)
• REDO SIZE (pages 125, 135, 152)
• MESSAGES SENT (pages 125, 126)
• REDO SYNC WRITE TIME, REDO SYNCH TIME (USEC), REDO SYNC LONG WAITS (page 129)
• REDO BLOCKS WRITTEN (page 131)
• REDO ENTRIES (pages 131, 152)
• REDO WASTAGE (pages 131, 135)
• UNDO CHANGE VECTOR SIZE (page 152)
• PHYSICAL READS FOR FLASHBACK NEW (page 156)
• PARSE COUNT (TOTAL) (pages 173, 174, 176, 178)
• PARSE COUNT (HARD) (pages 174, 176, 178, 194)
• SESSION CURSOR CACHE HITS (pages 175, 176)
• CURSOR AUTHENTICATIONS, SESSION CURSOR CACHE COUNT (page 176)
• GC CURRENT BLOCK PIN TIME, GC CURRENT BLOCK FLUSH TIME (page 218)
• GC CR BLOCK BUILD TIME, GC CR BLOCK FLUSH TIME (page 219)

 Wait Events (the index at the back of the book misses most of these entries):

• READ BY OTHER SESSION, BUFFER DEADLOCK (page 113)
• BUFFER BUSY WAITS (pages 113, 224)
• DB FILE SEQUENTIAL READ, DB FILE PARALLEL READ, DB FILE SCATTERED READ (page 117)
• LOG BUFFER SPACE (pages 123, 133)
• LOG FILE SYNC (pages 125, 126, 132, 134)
• RDBMS IPC MESSAGE (pages 125, 126)
• LGWR WAIT FOR REDO COPY (pages 133, 134)
• LOG FILE PARALLEL WRITE (page 134)
• CF ENQUEUE (page 146)
• ENQ: KO – FAST OBJECT CHECKPOINT, ENQ: RO – FAST OBJECT REUSE (page 150)
• LOG FILE SWITCH (PRIVATE STRAND FLUSH INCOMPLETE) (page 152)
• ROW CACHE LOCK (pages 169, 227)
• CURSOR: PIN S WAIT ON X (page 192)
• LIBRARY CACHE PIN (pages 192, 196)
• GC CR BLOCK 2-WAY, GC CR BLOCK 3-WAY, GC CURRENT BLOCK 2-WAY, GC CURRENT BLOCK 3-WAY (page 217)
• GC BUFFER BUSY (pages 224, 227)
• LATCH: GES RESOURCE HASH LIST (page 227)

 Functions:

• DBMS_SYSTEM.KSDWRT (write to trace file or alert log) (page 238)
• DBMS_SYSTEM.SET_EV (page 239)

 Latches:

• REDO ALLOCATION (pages 14, 126, 132, 134, 136, 152)
• REDO COPY (page 15)
• IN MEMORY UNDO LATCH (page 16)
• CACHE BUFFERS CHAINS (pages 106, 112, 114, 116, 178)
• CACHE BUFFERS LRU CHAIN (page 116)
• REDO WRITING (pages 125, 132)
• CHECKPOINT QUEUE LATCH (page 139, 141)
• ACTIVE CHECKPOINT QUEUE LATCH (pages 141, 146)
• LIBRARY CACHE, LIBRARY CACHE LOCK (page 175)
• SHARED POOL (pages 178, 190)

 Oracle Error Messages:

• ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction (page 13)
• ORA-08177: can’t serialize access for this transaction (page 35)
• ORA-01555: snapshot too old (page 56)
• ORA-22924: snapshot too old (page 57)
• ORA-00060: deadlock detected (pages 82-84, 232)
• WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK (page 169)
• ORA-01000: maximum open cursors exceeded (page 177)
• ORA-04031: unable to allocate %n bytes of memory(%s, %s, %s, %s) (pages 183, 190, 191)
• ORA-03113: end-of-file on communication channel (page 237)

The Transforming Face of the Oracle Support Site

December 23, 2011

A day or two ago I saw an announcement that the HTML (non-Flash) version of the Oracle Support site was to be phased out in January 2012.  It seems like the last time I tried to use that site, the search functionality did not quite work for Oracle Database products; I gave up on the HTML version of the site and went back to the Flash-based version of the site. 

The performance of the Flash-based version of Metalink (I usually navigate to the site by entering metalink.oracle.com into the web browser’s address bar) My Oracle Support has improved significantly since the initial launch, and it appears that once the website is loaded, it is a bit more stable now.  I do not own any i products (iPhone, iPad, iToy) which do not support Flash, so the Flash-based version of the Oracle support site works OK on most of my devices (Windows 7 64 bit with IE 9, Windows 7 32 bit with IE 8, Windows XP 32 bit, Motorola Xoom, Amazon BlackBerry Playbook, etc.) as long as Flash is supported in the web browser.  Now the shocker, the Flash-based version of the support site is also planned to be phased out, based on Metalink (MOS) Doc ID 1385682.1.

Turning the other cheek, or doing an about-face?  The support document mentions that ADF Faces will be used – the site will be built using Oracle Application Development Framework.  I hate to say that this is a case of Oracle eating its own dog food – maybe there is a better way to state that the support site will be using their own technology rather than the technology of a third party?  I wonder if the Oracle OTN forums are also being redesigned to use Oracle technology, rather than that of a third party?  On a related note, it appears that the OTN forum site has fixed the Internal Server Error problem.

—

Anyone found any additional information about the change to the support site?  Any experience with ADF Faces?

Idle Thoughts – SSD, Redo Logs, and Sector Size

December 18, 2011

It seems that I have been quite busy lately with computer related tasks that are not associated with Oracle Database, making it difficult to slow down and focus on items that have a foundation in logic.  Today I have had some spare time to dig further into the recently released “Oracle Core” book.  A Note on page 123 (obviously a logical page number) gave me a moment to pause.  Quoting a small portion of the Note:

“Recently disk manufacturers have started to produce disks with a 4KB sector size… From 11.2 Oracle lets the DBA choose which is the lesser of two evils by allowing you to specify the block size for the log file as 512 bytes, 1KB, or 4KB.” 

Do you see it yet, the reason to pause?

-

-

-

I am typing this blog article on a laptop that is a bit over a year old with two 256GB Crucial SSD drives in a RAID 0 arrangement.  Even though the laptop supports 3Gb/s transfer speeds from the internal drives rather than the more recent 6Gb/s transfer speeds, the drive arrangement is very quick (quick and potentially subject to a very bad data loss if one drive in the RAID 0 array fails), hitting a speed of 539 MB per second in a parallel full table scan.

I believe that the Crucial SSD drives have a 4 KB sector size, much like many other SSD drives.  It seems that some of Intel’s higher-end single layer drives use a 16 KB sector size (4000h = 16,384, see page 17 for the X25 series and page 22 for the 510 series).  I recall from reading the Oracle Database documentation the following statement:

“Unlike the database block size, which can be between 2K and 32K, redo log files always default to a block size that is equal to the physical sector size of the disk. Historically, this has typically been 512 bytes (512B).

Some newer high-capacity disk drives offer 4K byte (4K) sector sizes for both increased ECC capability and improved format efficiency. Most Oracle Database platforms are able to detect this larger sector size. The database then automatically creates redo log files with a 4K block size on those disks.” 

Do you see it yet, the reason to pause?

-

-

-

I recently read an interesting article titled De-Confusing SSD (for Oracle Databases) that inspired several interesting comments.  I think that the following two statements in the article contributed to the comment count:

SSD’s base memory unit is a cell, which holds 1 bit in SLC and 2 bits in MLC. Cells are organized in pages (usually 4k) and pages are organized in blocks (512K). Data can be read and written in pages, but is always deleted in blocks. This will become really important in a moment.

* Placing redo logs on SSD is not recommended. Exadata now has a “Smart Flash Logging” feature that uses redo logs on SSD. Note that it uses redo logs *also* on SSD. This feature allows Oracle to write redo in parallel to a file on SSD and a file on the magnetic disk, and finish the operation when one of the calls is successful.

Do you see it yet, the reason to pause?

-

-

-

So, the “Oracle Core” book states that as of Oracle Database 11.2 the DBA is able to select a 512 byte, 1024 byte, or 4096 byte (4 KB) block size for the redo log files.  Idle thinking… I have Oracle Database 11.2.0.2 installed on this laptop for educational purposes, let’s try executing a simple SQL statement that retrieves the block size for the redo log files:

SELECT
  GROUP#,
  BLOCKSIZE
FROM
  V$LOG
ORDER BY
  GROUP#;

GROUP#  BLOCKSIZE
------ ----------
     1        512
     2        512
     3        512 

So, if the sector size of my Crucial SSD drive is 4 KB, I have a (default) block size for the redo log files that potentially conflicts with the documentation, and if this is not an isolated issue, it might explain (in part) why the De-Confusing SSD (for Oracle Databases) article states that placing redo logs on SSD is not recommended.

Do you see it yet, the reason to pause?

-

-

-

I wonder if in testing redo log performance on SSD drives, if those drives were provided a “fair” test environment, where the BLOCKSIZE of the redo log files was sized appropriately for the write characteristics of SSD drives?  Any thoughts?

Internal Server Error – Contact Your System Administrator

December 6, 2011

The Oracle OTN forums changed a bit a year or two ago, and in the process I stopped receiving email notifications when new entries were added to discussion threads.  The absence of email notifications was not a significant loss, although at times it is a bit interesting to see how a post in some of the threads changed a week or two after the initial post.  It appears that the OTN staff have corrected the email notification problems, and the speed of the forums seems to have improved significantly since the dramatic performance drop that was caused by an upgrade to the OTN forums a couple of years ago.

An interesting new problem has arrived.  The unbreakable (but free) forums tell me to contact the system administrator after I attempt to log in – the web server claims that the problem is caused by either an internal error or a misconfiguration.  I tried calling the system administrator here, but his phone is busy every time I try to place the call, and remarkably I always get interrupted when I try calling from a different phone.    This is the error that I see immediately after logging in:

What is really irritating is that I received three emails today from OTN telling me that the OP has updated an OTN thread that I responded to, but sadly I cannot reach that thread.  After logging into OTN, I can’t even tell the browser to display forums.oracle.com – this is what I see: 

I can make it into Metalink (My Oracle Support) without an issue – I didn’t even need to log in (no password requested):

So, what happens if I click Sign Out in My Oracle Support?  Let’s try and then head back to forums.oracle.com (this seems to work sometimes):

So, the forums work, just as long as you do not care to contribute.    If we skip the login step, there are a couple of threads in the Community Feedback and Discussion forum about the problems (thread1, thread2).

Let’s log in again… (something comes to mind about the definition of insanity and doing something over and over again):

Out of curiosity, let’s see where forums.oracle.com is pointing:The traceroute is successful (ignore the long ping times – that was caused by other traffic on the Internet connection).

I noted that Google’s 8.8.8.8 DNS server is currently resolving forums.oracle.com also to e4606.b.akamaiedge.net which Google’s DNS server indicates is at IP address 184.25.198.174 (using Google’s DNS server does not change the error message that is displayed in the browser):

Without using Google’s DNS server, forums.oracle.com resolves to 23.1.18.174, as indicated by the trace route output.  I was curious what Wireshark might show when attempting to display forums.oracle.com (while logged in), so I fired it up and then told the browser to refresh the screen twice:

TCP ACKed lost segments…  Would the same problem happen when using Google’s DNS server, which points at IP address 184.25.198.174?  Let’s check:

Not exactly the same, but similar.  I have not spent a lot of time trying to dig through the Wireshark captures, but it is a bit odd that there are still TCP retransmissions (I might need to take a closer look at the Internet connection).

—

I guess that maybe this blog article drifted a bit.  Anyone else fail to connect 100% of the time, or never have a problem connecting?  I can only imagine the staff changes that probably would take place if one of our internal systems offered less than 95% uptime, much less the 99.99999% uptime that seems to be our internal expectation.  It is important to keep in mind that the OTN forums (even the copyrighted error message in the second screen capture) is a free service offered by Oracle Corporation – the problems will be resolved.

Stored Outlines (Plan Stability) are an Enterprise Edition Feature? A Self-Conflicting Story

December 2, 2011

An innocent question was asked in an OTN thread regarding the availability of plan stability options in the Standard Edition of Oracle Database.  If we check the documentation for the latest release (11.2.0.x) of Oracle Database, we will find the following statement:

“The use of stored outlines is deprecated in Oracle Database 11g Release 1 (11.1). Instead, you should use the SQL plan management feature that enables the optimizer to maintain a history of execution plans for a SQL statement.”

OK, so stored outlines are deprecated, even though stored outlines continue to work.  The documentation suggests using SQL Plan Management as a replacement for stored outlines.  That seems somewhat logical, because the Oracle Database documentation for 9.2 includes in its available feature list for the various Oracle Editions:

Plan Stability:

• Standard Edition: Not available
• Enterprise Edition: Available
• Personal Edition: Available

“Allows execution plans for SQL to be stored so that the plan remains consistent throughout schema changes, database reorganizations, and data volume changes.”

The same documentation also suggest checking the V$OPTION view to see which options are enabled for the particular Edition of Oracle Database that is installed.

Based on the information found in the documentation for the older Oracle Database version, using Outlines (Plan Stability) requires the Enterprise Edition of Oracle Database, so it seems as though the quote from the first documentation link offers a good suggestion to use the Enterprise Edition feature of SQL Plan Management in place of stored outlines.  A couple of people have pointed out on this blog various documentation errors, so let’s check with Oracle support (that was a good suggestion offered by one of the responders in the OTN thread).

An easy to find article in My Oracle Support is Metalink (MOS) Doc ID 100911.1, “V$OPTION Fixed Table and Support Releases and Options”.  That document states that an Enterprise Edition license is needed to use stored outlines.  The document mentions Oracle Database 8i, if I recall correctly.

A quick check of the book “Performance Tuning Recipes” finds on page 412 an indication that stored outlines are only supported on the Enterprise Edition of Oracle Database.

Based on the above, the situation does not appear to be too positive for the OP in the OTN thread.  Maybe we should spend some significant time digging through My Oracle Support.  If we are lucky, we might stumble across the following two articles that offer a different opinion:

• Metalink (MOS) Doc ID 271886.1, “Differences Between Different Editions of Oracle Database 10G Release 1
• Metalink (MOS) Doc ID 161556.1, “Differences between Oracle9i Personal, Standard and Enterprise on NT/2000″

If we continue searching, we will find an indication in the “Troubleshooting Oracle Performance” book on page 248 a statement that the Standard Edition is sufficient for the use of stored outlines.

We might even take the suggestion of the Oracle Database documentation and check the V$OPTION view, after confirming that we are connected to a Standard Edition 11.2.0.2 database:

SELECT
  *
FROM
  V$VERSION;

BANNER
---------------------------------------------------------
Oracle Database 11g Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SELECT
  PARAMETER,
  VALUE
FROM
  V$OPTION
ORDER BY
  DECODE(PARAMETER,'Plan Stability','1',PARAMETER);

PARAMETER                          VALUE
---------------------------------- -----
Plan Stability                     TRUE    <---------
Active Data Guard                  FALSE
Advanced Compression               FALSE
Advanced replication               FALSE
Application Role                   FALSE
Automatic Storage Management       FALSE
Backup Encryption                  FALSE
Basic Compression                  FALSE
Bit-mapped indexes                 FALSE
Block Change Tracking              FALSE
Block Media Recovery               FALSE
Change Data Capture                FALSE
Coalesce Index                     TRUE
Connection multiplexing            TRUE
Connection pooling                 TRUE
DICOM                              TRUE
Data Mining                        FALSE
Database queuing                   TRUE
Database resource manager          FALSE
Deferred Segment Creation          FALSE
Duplexed backups                   FALSE
Enterprise User Security           FALSE
Export transportable tablespaces   FALSE
Fast-Start Fault Recovery          FALSE
File Mapping                       FALSE
Fine-grained Auditing              FALSE
Fine-grained access control        FALSE
Flashback Data Archive             FALSE
Flashback Database                 FALSE
Flashback Table                    FALSE
Incremental backup and recovery    TRUE
Instead-of triggers                TRUE
Java                               TRUE
Join index                         FALSE
Managed Standby                    FALSE
Materialized view rewrite          FALSE
OLAP                               FALSE
OLAP Window Functions              TRUE
Objects                            TRUE
Online Index Build                 FALSE
Online Redefinition                FALSE
Oracle Data Guard                  FALSE
Oracle Database Vault              FALSE
Oracle Label Security              FALSE
Parallel backup and recovery       FALSE
Parallel execution                 FALSE
Parallel load                      TRUE
Partitioning                       FALSE
Point-in-time tablespace recovery  FALSE
Proxy authentication/authorization TRUE
Real Application Clusters          FALSE
Real Application Testing           FALSE
Result Cache                       FALSE
SQL Plan Management                FALSE
Sample Scan                        TRUE
SecureFiles Encryption             FALSE
Server Flash Cache                 FALSE
Spatial                            FALSE
Streams Capture                    FALSE
Transparent Application Failover   TRUE
Transparent Data Encryption        FALSE
Trial Recovery                     FALSE
Unused Block Compression           FALSE
XStream                            TRUE 

The above shows that Plan Stability is enabled for the Standard Edition, and thus usable with Standard Edition.  The innocent OTN question has thus led to a lot of effort to demonstrate that not only can the Oracle Database documentation be self-conflicting, but so can the My Oracle Support site.

It is interesting to note that AWR collection and the related features in Enterprise Manager are enabled by default in the Standard Edition of Oracle Database 10.1.0.x and 10.2.0.x.  However, just because these AWR related features are enabled by default does not mean that the features may be legally used in the Standard Edition of Oracle Database (or the Enterprise Edition without the additional cost Diagnostics Pack license).  I recall discussions in a couple of OTN threads where posters claimed that AWR related features were “free” with the Standard Edition, because those features were enabled by default – sorry, it does not work that way (if I recall correctly, partitioning is enabled by default in the Enterprise Edition, even though it is an additional cost option). 

In the OTN thread Pierre Forstmann offered the following helpful demonstration test case, which checks another item found in the V$OPTION view that is set to FALSE for the Standard Edition:

SQL> alter index emp_job_ix rebuild online;
alter index emp_job_ix rebuild online
*
ERROR at line 1:
ORA-00439: feature not enabled: Online Index Build 

As such, if stored outlines were not available on the Standard Edition of Oracle Database (just as OLAP Window Functions were not in 8.1.7.4 and Online Index Build are not in 11.2.0.2), using those features should result in an ORA-00439.

A couple of helpful articles that are related to stored outlines (most written by OakTable Network members):
http://oracle-randolf.blogspot.com/2009/03/plan-stability-in-10g-using-existing.html
http://kerryosborne.oracle-guy.com/2008/12/oracle-outlines-aka-plan-stability/
http://www.oracle-base.com/articles/misc/Outlines.php
http://www.jlcomp.demon.co.uk/outline_hack.html
http://jonathanlewis.wordpress.com/2010/03/11/dropping-outln/
http://www.oracle.com/technetwork/database/focus-areas/bi-datawarehousing/twp-upgrading-10g-to-11g-what-to-ex-133707.pdf

—

Any other good self-conflicting stories related to Oracle Database?

A Year Older, Any Wiser? 2

November 30, 2011

(Back to the Previous Post in the Series)

Today marks the second anniversary of this blog.  With just 372 articles posted in the last two years, the rate at which new articles are posted has decreased rather dramatically from the 1+ article per day average that was established shortly after the launch of the blog.  To celebrate the second anniversary, I thought that I would post a couple of statistics and bits of information about this blog:

• On October 21, 2011 there were 1,020 views of the articles on this blog, the most in any one day.  Yet on that day one of the most simple blog articles was posted.
• On a typical day search engines generate roughly half of the page views on this blog (this percentage increased in the last couple of months).  I periodically wonder if those people searching for Charles Hooper (currently the most common search keyword) are in search of something else (that link is safe to view at work, although it does recall a certain article by Richard Foote that was posted March 31, 2008).
• No article on this blog has ever resulted in an unintentional distributed denial of service attack (I am not explicitly pointing the finger at this article: http://jonathanlewis.wordpress.com/2011/11/28/oracle-core/ ). 
• The most viewed article since this blog went online (currently with roughly 7,530 views) was written roughly 23 months ago and describes a deadlock that can be triggered in Oracle Database 11.1.0.6 and greater, where the same sample code does not trigger a deadlock in Oracle Database 10.2.0.1 through 10.2.0.5 (earlier release versions not tested).
• The second most viewed article since this blog went online (at roughly 5,500 views) was written just 5.5 months ago and has more of a mathematics focus than an Oracle Database slant – more so than any other article on this blog.
• The third most viewed article since this blog went online (at roughly 4,200 views) describes a case where I made an error in testing Oracle Database 10.2.0.1 in 2006, and accidentally confused a partially related cause with an effect.  In the article I then tried to determine the coincidental cause and effect.  Interestingly, there is an odd association with the contents of that blog article with the false DMCA claim that was filed against one of my articles earlier this year.
• One Oracle Database book review article on this blog extended to roughly 18 typewritten (single spaced) pages in length, and I read that book twice from cover to cover.
• One Oracle Database book review article on this blog extended to roughly 24 typewritten (single spaced) pages in length, and that review covered ONLY the first 230 or so pages of the 1100+ page book plus a couple of other pages later in the book.
• One Oracle Database book review article on this blog extended to a record shattering 35.5 typewritten (single spaced) pages in length that excluded two of the book’s chapters, and due to the length of the review had to be divided into two separate blog articles.
• The blog category name Quiz – Whose Answer is it Anyway? is derived from the name of the TV show “Whose Line is it Anyway?” – a TV show where the actors improvise the show content in reaction to the behavior of other actors.  The articles in this category (58 articles by the latest count) demonstrate cases where unwitting actors (uh, authors) improvise their story of how Oracle Database works and how it does not work.
• Some of the blog articles have titles with double or triple meanings.  I sometimes accidentally embed humor in some of the articles – sometimes I don’t recognize the humor for days.
• It is occasionally difficult to develop unique blog article content that is not already better described in five blogs authored by other members of the Oracle community.  I monitor a couple of different discussion forums (OTN, Usenet, Oracle-L, and a forum operated for commercial purposes) to find interesting problems that might appeal to a wider audience.  I am still trying to determine if there is anything somewhat interesting about this article – how hard must the developer work to hide information from the query optimizer? 
• Some of the articles are posted in an incomplete form, knowing that helpful readers will often fill in the missing details… or the missing link.  If I could remember everything that I have forgotten about Oracle Database, I would have to forget something else – so thanks for the gentle reminders that I receive on occasion.
• I probably should have created a couple of more blog article categories: Documentation Errors; Test Cases; Ouch, that’s Not Supposed to Happen; and Where Did You get that Idea?.
• I pay a yearly fee to WordPress that allows my articles to stretch across your widescreen monitor, rather than being confined to the narrow width of the title banner at the top of this page.  I also pay a yearly fee to WordPress so that unwanted advertisements do not clutter the page for visitors not logged into a WordPress account.

Looking forward, I can only predict that there is a better than 50% chance that there will be a part three to this blog article series in 12 months.  I managed to purchase four books from Apress during their cyber Monday sale (in between the distributed denial of service attacks) a couple of days ago, including a book that I recently received in paperback form from Amazon.  I think that this might be a hint that there will be more Oracle Database book reviews posted to this blog in the future.

BIN$ Index Found in the Execution Plan – Digging through the Recycle Bin

November 28, 2011

There are a few articles that can be found on the Internet that describe the cause of indexes with names similar to BIN$ld5VAtb88PngQAB/AQF8hg==$0 and BIN$PESygWW5R0WhbOaDugxqwQ==$0 appearing in execution plans.  As is likely known by readers, the Oracle Database documentation describes that these object names are associated with the recycle bin that was introduced in Oracle Database 10.1.  When an object is dropped (but not purged), it is placed into the recycle bin with a name that begins with BIN$ and ends with ==$ followed by a number (the version, which in brief testing seems to always be 0).

I have answered this question a couple of times in the past in various Oracle Database forums, including a recent OTN thread.  What is the significance of having an index named, for instance, BIN$ld5VAtb88PngQAB/AQF8hg==$0 in an execution plan.  Does that mean that Oracle’s query optimizer has selected to use a dropped index?  No.  The simple answer is that the table to which the index belongs was dropped and then flashed back to before the drop.  When this happens, the table name is restored to its original name, but the names of the associated indexes are not restored.

A quick test case to demonstrate.  First, we will create a table with an index, and then collect statistics:

DROP TABLE T1 PURGE;

CREATE TABLE T1 AS
SELECT
  ROWNUM C1,
  RPAD('A',255,'A') C2
FROM
  DUAL
CONNECT BY
  LEVEL<=10000;

COMMIT;

CREATE INDEX IND_T1_C1 ON T1(C1);

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

Let’s put together a simple SQL statement that will hopefully use the index, and then confirm that the index was in fact used by displaying the actual execution plan used for the SQL statement:

SELECT
  C1,
  SUBSTR(C2,1,2)
FROM
  T1
WHERE
  C1<=5;

 C1 SU
--- --
  1 AA
  2 AA
  3 AA
  4 AA
  5 AA

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

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID  g0kkvxqg3v145, child number 0
-------------------------------------
SELECT   C1,   SUBSTR(C2,1,2) FROM   T1 WHERE   C1<=5

Plan hash value: 683303157

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

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C1"<=5) 

The above plan shows that the IND_T1_C1 index was used for this particular SQL statement.

Next, we will purge the recycle bin (make certain that there is nothing useful in the recycle bin first), drop the index, and see if it can still be used in an execution plan:

DROP INDEX IND_T1_C1;

SELECT
  C1,
  SUBSTR(C2,1,2)
FROM
  T1
WHERE
  C1<=5;

 C1 SU
--- --
  1 AA
  2 AA
  3 AA
  4 AA
  5 AA

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID  g0kkvxqg3v145, child number 0
-------------------------------------
SELECT   C1,   SUBSTR(C2,1,2) FROM   T1 WHERE   C1<=5

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |   137 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |     5 |  1300 |   137   (5)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("C1"<=5)

/* SHOW RECYCLEBIN  should be roughly equivalent to the following SQL statement */
SELECT
  OBJECT_NAME,
  ORIGINAL_NAME,
  TYPE,
  CREATETIME
FROM
  RECYCLEBIN
ORDER BY
  TYPE,
  ORIGINAL_NAME,
  OBJECT_NAME;

no rows selected 

From the above, it is apparent that an index that is dropped will not appear in an execution plan.  The dropped index is not in the recycle bin either.

Let’s recreate the index:

CREATE INDEX IND_T1_C1 ON T1(C1);

Then, using SQL statements similar to those at the start of this article (without the PURGE clause in the DROP TABLE statement), we will drop and recreate the table:

DROP TABLE T1;

CREATE TABLE T1 AS
SELECT
  ROWNUM C1,
  RPAD('A',255,'A') C2
FROM
  DUAL
CONNECT BY
  LEVEL<=10000;

COMMIT;

CREATE INDEX IND_T1_C1 ON T1(C1);

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

Next, let’s take a look at the recycle bin (note that we could simply execute SHOW RECYCLEBIN rather than execute the SQL statement, but the index would not be listed using that method):

SELECT
  OBJECT_NAME,
  ORIGINAL_NAME,
  TYPE,
  CREATETIME
FROM
  RECYCLEBIN
ORDER BY
  TYPE,
  ORIGINAL_NAME,
  OBJECT_NAME,
  CREATETIME;

OBJECT_NAME                    ORIGINAL_N TYPE  CREATETIME
------------------------------ ---------- ----- -------------------
BIN$cU4bWUSaSu2PUYdJvOq+hA==$0 IND_T1_C1  INDEX 2011-11-28:07:21:30
BIN$2smXLnTGTSqcBa8SJucvtg==$0 T1         TABLE 2011-11-28:07:21:30 

The above shows that we now have one table and its index in the recycle bin.  Let’s repeat the drop and recreate:

DROP TABLE T1;

CREATE TABLE T1 AS
SELECT
  ROWNUM C1,
  RPAD('A',255,'A') C2
FROM
  DUAL
CONNECT BY
  LEVEL<=10000;

COMMIT;

CREATE INDEX IND_T1_C1 ON T1(C1);

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

Now let’s take a look at the recycle bin’s contents again:

SELECT
  OBJECT_NAME,
  ORIGINAL_NAME,
  TYPE,
  CREATETIME
FROM
  RECYCLEBIN
ORDER BY
  TYPE,
  ORIGINAL_NAME,
  OBJECT_NAME,
  CREATETIME;

OBJECT_NAME                    ORIGINAL_N TYPE  CREATETIME
------------------------------ ---------- ----- -------------------
BIN$cU4bWUSaSu2PUYdJvOq+hA==$0 IND_T1_C1  INDEX 2011-11-28:07:21:30
BIN$/40oC3RJSNiLmEESZ7VNEw==$0 IND_T1_C1  INDEX 2011-11-28:07:21:48
BIN$2smXLnTGTSqcBa8SJucvtg==$0 T1         TABLE 2011-11-28:07:21:30
BIN$nYId4wdGRf6IgpSXSDb4Kw==$0 T1         TABLE 2011-11-28:07:21:48 

The above output now shows that there are two tables and their associated indexes in the recycle bin.  Let’s recover one of those tables and its index:

FLASHBACK TABLE T1 TO BEFORE DROP;

Flashback complete. 

A quick check of the recycle bin shows that the most recently dropped table and its associated index are no longer in the recycle bin, but the older version of table T1 and its index are still in the recycle bin:

SELECT
  OBJECT_NAME,
  ORIGINAL_NAME,
  TYPE,
  CREATETIME
FROM
  RECYCLEBIN
ORDER BY
  TYPE,
  ORIGINAL_NAME,
  OBJECT_NAME,
  CREATETIME; 

OBJECT_NAME                    ORIGINAL_N TYPE  CREATETIME
------------------------------ ---------- ----- -------------------
BIN$cU4bWUSaSu2PUYdJvOq+hA==$0 IND_T1_C1  INDEX 2011-11-28:07:21:30
BIN$2smXLnTGTSqcBa8SJucvtg==$0 T1         TABLE 2011-11-28:07:21:30

Let’s re-execute the SQL statement that queries table T1:

SELECT
  C1,
  SUBSTR(C2,1,2)
FROM
  T1
WHERE
  C1<=5;

 C1 SU
--- --
  1 AA
  2 AA
  3 AA
  4 AA
  5 AA 

So, after recoving the table from the recycle bin, we are able to query the table.  Let’s take a look at the execution plan for this query:

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID  g0kkvxqg3v145, child number 0
-------------------------------------
SELECT   C1,   SUBSTR(C2,1,2) FROM   T1 WHERE   C1<=5

Plan hash value: 1395723482

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                                |       |       |     3 (100)|       |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1                             |     5 |  1300 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | BIN$/40oC3RJSNiLmEESZ7VNEw==$0 |     5 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C1"<=5) 

Notice in the above execution plan, the index name of BIN$/40oC3RJSNiLmEESZ7VNEw==$0 – that is what the index was named when it was sent to the recycle bin.  Let’s fix the odd BIN$ name and re-execute the query:

ALTER INDEX "BIN$/40oC3RJSNiLmEESZ7VNEw==$0" RENAME TO IND_T1_C1;

Index altered. 

SELECT
  C1,
  SUBSTR(C2,1,2)
FROM
  T1
WHERE
  C1<=5;

 C1 SU
--- --
  1 AA
  2 AA
  3 AA
  4 AA
  5 AA

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

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID  g0kkvxqg3v145, child number 0
-------------------------------------
SELECT   C1,   SUBSTR(C2,1,2) FROM   T1 WHERE   C1<=5

Plan hash value: 683303157

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

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C1"<=5)

So, the above shows how a BIN$ named index might appear in an execution plan, and how to fix the name.

—

We still have one table and its index in the recycle bin.  Let’s take a quick look at that table:

SELECT
  C1,
  SUBSTR(C2,1,2)
FROM
  "BIN$2smXLnTGTSqcBa8SJucvtg==$0"
WHERE
  C1<=5;

 C1 SU
--- --
  1 AA
  2 AA
  3 AA
  4 AA
  5 AA 

We are able to query the table that is in the recycle bin, as long as we enclose the table name (OBJECT_NAME in the query of RECYCLEBIN) in quotation marks (“).  Let’s take a look at the execution plan for the previous SQL statement:

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID  a2a2vcsbtw5ac, child number 0
-------------------------------------
SELECT   C1,   SUBSTR(C2,1,2) FROM   "BIN$2smXLnTGTSqcBa8SJucvtg==$0"
WHERE   C1<=5

Plan hash value: 3681245720

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                                |       |       |     3 (100)|       |
|   1 |  TABLE ACCESS BY INDEX ROWID| BIN$2smXLnTGTSqcBa8SJucvtg==$0 |     5 |  1300 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | BIN$cU4bWUSaSu2PUYdJvOq+hA==$0 |     5 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C1"<=5) 

Now we have both a BIN$ prefixed table and index in the execution plan.

Let’s recover the old version of the T1 table (renaming it when it is recovered) and rename its associated recovered index:

FLASHBACK TABLE T1 TO BEFORE DROP RENAME TO T1_OLD;

Flashback complete.

ALTER INDEX "BIN$cU4bWUSaSu2PUYdJvOq+hA==$0" RENAME TO IND_T1_OLD_C1;

Index altered. 

Let’s query the recovered table (now called T1_OLD) and check the execution plan:

SELECT
  C1,
  SUBSTR(C2,1,2)
FROM
  T1_OLD
WHERE
  C1<=5;

 C1 SU
--- --
  1 AA
  2 AA
  3 AA
  4 AA
  5 AA

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

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
SQL_ID  bv1ty7jq2hc5g, child number 0
-------------------------------------
SELECT   C1,   SUBSTR(C2,1,2) FROM   T1_OLD WHERE   C1<=5

Plan hash value: 3358254750

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1_OLD        |     5 |  1300 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T1_OLD_C1 |     5 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C1"<=5) 

Now let’s make certain that there is nothing in the recycle bin:

SELECT
  OBJECT_NAME,
  ORIGINAL_NAME,
  TYPE,
  CREATETIME
FROM
  RECYCLEBIN
ORDER BY
  TYPE,
  ORIGINAL_NAME,
  OBJECT_NAME,
  CREATETIME;

no rows selected 

The experimental tables and indexes are not in the recycle bin.  Let’s drop the experimental tables that we recovered from the recycle bin, this time skipping the recycle bin:

DROP TABLE T1 PURGE;
DROP TABLE T1_OLD PURGE; 

Just to confirm that the tables and their associated indexes are not in the recycle bin:

SELECT
  OBJECT_NAME,
  ORIGINAL_NAME,
  TYPE,
  CREATETIME
FROM
  RECYCLEBIN
ORDER BY
  TYPE,
  ORIGINAL_NAME,
  OBJECT_NAME,
  CREATETIME;

no rows selected 

—–

Hopefully, by now everyone is aware of those BIN$ prefixed object names that might appear in execution plans, what causes the names to be created, and how to fix the names.

Why Isn’t My Index Used… When USER2 Executes this Query?

November 23, 2011

I previously wrote a couple of articles that mention reasons why an index might not be use for a particular query, including an article that was formatted as a True or False quiz with several reference articles.  A few days ago I saw an OTN thread that caught my curiosity, where the original poster (OP) claimed that the optimizer simply will not use an index to access a table when any user other than the schema owner or the SYS user executes a particular query.

Why is the OP attempting to execute the SQL statement as the SYS user?  The SYS user is special.  As mentioned in my review of the book “Practical Oracle 8i“, as I read the book I wrote the following paraphrase into my notes, the SYS user is special:

Oracle 8i introduces row level security, which uses a PL/SQL function to apply an additional WHERE clause predicate to a table – row level security does not apply to the SYS user. It is important to use CONSISTENT=Y when exporting partitioned tables. When CONSISTENT=N is specified, the export of each partition in a table is treated as a separate transaction, and may be exported at a different SCN number (incremented when any session commits). When tables are exported which contain nested tables, the two physical segments are exported in separate transactions, potentially resulting in inconsistent data during the import if the export was performed with the default CONSISTENT=N.

Is the above paraphrase from this 10 year old book a clue?  Maybe it is a problem related to secure view merging because the SQL statement uses the index when the schema owner executes the SQL statement (there is a very good example of this type of problem found in the book “Troubleshooting Oracle Performance“).  Maybe it is a problem where the public synonym for the table actually points to a view or an entirely different table – the execution plan for the non-schema owner did show a VIEW operation, while the execution plan for the schema owner did not show the VIEW operation.  Maybe it is a problem where the optimizer parameters are adjusted differently for different users – in such a case we might need to dig into the V$SYS_OPTIMIZER_ENV, V$SES_OPTIMIZER_ENV, and V$SQL_OPTIMIZER_ENV views.

Maybe taking a look at the DBMS_XPLAN output would help.  Why does the Predicate Information section of the execution plan show the following only for the non-schema owner?

7 - filter(("SEAL_FLAG" IS NULL OR "SEAL_FLAG"<>'Y'))
9 - filter(("SEAL_FLAG" IS NULL OR "SEAL_FLAG"<>'Y'))
11 - filter(("SEAL_FLAG"<>'Y' OR "SEAL_FLAG" IS NULL))
13 - filter(("SEAL_FLAG"<>'Y' OR "SEAL_FLAG" IS NULL))
19 - filter(("SEAL_FLAG"<>'Y' OR "SEAL_FLAG" IS NULL)) 

A significant clue?  If those predicates were also found in the DBMS_XPLAN generated output for the schema owner (and the SYS user), I would probably conclude that the optimizer generated those additional predicates from defined column constraints, and that a review of a 10053 trace file might help determine what caused those predicates to be automatically created.  However, those predicates did not appear in the execution plan that was generated for the schema owner.  It might be time to start checking the V$VPD_POLICY view for this particular SQL_ID, for example (a completely unrelated test case output):

SELECT
  *
FROM
  V$VPD_POLICY
WHERE
  SQL_ID='6hqw5p9d8g8wf';

ADDRESS          PARADDR            SQL_HASH SQL_ID        CHILD_NUMBER OBJECT_OWNER OBJECT_NAME                    POLICY_GROUP                   POLICY                 POLICY_FUNCTION_OWNER          PREDICATE
---------------- ---------------- ---------- ------------- ------------ ------------ ------------------------------ ------------------------------ ---------------------- ------------------------------ ------------------------------------------------------------------------------------
000007FFB7701608 000007FFB7743350 1518838670 6hqw5p9d8g8wf            0 TESTUSER     T12                            SYS_DEFAULT                    T_SEC                  TESTUSER                       ID < 10 

Maybe we should also check some of the other virtual private database (VPD) related views including ALL_POLICIES (once again from a completely unrelated test case):

SELECT
  *
FROM
  ALL_POLICIES;

OBJECT_OWNER                   OBJECT_NAME                    POLICY_GROUP                  POLICY_NAME                    PF_OWNER                       PACKAGE                       FUNCTION                       SEL INS UPD DEL IDX CHK ENA STA POLICY_TYPE              LON
------------------------------ ------------------------------ ----------------------------- ------------------------------ ------------------------------ ----------------------------- ------------------------------ --- --- --- --- --- --- --- --- ------------------------ ---
TESTUSER                       T12                            SYS_DEFAULT                   T_SEC                          TESTUSER                       S                                                            YES YES YES YES NO  NO  YES NO  DYNAMIC                  NO 

There are known performance problems related to the use of VPD, some of which are Oracle Database version dependent, and some of which have been corrected in recent versions.  Maybe a quick check of one of the following articles would help, if the OP finds that VPD is in fact in use (the second article provides step by step directions for investigation):

• Metalink (MOS) Doc ID 728292.1 “Known Performance Issues When Using TDE and Indexes on the Encrypted Columns”
• Metalink (MOS) Doc ID 967042.1 “How to Investigate Query Performance Regressions Caused by VPD (FGAC) Predicates?”

—

Take a look at the OTN thread.  Any other suggestions for the OP?

Select For Update – In What Order are the Rows Locked?

November 21, 2011

A recent thread in the comp.databases.oracle.server usenet group asked whether or not a SELECT FOR UPDATE statement locks rows in the order specified by the ORDER BY clause.  Why might this be an important question?  Possibly if the SKIP LOCKED clause is implemented in the SELECT FOR UPDATE statement?  Possibly if a procedure is hanging, and it is important to know at what point an enqueue happened?  Any other reasons?

Without testing, I would have suggested that the rows are locked as the rows are read from the table blocks, and not after the ORDER BY clause alters the order of the rows.  Why?  Oracle Database is fundamentally lazy, or put another way, fundamentally efficient – in general it does not perform unnecessary work.  Locking the rows after applying the ORDER BY clause would require a second visit to the table blocks (possibly having to visit each block multiple times to lock different rows in the same block) in the order specified by the ORDER BY clause.  Such an approach could be incredibly inefficient and also error prone (what happens if a row was locked by a second session while the first session was sorting the rows per the ORDER BY clause?).

We could guess, but why guess when we are able to easily test the theory?  Let’s create a simple table with 10,000 rows:

DROP TABLE T1 PURGE;

CREATE TABLE T1 AS
SELECT
  ROWNUM C1,
  RPAD('A',255,'A') C2
FROM
  DUAL
CONNECT BY
  LEVEL<=10000;

COMMIT; 

Now we need 2 sessions (I will call them Session 1 and Session 2).  We will execute the same SELECT FOR UPDATE statement in both sessions, with Session 1 first selecting the table rows in ascending order and then Session 2 selecting the table rows in descending order.  If the ORDER BY clause determines the order in which the rows are locked, the row with a C1 value of 10,000 should be identified as the row that caused the enqueue because that is the first row that should be returned to Session 2.

In Session 1:

SELECT
  C1,
  C2
FROM
  T1
WHERE
  MOD(C1,100)=0
ORDER BY
  C1
FOR UPDATE; 

In Session 2:

SELECT
  C1,
  C2
FROM
  T1
WHERE
  MOD(C1,100)=0
ORDER BY
  C1 DESC
FOR UPDATE; 

(Session 2 is hung)

Let’s try to identify the row that Session 2 is waiting to lock.  There are a couple of ways to accomplish this task (on Oracle Database 10.1 and higher the join to the V$SESSION_WAIT view is unnecessary because that information is found in V$SESSION).

In Session 1:

COLUMN EVENT FORMAT A20
COLUMN OBJ# FORMAT 999999
COLUMN BLOCK# FORMAT 999999
COLUMN ROW# FORMAT 99

SELECT
  SW.EVENT,
  S.ROW_WAIT_OBJ# OBJ#,
  S.ROW_WAIT_FILE# FILE#,
  S.ROW_WAIT_BLOCK# BLOCK#,
  S.ROW_WAIT_ROW# ROW#
FROM
  V$SESSION_WAIT SW,
  V$SESSION S
WHERE
  S.USERNAME IS NOT NULL
  AND SW.SID=S.SID
  AND SW.EVENT NOT LIKE '%SQL*Net%'
  AND SW.EVENT NOT IN ('Streams AQ: waiting for messages in the queue', 'wait for unread message on broadcast channel');

EVENT                   OBJ#      FILE#  BLOCK# ROW#
-------------------- ------- ---------- ------- ----
enq: TX - row lock c   71913          4    4262   18
ontention 

We have the object ID (71913 – technically we need the DATA_OBJECT_ID, not the OBJECT_ID for the DBMS_ROWID.ROWID_CREATE call, but the two values will be identical in this test case), absolute file number (4), block (4262), and row (18) in the block that caused the enqueue.  Let’s select that row from the table:

SELECT
  C1
FROM
  T1
WHERE
  ROWID=DBMS_ROWID.ROWID_CREATE(1, 71913, 4, 4262, 18);

 C1
---
100 

The row with a C1 value of 100 is the first row that was returned by Session 1 (it will be the last row requested to be returned by Session 2), but it is also the first row that matched the WHERE clause predicates for Session 2′s SQL statement as the blocks were read (you could confirm the order in which the blocks are read by flushing the buffer cache before the select, setting the DB_FILE_MULTIBLOCK_READ_COUNT parameter at the session level to 1, enabling a 10046 trace, and then executing the SELECT FOR UPDATE statement).

Let’s retrieve the execution plan for Session 2 to determine why the original poster (OP) might have throught that the rows were locked after the ORDER BY is applied (note that the SQL_ID and SQL_CHILD_NUMBER columns only exist in V$SESSION as of Oracle Database 10.1, so technically the join to V$SESSION_WAIT is unnecessary; however, for consistency with the previous SQL statement that determined the locked row, the join to the V$SESSION_WAIT view is included):

SELECT
  S.SQL_ID,
  S.SQL_CHILD_NUMBER
FROM
  V$SESSION_WAIT SW,
  V$SESSION S
WHERE
  S.USERNAME IS NOT NULL
  AND SW.SID=S.SID
  AND SW.EVENT NOT LIKE '%SQL*Net%'
  AND SW.EVENT NOT IN ('Streams AQ: waiting for messages in the queue', 'wait for unread message on broadcast channel');

SQL_ID        SQL_CHILD_NUMBER
------------- ----------------
2dnpymtj0rc1r                0 

Now retrieving the execution plan:

SELECT
  *
FROM
  TABLE(DBMS_XPLAN.DISPLAY_CURSOR('2dnpymtj0rc1r',0,'TYPICAL'));

SQL_ID  2dnpymtj0rc1r, child number 0
-------------------------------------
SELECT   C1,   C2 FROM   T1 WHERE   MOD(C1,100)=0 ORDER BY   C1 DESC
FOR UPDATE

Plan hash value: 3991553210

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |    33 (100)|          |
|   1 |  FOR UPDATE         |      |       |       |            |          |
|   2 |   SORT ORDER BY     |      |   123 | 17466 |    33   (4)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| T1   |   123 | 17466 |    32   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(MOD("C1",100)=0)

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

As mentioned by the OP, the execution plan appears to be slightly misleading – unless of course you remember that locking the rows after sorting the rows based on the ORDER BY clause  would require revisiting the rows in the table blocks.  The situation could be different in this test case if there was an index on column C1.  In such a case the index could be read in descending order, thus making it appear that the rows were attempted to be locked in the order described by the ORDER BY clause.

Let’s slightly adapt the original test case to test the second theory, that it could appear that the rows are locked in the order specified by the ORDER BY clause.

In Session 1:

ROLLBACK;

In Session 2:

ROLLBACK;

Now we will create an index on column C1.

In Session 1:

CREATE INDEX IND_T1_C1 ON T1(C1);

Now the revised test begins.

In Session 1:

SELECT /*+ INDEX(T1) */
  C1,
  C2
FROM
  T1
WHERE
  C1<=100
ORDER BY
  C1
FOR UPDATE;

In Session 2:

SELECT /*+ INDEX(T1) */
  C1,
  C2
FROM
  T1
WHERE
  C1<=100
ORDER BY
  C1 DESC
FOR UPDATE;

(Session 2 is hung)

Let’s try to identify the row that Session 2 is waiting to lock.

In Session 1:

COLUMN EVENT FORMAT A20
COLUMN OBJ# FORMAT 999999
COLUMN BLOCK# FORMAT 999999
COLUMN ROW# FORMAT 99

SELECT
  SW.EVENT,
  S.ROW_WAIT_OBJ# OBJ#,
  S.ROW_WAIT_FILE# FILE#,
  S.ROW_WAIT_BLOCK# BLOCK#,
  S.ROW_WAIT_ROW# ROW#
FROM
  V$SESSION_WAIT SW,
  V$SESSION S
WHERE
  S.USERNAME IS NOT NULL
  AND SW.SID=S.SID
  AND SW.EVENT NOT LIKE '%SQL*Net%'
  AND SW.EVENT NOT IN ('Streams AQ: waiting for messages in the queue', 'wait for unread message on broadcast channel');

EVENT                   OBJ#      FILE#  BLOCK# ROW#
-------------------- ------- ---------- ------- ----
enq: TX - row lock c   71913          4    4259    0
ontention 

Notice in the above output, now block number 4259 is identified, while in the earlier test script block number 4262 was identified by the above SQL statement.  We have the object ID (we actually need the DATA_OBJECT_ID, but the values will be the same in this test case), the absolute file number, the block number, and the row number in the block (0 in this case).  Let’s select that row from the table:

SELECT
  C1
FROM
  T1
WHERE
  ROWID=DBMS_ROWID.ROWID_CREATE(1, 71913, 4, 4259, 0);

 C1
---
 1 

The row with a C1 value of 1 is the first row that was returned by Session 1 (it will be the last row requested to be returned by Session 2), but it is also the first row that matched the WHERE clause predicates for Session 2′s SQL statement as the blocks were read.  This is the same result as we saw before – maybe it does not matter whether an index access path is used to avoid the sort operation that would be otherwise required to satisfy the ORDER BY clause – is the second theory false?  Before making that determination, let’s take a look at the execution plan for Session 2′s SQL statement:

SELECT
  S.SQL_ID,
  S.SQL_CHILD_NUMBER
FROM
  V$SESSION_WAIT SW,
  V$SESSION S
WHERE
  S.USERNAME IS NOT NULL
  AND SW.SID=S.SID
  AND SW.EVENT NOT LIKE '%SQL*Net%'
  AND SW.EVENT NOT IN ('Streams AQ: waiting for messages in the queue', 'wait for unread message on broadcast channel');

SQL_ID        SQL_CHILD_NUMBER
------------- ----------------
3yz7pu7rw5cw0                0

SELECT
  *
FROM
  TABLE(DBMS_XPLAN.DISPLAY_CURSOR('3yz7pu7rw5cw0',0,'TYPICAL'));

Plan hash value: 3432103074

-------------------------------------------------------------------------------------------
| Id  | Operation                     | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |           |       |       |     7 (100)|          |
|   1 |  FOR UPDATE                   |           |       |       |            |          |
|   2 |   SORT ORDER BY               |           |   100 | 14200 |     7  (15)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1        |   100 | 14200 |     6   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IND_T1_C1 |   100 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("C1"<=100)

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

The index that we created was definitely used, but notice that there is still a SORT ORDER BY operation in the execution plan.  The rows in the index were read in ascending order, not descending order!  Let’s try again using an INDEX_DESC hint in the SQL statement for Session 2.

In Session 1:

ROLLBACK;

In Session 2:

ROLLBACK;

In Session 1:

SELECT /*+ INDEX(T1) */
  C1,
  C2
FROM
  T1
WHERE
  C1<=100
ORDER BY
  C1
FOR UPDATE;

In Session 2:

SELECT /*+ INDEX_DESC(T1) */
  C1,
  C2
FROM
  T1
WHERE
  C1<=100
ORDER BY
  C1 DESC
FOR UPDATE;

(Session 2 is hung)

Let’s determine the row that Session 2 is waiting to lock.

In Session 1:

SELECT
  SW.EVENT,
  S.ROW_WAIT_OBJ# OBJ#,
  S.ROW_WAIT_FILE# FILE#,
  S.ROW_WAIT_BLOCK# BLOCK#,
  S.ROW_WAIT_ROW# ROW#
FROM
  V$SESSION_WAIT SW,
  V$SESSION S
WHERE
  S.USERNAME IS NOT NULL
  AND SW.SID=S.SID
  AND SW.EVENT NOT LIKE '%SQL*Net%'
  AND SW.EVENT NOT IN ('Streams AQ: waiting for messages in the queue', 'wait for unread message on broadcast channel');

EVENT                   OBJ#      FILE#  BLOCK# ROW#
-------------------- ------- ---------- ------- ----
enq: TX - row lock c   71913          4    4262   18
ontention

SELECT
  C1
FROM
  T1
WHERE
  ROWID=DBMS_ROWID.ROWID_CREATE(1, 71913, 4, 4262, 18);

 C1
---
100

The above output now shows that Session 2 is attempting to lock the first row (with a C1 value of 100) that it intends to return.  Has the execution plan changed?

SELECT
  S.SQL_ID,
  S.SQL_CHILD_NUMBER
FROM
  V$SESSION_WAIT SW,
  V$SESSION S
WHERE
  S.USERNAME IS NOT NULL
  AND SW.SID=S.SID
  AND SW.EVENT NOT LIKE '%SQL*Net%'
  AND SW.EVENT NOT IN ('Streams AQ: waiting for messages in the queue', 'wait for unread message on broadcast channel');

SQL_ID        SQL_CHILD_NUMBER
------------- ----------------
806mtjxk7k1dv                0

SELECT
  *
FROM
  TABLE(DBMS_XPLAN.DISPLAY_CURSOR('806mtjxk7k1dv',0,'TYPICAL'));

Plan hash value: 3814195162

--------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |           |       |       |     6 (100)|          |
|   1 |  FOR UPDATE                    |           |       |       |            |          |
|   2 |   BUFFER SORT                  |           |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID | T1        |   100 | 14200 |     6   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN DESCENDING| IND_T1_C1 |   100 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("C1"<=100)

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

Notice in the above execution plan that there is no longer a SORT ORDER BY operation in the execution plan (replaced by a BUFFER SORT operation) and the INDEX RANGE SCAN operation was also replaced by an INDEX RANGE SCAN DESCENDING operation.  Simply because the index range scan is performed in the same order as specified by the ORDER BY clause, the rows are locked in the same order as is specified by the ORDER BY clause – that is, after all, the order in which the rows were touched.

Any other ideas for a demonstration of the order in which rows are locked when a SELECT FOR UPDATE is used?

Matching the Expected Output – Analytic RANK, ROW_NUMBER, DENSE_RANK, or Something Different?

November 16, 2011

I noticed an interesting request on the comp.databases.oracle.misc usenet group, and I thought that I would make an attempt at solving the request.  The original poster (OP) stated that he had a table with data similar to the following:

TX   ID   DEPT   LOCATION   LOAD
1    99    A        NY       12
2    99    A        LA       10
3    99    B        LA       05
4    77    B        LA       15
5    77    C        NY       12
6    77    D        LA       11  

He would like to obtain the following output:

TX   ID   DEPT   DEPT_RANK   LOCATION   LOC_RANK   LOAD
1    99    A         1          NY         2        12
2    99    A         1          LA         1        10
3    99    B         2          LA         1        05
4    77    B         1          LA         1        15
5    77    C         2          NY         2        12
6    77    D         3          LA         1        11  

The rankings are to be determined as follows:

DEPT_RANK for ID 99 is 1 for A because sum(LOAD) = 22 is the max
LOC_RANK for ID 99 is 1 for LA because sum(LOAD) = 15 is the max

At first glance, that request seems to be reasonably easy to accomplish.  Let’s start by creating a table with the sample data (ideally, the OP should have provided the DDL and DML to create and populate this table):

DROP TABLE T1 PURGE;

CREATE TABLE T1 (
  TX NUMBER,
  ID NUMBER,
  DEPT VARCHAR2(1),
  LOCATION VARCHAR2(2),
  LOAD NUMBER);

INSERT INTO T1 VALUES (1,99,'A','NY',12);
INSERT INTO T1 VALUES (2,99,'A','LA',10);
INSERT INTO T1 VALUES (3,99,'B','LA',05);
INSERT INTO T1 VALUES (4,77,'B','LA',15);
INSERT INTO T1 VALUES (5,77,'C','NY',12);
INSERT INTO T1 VALUES (6,77,'D','LA',11); 

The first step, if we were to think about creating the solution in logical steps, is to find a way to calculate the SUM values that were mentioned by the OP.  So, as a starting point, we might try this:

SELECT
  TX,
  ID,
  DEPT,
  SUM(LOAD) OVER (PARTITION BY ID,DEPT) SUM_LOAD_ID,
  LOCATION,
  SUM(LOAD) OVER (PARTITION BY ID,LOCATION) SUM_LOAD_LOCATION,
  LOAD
FROM
  T1
ORDER BY
  TX;

 TX  ID D SUM_LOAD_ID LO SUM_LOAD_LOCATION  LOAD
--- --- - ----------- -- ----------------- -----
  1  99 A          22 NY                12    12
  2  99 A          22 LA                15    10
  3  99 B           5 LA                15     5
  4  77 B          15 LA                26    15
  5  77 C          12 NY                12    12
  6  77 D          11 LA                26    11 

If I am understanding the OP’s request correctly, the above is a good starting point (even though the alias for the first analytic function could have been better selected).

We are then able to take the above SQL statement and push it into an inline view to hopefully produce the output that is expected by the OP  (note that the PARTITION clause differs for the LOC_RANK column from what is specified in the inline view for the function that is used to create that column):

SELECT
  TX,
  ID,
  DEPT,
  RANK() OVER (PARTITION BY ID,DEPT ORDER BY SUM_LOAD_ID DESC) DEPT_RANK,
  LOCATION,
  RANK() OVER (PARTITION BY ID,DEPT ORDER BY SUM_LOAD_LOCATION DESC) LOC_RANK,
  LOAD
FROM
  (SELECT
    TX,
    ID,
    DEPT,
    SUM(LOAD) OVER (PARTITION BY ID,DEPT) SUM_LOAD_ID,
    LOCATION,
    SUM(LOAD) OVER (PARTITION BY ID,LOCATION) SUM_LOAD_LOCATION,
    LOAD
  FROM
    T1)
ORDER BY
  TX;

 TX  ID D  DEPT_RANK LO   LOC_RANK  LOAD
--- --- - ---------- -- ---------- -----
  1  99 A          1 NY          2    12
  2  99 A          1 LA          1    10
  3  99 B          1 LA          1     5
  4  77 B          1 LA          1    15
  5  77 C          1 NY          1    12
  6  77 D          1 LA          1    11 

Let’s compare the above output with what the OP requested:

TX   ID   DEPT   DEPT_RANK   LOCATION   LOC_RANK   LOAD
1    99    A         1          NY         2        12
2    99    A         1          LA         1        10
3    99    B         2          LA         1        05
4    77    B         1          LA         1        15
5    77    C         2          NY         2        12
6    77    D         3          LA         1        11

Almost for the LOC_RANK column (not even close for the DEPT_RANK column), but not quite right.  The problem is that when attempting to calculate the RANK columns in the above output, we should only PARTITION on the ID column, not the ID column and some other column, as was the case when we used the SUM analytic function.

Let’s fix the PARTITION clause and try again:

SELECT
  TX,
  ID,
  DEPT,
  RANK() OVER (PARTITION BY ID ORDER BY SUM_LOAD_ID DESC) DEPT_RANK,
  LOCATION,
  RANK() OVER (PARTITION BY ID ORDER BY SUM_LOAD_LOCATION DESC) LOC_RANK,
  LOAD
FROM
  (SELECT
    TX,
    ID,
    DEPT,
    SUM(LOAD) OVER (PARTITION BY ID,DEPT) SUM_LOAD_ID,
    LOCATION,
    SUM(LOAD) OVER (PARTITION BY ID,LOCATION) SUM_LOAD_LOCATION,
    LOAD
  FROM
    T1)
ORDER BY
  TX;

 TX  ID D  DEPT_RANK LO   LOC_RANK  LOAD
--- --- - ---------- -- ---------- -----
  1  99 A          1 NY          3    12
  2  99 A          1 LA          1    10
  3  99 B          3 LA          1     5
  4  77 B          1 LA          1    15
  5  77 C          2 NY          3    12
  6  77 D          3 LA          1    11 

Let’s compare the above output with what the OP requested:

TX   ID   DEPT   DEPT_RANK   LOCATION   LOC_RANK   LOAD
1    99    A         1          NY         2        12
2    99    A         1          LA         1        10
3    99    B         2          LA         1        05
4    77    B         1          LA         1        15
5    77    C         2          NY         2        12
6    77    D         3          LA         1        11

Notice in the above that when two rows have the same SUM_LOAD_ value, the displayed rank is correct, but that repeated rank value then causes a rank value to be skipped (compare the DEPT_RANK column value on row 3, the LOC_RANK column value on row 1, and the LOC_RANK column value on row 5).

Now what?  The ROW_NUMBER function could be used to produce sequential rank numbers without gaps, for example:

SELECT
  TX,
  ID,
  DEPT,
  ROW_NUMBER() OVER (PARTITION BY ID ORDER BY SUM_LOAD_ID DESC) DEPT_RANK,
  LOCATION,
  ROW_NUMBER() OVER (PARTITION BY ID ORDER BY SUM_LOAD_LOCATION DESC) LOC_RANK,
  LOAD
FROM
  (SELECT
    TX,
    ID,
    DEPT,
    SUM(LOAD) OVER (PARTITION BY ID,DEPT) SUM_LOAD_ID,
    LOCATION,
    SUM(LOAD) OVER (PARTITION BY ID,LOCATION) SUM_LOAD_LOCATION,
    LOAD
  FROM
    T1)
ORDER BY
  TX;

 TX  ID D  DEPT_RANK LO   LOC_RANK  LOAD
--- --- - ---------- -- ---------- -----
  1  99 A          1 NY          3    12
  2  99 A          2 LA          2    10
  3  99 B          3 LA          1     5
  4  77 B          1 LA          1    15
  5  77 C          2 NY          3    12
  6  77 D          3 LA          2    11 

The above output, as mentioned, does not match the output requested by the OP, since the OP’s requested output specifes that equal values for different rows should show the same rank value.

One more try using the DENSE_RANK analytic function:

SELECT
  TX,
  ID,
  DEPT,
  DENSE_RANK() OVER (PARTITION BY ID ORDER BY SUM_LOAD_ID DESC) DEPT_RANK,
  LOCATION,
  DENSE_RANK() OVER (PARTITION BY ID ORDER BY SUM_LOAD_LOCATION DESC) LOC_RANK,
  LOAD
FROM
  (SELECT
    TX,
    ID,
    DEPT,
    SUM(LOAD) OVER (PARTITION BY ID,DEPT) SUM_LOAD_ID,
    LOCATION,
    SUM(LOAD) OVER (PARTITION BY ID,LOCATION) SUM_LOAD_LOCATION,
    LOAD
  FROM
    T1)
ORDER BY
  TX;

 TX  ID D  DEPT_RANK LO   LOC_RANK  LOAD
--- --- - ---------- -- ---------- -----
  1  99 A          1 NY          2    12
  2  99 A          1 LA          1    10
  3  99 B          2 LA          1     5
  4  77 B          1 LA          1    15
  5  77 C          2 NY          2    12
  6  77 D          3 LA          1    11 

Let’s compare the above output with what the OP requested:

TX   ID   DEPT   DEPT_RANK   LOCATION   LOC_RANK   LOAD
1    99    A         1          NY         2        12
2    99    A         1          LA         1        10
3    99    B         2          LA         1        05
4    77    B         1          LA         1        15
5    77    C         2          NY         2        12
6    77    D         3          LA         1        11

As best as I am able to determine, the above SQL statement will satisfy the OP’s request.

—–

Part 2 of the Challenge

If the OP has the following SQL statement:

SELECT
  LOAD_YEAR,
  ORG_UNIT_CODE,
  RANK () OVER (PARTITION BY LOAD_YEAR, CLASSIF_CODE ORDER BY SUM (FTE_DAYS) DESC) ORG_RANK,
  CLASSIF_CODE,
  RANK () OVER (PARTITION BY LOAD_YEAR, ORG_UNIT_CODE ORDER BY SUM (FTE_DAYS) DESC) CLASSIF_RANK,
  SUM (FTE_DAYS) FTE
FROM
  STAFF_LOAD
GROUP BY
  LOAD_YEAR,
  ORG_UNIT_CODE,
  CLASSIF_CODE;  

And the above SQL statement produces the following output:

YEAR ORG_UNIT_CODE ORG_RANK CLASSIF_CODE CLASSIF_RANK FTE
2010 A46                  1 HEW3                    1  59
2010 A42                  2 HEW3                    1  13
2010 A42                  1 HEW4                    1  13
2010 A46                  2 HEW4                    2  12

And the OP wants the output to look like this:

YEAR ORG_UNIT_CODE ORG_RANK CLASSIF_CODE CLASSIF_RANK FTE
2010 A46                  1 HEW3                    1  59
2010 A42                  2 HEW3                    1  13
2010 A42                  2 HEW4                    2  13
2010 A46                  1 HEW4                    2  12  

Write the DDL and DML statements to create the source table and populate it with the non-aggregated original data, and then produce the output requested by the OP (without looking at the updated usenet thread).

Book Review: Troubleshooting Oracle Performance (Part 2)

November 7, 2011

Most Thorough, Yet Compact Performance Tuning Book 9i-11g
http://www.amazon.com/Troubleshooting-Oracle-Performance-Christian-Antognini/dp/1590599179

(Back to the Previous Post in the Series)

I originally reviewed the “Troubleshooting Oracle Performance” book a bit over three years ago, having pre-ordered the book prior to its publication.  The original review is quite limited in depth compared to some of my more recent Oracle Database book reviews.  I recently decided to purchase the companion PDF file from Apress, as well as to re-read the book so that I could provide a much more detailed book review. 

Since the time when I wrote my original review of this book I have formally reviewed at least three other books that are Oracle Database performance specific, reviewed a handful of other books that contain Oracle Database performance sections, and briefly viewed and wrote comments about a couple of other performance related books.  The “Troubleshooting Oracle Performance” book effectively sets the standard by which all other Oracle Database performance books are measured.  The depth of coverage, accuracy of contents, and signal to noise ratio are unparalleled in the Oracle Database performance book category.

There are several factors that separate this book from the other Oracle Database performance books on the market:

• For every feature that is described to help solve a problem, as many as possible of the benefits are listed, and an equal amount of attention is paid to the potentially wide-ranging problem areas of various solutions.  Very few potential problems were overlooked.  Some of the other books on the market only describe the potential benefits of implementing a feature, without discussing limitations or unintended side-effects.
• For nearly every feature described in the book, the book lists the licensing and version requirements (sometimes to a specific point release such as 9.2.0.4, 9.2.0.5, 10.2.0.3, 10.2.0.4) that are required so that the reader is able to take advantage of the feature – these requirements are often listed early in the description of the feature.  The book commonly describes how to accomplish a task in the current Oracle Database release, as well as older releases, if the approach differs.  Some of the other books on the market inter-mix features and behaviors in various Oracle Database releases, without clearly distinguishing what will and what will not be available in the reader’s environment.
• While many strong statements are made about Oracle Database in the book, there is no “hand waiving”, and there are very few inaccurate statements.  The book uses a “demonstrate and test in your environment” approach from cover to cover.  The downloadable scripts library is extensive, and often contains more performance information than what is presented in the book.  It is thus recommended to view the scripts and experiment with those scripts while the book is read.  The downloadable scripts on the Apress website appear to be corrupt (this corruption appears to affect more than just the scripts for this book).  Updated versions of the scripts are available for download from the author’s website.  In contrast, other books seem to take the approach of “trust me, I have performed this task 1,000 times and never had a problem” rather than the “demonstrate and test in your environment” approach as was used in this book.
• Information in this book is densely packaged, without unnecessarily repeating information, and without giving the impression that sections of the book are a paraphrase of some other set of articles.  Additionally, the information is well organized into a logical progression of topics, rather than each section of the book appearing as an island of unrelated information.
• The well-placed graphics throughout the book support the contents of the book, rather than distract from the information that is described.
• In the acknowledgments section at the beginning of the book the author mentioned that his English writing ability is poor and that “I should really try to improve my English skills someday.”  In the book the only hint that English is not the author’s primary language is the repeated use of the phrase “up to” when describing features that exist in one Oracle Database release version or another.  The author’s use of “up to” should be interpreted as “through” (including the specified end-point) rather than as “prior to” (before the specified end-point).  It appears that the author exercised great care when presenting his information on each page.  In contrast, some of the other Oracle Database book authors seem to be more concerned with slamming something onto the page so that something else that is more interesting could be introduced, in the process introducing sentences that can best be described as non-sense.

The “Troubleshooting Oracle Performance” book covers Oracle releases through 9.2.0.8, 10.1.0.5, 10.2.0.4, and 11.1.0.6.  For the most part the information provided in the book applies to Oracle Database 11.1 and above, with exceptions noted for earlier release versions.  The author’s website effectively extends the book’s contents to cover Oracle Database 10.2.0.5, 11.1.0.7, and 11.2.0.x.  It is recommended that the Oracle sample schemas are installed in a test database so that the reader is able to experiment with all of the sample scripts provided with the book.  The book appears to be mostly directed at DBAs, however sections of the book are appropriate for developers.

This review is a bit long (roughly 18 typewritten pages), and might not completely appear on Amazon (see my Oracle blog if the review does not appear in full).  As such I will begin the detail portion of the review with the problems/corrections to the book that I have identified (see the author’s website for the complete list of errata), describe some of the foundation knowledge/tips found in the book, and then list various data dictionary views/tables, Oracle Database parameters, SQL hints, built-in functions, execution plan elements, and Oracle error messages that are described in the book (many of these items cannot be located in the index at the back of the book, so a page number reference is provided).

—

Comments, Corrections, and Problems:

• The descriptions of both the IS_BIND_AWARE and IS_SHAREABLE columns of V$SQL include the phrase “If set to N, the cursor is obsolete, and it will no longer be used.”  It appears that this phrase was accidentally added to the description of the IS_BIND_AWARE column. (pages 27-28)
• The book states, “Remember, execution plans are stored only when the cursors are closed, and the wait events are stored only if they occurred while the tracing of wait events was enabled.”  Technically, this behavior changed with the release of Oracle Database 11.1.  The PLAN_STAT parameter of the DBMS_SESSION.SESSION_TRACE_ENABLE function, and the PLAN_STAT parameter of the various DBMS_MONITOR functions default to a value of FIRST_EXECUTION.  The default behavior in 11.1 and later is to write out the execution plans to the trace file after the first execution (before the cursor is closed), however that parameter may be changed to ALL_EXECUTIONS (plan is written to the trace file after each execution) or NEVER (do not output the execution plan). (page 82)
• The book states, “Notice how the number of waits, 941, exactly matches the number of physical writes of the operation HASH GROUP BY provided earlier in the row source operations.”  The statement in the book is correct, but as written it might be slightly confusing.  This statement probably could have been clarified slightly, repeating what was stated earlier about the cumulative nature of the statistics for the parent and child operations.  The reader would then more easily understand that the pw=1649 value associated with the “TABLE ACCESS FULL SALES” operation must be subtracted from the pw=2590 value found on the “HASH GROUP BY” operation to arrive at the number 941 mentioned in the book. (page 86)
• The book states, “As of Oracle Database 10g, the rule-based optimizer is no longer supported and, therefore, will not be covered here.”  This sentence, as written, could be misinterpreted.  The rule based optimizer still exists in the latest release of Oracle Database, but its use is deprecated, and therefore the use of the rule based optimizer is no longer supported by Oracle Corp., even though it still exists for backward compatibility purposes.  Page 174 of the book also states that the rule based optimizer has been desupported. (page 108)
• The script that compares the time required to read from table BIG_TABLE using different values for the DB_FILE_MULTIBLOCK_READ_COUNT parameter is subject to at least three potential problems that could throw off the accuracy of the results: 1. Portions of the table may remain cached in the buffer cache between each execution (a warning about this potential issue is provided in the book immediately after the script). 2. The query optimizer may decide to use serial direct path reads (“direct path read” wait event), rather than the commonly expected “db file scattered read” type accesses for the full table scan.  Testing seems to indicate that the number of blocks read in a single serial direct path read is related to the DB_FILE_MULTIBLOCK_READ_COUNT value – the maximum number of blocks seems to be the largest power of two that is less than or equal to the DB_FILE_MULTIBLOCK_READ_COUNT value (this might explain the stair-stepped diagram that is included in the book).  Serial direct path reads where introduced in Oracle Database 11.1 as a potential replacement for Oracle buffer cache buffered reads when parallel query was not implemented; that change in behavior was apparently not documented prior to the publication of this book. 3. What unrelated blocks are in the buffer cache at the time that the test started might be important. (page 178)
• That book states, “Unfortunately, no feature is provided by the package dbms_xplan to query it [the stats$sql_plan repository table].”  There is a way to use DBMS_XPLAN.DISPLAY to display an execution plan that was captured in the PERFSTAT.STATS$SQL_PLAN table, but the syntax is a little awkward. (page 204)
• The book demonstrates that it is possible to add comment text to a hint block without affecting the hint in that hint block.  Ideally, the book would have mentioned that there are risks that hints will be ignored by the optimizer when adding regular comments to hint blocks, especially when the comments are added in front of the hints. (page 254)
• “8;” is missing from the first EXPLAIN PLAN FOR SELECT statement. (page 260)
• A test case (access_structures_1000.sql) is provided that uses partitioning without first mentioning that the partitioning option may only be purchased for the Enterprise Edition.  This is one of the very few instances where the licensing requirements for a feature are not mentioned in the book when the feature is introduced.  The licensing requirements are described two pages later. (page 348)
• Considering the depth of explanation found in the rest of the book, the book should have mentioned that “ALTER TABLE t SHINK SPACE” is only valid if table t is in an ASSM tablespace. (page 351)
• The book references the clustering_factor.sql script, but that script does not exist in the chapter 9 script library.  A clustering_factor.sql script does exist in the chapter 4 script library, but the table definition differs from what is shown in chapter 9.  This does not appear to be a significant problem because the essential portions of the script appear in the book. (page 375)
• The book states, “Note: Full table scans, full partition scans, and fast full index scans executed in parallel use direct reads and, therefore, bypass the buffer cache.”  This was a correct statement at the time the book was printed.  However, Oracle Database 11.2 introduced in-memory parallel execution.  The book author briefly mentions this feature in one of his blog articles. (page 500)
• The book states that direct path insert does not work with INSERT statements containing a VALUES clause.  Since the time when the book was published, the author created a blog article that demonstrates how to use direct path insert for SQL statements that have a VALUES clause.  According to the blog article, the behavior changed in Oracle Database 11.1 and again in 11.2. (page 513)

The actual errors found in the book are minor in comparison to the amount of information covered by the book.

—

Foundation Knowledge, and Miscellaneous Tips:

• A quote from the book, one of the reasons why application performance is important: “The studies showed a one-to-one decrease in user think time and error rates as system transaction rates increased. This was attributed to a user’s loss of attention because of longer wait times.” (page 3)
• The basic equation that determines the time required for a response from the database server: “response time = service time + wait time” (page 4)
• Description of service level agreements, and a description of a method to design and test code to meet service level agreements. (pages 5-8)
• A quote that sets the tone for the rest of the book: “So if you do not want to troubleshoot nonexistent or irrelevant problems (compulsive tuning disorder), it is essential to understand what the problems are from a business perspective—even if more subtle work is required.” (page 11)
• Describing the selectivity and cardinality statistics: “selectivity is a value between 0 and 1 representing the fraction of rows filtered by an operation.” “cardinality = selectivity * num_rows”. (page 13)
• The description of each step in the cursor life cycle is explained: open, parse, define output variables, bind input variables, execute, fetch, close. (page 15)
• Description of each step in parsing: include VPD predicates; check syntax, semantics, access rights; store parent in library cache; logical optimization; physical optimization; store child in the library cache. (page 18)
• Advantages and disadvantages of bind variables (pages 22-23)
• Causes of new child cursors when using bind variables: maximum size of the bind variable increases, execution environment changes, adaptive (extended) cursor sharing. (pages 23, 27)
• Bind variable peeking was introduced in Oracle 9i. (page 25)
• A caution about using bind variables when histograms are present: “On the other hand, bind variables should be avoided at all costs whenever histograms provide important information to the query optimizer.” (page 30)
• Various profiling applications mentioned: PerformaSure, JProbe; load-testing framework: the Grinder.
• Sample TKPROF output, along with the original 10046 extended SQL trace file.
• Debugging event numbers are listed in the file $ORACLE_HOME/rdbms/mesg/oraus.msg – but that file is not distributed on all platforms. (page 63)
• Structure of 10046 trace files (pages 73-76)
• Using TRCSESS to process 10046 trace files. (page 76-77)
• Using TKPROF to process 10046 trace files. (page 78-90)
• Unlike other books, this book provides a warning about using the EXPLAIN parameter of TKPROF, “In any case, even if all the previous conditions are met, as the execution plans generated by EXPLAIN PLAN do not necessarily match the real ones (the reasons will be explained in Chapter 6), it is not advisable to specify the argument explain.” (page 79)
• Interesting comment about the SORT parameter for TKPROF – it does not perform a multi-level sort, “When you specify a comma-separated list of values [for the SORT parameter], TKPROF sums the value of the options passed as arguments. This occurs even if they are incompatible with each other.” (page 81)
• Using TVD$XTAT – a trace file parser developed by the book author. (pages 90-100)
• Discussion of the I/O cost model, CPU cost model, noworkload statistics, and workload statistics. (page 111)
• A possible explanation why some developers want table columns to appear in a specified order in a table’s definition: “The estimated CPU cost to access a column depends on its position in the table.” “cpu_cost = column_position*20” (page 117)
• Calculated Cost of an operation is approximately IO_COST + CPU_COST  / (CPUSPEED * SREADTIME * 1000). (page 118)
• When using noworkload statistics, SREADTIME is calculated as IOSEEKTIM + (DB_BLOCK_SIZE / IOTFRSPEED). (page 119)
• When SREADTIM, MREADTIM, or MBRC are not available in SYS.AUX_STATS$, the optimizer falls back to noworkload statistics. (page 119)
• An ASSOCIATE STATISTICS SQL statement may be used to associate statistics with columns, functions, packages, types, domain indexes, and index types. (page 120)
• The ENDPOINT_VALUE of the USER_TAB_HISTOGRAMS view only includes the first 6 bytes of character columns – the book did not mention the ENDPOINT_ACTUAL_VALUE column. (pages 126, 129)
• Extended statistics for column groups work only with equality predicates because of the hashing function that is applied to the column group values. (page 132)
• An index is always balanced because the same number of branch blocks are present between the root block and all of the leaf blocks. (page 133)
• As of Oracle Database 10.1 the default value of DBMS_STATS’ CASCADE parameter is DBMS_STATS.AUTO_CASCADE, which allows the database engine to decide when to collect index statistics when the table’s statistics are collected. (page 140)
• Very through description of the DBMS_STATS package.
• Regarding when Oracle determines to automatically collect object statistics, the book states: “By default, a table is considered stale when more than 10 percent of the rows change. This is a good default value. As of Oracle Database 11g, this can be changed if necessary.” (page 163)
• When describing historical object statistics, the book states: “As of Oracle Database 10g, whenever system statistics or object statistics are gathered through the package dbms_stats, instead of simply overwriting current statistics with the new statistics, the current statistics are saved in other data dictionary tables that keep a history of all changes occurring within a retention period.” (page 164)
• Interesting flowchart diagram that describes a process of setting initialization parameters and statistics. (page 171)
• Factors that might cause fewer blocks to be read than is specified by the DB_FILE_MULTIBLOCK_READ_COUNT parameter: “Segment headers are read with single-block reads. Physical reads never span several extents. Blocks already in the buffer cache, except for direct reads, are not reread from the I/O subsystem.” (page 175)
• Regarding behavior changes related to specific initialization parameters from one Oracle Database release to another, the book states: “When workload system statistics are available, the I/O cost is no longer dependent on the value of the initialization parameter db_file_multiblock_read_count.” (page 177)
• A close approximation for the calculation of the auto-tuned DB_FILE_MULTIBLOCK_READ_COUNT parameter is provided in the book, rather than simply stating that the auto-tuned parameter could be affected “if the number of sessions is extremely large” as is stated in the Oracle Database documentation. (page 178)
• Nice test case that demonstrates dynamic sampling. (pages 180-183)
• For an index access, the Io_cost ≈ blevel + (leaf_blocks +clustering_factor) * selectivity * (optimizer_index_cost_adj/100). (page 184)
• A danger in modifying the OPTIMIZER_INDEX_COST_ADJ parameter to too low of a value is that the optimizer might calculate the same cost for two different indexes due to rounding in the cost estimates – the optimizer will then select the index that sorts first alphabetically.  The book provides a partial test case that demonstrates the problem. (page 185-186)
• The purpose of the OPTIMIZER_INDEX_CACHING  parameter is often misstated in various books and websites.  This book correctly states: The OPTIMIZER_INDEX_CACHING parameter “does not specify how much of each of the indexes is actually cached by the database engine…  Values greater than 0 decrease the cost of index scans performed for in-list iterators and in the inner loop of nested loop joins. Because of this, it is used to push the utilization of these operations.” (page 186)
• Formula showing how the OPTIMIZER_INDEX_CACHING parameter is applied to costing calculations (page 186).
• Defining a non-mergeable view: “When the view contains grouping functions in the SELECT clause, set operators, or a hierarchical query, the query optimizer is not able to use view merging. Such a view is called a nonmergeable view.” (page 188)
• Test case showing why the OPTIMIZER_SECURE_VIEW_MERGING parameter defaults to TRUE, when it is sensible to set that parameter to FALSE for performance reasons, and the privileges that may be assigned to a user so that the user is not subject to the negative performance effects caused by having a value of TRUE set for this parameter. (pages 187-189)
• In Oracle Database 9.2, automatic PGA management did not work with a shared server configuration, but it does work with a shared server configuration starting in Oracle Database 10.1. (page 190)
• Oracle Database 10.1 and lower artificially limit the amount of memory that a session can allocate when automatic PGA management is enabled (for example 100MB for serial operations), and overcoming that limit requires the modification of hidden initialization parameters.  Oracle Database 10.2 and higher remove this artificial limit, allowing PGA allocation to increase as the amount of memory increases.  While not stated in the book, setting the OPTIMIZER_FEATURES_ENABLE parameter value to that of an earlier release, 10.1.0.4 for example, causes the PGA allocation to be limited, just as was the case prior to Oracle Database 10.2. (page 190)
• As of Oracle Database 10.1, the default value for the PGA_AGGREGATE_TARGET is 20% of the SGA size. (page 190)
• The PLAN_TABLE exists by default starting in Oracle Database 10.1 as a global temporary table.  The utlxplan.sql script only needs to be run in Oracle Database versions prior to 10.1 – this fact was missed by a couple of recently released books that still indicate that the utlxplan.sql script must be executed to create the PLAN_TABLE. (page 197)
• Detailed explanation why EXPLAIN PLAN FOR with a SQL statement using bind variables might generate an incorrect execution plan: no bind peeking, all bind variables are handled as VARCHAR2. (pages 198-199)
• Demonstration of a method to simulate DBMS_XPLAN.DISPLAY_CURSOR on Oracle Database 9.2. (page 202)
• The book states that understanding 10053 trace files is not as easy task, that the trace file is only generated when there is a hard parse.  The book provides three references to other sources to help the reader understand 10053 trace file contents. (page 205)
• The book describes setting event 10132 to cause Oracle Database to write out the SQL statement, execution plan, and initialization parameters that affects the optimizer on each hard parse. (page 206)
• In an execution plan, the Used-Tmp  and Max-Tmp columns are indicated in KB, so the column values must be multiplied by 1024 so that the unit of measure is consistent with the other memory related columns. (page 210)
• Detailed walk through of the order of operations in execution plans (pages 224, 227, 229, 231, 232, 235, 237-239, 240, 241, 363, 401, 421, 437, 453-454, 501)
• The book describes when a feature requires an Enterprise Edition license (such as SQL plan baselines), Enterprise Edition with the Tuning Pack (such as SQL profiles), and the Oracle Database release version that first supported the feature.
• The book correctly states about SQL hints: “Simply put, hints are directives added to SQL statements to influence the query optimizer’s decisions. In other words, it is something that impels toward an action, not merely suggesting one.” (page 252)
• Default prefixes for query block names: CRI$ – CREATE INDEX statements; DEL$ – DELETE statements; INS$ – INSERT statements; MISC$ – Miscellaneous SQL statements like LOCK TABLE; MRG$ – MERGE statements; SEL$ – SELECT statements; SET$ – Set operators like UNION and MINUS; UPD$ – UPDATE statements (page 258)
• Table 7-3 contains a cross-reference between certain initialization parameters and equivalent hints that affect a single SQL statement. (pages 262-263)
• The text of SQL statements is normalized so that it is case-insensitive and white-space insensitive when SQL profiles are created.  This normalization allows the SQL profile to work even if changes in capitalization and white-space result in a different SQL_ID for the SQL statement (and can be set to normalize changes in constants on Oracle Database 11g). (page 271)
• The book states about SQL Profiles: “Simply put, a SQL profile stores a set of hints representing the adjustments to be performed by the query optimizer.” (page 275)
• When both a stored outline and SQL profile exist for the same SQL statement, the stored outline will be used rather than the SQL profile. (page 279)
• Procedure for editing a stored outline. (pages 286-288)
• Interesting three part test case that demonstrates the execution time difference for a parse intensive SQL statement that is repeatedly parsed with different literal values, with bind variables when cursors are closed, and with bind variables when cursors are held open. (pages 317-324)
• While other books advocate the use of non-default values for the CURSOR_SHARING parameter, this book provides the following warning, “Cursor sharing has a reputation for not being very stable. This is because, over the years, plenty of bugs related to it have been found and fixed. Therefore, my advice is to carefully test applications when cursor sharing is enabled.”  The book does not mention that the CURSOR_SHARING value of SIMILAR is deprecated as of Oracle Database 11.1 (see Metalink (MOS) Doc ID: 1169017.1), however, this book was likely written long before that parameter value was officially deprecated. (page 325)
• The book correctly states the default value for the SESSION_CACHED_CURSORS parameter is dependent on the Oracle Database release version, while the Oracle Database documentation for 10.2 incorrectly states the default value, and other books simply pick one of the previously used default values (0, 20, or 50) when discussing the parameter. (page 327)
• The book correctly states that there is a change introduced in Oracle Database 9.2.0.5 that changed the default number of cached cursors from being dependent on the OPEN_CURSORS parameter to being dependent on the SESSION_CACHED_CURSORS parameter.  The book probably should have stressed that this change in behavior is only related to PL/SQL cursors. (page 331)
• Code demonstrations are provided in standard SQL, PL/SQL, C, and Java.
• Non-aggregated access paths that use 5 or fewer logical I/Os per row returned are reasonable; non-aggregated access paths that use between 5 and 15 logical I/Os per row returned are probably reasonable; non-aggregated access paths that use more than 15 to 20 logical I/Os per row returned are probably inefficient.  Read consistency and row prefetching (array fetch size, the ARRAYSIZE system variable in SQL*Plus) can distort these suggested targets. (pages 341, 343, 376-378)
• For a simple execution plan with a single full table scan, if the array fetch size is set to 1, reading each row will increment the number of consistent gets for the session by 1.  If the array fetch size is set to a value larger than the maximum number of rows in the table’s blocks, the number of consistent gets will be approximately the same as the number of blocks in the table. (page 343)
• In Oracle Database 11.1 and above, it is possible to use a virtual column as a partition key. (page 358)
• Characteristics of bitmap indexes: cannot be scanned in descending order (SORT ORDER BY operation is required), bitmap indexes always store NULL values (b*tree indexes do not store NULL values when all column values are NULL). (page 371)  The clustering factor of bitmap indexes is always set to the number of keys in the index. (page 375)  Cannot be used to enforce primary/unique key constraints, do not support row-level locking, space management problems due to updates in releases prior to 10.1, supports efficient index combine operations, supports star transformation. (page 378)  A single bitmap index entry might reference thousands of rows – modifying one of those rows may cause concurrency problems for other sessions that need to modify one of the other rows referenced by the same bitmap index entry; bitmap indexes generally work better than b*tree indexes with low cardinality data, but that does not mean that they work efficiently for extremely low cardinality data (unless bitmap combine operations significantly reduce the number of rowids that are used to fetch table rows). (page 379)
• As of Oracle Database 10.1 it is possible to specify table columns in index hints, which effectively forces the optimizer to select an index that references the specified column, ex: /*+ index_asc(t (t.id)) */.  (page 385)
• The book states about index-organized tables: “A row in an index-organized table is not referenced by a physical rowid. Instead, it is referenced by a logical rowid. This kind of rowid is composed of two parts: first, a guess referencing the block that contains the row (key) at the time it is inserted, and second, the value of the primary key.” Use ALTER INDEX i UPDATE BLOCK REFERENCES;  to update the guess references. (page 404)
• Changing an index definition from unique to non-unique could result in the structure of an execution plan changing, even though the index is still used, apparently to help take advantage of block prefetching. (page 423)
• Join elimination is introduced in Oracle Database 10.2, which allows the query optimizer to eliminate a join to a table, which then removes the linked table from the execution plan.  This is possible if no columns are selected from the linked table, and a validated foreign key links to the table from the other tables specified in the SQL statement. (page 448)
• The star transformation is a cost-based transformation, even when a STAR_TRANSFORMATION hint is specified in the SQL statement. (page 456)
• When materialized views are created, the ORDER BY clause is only respected during the initial materialized view creation because the ORDER BY clause is not included in the definition that is stored in the data dictionary. (page 461)
• Automatic query rewrite that allows the optimizer to take advantage of materialized views is a cost based decision that can be controlled by the REWRITE and NOREWRITE hints.  The materialized view must be altered to ENABLE QUERY REWRITE, and the QUERY_REWRITE_ENABLED parameter must be set to TRUE. (pages 462-463)
• The book states: “Full-text-match and partial-text-match query rewrites can be applied very quickly… In contrast, general query rewrite is much more powerful. The downside is that the overhead of applying it is much higher.” (page 466)
• Extensive coverage of materialized views, and improving the performance of those materialized views.  The book contains a warning (page 481) not to use ANSI join syntax with materialized views. (pages 459-481)
• The various results caches are described. (pages 481-489)
• Result cache limitations: “Queries that reference nondeterministic functions, sequences, and temporary tables are not cached.  Queries that violate read consistency are not cached. For example, the result set created by a session with outstanding transactions on the referenced tables cannot be cached.  Queries that reference data dictionary views are not cached.” (page 485)
• Various details of parallel processing (query, DML, and DDL) are described. (pages 489-513)
• “Parallel DML statements are disabled by default (be careful, this is the opposite of parallel queries).”  Use a command such as “ALTER SESSION ENABLE PARALLEL DML” or “ALTER SESSION FORCE PARALLEL DML PARALLEL 4” to specify the default degree of DML parallelism. (page 503)
• The book states: “Parallel DDL statements are enabled by default.” (page 505)
• Disabling parallel execution at the session level does not disable parallel execution for recursive SQL statements. (page 509)
• Parallel execution should not be enabled unless there is sufficient CPU/memory/IO bandwidth available and the query requires at least a minute to execute.  “It is important to stress that if these two conditions are not met, the performance could decrease instead of increase.” (page 509)
• Regarding the various parallel related hints, the book states: “It is very important to understand that the hints parallel and parallel_index do not force the query optimizer to use parallel processing. Instead, they override the degree of parallelism defined at the table or index level.” (page 509)
• The book describes direct path insert. (pages 513-517)
• Regarding the reduction of redo generation, the book states: “Even if minimal logging is not used, a database running in noarchivelog mode doesn’t generate redo for direct-path inserts.” (page 516)
• Enabling array fetching (row prefetching) in PL/SQL (pages 518-519), OCI (pages 519-520), JDBC (pages 520-521), ODP.NET (page 521), SQL*Plus (page 521)
• Enabling array INSERT in PL/SQL (pages 523-524), OCI (page 524), JDBC (page 524), ODP.NET (page 524-525)
• The book provides a test case that demonstrates performance differences caused by the relative position of columns in a table’s definition.  The book states: “So whenever a row has more than a few columns, a column near the beginning of the row might be located much faster than a column near the end of the row.” (page 528)
• Selecting the appropriate datatype for columns. (pages 529-535)
• Row chaining and row migration: row migration happens when there is not enough free space in a block for a row, so the entire row (leaving behind forwarding information) is moved to another block; row chaining happens when a row contains more than 255 columns, or where a row exceeds the full storage capacity of a block. (pages 535-538)
• Block contention (pages 539-546)
• Data compression (pages 546-548)

—

Data Dictionary Views/Tables:

• V$SQLAREA (page 18)
• V$SQL (pages 18, 27)
• V$SQL_SHARED_CURSOR (page 21)
• V$SQL_BIND_METADATA (page 23)
• V$SQL_CS_STATISTICS, V$SQL_CS_SELECTIVITY, V$SQL_CS_HISTOGRAM (page 29)
• V$SYS_TIME_MODEL, V$SESS_TIME_MODEL (page 39)
• V$SESSION (pages 45, 501, 503)
• DBA_ENABLED_TRACES (page 67)
• V$PARAMETER (page 70)
• V$BGPROCESS, V$DIAG_INFO, V$DISPATCHER, V$SHARED_SERVER, V$PX_PROCESS (page 71)
• V$PROCESS (page 73)
• SYS.AUX_STATS$ (page 112)
• V$FILESTAT (page 114)
• USER_JOBS (pages 115, 474)
• USER_SCHEDULER_JOBS (page 115)
• USER_TAB_STATISTICS, USER_TABLES, USER_TAB_STATISTICS, USER_TAB_PARTITIONS, USER_TAB_STATISTICS, USER_TAB_SUBPARTITIONS, USER_TAB_COL_STATISTICS, USER_TAB_HISTOGRAMS, USER_PART_COL_STATISTICS, USER_PART_HISTOGRAMS, USER_SUBPART_COL_STATISTICS, USER_SUBPART_HISTOGRAMS, USER_IND_STATISTICS, USER_INDEXES, USER_IND_STATISTICS, USER_IND_PARTITIONS, USER_IND_STATISTICS, USER_IND_SUBPARTITIONS (page 120)
• USER_STAT_EXTENSIONS, USER_TAB_COLS (page 132)
• SYS.USER$, SYS.REGISTRY$, SYS.REGISTRY$SCHEMAS (page 138)
• ALL_TAB_MODIFICATIONS, DBA_TAB_MODIFICATIONS, USER_TAB_MODIFICATIONS (page 140)
• SYS.COL_USAGE$ (page 145)
• SYS.COL$, SYS.OBJ$, SYS.USER$ (page 146)
• SYS.OPTSTAT_HIST_CONTROL$ (page 147)
• DBA_TAB_STAT_PREFS (page 150)
• USER_TAB_PENDING_STATS, USER_IND_PENDING_STATS (page 151)
• USER_COL_PENDING_STATS, USER_TAB_HISTGRM_PENDING_STATS, DBA_SCHEDULER_JOBS, DBA_SCHEDULER_PROGRAMS, DBA_SCHEDULER_WINGROUP_MEMBERS, DBA_SCHEDULER_WINDOWS (page 152)
• DBA_AUTOTASK_TASK, DBA_AUTOTASK_WINDOW_CLIENTS (page 154)
• DBA_TAB_STATS_HISTORY, USER_TAB_STATS_HISTORY (page 165)
• DBA_OPTSTAT_OPERATIONS  (page 167)
• V$PARAMETER_VALID_VALUES (page 174)
• V$PGASTAT (page 191)
• PLAN_TABLE (page 197, 532)
• V$SQL_PLAN, V$SQL_PLAN_STATISTICS (page 199)
• V$SQL_WORKAREA, V$SQL_PLAN_STATISTICS_ALL (page 200)
• DBA_HIST_SQL_PLAN (page 203)
• STATS$SQL_PLAN (page 204)
• DBA_HIST_SQLTEXT, V$DATABASE (page 219)
• V$SQL_HINT (page 256)
• V$SYS_OPTIMIZER_ENV, V$SES_OPTIMIZER_ENV (page 263)
• V$SQL_OPTIMIZER_ENV (page 264)
• DBA_SQL_PROFILES (page 271)
• SYS.SQLPROF$ATTR (page 275)
• SYS.SQLPROF$ (page 276)
• USER_OUTLINES, USER_OUTLINE_HINTS (page 282)
• OUTLN.OL$,OUTLN.OL$HINTS,OUTLN.OL$NODES (page 284)
• DBA_SQL_PLAN_BASELINES (page 296)
• SYS.SQLOBJ$DATA, SYS.SQLOBJ$ (page 298)
• DBA_SQL_MANAGEMENT_CONFIG (page 303)
• V$SESSTAT, V$STATNAME (page 320)
• USER_TAB_PARTITIONS (pages 353, 355, 366)
• USER_TAB_SUBPARTITIONS (page 366)
• USER_TAB_COLUMNS (page 396)
• INDEX_STATS (page 399)
• V$SQL_WORKAREA_ACTIVE (page 438)
• USER_MVIEWS (page 471)
• ALL_SUMDELTA, USER_MVIEW_LOGS (page 475)
• MV_CAPABILITIES_TABLE (pages 476, 480)
• V$RESULT_CACHE_OBJECTS, V$SGASTAT, V$RESULT_CACHE_DEPENDENCY, V$RESULT_CACHE_MEMORY, V$RESULT_CACHE_STATISTICS (page 484)
• V$PX_PROCESS_SYSSTAT (page 495)
• V$SYSSTAT (page 500)
• V$PQ_TQSTAT (page 511)
• DBA_TABLES (page 538)
• V$WAITSTAT (page 539)
• DBA_EXTENTS (page 542)

—

Initialization Parameters:

• OPTIMIZER_MODE (pages 20, 173)
• SQL_TRACE (page 63)
• TIMED_STATISTICS, STATISTICS_LEVEL, MAX_DUMP_FILE_SIZE, USER_DUMP_DEST, BACKGROUND_DUMP_DEST (page 70)
• DIAGNOSTIC_DEST (page 71)
• TRACEFILE_IDENTIFIER, _TRACE_FILES_PUBLIC (page 73)
• _OPTIMIZER_COST_MODEL (page 111)
• OPTIMIZER_USE_PENDING_STATISTICS (page 151)
• OPTIMIZER_FEATURES_ENABLE (pages 174, 179)
• DB_FILE_MULTIBLOCK_READ_COUNT, DB_BLOCK_SIZE (page 175)
• OPTIMIZER_DYNAMIC_SAMPLING (page 179)
• OPTIMIZER_INDEX_COST_ADJ (pages 111, 183)
• OPTIMIZER_INDEX_CACHING (pages 111, 186)
• OPTIMIZER_SECURE_VIEW_MERGING (page 187)
• WORKAREA_SIZE_POLICY (page 189)
• PGA_AGGREGATE_TARGET, MEMORY_TARGET, MEMORY_MAX_TARGET (page 190)
• SORT_AREA_SIZE (page 191)
• SORT_AREA_RETAINED_SIZE, HASH_AREA_SIZE (page 192)
• BITMAP_MERGE_AREA_SIZE (page 193)
• SQLTUNE_CATEGORY (page 273)
• CREATE_STORED_OUTLINES (281)
• USE_STORED_OUTLINES (page 284)
• OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES (page 292)
• OPTIMIZER_USE_SQL_PLAN_BASELINES (page 301)
• OPEN_CURSORS (page 322)
• CURSOR_SHARING (page 325)
• SESSION_CACHED_CURSORS (page 327)
• QUERY_REWRITE_ENABLED (pages 391, 463)
• QUERY_REWRITE_INTEGRITY (pages 391, 469)
• NLS_COMP, NLS_SORT (page 392)
• _B_TREE_BITMAP_PLANS (page 402)
• STAR_TRANSFORMATION_ENABLED (page 452)
• RESULT_CACHE_MAX_SIZE, RESULT_CACHE_MODE (pages 484, 485)
• RESULT_CACHE_MAX_RESULT, RESULT_CACHE_REMOTE_EXPIRATION (page 485)
• CLIENT_RESULT_CACHE_SIZE, CLIENT_RESULT_CACHE_LAG (PAGE 487)
• OCI_RESULT_CACHE_MAX_SIZE, OCI_RESULT_CACHE_MAX_RSET_SIZE, OCI_RESULT_CACHE_MAX_RSET_ROWS (in the client’s sqlnet.ora file, page 488)
• PARALLEL_MIN_SERVERS (page 494)
• PARALLEL_MAX_SERVERS, CPU_COUNT, PARALLEL_AUTOMATIC_TUNING, PARALLEL_EXECUTION_MESSAGE_SIZE (page 495)
• PARALLEL_THREADS_PER_CPU, PARALLEL_MIN_PERCENT (page 497)
• PARALLEL_ADAPTIVE_MULTI_USER (page 498)
• PLSQL_OPTIMIZE_LEVEL (page 518)
• NLS_NUMERIC_CHARACTERS (page 530)

—

SQL Hints:

• NO_CPU_COSTING (page 111)
• OPTIMIZER_FEATURES_ENABLE (page 175)
• DYNAMIC_SAMPLING (page 179)
• GATHER_PLAN_STATISTICS (page 199)
• FULL (page 254)
• QB_NAME (page 257)
• CURSOR_SHARING, OPT_PARAM (page 262)
• ALL_ROWS, FIRST_ROWS(n), RULE (page 263)
• RESULT_CACHE (pages 263, 482)
• NO_RESULT_CACHE (pages 263, 484)
• IGNORE_OPTIM_EMBEDDED_HINTS (page 276)
• OPT_ESTIMATE (page 277)
• TABLE_STATS, COLUMN_STATS, INDEX_STATS (page 278)
• CURSOR_SHARING_EXACT (page 325)
• INDEX (pages 369, 419)
• INDEX_FFS, INDEX_ASC, INDEX_DESC (page 370)
• INDEX_SS (page 397)
• INDEX_SS_ASC, INDEX_SS_DESC (page 398)
• INDEX_COMBINE (page 400, 402)
• LEADING, USE_NL, FULL, ORDERED (page 419)
• NLJ_BATCHING, NO_NLJ_BATCHING (page 423)
• USE_MERGE (page 425)
• USE_HASH (page 435)
• INDEX_JOIN (page 439)
• SWAP_JOIN_INPUTS (page 440)
• PQ_DISTRIBUTE (page 445)
• NO_OUTER_JOIN_TO_INNER (page 449)
• NO_UNNEST (page 450)
• STAR_TRANSFORMATION, NO_STAR_TRANSFORMATION (page 456)
• REWRITE, NO_REWRITE (page 462)
• PARALLEL, NO_PARALLEL, NOPARALLEL, PARALLEL_INDEX, NOPARALLEL_INDEX (page 497)
• APPEND (page 513)

—

Built-In Functions/Procedures:

• USERENV (page 45)
• SYS_CONTEXT (pages 46, 268)
• DBMS_SESSION.SET_SQL_TRACE, DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (page 63)
• DBMS_SYSTEM.SET_EV (note: DBMS_SYSTEM package function by default can only be executed by the SYS user, page 64)
• DBMS_SUPPORT.START_TRACE_IN_SESSION (page 65)
• DBMS_MONITOR.SESSION_TRACE_ENABLE, DBMS_MONITOR.SESSION_TRACE_DISABLE (page 66)
• DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE, DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE (page 67)
• DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE, DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE (page 68)
• DBMS_MONITOR.DATABASE_TRACE_ENABLE, DBMS_MONITOR.DATABASE_TRACE_DISABLE (page 69)
• DBMS_STATS.DELETE_SYSTEM_STATS, DBMS_STATS.SET_SYSTEM_STATS (page 117)
• UTL_RAW.CAST_TO_NUMBER, UTL_RAW.CAST_TO_BINARY_DOUBLE, UTL_RAW.CAST_TO_BINARY_FLOAT, UTL_RAW.CAST_TO_BINARY_INTEGER, UTL_RAW.CAST_TO_NVARCHAR2, UTL_RAW.CAST_TO_RAW, UTL_RAW.CAST_TO_VARCHAR2, DBMS_STATS.CONVERT_RAW_VALUE, DBMS_STATS.CONVERT_RAW_VALUE_NVARCHAR, DBMS_STATS.CONVERT_RAW_VALUE_ROWID (page 123)
• NTILE (page 127)
• DBMS_STATS.CREATE_EXTENDED_STATS (page 131)
• SYS_OP_COMBINED_HASH (page 132)
• DBMS_ROWID.ROWID_BLOCK_NUMBER, DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO (page 135)
• DBMS_STATS.SET_PARAM (page 147)
• DBMS_STATS.SET_GLOBAL_PREFS, DBMS_STATS.SET_DATABASE_PREFS, DBMS_STATS.SET_SCHEMA_PREFS, DBMS_STATS.SET_TABLE_PREFS (page 148)
• DBMS_STATS.GET_PREFS (page 149)
• DBMS_SCHEDULER.ENABLE, DBMS_SCHEDULER.DISABLE (page 153)
• DBMS_AUTO_TASK_ADMIN.ENABLE, DBMS_AUTO_TASK_ADMIN.DISABLE, DBMS_STATS.LOCK_SCHEMA_STATS (page 155)
• DBMS_STATS.UNLOCK_SCHEMA_STATS (page 156)
• DBMS_STATS.DIFF_TABLE_STATS_IN_STATTAB, DBMS_STATS.DIFF_TABLE_STATS_IN_HISTORY, DBMS_STATS.DIFF_TABLE_STATS_IN_PENDING (page 160)
• DBMS_STATS.GET_STATS_HISTORY_RETENTION, DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (page 164)
• DBMS_STATS.PURGE_STATS (page 165)
• DBMS_STATS.RESTORE_SCHEMA_STATS, DBMS_STATS.CREATE_STAT_TABLE, DBMS_STATS.DROP_STAT_TABLE (page 166)
• DBMS_UTILITY.GET_TIME (page 178)
• DBMS_XPLAN.DISPLAY, TABLE (page 197)
• DBMS_XPLAN.DISPLAY_CURSOR (pages 201, 217)
• DBMS_XPLAN.DISPLAY_AWR (pages 204, 219)
• DBMS_XPLAN.DISPLAY(page 213)
• DBMS_OUTLN.CREATE_OUTLINE (page 282)
• DBMS_OUTLN.UPDATE_BY_CAT (page 283)
• DBMS_OUTLN_EDIT.CREATE_EDIT_TABLES, DBMS_OUTLN_EDIT.REFRESH_PRIVATE_OUTLINE (page 287)
• DBMS_OUTLN.DROP_BY_CAT (page 289)
• DBMS_OUTLN.CLEAR_USED (page 290)
• DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (page 294)
• DBMS_SPM.LOAD_PLANS_FROM_SQLSET, DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE (page 297)
• EXTRACTVALUE, XMLSEQUENCE, XMLTYPE (page 298)
• DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE (page 299)
• DBMS_SPM.ALTER_SQL_PLAN_BASELINE (page 300)
• DBMS_SPM.CREATE_STGTAB_BASELINE, DBMS_SPM.PACK_STGTAB_BASELINE, DBMS_SPM.UNPACK_STGTAB_BASELINE (page 301)
• DBMS_SPM.DROP_SQL_PLAN_BASELINE, DBMS_SPM.CONFIGURE (page 303)
• DBMS_MVIEW.EXPLAIN_REWRITE (page 470)
• DBMS_MVIEW.EXPLAIN_MVIEW (page 471)
• DBMS_MVIEW.REFRESH, DBMS_MVIEW.REFRESH_ALL_MVIEWS, DBMS_MVIEW.REFRESH_DEPENDENT (page 473)
• DBMS_REFRESH.REFRESH (page 474)
• DBMS_RESULT_CACHE.BYPASS , DBMS_RESULT_CACHE.FLUSH , DBMS_RESULT_CACHE.INVALIDATE , DBMS_RESULT_CACHE.INVALIDATE_OBJECT, DBMS_RESULT_CACHE.MEMORY_REPORT, DBMS_RESULT_CACHE.STATUS (page 485)
• VSIZE (page 533)

—

Packages:

• DBMS_SQL (page 17, 332)
• DBMS_SESSION (page 45)
• DBMS_APPLICATION_INFO (page 46)
• DBMS_PROFILER (page 100)
• DBMS_STATS (pages 109, 136)
• DBMS_XPLAN (page 208)
• DBMS_SQLTUNE (pages 267-279)

—

Execution Plans:

• TABLE ACCESS BY INDEX ROWID operation (page 25)
• Common columns found in an execution plan (pages 209-210)
• Query Block Name/Object Alias, Predicate Information (page 211)
• Column Projection, Note (page 212)
• Format parameters for DBMS_XPLAN (pages 213-214, 218)
• Permissions to display execution plans (197, 217, 219)
• Basic rules for parent and child operations in execution plans (pages 221-222)
• COUNT STOPKEY operation (pages 224-225)
• FILTER operation (pages 225, 229)
• UNION-ALL operation (page 226)
• NESTED LOOPS operation (page 228)
• UPDATE operation (page 232)
• CONNECT BY WITH FILTERING operation (page 233)
• Identifying wrong estimated cardinality problems (page 241)
• Displaying SQL Plan Baselines (page 297)
• TABLE ACCESS FULL operation (page 350)
• PARTITION RANGE SINGLE operation (pages 354, 355)
• PARTITION RANGE ITERATOR (page 356)
• PARTITION RANGE INLIST (page 357)
• PARTITION RANGE ALL (page 358)
• PARTITION RANGE EMPTY, PARTITION RANGE OR (page 359)
• PARTITION RANGE ITERATOR, PARTITION RANGE SUBQUERY (page 361)
• PART JOIN FILTER CREATE, PARTITION RANGE JOIN-FILTER (page 362)
• PARTITION RANGE MULTI-COLUMN (page 364)
• PARTITION LIST SINGLE (page 367)
• INDEX FULL SCAN (page 369)
• INDEX FAST FULL SCAN (page 370)
• INDEX FULL SCAN DESCENDING, SORT AGGREGATE, BITMAP CONVERSION TO ROWIDS, BITMAP INDEX FULL SCAN (page 371)
• BITMAP CONVERSION TO ROWIDS, BITMAP INDEX FAST FULL SCAN (page 372)
• TABLE ACCESS BY USER ROWID (page 373)
• INLIST ITERATOR (page 374)
• TABLE ACCESS BY INDEX ROWID (page 376)
• INDEX UNIQUE SCAN (page 381)
• INDEX RANGE SCAN, INDEX RANGE SCAN DESCENDING (page 382)
• BITMAP INDEX SINGLE VALUE, BITMAP CONVERSION TO ROWIDS (page 383)
• BITMAP INDEX RANGE SCAN (page 386)
• INDEX FULL SCAN (MIN/MAX), INDEX RANGE SCAN (MIN/MAX) (page 389)
• NLSSORT (page 394)
• INDEX SKIP SCAN (page 397)
• INDEX SKIP SCAN DESCENDING (page 398)
• BITMAP AND, BITMAP OR (page 400)
• BITMAP MINUS (page 401)
• BITMAP CONVERSION FROM ROWIDS (page 402)
• TABLE ACCESS BY GLOBAL INDEX ROWID, TABLE ACCESS BY LOCAL INDEX ROWID (page 406)
• TABLE ACCESS HASH (page 407)
• CONCATENATION (page 408)
• MERGE JOIN, SORT JOIN (page 425)
• 0Mem column, Used-Mem column (page 429)
• 1Mem column, Used-Tmp column (pages 433-434)
• INDEX$_JOIN$ (page 439)
• HASH JOIN RIGHT OUTER (page 440)
• PARTITION HASH ALL, PX PARTITION HASH ALL (page 445)
• PX SEND QC (RANDOM) (pages 445, 492; page 493 for methods other than QC RANDOM)
• HASH JOIN BUFFERED, PX SEND PARTITION (KEY) (page 447)
• PX BLOCK ITERATOR (pages 447, 491)
• HASH JOIN SEMI (page 450)
• BUFFER SORT, BITMAP MERGE, BITMAP KEY ITERATION (page 453)
• BITMAP CONVERSION TO ROWIDS (page 454)
• TEMP TABLE TRANSFORMATION, LOAD AS SELECT (page 455)
• MAT_VIEW REWRITE ACCESS FULL (page 463)
• RESULT CACHE (page 483)
• TQ column (page 491)
• IN-OUT column (pages 491, 493)
• PX COORDINATOR (page 494)

—

Oracle Error Numbers:

• ORA-13859: action cannot be specified without the module specification (page 67)
• ORA-00922: missing or invalid option (page 80)
• ORA-20005: object statistics are locked (pages 142, 156)
• ORA-01013: user requested cancel of current operation (page 153)
• ORA-38029: object statistics are locked (page 157)
• ORA-03113: TNS:end-of-file on communication channel (page 175)
• ORA-01039: insufficient privileges on underlying objects of the view (page 196)
• ORA-00931: missing identifier (page 283)
• ORA-18009: one or more outline system tables do not exist (page 286)
• ORA-07445, ORA-00600 (page 452)
• ORA-23538: cannot explicitly refresh a NEVER REFRESH materialized view (page 472)
• ORA-12026: invalid filter column detected (page 481)
• ORA-12827: insufficient parallel query slaves available (page 497)
• ORA-12838: cannot read/modify an object after modifying it in parallel (pages 513, 516)
• ORA-26040: data block was loaded using the NOLOGGING option (page 514)
• ORA-01722: invalid number (page 530)

Simple SQL – Finding the Next Operation

November 3, 2011

An interesting request came in from an ERP mailing list – how would you write a SQL statement that indicates the next operation in a manufacturing process.  Sounds like an easy requirement.  Let’s take a look at a graphical view of one example (the graphical view is created using a program that I wrote probably 7 years ago):

The cyan colored rectangles in the above picture are the various operations (found in the OPERATION table) in the manufacturing process.  The light green colored rectangles are the material requirements (found in the REQUIREMENT table) that are used by the operation that is immediately above the sequence of light green rectangles.  The white rectangles are the sub-assembly headers for the manufacturing process.  The white rectangle at the far left is considered the main sub-assembly 0 (WORKORDER_SUB_ID = ’0′), the middle white rectangle in this case is sub-assembly 1 (WORKORDER_SUB_ID = ’1′), and the right-most white rectangle in this case is sub-assembly 200 (WORKORDER_SUB_ID = ’200′).  All sub-assemblies except the main sub-assembly 0 are tied to another “parent” operation that consumes the sub-assembly just as if it were another material requirement; therefore a dummy row is added to the material requirements table (REQUIREMENT) with the SUBORD_WO_SUB_ID column set to the sub-assembly number of the connected operation.

In the above picture, on the main sub-assembly 0, operation 888 (at resource ID INSPECT) is the first operation, and operation 999 (at resource ID SHIP) is the second operation.  On sub-assembly 1, operation 10 (at resource ID 92) is the first operation, operation 20 (at resource ID 62) is the second operation, and operation 541 (at resource ID KRW) is the third operation.  On sub-assembly 200, operation 10 (at resource ID WSD70-TACK) is the first operation, operation 20 (at resource ID WELD-CBA) is the second operation, operation 40 (at resource ID BLAST) is the third operation, and operation 50 (at resource ID PAINT) is the fourth operation.

Since I am working with Oracle Database, I can construct a SQL statement using the LEAD analytic function to find the next operation number and the next resource ID:

SELECT
  *
FROM
  (SELECT
    O.WORKORDER_BASE_ID,
    O.WORKORDER_LOT_ID,
    O.WORKORDER_SPLIT_ID,
    O.WORKORDER_SUB_ID,
    O.SEQUENCE_NO,
    O.RESOURCE_ID,
    LEAD(O.SEQUENCE_NO,1) OVER (PARTITION BY O.WORKORDER_BASE_ID,
                                             O.WORKORDER_LOT_ID,
                                             O.WORKORDER_SPLIT_ID,
                                             O.WORKORDER_SUB_ID
                                ORDER BY O.SEQUENCE_NO) AS NEXT_SEQUENCE_NO,
    LEAD(O.RESOURCE_ID,1) OVER (PARTITION BY O.WORKORDER_BASE_ID,
                                             O.WORKORDER_LOT_ID,
                                             O.WORKORDER_SPLIT_ID,
                                             O.WORKORDER_SUB_ID
                                ORDER BY O.SEQUENCE_NO) AS NEXT_RESOURCE_ID
  FROM
    OPERATION O
  WHERE
    O.WORKORDER_TYPE='W'
    AND O.WORKORDER_BASE_ID='13000'
    AND O.WORKORDER_LOT_ID='50'
    AND O.WORKORDER_SPLIT_ID='0') O
ORDER BY
  O.WORKORDER_BASE_ID,
  O.WORKORDER_LOT_ID,
  O.WORKORDER_SPLIT_ID,
  O.WORKORDER_SUB_ID,
  O.SEQUENCE_NO;

WORKORDER_BASE_ID    WOR WOR WOR SEQUENCE_NO RESOURCE_ID     NEXT_SEQUENCE_NO NEXT_RESOURCE_I
-------------------- --- --- --- ----------- --------------- ---------------- ---------------
13000                50  0   0           888 INSPECT                      999 SHIP
13000                50  0   0           999 SHIP
13000                50  0   1            10 92                            20 62
13000                50  0   1            20 62                           541 KRW
13000                50  0   1           541 KRW
13000                50  0   200          10 WSD70-TACK FIX                20 WELD-CBA
13000                50  0   200          20 WELD-CBA                      40 BLAST
13000                50  0   200          40 BLAST                         50 PAINT
13000                50  0   200          50 PAINT

Looks like a pretty easy solution… unless we recognize that after the last operation on sub-assembly 200 the next operation is really the parent operation of that sub-assembly (operation 10 on sub-assembly 1).  Likewise, after the last operation on sub-assembly 1, the next operation is really the parent operation of that sub-assembly (operation 888 on sub-assembly 0).  There is no next operation after operation 999 on the main sub-assembly 0.  How can we fix this problem with the NULL next operations in the previous output?  We just need an outer join to the dummy row in the REQUIREMENT table to pick up the parent’s sub-assembly number, and then join that dummy row back to a second reference of the OPERATION table.  The NVL2 and COALESCE functions are used to handle the cases where the original next operation and next resource ID would have been output as NULL values:

SELECT
  O.WORKORDER_BASE_ID,
  O.WORKORDER_LOT_ID,
  O.WORKORDER_SPLIT_ID,
  O.WORKORDER_SUB_ID,
  O.SEQUENCE_NO,
  O.RESOURCE_ID,
  NVL2(O.NEXT_SEQUENCE_NO,O.WORKORDER_SUB_ID,O2.WORKORDER_SUB_ID) NEXT_SUB_ID,
  COALESCE(O.NEXT_SEQUENCE_NO,O2.SEQUENCE_NO) NEXT_SEQUENCE_NO,
  COALESCE(O.NEXT_RESOURCE_ID,O2.RESOURCE_ID) NEXT_RESOURCE_ID
FROM
  (SELECT
    O.WORKORDER_TYPE,
    O.WORKORDER_BASE_ID,
    O.WORKORDER_LOT_ID,
    O.WORKORDER_SPLIT_ID,
    O.WORKORDER_SUB_ID,
    O.SEQUENCE_NO,
    O.RESOURCE_ID,
    LEAD(O.SEQUENCE_NO,1) OVER (PARTITION BY O.WORKORDER_BASE_ID,
                                             O.WORKORDER_LOT_ID,
                                             O.WORKORDER_SPLIT_ID,
                                             O.WORKORDER_SUB_ID
                                ORDER BY O.SEQUENCE_NO) AS NEXT_SEQUENCE_NO,
    LEAD(O.RESOURCE_ID,1) OVER (PARTITION BY O.WORKORDER_BASE_ID,
                                             O.WORKORDER_LOT_ID,
                                             O.WORKORDER_SPLIT_ID,
                                             O.WORKORDER_SUB_ID
                                ORDER BY O.SEQUENCE_NO) AS NEXT_RESOURCE_ID
  FROM
    OPERATION O
  WHERE
    O.WORKORDER_TYPE='W'
    AND O.WORKORDER_BASE_ID='13000'
    AND O.WORKORDER_LOT_ID='50'
    AND O.WORKORDER_SPLIT_ID='0') O,
  REQUIREMENT R,
  OPERATION O2
WHERE
  O.WORKORDER_TYPE=R.WORKORDER_TYPE(+)
  AND O.WORKORDER_BASE_ID=R.WORKORDER_BASE_ID(+)
  AND O.WORKORDER_LOT_ID=R.WORKORDER_LOT_ID(+)
  AND O.WORKORDER_SPLIT_ID=R.WORKORDER_SPLIT_ID(+)
  AND O.WORKORDER_SUB_ID=R.SUBORD_WO_SUB_ID(+)
  AND R.WORKORDER_TYPE=O2.WORKORDER_TYPE(+)
  AND R.WORKORDER_BASE_ID=O2.WORKORDER_BASE_ID(+)
  AND R.WORKORDER_LOT_ID=O2.WORKORDER_LOT_ID(+)
  AND R.WORKORDER_SPLIT_ID=O2.WORKORDER_SPLIT_ID(+)
  AND R.WORKORDER_SUB_ID=O2.WORKORDER_SUB_ID(+)
  AND R.OPERATION_SEQ_NO=O2.SEQUENCE_NO(+)
ORDER BY
  O.WORKORDER_BASE_ID,
  O.WORKORDER_LOT_ID,
  O.WORKORDER_SPLIT_ID,
  O.WORKORDER_SUB_ID,
  O.SEQUENCE_NO;

WORKORDER_BASE_ID    WOR WOR WOR SEQUENCE_NO RESOURCE_ID     NEX NEXT_SEQUENCE_NO NEXT_RESOURCE_I
-------------------- --- --- --- ----------- --------------- --- ---------------- ---------------
13000                50  0   0           888 INSPECT         0                999 SHIP
13000                50  0   0           999 SHIP
13000                50  0   1            10 92              1                 20 62
13000                50  0   1            20 62              1                541 KRW
13000                50  0   1           541 KRW             0                888 INSPECT
13000                50  0   200          10 WSD70-TACK FIX  200               20 WELD-CBA
13000                50  0   200          20 WELD-CBA        200               40 BLAST
13000                50  0   200          40 BLAST           200               50 PAINT
13000                50  0   200          50 PAINT           1                 10 92
 

In case you are wondering, the execution plan for the above SQL statement looks like this:

Plan hash value: 1263351280

-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                 |      1 |        |      9 |00:00:00.01 |      43 |       |       |          |
|   1 |  SORT ORDER BY                   |                 |      1 |      9 |      9 |00:00:00.01 |      43 |  2048 |  2048 | 2048  (0)|
|   2 |   NESTED LOOPS OUTER             |                 |      1 |      9 |      9 |00:00:00.01 |      43 |       |       |          |
|   3 |    NESTED LOOPS OUTER            |                 |      1 |      9 |      9 |00:00:00.01 |      23 |       |       |          |
|   4 |     VIEW                         |                 |      1 |      9 |      9 |00:00:00.01 |       9 |       |       |          |
|   5 |      WINDOW BUFFER               |                 |      1 |      9 |      9 |00:00:00.01 |       9 |  2048 |  2048 | 2048  (0)|
|   6 |       TABLE ACCESS BY INDEX ROWID| OPERATION       |      1 |      9 |      9 |00:00:00.01 |       9 |       |       |          |
|*  7 |        INDEX RANGE SCAN          | SYS_C0021734    |      1 |      9 |      9 |00:00:00.01 |       3 |       |       |          |
|   8 |     TABLE ACCESS BY INDEX ROWID  | REQUIREMENT     |      9 |      1 |      7 |00:00:00.01 |      14 |       |       |          |
|*  9 |      INDEX RANGE SCAN            | X_REQUIREMENT_5 |      9 |      1 |      7 |00:00:00.01 |      13 |       |       |          |
|  10 |    TABLE ACCESS BY INDEX ROWID   | OPERATION       |      9 |      1 |      7 |00:00:00.01 |      20 |       |       |          |
|* 11 |     INDEX UNIQUE SCAN            | SYS_C0021734    |      9 |      1 |      7 |00:00:00.01 |      13 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   7 - access("O"."WORKORDER_TYPE"='W' AND "O"."WORKORDER_BASE_ID"='13000' AND "O"."WORKORDER_LOT_ID"='50' AND
              "O"."WORKORDER_SPLIT_ID"='0')
   9 - access("O"."WORKORDER_TYPE"="R"."WORKORDER_TYPE" AND "O"."WORKORDER_BASE_ID"="R"."WORKORDER_BASE_ID" AND
              "O"."WORKORDER_LOT_ID"="R"."WORKORDER_LOT_ID" AND "O"."WORKORDER_SPLIT_ID"="R"."WORKORDER_SPLIT_ID" AND
              "O"."WORKORDER_SUB_ID"="R"."SUBORD_WO_SUB_ID")
       filter("R"."SUBORD_WO_SUB_ID" IS NOT NULL)
  11 - access("R"."WORKORDER_TYPE"="O2"."WORKORDER_TYPE" AND "R"."WORKORDER_BASE_ID"="O2"."WORKORDER_BASE_ID" AND
              "R"."WORKORDER_LOT_ID"="O2"."WORKORDER_LOT_ID" AND "R"."WORKORDER_SPLIT_ID"="O2"."WORKORDER_SPLIT_ID" AND
              "R"."WORKORDER_SUB_ID"="O2"."WORKORDER_SUB_ID" AND "R"."OPERATION_SEQ_NO"="O2"."SEQUENCE_NO")

Note
-----
   - cardinality feedback used for this statement 

The above takes less that 0.01 seconds to execute, in part because I was careful to preserve the leading columns in the primary key indexes’ columns when building the inline view.

But, what if the OP (as he mentioned) was running with an older version of SQL Server that did not support the LEAD analytic function?  Is the OP out of luck (it could be argued that is a trick question)?

Let’s start again, this time we will self-join the OPERATION table rather than using an analytic function to determine the next operation, and just for fun we will use ANSI join syntax:

SELECT
  O.WORKORDER_BASE_ID,
  O.WORKORDER_LOT_ID,
  O.WORKORDER_SPLIT_ID,
  O.WORKORDER_SUB_ID,
  O.SEQUENCE_NO,
  MIN(O2.SEQUENCE_NO) AS NEXT_SEQUENCE_NO
FROM
  OPERATION O
LEFT OUTER JOIN
  OPERATION O2
ON
  O.WORKORDER_TYPE=O2.WORKORDER_TYPE
  AND O.WORKORDER_BASE_ID=O2.WORKORDER_BASE_ID
  AND O.WORKORDER_LOT_ID=O2.WORKORDER_LOT_ID
  AND O.WORKORDER_SPLIT_ID=O2.WORKORDER_SPLIT_ID
  AND O.WORKORDER_SUB_ID=O2.WORKORDER_SUB_ID
  AND O.SEQUENCE_NO < O2.SEQUENCE_NO
WHERE
  O.WORKORDER_TYPE='W'
  AND O.WORKORDER_BASE_ID='13000'
  AND O.WORKORDER_LOT_ID='50'
  AND O.WORKORDER_SPLIT_ID='0'
GROUP BY
  O.WORKORDER_BASE_ID,
  O.WORKORDER_LOT_ID,
  O.WORKORDER_SPLIT_ID,
  O.WORKORDER_SUB_ID,
  O.SEQUENCE_NO
ORDER BY
  O.WORKORDER_BASE_ID,
  O.WORKORDER_LOT_ID,
  O.WORKORDER_SPLIT_ID,
  O.WORKORDER_SUB_ID,
  O.SEQUENCE_NO;

WORKORDER_BASE_ID    WOR WOR WOR SEQUENCE_NO NEXT_SEQUENCE_NO
-------------------- --- --- --- ----------- ----------------
13000                50  0   0           888              999
13000                50  0   0           999
13000                50  0   1            10               20
13000                50  0   1            20              541
13000                50  0   1           541
13000                50  0   200          10               20
13000                50  0   200          20               40
13000                50  0   200          40               50
13000                50  0   200          50 

Now, we will slide the above SQL statement into an inline view and use the COALESCE function to return the first non-NULL value in a list of columns, and the Oracle only NVL2 function to return one of two column values depending on whether or not a third column contains a NULL value:

SELECT
  O.WORKORDER_TYPE,
  O.WORKORDER_BASE_ID,
  O.WORKORDER_LOT_ID,
  O.WORKORDER_SPLIT_ID,
  O.WORKORDER_SUB_ID,
  O.SEQUENCE_NO,
  O.RESOURCE_ID,
  NVL2(O.NEXT_SEQUENCE_NO,O.WORKORDER_SUB_ID,R.WORKORDER_SUB_ID) AS NEXT_SUB_ID,
  COALESCE(O.NEXT_SEQUENCE_NO,R.OPERATION_SEQ_NO) AS NEXT_SEQUENCE_NO
FROM
  (SELECT
    O.WORKORDER_TYPE,
    O.WORKORDER_BASE_ID,
    O.WORKORDER_LOT_ID,
    O.WORKORDER_SPLIT_ID,
    O.WORKORDER_SUB_ID,
    O.SEQUENCE_NO,
    O.RESOURCE_ID,
    MIN(O2.SEQUENCE_NO) AS NEXT_SEQUENCE_NO
  FROM
    OPERATION O
  LEFT OUTER JOIN
    OPERATION O2
  ON
    O.WORKORDER_TYPE=O2.WORKORDER_TYPE
    AND O.WORKORDER_BASE_ID=O2.WORKORDER_BASE_ID
    AND O.WORKORDER_LOT_ID=O2.WORKORDER_LOT_ID
    AND O.WORKORDER_SPLIT_ID=O2.WORKORDER_SPLIT_ID
    AND O.WORKORDER_SUB_ID=O2.WORKORDER_SUB_ID
    AND O.SEQUENCE_NO < O2.SEQUENCE_NO
  WHERE
    O.WORKORDER_TYPE='W'
    AND O.WORKORDER_BASE_ID='13000'
    AND O.WORKORDER_LOT_ID='50'
    AND O.WORKORDER_SPLIT_ID='0'
  GROUP BY
    O.WORKORDER_TYPE,
    O.WORKORDER_BASE_ID,
    O.WORKORDER_LOT_ID,
    O.WORKORDER_SPLIT_ID,
    O.WORKORDER_SUB_ID,
    O.SEQUENCE_NO,
    O.RESOURCE_ID) O
LEFT OUTER JOIN
  REQUIREMENT R
ON
  O.WORKORDER_TYPE=R.WORKORDER_TYPE
  AND O.WORKORDER_BASE_ID=R.WORKORDER_BASE_ID
  AND O.WORKORDER_LOT_ID=R.WORKORDER_LOT_ID
  AND O.WORKORDER_SPLIT_ID=R.WORKORDER_SPLIT_ID
  AND O.WORKORDER_SUB_ID=R.SUBORD_WO_SUB_ID
ORDER BY
  O.WORKORDER_BASE_ID,
  O.WORKORDER_LOT_ID,
  O.WORKORDER_SPLIT_ID,
  O.WORKORDER_SUB_ID,
  O.SEQUENCE_NO;

W WORKORDER_BASE_ID    WOR WOR WOR SEQUENCE_NO RESOURCE_ID     NEX NEXT_SEQUENCE_NO
- -------------------- --- --- --- ----------- --------------- --- ----------------
W 13000                50  0   0           888 INSPECT         0                999
W 13000                50  0   0           999 SHIP
W 13000                50  0   1            10 92              1                 20
W 13000                50  0   1            20 62              1                541
W 13000                50  0   1           541 KRW             0                888
W 13000                50  0   200          10 WSD70-TACK FIX  200               20
W 13000                50  0   200          20 WELD-CBA        200               40
W 13000                50  0   200          40 BLAST           200               50
W 13000                50  0   200          50 PAINT           1                 10 

Almost there – we still need the resource ID of the next operation.  Sliding the above into another level of inline view, we end up with the following:

SELECT
  O.WORKORDER_BASE_ID,
  O.WORKORDER_LOT_ID,
  O.WORKORDER_SPLIT_ID,
  O.WORKORDER_SUB_ID,
  O.RESOURCE_ID,
  O.NEXT_SUB_ID,
  O.NEXT_SEQUENCE_NO,
  O2.RESOURCE_ID NEXT_RESOURCE_ID
FROM
  (SELECT
    O.WORKORDER_TYPE,
    O.WORKORDER_BASE_ID,
    O.WORKORDER_LOT_ID,
    O.WORKORDER_SPLIT_ID,
    O.WORKORDER_SUB_ID,
    O.SEQUENCE_NO,
    O.RESOURCE_ID,
    NVL2(O.NEXT_SEQUENCE_NO,O.WORKORDER_SUB_ID,R.WORKORDER_SUB_ID) AS NEXT_SUB_ID,
    COALESCE(O.NEXT_SEQUENCE_NO,R.OPERATION_SEQ_NO) AS NEXT_SEQUENCE_NO
  FROM
    (SELECT
      O.WORKORDER_TYPE,
      O.WORKORDER_BASE_ID,
      O.WORKORDER_LOT_ID,
      O.WORKORDER_SPLIT_ID,
      O.WORKORDER_SUB_ID,
      O.SEQUENCE_NO,
      O.RESOURCE_ID,
      MIN(O2.SEQUENCE_NO) AS NEXT_SEQUENCE_NO
    FROM
      OPERATION O
    LEFT OUTER JOIN
      OPERATION O2
    ON
      O.WORKORDER_TYPE=O2.WORKORDER_TYPE
      AND O.WORKORDER_BASE_ID=O2.WORKORDER_BASE_ID
      AND O.WORKORDER_LOT_ID=O2.WORKORDER_LOT_ID
      AND O.WORKORDER_SPLIT_ID=O2.WORKORDER_SPLIT_ID
      AND O.WORKORDER_SUB_ID=O2.WORKORDER_SUB_ID
      AND O.SEQUENCE_NO < O2.SEQUENCE_NO
    WHERE
      O.WORKORDER_TYPE='W'
      AND O.WORKORDER_BASE_ID='13000'
      AND O.WORKORDER_LOT_ID='50'
      AND O.WORKORDER_SPLIT_ID='0'
    GROUP BY
      O.WORKORDER_TYPE,
      O.WORKORDER_BASE_ID,
      O.WORKORDER_LOT_ID,
      O.WORKORDER_SPLIT_ID,
      O.WORKORDER_SUB_ID,
      O.SEQUENCE_NO,
      O.RESOURCE_ID) O
  LEFT OUTER JOIN
    REQUIREMENT R
  ON
    O.WORKORDER_TYPE=R.WORKORDER_TYPE
    AND O.WORKORDER_BASE_ID=R.WORKORDER_BASE_ID
    AND O.WORKORDER_LOT_ID=R.WORKORDER_LOT_ID
    AND O.WORKORDER_SPLIT_ID=R.WORKORDER_SPLIT_ID
    AND O.WORKORDER_SUB_ID=R.SUBORD_WO_SUB_ID) O
LEFT OUTER JOIN
  OPERATION O2
ON
  O.WORKORDER_TYPE=O2.WORKORDER_TYPE
  AND O.WORKORDER_BASE_ID=O2.WORKORDER_BASE_ID
  AND O.WORKORDER_LOT_ID=O2.WORKORDER_LOT_ID
  AND O.WORKORDER_SPLIT_ID=O2.WORKORDER_SPLIT_ID
  AND O.NEXT_SUB_ID=O2.WORKORDER_SUB_ID
  AND O.NEXT_SEQUENCE_NO=O2.SEQUENCE_NO
ORDER BY
  O.WORKORDER_BASE_ID,
  O.WORKORDER_LOT_ID,
  O.WORKORDER_SPLIT_ID,
  O.WORKORDER_SUB_ID,
  O.SEQUENCE_NO;

WORKORDER_BASE_ID    WOR WOR WOR RESOURCE_ID     NEX NEXT_SEQUENCE_NO NEXT_RESOURCE_I
-------------------- --- --- --- --------------- --- ---------------- ---------------
13000                50  0   0   INSPECT         0                999 SHIP
13000                50  0   0   SHIP
13000                50  0   1   92              1                 20 62
13000                50  0   1   62              1                541 KRW
13000                50  0   1   KRW             0                888 INSPECT
13000                50  0   200 WSD70-TACK FIX  200               20 WELD-CBA
13000                50  0   200 WELD-CBA        200               40 BLAST
13000                50  0   200 BLAST           200               50 PAINT
13000                50  0   200 PAINT           1                 10 92 

The above SQL statement has the following execution plan:

Plan hash value: 3374377097

---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |              |      1 |        |      9 |00:00:00.01 |      41 |       |       |          |
|   1 |  SORT ORDER BY                    |              |      1 |      9 |      9 |00:00:00.01 |      41 |  2048 |  2048 | 2048  (0)|
|   2 |   NESTED LOOPS OUTER              |              |      1 |      9 |      9 |00:00:00.01 |      41 |       |       |          |
|   3 |    VIEW                           |              |      1 |      9 |      9 |00:00:00.01 |      19 |       |       |          |
|   4 |     HASH GROUP BY                 |              |      1 |      9 |      9 |00:00:00.01 |      19 |   781K|   781K| 1103K (0)|
|*  5 |      HASH JOIN OUTER              |              |      1 |      9 |     13 |00:00:00.01 |      19 |   851K|   851K|  653K (0)|
|*  6 |       HASH JOIN OUTER             |              |      1 |      9 |      9 |00:00:00.01 |      16 |   927K|   927K|  651K (0)|
|   7 |        TABLE ACCESS BY INDEX ROWID| OPERATION    |      1 |      9 |      9 |00:00:00.01 |       9 |       |       |          |
|*  8 |         INDEX RANGE SCAN          | SYS_C0021734 |      1 |      9 |      9 |00:00:00.01 |       3 |       |       |          |
|*  9 |        TABLE ACCESS BY INDEX ROWID| REQUIREMENT  |      1 |      1 |      2 |00:00:00.01 |       7 |       |       |          |
|* 10 |         INDEX RANGE SCAN          | SYS_C0021842 |      1 |      1 |     18 |00:00:00.01 |       3 |       |       |          |
|* 11 |       INDEX RANGE SCAN            | SYS_C0021734 |      1 |      1 |      9 |00:00:00.01 |       3 |       |       |          |
|  12 |    TABLE ACCESS BY INDEX ROWID    | OPERATION    |      9 |      1 |      8 |00:00:00.01 |      22 |       |       |          |
|* 13 |     INDEX UNIQUE SCAN             | SYS_C0021734 |      9 |      1 |      8 |00:00:00.01 |      14 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("O"."WORKORDER_SUB_ID"="O2"."WORKORDER_SUB_ID" AND "O"."WORKORDER_SPLIT_ID"="O2"."WORKORDER_SPLIT_ID" AND
              "O"."WORKORDER_LOT_ID"="O2"."WORKORDER_LOT_ID" AND "O"."WORKORDER_BASE_ID"="O2"."WORKORDER_BASE_ID" AND
              "O"."WORKORDER_TYPE"="O2"."WORKORDER_TYPE")
       filter("O"."SEQUENCE_NO"<"O2"."SEQUENCE_NO")
   6 - access("O"."WORKORDER_SUB_ID"="R"."SUBORD_WO_SUB_ID" AND "O"."WORKORDER_SPLIT_ID"="R"."WORKORDER_SPLIT_ID" AND
              "O"."WORKORDER_LOT_ID"="R"."WORKORDER_LOT_ID" AND "O"."WORKORDER_BASE_ID"="R"."WORKORDER_BASE_ID" AND
              "O"."WORKORDER_TYPE"="R"."WORKORDER_TYPE")
   8 - access("O"."WORKORDER_TYPE"='W' AND "O"."WORKORDER_BASE_ID"='13000' AND "O"."WORKORDER_LOT_ID"='50' AND
              "O"."WORKORDER_SPLIT_ID"='0')
   9 - filter("R"."SUBORD_WO_SUB_ID" IS NOT NULL)
  10 - access("R"."WORKORDER_TYPE"='W' AND "R"."WORKORDER_BASE_ID"='13000' AND "R"."WORKORDER_LOT_ID"='50' AND
              "R"."WORKORDER_SPLIT_ID"='0')
  11 - access("O2"."WORKORDER_TYPE"='W' AND "O2"."WORKORDER_BASE_ID"='13000' AND "O2"."WORKORDER_LOT_ID"='50' AND
              "O2"."WORKORDER_SPLIT_ID"='0')
  13 - access("O"."WORKORDER_TYPE"="O2"."WORKORDER_TYPE" AND "O"."WORKORDER_BASE_ID"="O2"."WORKORDER_BASE_ID" AND
              "O"."WORKORDER_LOT_ID"="O2"."WORKORDER_LOT_ID" AND "O"."WORKORDER_SPLIT_ID"="O2"."WORKORDER_SPLIT_ID" AND
              "O"."NEXT_SUB_ID"="O2"."WORKORDER_SUB_ID" AND "O"."NEXT_SEQUENCE_NO"="O2"."SEQUENCE_NO")

Note
-----
   - cardinality feedback used for this statement 

—

So, now that we have two different SQL statements that both solve the problem that was presented by the OP, which version of the SQL statement is the most efficient, and which would you use in production?

Simple SQL with and without Inline Views 2

October 26, 2011

(Back to the Previous Post in the Series)

In the previous article of this series we examined a couple of moderately simple data retrieval requirements, and used inline views and/or analytic functions to solve those data retrieval requirements.  I recently saw another opportunity to help a person on the ERP mailing list with a SQL statement.  The original poster (OP) provided a screen capture that contained information similar to the following:

CUST_ORDER_ID   PART_ID         TYPE CLASS WAREHOUSE_ID LOCATION_ID  QTY_ON_HAND TRANSACTI
--------------- --------------- ---- ----- ------------ ------------ ----------- ---------
ZQHZG           2J-2908         O    I     FIB          AB7A10                30 21-JUN-10
ZQHZZ           2J-2909         O    I     FIB          AB7A10                 1 21-JUN-10
ZIHKR           2J0836          O    I     HIBB         A77Y3               1092 30-NOV-07
ZQKZH           2J0836          O    I     HIBB         A77Y3               1092 10-JUN-08
ZQIHZ           2J0836          O    I     HIBB         A77Y3               1092 23-NOV-09
ZKOHI           2J4120          O    I     PIPABTONA    AB7A10                 2 16-NOV-95
ZKOHQ           2J4129          O    I     PIPABTONA    AB7A10                 2 16-NOV-95
ZKGQG           2J9785          O    I     HIBB         AB7A10                 2 25-SEP-95
ZKGQG           2J9785          O    I     HIBB         AB7A10                 2 25-SEP-95
ZKGQG           2J9785          O    I     HIBB         AB7A10                 2 26-OCT-95
ZKGQG           2J9785          O    I     HIBB         AB7A10                 2 26-OCT-95
ZKGQG           2J9785          O    I     HIBB         AB7A10                 2 12-DEC-95
ZKGQG           2J9785          O    I     HIBB         AB7A10                 2 12-DEC-95
ZKGQG           2J9785          O    I     HIBB         AB7A10                 2 27-FEB-96
ZKGQG           2J9785          O    I     HIBB         AB7A10                 2 04-APR-96
ZKGQG           2J9785          O    I     HIBB         AB7A10                 2 04-APR-96
ZKGQG           2J9785          O    I     HIBB         AB7A10                 2 31-MAY-96
ZKGQG           2J9785          O    I     HIBB         AB7A10                 2 31-MAY-96
ZKGQG           2J9785          O    I     HIBB         AB7A10                 2 06-SEP-96
ZKGQG           2J9785          O    I     HIBB         AB7A10                 2 30-SEP-96
ZKGQG           2J9785          O    I     HIBB         AB7A10                 2 30-SEP-96
ZKGQG           2J9785          O    I     HIBB         AB7A10                 2 30-SEP-96
ZKGQG           2J9785          O    I     HIBB         AB7A10                 2 12-JAN-98
ZKGQG           2J9785          O    I     HIBB         AB7A10                 2 12-FEB-98
ZKGQG           2J9785          O    I     HIBB         AB7A10                 2 04-MAR-98
ZKGQG           2J9785          O    I     HIBB         AB7A10                 2 05-MAY-98
ZKGQG           2J9785          O    I     HIBB         AB7A10                 2 05-MAY-98
ZKGQG           2J9785          O    I     HIBB         AB7A10                 2 18-AUG-98
ZKGQG           2J9785          O    I     HIBB         AB7A10                 2 18-AUG-98
ZKGQG           2J9785          O    I     HIBB         AB7A10                 2 29-SEP-98
ZKGQG           2J9785          O    I     HIBB         AB7A10                 2 02-DEC-98
ZKGQG           2J9785          O    I     HIBB         AB7A10                 2 02-DEC-98
ZKGQG           2J9785          O    I     HIBB         AB7A10                 2 02-DEC-98
ZIRZQ           3J8547          O    I     HIBB         A77Y2                252 18-OCT-07
ZZUQG           4G0007          O    I     HIBB         AB7A10                 2 08-NOV-99
ZZUQG           4G0007          O    I     HIBB         AB7A10                 2 30-NOV-99 
...

Initially, the OP requested that the most recent TRANSACTION_DATE (the last column in the above output) for the PART_ID value to be shown on each row of the output, rather than the TRANSACTION_DATE that is directly associated with a specific row.  However, there are two problems:

• The OP did not mention what tables were used as the data source for the included screen capture.
• The OP did not mention whether a recent release of Oracle Database or some other brand of database was in use.

For times like these, it helps a lot to be familiar with the data model used in the database.  The INVENTORY_TRANS table in this particular ERP system has columns that match several of the columns that were included in the OP’s screen capture: CUST_ORDER_ID, PART_ID, TYPE, CLASS, WAREHOUSE_ID, LOCATION_ID, and TRANSACTION_DATE.  The question remains, where did the QTY_ON_HAND column come from – that column is not in the INVENTORY_TRANS table.  We could find out, at least on Oracle Database, with a SQL statement similar to the following:

SELECT
  DTC.TABLE_NAME,
  DT.TABLE_NAME
FROM
  DBA_TAB_COLUMNS DTC,
  DBA_TABLES DT
WHERE
  DTC.COLUMN_NAME='QTY_ON_HAND'
  AND DTC.OWNER='SCHEMA_OWNER_HERE'
  AND DTC.OWNER=DT.OWNER(+)
  AND DTC.TABLE_NAME=DT.TABLE_NAME(+)
ORDER BY
  DTC.TABLE_NAME;

TABLE_NAME                     TABLE_NAME
------------------------------ ----------
CR_EC_LINE_PART
CR_OM_CO_LINE_PART
CR_PART_PO_LINE
CR_WO_PART
PART                           PART
PART_USER_DEFINED_VIEW 

Note that in the above, I had to join the DBA_TAB_COLUMNS dictionary view with the DBA_TABLES dictionary view (I could have used DBA_OBJECTS instead) to determine that 5 of the 6 rows returned from the DBA_TAB_COLUMNS view are in fact not tables, but views.  Thus, the QTY_ON_HAND column in the OP’s screen capture must have originated from the PART table (unless, of course, the column in the screen capture is actually an aliased calculation).

Now that the data sources are known for the query, we can take a best guess approach at reproducing the output that the OP provided in the screen capture:

SELECT
  IT.CUST_ORDER_ID,
  IT.PART_ID,
  TYPE,
  CLASS,
  IT.WAREHOUSE_ID,
  IT.LOCATION_ID,
  P.QTY_ON_HAND,
  IT.TRANSACTION_DATE
FROM
  INVENTORY_TRANS IT,
  PART P
WHERE
  IT.CUST_ORDER_ID IS NOT NULL
  AND IT.TYPE='O'
  AND IT.CLASS='I'
  AND IT.PART_ID=P.ID
  AND P.QTY_ON_HAND>0
ORDER BY
  IT.PART_ID,
  IT.TRANSACTION_DATE; 

Next, there are a couple of ways to have the most recent transaction date appear on each row of the output (partially dependent on the database version).  First, let’s build a query that returns the most recent transaction date where the TYPE is O and the CLASS is I and the CUST_ORDER_ID column does not contain a NULL:

SELECT
  PART_ID,
  MAX(TRANSACTION_DATE) AS LAST_TRANSACTION_DATE
FROM
  INVENTORY_TRANS
WHERE
  TYPE='O'
  AND CLASS='I'
  AND CUST_ORDER_ID IS NOT NULL
GROUP BY
  PART_ID
ORDER BY
  PART_ID; 

PART_ID         LAST_TRAN
--------------- ---------
0000009         29-DEC-00
005035008005    29-MAY-98
005035008006    29-MAY-98
00576649-0080   20-OCT-11
00576649-0088   08-JUN-11
00576649-8005   14-JAN-11
007580398       17-JAN-03
0099229940      01-NOV-95
0099229990      01-NOV-95
0108556         28-APR-00
01550867KM      15-NOV-05
01552316KM      02-OCT-00
01552346KM      30-APR-03
01552369KM      30-APR-01
01558943M       07-JAN-10
01561230KM      30-OCT-01
01563001M       08-MAR-10
01563882M       01-APR-10
01566790KM      08-SEP-08
01569945M       01-APR-10
01583508TO      11-FEB-05
01780151TO      19-JUL-07
...

Next, we will just plug the above inline view into the FROM clause of our original SQL statement and add an additional predicate into the WHERE clause to tell the query optimizer how to associate the inline view with the other tables in the SQL statement:

SELECT
  IT.CUST_ORDER_ID,
  IT.PART_ID,
  IT.QTY,
  IT.WAREHOUSE_ID,
  IT.LOCATION_ID,
  P.QTY_ON_HAND,
  MIT.LAST_TRANSACTION_DATE
FROM
  INVENTORY_TRANS IT,
  PART P,
  (SELECT
     PART_ID,
     MAX(TRANSACTION_DATE) AS LAST_TRANSACTION_DATE
   FROM
     INVENTORY_TRANS
   WHERE
     TYPE='O'
     AND CLASS='I'
     AND CUST_ORDER_ID IS NOT NULL
   GROUP BY
     PART_ID) MIT
WHERE
  IT.CUST_ORDER_ID IS NOT NULL
  AND IT.TYPE='O'
  AND IT.CLASS='I'
  AND IT.PART_ID=P.ID
  AND P.QTY_ON_HAND>0
  AND IT.PART_ID=MIT.PART_ID
ORDER BY
  IT.PART_ID,
  IT.CUST_ORDER_ID,
  IT.TRANSACTION_DATE; 

CUST_ORDER_ID   PART_ID             QTY WAREHOUSE_ID LOCATION_ID  QTY_ON_HAND LAST_TRAN
--------------- ------------ ---------- ------------ ------------ ----------- ---------
ZQHZG           2J-2908               1 FIB          AB7A10                30 21-JUN-10
ZQHZZ           2J-2909               1 FIB          AB7A10                 1 21-JUN-10
ZIHKR           2J0836               48 HIBB         A77Y3               1092 23-NOV-09
ZQIHZ           2J0836               72 HIBB         A77Y3               1092 23-NOV-09
ZQKZH           2J0836              111 HIBB         A77Y3               1092 23-NOV-09
ZKOHI           2J4120                2 PIPABTONA    AB7A10                 2 16-NOV-95
ZKOHQ           2J4129                2 PIPABTONA    AB7A10                 2 16-NOV-95
ZKGQG           2J9785                9 HIBB         AB7A10                 2 02-DEC-98
ZKGQG           2J9785                1 HIBB         AB7A10                 2 02-DEC-98
ZKGQG           2J9785                7 HIBB         AB7A10                 2 02-DEC-98
ZKGQG           2J9785                2 HIBB         AB7A10                 2 02-DEC-98
ZKGQG           2J9785                6 HIBB         AB7A10                 2 02-DEC-98
ZKGQG           2J9785                4 HIBB         AB7A10                 2 02-DEC-98
ZKGQG           2J9785               10 HIBB         AB7A10                 2 02-DEC-98
ZKGQG           2J9785                8 HIBB         AB7A10                 2 02-DEC-98
ZKGQG           2J9785                4 HIBB         AB7A10                 2 02-DEC-98
ZKGQG           2J9785               10 HIBB         AB7A10                 2 02-DEC-98
ZKGQG           2J9785                4 HIBB         AB7A10                 2 02-DEC-98
ZKGQG           2J9785               12 HIBB         AB7A10                 2 02-DEC-98
...

Since I am using Oracle Database 11.2.0.2, I can take advantage of analytic functions to further simplify the SQL statement:

SELECT
  IT.CUST_ORDER_ID,
  IT.PART_ID,
  IT.QTY,
  IT.WAREHOUSE_ID,
  IT.LOCATION_ID,
  P.QTY_ON_HAND,
  MAX(IT.TRANSACTION_DATE) OVER (PARTITION BY IT.PART_ID) AS LAST_TRANSACTION_DATE
FROM
  INVENTORY_TRANS IT,
  PART P
WHERE
  IT.CUST_ORDER_ID IS NOT NULL
  AND IT.TYPE='O'
  AND IT.CLASS='I'
  AND IT.PART_ID=P.ID
  AND P.QTY_ON_HAND>0
ORDER BY
  IT.PART_ID,
  IT.CUST_ORDER_ID,
  IT.TRANSACTION_DATE; 

CUST_ORDER_ID   PART_ID             QTY WAREHOUSE_ID LOCATION_ID  QTY_ON_HAND LAST_TRAN
--------------- ------------ ---------- ------------ ------------ ----------- ---------
ZQHZG           2J-2908               1 FIB          AB7A10                30 21-JUN-10
ZQHZZ           2J-2909               1 FIB          AB7A10                 1 21-JUN-10
ZIHKR           2J0836               48 HIBB         A77Y3               1092 23-NOV-09
ZQKZH           2J0836              111 HIBB         A77Y3               1092 23-NOV-09
ZQIHZ           2J0836               72 HIBB         A77Y3               1092 23-NOV-09
ZKOHI           2J4120                2 PIPABTONA    AB7A10                 2 16-NOV-95
ZKOHQ           2J4129                2 PIPABTONA    AB7A10                 2 16-NOV-95
ZKGQG           2J9785                9 HIBB         AB7A10                 2 02-DEC-98
ZKGQG           2J9785                1 HIBB         AB7A10                 2 02-DEC-98
ZKGQG           2J9785                7 HIBB         AB7A10                 2 02-DEC-98
ZKGQG           2J9785                2 HIBB         AB7A10                 2 02-DEC-98
ZKGQG           2J9785                6 HIBB         AB7A10                 2 02-DEC-98
ZKGQG           2J9785                4 HIBB         AB7A10                 2 02-DEC-98
ZKGQG           2J9785               10 HIBB         AB7A10                 2 02-DEC-98
ZKGQG           2J9785                8 HIBB         AB7A10                 2 02-DEC-98
ZKGQG           2J9785                4 HIBB         AB7A10                 2 02-DEC-98
ZKGQG           2J9785               10 HIBB         AB7A10                 2 02-DEC-98
ZKGQG           2J9785                4 HIBB         AB7A10                 2 02-DEC-98
ZKGQG           2J9785               12 HIBB         AB7A10                 2 02-DEC-98
ZKGQG           2J9785                2 HIBB         AB7A10                 2 02-DEC-98
ZKGQG           2J9785               11 HIBB         AB7A10                 2 02-DEC-98
...

—–

Let’s assume that the OP’s intended result changes – the OP just wants to know which PART_IDs have not had any transactions in the last 89 day.  So, the OP is interested in those PART_IDs with a last transaction that was either 90 or more days ago, or there has never been a transaction for the PART_ID.  Oh, scrap… start over.

First, we will identify the list of PART_IDs that have had transactions in the last 89 days (CLASS values of ‘A’ are adjustments, not true inventory transactions, so rows with those CLASS values will be excluded).  Once we have that list, we can simply subtract the list from the entire list of PART_IDs:

SELECT DISTINCT
  PART_ID
FROM
  INVENTORY_TRANS
WHERE
  TRANSACTION_DATE > TRUNC(SYSDATE-90)
  AND PART_ID IS NOT NULL
  AND CLASS<>'A'; 

PART_ID
---------
2J-2908
9F4810
867-8649
90772357L
90772341L
90772297L
8475047
8145432
90772326L
9340730
90772357
90772347
782-9484
...

The OP is interested in seeing the actual warehouse location containing the PART_IDs, so we need to use the PART_LOCATION table, not the PART table as we did before.  We then need to create a left outer join between the above list and the PART_LOCATION table – essentially, we will find cases where there is a row in the PART_LOCATION table without a corresponding part ID in the above list.  ANSI syntax will be used here for the outer join, because it is still unknown whether the OP is running an Oracle Database backend or a SQL Server backend:

SELECT
  PL.PART_ID,
  PL.WAREHOUSE_ID,
  PL.LOCATION_ID,
  IT.PART_ID,
  PL.QTY
FROM
  PART_LOCATION PL
LEFT OUTER JOIN
  (SELECT DISTINCT
     PART_ID
   FROM
     INVENTORY_TRANS
   WHERE
     TRANSACTION_DATE > TRUNC(SYSDATE-90)
     AND PART_ID IS NOT NULL
     AND CLASS<>'A') IT
ON
  PL.PART_ID=IT.PART_ID
  AND IT.PART_ID IS NULL
WHERE
  PL.QTY<>0
ORDER BY
  PL.PART_ID,
  PL.WAREHOUSE_ID,
  PL.LOCATION_ID; 

PART_ID                WAREHOUSE_ID LOCATION_ID  PART_ID             QTY
---------------------- ------------ ------------ ------------ ----------
#2                     HIBB         AB7A10                             4
#46954                 HIBB         TOOL CRAB                         15
#4HWG LHRK GROUND WIRK HIBB         TOOL CRAB                        250
#4THHN                 HIBB         AB7A10                          2460
#5                     HIBB         AB7A10                             3
#6                     HIBB         TOOL CRAB                          4
#649                   HIBB         TOOL CRAB                          2
#655                   HIBB         TOOL CRAB                          4
#709                   HIBB         TOOL CRAB                          2
#889                   HIBB         TOOL CRAB                          1
#89 KJTKNSION          HIBB         TOOL CRAB                          3
#90                    HIBB         AB7A10                             4
#92 KJTKNSION          HIBB         TOOL CRAB                          2
*G-WM370H-MT           HIBB         AB7A10                             2
.235-H522              BURN         AB7A10                            .5
.235RD-9K0045          BURN         AB7A10                            .7
.260RDJ.963-HSTMH57    BURN         AB7A10                        2.5625
.35-H596-845           PIPABTONA    AB7A10                         4.039
...

In the above, notice that there is a seeminly odd ON clause: “PL.PART_ID=IT.PART_ID  AND IT.PART_ID IS NULL” – that simply means that when resolving the left outer join, a value must be found on the left side, but a corresponding value should not be found on the right side of the join.  This is the “minus” syntax that I selected to use here (the SQL MINUS syntax could not be used because the WAREHOUSE_ID, LOCATION_ID, and QTY column values returned by the query of the INVENTORY_TRANS table would have to exactly match the values found in the PART_LOCATION table… and that is more difficult to accomplish).

If I was certain that the OP was running with an Oracle Database backend, I would have suggested the following syntax, rather than the ANSI syntax as shown above:

SELECT
  PL.PART_ID,
  PL.WAREHOUSE_ID,
  PL.LOCATION_ID,
  IT.PART_ID,
  PL.QTY
FROM
  PART_LOCATION PL,
  (SELECT DISTINCT
     PART_ID
   FROM
     INVENTORY_TRANS
   WHERE
     TRANSACTION_DATE > TRUNC(SYSDATE-90)
     AND PART_ID IS NOT NULL
     AND CLASS<>'A') IT
WHERE
  PL.PART_ID=IT.PART_ID(+)
  AND IT.PART_ID IS NULL
  AND PL.QTY<>0
ORDER BY
  PL.PART_ID,
  PL.WAREHOUSE_ID,
  PL.LOCATION_ID; 

Why the NULL values in the fourth column (the second PART_ID column) of the output?  That NULL simply means that the query is working exactly as intended.  That fourth column would be showing only those PART_IDs that have had transactions in the last 89 days (and NULL if no transactions in the last 89 days) if the restriction “AND IT.PART_ID IS NULL” was NOT included in the ON clause – but such rows should not be included in the output if the output is to be used as intended.