SQL – Filling in Gaps in the Source Data

7 12 2009

December 7, 2009

Some time ago the following question was asked in the comp.databases.oracle.server Usenet group: http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/2244cd033b534dae

Say we have a table with date/time related data

create table hits
(date_time              date,
 ip_address             varchar2(240));

And our data look like this:

date_time               ip_address
---------       ----------
31-oct-2008 13:08       192.168.0.1
31-oct-2008 13:08       192.168.0.1
31-oct-2008 13:11       192.168.0.1
31-oct-2008 13:12       192.168.0.1
31-oct-2008 13:15       192.168.0.1
31-oct-2008 13:15       192.168.0.1

I want to produce a report that shows the number of hits per minute but
include the “gaps” where there is no data recorded.
So I would like to see:

date_time               hits
---------       ----
31-oct-2008 13:08       2
31-oct-2008 13:09       0
31-oct-2008 13:10       0
31-oct-2008 13:11       1
31-oct-2008 13:12       1
31-oct-2008 13:13       0
31-oct-2008 13:14       0
31-oct-2008 13:15       2

Is it possible without outer-joining to another table with “time” data in it?

I was previously pointed to http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/analysis.htm which relates to data densification – but this method involves getting the “blanks” from another table.

You need a way to generate rows with the missing time elements, for instance by creating an inline view with a simple counter:

SELECT
  LEVEL COUNTER
FROM
  DUAL
CONNECT BY
  LEVEL<=1000;

 

   COUNTER
==========
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
...

With a slight modification to the above:

SELECT
  DT.DATE_TIME
FROM
  (SELECT
    TO_DATE('31-OCT-2008','DD-MON-YYYY') + (LEVEL/1440) DATE_TIME
  FROM
    DUAL
  CONNECT BY
    LEVEL<=1000) DT;

 

====================
31-OCT-2008 00:01:00
31-OCT-2008 00:02:00
31-OCT-2008 00:03:00
31-OCT-2008 00:04:00
31-OCT-2008 00:05:00
31-OCT-2008 00:06:00
31-OCT-2008 00:07:00
31-OCT-2008 00:08:00
31-OCT-2008 00:09:00
31-OCT-2008 00:10:00

Once the rows with the missing time elements are available, you could outer join your table to this inline view, something like this:

SELECT
  DT.DATE_TIME,
  NVL(COUNT(*),0) HIT_COUNT
FROM
  (SELECT
    TO_DATE('31-OCT-2008','DD-MON-YYYY') + (LEVEL/1440) DATE_TIME
  FROM
    DUAL
  CONNECT BY
    LEVEL<=1000) DT,
  HITS
WHERE
  DT.DATE_TIME=HITS.DATE_TIME(+)
GROUP BY
  DT.DATE_TIME;

There are of course more than 1000 minutes in a day (right around 1440), so some adjustment will be necessary.





Enqueue Experimentations

6 12 2009

December 6, 2009

Over the years I have participated in several discussions about enqueue waits.  Below are some of those discussions.

http://forums.oracle.com/forums/thread.jspa?threadID=638246
ENQ: TX – ROW LOCK CONTENTION – this is a row level lock caused by a possible primary key or unique index violation if the first session commits its current transaction. A second session attempting to insert the value that will result in the primary key violation will sit in this wait event until the first session commits or rolls back.

Problems with initrans would show up as ENQ: TX – ALLOCATE ITL ENTRY waits in 10g/11g. But, such problems should be rare with tablespaces using ASSM.

After fixing the formatting of the top 5 wait events (total duration unknown):

EVENT                        TOTAL_WAITS  TIME_WAITED   AVG_MS PERCENT
----------------------------------------------------------------------
CPU                                         94,623.39             48
enq: TX - row lock contention     12,531    36,607.28  2921.34    18
control file parallel write    1,300,731    30,880.79    23.74    16
log file parallel write        1,510,503    12,640.80     8.37     6
log file sync                  1,072,553     9,680.07     9.03     5

12,531 * 3 second time out = 37,593 seconds = 10.44 hours.

What if the reason for the 3 second average wait time is due to a timeout. I performed a little experiment… I changed a row in a test table and then made a pot of coffee.

In session 1:

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

INSERT INTO T1
SELECT
  ROWNUM,
  ROWNUM*10
FROM
  DUAL
CONNECT BY
  LEVEL<=1000000;

COMMIT;

I now have a test table with 1,000,000 rows. I start monitoring the changes in the wait events roughly every 60 seconds, and V$SESSION_WAIT and V$LOCK roughly 4 times per second.

Back in session 1:

UPDATE
  T1
SET
  C1=-C1
WHERE
  C1<=100;

I have now modified the first 100 rows that were inserted into the table, time to make the pot of coffee.

In session 2, I try to insert a row with a primary key value of -10:

INSERT INTO T1 VALUES (
  -10,
  10);

Session 2 hangs.

If I take the third 60 second snap of the system wide wait events as the zero point, and the 11th snap as the end point. There were 149 waits on ENQ: TX – ROW LOCK CONTENTION, 148 time outs, 446.62 seconds of total time in the wait event, with an average wait time of 2.997450 seconds.

Rolling down to the session level wait events, SID 208 (my session 2) had 149 waits on ENQ: TX – ROW LOCK CONTENTION, for a total time of 446.61 seconds with an average wait time of 2.997383 seconds. All of the 149 waits and the wait time was in this one session that was locked up for the full duration of this time period because session 1 was making a pot of coffee.

Rolling down to V$SESSION_WAIT (sampled roughly 4 times per second): At the start of the third time interval, SID 208 has been in the ENQ: TX – ROW LOCK CONTENTION wait event for 39 seconds and is actively waiting trying to execute SQL with a hash value of 1001532423, the wait object is -1, wait file is 0, wait block is 0, wait row is 0, P1 is 1415053316, P2 is 196646, P3 is 4754.
At the end of the 11th time interval: , SID 208 has been in the ENQ: TX – ROW LOCK CONTENTION wait event for 483 seconds and is actively waiting trying to execute SQL with a hash value of 1001532423, the wait object is -1, wait file is 0, wait block is 0, wait row is 0, P1 is 1415053316, P2 is 196646, P3 is 4754.

Rolling down to V$LOCK (sampled roughly 4 times per second): I see that SID 214 (session 1) is blocking SID 208 (session 2). SID 214 has a TX lock in mode 6 with ID1 of 196646 and ID2 of 4754. SID 208 is requesting a TX lock in mode 4 with ID1 of 196646 and ID2 of 4754.

So, it seems that I need a faster coffee pot rather than an additional index on my table. It could be that the above process would have found that the application associated with SID 214 was abandoned or crashed and for some reason the lock was not released for a long period of time, a little less than 10.44 hours in your case.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

http://forums.oracle.com/forums/thread.jspa?threadID=860488
TYPE=TX, LMODE=6, REQUEST=6 = session 2 is attempting to update a row that is locked by session 1’s transaction (likely session 1 already updated the row)
TYPE=TX, LMODE=6, REQUEST=4 = session 2 is attempting to insert a row with the same primary key value or unique index value as a row just inserted by session 1
TYPE=TX, LMODE=6, REQUEST=4 = ITL Problem or bitmap index fragment problem
RBS Seg # = trunc(p2/65536) = v$transaction.xidusn
RBS Slot # = bitand( p2, 65535) = v$transaction.xidslot
P3 = v$transaction.xidsqn

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

http://forums.oracle.com/forums/thread.jspa?messageID=2613515

Let’s try a couple experiments to see if we can reproduce what you are seeing (this may not be the only possible cause):

#Basic Setup
Session 1:

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

Table created.

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

1 row created.

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

1 row created.
SELECT DISTINCT
  SID
FROM
  V$MYSTAT;

       SID
----------
       150

#Test 1 (Primary Key Violation, No Commit)
Session 2:

SELECT DISTINCT
  SID
FROM
  V$MYSTAT;

       SID
----------
       143

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

(Session 2 Hung)
In Session 1:

SELECT
  S.SID,
  S.PROGRAM,
  S.STATUS,
  SW.EVENT,
  SW.WAIT_TIME,
  SW.STATE,
  SW.SECONDS_IN_WAIT,
  S.SQL_ADDRESS,
  S.SQL_HASH_VALUE,
  S.ROW_WAIT_OBJ#,
  S.ROW_WAIT_FILE#,
  S.ROW_WAIT_BLOCK#,
  S.ROW_WAIT_ROW#,
  SW.P1 ,
  SW.P2,
  SW.P3
FROM
  V$SESSION S,
  V$SESSION_WAIT SW
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');

       SID PROGRAM       STATUS   EVENT                          WAIT_TIME STATE     SECONDS_IN_WAIT SQL_ADDR SQL_HASH_VALUE ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#         P1         P2         P3
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       143 sqlplus.exe   ACTIVE   enq: TX - row lock contention          0 WAITING               424 1CA703CC     2983910641            -1              0               0             0 1415053316     393235      11543

 

SELECT
  S.SID,
  S.PROGRAM,
  S.SQL_HASH_VALUE,
  L.REQUEST,
  L.ID1,
  L.ID2,
  L.LMODE,
  L.TYPE,
  L.BLOCK
FROM
  V$LOCK L,
  V$SESSION S
WHERE
  (L.ID1, L.ID2, L.TYPE) IN
  (SELECT
    ID1,
    ID2,
    TYPE
  FROM
    V$LOCK
  WHERE
    REQUEST > 0)
  AND L.SID=S.SID;

       SID PROGRAM     SQL_HASH_VALUE    REQUEST        ID1        ID2      LMODE TY      BLOCK
-----------------------------------------------------------------------------------------------
       143 sqlplus.exe     2983910641          4     393235      11543          0 TX          0
       150 sqlplus.exe      883681740          0     393235      11543          6 TX          1

#Test 1 missed the mark, no ROW_WAIT_OBJ#

COMMIT;

In Session 2:

ERROR at line 1:
ORA-00001: unique constraint (TESTUSER.SYS_C0014294) violated

#Test 2 (Primary Key Updated, No Commit)
In Session 1:

UPDATE
  T1
SET
  C1=C1
WHERE
  C1='TWO';

1 row updated.

In Session 2:

UPDATE
  T1
SET
  C1=C1
WHERE
  C1='TWO';

In Session 1:

SELECT
  S.SID,
  S.PROGRAM,
  S.STATUS,
  SW.EVENT,
  SW.WAIT_TIME,
  SW.STATE,
  SW.SECONDS_IN_WAIT,
  S.SQL_ADDRESS,
  S.SQL_HASH_VALUE,
  S.ROW_WAIT_OBJ#,
  S.ROW_WAIT_FILE#,
  S.ROW_WAIT_BLOCK#,
  S.ROW_WAIT_ROW#,
  SW.P1 ,
  SW.P2,
  SW.P3
FROM
  V$SESSION S,
  V$SESSION_WAIT SW
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');

       SID PROGRAM       STATUS   EVENT                          WAIT_TIME STATE     SECONDS_IN_WAIT SQL_ADDR SQL_HASH_VALUE ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#         P1         P2         P3
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       143 sqlplus.exe   ACTIVE   enq: TX - row lock contention           0 WAITING               38 222799C4     1367536044         73419              1           84362             1 1415053318     589828      11227

 

SELECT
  S.SID,
  S.PROGRAM,
  S.SQL_HASH_VALUE,
  L.REQUEST,
  L.ID1,
  L.ID2,
  L.LMODE,
  L.TYPE,
  L.BLOCK
FROM
  V$LOCK L,
  V$SESSION S
WHERE
  (L.ID1, L.ID2, L.TYPE) IN
  (SELECT
    ID1,
    ID2,
    TYPE
  FROM
    V$LOCK
  WHERE
    REQUEST > 0)
  AND L.SID=S.SID;

       SID PROGRAM     SQL_HASH_VALUE    REQUEST        ID1        ID2      LMODE TY      BLOCK
-----------------------------------------------------------------------------------------------
       143 sqlplus.exe     1367536044          6     589828      11227          0 TX          0
       150 sqlplus.exe      883681740          0     589828      11227          6 TX          1

#Test 2 missed the mark, ROW_WAIT_OBJ# exists and ROW_WAIT_ROW# exists

COMMIT;

In Session 2:

COMMIT;

#Test 3 (Unique Index Violation) 
In Session 1:

CREATE UNIQUE INDEX IND_T1 ON T1(C2);

Index created.

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

1 row created.

In Session 2:

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

(Session 2 Hung)
In Session 1:

SELECT
  S.SID,
  S.PROGRAM,
  S.STATUS,
  SW.EVENT,
  SW.WAIT_TIME,
  SW.STATE,
  SW.SECONDS_IN_WAIT,
  S.SQL_ADDRESS,
  S.SQL_HASH_VALUE,
  S.ROW_WAIT_OBJ#,
  S.ROW_WAIT_FILE#,
  S.ROW_WAIT_BLOCK#,
  S.ROW_WAIT_ROW#,
  SW.P1 ,
  SW.P2,
  SW.P3
FROM
  V$SESSION S,
  V$SESSION_WAIT SW
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');

       SID PROGRAM       STATUS   EVENT                          WAIT_TIME STATE    SECONDS_IN_WAIT SQL_ADDR SQL_HASH_VALUE ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#         P1         P2         P3
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       143 sqlplus.exe   ACTIVE   enq: TX - row lock contention          0 WAITING               41 1C8F8B60      463727853            -1              1           84362             1 1415053316     393242      11541

 

SELECT
  S.SID,
  S.PROGRAM,
  S.SQL_HASH_VALUE,
  L.REQUEST,
  L.ID1,
  L.ID2,
  L.LMODE,
  L.TYPE,
  L.BLOCK
FROM
  V$LOCK L,
  V$SESSION S
WHERE
  (L.ID1, L.ID2, L.TYPE) IN
  (SELECT
    ID1,
    ID2,
    TYPE
  FROM
    V$LOCK
  WHERE
    REQUEST > 0)
  AND L.SID=S.SID;

       SID PROGRAM     SQL_HASH_VALUE    REQUEST        ID1        ID2      LMODE TY      BLOCK
-----------------------------------------------------------------------------------------------
       143 sqlplus.exe      463727853          4     393242      11541          0 TX          0
       150 sqlplus.exe      883681740          0     393242      11541          6 TX          1

#Test 3 missed the mark, no ROW_WAIT_OBJ#
In Session 1:

COMMIT;

Commit complete.

In Session 2:

ERROR at line 1:
ORA-00001: unique constraint (TESTUSER.IND_T1) violated

#Test 4 (Unique Index Violation)
In Session 1:

UPDATE
  T1
SET
  C2=C2+1
WHERE
  C2=3;

1 row updated.

In Session 2:

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

(Session 2 Hung)
In Session 1:

SELECT
  S.SID,
  S.PROGRAM,
  S.STATUS,
  SW.EVENT,
  SW.WAIT_TIME,
  SW.STATE,
  SW.SECONDS_IN_WAIT,
  S.SQL_ADDRESS,
  S.SQL_HASH_VALUE,
  S.ROW_WAIT_OBJ#,
  S.ROW_WAIT_FILE#,
  S.ROW_WAIT_BLOCK#,
  S.ROW_WAIT_ROW#,
  SW.P1 ,
  SW.P2,
  SW.P3
FROM
  V$SESSION S,
  V$SESSION_WAIT SW
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');

       SID PROGRAM       STATUS   EVENT                          WAIT_TIME STATE    SECONDS_IN_WAIT SQL_ADDR SQL_HASH_VALUE ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#         P1         P2         P3
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       143 sqlplus.exe   ACTIVE   enq: TX - row lock contention          0 WAITING               18 1C8F8B60      463727853            -1              1           84362             1 1415053316      65560       8736

 

SELECT
  S.SID,
  S.PROGRAM,
  S.SQL_HASH_VALUE,
  L.REQUEST,
  L.ID1,
  L.ID2,
  L.LMODE,
  L.TYPE,
  L.BLOCK
FROM
  V$LOCK L,
  V$SESSION S
WHERE
  (L.ID1, L.ID2, L.TYPE) IN
  (SELECT
    ID1,
    ID2,
    TYPE
  FROM
    V$LOCK
  WHERE
    REQUEST > 0)
  AND L.SID=S.SID;

       SID PROGRAM     SQL_HASH_VALUE    REQUEST        ID1        ID2      LMODE TY      BLOCK
-----------------------------------------------------------------------------------------------
       143 sqlplus.exe      463727853          4      65560       8736          0 TX          0
       150 sqlplus.exe      883681740          0      65560       8736          6 TX          1

#Test 4 missed the mark, ROW_WAIT_OBJ# does not exist and ROW_WAIT_ROW# exists

ROLLBACK;

In Session 2:

ROLLBACK;

#Test 5 (Select For Update in Two Sessions)
In Session 1:

SELECT
  *
FROM
  T1
FOR UPDATE;

C1                 C2
---------------------
ONE                 1
TWO                 2
THREE               3

In Session 1:

SELECT
  *
FROM
  T1
FOR UPDATE;

(Session 2 Hung)
In Session 1:

SELECT
  S.SID,
  S.PROGRAM,
  S.STATUS,
  SW.EVENT,
  SW.WAIT_TIME,
  SW.STATE,
  SW.SECONDS_IN_WAIT,
  S.SQL_ADDRESS,
  S.SQL_HASH_VALUE,
  S.ROW_WAIT_OBJ#,
  S.ROW_WAIT_FILE#,
  S.ROW_WAIT_BLOCK#,
  S.ROW_WAIT_ROW#,
  SW.P1 ,
  SW.P2,
  SW.P3
FROM
  V$SESSION S,
  V$SESSION_WAIT SW
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');

       SID PROGRAM       STATUS   EVENT                          WAIT_TIME STATE    SECONDS_IN_WAIT SQL_ADDR SQL_HASH_VALUE ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#         P1         P2         P3
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       143 sqlplus.exe   ACTIVE   enq: TX - row lock contention          0 WAITING               18 1C911EE4     3199333328         73419              1           84362             0 1415053318     327707      11953

 

SELECT
  S.SID,
  S.PROGRAM,
  S.SQL_HASH_VALUE,
  L.REQUEST,
  L.ID1,
  L.ID2,
  L.LMODE,
  L.TYPE,
  L.BLOCK
FROM
  V$LOCK L,
  V$SESSION S
WHERE
  (L.ID1, L.ID2, L.TYPE) IN
  (SELECT
    ID1,
    ID2,
    TYPE
  FROM
    V$LOCK
  WHERE
    REQUEST > 0)
  AND L.SID=S.SID;

       SID PROGRAM     SQL_HASH_VALUE    REQUEST        ID1        ID2      LMODE TY      BLOCK
-----------------------------------------------------------------------------------------------
       143 sqlplus.exe     3199333328          6     327707      11953          0 TX          0
       150 sqlplus.exe      883681740          0     327707      11953          6 TX          1

#Test 5 ROW_WAIT_OBJ# exist and ROW_WAIT_ROW# does not exists, lock request mode is 6 and the other session is holding the lock in mode 6.  It looks like one possibility was found.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

http://forums.oracle.com/forums/thread.jspa?messageID=3188695
Based on a calculation it appears that the Statspack duration is about 20 minutes (this is likely reasonable). I did not spend a lot of time analyzing the information that you posted, so a quick summary of what I see that looks interesting:

                                                                  Avg
                                                     Total Wait   wait    Waits
Event                               Waits   Timeouts  Time (s)    (ms)     /txn
---------------------------- ------------ ---------- ---------- ------ --------
enqueue                                83         58        208   2509      0.1
resmgr:waiting in end wait            265          0         25     94      0.4
log file parallel write             1,939          0         22     11      3.2
resmgr:waiting in check                 8          0         17   2112      0.0

 

In the above, notice that there are 58 time outs for the enqueue. The timeout period for an enqueue is 3 seconds, which means that you potentially had a single wait that lasted 58*3 (+ up to 2.99) seconds, which totals 174 (+ up to 2.99) seconds. To make the calculation simple, assume that this was a single wait lasting exactly 174 seconds. The remaining 47 seconds of the wait divided by 25 (83-58) means that the remaining average wait time is 1.88 seconds. With Oracle 9.0.1 it is more difficult to see how much time was spent in each of the different types of enqueues compared to Oracle 10g and above.

Looking at the enqueue information that you posted:

                                                       Avg Wt        Wait
Eq     Requests    Succ Gets Failed Gets       Waits  Time (ms)    Time (s)
-- ------------ ------------ ----------- ----------- ----------- ------------
TX          569          568           0          30    7,356.07          221

 

It appears that all of the enqueue activity is of the type TX, while are row level enqueues – usually meaning that someone updated a row, and there was a pause between the time that the update was performed and a commit or rollback was executed. There are multiple causes for TX enqueues which may not be found with a Statspack report. From my notes (possibly based on the Oracle Performance Tuning Guide):
* TX enqueues are acquired exclusive when a transaction initiates its first change and held until the transaction COMMITs or ROLLBACK.
* Waits for TX in mode 6 occurs when a session is waiting for a row level lock that is already held by another session. The wait event for mode 6 TX enqueues is ‘enq: TX – row lock contention’ on 10g and above.
* Waits for TX in mode 4 can occur when a session wants to lock a row in the block, but one or more other sessions have rows locked in the same block and there is no free Interested Transaction LIST slot in the block. Oracle will usually dynamically add another ITL slot if there is sufficient space in the block. To fix this problem, increase INITRANS or MAXTRANS for the table. This type of TX enqueue corresponds to the wait event ‘enq: TX – allocate ITL’ on 10g and above.
* Waits for TX in mode 4 can also occur if the session is waiting for potential duplicates in a UNIQUE index, which would occur if a second session inserts a value before the first tries to insert the same value, and the second session has not yet committed. This type of enqueue corresponds to the wait event: ‘enq: TX – row lock contention’ on 10g and above.
* ‘Waits for TX in mode 4 can also occur when a transaction is inserting a row into an index and must wait for the end of an index block split being done by another transaction. This type of TX enqueue wait corresponds to the wait event ‘enq: TX – index’ on 10g and above.
You might want to look at the SQL statements sorted by CPU utilization based on the above description.

Taking a look at a couple SQL statements that you posted:

                                                     CPU      Elapsd
  Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------                                                          
      3,301,423           91       36,279.4   67.5     0.01      0.01 4086952936
UPDATE IPAO.EMP_MAST SET OSCAL_CODE=:b1 WHERE :b1 < 600

        902,040           30       30,068.0   18.5     0.00      0.00 2333288512
UPDATE IPAO.MON_ACT SET CHEQUE_NO=:b1,CHEQUE_DT=:b2,CHEQUE_AMT=:b3 WHERE V_NO = :b4  AND V_DATE = :b5

 

The first of the two looks interesting. I am a little surprised by the small number for the CPU utilization considering the number of buffer gets. The WHERE clause “WHERE :b1 < 600″ seems a little unusual.





Deadlock Experimentations

6 12 2009

December 6, 2009

Did you know that it is possible for multiple sessions to incorrectly trigger a deadlock on Oracle 11.1.0.6, 11.1.0.7, and 11.2.0.1 by executing a sequence of commands, when the same sequence of commands does not trigger a deadlock on 10.2.0.4?  See chapter 8 “Understanding Performance Optimization Methods” of the “Expert Oracle Practices: Oracle Database Administration from the Oak Table” book for the test case.

Over the years I have posted several test cases that will intentionally trigger a deadlock to permit others to locate a matching deadlock pattern to help identify a cause of their deadlock.  Below are a couple of those test cases.

http://forums.oracle.com/forums/thread.jspa?threadID=689654

In the demo, the sessions are holding SX (Row exclusive – write access to a compound resource) locks due to a select for update, rather than S (Share – One of more sessions are reading) locks, but with a bit more experimentation, I am sure that it would be possible to create a demo where both the sessions are holding S locks.

The demo:

/* First, the creation of the tables with a foreign key index */
CREATE TABLE T1(C1 NUMBER(10) PRIMARY KEY);

INSERT INTO T1 VALUES(1);
INSERT INTO T1 VALUES(2);
INSERT INTO T1 VALUES(3);
INSERT INTO T1 VALUES(4);

COMMIT;

CREATE TABLE T2(
  C1 NUMBER(10) PRIMARY KEY,
  C2 NUMBER(10),
  CONSTRAINT FK_T1_C1 FOREIGN KEY(C2) REFERENCES T1(C1) ENABLE);

CREATE INDEX IND_T2_C2 ON T2(C2);

