A Year Older, Any Wiser? 2

30 11 2011

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

28 11 2011

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?

23 11 2011

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?

21 11 2011

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?

16 11 2011

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)

7 11 2011

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

3 11 2011

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?








Follow

Get every new post delivered to your Inbox.

Join 139 other followers