INSERT INTO T2 VALUES (1,1);
INSERT INTO T2 VALUES (2,2);
INSERT INTO T2 VALUES (3,3);
INSERT INTO T2 VALUES (4,4);

COMMIT;

 

/* Test 1 - No problems */
/* SESSION 1 */
SELECT
  *
FROM
  T2
WHERE
  C2=1
FOR UPDATE;

        C1         C2
---------------------
         1          1

 

/* SESSION 2 */
SELECT
  *
FROM
  T2
WHERE
  C2=2
FOR UPDATE;

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

 

UPDATE
  T1
SET
  C1=2
WHERE
  C1=2;

1 row updated.

 

/* SESSION 1 */
UPDATE
  T1
SET
  C1=1
WHERE
  C1=1;

1 row updated.

ROLLBACK;

 

/* SESSION 2 */
ROLLBACK;

/* Test 2 – What happens when we repeat the test without the foreign key index */

DROP INDEX IND_T2_C2;

/* SESSION 2 */
SELECT
  *
FROM
  T2
WHERE
  C2=2
FOR UPDATE;

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

 

/* SESSION 1 */
SELECT
  *
FROM
  T2
WHERE
  C2=1
FOR UPDATE;

        C1         C2
---------------------
         1          1

UPDATE
  T1
SET
  C1=1
WHERE
  C1=1;
/* HANGS */

/* SESSION 2 */
UPDATE
  T1
SET
  C1=2
WHERE
  C1=2;
/* HANGS */

 

/* SESSION 1 */
  T1
  *
ERROR at line 2:
ORA-00060: deadlock detected while waiting for resource

 

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TM-0000d63f-00000000        17     208    SX   SSX       18     210    SX   SSX
TM-0000d63f-00000000        18     210    SX   SSX       17     208    SX   SSX
session 208: DID 0001-0011-000000CE session 210: DID 0001-0012-000000D7
session 210: DID 0001-0012-000000D7 session 208: DID 0001-0011-000000CE
Rows waited on:
Session 210: obj - rowid = 000027EB - AAACfrAABAAAG/BAAA
  (dictionary objn - 10219, file - 1, block - 28609, slot - 0)

Your deadlock graph showed that both sessions were trying to obtain a TM lock in mode 5 (SSX), but the other sessions were already holding locks in mode 4 (S), and neither session was able to continue.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

http://forums.oracle.com/forums/thread.jspa?threadID=689654

If the deadlock points to a type TX, then the problem is probably not related to foreign keys indexing problems.

Another test setup to produce a deadlock with lock type TX:

/* The setup (assumes that the tables from the previous test setup still exist) */
DROP TABLE T2;
DROP TABLE T1;
DROP TABLE T3;

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

INSERT INTO T1 VALUES(1,NULL);
INSERT INTO T1 VALUES(2,NULL);
INSERT INTO T1 VALUES(3,NULL);
INSERT INTO T1 VALUES(4,NULL);

COMMIT;

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

INSERT INTO T2 VALUES(1,NULL);
INSERT INTO T2 VALUES(2,NULL);
INSERT INTO T2 VALUES(3,NULL);
INSERT INTO T2 VALUES(4,NULL);

COMMIT;

CREATE TABLE T3(TRANSACTION_ID NUMBER(10) PRIMARY KEY);

INSERT INTO T3 VALUES(1);
INSERT INTO T3 VALUES(2);
INSERT INTO T3 VALUES(3);
INSERT INTO T3 VALUES(4);

COMMIT;

/* Now we have two data tables and a third table, which could be interesting if there were 3 sessions involved */

/* Test 3 - session 1 updates a row, waits, session 2 updates 2 rows and hangs */
/* SESSION 1 */
UPDATE
  T1
SET
  C2=C1
WHERE
  C1=1;

 

/* SESSION 2 */
UPDATE
  T2
SET
  C2=C1
WHERE
  C1=1;

UPDATE
  T1
SET
  C2=C1
WHERE
  C1=1;
/* HANGS */

/* SESSION 1 */
UPDATE
  T2
SET
  C2=C1
WHERE
  C1=1;
/* HANGS */

/* SESSION 2 */
  T1
  *
ERROR at line 2:
ORA-00060: deadlock detected while waiting for resource

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00010003-0000238f        18     208     X             17     210           X
TX-00090005-00002383        17     210     X             18     208           X
session 208: DID 0001-0012-000000D9 session 210: DID 0001-0011-000000D0
session 210: DID 0001-0011-000000D0 session 208: DID 0001-0012-000000D9
Rows waited on:
Session 210: obj - rowid = 0000D644 - AAANZEAAEAAFvr2AAA
  (dictionary objn - 54852, file - 4, block - 1506038, slot - 0)
Session 208: obj - rowid = 0000D642 - AAANZCAAEAAFvrmAAA
  (dictionary objn - 54850, file - 4, block - 1506022, slot - 0)

 

/* SESSION 2 */
ROLLBACK;

 

/* SESSION 1 */
ROLLBACK;

 
/* Test 4 – Transaction table that does not use a sequence contributes to the problem */

/* SESSION 1 */
UPDATE
  T1
SET
  C2=C1
WHERE
  C1=1;

INSERT INTO
  T3
SELECT
  MAX(TRANSACTION_ID)+1
FROM
  T3;

 

/* SESSION 2 */
UPDATE
  T2
SET
  C2=C1+1
WHERE
  C1=1;

INSERT INTO
  T3
SELECT
  MAX(TRANSACTION_ID)+1
FROM
  T3;
/* HANGS */

 

/* SESSION 1 */
DELETE FROM T2;
/* HANGS */

 

/* SESSION 2 */
  T3
  *
ERROR at line 2:
ORA-00060: deadlock detected while waiting for resource

 

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00060029-00002376        18     208     X             17     210           X
TX-00090016-00002386        17     210     X             18     208           S
session 208: DID 0001-0012-000000D9 session 210: DID 0001-0011-000000D0
session 210: DID 0001-0011-000000D0 session 208: DID 0001-0012-000000D9
Rows waited on:
Session 210: obj - rowid = 0000D644 - AAANZEAAEAAFvr2AAA
  (dictionary objn - 54852, file - 4, block - 1506038, slot - 0)
Session 208: obj - rowid = 0000D642 - AAANZCAAEAAFvrmAAA
  (dictionary objn - 54850, file - 4, block - 1506022, slot - 0)

Test 5 (if setup) might have used 3 sessions, the first session would update a row in T1 and then insert a row into T3. The second session would update a row in T2 and attempt to insert a row into T3 (session 2 would hang). Session 3 would update a different row in T2, and then attempt to insert a row into T3 (session 3 would hang). Session 1 would see that the row originally updated by session 2 was not updated, and attempt to update that row (session 1 would hang). The Deadlock graph?

Deadlocks almost always point to application design problems (or user design problems when the user attempts to start the same batch process twice).

One thing that you do not know is what the two sessions were doing before they executed the insert into T2 statement. The first set of numbers in the deadlock graph is the hex value of an OBJECT_ID. If you execute the following query:

SELECT
  OWNER,
  OBJECT_NAME
FROM
  DBA_OBJECTS
WHERE
  OBJECT_ID=241021;

Does it show the table T2, T1, T3, or some other table?

You might find the following Metalink documents helpful:
Note:15476.1 FAQ about Detecting and Resolving Locking Conflicts
Note:198828.1 Analysing locking issues with LOGMINER
Note:102925.1 Tracing sessions: waiting on an enqueue

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

http://forums.oracle.com/forums/thread.jspa?threadID=867295
The setup that I was trying to describe was one in which the TM lock could not be obtained due to another session already holding a TX lock on the child, something like this:

CREATE TABLE T1(C1 NUMBER(10) PRIMARY KEY);

INSERT INTO T1 VALUES(1);
INSERT INTO T1 VALUES(2);
INSERT INTO T1 VALUES(3);
INSERT INTO T1 VALUES(4);

COMMIT;

CREATE TABLE T2(
  C1 NUMBER(10) PRIMARY KEY,
  C2 NUMBER(10),
  CONSTRAINT FK_T1_C1 FOREIGN KEY(C2) REFERENCES T1(C1) ENABLE);

INSERT INTO T2 VALUES (1,1);
INSERT INTO T2 VALUES (2,2);
INSERT INTO T2 VALUES (3,3);
INSERT INTO T2 VALUES (4,4);

COMMIT;

In session 1:
SELECT
  *
FROM
  T2
WHERE
  C2=1
FOR UPDATE;

In session 2:
INSERT INTO T1 VALUES(15);

UPDATE
  T1
SET
  C1=15
WHERE
  C1=15;

(Session 2 hangs trying to acquire a TM lock on the child table)

In session 1:
INSERT INTO T1 VALUES(15);

(Session 1 and 2 deadlock)

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-000a0025-000038bf        17     213     X             18     210           S
TM-0000d79a-00000000        18     210    SX             17     213    SS     S
session 213: DID 0001-0011-000003C2 session 210: DID 0001-0012-000002F1
session 210: DID 0001-0012-000002F1 session 213: DID 0001-0011-000003C2
Rows waited on:
Session 210: no row
Session 213: no row

 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

In session 1

CREATE TABLE T1(
  MY_ID VARCHAR2(30),
  MY_DATE DATE,
  CUSTOMER_ID VARCHAR2(15),
  PRIMARY KEY (MY_ID));

INSERT INTO T1 VALUES(
  '001',
  TO_DATE('01-JAN-2006','DD-MON-YYYY'),
  'C001');

INSERT INTO T1 VALUES(
  '002',
  TO_DATE('01-JAN-2006','DD-MON-YYYY'),
  'C002');

INSERT INTO T1 VALUES(
  '003',
  TO_DATE('01-JAN-2006','DD-MON-YYYY'),
  'C003');

INSERT INTO T1 VALUES(
  '004',
  TO_DATE('01-JAN-2006','DD-MON-YYYY'),
  'C001');

COMMIT;

INSERT INTO T1 VALUES(
  '005',
  TO_DATE('01-JAN-2007','DD-MON-YYYY'),
  'C003');

INSERT INTO T1 VALUES(
  '006',
  TO_DATE('01-JAN-2007','DD-MON-YYYY'),
  'C001');

In session 2:

INSERT INTO T1 VALUES(
  '007',
  TO_DATE('01-FEB-2007','DD-MON-YYYY'),
  'C003');

INSERT INTO T1 VALUES(
  '008',
  TO_DATE('01-FEB-2007','DD-MON-YYYY'),
  'C001');

UPDATE
  T1
SET
  MY_DATE=MY_DATE+1
WHERE
  MY_ID='001';

UPDATE
  T1
SET
  MY_DATE=MY_DATE+1
WHERE
  MY_ID='002';

UPDATE
  T1
SET
  MY_DATE=MY_DATE+1
WHERE
  MY_ID='003';

INSERT INTO T1 VALUES(
  '005',
  TO_DATE('01-FEB-2007','DD-MON-YYYY'),
  'C001');

(The above will be a primary key violation if session 1 commits, so session 2 hangs)

In session 2’s 10046 trace file:

------------
*** 2008-02-12 10:09:27.984
WAIT #2: nam='enq: TX - row lock contention' ela= 3000118 name|mode=1415053316 usn<<16 | slot=131104 sequence=8831 obj#=-1 tim=1749514958
WAIT #2: nam='enq: TX - row lock contention' ela= 3000116 name|mode=1415053316 usn<<16 | slot=131104 sequence=8831 obj#=-1 tim=1752515272
WAIT #2: nam='enq: TX - row lock contention' ela= 3000157 name|mode=1415053316 usn<<16 | slot=131104 sequence=8831 obj#=-1 tim=1755515569
WAIT #2: nam='enq: TX - row lock contention' ela= 3000084 name|mode=1415053316 usn<<16 | slot=131104 sequence=8831 obj#=-1 tim=1758515803
*** 2008-02-12 10:09:39.983
WAIT #2: nam='enq: TX - row lock contention' ela= 3000079 name|mode=1415053316 usn<<16 | slot=131104 sequence=8831 obj#=-1 tim=1761516009
WAIT #2: nam='enq: TX - row lock contention' ela= 2999987 name|mode=1415053316 usn<<16 | slot=131104 sequence=8831 obj#=-1 tim=1764516178
WAIT #2: nam='enq: TX - row lock contention' ela= 2999994 name|mode=1415053316 usn<<16 | slot=131104 sequence=8831 obj#=-1 tim=1767516313
WAIT #2: nam='enq: TX - row lock contention' ela= 3000015 name|mode=1415053316 usn<<16 | slot=131104 sequence=8831 obj#=-1 tim=1770516482
*** 2008-02-12 10:09:51.983
WAIT #2: nam='enq: TX - row lock contention' ela= 3000084 name|mode=1415053316 usn<<16 | slot=131104 sequence=8831 obj#=-1 tim=1773516699
WAIT #2: nam='enq: TX - row lock contention' ela= 3000063 name|mode=1415053316 usn<<16 | slot=131104 sequence=8831 obj#=-1 tim=1776516940
------------

In session 1:

SELECT
  *
FROM
  T1
FOR UPDATE;

Session 1 hangs

Session 2 immediately displays:

INSERT INTO TESTUSER.T1 VALUES(
                    *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

In session 2’s 10046 trace file:

DEADLOCK DETECTED
[Transaction Deadlock]
Current SQL statement for this session:
INSERT INTO TESTUSER.T1 VALUES(
  '005',
  TO_DATE('01-FEB-2007','DD-MON-YYYY'),
  'C001')
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-0006002d-000023ef        25     201     X             22     204           X
TX-00020020-0000227f        22     204     X             25     201           S
session 201: DID 0001-0019-000001D2 session 204: DID 0001-0016-000000FF
session 204: DID 0001-0016-000000FF session 201: DID 0001-0019-000001D2
Rows waited on:
Session 204: obj - rowid = 0000829B - AAAIKbAAEAAEBROAAA

In session 2:

SELECT
  *
FROM
  T1
WHERE
  ROWID='AAAIKbAAEAAEBROAAA';

MY_ID                          MY_DATE   CUSTOMER_ID
------------------------------ --------- -----------
001                            02-JAN-06 C001

Session 2’s update of the row with MY_ID =’001′ was pinpointed in the deadlock.

In session 1’s 10046 trace file:

PARSING IN CURSOR #2 len=31 dep=0 uid=288 oct=3 lid=288 tim=1776466041 hv=3199333328 ad='46baac58'
SELECT
  *
FROM
  T1
FOR UPDATE
END OF STMT
PARSE #2:c=0,e=93402,p=0,cr=17,cu=0,mis=1,r=0,dep=0,og=1,tim=1776466031
BINDS #2:
WAIT #2: nam='enq: TX - row lock contention' ela= 2997875 name|mode=1415053318 usn<<16 | slot=393261 sequence=9199 obj#=33435 tim=1779470295
WAIT #2: nam='enq: TX - row lock contention' ela= 3000109 name|mode=1415053318 usn<<16 | slot=393261 sequence=9199 obj#=33435 tim=1782470542
WAIT #2: nam='enq: TX - row lock contention' ela= 3000151 name|mode=1415053318 usn<<16 | slot=393261 sequence=9199 obj#=33435 tim=1785470824
*** 2008-02-12 10:10:06.936
WAIT #2: nam='enq: TX - row lock contention' ela= 3000127 name|mode=1415053318 usn<<16 | slot=393261 sequence=9199 obj#=33435 tim=1788471107
WAIT #2: nam='enq: TX - row lock contention' ela= 2999987 name|mode=1415053318 usn<<16 | slot=393261 sequence=9199 obj#=33435 tim=1791471260
WAIT #2: nam='enq: TX - row lock contention' ela= 3000059 name|mode=1415053318 usn<<16 | slot=393261 sequence=9199 obj#=33435 tim=1794471450
WAIT #2: nam='enq: TX - row lock contention' ela= 3000020 name|mode=1415053318 usn<<16 | slot=393261 sequence=9199 obj#=33435 tim=1797471623
*** 2008-02-12 10:10:18.936
WAIT #2: nam='enq: TX - row lock contention' ela= 3000059 name|mode=1415053318 usn<<16 | slot=393261 sequence=9199 obj#=33435 tim=1800471821
WAIT #2: nam='enq: TX - row lock contention' ela= 2999913 name|mode=1415053318 usn<<16 | slot=393261 sequence=9199 obj#=33435 tim=1803471915
WAIT #2: nam='enq: TX - row lock contention' ela= 3000109 name|mode=1415053318 usn<<16 | slot=393261 sequence=9199 obj#=33435 tim=1806472157
WAIT #2: nam='enq: TX - row lock contention' ela= 3000086 name|mode=1415053318 usn<<16 | slot=393261 sequence=9199 obj#=33435 tim=1809472374

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Experimenting with a 4 session deadlock:

Session 1:
CREATE TABLE T1(C1 NUMBER PRIMARY KEY, C2 NUMBER CONSTRAINT CON_T1 UNIQUE);
CREATE TABLE T2(C1 NUMBER PRIMARY KEY, C2 NUMBER CONSTRAINT CON_T2 UNIQUE);
CREATE TABLE T3(C1 NUMBER PRIMARY KEY, C2 NUMBER CONSTRAINT CON_T3 UNIQUE);
CREATE TABLE T4(C1 NUMBER PRIMARY KEY, C2 NUMBER CONSTRAINT CON_T4 UNIQUE);

INSERT INTO T1 VALUES (1,1);
INSERT INTO T2 VALUES (1,1);
INSERT INTO T3 VALUES (1,1);
INSERT INTO T4 VALUES (1,1);

COMMIT;

INSERT INTO T1
SELECT
  2,
  MAX(C2)+1 C2
FROM
  T1;

UPDATE T1 SET C2=3 WHERE C1=1;

Session 2:
INSERT INTO T2 SELECT   2,   MAX(C2)+1 C2 FROM   T2;   UPDATE T2 SET C2=3 WHERE C1=1;  

Session 3:
INSERT INTO T3
SELECT
  2,
  MAX(C2)+1 C2
FROM
  T3;

UPDATE T3 SET C2=3 WHERE C1=1;

Session 4:
INSERT INTO T4
SELECT
  2,
  MAX(C2)+1 C2
FROM
  T4;

UPDATE T4 SET C2=3 WHERE C1=1;

Session 1:
INSERT INTO T2
SELECT
  3,
  MAX(C2)+1 C2
FROM
  T2;

(Session 1 hung)

Session 2:
INSERT INTO T3
SELECT
  3,
  MAX(C2)+1 C2
FROM
  T3;

(Session 2 hung)

Session 3:
INSERT INTO T4
SELECT
  3,
  MAX(C2)+1 C2
FROM
  T4;

(Session 3 hung)

Session 4:
INSERT INTO T1
SELECT
  3,
  MAX(C2)+1 C2
FROM
  T1;

(Session 4 hung, triggers a deadlock in session 3)

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-0003000f-00002d44        21     144     X             20     145           S
TX-0004000e-0000218e        20     145     X             19     146           S
TX-00070013-000021f4        19     146     X             22     143           S
TX-000a0021-000021f1        22     143     X             21     144           S

session 144: DID 0001-0015-00000007 session 145: DID 0001-0014-00000003
session 145: DID 0001-0014-00000003 session 146: DID 0001-0013-00000003
session 146: DID 0001-0013-00000003 session 143: DID 0001-0016-00000001
session 143: DID 0001-0016-00000001 session 144: DID 0001-0015-00000007

Rows waited on:
  Session 144: no row
  Session 145: no row
  Session 146: no row
  Session 143: no row

(Rollback all sessions)

 

Session 1:
UPDATE T1 SET C2=3 WHERE C1=1;

Session 2:
UPDATE T2 SET C2=3 WHERE C1=1;

Session 3:
UPDATE T3 SET C2=3 WHERE C1=1;

Session 4:
UPDATE T4 SET C2=3 WHERE C1=1;

Session 1:
UPDATE T2 SET C2=3 WHERE C1=1;

(Session 1 hung)

Session 2:
UPDATE T3 SET C2=3 WHERE C1=1;

(Session 2 hung)

Session 3:
UPDATE T4 SET C2=3 WHERE C1=1;

(Session 3 hung)

Session 4:
UPDATE T1 SET C2=3 WHERE C1=1;

(Session 4 hung, triggers a deadlock in session 1)

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00010012-000021f2        19     146     X             22     143           X
TX-00030010-00002d4b        22     143     X             21     144           X
TX-0009000a-00002c69        21     144     X             20     145           X
TX-00060003-00002c5d        20     145     X             19     146           X

session 146: DID 0001-0013-00000003 session 143: DID 0001-0016-00000001
session 143: DID 0001-0016-00000001 session 144: DID 0001-0015-00000007
session 144: DID 0001-0015-00000007 session 145: DID 0001-0014-00000003
session 145: DID 0001-0014-00000003 session 146: DID 0001-0013-00000003

Rows waited on:
  Session 146: obj - rowid = 00015185 - AAAVGFAAEAAFps4AAA
  (dictionary objn - 86405, file - 4, block - 1481528, slot - 0)
  Session 143: obj - rowid = 00015182 - AAAVGCAAEAAFpsgAAA
  (dictionary objn - 86402, file - 4, block - 1481504, slot - 0)
  Session 144: obj - rowid = 0001518B - AAAVGLAAEAAFptoAAA
  (dictionary objn - 86411, file - 4, block - 1481576, slot - 0)
  Session 145: obj - rowid = 00015188 - AAAVGIAAEAAFptQAAA
  (dictionary objn - 86408, file - 4, block - 1481552, slot - 0)

 

Session 1:
INSERT INTO T1
SELECT
  2,
  MAX(C2)+1 C2
FROM
  T1;

Session 2:
INSERT INTO T2
SELECT
  2,
  MAX(C2)+1 C2
FROM
  T2;

Session 3:
INSERT INTO T3
SELECT
  2,
  MAX(C2)+1 C2
FROM
  T3;

Session 4:
INSERT INTO T4
SELECT
  2,
  MAX(C2)+1 C2
FROM
  T4;

Session 1:
INSERT INTO T2
SELECT
  2,
  MAX(C2)+1 C2
FROM
  T2;

(Session 1 hung)

Session 2:
INSERT INTO T3
SELECT
  2,
  MAX(C2)+1 C2
FROM
  T3;

(Session 2 hung)

Session 3:
INSERT INTO T4
SELECT
  2,
  MAX(C2)+1 C2
FROM
  T4;

(Session 3 hung)

Session 4:
INSERT INTO T1
SELECT
  2,
  MAX(C2)+1 C2
FROM
  T1;

(Session 4 hung, triggers a deadlock in session 2)

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-0004001d-000008c5        24     144     X             22     143           S
TX-0006001c-00000b8e        22     143     X             25     152           S
TX-00080003-00000b7c        25     152     X             23     158           S
TX-00090027-00000b7c        23     158     X             24     144           S
session 144: DID 0001-0018-00000002 session 143: DID 0001-0016-00000002
session 143: DID 0001-0016-00000002 session 152: DID 0001-0019-00000002
session 152: DID 0001-0019-00000002 session 158: DID 0001-0017-00000006
session 158: DID 0001-0017-00000006 session 144: DID 0001-0018-00000002
Rows waited on:
Session 143: no row
Session 152: no row
Session 158: obj - rowid = 0000BD35 - AAAL01AAEAAGGSOAAA
  (dictionary objn - 48437, file - 4, block - 1598606, slot - 0)
Session 144: no row

(Rollback all sessions)

 

Session 1:
UPDATE T1 SET C1=3 WHERE C1=1;

Session 2:
UPDATE T2 SET C1=3 WHERE C1=1;

Session 3:
UPDATE T3 SET C1=3 WHERE C1=1;

Session 4:
UPDATE T4 SET C1=3 WHERE C1=1;

Session 1:
UPDATE T2 SET C1=3 WHERE C1=1;

(Session 1 hung)

Session 2:
UPDATE T3 SET C1=3 WHERE C1=1;

(Session 2 hung)

Session 3:
UPDATE T4 SET C1=3 WHERE C1=1;

(Session 3 hung)

Session 4:
UPDATE T1 SET C1=3 WHERE C1=1;

(Session 4 hung, triggers a deadlock in session 3)

                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00070028-00000871        23     158     X             24     144           X
TX-0006000c-00000b8f        24     144     X             22     143           X
TX-0005002d-00000b88        22     143     X             25     152           X
TX-00090004-00000b7d        25     152     X             23     158           X
session 158: DID 0001-0017-00000006 session 144: DID 0001-0018-00000002
session 144: DID 0001-0018-00000002 session 143: DID 0001-0016-00000002
session 143: DID 0001-0016-00000002 session 152: DID 0001-0019-00000002
session 152: DID 0001-0019-00000002 session 158: DID 0001-0017-00000006
Rows waited on:
Session 144: obj - rowid = 0000BD32 - AAAL0yAAEAAFhH2AAA
  (dictionary objn - 48434, file - 4, block - 1446390, slot - 0)
Session 143: obj - rowid = 0000BD2F - AAAL0vAAEAAFhHeAAA
  (dictionary objn - 48431, file - 4, block - 1446366, slot - 0)
Session 152: obj - rowid = 0000BD2C - AAAL0sAAEAAFOjOAAA
  (dictionary objn - 48428, file - 4, block - 1370318, slot - 0)
Session 158: obj - rowid = 0000BD35 - AAAL01AAEAAGGSOAAA
  (dictionary objn - 48437, file - 4, block - 1598606, slot - 0)

(Rollback all sessions)

 

Session 1:
ALTER TABLE T1 ADD (C3 NUMBER);
ALTER TABLE T2 ADD (C3 NUMBER);
ALTER TABLE T3 ADD (C3 NUMBER);
ALTER TABLE T4 ADD (C3 NUMBER);

Session 1:
UPDATE T1 SET C3=3 WHERE C1=1;

Session 2:
UPDATE T2 SET C3=3 WHERE C1=1;

Session 3:
UPDATE T3 SET C3=3 WHERE C1=1;

Session 4:
UPDATE T4 SET C3=3 WHERE C1=1;

Session 1:
UPDATE T2 SET C3=3 WHERE C1=1;

(Session 1 hung)

Session 2:
UPDATE T3 SET C3=3 WHERE C1=1;

(Session 2 hung)

Session 3:
UPDATE T4 SET C3=3 WHERE C1=1;

(Session 3 hung)

Session 4:
UPDATE T1 SET C3=3 WHERE C1=1;

(Session 4 hung, triggers a deadlock in session 1)

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00090005-00002c7c        19     146     X             22     143           X
TX-0005001c-00002cb3        22     143     X             21     144           X
TX-0007000f-0000223a        21     144     X             20     145           X
TX-00080020-00002d01        20     145     X             19     146           X

session 146: DID 0001-0013-00000003 session 143: DID 0001-0016-00000001
session 143: DID 0001-0016-00000001 session 144: DID 0001-0015-00000007
session 144: DID 0001-0015-00000007 session 145: DID 0001-0014-00000003
session 145: DID 0001-0014-00000003 session 146: DID 0001-0013-00000003

Rows waited on:
  Session 146: obj - rowid = 00015185 - AAAVGFAAEAAFps4AAA
  (dictionary objn - 86405, file - 4, block - 1481528, slot - 0)
  Session 143: obj - rowid = 00015182 - AAAVGCAAEAAFpsgAAA
  (dictionary objn - 86402, file - 4, block - 1481504, slot - 0)
  Session 144: obj - rowid = 0001518B - AAAVGLAAEAAFptoAAA
  (dictionary objn - 86411, file - 4, block - 1481576, slot - 0)
  Session 145: obj - rowid = 00015188 - AAAVGIAAEAAFptQAAA
  (dictionary objn - 86408, file - 4, block - 1481552, slot - 0)




Faulty Quotes 2 – Test Cases

6 12 2009

December 6, 2009 (Updated February 24, 2010)

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

Are test cases valuable or not?  These quotes were found through Internet searches:

http://forums.oracle.com/forums/thread.jspa?threadID=587394

“Oracle is NOT MATH, a single contrary test case does not invalidate any general principle of Oracle performance.”

http://forums.oracle.com/forums/thread.jspa?threadID=963129

“Just because someone show a case where indexes do not need to be rebuilt, that DOES NOT mean that positive cases don’t exist!

I can write a test case to ‘prove’ that virtually any statement about Oracle is un-true. It’s easy.

People who believe that a single negative test case proves something is wrong join the ranks of the ‘deniers’, folks who cite ‘proof’ that the moon landing never happened, and that 911 was a government conspiracy.”

http://forums.oracle.com/forums/thread.jspa?messageID=3195898&tstart=0 (thread was taken offline by OTN, but still shows in search results)

“100% true? You are joking, right? There is NOTHING that is 100% true about database tuning . . . . YOU CANNOT PROVE ANYTHING ABOUT ORACLE PERFORMANCE. EVER. NO EQUATIONS, NO PROOFS, NO WAY, NO HOW. . . . Only fools or charlatans will claim that something about Oracle performance has been ‘proven wrong’. . . .

http://forums.oracle.com/forums/thread.jspa?threadID=963129&start=0

“It’s ridiculous to reverse engineer Oracle with test cases, when we can ask the people who hold the source code.”

“A test case is not the same as software testing!

The problem is that a single-user ‘test case’ on a PC is not a valid test, by any measure. . .

It does not accurately reproduce real-world behavior, especially in performance tuning, where slowdowns are only seen under heavy loads.”

“It baffles me why any practicng DBA would want to write a test case, when they have a real-world test database, full of real data and waiting to be used . . .”

————————-

(Added February 24, 2010):

If you search this site for the phrase test case, I think that it will be clear that test cases, when properly constructed, are extremely helpful for determining how things work – and how things should not work.  Properly constructing a test case is critical to help eliminate false causation and false correlation.  Employing the scientific method (secondary reference) is important when building test cases to help control false positives and false negatives.  Keep in mind that if something is stated as an absolute (for example “the sun rises in the East” or “on Earth the sun always rises from the East“), it only requires a single negative test case to refute the absolute statement.





COLLECTION ITERATOR (PICKLER FETCH) Appearing in DBMS_XPLAN Output

6 12 2009

December 6, 2009

In several discussion threads a DBMS_XPLAN is requested, and the provided output will occasionally look like this:

-----------------------------------------------------
| Id  | Operation                         | Name    |
-----------------------------------------------------
|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY |
-----------------------------------------------------

One such case appeared in this discussion thread:
http://forums.oracle.com/forums/thread.jspa?threadID=656614&start=0

In short, the problem is caused by either enabling server output, or enabling SQL*Plus’ autotrace.

A quick test setup that I provided in the above thread to reproduce the problem:

CREATE TABLE T1 (C1 NUMBER(12));

INSERT INTO
  T1
SELECT
  ROWNUM
FROM
  DUAL
CONNECT BY
  LEVEL<=10000;

COMMIT;

Let’s try an experiment with a simple SQL statement:

SELECT
  C1
FROM
  T1
WHERE
  C1 BETWEEN 90 AND 150;

With the following settings:

OPTIMIZER_MODE=CHOOSE
OPTIMIZER_FEATURES_ENABLE=9.0.1
STATISTICS_LEVEL=ALL  (session level)

 

SQL_ID  84bwrtyfrxhzx, child number 0
--------------------------------------------------------------------------------
SELECT    C1  FROM    T1  WHERE    C1 BETWEEN 90 AND 150
Plan hash value: 3617692013
---------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| T1   |      1 |     61 |00:00:00.01 |      23 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(("C1"<=150 AND "C1">=90))
Note
--------------------------------------------------------------------------------
   - rule based optimizer used (consider using cbo)

(That warning surprised me a bit… as this is running on Oracle 11.1.0.6 and the rule based optimizer was deprecated in 10g R1.)

Partial output from a 10046 at level 12 enabled at the same time:

=====================
PARSING IN CURSOR #3 len=56 dep=0 uid=63 oct=3 lid=63 tim=657639757986 hv=2642330621 ad='1af34a2c' sqlid='84bwrtyfrxhzx'
SELECT
  C1
FROM
  T1
WHERE
  C1 BETWEEN 90 AND 150
END OF STMT
PARSE #3:c=0,e=701,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=657639757982
BINDS #3:
EXEC #3:c=0,e=57,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=657639758209
WAIT #3: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=657639758249
FETCH #3:c=15600,e=713,p=0,cr=23,cu=0,mis=0,r=61,dep=0,og=4,tim=657639759004
STAT #3 id=1 cnt=61 pid=0 pos=1 obj=72628 op='TABLE ACCESS FULL T1 (cr=23 pr=0 pw=0 time=99 us)'
WAIT #3: nam='SQL*Net message from client' ela= 9637 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=657639779290
=====================
...
PARSING IN CURSOR #4 len=80 dep=0 uid=63 oct=3 lid=63 tim=657640013624 hv=266232738 ad='22039234' sqlid='0nfjn6n7xwsx2'
SELECT
  *
FROM
  TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'))
END OF STMT
PARSE #4:c=46800,e=234176,p=2,cr=199,cu=0,mis=1,r=0,dep=0,og=4,tim=657640013619
WAIT #4: nam='SQL*Net message to client' ela= 8 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=657640013799
WAIT #4: nam='SQL*Net message from client' ela= 570 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=657640014444
BINDS #4:
EXEC #4:c=0,e=58,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=657640014557
WAIT #4: nam='SQL*Net message to client' ela= 9 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=657640495379
FETCH #4:c=171601,e=480979,p=15,cr=888,cu=0,mis=0,r=21,dep=0,og=4,tim=657640495566
STAT #4 id=1 cnt=21 pid=0 pos=1 obj=0 op='COLLECTION ITERATOR PICKLER FETCH DISPLAY_CURSOR (cr=888 pr=15 pw=15 time=81 us)'

In the above 10046 trace, note the STAT line containing “COLLECTION ITERATOR PICKLER FETCH DISPLAY_CURSOR” on the last line – that is the execution plan for the call to DBMS_XPLAN.DISPLAY_CURSOR, and not the original query that was of interest. That was the plan that Lokesh provided to Jonathan, and not the one that Jonathan requested.

Let’s try again to remove the “rule based optimizer used” warning:

OPTIMIZER_MODE=ALL_ROWS
OPTIMIZER_FEATURES_ENABLE=11.1.0.6
STATISTICS_LEVEL=ALL  (session level)

The DBMS_XPLAN:

SQL_ID  84bwrtyfrxhzx, child number 1
--------------------------------------------------------------------------------
SELECT    C1  FROM    T1  WHERE    C1 BETWEEN 90 AND 150
Plan hash value: 3617692013
------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| T1   |      1 |     61 |     61 |00:00:00.01 |      23 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(("C1">=90 AND "C1"<=150))
Note
--------------------------------------------------------------------------------
   - dynamic sampling used for this statement

 
Partial output from a 10046 at level 12 enabled at the same time:

=====================
PARSING IN CURSOR #12 len=56 dep=0 uid=63 oct=3 lid=63 tim=657812448763 hv=2642330621 ad='1af34a2c' sqlid='84bwrtyfrxhzx'
SELECT
  C1
FROM
  T1
WHERE
  C1 BETWEEN 90 AND 150
END OF STMT
PARSE #12:c=46801,e=54061,p=0,cr=24,cu=0,mis=1,r=0,dep=0,og=1,tim=657812448759
BINDS #12:
EXEC #12:c=0,e=51,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=657812449038
WAIT #12: nam='SQL*Net message to client' ela= 8 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=657812449085
FETCH #12:c=0,e=768,p=0,cr=23,cu=0,mis=0,r=61,dep=0,og=1,tim=657812449899
STAT #12 id=1 cnt=61 pid=0 pos=1 obj=72628 op='TABLE ACCESS FULL T1 (cr=23 pr=0 pw=0 time=75 us cost=7 size=793 card=61)'
WAIT #12: nam='SQL*Net message from client' ela= 10282 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=657812460271
=====================
...
PARSING IN CURSOR #11 len=80 dep=0 uid=63 oct=3 lid=63 tim=657812468416 hv=266232738 ad='22039234' sqlid='0nfjn6n7xwsx2'
SELECT
  *
FROM
  TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'))
END OF STMT
PARSE #11:c=0,e=7984,p=0,cr=111,cu=0,mis=1,r=0,dep=0,og=1,tim=657812468413
FETCH #11:c=62400,e=56650,p=0,cr=274,cu=0,mis=0,r=21,dep=0,og=1,tim=657812526032
STAT #11 id=1 cnt=21 pid=0 pos=1 obj=0 op='COLLECTION ITERATOR PICKLER FETCH DISPLAY_CURSOR (cr=274 pr=0 pw=0 time=86 us)'

I hope that clears things up for you. I tried the following, but was not able to reproduce DBMS_XPLAN returning the plan for DBMS_XPLAN, rather than the last plan executed:

ALTER SESSION SET STATISTICS_LEVEL='ALL';

ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

SET AUTOTRACE ON STATISTICS

SELECT
  C1
FROM
  T1
WHERE
  C1 BETWEEN 90 AND 150;

 

Statistics
--------------------------------------------------------------------------------
          0  recursive calls
          0  db block gets
         28  consistent gets
          0  physical reads
          0  redo size
       1279  bytes sent via SQL*Net to client
        460  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         61  rows processed

 

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

 

SQL> SET AUTOTRACE ON STATISTICS
SQL> SET SERVEROUTPUT ON
SQL> SELECT /*+ gather_plan_statistics */
  2    C1
  3  FROM
  4    T1
  5  WHERE
  6    C1 BETWEEN 90 AND 150;
        C1
--------------------------------------------------------------------------------
        90
        91
        92
...
       148
       149
       150
61 rows selected.

Statistics
--------------------------------------------------------------------------------
          0  recursive calls
          0  db block gets
         28  consistent gets
          0  physical reads
          0  redo size
       1029  bytes sent via SQL*Net to client
        378  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         61  rows processed

 

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  9babjv8yq8ru3, child number 0
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)
--------------------------------------------------------------------------------




Determining Why a Query Using MIN(column) in the WHERE Clause on an Indexed Column takes a Long Time

6 12 2009

December 6, 2009

Some time ago the following question appeared in the comp.database.oracle.server Usenet group:
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/79d0a3d85381dc64

I have a basic event table in my 10g database, primary integer key, and a nonnull timestamp “eventdate” field.  When executing the following command:

select min(eventdate) from events;

It can occasionally take a VERY long time.  There is a standard btree
index on “eventdate”, and roughly 20 other columns, a few of which
also have basic btree indexes.

In my current scenario, the table has less than a million records, and
the query seems to be taking upwards of 10 minutes!!!  Here’s what the
console says the plan is:

Operation       Object  Object Type     Order   Rows    Size (KB)       Cost    Time (sec)      CPU
Cost    I/O Cost
SELECT STATEMENT                        3                               4
 SORT AGGREGATE                         2       1       0.011
  INDEX FULL SCAN (MIN/MAX) EVENTS_EVENTDATE    INDEX   1       736195  7,908.345
4       1       28686   4

I’ve run the ADDM, it updated the stats, but that doesn’t seem to help
(i run it again it has no recommendations).  Am i doing something
silly with this table or is there some better way for me to phrase
this query?

I provided the following test case to demonstrate one possible reason for a MIN(column) query to require a longer time to execute than it should:

CREATE TABLE T1 (EVENTDATE TIMESTAMP NOT NULL);
CREATE INDEX T1_IND1 ON T1(EVENTDATE);
INSERT INTO
  T1
SELECT
  TRUNC(SYSDATE-3000)+ROWNUM/100
FROM
  DUAL
CONNECT BY
  LEVEL<=900000;

COMMIT;

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

SELECT
  MIN(EVENTDATE)
FROM
  T1;

The DBMS Xplan:

 
---------------------------------------------------------------------------­--------------------
| Id  | Operation                  | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------­--------------------
|   1 |  SORT AGGREGATE            |         |      1 |      1 |      1 |00:00:00.01 |       3 |
|   2 |   INDEX FULL SCAN (MIN/MAX)| T1_IND1 |      1 |    904K|      1 |00:00:00.01 |       3 |
---------------------------------------------------------------------------­--------------------

From the 10046 trace file:

PARSE #1:c=0,e=6954,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=5794606730
EXEC #1:c=0,e=107,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=5794608342
WAIT #1: nam='SQL*Net message to client' ela= 7 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=5794608829
FETCH #1:c=0,e=91,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,tim=5794609296
WAIT #1: nam='SQL*Net message from client' ela= 10946 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=5794620734
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=3 pr=0 pw=0 time=93 us)'
STAT #1 id=2 cnt=1 pid=1 pos=1 obj=32286 op='INDEX FULL SCAN (MIN/MAX) T1_IND1 (cr=3 pr=0 pw=0 time=55 us)'

In the above, Oracle used an INDEX FULL SCAN (MIN/MAX) to find the minimum using an optimization, requiring only 3 consistent reads (cr=3 and Buffers = 3), rather than having to scan all of the blocks in the index.  Oracle required 91/1000000 of a second to fetch the result.
Now, let’s see what happens when other sessions are involved.  In a second session:

DELETE FROM
  T1
WHERE
  EVENTDATE<SYSDATE-1000;

200,053 ROWS DELETED

(no commit performed)
Back to the first session to see how the query is affected:

SELECT
  MIN(EVENTDATE)
FROM
  T1;

The DBMS Xplan:

---------------------------------------------------------------------------­--------------------
| Id  | Operation                  | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------­--------------------
|   1 |  SORT AGGREGATE            |         |      1 |      1 |      1 |00:00:00.01 |       6 |
|   2 |   INDEX FULL SCAN (MIN/MAX)| T1_IND1 |      1 |    904K|      1 |00:00:00.01 |       6 |
---------------------------------------------------------------------------­--------------------

From the 10046 trace file:

 
PARSE #1:c=0,e=140,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=5929305668
EXEC #1:c=0,e=106,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=5929307277
WAIT #1: nam='SQL*Net message to client' ela= 6 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=5929307728
FETCH #1:c=0,e=7540,p=0,cr=6,cu=0,mis=0,r=1,dep=0,og=1,tim=5929315743
WAIT #1: nam='SQL*Net message from client' ela= 1728 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=5929318030
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=6 pr=0 pw=0 time=7540 us)'
STAT #1 id=2 cnt=1 pid=1 pos=1 obj=32286 op='INDEX FULL SCAN (MIN/MAX) T1_IND1 (cr=6 pr=0 pw=0 time=7501 us)'

Note that there are now 6 consistent reads, rather than 3, and that the elapsed time for the FETCH has increased from 91 to 7540.
In a third session:

DELETE FROM
  T1
WHERE
  EVENTDATE>SYSDATE+400;

559,947 ROWS DELETED

Back to the first session:

SELECT
  MIN(EVENTDATE)
FROM
  T1;

The DBMS Xplan:

---------------------------------------------------------------------------­--------------------
| Id  | Operation                  | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------­--------------------
|   1 |  SORT AGGREGATE            |         |      1 |      1 |      1 |00:00:00.01 |       6 |
|   2 |   INDEX FULL SCAN (MIN/MAX)| T1_IND1 |      1 |    904K|      1 |00:00:00.01 |       6 |
---------------------------------------------------------------------------­--------------------

From the 10046 trace file:

PARSE #3:c=0,e=89,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=6311133836
EXEC #3:c=0,e=167,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=6311135482
WAIT #3: nam='SQL*Net message to client' ela= 6 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=6311135920
FETCH #3:c=0,e=565,p=0,cr=6,cu=0,mis=0,r=1,dep=0,og=1,tim=6311136874
WAIT #3: nam='SQL*Net message from client' ela= 1520 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=6311138894
STAT #3 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=6 pr=0 pw=0 time=568 us)'
STAT #3 id=2 cnt=1 pid=1 pos=1 obj=32286 op='INDEX FULL SCAN (MIN/MAX) T1_IND1 (cr=6 pr=0 pw=0 time=530 us)'

No change, other than the elapsed time decreasing.
In the third session:

ROLLBACK;

INSERT INTO
  T1
SELECT
  TRUNC(SYSDATE-3001)+ROWNUM/100
FROM
  DUAL
CONNECT BY
  LEVEL<=2;

INSERT INTO
  T1
SELECT
  TRUNC(SYSDATE-3030)+ROWNUM/100
FROM
  DUAL
CONNECT BY
  LEVEL<=3000;

DELETE FROM
  T1
WHERE
  EVENTDATE>SYSDATE+4000;

Back to the first session:

SELECT
  MIN(EVENTDATE)
FROM
  T1;

The DBMS Xplan:

---------------------------------------------------------------------------­--------------------
| Id  | Operation                  | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------­--------------------
|   1 |  SORT AGGREGATE            |         |      1 |      1 |      1 |00:00:00.04 |     365 |
|   2 |   INDEX FULL SCAN (MIN/MAX)| T1_IND1 |      1 |    904K|      1 |00:00:00.04 |     365 |
---------------------------------------------------------------------------­--------------------

From the 10046 trace file:

PARSE #7:c=0,e=54,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=6738688175
EXEC #7:c=0,e=103,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=6738689672
WAIT #7: nam='SQL*Net message to client' ela= 6 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=6738690122
FETCH #7:c=0,e=44496,p=0,cr=365,cu=0,mis=0,r=1,dep=0,og=1,tim=6738734984
WAIT #7: nam='SQL*Net message from client' ela= 1605 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=6738737134
STAT #7 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=365 pr=0 pw=0 time=44494 us)'
STAT #7 id=2 cnt=1 pid=1 pos=1 obj=32286 op='INDEX FULL SCAN (MIN/MAX) T1_IND1 (cr=365 pr=0 pw=0 time=44453 us)'

Note now that Oracle performed 365 consistent reads, and that the elapsed time has increased from 565 to 44496.  Actual time in the DBMS Xplan reports an increase from 0.01 seconds to 0.04 seconds.
You might take a look at the output of the following to help determine if Oracle is having to rollback uncommitted data to provide a consistent view of the data due to an in-process transaction:

SELECT
  *
FROM
  V$TRANSACTION;

SELECT
  *
FROM
  V$LOCK;

The original poster provided the following and sent to me a trace file for the query execution:

I tried your experiment, and achieved similar responses – i’m a complete novice and don’t know exactly how you got the 10046 trace output, but i did do the explain stuff:

---------------------------------------------------------------------------­----------
| Id  | Operation                  | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------­----------
|   0 | SELECT STATEMENT           |         |     1 |    11 |       3 (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |         |     1 |    11 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| T1_IND1 |   901K|  9685K|       3 (0)| 00:00:01 |
---------------------------------------------------------------------------­----------

I think this is comparable, but please advise if not.
2nd question – my oracle web console tells me i’m running 10.2.0.1.0. I attempted to enable the plustrace role, and do as suggested, here’s what i got:
SQL> select min(eventdate) from events;

 
MIN(EVENTDATE)
---------------------------------------------------------------------------
21-JAN-08 04.51.45.525000 PM

Execution Plan
----------------------------------------------------------
Plan hash value: 116994577
---------------------------------------------------------------------------­---------------------
| Id  | Operation                  | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------­---------------------
|   0 | SELECT STATEMENT           |                    |     1 |    11 |     4   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |                    |     1 |    11 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| EVENTS_EVENTDATE   |   736K|  7908K|     4   (0)| 00:00:01 |
---------------------------------------------------------------------------­---------------------
Statistics
----------------------------------------------------------
        901  recursive calls
          0  db block gets
     118525  consistent gets
     118248  physical reads
          0  redo size
        424  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         18  sorts (memory)
          0  sorts (disk)
          1  rows processed

Does this explain anything?  Here’s the same output using the “new” table, which for whatever reason is much better:

SQL> select min(eventdate) from t1;

MIN(EVENTDATE)
---------------------------------------------------------------------------
11-NOV-99 12.14.24.000000 AM

Execution Plan

----------------------------------------------------------
Plan hash value: 3630964933
---------------------------------------------------------------------------­----------
| Id  | Operation                  | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------­----------
|   0 | SELECT STATEMENT           |         |     1 |    11 |       3 (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |         |     1 |    11 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| T1_IND1 |   901K|  9685K|       3 (0)| 00:00:01 |
---------------------------------------------------------------------------­----------
Statistics
----------------------------------------------------------
        676  recursive calls
          0  db block gets
        132  consistent gets
          6  physical reads
          0  redo size
        422  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         22  sorts (memory)
          0  sorts (disk)
          1  rows processed

 In the above, notice the 118,525 consistent gets and the 118,248 for the original table compared to the 132 consistent gets and 6 physical reads for the test table that the OP created, which indicates that Oracle was having trouble immediately jumping to the index block containing the minimum value when performing the INDEX FULL SCAN (MIN/MAX) operation.

The OP provided the following follow up:

One followup question, that you’ll all likely laugh at.  I got into this “mess” by having a table that we frequently perform delete from TABLE where DATE < ? – this situation is going to continue to arise. Should i simply schedule frequent index rebuilds?  I understand partitioning the data is probably the way to go, but what is frequent rebuilds the simplest solution here (by simple, i mean least knowledge/testing/sql involved)?

Jonathan Lewis provided an excellent response to the OP.





Faulty Quotes 1 – OPTIMIZER_INDEX_COST_ADJ

6 12 2009

December 5, 2009

(Forward to the Next Post in the Series)

There are several initialization parameters, some of which are hidden (all hidden parameters begin with an underscore character and should not be changed without authorization from Oracle support), which control memory utilization, execution plan costing, latch spin behavior, maximum I/O size for multiblock reads from disk, etc. It is easy to fall into a pattern of blindly changing the initialization parameter without identifying the actual source of the problem for which the initialization parameter change is expected to magically correct. The approach of blindly changing the initialization parameters without understanding the scope/purpose of the parameters nor the source of the problem is apparently encouraged by various Oracle books, official looking web pages, and web discussion threads.

One such parameter that is frequently abused is OPTIMIZER_INDEX_COST_ADJ.  This parameter specifies the percentage of the calculated index cost to retain.  A value of 1 for OPTIMIZER_INDEX_COST_ADJ is a bad idea, as it not only makes index access paths appear to be 1/100 times as expensive (1% of the original cost) which will drive the use of indexes, but also potentially causes the wrong index to be used if two or more indexes have the same (rounded) calculated cost.

Examples quotes recommending low values for this parameter, even in 10g R1 and above:

praetoriate.com/t_op_sql_index_behavior.htm and
http://books.google.com/books?id=4xEozkbvxboC&pg=RA1-PA359#v=onepage&q=&f=false

“The optimizer_index_cost_adj parameter is an initialization parameter that can be very useful for SQL tuning. It is a numeric parameter with values from zero to 1,000 and a default value of 1,000.”

praetoriate.com/t_op_sql_index_behavior.htm

“If you are having slow performance because the CBO first_rows mode is favoring too many full-table scans, you can reset the optimizer_index_cost_adj parameter to immediately tune all of the SQL in your database to favor index scans over full-table scans.”

http://books.google.com/books?id=TuzniwcsNtUC&pg=PT771#v=onepage&q=&f=false

“Unfortunately, the optimizer might do more full table scans as a result of setting DB_FILE_MULTIBLOCK_READ_COUNT (you don’t want this behavior), so you may also need to set the OPTIMIZER_INDEX_COST_ADJ to a number, such as 10, to eliminate this problem and drive the use of indexes.”

http://books.google.com/books?id=omq9aRx8s0EC&pg=PT171#v=onepage&q=&f=false

“If the use of DB_FILE_MULTIBLOCK_READ_COUNT starts to cause too many full table scans (since the optimizer now decides it can perform full table scans much faster and decides to do more of them) then set OPTIMIZER_INDEX_COST_ADJ between 1 and 10 (I usually use 10) to force index use more frequently.”

oracle-training.cc/oracle_tips_opq.htm

“The default value for optimizer_index_cost_adj is 1,000, and any value less than 1,000 makes the CBO view indexes less expensive. If you do not like the propensity of the CBO first_rows mode to favor full-table scans, you can lower the value of optimizer_index_cost_adj to 10, thereby telling the CBO to always favor index scans over full-table scans.”

http://books.google.com/books?id=bxHDtttb0ZAC&pg=PA566#v=onepage&q=&f=false

“The most important parameter is the optimizer_index_cost_adj, and the default setting of 100 is incorrect for most Oracle systems.  For OLTP systems, resetting the parameter to a smaller value (between 10 and 30) may result in huge performance gains as SQL statements change from large-table full-table scans to index range scans.”

http://willgreene.net/siebel/Siebel%20Knowledgebase/Performance%20Tuning%20Guide%20for%20Siebel%20on%20Oracle.pdf

“OPTIMIZER_INDEX_COST_ADJ – Controls the access path selection to be more or less index friendly.  Recommended Value = 1

For Oracle 9i CBO setting OPTIMIZER_INDEX_COST_ADJ = 1 is imperative. This will set the optimizer goal for best response time (versus best throughput). Incorrect setting may cause the optimizer to favor full-table scans instead of index access.
• For Oracle 10g/11g CBO setting OPTIMIZER_INDEX_COST_ADJ = 1 is also recommended, although the default setting of 100 will deliver good results in most cases. It is important to understand that in-house tuning of Siebel CRM application was performed with OPTIMIZER_INDEX_COST_ADJ = 1 setting. This means that customers who want to implement OPTIMIZER_INDEX_COST_ADJ = 100 on Oracle 10g/11g will need to allocate extra development time for additional tuning that may be necessary.
Under no circumstances OPTIMIZER_INDEX_COST_ADJ parameter should be set to values other than 1 (Siebel recommended) or 100 (Oracle default on 10g/11g).”

 Below is a link to a test case, which shows that even when retrieving 0.06% of the rows from a 100 million row table it is potentially faster when a full table scan is used, rather than an index range scan (the index clustering factor was very high):
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/6c436cee329326ec

Before experimenting with this parameter take a look at the following:
http://books.google.com/books?id=b3DIkYO2gBQC&pg=PA185#v=onepage&q=&f=false

http://richardfoote.wordpress.com/2009/07/08/the-cbo-and-indexes-optimizer_index_cost_adj-part-i/

http://richardfoote.wordpress.com/2009/07/22/the-cbo-and-indexes-optimizer_index_cost_adj-part-ii/

http://richardfoote.wordpress.com/2009/08/20/the-cbo-and-indexes-optimizer_index_cost_adj-part-iii/

http://forums.oracle.com/forums/thread.jspa?messageID=3917291 (Joze Senegacnik)

“The parameter optimizer_index_caching is considered by CBO for IN list and NESTED LOOP operations and should be set. The optimizer_index_cost_adj could be very dangerous unless you really know what you are doing, especially when system statistics is used. It was introduced in 8i, but in later releases one should use system statistics to tell CBO what is the timing difference between single block and multiple block I/O. Jonathan Lewis has written about this on his site.

What I really hate is that someone says: set this parameter to a certain value without considering what kind of system is that and what is even more important: without considering what are the consequences of such setting. It is like taking a wrong medicine.

My personal opinion regarding optimizer_index_cost_adj is that one should leave it at 100 (default) and if you really understand the mechanism behind then you may experiment with it. Personally I would use it only at statement level by using OPT_PARAM hint if this would be really necessary. This way you don’t make a system wide change.”





SQL – Retain Specific Sort Order

5 12 2009

December 5, 2009

Some time ago the following question appeared in the comp.database.oracle.misc Usenet group: http://groups.google.com/group/comp.databases.oracle.misc/browse_thread/thread/4352a39cab5ab408

Is there a simple way to use a previous result in an ORDER BY clause?

I have a rather complicate query that filters, sorts and returns a series of IDs:

FOO_ID
======
      1
     98
     12
     33

Then, I use these IDs to fetch further information about the items they represent:

SELECT .......
FROM FOO
LEFT JOIN BAR .......
WHERE FOO_ID IN (1, 98, 12, 33)

I keep the two queries separate to avoid excessive complexity. I compose the SQL code using PHP. Right now, the second query comes unsorted from Oracle: I use PHP to sort it at a later stage in my application (my PHP skills are better than my SQL ones). Would it be possible to use the ID list to sort the second query inside Oracle?

Server runs Oracle9i Enterprise Edition Release 9.2.0.1.0

No need to make this too difficult.  If you were on 10g, you could so some fancy things with regexp_substr.  A simple example which should work on 9i and below:
Create a testing table for this demonstration named T1, think of this as your FOO table:

CREATE TABLE T1 AS
SELECT
  ROWNUM C1
FROM
  DUAL
CONNECT BY
  LEVEL<=100;

Now the first step, just retrieve the rows you want:

SELECT
  C1
FROM
  T1
WHERE
  C1 IN (1,98,12,33);

        C1
----------
         1
        12
        33
        98

Now, sort the rows:

SELECT
  C1
FROM
  T1
WHERE
  C1 IN (1,98,12,33)
ORDER BY
  INSTR('1,98,12,33,' , TO_CHAR(C1)||',');

        C1
----------
         1
        98
        12
        33

Note in the INSTR, the sequence of the numbers must end in a comma, and we tell INSTR to locate the number in the list with a comma appended to the end of the value of C1.
Your SQL statement would look like this:

SELECT .......
FROM FOO
LEFT JOIN BAR .......
WHERE FOO_ID IN (1, 98, 12, 33)
ORDER BY
  INSTR(',1,98,12,33,' , ','||TO_CHAR(FOO_ID)||',');

Or:

SELECT .......
FROM
  FOO,
  BAR
WHERE
  FOO.FOO_ID=BAR.FOO_ID(+)
  AND FOO_ID IN (1, 98, 12, 33)
ORDER BY
  INSTR(',1,98,12,33,' , ','||TO_CHAR(FOO_ID)||',');

Before deciding to use the above technique, determine if there is a better way to do *everything* in a single SQL statement.  You might be able to do this by wrapping your complicated SQL statement into an inline view, and joining to that just as if it were a regular table:

SELECT .......
FROM
  FOO,
  BAR,
  ( complicated SQL here ) V
WHERE
  V.FOO_ID=FOO.ID
  AND FOO.FOO_ID=BAR.FOO_ID(+)
ORDER BY
  V.RN;

The RN column would be generated inside the inline view V, possibly like this, if there is an ORDER BY clause in the inline view:

  ROWNUM RN




SQL – Recursive Summing of Related Entities

5 12 2009

December 5, 2009

A recent thread in the comp.databases.oracle.server Usenet group asked the following:
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/fdce5b3d641c5f1a

Table dir_size stores the mbytes of storage used in a given directory. Table directories stores various directory names which may or may not exist in table dir_size.
For every directory in table directories, report the cumulative storage in that directory and all its subdirectories. This solution uses a cartesian join.  I imagine it will not scale well.

Thanks in advance!

create table dir_size (
  dir_name     varchar2(40),
  mbytes       number
  );

create table directories (
  dir_name    varchar2(40)
  );

insert into dir_size values ('c:\aaa\bbb\ccc\ddd', 100);
insert into dir_size values ('c:\aaa\bbb\ccc', 100);
insert into dir_size values ('c:\aaa\bbb', 100);
insert into dir_size values ('c:\aaa', 100);
insert into dir_size values ('c:\', 100);
insert into directories values ('c:\aaa\bbb\ccc\ddd');
insert into directories values ('c:\aaa\bbb\ccc');
insert into directories values ('c:\aaa\bbb');
insert into directories values ('c:\aaa');
insert into directories values ('c:\');
insert into directories values ('c:\xxx\yyy\zzz');
commit;

select dir_name, sum(mbytes) from (
  select directories.dir_name,
    instr(dir_size.dir_name, directories.dir_name) INSTR,
    mbytes
  from directories, dir_size
)
where INSTR = 1
group by dir_name
order by 1;

DIR_NAME                                 SUM(MBYTES)
---------------------------------------- -----------
c:\                                              500
c:\aaa                                           400
c:\aaa\bbb                                       300
c:\aaa\bbb\ccc                                   200
c:\aaa\bbb\ccc\ddd                               100

This appears to be a hard problem.  To avoid headaches, make certain that each of the DIR_NAMES ends with “\”

Let’s start here:

SELECT
  'c:\aaa\bbb\ccc\ddd\' DIR_NAME,
  100 MBYTES
FROM
  DUAL;

DIR_NAME                 MBYTES
-------------------- ----------
c:\aaa\bbb\ccc\ddd\         100

In your example, you would like to put 100MB into the following directories based on the above:

c:\
c:\aaa\
c:\aaa\bbb\
c:\aaa\bbb\ccc\
c:\aaa\bbb\ccc\ddd\

You somehow need to be able to break that one row into 5 rows.  The following might help

SELECT
  LEVEL L
FROM
  DUAL
CONNECT BY
  LEVEL<=20;

  L
---
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20

If we join those two row sources together we might be able to create 5 rows from the one row:

SELECT
  SUBSTR(DIR_NAME,1,INSTR(DIR_NAME,'\',1,L)) DIR_NAME2,
  MBYTES
FROM
  (SELECT
    'c:\aaa\bbb\ccc\ddd\' DIR_NAME,
    100 MBYTES
  FROM
    DUAL) DIR_SIZE,
  (SELECT
    LEVEL L
  FROM
    DUAL
  CONNECT BY
    LEVEL<=20) C
WHERE
  SUBSTR(DIR_NAME,1,INSTR(DIR_NAME,'\',1,L)) IS NOT NULL;

DIR_NAME2                MBYTES
-------------------- ----------
c:\                         100
c:\aaa\                     100
c:\aaa\bbb\                 100
c:\aaa\bbb\ccc\             100
c:\aaa\bbb\ccc\ddd\         100

Now, if we performed the same process for all of the rows in the DIR_SIZE table, grouping on DIR_NAME2, we might be able to find the SUM of the MBYTES column.

(Note that I did not provide an exact/final answer to the original poster – my post was intended to push the OP in the right direction of a solution.)

The OP followed up with this comment:

Thanks for the suggestion.  I suspect the best way will involve some kind of recursive processing.  The tricky bit is the matching of the rows in the directories table to the rows in the dir_size table.  We need to do a “like” (which we can’t, of course) which is why I thought of the instr.

 The LIKE keyword is not necessary.

Notice how closely the output of the following SQL statement:

SELECT
  'c:\aaa\bbb\ccc\ddd\' DIR_NAME,
  100 MBYTES
FROM
  DUAL;

Matches the row created by one of your insert statements:

insert into dir_size values ('c:\aaa\bbb\ccc\ddd', 100);

You might try replacing in the above examples:

SELECT
  'c:\aaa\bbb\ccc\ddd\' DIR_NAME,
  100 MBYTES
FROM
  DUAL;

With a SQL statement that selects all of the rows from your DIR_SIZE table – the results might surprise you IF each of the DIR_NAME values end with a “\”.
You really need more variety in the insert statements to see what is happening, for example:

insert into dir_size values ('c:\ddd\', 800);
insert into dir_size values ('c:\ddd\kkk\', 300);

The first of the above SQL statements will increase the calculated SUM in the c:\ directory by 800, and the second insert statement will increase the SUM in both of the c:\ and c:\ddd\ directories by 300 if you modify my original example to use the DIR_SIZE table rather than the DUAL table.
The final part that I did not provide to the OP is below:

TRUNCATE TABLE DIR_SIZE;

insert into dir_size values ('c:\aaa\bbb\ccc\ddd\', 100);
insert into dir_size values ('c:\aaa\bbb\ccc\', 100);
insert into dir_size values ('c:\aaa\bbb\', 100);
insert into dir_size values ('c:\aaa\', 100);
insert into dir_size values ('c:\', 100);
insert into dir_size values ('c:\ddd\', 800);
insert into dir_size values ('c:\ddd\kkk\', 300);

Working with the hints provided and the final SQL statement in my post, we start with the following:

SELECT
  SUBSTR(DIR_NAME,1,INSTR(DIR_NAME,'\',1,L)) DIR_NAME2,
  MBYTES
FROM
  (SELECT
    *
  FROM
    DIR_SIZE) DIR_SIZE,
  (SELECT
    LEVEL L
  FROM
    DUAL
  CONNECT BY
    LEVEL<=20) C
WHERE
  SUBSTR(DIR_NAME,1,INSTR(DIR_NAME,'\',1,L)) IS NOT NULL;

DIR_NAME2                MBYTES
-------------------- ----------
c:\                         100
c:\                         100
c:\                         100
c:\                         100
c:\                         100
c:\                         800
c:\                         300
c:\aaa\                     100
c:\aaa\                     100
c:\aaa\                     100
c:\aaa\                     100
c:\ddd\                     800
c:\ddd\                     300
c:\aaa\bbb\                 100
c:\aaa\bbb\                 100
c:\aaa\bbb\                 100
c:\ddd\kkk\                 300
c:\aaa\bbb\ccc\             100
c:\aaa\bbb\ccc\             100
c:\aaa\bbb\ccc\ddd\         100

 

SELECT
  SUBSTR(DIR_NAME,1,INSTR(DIR_NAME,'\',1,L)) DIR_NAME2,
  SUM(MBYTES) MBYTES
FROM
  DIR_SIZE,
  (SELECT
    LEVEL L
  FROM
    DUAL
  CONNECT BY
    LEVEL<=20) C
WHERE
  SUBSTR(DIR_NAME,1,INSTR(DIR_NAME,'\',1,L)) IS NOT NULL
GROUP BY
  SUBSTR(DIR_NAME,1,INSTR(DIR_NAME,'\',1,L))
ORDER BY
  SUBSTR(DIR_NAME,1,INSTR(DIR_NAME,'\',1,L));

DIR_NAME2                MBYTES
-------------------- ----------
c:\                        1600
c:\aaa\                     400
c:\aaa\bbb\                 300
c:\aaa\bbb\ccc\             200
c:\aaa\bbb\ccc\ddd\         100
c:\ddd\                    1100
c:\ddd\kkk\                 300




SQL – Methods of Reformatting into Equivalent Forms 6

5 12 2009

December 5, 2009

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

A recent post in the comp.database.oracle.server Usenet group asked the following question:
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/40d63ab1b4c0a3b4

Is there way to force the randomize within a set of number in Oracle?
Say I have a set of integers ( 2,8,6,5) and I want to force randomize function to randomly pick within that set only. I know about the DBMS_RANDOM package, sample and seed clause but none help what I need to do here.

any thoughts?

It was not clear if the integers will be present in table rows, supplied in a comma separated list, or through another method. Additionally, it was not clear if the solution should be provided in SQL, PL/SQL, or another programming language.  A couple people offered potential solutions.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Vladimir Zakharychev suggested the following:

Straightforward way: just generate random numbers between 0 and 1, divide the 0..1 range into 4 subranges and then pick a number from
your list depending on which subrange your generated random value belongs, like this:

with rnd as (select dbms_random.value val from dual)
select
  case
    when val < 0.25 then 2
    when val >= 0.25 and val < 0.5 then 8
    when val >= 0.5  and val < 0.75 then 6
    when val >= 0.75 then 5
  end x
from rnd

(note that this query does not work correctly in 9.2.0.8 for some reason – returns wrong results. In 10.2.0.4 it returns expected results.) Obviously, you can partition 0..1 range into as many subranges as there are numbers in your list and apply the same technique. A function that will do this automatically given an array of possible return values is not too hard to write.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Tiago offered the following solution:

SET serveroutput ON
DECLARE
Type tabIntegers IS TABLE OF NUMBER ;
  Integers tabIntegers ;
  minInteger NUMBER ;
  maxInteger NUMBER ;
  rndInteger NUMBER ;
  rndIntOk   BOOLEAN := False ;
BEGIN
  integers := tabIntegers( 2, 5, 10, 7, 3, 8, 11, 25, 0 ) ;
  FOR i IN integers.first..integers.last
  LOOP
    minInteger := Least(NVL(minInteger,integers(i)), integers(i));
    maxInteger := Greatest(NVL(maxInteger,integers(i)), integers(i));
  END LOOP ;
  LOOP
    rndInteger := TRUNC( dbms_random.value(minInteger,maxInteger) ) ;
    FOR i IN integers.first..integers.last
    LOOP
      IF rndInteger = integers(i) THEN
        rndIntOk   := true ;
        EXIT ;
      END IF ;
    END LOOP ;
    EXIT WHEN rndIntOk ;
  END LOOP ;
  dbms_output.put_line(rndInteger);
END ;
   

Tiago offered a follow-up solution: 

simplified, don’t know what I was thinking when did version 1.0.

SET serveroutput ON
DECLARE
Type tabIntegers
IS
  TABLE OF NUMBER ;
  Integers tabIntegers ;
  rndInteger NUMBER ;
BEGIN
  integers := tabIntegers( 2, 5, 10, 7, 3, 8, 11, 25, 0 ) ;
  rndInteger := integers(TRUNC( dbms_random.value(1,integers.last) ) ) ;
  dbms_output.put_line(rndInteger);
END ;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Mark Powell offered the following advice:

CK, instead of using a fixed range as Vladimir had in his example I was thinking I might use a MOD division of the random number returned from dbms_random to generate the index key into my table of values. This would make the code flexible for variable length lists.  If you have a fixed number of entries then Vlad’s solution is simple and easy to understand.  If your number of variables varies then I think this would fit the bill.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I offered the following:

Here is another way to do it, if you do not know how many elements will be in the list.

First, we return a row from DUAL with the rows of interest with a comma appended at the start and end:

SELECT
  ',2,8,6,5,54,100,67,7778,6,' ITEMS
FROM
  DUAL;

ITEMS
--------------------------
,2,8,6,5,54,100,67,7778,6,

Next, we need to determine the number of elements and pick an element position at random:

SELECT
  ITEMS,
  SUM(SIGN(INSTR(ITEMS, ',',1,ROWNUM)))-1 NUM_ITEMS,
  (TRUNC(DBMS_RANDOM.VALUE(0,SUM(SIGN(INSTR(ITEMS ,',',1,ROWNUM)))-1)) +1) SEL_ITEM
FROM
  (SELECT
    ',2,8,6,5,54,100,67,7778,6,' ITEMS
  FROM
    DUAL)
CONNECT BY
  LEVEL<20;

ITEMS                       NUM_ITEMS   SEL_ITEM
-------------------------- ---------- ----------
,2,8,6,5,54,100,67,7778,6,          9          6

Finally, we push the above SQL statement into an inline view, search for the specified number of commas according to SEL_ITEM column to determine the starting position of the element, and then search for the next comma to determine the ending position of the element:

SELECT
  ITEMS,
  SEL_ITEM,
  SUBSTR(ITEMS,INSTR(ITEMS,',',1,SEL_ITEM)+1,(INSTR(ITEMS,',', 1,SEL_ITEM+1)) - (INSTR(ITEMS,',',1,SEL_ITEM)) -1) ITEM
FROM
(SELECT
  ITEMS,
  SUM(SIGN(INSTR(ITEMS, ',',1,ROWNUM)))-1 NUM_ITEMS,
  (TRUNC(DBMS_RANDOM.VALUE(0,SUM(SIGN(INSTR(ITEMS ,',',1,ROWNUM)))-1)) +1) SEL_ITEM
FROM
  (SELECT
    ',2,8,6,5,54,100,67,7778,6,' ITEMS
  FROM
    DUAL)
CONNECT BY
  LEVEL<20);

ITEMS                        SEL_ITEM ITEM
-------------------------- ---------- ----
,2,8,6,5,54,100,67,7778,6,          6 100

You might also be able to do something like this:

SELECT
  '2,8,6,5,54,100,67,7778,6' ITEMS,
  DBMS_RANDOM.VALUE(0,1) PERCENT
FROM
  DUAL;

ITEMS                       PERCENT
------------------------ ----------
2,8,6,5,54,100,67,7778,6 .582165524

 

SELECT
  ROWNUM ITEM_NUMBER,
  REGEXP_SUBSTR(ITEMS,'\w+',1,LEVEL) ITEM,
  PERCENT
FROM
  (SELECT
    '2,8,6,5,54,100,67,7778,6' ITEMS,
    DBMS_RANDOM.VALUE(0,1) PERCENT
  FROM
    DUAL)
CONNECT BY
  REGEXP_SUBSTR(ITEMS,'\w+',1,LEVEL) IS NOT NULL;

ITEM_NUMBER ITEM     PERCENT
----------- ----- ----------
          1 2     .104480002
          2 8      .81670697
          3 6     .826051929
          4 5     .477132421
          5 54     .89077554
          6 100   .640842927
          7 67    .145088893
          8 7778  .252241096
          9 6     .490905924

As you can see from the above, we have a problem in that the random percent changes for each row, which will cause a problem for us if we try to use it in a WHERE clause.

SELECT
  MAX(ITEM_NUMBER) OVER () NUM_ITEMS,
  PERCENT_RANK() OVER (ORDER BY ITEM_NUMBER) PR,
  ITEM_NUMBER,
  ITEM,
  PERCENT
FROM
(SELECT
  ROWNUM ITEM_NUMBER,
  REGEXP_SUBSTR(ITEMS,'\w+',1,LEVEL) ITEM,
  PERCENT
FROM
  (SELECT
    '2,8,6,5,54,100,67,7778,6' ITEMS,
    DBMS_RANDOM.VALUE(0,1) PERCENT
  FROM
    DUAL)
CONNECT BY
  REGEXP_SUBSTR(ITEMS,'\w+',1,LEVEL) IS NOT NULL);

NUM_ITEMS         PR ITEM_NUMBER ITEM     PERCENT
--------- ---------- ----------- ----- ----------
        9          0           1 2     .110718377
        9       .125           2 8     .306241972
        9        .25           3 6     .953005936
        9       .375           4 5     .033518415
        9         .5           5 54    .803485415
        9       .625           6 100   .456278133
        9        .75           7 67     .04461405
        9       .875           8 7778  .249680394
        9          1           9 6     .484834331

If we now use a FIRST_VALUE analytic function, we could just retrieve
the first PERCENT value and use that in a WHERE clause (the PERCENT_RANK function was the start of another approach which was never developed):

SELECT
  NUM_ITEMS,
  ITEM_NUMBER,
  ITEM
FROM
(SELECT
  MAX(ITEM_NUMBER) OVER () NUM_ITEMS,
  ITEM_NUMBER,
  ITEM,
  FIRST_VALUE(PERCENT) OVER () PERCENT
FROM
(SELECT
  ROWNUM ITEM_NUMBER,
  REGEXP_SUBSTR(ITEMS,'\w+',1,LEVEL) ITEM,
  PERCENT
FROM
  (SELECT
    '2,8,6,5,54,100,67,7778,6' ITEMS,
    DBMS_RANDOM.VALUE(0,1) PERCENT
  FROM
    DUAL)
CONNECT BY
  REGEXP_SUBSTR(ITEMS,'\w+',1,LEVEL) IS NOT NULL))
WHERE
  ITEM_NUMBER=(TRUNC(NUM_ITEMS*PERCENT)+1);

NUM_ITEMS ITEM_NUMBER ITEM
--------- ----------- ----
        9           7 67

One more, which was originally based on my second solution, this time ordering the rows in random order:

SELECT
  ROWNUM ITEM_NUMBER,
  REGEXP_SUBSTR(ITEMS,'\w+',1,LEVEL) ITEM
FROM
  (SELECT
    '2,8,6,5,54,100,67,7778,6' ITEMS
  FROM
    DUAL)
CONNECT BY
  REGEXP_SUBSTR(ITEMS,'\w+',1,LEVEL) IS NOT NULL
ORDER BY
  DBMS_RANDOM.VALUE(0,1);

ITEM_NUMBER ITEM
----------- ----
          8 7778
          4 5
          9 6
          6 100
          5 54
          2 8
          7 67
          3 6
          1 2

Now, just slide the above into an inline view and retrieve just the first row:

SELECT
  ITEM_NUMBER,
  ITEM
FROM
(SELECT
  ROWNUM ITEM_NUMBER,
  REGEXP_SUBSTR(ITEMS,'\w+',1,LEVEL) ITEM
FROM
  (SELECT
    '2,8,6,5,54,100,67,7778,6' ITEMS
  FROM
    DUAL)
CONNECT BY
  REGEXP_SUBSTR(ITEMS,'\w+',1,LEVEL) IS NOT NULL
ORDER BY
  DBMS_RANDOM.VALUE(0,1))
WHERE
  ROWNUM=1;

ITEM_NUMBER ITEM
----------- ----
          6 100

There are probably a couple more ways to pick a random element.





SQL – Reformatting to Improve Performance 11

4 12 2009

December 4, 2009

(Back to the Previous Post in the Series)

Some time ago the following question appeared in a forum:
http://groups.google.com/group/oracle_dba_experts/browse_thread/thread/1194b893e71e2e15

After analyzing I have found that this query is taking a long time to
execute

    (
       select count(*) as cntFixed
         from mdl_parameter_tree u
         where u.prmid not in
        (
          select t.prmid
          from mdl_parameter_tree t
         where t.rootnode in
          (
           select b.rootnode
           from subjective_prm_trans a, mdl_parameter_tree b
           where
                a.mdlid = b.mdlid and a.prmid = b.prmid and
                a.endnodeflag = 'N' and a.value between 0.0001 and 1
and
                a.prmid not in (select r.prmid from mdl_parameter_tree
r where trim(lower(r.prmname)) = 'project risk' and r.mdlid=
&var_mdlid ) and
                a.borrid= &var_borrId and a.mdlid= &var_mdlid and
a.user_id= &var_user_id
               ) and t.endnodeflag = 'E' and parametertype = 'S' and
mdlid= &var_mdlid
)         and
        ( u.endnodeflag ='E' and
         u.parametertype = 'S' and
         u.mdlid= &var_mdlid

   )
)
This query is taking 58 seconds to execute.
Below is the explain plan of the above query

--------------------------------------------------------------------------­-------
| Id  | Operation                |  Name                 | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------­-------
|   0 | SELECT STATEMENT         |                       |     1 |   284 |   349 |
|   1 |  SORT AGGREGATE          |                       |     1 |   284 |       |
|   2 |   FILTER                 |                       |       |       |       |
|   3 |    TABLE ACCESS FULL     | MDL_PARAMETER_TREE    |     1 |   284 |     3 |
|   4 |    FILTER                |                       |       |       |       |
|   5 |     NESTED LOOPS         |                       |     1 |   636 |   343 |
|   6 |      MERGE JOIN CARTESIAN|                       |     1 |   481 |   340 |
|   7 |       TABLE ACCESS FULL  | SUBJECTIVE_PRM_TRANS  |     1 |    68 |   337 |
|   8 |       BUFFER SORT        |                       |     1 |   413 |     3 |
|   9 |        TABLE ACCESS FULL | MDL_PARAMETER_TREE    |     1 |   413 |     3 |
|  10 |      TABLE ACCESS FULL   | MDL_PARAMETER_TREE    |    12 |  1860 |     3 |
|  11 |     TABLE ACCESS FULL    | MDL_PARAMETER_TREE    |     1 |   155 |     3 |
--------------------------------------------------------------------------­-------

Note: cpu costing is off, 'PLAN_TABLE' is old version

Please assist me sir to tune the below query

—-
It is a good idea to simplify the SQL statement, if possible.  The following may not be 100% accurate, so test the results.

Your original SQL statement, slightly reformatted:

select
  count(*) as cntFixed
from
  mdl_parameter_tree u
where
  u.prmid not in (
    select
      t.prmid
    from
      mdl_parameter_tree t
    where
      t.rootnode in (
        select
          b.rootnode
        from
          subjective_prm_trans a,
          mdl_parameter_tree b
        where
          a.mdlid = b.mdlid
          and a.prmid = b.prmid
          and a.endnodeflag = 'N'
          and a.value between 0.0001 and 1
          and a.prmid not in (
            select
              r.prmid
            from
              mdl_parameter_tree r
           where
             trim(lower(r.prmname)) = 'project risk'
             and r.mdlid= &var_mdlid )
          and a.borrid= &var_borrId
          and a.mdlid= &var_mdlid
          and a.user_id= &var_user_id)
      and t.endnodeflag = 'E'
      and parametertype = 'S'
      and mdlid= &var_mdlid)
  and u.endnodeflag ='E'
  and u.parametertype = 'S'
  and u.mdlid= &var_mdlid;

Now, removing one of the subqueries, transforming it to <> and OR:

select
  count(*) as cntFixed
from
  mdl_parameter_tree u
where
  u.prmid not in (
    select
      t.prmid
    from
      mdl_parameter_tree t
    where
      t.rootnode in (
        select
          b.rootnode
        from
          subjective_prm_trans a,
          mdl_parameter_tree b
        where
          a.mdlid = b.mdlid
          and a.prmid = b.prmid
          and a.endnodeflag = 'N'
          and a.value between 0.0001 and 1
          and (trim(lower(r.prmname)) <> 'project risk' or r.mdlid <> &var_mdlid)
          and a.borrid= &var_borrId
          and a.mdlid= &var_mdlid
          and a.user_id= &var_user_id)
      and t.endnodeflag = 'E'
      and parametertype = 'S'
      and mdlid= &var_mdlid)
  and u.endnodeflag ='E'
  and u.parametertype = 'S'
  and u.mdlid= &var_mdlid;

Let’s look at what this SQL statement is attempting to accomplish: Count of rows in mdl_parameter_tree that do not have a rootnode in the inner-most subquery.
Let’s rewrite per the specification to use an inline view, rather than a subquery:

select
  count(*) as cntFixed
from
  mdl_parameter_tree u,
  (select
    b.prmid,
    b.rootnode
  from
    subjective_prm_trans a,
    mdl_parameter_tree b
  where
    a.mdlid = b.mdlid
    and a.prmid = b.prmid
    and a.endnodeflag = 'N'
    and a.value between 0.0001 and 1
    and (trim(lower(b.prmname)) <> 'project risk' or b.mdlid <> &var_mdlid)
    and a.borrid= &var_borrId
    and a.mdlid= &var_mdlid
    and a.user_id= &var_user_id) n
where
  u.prmid=n.prmid(+)
  and n.prmid is null;

Once again, verify that this is showing the same number of rows as the original.

If you are still experiencing a problem, create a 10046 trace at level 8 for the query.  On this page I show how to enable a 10046 trace at level 12, which will work just as well as a level 8 trace for you:
http://forums.oracle.com/forums/thread.jspa?messageID=2384639&

This link shows how to decode the contents of a 10046 trace file:
http://forums.oracle.com/forums/thread.jspa?threadID=661124





SQL – Reformatting to Improve Performance 10

4 12 2009

December 4, 2009

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

Some time ago the following question appeared in a forum:
http://groups.google.com/group/oracle_dba_experts/browse_thread/thread/8176f2571da1da3d

I wanted to tune the below query.Any help would be of great help. Below is the query

--Find Year to Generate Risk Score Starts here
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as year
 from statement
 where borrid= &var_borrId and user_id= &var_user_id and coaid = &var_mdlid
 and to_char(stmtdt, 'yyyy') in
 (
   select os.year from
   (
     select borrid, year, count(*) as cntActuals
     from subjective_prm_trans
     where borrid= &var_borrId and user_id= &var_user_id and mdlid = &var_mdlid and endnodeflag = 'E'
     group by year, borrid
   ) os,
   (
     select j.borrid, j.year, j.cntVariable + k.cntFixed as cntMdlTotals
     from
     (
       select a.borrid, a.year , count(*) as cntVariable
       from subjective_prm_trans a, mdl_Parameter_Tree m
       where
       a.prmid = m.parentid and a.mdlid = m.mdlid and
       a.endnodeflag = 'N' and a.value between 0.0001 and 1 and
       a.prmid not in (select r.prmid from mdl_parameter_tree r
    where
       trim(lower(r.prmname)) = 'project risk' and r.mdlid= &var_mdlid ) and
       a.borrid= &var_borrId and a.mdlid= &var_mdlid and a.user_id= &var_user_id
       group by a.borrid , a.year
     ) j,
     (
       select count(*) as cntFixed
         from mdl_parameter_tree u
         where u.prmid not in
        (
          select t.prmid
          from mdl_parameter_tree t
          where t.rootnode in
          (
           select b.rootnode
           from subjective_prm_trans a, mdl_parameter_tree b
           where
                a.mdlid = b.mdlid and a.prmid = b.prmid and
                a.endnodeflag = 'N' and a.value between 0.0001 and 1
                and a.prmid not in (select r.prmid from mdl_parameter_tree r
                  where trim(lower(r.prmname)) = 'project risk' and r.mdlid= &var_mdlid ) and
                a.borrid= &var_borrId and a.mdlid= &var_mdlid and
                a.user_id= &var_user_id ) and
          t.endnodeflag = 'E' and parametertype = 'S' and mdlid= &var_mdlid
        ) and
         u.endnodeflag ='E' and
         u.parametertype = 'S' and
         u.mdlid= &var_mdlid
     ) k
   ) om
   where
   os.borrid = om.borrid and
   os.year = om.year and
   os.cntActuals  = om.cntMdlTotals
 )
 order by year desc

Well below is the explain plan:

Execution Plan
----------------------------------------------------------
0     SELECT STATEMENT Optimizer=ALL_ROWS (Cost=28 Card=1 Bytes=61 )
1 0    SORT (ORDER BY) (Cost=28 Card=1 Bytes=61)
2 1     HASH JOIN (SEMI) (Cost=26 Card=1 Bytes=61)
3 2      TABLE ACCESS (FULL) OF 'STATEMENT' (Cost=2 Card=1 Bytes=48)
4 2      VIEW OF 'VW_NSO_1' (Cost=23 Card=1 Bytes=13)
5 4       NESTED LOOPS (Cost=23 Card=1 Bytes=91)
6 5        HASH JOIN (Cost=13 Card=1 Bytes=78)
7 6         VIEW (Cost=4 Card=1 Bytes=39)
8 7          SORT (GROUP BY) (Cost=4 Card=1 Bytes=55)
9 8           TABLE ACCESS (FULL) OF 'SUBJECTIVE_PRM_TRANS' (Cost=2 Card=1 Bytes=55)
10 6        VIEW (Cost=9 Card=1 Bytes=39)
11 10        SORT (GROUP BY) (Cost=7 Card=1 Bytes=107)
12 11         FILTER
13 12          HASH JOIN (Cost=5 Card=1 Bytes=107)
14 13           TABLE ACCESS (FULL) OF 'SUBJECTIVE_PRM_TRANS' (Cost=2 Card=1 Bytes=81)
15 13           TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=2 Card=142 Bytes=3692)
16 12          TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=2 Card=1 Bytes=155)
17 5       VIEW
18 17       SORT (AGGREGATE)
19 18        FILTER
20 19         TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=2 Card=1 Bytes=284)
21 19         FILTER
22 21          NESTED LOOPS (Cost=6 Card=1 Bytes=636)
23 22           MERGE JOIN (CARTESIAN) (Cost=4 Card=1 By tes=481)
24 23            TABLE ACCESS (FULL) OF 'SUBJECTIVE_PRM_TRANS' (Cost=2 Card=1 Bytes=68)
25 23            BUFFER (SORT) (Cost=2 Card=1 Bytes=413)
26 25             TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=2 Card=1 Bytes=413)
27 22           TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=2 Card=18 Bytes=2790)
28 21          TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=2 Card=1 Bytes=155)

 

Statistics
----------------------------------------------------------
18 recursive calls
0 db block gets
387229 consistent gets
306954 physical reads
0 redo size
432 bytes sent via SQL*Net to client
584 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
114 sorts (memory)
0 sorts (disk)
1 rows processed

Any help would help to resolve the issue

———-
David has already mentioned that your use of “and to_char(stmtdt, ‘yyyy’)” makes it impossible to use a regular index on the stmtdt column, if one exists.

Looking at the plan, you may notice that there is not a single index access, the cardinality numbers are low (possibily indicating that it has been a while since statistics were gathered), there is a merge join Cartesian which can kill performance with large numbers of rows, the formatting without spaces makes it impossible to see how the data was retrieved in order, and it appears that you are using a version of Oracle prior to 10g R1 based on the presence of “SORT (GROUP BY)”. That may be an indication that Oracle is converting or is not converting some/all of the NOT IN subqueries as necessary.

Looking at the query, slightly reformatted:

SELECT
  TO_CHAR(STMTDT, 'YYYY') AS ID, TO_CHAR(STMTDT, 'YYYY') AS YEAR
FROM
  STATEMENT
WHERE
  BORRID= &VAR_BORRID
  AND USER_ID= &VAR_USER_ID
  AND COAID = &VAR_MDLID
  AND TO_CHAR(STMTDT, 'YYYY') IN
  (
   SELECT
     OS.YEAR
   FROM
   (
     SELECT
       BORRID,
       YEAR,
       COUNT(*) AS CNTACTUALS
     FROM
       SUBJECTIVE_PRM_TRANS
     WHERE
       BORRID= &VAR_BORRID
       AND USER_ID= &VAR_USER_ID
       AND MDLID = &VAR_MDLID
       AND ENDNODEFLAG = 'E'
     GROUP BY
       YEAR,
       BORRID) OS,
   (
     SELECT
       J.BORRID,
       J.YEAR,
       J.CNTVARIABLE + K.CNTFIXED AS CNTMDLTOTALS
     FROM
     (
       SELECT
         A.BORRID,
         A.YEAR ,
         COUNT(*) AS CNTVARIABLE
       FROM
         SUBJECTIVE_PRM_TRANS A,
         MDL_PARAMETER_TREE M
       WHERE
         A.PRMID = M.PARENTID
         AND A.MDLID = M.MDLID
         AND A.ENDNODEFLAG = 'N'
         AND A.VALUE BETWEEN 0.0001 AND 1
         AND A.PRMID NOT IN
           (SELECT
             R.PRMID
           FROM
             MDL_PARAMETER_TREE R
           WHERE
             TRIM(LOWER(R.PRMNAME)) = 'PROJECT RISK'
             AND R.MDLID= &VAR_MDLID )
         AND A.BORRID= &VAR_BORRID
         AND A.MDLID= &VAR_MDLID
         AND A.USER_ID= &VAR_USER_ID
       GROUP BY
         A.BORRID ,
         A.YEAR ) J,
     (
       SELECT
         COUNT(*) AS CNTFIXED
       FROM
         MDL_PARAMETER_TREE U
       WHERE
         U.PRMID NOT IN
           (SELECT
             T.PRMID
           FROM
             MDL_PARAMETER_TREE T
           WHERE
             T.ROOTNODE IN
               (SELECT
                 B.ROOTNODE
               FROM
                 SUBJECTIVE_PRM_TRANS A,
                 MDL_PARAMETER_TREE B
               WHERE
                 A.MDLID = B.MDLID
                 AND A.PRMID = B.PRMID
                 AND A.ENDNODEFLAG = 'N' AND A.VALUE BETWEEN 0.0001 AND 1
                 AND A.PRMID NOT IN
                   (SELECT
                     R.PRMID
                   FROM
                     MDL_PARAMETER_TREE R
                   WHERE
                     TRIM(LOWER(R.PRMNAME)) = 'PROJECT RISK'
                    AND R.MDLID= &VAR_MDLID )
             AND A.BORRID= &VAR_BORRID
             AND A.MDLID= &VAR_MDLID
             AND A.USER_ID= &VAR_USER_ID
          )
         AND T.ENDNODEFLAG = 'E'
         AND PARAMETERTYPE = 'S'
         AND MDLID= &VAR_MDLID
        )
       AND U.ENDNODEFLAG ='E'
       AND U.PARAMETERTYPE = 'S'
       AND U.MDLID= &VAR_MDLID
     ) K
   ) OM
   WHERE
     OS.BORRID = OM.BORRID
     AND OS.YEAR = OM.YEAR
     AND OS.CNTACTUALS  = OM.CNTMDLTOTALS
 )
ORDER BY
  YEAR DESC

You seem to be repeating the same inline view SQL in a couple places, you seem to be performing a COUNT in two inline views so that you can perform “AND OS.CNTACTUALS  = OM.CNTMDLTOTALS” – there might be a better way.  Where you have “SELECT OS.YEAR” – you may want to convert this to 2 date columns with Jan 1 of the year in the first date column and Dec 31 of the year in the second – doing that would allow you to change “TO_CHAR(STMTDT, ‘YYYY’) IN” into a statement that does not require TO_CHAR.

It will take someone familiar with the data to tell you if some of the extra work in the inline views may be eliminated.

A follow-up email to the OP:
There are some basic problems that I found in the query and the explain plan that should be fixed before trying David’s suggestions for a materialed view.  I suggested the following:
* There are no indexes used in the query, which may be part or most of the performance problem – verify that indexes exist and statistics are gathered for those indexes.
* The cardinality numbers are low (possibily indicating that it has been a while since statistics were gathered) – make certain that statistics are gathered with DBMS_STATS.
* There is a merge join Cartesian in the plan which can severely hurt performance with large numbers of rows – you may need a hint to fix this.
* The formatting of the plan without spaces makes it impossible to see how the data was retrieved by the plan.
* You seem to be using a version of Oracle prior to 10g R1, and those versions may not convert/change/transform some/all of the NOT IN subqueries as necessary for best performance.
* You seem to be repeating the same inline view SQL in a couple places.
* You seem to be performing a COUNT in two inline views so that you can perform “AND OS.CNTACTUALS  = OM.CNTMDLTOTALS” – there might be a better way.
* Where you have “SELECT OS.YEAR” – you may want to convert this to 2 date columns with Jan 1 of the year in the first date column and Dec 31 of the year in the second – doing that would allow you to change “TO_CHAR(STMTDT, ‘YYYY’) IN” into a statement that does not require TO_CHAR.
* It will take someone familiar with the data to tell you if some of the extra work in the inline views may be eliminated.





SQL – Reformatting to Improve Performance 9

4 12 2009

December 4, 2009

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

A couple months ago  an interesting/huge/confusing SQL statement appeared on the OTN forums that was experiencing performance problems when running on Oracle 8i.  The SQL statement contained several very complex inline view, with inline views nested inside other inline views:

http://forums.oracle.com/forums/thread.jspa?messageID=3802738

I offered the following suggestions to the person:

That is a long SQL statement. Splitting the SQL statement up into multiple views likely will not help performance, but could hurt performance.

A couple comments about the SQL statement:
* “SELECT DISTINCT” repeated in many places, including in an inline view using a GROUP BY clause. Are all of the DISTINCT clauses necessary?

* ORDER BY is frequently used in the inline views: “order by group_name” in the T11 inline view, “order by groupname” in the T6 inline view, “order by groupname” in the T3 inline view, “order by linecode” in the T5 inline view. Remove those ORDER BY clauses.

* “UNION” is used in T3 inline view – is it possible to replace that with a “UNION ALL”?

* IN clauses may be inefficient in Oracle 8i, consider switching to an EXISTS clause or transforming the IN clause to an inline view.

* Odd LIKE syntax in several places: where “groupname like ‘%’ and hub like ‘%'”, “lr.groupname like ‘%'” in T1 inline view, “lr.groupname like ‘%'” in T2 inline view.

* The table autoloadflow_lineresults is accessed many times – is that a large table? Is it possible to consolidate the queries accessing that table?

* The WHERE clauses seem to limit the usefulness of any indexes which may exist.

* It appears that you want the results from the T6 inline view (which join the T1, T2, T3, T4, and T5 inline views) to drive into the T7, T8, T9, T10, and T11 inline views – does the execution plan show that is happening?





SQL – Reformatting to Improve Performance 8

4 12 2009

December 4, 2009

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

Some time ago the following question appeared in a forum:
http://groups.google.com/group/oracle_dba_experts/browse_thread/thread/deb2e3f18be836c0

I am using Oracle 9i.
I have some reports developed in Reports 6i which run very slow.
When I run the query in TOAD, it’s taking too much time for displaying
result.
Can anybody suggest me how to tune this query ?

SELECT DISTINCT c.AccName, SUBSTR (a.VouNo, 3) VouNo,
                a.VouDate, a.TranType, b.ChequeNo,
                b.ChequeDate,
                DECODE (BalType, 'Debit', Amount, 0) Debit,
                DECODE (BalType, 'Credit', Amount, 0) Credit,
                d.DocNo, d.DocDate, a.Remark
           FROM TranDetails a,
                ChequeDetails b,
                AccMaster c,
                TranDocDetails d
          WHERE c.AccCode <> :p_BankAccCode
            AND a.VouDate BETWEEN :st_date AND :end_date
            AND a.CompanyNo = c.CompanyNo
            AND a.CompanyNo = b.CompanyNo(+)
            AND a.VouNo = b.VouNo(+)
            AND a.VouDate = b.VouDate(+)
            AND a.nu_serial_no = b.nu_serial_no(+)
            AND a.nu_serial_no = d.nu_serial_no(+)
            AND a.AccCode = c.AccCode
            AND a.TranType IN ('JV', 'BR', 'BP', 'CN', , 'CR', 'CP')
            AND NVL (a.SecFlag, 'N') = 'Y'
            AND d.VouNo(+) = a.VouNo
            AND d.VouDate(+) = a.VouDate
            AND c.CompanyNo = :p_CompanyNo
            AND d.CompanyNo(+) = :p_CompanyNo
            AND    a.CompanyNo
                || a.VouNo
                || TO_CHAR (a.VouDate, 'dd-mm-yyyy') IN (
                   SELECT    d.CompanyNo
                          || d.VouNo
                          || TO_CHAR (d.VouDate, 'dd-mm-yyyy')
                     FROM TranDetails d
                    WHERE d.AccCode = :p_BankAccCode
                      AND NVL (d.SecFlag, 'N') = 'Y'
                      AND d.CompanyNo = :p_CompanyNo)
       ORDER BY 3, 2

The Oracle version number (for example 9.2.0.4) is important, as it determines what types of transformations Oracle may use to help improve the query performance.  First, a slight change in the formatting to make it easier for me to read (you will need to fix the DECODE statements):

SELECT DISTINCT
  AM.ACCNAME,
  SUBSTR (TD.VOUNO, 3) VOUNO,
  TD.VOUDATE,
  TD.TRANTYPE,
  CD.CHEQUENO,
  CD.CHEQUEDATE,
  DECODE (BALTYPE, 'DEBIT', AMOUNT, 0) DEBIT,
  DECODE (BALTYPE, 'CREDIT', AMOUNT, 0) CREDIT,
  TDD.DOCNO,
  TDD.DOCDATE,
  TD.REMARK
FROM
  TRANDETAILS TD,
  CHEQUEDETAILS CD,
  ACCMASTER AM,
  TRANDOCDETAILS TDD
WHERE
  AM.ACCCODE <> :V_BANKACCCODE
  AND TD.VOUDATE BETWEEN :ST_DATE AND :END_DATE
  AND TD.COMPANYNO = AM.COMPANYNO
  AND TD.COMPANYNO = CD.COMPANYNO(+)
  AND TD.VOUNO = CD.VOUNO(+)
  AND TD.VOUDATE = CD.VOUDATE(+)
  AND TD.NU_SERIAL_NO = CD.NU_SERIAL_NO(+)
  AND TD.NU_SERIAL_NO = TDD.NU_SERIAL_NO(+)
  AND TD.ACCCODE = AM.ACCCODE
  AND TD.TRANTYPE IN ('JV', 'BR', 'BP', 'CN', , 'CR', 'CP')
  AND NVL (TD.SECFLAG, 'N') = 'Y'
  AND TDD.VOUNO(+) = TD.VOUNO
  AND TDD.VOUDATE(+) = TD.VOUDATE
  AND AM.COMPANYNO = :V_COMPANYNO
  AND TDD.COMPANYNO(+) = :V_COMPANYNO
  AND TD.COMPANYNO || TD.VOUNO || TO_CHAR (TD.VOUDATE, 'DD-MM-YYYY') IN
    (SELECT
      TD2.COMPANYNO || TD2.VOUNO || TO_CHAR (TD2.VOUDATE, 'DD-MM-YYYY')
    FROM
      TRANDETAILS TD2
    WHERE
      TD2.ACCCODE = :V_BANKACCCODE
      AND NVL (TD2.SECFLAG, 'N') = 'Y'
      AND TD2.COMPANYNO = :V_COMPANYNO)
ORDER BY
  3,
  2;

In the above, you are concatenating three columns into a single value, and then trying to determine if the same concatenated value exists in a table.  A DBMS_XPLAN would probably show many, many full table scans of the TRANSDETAILS table.

If we just list the three columns, and use TRUNC rather than TO_CHAR, we may be able to take advantage of an index on those columns, and avoid the CPU overhead of a data type conversion of a DATE column to a character data type.  You could use an EXISTS syntax instead, which might be more efficient (not shown):

SELECT DISTINCT
  AM.ACCNAME,
  SUBSTR (TD.VOUNO, 3) VOUNO,
  TD.VOUDATE,
  TD.TRANTYPE,
  CD.CHEQUENO,
  CD.CHEQUEDATE,
  DECODE (BALTYPE, 'DEBIT', AMOUNT, 0) DEBIT,
  DECODE (BALTYPE, 'CREDIT', AMOUNT, 0) CREDIT,
  TDD.DOCNO,
  TDD.DOCDATE,
  TD.REMARK
FROM
  TRANDETAILS TD,
  CHEQUEDETAILS CD,
  ACCMASTER AM,
  TRANDOCDETAILS TDD
WHERE
  AM.ACCCODE <> :V_BANKACCCODE
  AND TD.VOUDATE BETWEEN :ST_DATE AND :END_DATE
  AND TD.COMPANYNO = AM.COMPANYNO
  AND TD.COMPANYNO = CD.COMPANYNO(+)
  AND TD.VOUNO = CD.VOUNO(+)
  AND TD.VOUDATE = CD.VOUDATE(+)
  AND TD.NU_SERIAL_NO = CD.NU_SERIAL_NO(+)
  AND TD.NU_SERIAL_NO = TDD.NU_SERIAL_NO(+)
  AND TD.ACCCODE = AM.ACCCODE
  AND TD.TRANTYPE IN ('JV', 'BR', 'BP', 'CN', , 'CR', 'CP')
  AND NVL (TD.SECFLAG, 'N') = 'Y'
  AND TDD.VOUNO(+) = TD.VOUNO
  AND TDD.VOUDATE(+) = TD.VOUDATE
  AND AM.COMPANYNO = :V_COMPANYNO
  AND TDD.COMPANYNO(+) = :V_COMPANYNO
  AND (TD.COMPANYNO,TD.VOUNO,TRUNC(TD.VOUDATE)) IN
    (SELECT
      TD2.COMPANYNO,
      TD2.VOUNO,
      TRUNC(TD2.VOUDATE)
    FROM
      TRANDETAILS TD2
    WHERE
      TD2.ACCCODE = :V_BANKACCCODE
      AND NVL (TD2.SECFLAG, 'N') = 'Y'
      AND TD2.COMPANYNO = :V_COMPANYNO)
ORDER BY
  3,
  2;

Let’s transform the subquery into an inline view, which will sometimes help improve performance (usually a very noticeable on Oracle 8i).  Such a transformation may be performed by Oracle automatically:

SELECT DISTINCT
  AM.ACCNAME,
  SUBSTR (TD.VOUNO, 3) VOUNO,
  TD.VOUDATE,
  TD.TRANTYPE,
  CD.CHEQUENO,
  CD.CHEQUEDATE,
  DECODE (BALTYPE, 'DEBIT', AMOUNT, 0) DEBIT,
  DECODE (BALTYPE, 'CREDIT', AMOUNT, 0) CREDIT,
  TDD.DOCNO,
  TDD.DOCDATE,
  TD.REMARK
FROM
  TRANDETAILS TD,
  CHEQUEDETAILS CD,
  ACCMASTER AM,
  TRANDOCDETAILS TDD,
  (SELECT DISTINCT
    TD2.COMPANYNO,
    TD2.VOUNO,
    TRUNC(TD2.VOUDATE) VOUDATE
  FROM
    TRANDETAILS TD2
  WHERE
    TD2.ACCCODE = :V_BANKACCCODE
    AND NVL (TD2.SECFLAG, 'N') = 'Y'
    AND TD2.COMPANYNO = :V_COMPANYNO) TD2
WHERE
  AM.ACCCODE <> :V_BANKACCCODE
  AND TD.VOUDATE BETWEEN :ST_DATE AND :END_DATE
  AND TD.COMPANYNO = AM.COMPANYNO
  AND TD.COMPANYNO = CD.COMPANYNO(+)
  AND TD.VOUNO = CD.VOUNO(+)
  AND TD.VOUDATE = CD.VOUDATE(+)
  AND TD.NU_SERIAL_NO = CD.NU_SERIAL_NO(+)
  AND TD.NU_SERIAL_NO = TDD.NU_SERIAL_NO(+)
  AND TD.ACCCODE = AM.ACCCODE
  AND TD.TRANTYPE IN ('JV', 'BR', 'BP', 'CN', , 'CR', 'CP')
  AND NVL (TD.SECFLAG, 'N') = 'Y'
  AND TDD.VOUNO(+) = TD.VOUNO
  AND TDD.VOUDATE(+) = TD.VOUDATE
  AND AM.COMPANYNO = :V_COMPANYNO
  AND TDD.COMPANYNO(+) = :V_COMPANYNO
  AND TD.COMPANYNO=TD2.COMPANYNO
  AND TD.VOUNO=TD2.VOUNO
  AND TRUNC(TD.VOUDATE)=TD2.VOUDATE
ORDER BY
  3,
  2;

Is it really necessary to reference the TRANDETAILS table twice?  Will the following work?:

SELECT DISTINCT
  AM.ACCNAME,
  SUBSTR (TD.VOUNO, 3) VOUNO,
  TD.VOUDATE,
  TD.TRANTYPE,
  CD.CHEQUENO,
  CD.CHEQUEDATE,
  DECODE (BALTYPE, 'DEBIT', AMOUNT, 0) DEBIT,
  DECODE (BALTYPE, 'CREDIT', AMOUNT, 0) CREDIT,
  TDD.DOCNO,
  TDD.DOCDATE,
  TD.REMARK
FROM
  TRANDETAILS TD,
  CHEQUEDETAILS CD,
  ACCMASTER AM,
  TRANDOCDETAILS TDD
WHERE
  AM.ACCCODE <> :V_BANKACCCODE
  AND TD.VOUDATE BETWEEN :ST_DATE AND :END_DATE
  AND TD.COMPANYNO = AM.COMPANYNO
  AND TD.COMPANYNO = CD.COMPANYNO(+)
  AND TD.VOUNO = CD.VOUNO(+)
  AND TD.VOUDATE = CD.VOUDATE(+)
  AND TD.NU_SERIAL_NO = CD.NU_SERIAL_NO(+)
  AND TD.NU_SERIAL_NO = TDD.NU_SERIAL_NO(+)
  AND TD.ACCCODE = AM.ACCCODE
  AND TD.TRANTYPE IN ('JV', 'BR', 'BP', 'CN', , 'CR', 'CP')
  AND NVL (TD.SECFLAG, 'N') = 'Y'
  AND TDD.VOUNO(+) = TD.VOUNO
  AND TDD.VOUDATE(+) = TD.VOUDATE
  AND AM.COMPANYNO = :V_COMPANYNO
  AND TDD.COMPANYNO(+) = :V_COMPANYNO
  AND TD.ACCCODE = :V_BANKACCCODE
  AND NVL (TD.SECFLAG, 'N') = 'Y'
  AND TD.COMPANYNO = :V_COMPANYNO
ORDER BY
  3,
  2;

Why do you need to use the DISTINCT clause – is that a sign that you do not have sufficient joins between the tables?  It might be helpful to post a DBMS_XPLAN for the query.





SQL – Reformatting to Improve Performance 7

4 12 2009

December 4, 2009

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

Some time ago the following question appeared in a forum:
http://groups.google.com/group/comp.databases.oracle.misc/browse_thread/thread/7780f1fd94c03ced

I have been challenged to optimize a stored procedure on Oracle 9i that will return a ref cursor with the counts of each status for each batch from the tables defined below.  I started with a View to join the tables together on the OrderNo field.  Then I wrote a query in the stored procdure that grouped the Batch values together, and then did a count for each Status value in each Batch like this:

SELECT Batch,
COUNT(Batch) Total,
(SELECT  COUNT(Batch)
        FROM MYVIEW
        WHERE Status = 1
                AND Batch = V1.Batch
(SELECT  COUNT(Batch)
        FROM MYVIEW
        WHERE Status = 2
                AND Batch = V1.Batch
(SELECT  COUNT(Batch)
        FROM MYVIEW
        WHERE Status = 3
                AND Batch = V1.Batch
FROM MYVIEW V1
WHERE Status < 4
        GROUP BY Batch;

With 42 unique Batch values and 26,000 OrderNo values my dev server takes over 5 seconds.  I built the query without the view and added the joins in the query and took the same amount of time.  I can’t change the table structures but the view is wide open. This is a conversion project from MS SQL and this same data returns from MS SQL in 0.09 seconds.  I hope Oracle can beat MS

Suporting Data and table structures.

TableOne Definition:

 
Batch - VarChar
OrderNo - VarChar
Various other Columns of Data...

TableTwo Definition:

OrderNo - VarChar
Status - Number
Various other Columns of Data...

View Definition:

Select o.Batch, o.OrderNo, t.Status, ...
FROM TableOne o Left Outer Join TableTwo t
                on o.OrderNo = t.OrderNo;

Sample Data TableOne:

ABC1    123     ...
ABC1  234       ...
ABC1    345     ...
ABC1  456       ...
ABC2    567     ...
ABC2  678       ...
ABC2    789     ...
ABC2  321       ...
ABC3    432     ...
ABC4  543       ...

Sample Data TableTwo:

123     1       ...
234     1       ...
345     2       ...
456     3       ...
567     2       ...
678     1       ...
789     2       ...
321     2       ...
432     3       ...
543     3       ...

Expected Return Ref Cursor:

ABC1    4       2       1       3
ABC2    4       1       3       null
ABC3    1       null    null    1
ABC4    1       null    1       null

Reduce it to a simple SQL statement using DECODE.  If the STATUS is the expected value (1, 2, 3) for the column, return 1, otherwise return NULL.  Then count the non-null return values.  COUNT will only count non-null values:

SELECT
  BATCH,
  COUNT(BATCH) TOTAL,
  COUNT(DECODE(STATUS,1,1,NULL)) TOTAL_1,
  COUNT(DECODE(STATUS,2,1,NULL)) TOTAL_2,
  COUNT(DECODE(STATUS,3,1,NULL)) TOTAL_3
FROM
  MYVIEW V1
WHERE
  STATUS < 4
GROUP BY
  BATCH;

It appears that if COUNT returns 0, that you want NULL to be returned rather than 0.  Once again, use DECODE to convert 0 to NULL and all other values to the original formula:

SELECT
  BATCH,
  COUNT(BATCH) TOTAL,
  DECODE(COUNT(DECODE(STATUS,1,1,NULL)),0,NULL,COUNT(DECODE(STATUS,1,1,NULL))) TOTAL_1,
  DECODE(COUNT(DECODE(STATUS,2,1,NULL)),0,NULL,COUNT(DECODE(STATUS,2,1,NULL))) TOTAL_2,
  DECODE(COUNT(DECODE(STATUS,3,1,NULL)),0,COUNT(DECODE(STATUS,3,1,NULL))) TOTAL_3
FROM
  MYVIEW V1
WHERE
  STATUS < 4
GROUP BY
  BATCH;

The inclusion of STATUS < 4 leads me to believe that you are attempting to reuse a view definition for a different task.  Doing so may lead to performance issues.  It may be a good idea to eliminate the view and directly reference the exact SQL statement of interest.





SQL – Reformatting to Improve Performance 6

4 12 2009

December 4, 2009

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

Some time ago the following question appeared in a forum:
http://groups.google.com/group/comp.databases.oracle.misc/browse_thread/thread/c28103fe283b7551

I am learning SQL (trial by fire) and have a question regarding a query optimization. Lets say I have three tables ‘project’,  ‘notes’ & ‘reminder’. Key on ‘project’ is ‘proj_id’. The other two tables reference this key as ‘notes’ contains note entries on a given project and ‘reminder’ tracks todo type and due dates.

select count(a.proj_id)
from project a
where a.stat_cd = 'CLOSED'
and  exists
      (
      select b.proj_id
      from reminder b
      where b.rem_type = 'LAST'
      and a.proj_id = b.proj_id
      )
and exists
      (
      select c.proj_id
      from notes c
      where c.note_type = 'QA'
      and a.proj_id = c.proj_id
      )

I am trying to determine the number of projects that have a ‘CLOSED’ status, contain a reminder type of ‘LAST’ and has a note type of ‘QA’

I get the result I am looking for but these are very large tables and it takes over 30 minutes for this to run. Is there a better way to write this query? I’m betting there is. Thank you in advance for your advice.

Give this re-write a try to see if it improves performance – I have essentially moved the exists statements into two inline views (some versions of Oracle may automatically perform such transformations):

SELECT
  COUNT(A.PROJ_ID)
FROM
  PROJECT A,
  (SELECT DISTINCT
    B.PROJ_ID
  FROM
    REMINDER B
  WHERE
    B.REM_TYPE = 'LAST') B,
  (SELECT DISTINCT
    C.PROJ_ID
  FROM
    NOTES C
  WHERE
    C.NOTE_TYPE = 'QA') C
WHERE
  A.STAT_CD = 'CLOSED'
  AND A.PROJ_ID = B.PROJ_ID
  AND A.PROJ_ID = C.PROJ_ID;

Very likely, you will need an index on the PROJ_ID column for each table, especially if the REMINDER and NOTES tables contain columns that are wide.  An index on REMINDER.REM_TYPE might help.  An index on NOTES.NOTE_TYPE also might help.  Take a look at the explain plan (preferrably DBMS_XPLAN) for your query, and compare it to the explain plan for the one above.  Also, make certain that the tables and indexes are analyzed (use DBMS_STATS for Oracle 8i and above).

It is important to keep in mind that the two solutions provided may or may not yield the same results.  This will be evident in those cases where there is more than one row in REMINDER for a PROJ_ID WHERE REM_TYPE = ‘LAST’, and in cases where there is more than one row in NOTES for a PROJ_ID WHERE NOTE_TYPE = ‘QA’.

To work around the above issue, if it may occur, modify AlterEgo’s solution:

select count(a.projectid)

Can be modified as (assuming that PROJECTID is the primary key of the
PROJECT table):

SELECT
  COUNT(DISTINCT A.PROJECTID)

With the above change, you may find that AlterEgo’s SQL statement executes slightly faster than the solution that I provided (this may be Oracle version dependent).

One final note.  When building SQL statements with aliases, it is easier to troubleshoot problems with the SQL statements if the alias name is somehow related to the object name that it represents:
Rather than using:

  PROJECT A,
  REMINDER B,
  NOTES C

I would use:

  PROJECT P,
  REMINDER R,
  NOTES N




SQL – Reformatting to Improve Performance 5

4 12 2009

December 4, 2009

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

Some time ago the following question appeared in a forum:
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/97995c4a0e697539

Suppose I have a database table with 20 fields which are lookups to a single table.

configtable(configtable_id, a_field, something_lookup_id,
another_lookup_id, ...)
lookup(lookup_id, value, description, ...)

what is going to be faster to map the rows to an Object which needs the ‘value’ for every field ending in lookup_id

a) View

select c.configtable_id, l1.value as something_lookup, l2.value as
another_lookup
from configtable c,
       lookup l1,
       lookup l2
where c.something_lookup_id = l1.lookup_id
     and c.another_lookup_id = l2.lookup_id

foreach row
   map values to object
end

b) Cache all lookup values and populate

select c.* from configtable
foreach row
   map values to object
   if lookup_id
       find value from hashtable and map value to object
   endif
end

It seems that the latter *might* be better to scale outward better, as you could add application servers to do the caching/mapping and you only select from a single table?

——————————–
It might be a good idea to perform some testing with artifical data to see what happens when the size of the data set increases.  Performance wise, it is generally best to perform as much processing of data as possible on the database server, and return few result rows, compared to returning a large result set and performing the analysis outside the database.

To help you set up a test environment (T1 is your configtable):

CREATE TABLE T1(
  CONFIGTABLE_ID NUMBER(12),
  A_FIELD VARCHAR2(15),
  LOOKUP1 VARCHAR2(15),
  LOOKUP2 VARCHAR2(15),
  LOOKUP3 VARCHAR2(15),
  LOOKUP4 VARCHAR2(15),
  LOOKUP5 VARCHAR2(15),
  LOOKUP6 VARCHAR2(15),
  LOOKUP7 VARCHAR2(15),
  LOOKUP8 VARCHAR2(15),
  LOOKUP9 VARCHAR2(15),
  LOOKUP10 VARCHAR2(15),
  LOOKUP11 VARCHAR2(15),
  LOOKUP12 VARCHAR2(15),
  LOOKUP13 VARCHAR2(15),
  LOOKUP14 VARCHAR2(15),
  LOOKUP15 VARCHAR2(15),
  LOOKUP16 VARCHAR2(15),
  LOOKUP17 VARCHAR2(15),
  LOOKUP18 VARCHAR2(15),
  LOOKUP19 VARCHAR2(15),
  LOOKUP20 VARCHAR2(15),
  PRIMARY KEY(CONFIGTABLE_ID));

T2 is your lookup table:

CREATE TABLE T2(
  LOOKUP_ID VARCHAR2(15),
  VALUE NUMBER(12),
  DESCRIPTION VARCHAR2(30));

Note that I _forgot_ to declare a primary key on T2, so there is no index.

Now, let’s generate 10,000 rows of data in the T1 table, but don’t make the lookup columns too random – only three of the 15 characters in the lookup columns is permitted to be random:

INSERT INTO
  T1
SELECT
  ROWNUM CONFIGTABLE_ID,
  DBMS_RANDOM.STRING('A',15) A_FIELD,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP1,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP2,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP3,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP4,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP5,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP6,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP7,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP8,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP9,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP10,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP11,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP12,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP13,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP14,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP15,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP16,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP17,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP18,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP19,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP20
FROM
  DUAL
CONNECT BY
  LEVEL<=100000;

Let’s generate the data for the T2 table (your lookup table) using the distinct values in the 20 lookup columns of T1:

INSERT INTO
  T2
SELECT
  L LOOKUP_ID,
  TRUNC(DBMS_RANDOM.VALUE(1,100000)) VALUE,
  DBMS_RANDOM.STRING('A',25) DESCRIPTION
FROM
(SELECT
  LOOKUP1 L
FROM
  T1
UNION
SELECT
  LOOKUP2 L
FROM
  T1
UNION
SELECT
  LOOKUP3 L
FROM
  T1
UNION
SELECT
  LOOKUP4 L
FROM
  T1
UNION
SELECT
  LOOKUP5 L
FROM
  T1
UNION
SELECT
  LOOKUP6 L
FROM
  T1
UNION
SELECT
  LOOKUP7 L
FROM
  T1
UNION
SELECT
  LOOKUP8 L
FROM
  T1
UNION
SELECT
  LOOKUP9 L
FROM
  T1
UNION
SELECT
  LOOKUP10 L
FROM
  T1
UNION
SELECT
  LOOKUP11 L
FROM
  T1
UNION
SELECT
  LOOKUP12 L
FROM
  T1
UNION
SELECT
  LOOKUP13 L
FROM
  T1
UNION
SELECT
  LOOKUP14 L
FROM
  T1
UNION
SELECT
  LOOKUP15 L
FROM
  T1
UNION
SELECT
  LOOKUP16 L
FROM
  T1
UNION
SELECT
  LOOKUP17 L
FROM
  T1
UNION
SELECT
  LOOKUP18 L
FROM
  T1
UNION
SELECT
  LOOKUP19 L
FROM
  T1
UNION
SELECT
  LOOKUP20 L
FROM
  T1);
COMMIT;

Gather statistics on the two tables:

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

SQL statement test 1, with 14 instances of the T2 lookup table:

SELECT
  C.CONFIGTABLE_ID,
  L1.VALUE L1_VALUE,
  L2.VALUE L2_VALUE,
  L3.VALUE L3_VALUE,
  L4.VALUE L4_VALUE,
  L5.VALUE L5_VALUE,
  L6.VALUE L6_VALUE,
  L7.VALUE L7_VALUE,
  L8.VALUE L8_VALUE,
  L9.VALUE L9_VALUE,
  L10.VALUE L10_VALUE,
  L11.VALUE L11_VALUE,
  L12.VALUE L12_VALUE,
  L13.VALUE L13_VALUE,
  L14.VALUE L14_VALUE
FROM
  T1 C,
  T2 L1,
  T2 L2,
  T2 L3,
  T2 L4,
  T2 L5,
  T2 L6,
  T2 L7,
  T2 L8,
  T2 L9,
  T2 L10,
  T2 L11,
  T2 L12,
  T2 L13,
  T2 L14
WHERE
  C.LOOKUP1=L1.LOOKUP_ID
  AND C.LOOKUP2=L2.LOOKUP_ID
  AND C.LOOKUP3=L3.LOOKUP_ID
  AND C.LOOKUP4=L4.LOOKUP_ID
  AND C.LOOKUP5=L5.LOOKUP_ID
  AND C.LOOKUP6=L6.LOOKUP_ID
  AND C.LOOKUP7=L7.LOOKUP_ID
  AND C.LOOKUP8=L8.LOOKUP_ID
  AND C.LOOKUP9=L9.LOOKUP_ID
  AND C.LOOKUP10=L10.LOOKUP_ID
  AND C.LOOKUP11=L11.LOOKUP_ID
  AND C.LOOKUP12=L12.LOOKUP_ID
  AND C.LOOKUP13=L13.LOOKUP_ID
  AND C.LOOKUP14=L14.LOOKUP_ID;

The DBMS Xplan for the above – took about 11.7 seconds:

---------------------------------------------------------------------------­-----------------------------------------------------------
| Id  | Operation                       | Name | Starts | E-Rows | A- Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem |  O/1/M   |
---------------------------------------------------------------------------­-----------------------------------------------------------
|*  1 |  HASH JOIN                      |      |      1 |    100K|     100K|00:00:11.68 |   20806 |   5141 |  7744K|  2666K|     1/0/0|
|   2 |   TABLE ACCESS FULL             | T2   |      1 |    140K|     140K|00:00:00.01 |    1056 |    149 |       |       |          |
|*  3 |   HASH JOIN                     |      |      1 |    100K|     100K|00:00:10.95 |   19750 |   4992 |  7744K|  2666K|     1/0/0|
|   4 |    TABLE ACCESS FULL            | T2   |      1 |    140K|     140K|00:00:00.01 |    1056 |      0 |       |       |          |
|*  5 |    HASH JOIN                    |      |      1 |    100K|     100K|00:00:10.21 |   18694 |   4992 |  7744K|  2666K|     1/0/0|
|   6 |     TABLE ACCESS FULL           | T2   |      1 |    140K|     140K|00:00:00.01 |    1056 |      0 |       |       |          |
|*  7 |     HASH JOIN                   |      |      1 |    100K|     100K|00:00:09.57 |   17638 |   4992 |  7744K|  2666K|     1/0/0|
|   8 |      TABLE ACCESS FULL          | T2   |      1 |    140K|     140K|00:00:00.01 |    1056 |      0 |       |       |          |
|*  9 |      HASH JOIN                  |      |      1 |    100K|     100K|00:00:08.83 |   16582 |   4992 |  7744K|  2666K|     1/0/0|
|  10 |       TABLE ACCESS FULL         | T2   |      1 |    140K|     140K|00:00:00.01 |    1056 |      0 |       |       |          |
|* 11 |       HASH JOIN                 |      |      1 |    100K|     100K|00:00:08.16 |   15526 |   4992 |  7744K|  2666K|     1/0/0|
|  12 |        TABLE ACCESS FULL        | T2   |      1 |    140K|     140K|00:00:00.01 |    1056 |      0 |       |       |          |
|* 13 |        HASH JOIN                |      |      1 |    100K|     100K|00:00:07.43 |   14470 |   4992 |  7744K|  2666K|     1/0/0|
|  14 |         TABLE ACCESS FULL       | T2   |      1 |    140K|     140K|00:00:00.01 |    1056 |      0 |       |       |          |
|* 15 |         HASH JOIN               |      |      1 |    100K|     100K|00:00:06.78 |   13414 |   4992 |  7744K|  2666K|     1/0/0|
|  16 |          TABLE ACCESS FULL      | T2   |      1 |    140K|     140K|00:00:00.01 |    1056 |      0 |       |       |          |
|* 17 |          HASH JOIN              |      |      1 |    100K|     100K|00:00:06.05 |   12358 |   4992 |  7744K|  2666K|     1/0/0|
|  18 |           TABLE ACCESS FULL     | T2   |      1 |    140K|     140K|00:00:00.01 |    1056 |      0 |       |       |          |
|* 19 |           HASH JOIN             |      |      1 |    100K|     100K|00:00:05.40 |   11302 |   4992 |  7744K|  2666K|     1/0/0|
|  20 |            TABLE ACCESS FULL    | T2   |      1 |    140K|     140K|00:00:00.01 |    1056 |      0 |       |       |          |
|* 21 |            HASH JOIN            |      |      1 |    100K|     100K|00:00:04.65 |   10246 |   4992 |  7744K|  2666K|     1/0/0|
|  22 |             TABLE ACCESS FULL   | T2   |      1 |    140K|     140K|00:00:00.01 |    1056 |      0 |       |       |          |
|* 23 |             HASH JOIN           |      |      1 |    100K|     100K|00:00:04.01 |    9190 |   4992 |  7744K|  2666K|     1/0/0|
|  24 |              TABLE ACCESS FULL  | T2   |      1 |    140K|     140K|00:00:00.01 |    1056 |      0 |       |       |          |
|* 25 |              HASH JOIN          |      |      1 |    100K|     100K|00:00:03.28 |    8134 |   4992 |  7744K|  2666K|     1/0/0|
|  26 |               TABLE ACCESS FULL | T2   |      1 |    140K|     140K|00:00:00.01 |    1056 |      0 |       |       |          |
|* 27 |               HASH JOIN         |      |      1 |    100K|     100K|00:00:02.64 |    7078 |   4992 |  7744K|  2666K|     1/0/0|
|  28 |                TABLE ACCESS FULL| T2   |      1 |    140K|     140K|00:00:00.01 |    1056 |      0 |       |       |          |
|  29 |                TABLE ACCESS FULL| T1   |      1 |    100K|     100K|00:00:01.90 |    6022 |   4992 |       |       |          |
---------------------------------------------------------------------------­-----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("C"."LOOKUP14"="L14"."LOOKUP_ID")
   3 - access("C"."LOOKUP13"="L13"."LOOKUP_ID")
   5 - access("C"."LOOKUP12"="L12"."LOOKUP_ID")
   7 - access("C"."LOOKUP11"="L11"."LOOKUP_ID")
   9 - access("C"."LOOKUP10"="L10"."LOOKUP_ID")
  11 - access("C"."LOOKUP9"="L9"."LOOKUP_ID")
  13 - access("C"."LOOKUP8"="L8"."LOOKUP_ID")
  15 - access("C"."LOOKUP7"="L7"."LOOKUP_ID")
  17 - access("C"."LOOKUP6"="L6"."LOOKUP_ID")
  19 - access("C"."LOOKUP5"="L5"."LOOKUP_ID")
  21 - access("C"."LOOKUP4"="L4"."LOOKUP_ID")
  23 - access("C"."LOOKUP3"="L3"."LOOKUP_ID")
  25 - access("C"."LOOKUP2"="L2"."LOOKUP_ID")
  27 - access("C"."LOOKUP1"="L1"."LOOKUP_ID")

Let’s try again, this time with one instance of the T2 lookup table
listed in the SQL statement:

SELECT
  C.CONFIGTABLE_ID,
  MAX(DECODE(C.LOOKUP1,L1.LOOKUP_ID,L1.VALUE,NULL)) L1_VALUE,
  MAX(DECODE(C.LOOKUP2,L1.LOOKUP_ID,L1.VALUE,NULL)) L2_VALUE,
  MAX(DECODE(C.LOOKUP3,L1.LOOKUP_ID,L1.VALUE,NULL)) L3_VALUE,
  MAX(DECODE(C.LOOKUP4,L1.LOOKUP_ID,L1.VALUE,NULL)) L4_VALUE,
  MAX(DECODE(C.LOOKUP5,L1.LOOKUP_ID,L1.VALUE,NULL)) L5_VALUE,
  MAX(DECODE(C.LOOKUP6,L1.LOOKUP_ID,L1.VALUE,NULL)) L6_VALUE,
  MAX(DECODE(C.LOOKUP7,L1.LOOKUP_ID,L1.VALUE,NULL)) L7_VALUE,
  MAX(DECODE(C.LOOKUP8,L1.LOOKUP_ID,L1.VALUE,NULL)) L8_VALUE,
  MAX(DECODE(C.LOOKUP9,L1.LOOKUP_ID,L1.VALUE,NULL)) L9_VALUE,
  MAX(DECODE(C.LOOKUP10,L1.LOOKUP_ID,L1.VALUE,NULL)) L10_VALUE,
  MAX(DECODE(C.LOOKUP11,L1.LOOKUP_ID,L1.VALUE,NULL)) L11_VALUE,
  MAX(DECODE(C.LOOKUP12,L1.LOOKUP_ID,L1.VALUE,NULL)) L12_VALUE,
  MAX(DECODE(C.LOOKUP13,L1.LOOKUP_ID,L1.VALUE,NULL)) L13_VALUE,
  MAX(DECODE(C.LOOKUP14,L1.LOOKUP_ID,L1.VALUE,NULL)) L14_VALUE
FROM
  T1 C,
  T2 L1
WHERE
  C.LOOKUP1=L1.LOOKUP_ID
  OR C.LOOKUP2=L1.LOOKUP_ID
  OR C.LOOKUP3=L1.LOOKUP_ID
  OR C.LOOKUP4=L1.LOOKUP_ID
  OR C.LOOKUP5=L1.LOOKUP_ID
  OR C.LOOKUP6=L1.LOOKUP_ID
  OR C.LOOKUP7=L1.LOOKUP_ID
  OR C.LOOKUP8=L1.LOOKUP_ID
  OR C.LOOKUP9=L1.LOOKUP_ID
  OR C.LOOKUP10=L1.LOOKUP_ID
  OR C.LOOKUP11=L1.LOOKUP_ID
  OR C.LOOKUP12=L1.LOOKUP_ID
  OR C.LOOKUP13=L1.LOOKUP_ID
  OR C.LOOKUP14=L1.LOOKUP_ID
GROUP BY
  C.CONFIGTABLE_ID;

The DBMS Xplan, 21.3 seconds once the GROUP BY completed:

---------------------------------------------------------------------------­---------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A- Time   | Buffers | Reads  | Writes |  OMem |  1Mem |  O/1/M   |
---------------------------------------------------------------------------­---------------------------------------------------------
|   1 |  HASH GROUP BY       |      |      1 |  98476 |    100K| 00:00:21.31 |   85162 |  74613 |   4402 |       |       |          |
|   2 |   CONCATENATION      |      |      1 |        |   1399K| 00:00:14.33 |   85162 |  70211 |      0 |       |       |          |
|*  3 |    HASH JOIN         |      |      1 |    100K|    100K| 00:00:00.84 |    6083 |   5016 |      0 |  7744K|  2666K|     1/0/0|
|   4 |     TABLE ACCESS FULL| T2   |      1 |    140K|    140K| 00:00:00.01 |    1056 |      0 |      0 |       |       |          |
|   5 |     TABLE ACCESS FULL| T1   |      1 |    100K|    100K| 00:00:00.20 |    5027 |   5016 |      0 |       |       |          |
|*  6 |    HASH JOIN         |      |      1 |   5000 |  99998 | 00:00:01.00 |    6083 |   5015 |      0 |  7744K|  2666K|     1/0/0|
|   7 |     TABLE ACCESS FULL| T2   |      1 |    140K|    140K| 00:00:00.01 |    1056 |      0 |      0 |       |       |          |
|   8 |     TABLE ACCESS FULL| T1   |      1 |    100K|    100K| 00:00:00.20 |    5027 |   5015 |      0 |       |       |          |
|*  9 |    HASH JOIN         |      |      1 |    250 |  99998 | 00:00:00.93 |    6083 |   5015 |      0 |  7744K|  2666K|     1/0/0|
|  10 |     TABLE ACCESS FULL| T2   |      1 |    140K|    140K| 00:00:00.01 |    1056 |      0 |      0 |       |       |          |
|  11 |     TABLE ACCESS FULL| T1   |      1 |    100K|    100K| 00:00:00.30 |    5027 |   5015 |      0 |       |       |          |
|* 12 |    HASH JOIN         |      |      1 |     13 |  99997 | 00:00:00.93 |    6083 |   5016 |      0 |  7744K|  2666K|     1/0/0|
|  13 |     TABLE ACCESS FULL| T2   |      1 |    140K|    140K| 00:00:00.01 |    1056 |      0 |      0 |       |       |          |
|  14 |     TABLE ACCESS FULL| T1   |      1 |    100K|    100K| 00:00:00.20 |    5027 |   5016 |      0 |       |       |          |
|* 15 |    HASH JOIN         |      |      1 |      1 |  99999 | 00:00:00.94 |    6083 |   5013 |      0 |  7744K|  2666K|     1/0/0|
|  16 |     TABLE ACCESS FULL| T2   |      1 |    140K|    140K| 00:00:00.01 |    1056 |      0 |      0 |       |       |          |
|  17 |     TABLE ACCESS FULL| T1   |      1 |    100K|    100K| 00:00:00.20 |    5027 |   5013 |      0 |       |       |          |
|* 18 |    HASH JOIN         |      |      1 |      1 |  99994 | 00:00:00.97 |    6083 |   5015 |      0 |  7744K|  2666K|     1/0/0|
|  19 |     TABLE ACCESS FULL| T2   |      1 |    140K|    140K| 00:00:00.01 |    1056 |      0 |      0 |       |       |          |
|  20 |     TABLE ACCESS FULL| T1   |      1 |    100K|    100K| 00:00:00.20 |    5027 |   5015 |      0 |       |       |          |
|* 21 |    HASH JOIN         |      |      1 |      1 |  99993 | 00:00:00.99 |    6083 |   5016 |      0 |  7744K|  2666K|     1/0/0|
|  22 |     TABLE ACCESS FULL| T2   |      1 |    140K|    140K| 00:00:00.01 |    1056 |      0 |      0 |       |       |          |
|  23 |     TABLE ACCESS FULL| T1   |      1 |    100K|    100K| 00:00:00.20 |    5027 |   5016 |      0 |       |       |          |
|* 24 |    HASH JOIN         |      |      1 |      1 |  99995 | 00:00:00.95 |    6083 |   5014 |      0 |  7744K|  2666K|     1/0/0|
|  25 |     TABLE ACCESS FULL| T2   |      1 |    140K|    140K| 00:00:00.01 |    1056 |      0 |      0 |       |       |          |
|  26 |     TABLE ACCESS FULL| T1   |      1 |    100K|    100K| 00:00:00.20 |    5027 |   5014 |      0 |       |       |          |
|* 27 |    HASH JOIN         |      |      1 |      1 |  99988 | 00:00:00.97 |    6083 |   5016 |      0 |  7744K|  2666K|     1/0/0|
|  28 |     TABLE ACCESS FULL| T2   |      1 |    140K|    140K| 00:00:00.01 |    1056 |      0 |      0 |       |       |          |
|  29 |     TABLE ACCESS FULL| T1   |      1 |    100K|    100K| 00:00:00.20 |    5027 |   5016 |      0 |       |       |          |
|* 30 |    HASH JOIN         |      |      1 |      1 |  99994 | 00:00:00.97 |    6083 |   5016 |      0 |  7744K|  2666K|     1/0/0|
|  31 |     TABLE ACCESS FULL| T2   |      1 |    140K|    140K| 00:00:00.01 |    1056 |      0 |      0 |       |       |          |
|  32 |     TABLE ACCESS FULL| T1   |      1 |    100K|    100K| 00:00:00.30 |    5027 |   5016 |      0 |       |       |          |
|* 33 |    HASH JOIN         |      |      1 |      1 |  99988 | 00:00:01.09 |    6083 |   5015 |      0 |  7744K|  2666K|     1/0/0|
|  34 |     TABLE ACCESS FULL| T2   |      1 |    140K|    140K| 00:00:00.01 |    1056 |      0 |      0 |       |       |          |
|  35 |     TABLE ACCESS FULL| T1   |      1 |    100K|    100K| 00:00:00.30 |    5027 |   5015 |      0 |       |       |          |
|* 36 |    HASH JOIN         |      |      1 |      1 |  99991 | 00:00:00.96 |    6083 |   5016 |      0 |  7744K|  2666K|     1/0/0|
|  37 |     TABLE ACCESS FULL| T2   |      1 |    140K|    140K| 00:00:00.01 |    1056 |      0 |      0 |       |       |          |
|  38 |     TABLE ACCESS FULL| T1   |      1 |    100K|    100K| 00:00:00.20 |    5027 |   5016 |      0 |       |       |          |
|* 39 |    HASH JOIN         |      |      1 |      1 |  99988 | 00:00:00.93 |    6083 |   5014 |      0 |  7744K|  2666K|     1/0/0|
|  40 |     TABLE ACCESS FULL| T2   |      1 |    140K|    140K| 00:00:00.01 |    1056 |      0 |      0 |       |       |          |
|  41 |     TABLE ACCESS FULL| T1   |      1 |    100K|    100K| 00:00:00.20 |    5027 |   5014 |      0 |       |       |          |
|* 42 |    HASH JOIN         |      |      1 |      1 |  99997 | 00:00:00.98 |    6083 |   5014 |      0 |  7744K|  2666K|     1/0/0|
|  43 |     TABLE ACCESS FULL| T2   |      1 |    140K|    140K| 00:00:00.01 |    1056 |      0 |      0 |       |       |          |
|  44 |     TABLE ACCESS FULL| T1   |      1 |    100K|    100K| 00:00:00.20 |    5027 |   5014 |      0 |       |       |          |
---------------------------------------------------------------------------­---------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("C"."LOOKUP14"="L1"."LOOKUP_ID")
   6 - access("C"."LOOKUP13"="L1"."LOOKUP_ID")
       filter(LNNVL("C"."LOOKUP14"="L1"."LOOKUP_ID"))
   9 - access("C"."LOOKUP12"="L1"."LOOKUP_ID")
       filter((LNNVL("C"."LOOKUP13"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP14"="L1"."LOOKUP_ID")))
  12 - access("C"."LOOKUP11"="L1"."LOOKUP_ID")
       filter((LNNVL("C"."LOOKUP12"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP13"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP14"="L1"."LOOKUP_ID")))
  15 - access("C"."LOOKUP10"="L1"."LOOKUP_ID")
       filter((LNNVL("C"."LOOKUP11"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP12"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP13"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP14"="L1"."LOOKUP_ID")))
  18 - access("C"."LOOKUP9"="L1"."LOOKUP_ID")
       filter((LNNVL("C"."LOOKUP10"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP11"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP12"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP13"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP14"="L1"."LOOKUP_ID")))
  21 - access("C"."LOOKUP8"="L1"."LOOKUP_ID")
       filter((LNNVL("C"."LOOKUP9"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP10"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP11"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP12"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP13"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP14"="L1"."LOOKUP_ID")))
  24 - access("C"."LOOKUP7"="L1"."LOOKUP_ID")
       filter((LNNVL("C"."LOOKUP8"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP9"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP10"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP11"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP12"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP13"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP14"="L1"."LOOKUP_ID")))
  27 - access("C"."LOOKUP6"="L1"."LOOKUP_ID")
       filter((LNNVL("C"."LOOKUP7"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP8"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP9"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP10"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP11"="L1"."LOOKUP_ID")
              AND LNNVL("C"."LOOKUP12"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP13"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP14"="L1"."LOOKUP_ID")))
  30 - access("C"."LOOKUP5"="L1"."LOOKUP_ID")
       filter((LNNVL("C"."LOOKUP6"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP7"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP8"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP9"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP10"="L1"."LOOKUP_ID")
              AND LNNVL("C"."LOOKUP11"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP12"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP13"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP14"="L1"."LOOKUP_ID")))
  33 - access("C"."LOOKUP4"="L1"."LOOKUP_ID")
       filter((LNNVL("C"."LOOKUP5"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP6"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP7"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP8"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP9"="L1"."LOOKUP_ID")
              AND LNNVL("C"."LOOKUP10"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP11"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP12"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP13"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP14"="L1"."LOOKUP_ID")))
  36 - access("C"."LOOKUP3"="L1"."LOOKUP_ID")
       filter((LNNVL("C"."LOOKUP4"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP5"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP6"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP7"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP8"="L1"."LOOKUP_ID")
              AND LNNVL("C"."LOOKUP9"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP10"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP11"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP12"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP13"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP14"="L1"."LOOKUP_ID")))
  39 - access("C"."LOOKUP2"="L1"."LOOKUP_ID")
       filter((LNNVL("C"."LOOKUP3"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP4"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP5"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP6"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP7"="L1"."LOOKUP_ID")
              AND LNNVL("C"."LOOKUP8"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP9"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP10"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP11"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP12"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP13"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP14"="L1"."LOOKUP_ID")))
  42 - access("C"."LOOKUP1"="L1"."LOOKUP_ID")
       filter((LNNVL("C"."LOOKUP2"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP3"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP4"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP5"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP6"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP7"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP8"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP9"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP10"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP11"="L1"."LOOKUP_ID")
              AND LNNVL("C"."LOOKUP12"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP13"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP14"="L1"."LOOKUP_ID")))

OK, so we tried to be clever and it took twice as long.

Third try:

SELECT
  C.CONFIGTABLE_ID,
  MAX(DECODE(C.LOOKUP1,L1.LOOKUP_ID,L1.VALUE,NULL)) L1_VALUE,
  MAX(DECODE(C.LOOKUP2,L1.LOOKUP_ID,L1.VALUE,NULL)) L2_VALUE,
  MAX(DECODE(C.LOOKUP3,L1.LOOKUP_ID,L1.VALUE,NULL)) L3_VALUE,
  MAX(DECODE(C.LOOKUP4,L1.LOOKUP_ID,L1.VALUE,NULL)) L4_VALUE,
  MAX(DECODE(C.LOOKUP5,L1.LOOKUP_ID,L1.VALUE,NULL)) L5_VALUE,
  MAX(DECODE(C.LOOKUP6,L1.LOOKUP_ID,L1.VALUE,NULL)) L6_VALUE,
  MAX(DECODE(C.LOOKUP7,L1.LOOKUP_ID,L1.VALUE,NULL)) L7_VALUE,
  MAX(DECODE(C.LOOKUP8,L1.LOOKUP_ID,L1.VALUE,NULL)) L8_VALUE,
  MAX(DECODE(C.LOOKUP9,L1.LOOKUP_ID,L1.VALUE,NULL)) L9_VALUE,
  MAX(DECODE(C.LOOKUP10,L1.LOOKUP_ID,L1.VALUE,NULL)) L10_VALUE,
  MAX(DECODE(C.LOOKUP11,L1.LOOKUP_ID,L1.VALUE,NULL)) L11_VALUE,
  MAX(DECODE(C.LOOKUP12,L1.LOOKUP_ID,L1.VALUE,NULL)) L12_VALUE,
  MAX(DECODE(C.LOOKUP13,L1.LOOKUP_ID,L1.VALUE,NULL)) L13_VALUE,
  MAX(DECODE(C.LOOKUP14,L1.LOOKUP_ID,L1.VALUE,NULL)) L14_VALUE
FROM
  T1 C,
  T2 L1
WHERE
  DECODE(L1.LOOKUP_ID,C.LOOKUP1,1,
    C.LOOKUP2,1,
    C.LOOKUP3,1,
    C.LOOKUP4,1,
    C.LOOKUP5,1,
    C.LOOKUP6,1,
    C.LOOKUP7,1,
    C.LOOKUP8,1,
    C.LOOKUP9,1,
    C.LOOKUP10,1,
    C.LOOKUP11,1,
    C.LOOKUP12,1,
    C.LOOKUP13,1,
    C.LOOKUP14,1,0)=1
GROUP BY
  C.CONFIGTABLE_ID;

This one took more than two minutes (I killed it at that point).

Let’s try a full Cartesian join just to kill the database server:

SELECT /*+ ORDERED */
  C.CONFIGTABLE_ID,
  MAX(DECODE(C.LOOKUP1,L1.LOOKUP_ID,L1.VALUE,NULL)) L1_VALUE,
  MAX(DECODE(C.LOOKUP2,L1.LOOKUP_ID,L1.VALUE,NULL)) L2_VALUE,
  MAX(DECODE(C.LOOKUP3,L1.LOOKUP_ID,L1.VALUE,NULL)) L3_VALUE,
  MAX(DECODE(C.LOOKUP4,L1.LOOKUP_ID,L1.VALUE,NULL)) L4_VALUE,
  MAX(DECODE(C.LOOKUP5,L1.LOOKUP_ID,L1.VALUE,NULL)) L5_VALUE,
  MAX(DECODE(C.LOOKUP6,L1.LOOKUP_ID,L1.VALUE,NULL)) L6_VALUE,
  MAX(DECODE(C.LOOKUP7,L1.LOOKUP_ID,L1.VALUE,NULL)) L7_VALUE,
  MAX(DECODE(C.LOOKUP8,L1.LOOKUP_ID,L1.VALUE,NULL)) L8_VALUE,
  MAX(DECODE(C.LOOKUP9,L1.LOOKUP_ID,L1.VALUE,NULL)) L9_VALUE,
  MAX(DECODE(C.LOOKUP10,L1.LOOKUP_ID,L1.VALUE,NULL)) L10_VALUE,
  MAX(DECODE(C.LOOKUP11,L1.LOOKUP_ID,L1.VALUE,NULL)) L11_VALUE,
  MAX(DECODE(C.LOOKUP12,L1.LOOKUP_ID,L1.VALUE,NULL)) L12_VALUE,
  MAX(DECODE(C.LOOKUP13,L1.LOOKUP_ID,L1.VALUE,NULL)) L13_VALUE,
  MAX(DECODE(C.LOOKUP14,L1.LOOKUP_ID,L1.VALUE,NULL)) L14_VALUE
FROM
  (SELECT
    C.CONFIGTABLE_ID,
    C.LOOKUP1,
    C.LOOKUP2,
    C.LOOKUP3,
    C.LOOKUP4,
    C.LOOKUP5,
    C.LOOKUP6,
    C.LOOKUP7,
    C.LOOKUP8,
    C.LOOKUP9,
    C.LOOKUP10,
    C.LOOKUP11,
    C.LOOKUP12,
    C.LOOKUP13,
    C.LOOKUP14
  FROM
    T1 C) C,
  T2 L1
GROUP BY
  C.CONFIGTABLE_ID;

This one took more than two minutes (I killed it at that point).

Let’s try the second method again, hiding optimizations from Oracle, prohibiting the CONCAT/UNION ALL optimization and a couple other optimization possibilies:

SELECT /*+ NO_QUERY_TRANSFORMATION */
  C.CONFIGTABLE_ID,
  MAX(DECODE(C.LOOKUP1,L1.LOOKUP_ID,L1.VALUE,NULL)) L1_VALUE,
  MAX(DECODE(C.LOOKUP2,L1.LOOKUP_ID,L1.VALUE,NULL)) L2_VALUE,
  MAX(DECODE(C.LOOKUP3,L1.LOOKUP_ID,L1.VALUE,NULL)) L3_VALUE,
  MAX(DECODE(C.LOOKUP4,L1.LOOKUP_ID,L1.VALUE,NULL)) L4_VALUE,
  MAX(DECODE(C.LOOKUP5,L1.LOOKUP_ID,L1.VALUE,NULL)) L5_VALUE,
  MAX(DECODE(C.LOOKUP6,L1.LOOKUP_ID,L1.VALUE,NULL)) L6_VALUE,
  MAX(DECODE(C.LOOKUP7,L1.LOOKUP_ID,L1.VALUE,NULL)) L7_VALUE,
  MAX(DECODE(C.LOOKUP8,L1.LOOKUP_ID,L1.VALUE,NULL)) L8_VALUE,
  MAX(DECODE(C.LOOKUP9,L1.LOOKUP_ID,L1.VALUE,NULL)) L9_VALUE,
  MAX(DECODE(C.LOOKUP10,L1.LOOKUP_ID,L1.VALUE,NULL)) L10_VALUE,
  MAX(DECODE(C.LOOKUP11,L1.LOOKUP_ID,L1.VALUE,NULL)) L11_VALUE,
  MAX(DECODE(C.LOOKUP12,L1.LOOKUP_ID,L1.VALUE,NULL)) L12_VALUE,
  MAX(DECODE(C.LOOKUP13,L1.LOOKUP_ID,L1.VALUE,NULL)) L13_VALUE,
  MAX(DECODE(C.LOOKUP14,L1.LOOKUP_ID,L1.VALUE,NULL)) L14_VALUE
FROM
  T1 C,
  T2 L1
WHERE
  C.LOOKUP1=L1.LOOKUP_ID
  OR C.LOOKUP2=L1.LOOKUP_ID
  OR C.LOOKUP3=L1.LOOKUP_ID
  OR C.LOOKUP4=L1.LOOKUP_ID
  OR C.LOOKUP5=L1.LOOKUP_ID
  OR C.LOOKUP6=L1.LOOKUP_ID
  OR C.LOOKUP7=L1.LOOKUP_ID
  OR C.LOOKUP8=L1.LOOKUP_ID
  OR C.LOOKUP9=L1.LOOKUP_ID
  OR C.LOOKUP10=L1.LOOKUP_ID
  OR C.LOOKUP11=L1.LOOKUP_ID
  OR C.LOOKUP12=L1.LOOKUP_ID
  OR C.LOOKUP13=L1.LOOKUP_ID
  OR C.LOOKUP14=L1.LOOKUP_ID
GROUP BY
  C.CONFIGTABLE_ID;

This one took more than five minutes (I killed it at that point).

Maybe the fact that we forgot to put an index on the T2 table is causing the performance problem – all of those full tablescans can’t be good:

ALTER TABLE T2 ADD
  PRIMARY KEY (LOOKUP_ID);

SELECT
  INDEX_NAME
FROM
  DBA_INDEXES
WHERE
  TABLE_NAME='T2';

Now we have an index named SYS_C0022342.

Analyze table T2 and its index again:

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

Execute the first query again, and the same plan was generated.

Force the use of the index on five of the joins, and try a nested loop on one of the joins – took about the same amount of time as the first attempt:

SELECT /*+ INDEX(L1 SYS_C0022342) INDEX(L2 SYS_C0022342) INDEX(L3 SYS_C0022342) INDEX(L4 SYS_C0022342) INDEX(L5 SYS_C0022342) USE_NL(C L1) */
  C.CONFIGTABLE_ID,
  L1.VALUE L1_VALUE,
  L2.VALUE L2_VALUE,
  L3.VALUE L3_VALUE,
  L4.VALUE L4_VALUE,
  L5.VALUE L5_VALUE,
  L6.VALUE L6_VALUE,
  L7.VALUE L7_VALUE,
  L8.VALUE L8_VALUE,
  L9.VALUE L9_VALUE,
  L10.VALUE L10_VALUE,
  L11.VALUE L11_VALUE,
  L12.VALUE L12_VALUE,
  L13.VALUE L13_VALUE,
  L14.VALUE L14_VALUE
FROM
  T1 C,
  T2 L1,
  T2 L2,
  T2 L3,
  T2 L4,
  T2 L5,
  T2 L6,
  T2 L7,
  T2 L8,
  T2 L9,
  T2 L10,
  T2 L11,
  T2 L12,
  T2 L13,
  T2 L14
WHERE
  C.LOOKUP1=L1.LOOKUP_ID
  AND C.LOOKUP2=L2.LOOKUP_ID
  AND C.LOOKUP3=L3.LOOKUP_ID
  AND C.LOOKUP4=L4.LOOKUP_ID
  AND C.LOOKUP5=L5.LOOKUP_ID
  AND C.LOOKUP6=L6.LOOKUP_ID
  AND C.LOOKUP7=L7.LOOKUP_ID
  AND C.LOOKUP8=L8.LOOKUP_ID
  AND C.LOOKUP9=L9.LOOKUP_ID
  AND C.LOOKUP10=L10.LOOKUP_ID
  AND C.LOOKUP11=L11.LOOKUP_ID
  AND C.LOOKUP12=L12.LOOKUP_ID
  AND C.LOOKUP13=L13.LOOKUP_ID
  AND C.LOOKUP14=L14.LOOKUP_ID;

------------------------------------------------------------------------­---------------------------------------------------------------------------­--
| Id  | Operation                                 | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem | 1Mem |  O/1/M   |
---------------------------------------------------- -----------------------­-----------------------------------------------------------------------­--
|*  1 |  HASH JOIN                                |              |      1 |    131K|    100K|00:00:12.98 |     222K|   4967 |  7744K| 2666K|     1/0/0|
|   2 |   TABLE ACCESS FULL                       | T2           |      1 |    140K|    140K|00:00:00.01 |    1056 |      0 |       |      |          |
|*  3 |   HASH JOIN                               |              |      1 |    129K|    100K|00:00:12.25 |     221K|   4967 |  7744K| 2666K|     1/0/0|
|   4 |    TABLE ACCESS FULL                      | T2           |      1 |    140K|    140K|00:00:00.01 |    1056 |      0 |       |      |          |
|*  5 |    HASH JOIN                              |              |      1 |    126K|    100K|00:00:11.62 |     220K|   4967 |  7744K| 2666K|     1/0/0|
|   6 |     TABLE ACCESS FULL                     | T2           |      1 |    140K|    140K|00:00:00.01 |    1056 |      0 |       |      |          |
|*  7 |     HASH JOIN                             |              |      1 |    124K|    100K|00:00:10.99 |     219K|   4967 |  7744K| 2666K|     1/0/0|
|   8 |      TABLE ACCESS FULL                    | T2           |      1 |    140K|    140K|00:00:00.01 |    1056 |      0 |       |      |          |
|*  9 |      HASH JOIN                            |              |      1 |    121K|    100K|00:00:10.27 |     218K|   4967 |  7744K| 2666K|     1/0/0|
|  10 |       TABLE ACCESS FULL                   | T2           |      1 |    140K|    140K|00:00:00.01 |    1056 |      0 |       |      |          |
|* 11 |       HASH JOIN                           |              |      1 |    119K|    100K|00:00:09.64 |     217K|   4967 |  7744K| 2666K|     1/0/0|
|  12 |        TABLE ACCESS FULL                  | T2           |      1 |    140K|    140K|00:00:00.01 |    1056 |      0 |       |      |          |
|* 13 |        HASH JOIN                          |              |      1 |    117K|    100K|00:00:09.01 |     216K|   4967 |  7744K| 2666K|     1/0/0|
|  14 |         TABLE ACCESS FULL                 | T2           |      1 |    140K|    140K|00:00:00.01 |    1056 |      0 |       |      |          |
|* 15 |         HASH JOIN                         |              |      1 |    114K|    100K|00:00:08.29 |     215K|   4967 |  7744K| 2666K|     1/0/0|
|  16 |          TABLE ACCESS FULL                | T2           |      1 |    140K|    140K|00:00:00.01 |    1056 |      0 |       |      |          |
|* 17 |          HASH JOIN                        |              |      1 |    112K|    100K|00:00:07.66 |     214K|   4967 |  7744K| 2666K|     1/0/0|
|  18 |           TABLE ACCESS FULL               | T2           |      1 |    140K|    140K|00:00:00.01 |    1056 |      0 |       |      |          |
|* 19 |           HASH JOIN                       |              |      1 |    110K|    100K|00:00:07.03 |     213K|   4967 |  7744K| 2666K|     1/0/0|
|  20 |            TABLE ACCESS BY INDEX ROWID    | T2           |      1 |    140K|    140K|00:00:00.42 |    1520 |      0 |       |      |          |
|  21 |             INDEX FULL SCAN               | SYS_C0022342 |      1 |    140K|    140K|00:00:00.08 |     511 |      0 |       |      |          |
|* 22 |            HASH JOIN                      |              |      1 |    108K|    100K|00:00:05.87 |     211K|   4967 |  7744K| 2666K|     1/0/0|
|  23 |             TABLE ACCESS BY INDEX ROWID   | T2           |      1 |    140K|    140K|00:00:00.42 |    1520 |      0 |       |      |          |
|  24 |              INDEX FULL SCAN              | SYS_C0022342 |      1 |    140K|    140K|00:00:00.08 |     511 |      0 |       |      |          |
|* 25 |             HASH JOIN                     |              |      1 |    106K|    100K|00:00:04.82 |     210K|   4967 |  7744K| 2666K|     1/0/0|
|  26 |              TABLE ACCESS BY INDEX ROWID  | T2           |      1 |    140K|    140K|00:00:00.42 |    1520 |      0 |       |      |          |
|  27 |               INDEX FULL SCAN             | SYS_C0022342 |      1 |    140K|    140K|00:00:00.08 |     511 |      0 |       |      |          |
|* 28 |              HASH JOIN                    |              |      1 |    104K|    100K|00:00:03.76 |     208K|   4967 |  7744K| 2666K|     1/0/0|
|  29 |               TABLE ACCESS BY INDEX ROWID | T2           |      1 |    140K|    140K|00:00:00.42 |    1520 |      0 |       |      |          |
|  30 |                INDEX FULL SCAN            | SYS_C0022342 |      1 |    140K|    140K|00:00:00.08 |     511 |      0 |       |      |          |
|  31 |               NESTED LOOPS                |              |      1 |    101K|    100K|00:00:02.60 |     207K|   4967 |       |      |          |
|  32 |                TABLE ACCESS FULL          | T1           |      1 |    100K|    100K|00:00:01.00 |    6022 |   4967 |       |      |          |
|  33 |                TABLE ACCESS BY INDEX ROWID| T2           |    100K|      1 |    100K|00:00:01.35 |     201K|      0 |       |      |          |
|* 34 |                 INDEX UNIQUE SCAN         | SYS_C0022342 |    100K|      1 |    100K|00:00:00.73 |     101K|      0 |       |      |          |
---------------------------------------------------------------------------­------------------------------------------------------------------------­--
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("C"."LOOKUP14"="L14"."LOOKUP_ID")
   3 - access("C"."LOOKUP13"="L13"."LOOKUP_ID")
   5 - access("C"."LOOKUP12"="L12"."LOOKUP_ID")
   7 - access("C"."LOOKUP11"="L11"."LOOKUP_ID")
   9 - access("C"."LOOKUP10"="L10"."LOOKUP_ID")
  11 - access("C"."LOOKUP9"="L9"."LOOKUP_ID")
  13 - access("C"."LOOKUP8"="L8"."LOOKUP_ID")
  15 - access("C"."LOOKUP7"="L7"."LOOKUP_ID")
  17 - access("C"."LOOKUP6"="L6"."LOOKUP_ID")
  19 - access("C"."LOOKUP5"="L5"."LOOKUP_ID")
  22 - access("C"."LOOKUP4"="L4"."LOOKUP_ID")
  25 - access("C"."LOOKUP3"="L3"."LOOKUP_ID")
  28 - access("C"."LOOKUP2"="L2"."LOOKUP_ID")
  34 - access("C"."LOOKUP1"="L1"."LOOKUP_ID")

The short answer to the above demonstration is that the structure of the SQL statement makes a big difference in the execution time. Hiding information, possibily in views, retricts Oracle’s options when trying to determine the optimal execution plan – and disabling the options has a very negative impact on execution efficiency.  Your experience with your actual data set may be very different.
——————————–





SQL – Reformatting to Improve Performance 4

4 12 2009

December 4, 2009

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

Note: the Oracle cost-based query optimizer is able to automatically transform queries submitted by applications to produce an equivalent SQL statement that will hopefully improve SQL execution performance. The possible transformations are limited by the Oracle release version, the value of the OPTIMIZER_FEATURES_ENABLE parameter (and various other hidden parameters), the potential presence of NULL values, and hints embedded in the SQL statement.

Some time ago the following question appeared in a forum:
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/25ee58d1a12e1fd1

(The thread also contains an interesting discussion about the effects of over-indexing tables.)

I have the following two select statements in some code I’ve been looking at.  I don’t have access to a computer with Oracle on it currently so I can’t determine this myself.

Both selects do the same thing.  The function in the second version simply checks the condition what is in the first and send back a ‘Y’ or ‘N’ whereas the first has the check in the where clause itself.

My question is this:  is any one of the two selects more efficient (or less efficient) than the other?

SELECT a.mbr_dob,
       a.mbr_first_nm,
       a.mbr_gender,
       b.mbr_key,
       a.mbr_last_nm,
       b.mbr_sys_key,
       b.mbr_updated_dt,
       a.mbr_x_fst_nm,
       a.mbr_x_lst_nm,
       a.person_id,
       a.z_person_id
  FROM mbr_person a,
       mbr_system b
 WHERE a.person_id = b.person_id
   AND a.deactivate = 0
   AND b.deactivate = 0
   AND (
             a.mbr_last_nm NOT IN ( 'DATA','CORRECTION' )
         AND a.mbr_first_nm NOT IN ( 'DATA','CORRECTION' )
        );

 

SELECT a.mbr_dob,
       a.mbr_first_nm,
       a.mbr_gender,
       b.mbr_key,
       a.mbr_last_nm,
       b.mbr_sys_key,
       b.mbr_updated_dt,
       a.mbr_x_fst_nm,
       a.mbr_x_lst_nm,
       a.person_id,
       a.z_person_id
  FROM mbr_person a,
       mbr_system b
 WHERE a.person_id = b.person_id
   AND a.deactivate = 0
   AND b.deactivate = 0
   AND not_data_correction( a.mbr_last_nm, a.mbr_first_nm ) = 'Y';

——————————–

I would caution against creating too many indexes, as this will likely negatively impact performance of other parts of the system while offering minimal assistance to this SQL statement.  Histograms could be important on the columns MBR_PERSON.DEACTIVATE, MBR_SYSTEM.DEACTIVATE, MBR_PERSON.MBR_LAST_NM, and MBR_PERSON.MBR_FIRST_NM.  The histograms will give Oracle’s cost based optimizer a better picture of the contents of the columns, rather than assuming an even spread of the data values between the min and max for the column.

Keep in mind that Oracle may transform your SQL statement into another equivalent form, and may use constraints and transitive closure to generate additional predicates (think entries in the WHERE) clause for the SQL statement.  You may want to perform timing with alternate SQL syntax:

SELECT a.mbr_dob,
       a.mbr_first_nm,
       a.mbr_gender,
       b.mbr_key,
       a.mbr_last_nm,
       b.mbr_sys_key,
       b.mbr_updated_dt,
       a.mbr_x_fst_nm,
       a.mbr_x_lst_nm,
       a.person_id,
       a.z_person_id
  FROM mbr_person a,
       mbr_system b
 WHERE a.person_id = b.person_id
   AND a.deactivate = 0
   AND b.deactivate = 0
   AND A.DEACTIVATE = B.DEACTIVATE
   AND A.MBR_LAST_NM NOT IN ('DATA','CORRECTION')
   AND A.MBR_FIRST_NM NOT IN ('DATA','CORRECTION');

Indexes on the MBR_PERSON.DEACTIVATE, MBR_SYSTEM.DEACTIVATE columns may be helpful if most of the rows have something other than 0 in those columns.  Indexes on the MBR_PERSON.MBR_LAST_NM and MBR_PERSON.MBR_FIRST_NM columns will likely be of limited use.  An index on MBR_PERSON.PERSON_ID, MBR_SYSTEM.PERSON_ID will likely be very helpful.

My guess is that the above SQL statement will perform a full tablescan on both tables.  The full tablescans may be the most efficient way to retrieve the rows.  However, that will depend greatly on the composition of the data in the two tables.  Oracle may select to perform a hash join between the two tables, so a large HASH_AREA_SIZE may help.

As has been mentioned in this thread, avoid using PL/SQL for something that can be done efficiently in pure SQL.  Context switches (and I suppose on-the-fly compiling of the PL/SQL code if not using Native code compilation) are expensive on some operating system platforms, and may greatly decrease performance.

The best way to test the performance of the various methods requires access to the Oracle database, unless of course you want to dig through 10046 traces at level 8 or 12.

——————————–





SQL – Reformatting to Improve Performance 3

4 12 2009

December 4, 2009

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

Note: the Oracle cost-based query optimizer is able to automatically transform queries submitted by applications to produce an equivalent SQL statement that will hopefully improve SQL execution performance. The possible transformations are limited by the Oracle release version, the value of the OPTIMIZER_FEATURES_ENABLE parameter (and various other hidden parameters), the potential presence of NULL values, and hints embedded in the SQL statement.

Some time ago the following question appeared in a forum:
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/8661f136bdaefae4

The query is against a view (MY_VIEW below) in the same schema where the procedure lives.  The view selects from another view (EXT_VIEW below) in an external DB via a DBLINK.

Both servers are running Oracle 10g.  The host server is Red Hat Linux.  The external server is Win 2003.  If exact versions become important, I can post them.

The query that works, but slowly, is:

SELECT bv.* FROM MY_VIEW bv
WHERE bv.objectid in
(select linkid from LINKS_TABLE
  where projectid = 116448
    AND blocklist = 'N')
AND bv.GISDATASETNAME = 'XXX' ORDER BY bv.physical_length DESC

However, this takes 12 seconds to return exactly one row from the external database.  Here’s the explain plan:

Operation          Object Name
SELECT STATEMENT Optimizer Mode=ALL_ROWS
 SORT ORDER BY
  NESTED LOOPS SEMI
   REMOTE    .EXT_VIEW   EXTDB.US.OPVANTEK.COM
   TABLE ACCESS BY INDEX ROWID  MY_SCHEMA.LINKS_TABLE
    INDEX UNIQUE SCAN           OPDSCP.PK_PGM 1 0

——————————–

Oracle appears to be using the results of the remote data to drive into the MY_SCHEMA.LINKS_TABLE table using an index on the LINKS_TABLE table.  Thus, Oracle is retrieving all rows from the remote database where GISDATASETNAME = ‘XXX’ and probing the MY_SCHEMA.LINKS_TABLE for matching LINKID values.  You might repeat your explain plan using DBMS_XPLAN with the ALLSTATS LAST parameters to see how it compares.

Is it possible to rewrite the SQL statement into a more efficient form?

First question – can any one tell me a way to force the first query above to use the faster explain plan?  We know there will not be very many linkids returned by the inner query (less than 10).

Having given up on re-writing the first query, I then wrote some PL/SQL to loop over the inner query with a cursor and construct a comma separate list of all the linkids, which I can then use as the IN() clause of my dyanamic sql statement.  That works and runs much faster from a SQL Editor window (we use TOAD).

Let’s take another look at rewriting the original query, something like this:

SELECT /*+ ORDERED */
  BV.*
FROM
  (SELECT DISTINCT
    LINKID
  FROM
    LINKS_TABLE
  WHERE
    PROJECTID = 116448
    AND BLOCKLIST = 'N') L,
  MY_VIEW BV
WHERE
  L.LINKID=BV.OBJECTID
  AND BV.GISDATASETNAME = 'XXX'
ORDER BY
  BV.PHYSICAL_LENGTH DESC;

The above will instruct Oracle to retrieve the 10 or fewer rows from the LINK_TABLE, and use the results to drive into MY_VIEW, which points to the remote database.

How does a DBMS_XPLAN with the ALLSTATS LAST parameters for your original query compare with my rewrite above?

——————————–





SQL – Reformatting to Improve Performance 2

4 12 2009

December 4, 2009

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

Note: the Oracle cost-based query optimizer is able to automatically transform queries submitted by applications to produce an equivalent SQL statement that will hopefully improve SQL execution performance. The possible transformations are limited by the Oracle release version, the value of the OPTIMIZER_FEATURES_ENABLE parameter (and various other hidden parameters), the potential presence of NULL values, and hints embedded in the SQL statement.

Some time ago the following question appeared in a forum:
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/d4e3f3f275ed894d

The following query is performing poorly in the app. I have tried the scalar subquery approach (using formatted to_char)  with no luck. any help would be appreciated.

SELECT  MP_NO, MP_DESC, MP_ACTIVE AS "FLAG_YES_NO",
(
  ( SELECT COUNT(*)     FROM ST, STINV
                WHERE ST.ST_ID = STINV.ST_ID
                AND ST.ST_POSTED IS NOT NULL
                AND STINV.MP_NO = MPT.MP_NO )
   +
  ( SELECT COUNT(*) FROM PT, PTINV
                  WHERE PT.PT_ID = PTINV.PT_ID
                  AND PT.PT_POSTED IS NOT NULL
                  AND PTINV.MP_NO = MPT.MP_NO  )
)
FROM MPT
WHERE COMP_ID = 1
GROUP BY MP_NO, MP_DESC, MP_ACTIVE

——————————–

It might be interesting to see how the performance and DBMS_XPLAN of your SQL statement compares with the following:

SELECT
  MPT.MP_NO,
  MPT.MP_DESC,
  MPT.MP_ACTIVE "FLAG_YES_NO",
  NVL(S.V1,0) + NVL(P.V2,0)
FROM
  MPT,
  (SELECT
    STINV.MP_NO,
    COUNT(*) V1
  FROM
    ST,
    STINV
  WHERE
    ST.ST_ID = STINV.ST_ID
    AND ST.ST_POSTED IS NOT NULL
  GROUP BY
    STINV.MP_NO) S,
  (SELECT
    PTINV.MP_NO,
    COUNT(*) V2
  FROM
    PT,
    PTINV
  WHERE
    PT.PT_ID = PTINV.PT_ID
    AND PT.PT_POSTED IS NOT NULL
  GROUP BY
    PTINV.MP_NO) P
WHERE
  MPT.COMP_ID=1
  AND MPT.MP_NO=S.MP_NO(+)
  AND MPT.MP_NO=P.MP_NO(+)
GROUP BY
  MPT.MP_NO,
  MPT.MP_DESC,
  MPT.MP_ACTIVE;

If possible, the outer join [ (+) ] should be removed from the SQL statement that I posted above.

Is MPT.COMP_ID a column defined as NUMBER?

——————————–








Follow

Get every new post delivered to your Inbox.

Join 140 other followers