SQL – Bad Execution Plan caused by ROWNUM, ROW_NUMBER is Possible Fix

December 9, 2009

A recent discussion in the OTN forums demonstrated a cardinality calculation problem caused by using ROWNUM as a predicate in the WHERE clause (this specific issue was corrected in 11.2.0.1):
http://forums.oracle.com/forums/thread.jspa?threadID=934895&start=0

The person who started the OTN thread obviously knew how to ask a question, provide relevant information, and analyze the problem (essentially a model of a good request for assistance):

I’m having a couple of issues with a query, and I can’t figure out the best way to reach a solution.

Platform Information
Windows Server 2003 R2
Oracle 10.2.0.4

Optimizer Settings

SQL > show parameter optimizer

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      10.2.0.4
optimizer_index_caching              integer     90
optimizer_index_cost_adj             integer     30
optimizer_mode                       string      ALL_ROWS
optimizer_secure_view_merging        boolean     TRUE

The query below, is a simple “Top N” query, where the top result is returned. Here it is, with bind variables in the same location as the application code:

SELECT PRODUCT_DESC
FROM
(
 SELECT PRODUCT_DESC
 , COUNT(*) AS CNT
 FROM USER_VISITS 
 JOIN PRODUCT ON PRODUCT.PRODUCT_OID = USER_VISITS.PRODUCT_OID
 WHERE PRODUCT.PRODUCT_DESC != 'Home' 
 AND VISIT_DATE
  BETWEEN
   ADD_MONTHS    
   (
    TRUNC    
    (
     TO_DATE   
     (
      :vCurrentYear
     , 'YYYY'
     )
    , 'YEAR'
    )
   , 3*(:vCurrentQuarter-1)
   )
  AND
   ADD_MONTHS    
   (
    TRUNC    
    (
     TO_DATE   
     (
      :vCurrentYear
     , 'YYYY'
     )
    , 'YEAR'
    )
   , 3*:vCurrentQuarter
   ) - INTERVAL '1' DAY   
 GROUP BY PRODUCT_DESC
 ORDER BY CNT DESC
)
WHERE ROWNUM <= 1;

The explain plan I receive when running the query above.

| Id  | Operation                         | Name                          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY                    |                               |      1 |        |      1 |00:00:34.92 |   66343 |       |       |          |
|   2 |   VIEW                            |                               |      1 |      1 |      1 |00:00:34.92 |   66343 |       |       |          |
|*  3 |    FILTER                         |                               |      1 |        |      1 |00:00:34.92 |   66343 |       |       |          |
|   4 |     SORT ORDER BY                 |                               |      1 |      1 |      1 |00:00:34.92 |   66343 |  2048 |  2048 | 2048  (0)|
|   5 |      SORT GROUP BY NOSORT         |                               |      1 |      1 |     27 |00:00:34.92 |   66343 |       |       |          |
|   6 |       NESTED LOOPS                |                               |      1 |      2 |  12711 |00:00:34.90 |   66343 |       |       |          |
|   7 |        TABLE ACCESS BY INDEX ROWID| PRODUCT                       |      1 |     74 |     77 |00:00:00.01 |      44 |       |       |          |
|*  8 |         INDEX FULL SCAN           | PRODUCT_PRODDESCHAND_UNQ      |      1 |      1 |     77 |00:00:00.01 |       1 |       |       |          |
|*  9 |        INDEX FULL SCAN            | USER_VISITS#PK                |     77 |      2 |  12711 |00:00:34.88 |   66299 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=1)
   3 - filter(ADD_MONTHS(TRUNC(TO_DATE(TO_CHAR(:VCURRENTYEAR),'YYYY'),'fmyear'),3*(:VCURRENTQUARTER-1))<=ADD_MONTHS(TRUNC(TO_DATE(TO_CHAR(:VCURR
              ENTYEAR),'YYYY'),'fmyear'),3*:VCURRENTQUARTER)-INTERVAL'+01 00:00:00' DAY(2) TO SECOND(0))
   8 - filter("PRODUCT"."PRODUCT_DESC"<>'Home')
   9 - access("USER_VISITS"."VISIT_DATE">=ADD_MONTHS(TRUNC(TO_DATE(TO_CHAR(:VCURRENTYEAR),'YYYY'),'fmyear'),3*(:VCURRENTQUARTER-1)) AND
              "USER_VISITS"."PRODUCT_OID"="PRODUCT"."PRODUCT_OID" AND "USER_VISITS"."VISIT_DATE"<=ADD_MONTHS(TRUNC(TO_DATE(TO_CHAR(:VCURRENTYEAR),'YYYY')
              ,'fmyear'),3*:VCURRENTQUARTER)-INTERVAL'+01 00:00:00' DAY(2) TO SECOND(0))
       filter(("USER_VISITS"."VISIT_DATE">=ADD_MONTHS(TRUNC(TO_DATE(TO_CHAR(:VCURRENTYEAR),'YYYY'),'fmyear'),3*(:VCURRENTQUARTER-1)) AND
              "USER_VISITS"."VISIT_DATE"<=ADD_MONTHS(TRUNC(TO_DATE(TO_CHAR(:VCURRENTYEAR),'YYYY'),'fmyear'),3*:VCURRENTQUARTER)-INTERVAL'+01 00:00:00' DAY(2)
              TO SECOND(0) AND "USER_VISITS"."PRODUCT_OID"="PRODUCT"."PRODUCT_OID"))

TKPROF Row Source Generation

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.01          0          0          0           0
Fetch        2     35.10      35.13          0      66343          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     35.10      35.14          0      66343          0           1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 62 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  COUNT STOPKEY (cr=66343 pr=0 pw=0 time=35132008 us)
      1   VIEW  (cr=66343 pr=0 pw=0 time=35131996 us)
      1    FILTER  (cr=66343 pr=0 pw=0 time=35131991 us)
      1     SORT ORDER BY (cr=66343 pr=0 pw=0 time=35131936 us)
     27      SORT GROUP BY NOSORT (cr=66343 pr=0 pw=0 time=14476309 us)
  12711       NESTED LOOPS  (cr=66343 pr=0 pw=0 time=22921810 us)
     77        TABLE ACCESS BY INDEX ROWID PRODUCT (cr=44 pr=0 pw=0 time=3674 us)
     77         INDEX FULL SCAN PRODUCT_PRODDESCHAND_UNQ (cr=1 pr=0 pw=0 time=827 us)(object id 52355)
  12711        INDEX FULL SCAN USER_VISITS#PK (cr=66299 pr=0 pw=0 time=44083746 us)(object id 52949)

However when I run the query with an ALL_ROWS hint I receive this explain plan (reasoning for this can be found here Jonathan’s Lewis’ response: http://www.freelists.org/post/oracle-l/ORDER-BY-and-first-rows-10-madness,4):

---------------------------------------------------------------------------------------------
| Id  | Operation                  | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                |     1 |    39 |   223  (25)| 00:00:03 |
|*  1 |  COUNT STOPKEY             |                |       |       |            |          |
|   2 |   VIEW                     |                |     1 |    39 |   223  (25)| 00:00:03 |
|*  3 |    FILTER                  |                |       |       |            |          |
|   4 |     SORT ORDER BY          |                |     1 |    49 |   223  (25)| 00:00:03 |
|   5 |      HASH GROUP BY         |                |     1 |    49 |   223  (25)| 00:00:03 |
|*  6 |       HASH JOIN            |                |   490 | 24010 |   222  (24)| 00:00:03 |
|*  7 |        TABLE ACCESS FULL   | PRODUCT        |    77 |  2849 |     2   (0)| 00:00:01 |
|*  8 |        INDEX FAST FULL SCAN| USER_VISITS#PK |   490 |  5880 |   219  (24)| 00:00:03 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=1)
   3 - filter(ADD_MONTHS(TRUNC(TO_DATE(:VCURRENTYEAR,'YYYY'),'fmyear'),3*(TO_NUMBER(:
              VCURRENTQUARTER)-1))<=ADD_MONTHS(TRUNC(TO_DATE(:VCURRENTYEAR,'YYYY'),'fmyear'),3*TO_N
              UMBER(:VCURRENTQUARTER))-INTERVAL'+01 00:00:00' DAY(2) TO SECOND(0))
   6 - access("USER_VISITS"."PRODUCT_OID"="PRODUCT"."PRODUCT_OID")
   7 - filter("PRODUCT"."PRODUCT_DESC"<>'Home')
   8 - filter("USER_VISITS"."VISIT_DATE">=ADD_MONTHS(TRUNC(TO_DATE(:VCURRENTYEAR,'YYY
              Y'),'fmyear'),3*(TO_NUMBER(:VCURRENTQUARTER)-1)) AND
              "USER_VISITS"."VISIT_DATE"<=ADD_MONTHS(TRUNC(TO_DATE(:VCURRENTYEAR,'YYYY'),'fmyear'),
              3*TO_NUMBER(:VCURRENTQUARTER))-INTERVAL'+01 00:00:00' DAY(2) TO SECOND(0))

And the TKPROF Row Source Generation:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        3      0.51       0.51          0        907          0          27
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.51       0.51          0        907          0          27

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 62 

Rows     Row Source Operation
-------  ---------------------------------------------------
     27  FILTER  (cr=907 pr=0 pw=0 time=513472 us)
     27   SORT ORDER BY (cr=907 pr=0 pw=0 time=513414 us)
     27    HASH GROUP BY (cr=907 pr=0 pw=0 time=512919 us)
  12711     HASH JOIN  (cr=907 pr=0 pw=0 time=641130 us)
     77      TABLE ACCESS FULL PRODUCT (cr=5 pr=0 pw=0 time=249 us)
  22844      INDEX FAST FULL SCAN USER_VISITS#PK (cr=902 pr=0 pw=0 time=300356 us)(object id 52949)

The query with the ALL_ROWS hint returns data instantly, while the other one takes about 70 times as long.

Interestingly enough BOTH queries generate plans with estimates that are WAY off. The first plan is estimating 2 rows, while the second plan is estimating 490 rows. However the real number of rows is correctly reported in the Row Source Generation as 12711 (after the join operation).

TABLE_NAME                       NUM_ROWS     BLOCKS
------------------------------ ---------- ----------
USER_VISITS                        196044       1049

INDEX_NAME                         BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR LAST_ANALYZED
------------------------------ ---------- ----------- ------------- ----------------- -------------------
USER_VISITS#PK                          2         860        196002          57761 07/24/2009 13:17:59

COLUMN_NAME                    NUM_DISTINCT LOW_VALUE            HIGH_VALUE                                 DENSITY     NUM_NULLS HISTOGRAM
------------------------------ ------------ -------------------- -------------------- -------------------------------- ---------- ---------------
VISIT_DATE                           195900 786809010E0910       786D0609111328                      .0000051046452272          0 NONE

I don’t know how the first one is estimating 2 rows, but I can compute the second’s cardinality estimates by assuming a 5% selectivity for the TO_DATE() functions:

SQL > SELECT ROUND(0.05*0.05*196044) FROM DUAL;

ROUND(0.05*0.05*196044)
-----------------------
                    490

However, removing the bind variables (and clearing the shared pool), does not change the cardinality estimates at all. I would like to avoid hinting this plan if possible and that is why I’m looking for advice. I also have a followup question.

Per one of Jonathan Lewis’ articles, adding a ROWNUM predicate in the WHERE clause causes the optimizer to switch to a FIRST ROWS n optimizer mode, as if a /*+ FIRST_ROWS(n) */ hint were provided for the query. To work around this issue, changing the query to use a ROW_NUMBER analytic function rather than the ROWNUM pseudo column could prevent the optimizer from switching to first rows optimization.

Another question you might ask is if bind peeking enabled? It is enabled by default on Oracle 10g (and 9i). Are there automatically generated histograms on any of the columns in the WHERE clause? Do any of the tables involved have out of date statistics (or cases where the statistics as of 10 PM do not match the statistics as of the current time)? Is it possible to examine 10053 trace files for the SQL statement?

Testing with either a CARDINALITY or OPT_ESTIMATE hint to see if the execution plan changes dramatically to improve performance is another possible area of investigation. The question then becomes whether this will be sufficient to over-rule the first rows optimizer so that it does not use an index access which will avoid a sort.

There may also be a problem with the accuracy of the EXPLAIN PLAN in SQL*Plus due to the bind variables in the SQL statement. If I recall correctly, explain plan does not peek at bind variables, as happens when the query is actually executed (but that may have been a 9i problem, where 10g uses DBMS_XPLAN for explain plans), and that SQL*Plus passes in numeric bind variables as if they are VARCHAR2. (I have an example in 11.1.0.7 which shows that this might not be the case).

The original poster provided a great test case to demonstrate what is happening:

DDL/DML

CREATE TABLE TEST_AGGR
(
 ID NUMBER
,  VAL DATE
,  PAD VARCHAR2(500)
,  ID2 NUMBER
);

CREATE TABLE TEST_JOIN
(
        ID2 NUMBER
,       PAD VARCHAR2(5)
,       PROD VARCHAR2(15)
);

CREATE SEQUENCE TEST_SEQUENCE;

INSERT INTO TEST_AGGR
SELECT TEST_SEQUENCE.NEXTVAL
, SYSDATE + DBMS_RANDOM.VALUE*100
, RPAD('X',499,'0')
, MOD(ROWNUM,78)
FROM DUAL
CONNECT BY ROWNUM <= 195000;

INSERT INTO TEST_JOIN
SELECT  ID2
,  
 (CASE
  WHEN ROWNUM IN (3,5) THEN 'Y'
  ELSE RPAD('X',4,'0')
 END)
, (CASE
  WHEN ROWNUM = 1 THEN 'test_prod'
  WHEN ROWNUM BETWEEN 2 AND 3 THEN 'DUPLICATE A1'
  WHEN ROWNUM BETWEEN 4 AND 5 THEN 'A DUPLICATE A2'
  ELSE UPPER(DBMS_RANDOM.STRING('A',15)) END)
FROM  (SELECT DISTINCT ID2 FROM TEST_AGGR);

CREATE INDEX TEST_AGGR_IDX ON TEST_AGGR(ID,VAL,ID2);
CREATE UNIQUE INDEX TEST_JOIN_UIDX ON TEST_JOIN(PROD,PAD);

 Statistics Gathering

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'TEST_AGGR',cascade=>true);
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'TEST_JOIN',cascade=>true);

Bind Variable Setup

var a VARCHAR2(25);
var b VARCHAR2(25);

exec :a := '08/01/2009';
exec :b := '08/27/2009';

Non-hinted Query

SELECT /*repeatable_case_nohint*/ PROD
FROM
(
 SELECT TEST_JOIN.PROD
 , COUNT(*) CNT
 FROM TEST_AGGR
 JOIN TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2
 WHERE VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY')
 AND PROD  'TEST_PROD'
 GROUP BY TEST_JOIN.PROD
 ORDER BY CNT DESC
)
WHERE ROWNUM <= 1

 Non-hinted XPLAN Output

SQL_ID  9smb486cd31b2, child number 0
-------------------------------------
SELECT /*repeatable_case_nohint*/ PROD FROM (  SELECT TEST_JOIN.PROD  , COUNT(*) CNT  FROM TEST_AGGR  JOIN TEST_JOIN ON
TEST_JOIN.ID2 = TEST_AGGR.ID2  WHERE VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY')  AND PROD <> 'TEST_PROD'
GROUP BY TEST_JOIN.PROD  ORDER BY CNT DESC ) WHERE ROWNUM <= 1

Plan hash value: 301623847

-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |
-----------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY                    |                |      1 |        |      1 |00:00:07.33 |   57334 |       |       |          |
|   2 |   VIEW                            |                |      1 |      2 |      1 |00:00:07.33 |   57334 |       |       |          |
|*  3 |    FILTER                         |                |      1 |        |      1 |00:00:07.33 |   57334 |       |       |          |
|   4 |     SORT ORDER BY                 |                |      1 |      2 |      1 |00:00:07.33 |   57334 | 73728 | 73728 |          |
|   5 |      SORT GROUP BY NOSORT         |                |      1 |      2 |     76 |00:00:07.33 |   57334 |       |       |          |
|   6 |       NESTED LOOPS                |                |      1 |      2 |  50637 |00:00:07.24 |   57334 |       |       |          |
|   7 |        TABLE ACCESS BY INDEX ROWID| TEST_JOIN      |      1 |     77 |     78 |00:00:00.01 |    2 |          |       |          |
|*  8 |         INDEX FULL SCAN           | TEST_JOIN_UIDX |      1 |      1 |     78 |00:00:00.01 |    1 |          |       |          |
|*  9 |        INDEX FULL SCAN            | TEST_AGGR_IDX  |     78 |      2 |  50637 |00:00:07.15 |   57332 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=1)
   3 - filter(TO_DATE(:A,'MM/DD/YYYY')<=TO_DATE(:B,'MM/DD/YYYY'))
   8 - filter("TEST_JOIN"."PROD"<>'TEST_PROD')
   9 - access("TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND "TEST_JOIN"."ID2"="TEST_AGGR"."ID2" AND
              "TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY'))
       filter(("TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY') AND "TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND
              "TEST_JOIN"."ID2"="TEST_AGGR"."ID2"))

Hinted Query

SELECT /*+ ALL_ROWS repeatable_case_hint*/ PROD
FROM
(
 SELECT TEST_JOIN.PROD
 , COUNT(*) CNT
 FROM TEST_AGGR
 JOIN TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2
 WHERE VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY')
 AND PROD  'TEST_PROD'
 GROUP BY TEST_JOIN.PROD
 ORDER BY CNT DESC
)
WHERE ROWNUM <= 1

Hinted XPLAN Output

SQL_ID  283wx8s0d04kn, child number 0
-------------------------------------
SELECT /*+ ALL_ROWS repeatable_case_hint*/ PROD FROM (  SELECT TEST_JOIN.PROD  , COUNT(*) CNT  FROM TEST_AGGR  JOIN
TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2  WHERE VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY')  AND
PROD <> 'TEST_PROD'  GROUP BY TEST_JOIN.PROD  ORDER BY CNT DESC ) WHERE ROWNUM <= 1

Plan hash value: 828673535

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |
---------------------------------------------------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY             |               |      1 |        |      1 |00:00:00.34 |     772 |       |       |          |
|   2 |   VIEW                     |               |      1 |     75 |      1 |00:00:00.34 |     772 |       |       |          |
|*  3 |    FILTER                  |               |      1 |        |      1 |00:00:00.34 |     772 |       |       |          |
|   4 |     SORT ORDER BY          |               |      1 |     75 |      1 |00:00:00.34 |     772 | 73728 | 73728 |          |
|   5 |      HASH GROUP BY         |               |      1 |     75 |     76 |00:00:00.34 |     772 |   808K|   808K|     1/0/0|
|*  6 |       HASH JOIN            |               |      1 |  50069 |  50637 |00:00:00.26 |     772 |   842K|   842K|     1/0/0|
|*  7 |        TABLE ACCESS FULL   | TEST_JOIN     |      1 |     77 |     78 |00:00:00.01 |       3 |       |       |          |
|*  8 |        INDEX FAST FULL SCAN| TEST_AGGR_IDX |      1 |  50737 |  50637 |00:00:00.10 |     769 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=1)
   3 - filter(TO_DATE(:A,'MM/DD/YYYY')<=TO_DATE(:B,'MM/DD/YYYY'))
   6 - access("TEST_JOIN"."ID2"="TEST_AGGR"."ID2")
   7 - filter("TEST_JOIN"."PROD"<>'TEST_PROD')
   8 - filter(("TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY') AND "TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY')))

Observations

The cardinality estimates in the non-hinted plan are way, way off. However, in the hinted plan the cardinalities are nearly correct. In the non-hinted plan the cardinality estimates are incorrect at steps 8 and 9.

I tried to work out the cardinality estimate for the access of TEST_JOIN_UIDX by first calculating the “Effective Index Selectivity” based on the following data:

TABLE_NAME                     COLUMN_NAME                    NUM_DISTINCT    DENSITY  NUM_NULLS
------------------------------ ------------------------------ ------------ ---------- ----------
TEST_JOIN                      PROD                                     76 .013157895          0

Since in step 8 of the plan we are filtering based on a != condition we want ALL the rows except that one. Since the number of rows in this roughly equal the number of distinct values I calculated a rough effective index selectivity estimate of:

(1-0.13157895)*78 = 67.7368419

This is right in line with a logical understanding of the data.

Based on the following data:

TABLE_NAME                     COLUMN_NAME                    NUM_DISTINCT
------------------------------ ------------------------------ ------------
TEST_AGGR                      VAL                                  194588

I would expect the following cardinality out of the TEST_AGGR_IDX index:

SQL > SELECT (TO_DATE(:b,'MM/DD/YYYY')-TO_DATE(:a,'MM/DD/YYYY'))/(MAX(VAL)-MIN(VAL))+2/194588 AS SELECTIVITY FROM TEST_AGGR;

SELECTIVITY
-----------
 .260014672

SQL > SELECT .260014672*195128 AS CARDINALITY FROM DUAL;

CARDINALITY
-----------
 50736.1429

This almost exactly matches the HINTED plan (error due to rounding).

Based on the estimates in the non-hinted plan I would have expected a 5% * 5% to occur but my calculations don’t match:

SQL > select .05*.05*(1/78)*195128 AS CARDINALITY from dual;

CARDINALITY
-----------
 6.25410256

After playing with the test case for a little while, I recalled seeing this behavior in the past with ROWNUM in the WHERE clause.  What is happening is that the ROWNUM<=1 predicate is being pushed into the inline view. Take a look (at the E-Rows column):

SELECT /*+ gather_plan_statistics */ PROD
FROM
(
 SELECT TEST_JOIN.PROD
 , COUNT(*) CNT
 FROM TEST_AGGR
 JOIN TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2
 WHERE VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY')
 AND PROD <> 'TEST_PROD'
 GROUP BY TEST_JOIN.PROD
 ORDER BY CNT DESC
)
WHERE ROWNUM <= 1;

select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY                    |                |      1 |        |      1 |00:00:02.84 |   57178 |       |       |          |
|   2 |   VIEW                            |                |      1 |      2 |      1 |00:00:02.84 |   57178 |       |       |          |
|*  3 |    FILTER                         |                |      1 |        |      1 |00:00:02.84 |   57178 |       |       |          |
|   4 |     SORT ORDER BY                 |                |      1 |      2 |      1 |00:00:02.84 |   57178 | 73728 | 73728 |          |
|   5 |      SORT GROUP BY NOSORT         |                |      1 |      2 |     76 |00:00:02.80 |   57178 |       |       |          |
|   6 |       NESTED LOOPS                |                |      1 |      2 |  50596 |00:00:03.04 |   57178 |       |       |          |
|   7 |        TABLE ACCESS BY INDEX ROWID| TEST_JOIN      |      1 |     77 |     78 |00:00:00.01 |       2 |       |       |          |
|*  8 |         INDEX FULL SCAN           | TEST_JOIN_UIDX |      1 |      1 |     78 |00:00:00.01 |       1 |       |       |          |
|*  9 |        INDEX FULL SCAN            | TEST_AGGR_IDX  |     78 |      2 |  50596 |00:00:02.88 |   57176 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=1)
   3 - filter(TO_DATE(:A,'MM/DD/YYYY')<=TO_DATE(:B,'MM/DD/YYYY'))
   8 - filter("TEST_JOIN"."PROD"<>'TEST_PROD')
   9 - access("TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND "TEST_JOIN"."ID2"="TEST_AGGR"."ID2" AND
              "TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY'))
       filter(("TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY') AND "TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND
              "TEST_JOIN"."ID2"="TEST_AGGR"."ID2"))

 

SELECT /*+ gather_plan_statistics */ PROD
FROM
(
 SELECT TEST_JOIN.PROD
 , COUNT(*) CNT
 FROM TEST_AGGR
 JOIN TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2
 WHERE VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY')
 AND PROD  'TEST_PROD'
 GROUP BY TEST_JOIN.PROD
 ORDER BY CNT DESC
)
WHERE ROWNUM <= 2;

select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY                    |                |      1 |        |      2 |00:00:02.70 |   57178 |       |       |          |
|   2 |   VIEW                            |                |      1 |      3 |      2 |00:00:02.70 |   57178 |       |       |          |
|*  3 |    FILTER                         |                |      1 |        |      2 |00:00:02.70 |   57178 |       |       |          |
|   4 |     SORT ORDER BY                 |                |      1 |      3 |      2 |00:00:02.70 |   57178 |  9216 |  9216 | 8192  (0)|
|   5 |      SORT GROUP BY NOSORT         |                |      1 |      3 |     76 |00:00:02.73 |   57178 |       |       |          |
|   6 |       NESTED LOOPS                |                |      1 |      3 |  50596 |00:00:02.88 |   57178 |       |       |          |
|   7 |        TABLE ACCESS BY INDEX ROWID| TEST_JOIN      |      1 |     77 |     78 |00:00:00.01 |       2 |       |       |          |
|*  8 |         INDEX FULL SCAN           | TEST_JOIN_UIDX |      1 |      1 |     78 |00:00:00.01 |       1 |       |       |          |
|*  9 |        INDEX FULL SCAN            | TEST_AGGR_IDX  |     78 |      3 |  50596 |00:00:02.73 |   57176 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=2)
   3 - filter(TO_DATE(:A,'MM/DD/YYYY')<=TO_DATE(:B,'MM/DD/YYYY'))
   8 - filter("TEST_JOIN"."PROD"<>'TEST_PROD')
   9 - access("TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND "TEST_JOIN"."ID2"="TEST_AGGR"."ID2" AND
              "TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY'))
       filter(("TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY') AND "TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND
              "TEST_JOIN"."ID2"="TEST_AGGR"."ID2"))

 

SELECT /*+ gather_plan_statistics */ PROD
FROM
(
 SELECT TEST_JOIN.PROD
 , COUNT(*) CNT
 FROM TEST_AGGR
 JOIN TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2
 WHERE VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY')
 AND PROD  'TEST_PROD'
 GROUP BY TEST_JOIN.PROD
 ORDER BY CNT DESC
)
WHERE ROWNUM <= 3;

select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY                    |                |      1 |        |      3 |00:00:02.71 |   57178 |       |       |          |
|   2 |   VIEW                            |                |      1 |      4 |      3 |00:00:02.71 |   57178 |       |       |          |
|*  3 |    FILTER                         |                |      1 |        |      3 |00:00:02.71 |   57178 |       |       |          |
|   4 |     SORT ORDER BY                 |                |      1 |      4 |      3 |00:00:02.71 |   57178 |  9216 |  9216 | 8192  (0)|
|   5 |      SORT GROUP BY NOSORT         |                |      1 |      4 |     76 |00:00:02.67 |   57178 |       |       |          |
|   6 |       NESTED LOOPS                |                |      1 |      4 |  50596 |00:00:02.93 |   57178 |       |       |          |
|   7 |        TABLE ACCESS BY INDEX ROWID| TEST_JOIN      |      1 |     77 |     78 |00:00:00.01 |       2 |       |       |          |
|*  8 |         INDEX FULL SCAN           | TEST_JOIN_UIDX |      1 |      1 |     78 |00:00:00.01 |       1 |       |       |          |
|*  9 |        INDEX FULL SCAN            | TEST_AGGR_IDX  |     78 |      4 |  50596 |00:00:02.83 |   57176 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=3)
   3 - filter(TO_DATE(:A,'MM/DD/YYYY')<=TO_DATE(:B,'MM/DD/YYYY'))
   8 - filter("TEST_JOIN"."PROD"<>'TEST_PROD')
   9 - access("TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND "TEST_JOIN"."ID2"="TEST_AGGR"."ID2" AND
              "TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY'))
       filter(("TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY') AND "TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND
              "TEST_JOIN"."ID2"="TEST_AGGR"."ID2"))

 

SELECT /*+ gather_plan_statistics */ PROD
FROM
(
 SELECT TEST_JOIN.PROD
 , COUNT(*) CNT
 FROM TEST_AGGR
 JOIN TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2
 WHERE VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY')
 AND PROD  'TEST_PROD'
 GROUP BY TEST_JOIN.PROD
 ORDER BY CNT DESC
)
WHERE ROWNUM <= 50;

select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY                    |                |      1 |        |     50 |00:00:02.72 |   57178 |       |       |          |
|   2 |   VIEW                            |                |      1 |     51 |     50 |00:00:02.72 |   57178 |       |       |          |
|*  3 |    FILTER                         |                |      1 |        |     50 |00:00:02.72 |   57178 |       |       |          |
|   4 |     SORT ORDER BY                 |                |      1 |     51 |     50 |00:00:02.72 |   57178 |  9216 |  9216 | 8192  (0)|
|   5 |      SORT GROUP BY NOSORT         |                |      1 |     51 |     76 |00:00:02.65 |   57178 |       |       |          |
|   6 |       NESTED LOOPS                |                |      1 |     51 |  50596 |00:00:02.88 |   57178 |       |       |          |
|   7 |        TABLE ACCESS BY INDEX ROWID| TEST_JOIN      |      1 |     77 |     78 |00:00:00.01 |       2 |       |       |          |
|*  8 |         INDEX FULL SCAN           | TEST_JOIN_UIDX |      1 |      1 |     78 |00:00:00.01 |       1 |       |       |          |
|*  9 |        INDEX FULL SCAN            | TEST_AGGR_IDX  |     78 |     51 |  50596 |00:00:02.78 |   57176 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=50)
   3 - filter(TO_DATE(:A,'MM/DD/YYYY')<=TO_DATE(:B,'MM/DD/YYYY'))
   8 - filter("TEST_JOIN"."PROD"<>'TEST_PROD')
   9 - access("TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND "TEST_JOIN"."ID2"="TEST_AGGR"."ID2" AND
              "TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY'))
       filter(("TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY') AND "TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND
              "TEST_JOIN"."ID2"="TEST_AGGR"."ID2"))

 

SELECT /*+ gather_plan_statistics */ PROD
FROM
(
 SELECT TEST_JOIN.PROD
 , COUNT(*) CNT
 FROM TEST_AGGR
 JOIN TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2
 WHERE VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY')
 AND PROD  'TEST_PROD'
 GROUP BY TEST_JOIN.PROD
 ORDER BY CNT DESC
)
WHERE ROWNUM <= 74;

select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY                    |                |      1 |        |   74 |00:00:02.71 |   57178 |       |       |          |
|   2 |   VIEW                            |                |      1 |     75 |   74 |00:00:02.71 |   57178 |       |       |          |
|*  3 |    FILTER                         |                |      1 |        |   74 |00:00:02.71 |   57178 |       |       |          |
|   4 |     SORT ORDER BY                 |                |      1 |     75 |   74 |00:00:02.71 |   57178 |  9216 |  9216 | 8192  (0)|
|   5 |      SORT GROUP BY NOSORT         |                |      1 |     75 |   76 |00:00:02.66 |   57178 |       |       |          |
|   6 |       NESTED LOOPS                |                |      1 |     75 |50596 |00:00:02.93 |   57178 |       |       |          |
|   7 |        TABLE ACCESS BY INDEX ROWID| TEST_JOIN      |      1 |     77 |   78 |00:00:00.01 |       2 |       |       |          |
|*  8 |         INDEX FULL SCAN           | TEST_JOIN_UIDX |      1 |      1 |   78 |00:00:00.01 |       1 |       |       |          |
|*  9 |        INDEX FULL SCAN            | TEST_AGGR_IDX  |     78 |     75 |50596 |00:00:02.83 |   57176 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=74)
   3 - filter(TO_DATE(:A,'MM/DD/YYYY')<=TO_DATE(:B,'MM/DD/YYYY'))
   8 - filter("TEST_JOIN"."PROD"<>'TEST_PROD')
   9 - access("TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND "TEST_JOIN"."ID2"="TEST_AGGR"."ID2" AND
              "TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY'))
       filter(("TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY') AND "TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND
              "TEST_JOIN"."ID2"="TEST_AGGR"."ID2"))

 

SELECT /*+ gather_plan_statistics */ PROD
FROM
(
 SELECT TEST_JOIN.PROD
 , COUNT(*) CNT
 FROM TEST_AGGR
 JOIN TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2
 WHERE VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY')
 AND PROD  'TEST_PROD'
 GROUP BY TEST_JOIN.PROD
 ORDER BY CNT DESC
)
WHERE ROWNUM <= 75;

select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY          |               |      1 |        |     75 |00:00:00.06 |     741 |       |       |          |
|   2 |   VIEW                  |               |      1 |     75 |     75 |00:00:00.06 |     741 |       |       |          |
|*  3 |    FILTER               |               |      1 |        |     75 |00:00:00.06 |     741 |       |       |          |
|   4 |     SORT ORDER BY       |               |      1 |     75 |     75 |00:00:00.06 |     741 |  9216 |  9216 | 8192  (0)|
|   5 |      HASH GROUP BY      |               |      1 |     75 |     76 |00:00:00.06 |     741 |   963K|   963K| 2375K (0)|
|*  6 |       HASH JOIN         |               |      1 |  50074 |  50596 |00:00:00.30 |     741 |  1035K|  1035K| 1164K (0)|
|*  7 |        TABLE ACCESS FULL| TEST_JOIN     |      1 |     77 |     78 |00:00:00.01 |       7 |       |       |          |
|*  8 |        INDEX FULL SCAN  | TEST_AGGR_IDX |      1 |  50742 |  50596 |00:00:00.10 |     734 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=75)
   3 - filter(TO_DATE(:A,'MM/DD/YYYY')<=TO_DATE(:B,'MM/DD/YYYY'))
   6 - access("TEST_JOIN"."ID2"="TEST_AGGR"."ID2")
   7 - filter("TEST_JOIN"."PROD"<>'TEST_PROD')
   8 - access("TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND "TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY'))
       filter(("TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY') AND "TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY')))

—

Compare the above output with what follows from Oracle 11.2.0.1, with the following parameters set:

ALTER SESSION SET optimizer_index_caching=0;
ALTER SESSION SET optimizer_index_cost_adj=100;
ALTER SESSION SET TRACEFILE_IDENTIFIER='optimizer_test100';
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';

 

SELECT /*+ gather_plan_statistics */ PROD
FROM
(
 SELECT TEST_JOIN.PROD
 , COUNT(*) CNT
 FROM TEST_AGGR
 JOIN TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2
 WHERE VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY')
 AND PROD  'TEST_PROD'
 GROUP BY TEST_JOIN.PROD
 ORDER BY CNT DESC
)
WHERE ROWNUM <= 1;

select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |               |      1 |        |      1 |00:00:00.03 |     747 |       |       |          |
|*  1 |  COUNT STOPKEY             |               |      1 |        |      1 |00:00:00.03 |     747 |       |       |          |
|   2 |   VIEW                     |               |      1 |     75 |      1 |00:00:00.03 |     747 |       |       |          |
|*  3 |    SORT ORDER BY STOPKEY   |               |      1 |     75 |      1 |00:00:00.03 |     747 | 73728 | 73728 |          |
|   4 |     HASH GROUP BY          |               |      1 |     75 |     76 |00:00:00.03 |     747 |   964K|   964K| 2491K (0)|
|*  5 |      FILTER                |               |      1 |        |  44832 |00:00:00.06 |     747 |       |       |          |
|*  6 |       HASH JOIN            |               |      1 |  44262 |  44832 |00:00:00.05 |     747 |  1035K|  1035K| 1265K (0)|
|*  7 |        TABLE ACCESS FULL   | TEST_JOIN     |      1 |     77 |     78 |00:00:00.01 |       3 |       |       |          |
|*  8 |        INDEX FAST FULL SCAN| TEST_AGGR_IDX |      1 |  44852 |  44832 |00:00:00.02 |     744 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=1)
   3 - filter(ROWNUM<=1)
   5 - filter(TO_DATE(:A,'MM/DD/YYYY')<=TO_DATE(:B,'MM/DD/YYYY'))
   6 - access("TEST_JOIN"."ID2"="TEST_AGGR"."ID2")
   7 - filter("TEST_JOIN"."PROD"<>'TEST_PROD')
   8 - filter(("TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY') AND "TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY')))

— 

ALTER SESSION SET optimizer_index_caching=90;
ALTER SESSION SET optimizer_index_cost_adj=30;
ALTER SESSION SET TRACEFILE_IDENTIFIER='optimizer_test30';

 

SELECT /*+ gather_plan_statistics */ PROD
FROM
(
 SELECT TEST_JOIN.PROD
 , COUNT(*) CNT
 FROM TEST_AGGR
 JOIN TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2
 WHERE VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY')
 AND PROD  'TEST_PROD'
 GROUP BY TEST_JOIN.PROD
 ORDER BY CNT DESC
)
WHERE ROWNUM <= 1;

select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |               |      1 |        |      1 |00:00:00.03 |     747 |       |       |          |
|*  1 |  COUNT STOPKEY             |               |      1 |        |      1 |00:00:00.03 |     747 |       |       |          |
|   2 |   VIEW                     |               |      1 |     75 |      1 |00:00:00.03 |     747 |       |       |          |
|*  3 |    SORT ORDER BY STOPKEY   |               |      1 |     75 |      1 |00:00:00.03 |     747 | 73728 | 73728 |          |
|   4 |     HASH GROUP BY          |               |      1 |     75 |     76 |00:00:00.03 |     747 |   964K|   964K| 2501K (0)|
|*  5 |      FILTER                |               |      1 |        |  44832 |00:00:00.06 |     747 |       |       |          |
|*  6 |       HASH JOIN            |               |      1 |  44262 |  44832 |00:00:00.05 |     747 |  1035K|  1035K| 1286K (0)|
|*  7 |        TABLE ACCESS FULL   | TEST_JOIN     |      1 |     77 |     78 |00:00:00.01 |       3 |       |       |          |
|*  8 |        INDEX FAST FULL SCAN| TEST_AGGR_IDX |      1 |  44852 |  44832 |00:00:00.02 |     744 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=1)
   3 - filter(ROWNUM<=1)
   5 - filter(TO_DATE(:A,'MM/DD/YYYY')<=TO_DATE(:B,'MM/DD/YYYY'))
   6 - access("TEST_JOIN"."ID2"="TEST_AGGR"."ID2")
   7 - filter("TEST_JOIN"."PROD"<>'TEST_PROD')
   8 - filter(("TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY') AND "TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY')))

 

ALTER SESSION SET optimizer_index_caching=100;
ALTER SESSION SET optimizer_index_cost_adj=1;
ALTER SESSION SET TRACEFILE_IDENTIFIER='optimizer_test1';

 

SELECT /*+ gather_plan_statistics */ PROD
FROM
(
 SELECT TEST_JOIN.PROD
 , COUNT(*) CNT
 FROM TEST_AGGR
 JOIN TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2
 WHERE VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY')
 AND PROD  'TEST_PROD'
 GROUP BY TEST_JOIN.PROD
 ORDER BY CNT DESC
)
WHERE ROWNUM <= 1;

select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |               |      1 |        |      1 |00:00:01.16 |   57034 |       |       |          |
|*  1 |  COUNT STOPKEY          |               |      1 |        |      1 |00:00:01.16 |   57034 |       |       |          |
|   2 |   VIEW                  |               |      1 |     75 |      1 |00:00:01.16 |   57034 |       |       |          |
|*  3 |    SORT ORDER BY STOPKEY|               |      1 |     75 |      1 |00:00:01.16 |   57034 | 73728 | 73728 |          |
|   4 |     HASH GROUP BY       |               |      1 |     75 |     76 |00:00:01.16 |   57034 |   964K|   964K| 2506K (0)|
|*  5 |      FILTER             |               |      1 |        |  44832 |00:00:01.17 |   57034 |       |       |          |
|   6 |       NESTED LOOPS      |               |      1 |  44262 |  44832 |00:00:01.16 |   57034 |       |       |          |
|*  7 |        TABLE ACCESS FULL| TEST_JOIN     |      1 |     77 |     78 |00:00:00.01 |       3 |       |       |          |
|*  8 |        INDEX FULL SCAN  | TEST_AGGR_IDX |     78 |    575 |  44832 |00:00:01.14 |   57031 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=1)
   3 - filter(ROWNUM<=1)
   5 - filter(TO_DATE(:A,'MM/DD/YYYY')<=TO_DATE(:B,'MM/DD/YYYY'))
   7 - filter("TEST_JOIN"."PROD"<>'TEST_PROD')
   8 - access("TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND "TEST_JOIN"."ID2"="TEST_AGGR"."ID2" AND
              "TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY'))
       filter(("TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY') AND "TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND
              "TEST_JOIN"."ID2"="TEST_AGGR"."ID2"))

SQL – ROW_NUMBER, MOD, Even Distribution

December 9, 2009

A question appeared in the comp.databases.oracle.server Usenet group a couple years ago:
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/41ee30267e646e10

Say I have a bunch of bowling players of different skill level as indicated by his avg_score in the table below.

I need to allot them into n teams (say 8), of equivalent strength on the TEAM level so no team ends up with mostly high-scorers and vic-versa.
(let’s say players may not be evenly divided into teams because n numbers are “sick”)

Is there a way to do to this ?

10gR2> create table players (id integer primary key, avg_score number, team_no integer);

10gR2> desc players
Name      Type
--------- -------
ID        INTEGER
AVG_SCORE NUMBER
TEAM_NO   INTEGER

 

10gR2> BEGIN
  2    FOR i IN 1..120
  3    LOOP
  4        INSERT INTO players (id, avg_score) VALUES(i,round(dbms_random.value(75,295)));
  5    END LOOP;
  6  END ;
  7  /

Needs work, but may be enough to get you started:

SELECT
  ID,
  AVG_SCORE,
  ROW_NUMBER() OVER (ORDER BY AVG_SCORE) RANKING,
  COUNT(*) OVER (PARTITION BY 1) ROWS_COUNT
FROM
  PLAYERS;

        ID  AVG_SCORE    RANKING ROWS_COUNT
---------- ---------- ---------- ----------
        74         78          1        120
        91         82          2        120
        95         83          3        120
        77         86          4        120
        61         87          5        120
        23         87          6        120
         1         90          7        120
        67         91          8        120
        62         97          9        120
        33         98         10        120
...
        88        271        111        120
        41        272        112        120
       104        274        113        120
        32        275        114        120
        36        275        115        120
        99        276        116        120
        71        277        117        120
        31        285        118        120
         3        286        119        120
       113        288        120        120

If we were to take the people at rank 1 and rank 120, they would have roughly the same average as the people at rank 2 and rank 119, and they would have roughly the same average as the people at rank 3 and 118, etc.  This does not work exactly as planned as the number of people must be evenly divisible by 2 * the number of groups, and this is not the case with 120 people and 8 groups.

We can have Oracle skip from 1 to 9 to 17 to … by using the MOD function, but we must recognize the mid-point so that we can switch the formula.

By sliding the above into an inline view, we can perform the analysis that is required.  I included three additional columns to help determine whether or not the formula is close:

SELECT
  ID,
  AVG_SCORE,
  DECODE(SIGN(RANKING-(ROWS_COUNT/2)),-1,MOD(RANKING-1,8)+1,((8-1)-MOD(RANKING-(8/2),8))+1) TEAM_NO,
  RANKING,
  SUM(AVG_SCORE) OVER (PARTITION BY DECODE(SIGN(RANKING-(ROWS_COUNT/2)),-1,MOD(RANKING-1,8)+1,((8-1)-MOD(RANKING-(8/2),8))+1)) TEAM_AVG,
  COUNT(*) OVER (PARTITION BY DECODE(SIGN(RANKING-(ROWS_COUNT/2)),-1,MOD(RANKING-1,8)+1,((8-1)-MOD(RANKING-(8/2),8))+1)) NUM_TEAM_MEMBERS
FROM
  (SELECT
    ID,
    AVG_SCORE,
    ROW_NUMBER() OVER (ORDER BY AVG_SCORE) RANKING,
    COUNT(*) OVER (PARTITION BY 1) ROWS_COUNT
  FROM
    PLAYERS)
ORDER BY
  RANKING;

        ID  AVG_SCORE    TEAM_NO    RANKING   TEAM_AVG NUM_TEAM_MEMBERS
---------- ---------- ---------- ---------- ---------- ----------------
        74         78          1          1       2603 15
        91         82          2          2       2602 15
        95         83          3          3       2592 15
        77         86          4          4       2709 15
        61         87          5          5       2701 15
        23         87          6          6       2690 15
         1         90          7          7       2686 15
        67         91          8          8       2689 15
        62         97          1          9       2603 15
        33         98          2         10       2602 15
        79         98          3         11       2592 15
       120        100          4         12       2709 15
         2        101          5         13       2701 15
        39        101          6         14       2690 15
        60        102          7         15       2686 15
       101        104          8         16       2689 15
...
        14        257          8        108       2689 15
        59        259          7        109       2686 15
        29        262          6        110       2690 15
        88        271          5        111       2701 15
        41        272          4        112       2709 15
       104        274          3        113       2592 15
        32        275          2        114       2602 15
        36        275          1        115       2603 15
        99        276          8        116       2689 15
        71        277          7        117       2686 15
        31        285          6        118       2690 15
         3        286          5        119       2701 15
       113        288          4        120       2709 15

SQL – ROW_NUMBER Analytical Function, Many to One Join

December 9, 2009

A question appeared in the comp.databases.oracle.server Usenet group a couple years ago:
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/deeb9d899c8e6376

I have a problem filtering my results which involves a many-to-many join. Below is a brief layout of the problem, but briefly, I have a test that can have multiple results and those results may also belong multiple tests (i have done the same test under a different id and I want to inherit the previous test result). I also have an event table that is required to keep track of all changes.

The problem I have is that I cannot filter my results so where a result belongs to 2 tests I don’t want the details where it belongs to the test in the query. If anyone can suggest a way of returning 2 lines instead of 3 I would be very grateful.

SELECT tr.test_acc, e.event_id, re.resultevent_id, re.result_id,
re2.resultevent_id, re2.event_id, tr2.test_acc
FROM TestRequest tr JOIN Event e ON tr.test_acc=e.test_acc
JOIN ResultEvent re ON e.event_id=re.event_id
JOIN ResultEvent re2 ON re.result_id=re2.result_id
JOIN Event e2 ON e2.event_id=re2.event_id
JOIN TestRequest tr2 ON e2.test_acc=tr2.test_acc
WHERE tr.test_acc=3418;

+----------+----------+----------------+-----------+----------------+----------+----------+
| test_acc | event_id | resultevent_id | result_id | resultevent_id | event_id | test_acc |
+----------+----------+----------------+-----------+----------------+----------+----------+
|     3418 |    42178 |           6345 |      6321 |           6345 |    42178 |     3418 |
|     3418 |    42179 |           6346 |      4126 |           4126 |    28004 |     2248 |
|     3418 |    42179 |           6346 |      4126 |           6346 |    42179 |     3418 |
+----------+----------+----------------+-----------+----------------+----------+----------+
 
3 rows in set (0.00 sec)

 

+-------------+
| TestRequest |
+-------------+
| test_acc    |
+-------------+
     1 |
       |
     * |
+-------------+
| Event       |
+-------------+
| test_acc    |
| event_id    |
+-------------+
     1 |
       |
     * |
+----------------+
| ResultEvent    |
+----------------+
| resultevent_id |
| event_id       |
| result_id      |
+----------------+

I think that I understand what you are trying to do.  I changed the table names slightly (added _) in the mock up:
First, the table creation:

CREATE TABLE TEST_REQUEST (
  TEST_ACC NUMBER(10));

CREATE TABLE EVENT (
  TEST_ACC NUMBER(10),
  EVENT_ID NUMBER(10));

CREATE TABLE RESULT_EVENT (
  RESULTEVENT_ID NUMBER(10),
  EVENT_ID NUMBER(10),
  RESULT_ID NUMBER(10));

As best I can tell, the data that is in the tables:

INSERT INTO TEST_REQUEST VALUES (3418);
INSERT INTO TEST_REQUEST VALUES (2248);

INSERT INTO EVENT VALUES (3418,42178);
INSERT INTO EVENT VALUES (3418,42179);
INSERT INTO EVENT VALUES (2248,28004);

INSERT INTO RESULT_EVENT VALUES (6345,42178,6321);
INSERT INTO RESULT_EVENT VALUES (4126,28004,4126);
INSERT INTO RESULT_EVENT VALUES (6346,42179,4126);

COMMIT;

I reformatted your query so that I could more easily see what is happening:

SELECT
  TR.TEST_ACC,
  E.EVENT_ID,
  RE.RESULTEVENT_ID,
  RE.RESULT_ID,
  RE2.RESULTEVENT_ID,
  RE2.EVENT_ID,
  TR2.TEST_ACC
FROM
  TEST_REQUEST TR,
  EVENT E,
  RESULT_EVENT RE,
  RESULT_EVENT RE2,
  EVENT E2,
  TEST_REQUEST TR2
WHERE
  TR.TEST_ACC=3418
  AND TR.TEST_ACC=E.TEST_ACC
  AND E.EVENT_ID=RE.EVENT_ID
  AND RE.RESULT_ID=RE2.RESULT_ID
  AND E2.EVENT_ID=RE2.EVENT_ID
  AND E2.TEST_ACC=TR2.TEST_ACC;

  TEST_ACC   EVENT_ID RESULTEVENT_ID  RESULT_ID RESULTEVENT_ID   EVENT_ID  TEST_ACC
---------- ---------- -------------- ---------- -------------- ---------- ----------
      3418      42179           6346       4126           4126      28004      2248
      3418      42179           6346       4126           6346      42179      3418
      3418      42178           6345       6321           6345      42178      3418

It appears that if you have 2 rows with the same RESULT_ID, you only want the first EVENT_ID, so you need to have some way of numbering the rows.  The ROW_NUMBER analytical function might be able to help:

SELECT
  TR.TEST_ACC,
  E.EVENT_ID,
  RE.RESULTEVENT_ID,
  RE.RESULT_ID,
  RE2.RESULTEVENT_ID,
  RE2.EVENT_ID,
  TR2.TEST_ACC,
  ROW_NUMBER() OVER (PARTITION BY RE.RESULT_ID ORDER BY RE2.EVENT_ID) RN
FROM
  TEST_REQUEST TR,
  EVENT E,
  RESULT_EVENT RE,
  RESULT_EVENT RE2,
  EVENT E2,
  TEST_REQUEST TR2
WHERE
  TR.TEST_ACC=3418
  AND TR.TEST_ACC=E.TEST_ACC
  AND E.EVENT_ID=RE.EVENT_ID
  AND RE.RESULT_ID=RE2.RESULT_ID
  AND E2.EVENT_ID=RE2.EVENT_ID
  AND E2.TEST_ACC=TR2.TEST_ACC;

  TEST_ACC   EVENT_ID RESULTEVENT_ID  RESULT_ID RESULTEVENT_ID   EVENT_ID TEST_ACC   RN
---------- ---------- -------------- ---------- -------------- ---------- ---------- --
      3418      42179           6346       4126           4126      28004      2248   1
      3418      42179           6346       4126           6346      42179      3418   2
      3418      42178           6345       6321           6345      42178      3418   1

Now, if we can filter out any of the rows that do not have RN=1, we may have a usable solution.  By sliding the above into an inline view (and adding column aliases as necessary in the inline view), we can add a WHERE clause to return only those rows with RN=1:

SELECT
  TEST_ACC,
  EVENT_ID,
  RESULTEVENT_ID,
  RESULT_ID,
  RESULTEVENT_ID2,
  EVENT_ID2,
  TEST_ACC2
FROM
  (SELECT
    TR.TEST_ACC,
    E.EVENT_ID,
    RE.RESULTEVENT_ID,
    RE.RESULT_ID,
    RE2.RESULTEVENT_ID RESULTEVENT_ID2,
    RE2.EVENT_ID EVENT_ID2,
    TR2.TEST_ACC TEST_ACC2,
    ROW_NUMBER() OVER (PARTITION BY RE.RESULT_ID ORDER BY RE2.EVENT_ID) RN
  FROM
    TEST_REQUEST TR,
    EVENT E,
    RESULT_EVENT RE,
    RESULT_EVENT RE2,
    EVENT E2,
    TEST_REQUEST TR2
  WHERE
    TR.TEST_ACC=3418
    AND TR.TEST_ACC=E.TEST_ACC
    AND E.EVENT_ID=RE.EVENT_ID
    AND RE.RESULT_ID=RE2.RESULT_ID
    AND E2.EVENT_ID=RE2.EVENT_ID
    AND E2.TEST_ACC=TR2.TEST_ACC)
WHERE
  RN=1;

  TEST_ACC   EVENT_ID RESULTEVENT_ID  RESULT_ID RESULTEVENT_ID2  EVENT_ID2 TEST_ACC2
---------- ---------- -------------- ---------- --------------- ---------- ----------
      3418      42179           6346       4126            4126      28004      2248
      3418      42178           6345       6321            6345      42178      3418

SQL – ROW_NUMBER – Retrieving Rows from the Midpoint

December 9, 2009

A question appeared in the comp.databases.oracle.server Usenet group a couple of years ago:
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/a8314487f8c414ac

I have a question about rownum which is not mentioned.

Lets say that I want to extract a 3 records out of a result where the middle one is the query.
For example, I have records:

ID      Value 
--      ------
1       Hello 
2       Hi 
3       Wow 
4       Shrek 
5       Shus 
6       What?

And I need a query that by receiving ID, it will extract the record with the id and the previous one to that and the next one. For example for selecting ID=4, it will return records: 3,4,5.

Is there a way to do it in Oracle?

But the ids are not seqeuntial – it can verify from each record, for example:

ID      Value 
225     Hello 
228     Hi 
258     Wow 
240     Shrek 
259     Shus 
230     What?

 I offered the following:

Note: LAG and LEAD will collapse the result into a single row.  If that is not desired, you will need a different approach:
The set up:

CREATE TABLE T1 ( 
  C1 NUMBER(12), 
  C2 VARCHAR2(20)); 
 
INSERT INTO T1 VALUES (1,'Hello'); 
INSERT INTO T1 VALUES (2,'Hi'); 
INSERT INTO T1 VALUES (3,'Wow'); 
INSERT INTO T1 VALUES (4,'Shrek'); 
INSERT INTO T1 VALUES (5,'Shus'); 
INSERT INTO T1 VALUES (6,'What?');

A simple query using the ROW_NUMBER analytical function:

SELECT 
  C1, 
  C2, 
  ROW_NUMBER() OVER (ORDER BY C1) RN 
FROM 
  T1; 
 
        C1 C2                           RN 
---------- -------------------- ---------- 
         1 Hello                         1 
         2 Hi                            2 
         3 Wow                           3 
         4 Shrek                         4 
         5 Shus                          5 
         6 What?                         6

Sliding the above into an inline view to retrieve only those on either side of RN=4:

SELECT 
  T.C1, 
  T.C2 
FROM 
  (SELECT 
    C1, 
    C2, 
    ROW_NUMBER() OVER (ORDER BY C1) RN 
  FROM 
    T1) T 
WHERE 
  T.RN BETWEEN (4 -1) AND (4 +1);
        C1 C2 
---------- ------ 
         3 Wow 
         4 Shrek 
         5 Shus

But, the above is not exactly what you need, unless C1 (your ID column) always starts at 1 and incements by 1.  Essentially listing the inline view twice with a join fixes the problem:

SELECT 
  T.C1, 
  T.C2 
FROM 
  (SELECT 
    C1, 
    C2, 
    ROW_NUMBER() OVER (ORDER BY C1) RN 
  FROM 
    T1) T, 
  (SELECT 
    C1, 
    ROW_NUMBER() OVER (ORDER BY C1) RN 
  FROM 
    T1) T2 
WHERE 
  T2.C1=4 
  AND T.RN BETWEEN (T2.RN -1) AND (T2.RN +1); 
 
        C1 C2 
---------- ----- 
         5 Shus 
         4 Shrek 
         3 Wow

A quick test:

DELETE FROM 
  T1 
WHERE 
  C1=3;

The first method results in:

        C1 C2 
---------- ----- 
         4 Shrek 
         5 Shus 
         6 What?

The second method results in:

        C1 C2 
---------- ----- 
         5 Shus 
         4 Shrek 
         2 Hi

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

A couple other people in the thread offered suggestions, including this one by Martin T.:
Maybe Analytic Functions can help:

select * from ( 
SELECT some_table.*,
 lead(ID) over (order by date) as LEAD_ID,
 lag(id) over (order by date) as LAG_ID 
from some_table 
) some_table_plus 
where ID      = :search_id 
    or LEAD_ID = :search_id 
    or LAG_ID  = :search_id 
order by date

Failure to Collect Fixed Object Statistics Leads to ORA-01013 or ORA-07445

December 8, 2009 (note added December 9, 2009)

An interesting post from almost two years ago in the comp.databases.oracle.server Usenet group:
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/e0599d3e043fc199

select distinct name from all_source;

.......
SYS_YOID0000052452$
......
3196 rows selected.

Ok no problem.

select distinct owner from v$access
SQL> /
select distinct owner from v$access
                           *
ERROR at line 1:
ORA-01013: user requested cancel of current operation

I had to terminate it as it become non-terminating.

“select owner from v$access” returns a mere 193 rows, and it cannot sort it?

I suspect there is exists an infinite loop somewhere.

This was attempted on 10gR2, 11gR1 and both had the same problem.

How would someone start investigating this problem?  Find something that appears out of the ordinary, and start probing.  This is the approach that I used:

–

I was able to reproduce this problem on Oracle 10.2.0.2 with the Oracle October 2006 CPU on 64 bit Windows 2003.

From the udump trace file:

ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION]
[__intel_new_memcpy+610] [PC:0x3236532] [ADDR:0x0] [UNABLE_TO_READ] []
Current SQL statement for this session:
select distinct owner from v$access
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
__intel_new_memcpy+           0000000000000000     000000000 000000000
0118AF5A0 610                                                7FF970C7598
000007FF95D155F0     CALL???  __intel_new_memcpy+  0000007FF 013DF42E8
001749686
                              610                  000000000
...

From a 10046 trace at level 8:

*** 2008-01-23 06:51:57.901
WAIT #3: nam='latch: library cache' ela= 8566 address=1398554576 number=214 tries=1 obj#=-1 tim=5204643696
WAIT #3: nam='latch: library cache' ela= 859 address=1398554576 number=214 tries=1 obj#=-1 tim=5205047765
WAIT #3: nam='latch: library cache' ela= 2958 address=1398554576 number=214 tries=1 obj#=-1 tim=5205183748
WAIT #3: nam='latch: library cache' ela= 551 address=1398554576 number=214 tries=1 obj#=-1 tim=5205267198
*** 2008-01-23 06:54:39.117
WAIT #3: nam='latch: library cache' ela= 813 address=1398554576 number=214 tries=1 obj#=-1 tim=5365848854
*** 2008-01-23 06:55:06.288
WAIT #3: nam='latch: library cache' ela= 30 address=1398554576 number=214 tries=1 obj#=-1 tim=5393019180
*** 2008-01-23 06:55:30.006
WAIT #3: nam='latch: library cache' ela= 68 address=1398554576 number=214 tries=1 obj#=-1 tim=5416746379
*** 2008-01-23 06:55:50.584
WAIT #3: nam='latch: library cache' ela= 33 address=1398554576 number=214 tries=1 obj#=-1 tim=5437323921
*** 2008-01-23 06:57:09.536
WAIT #3: nam='latch: library cache' ela= 111 address=1398554576 number=214 tries=1 obj#=-1 tim=5516279642
*** 2008-01-23 06:57:20.895
WAIT #3: nam='latch: library cache' ela= 77 address=1398554576 number=214 tries=1 obj#=-1 tim=5527627340
*** 2008-01-23 06:57:36.082
WAIT #3: nam='latch: library cache' ela= 246 address=1398554576 number=214 tries=1 obj#=-1 tim=5542815685
*** 2008-01-23 06:57:57.957
WAIT #3: nam='latch: library cache' ela= 123 address=1398554576 number=214 tries=1 obj#=-1 tim=5564704225
*** 2008-01-23 06:58:14.644
WAIT #3: nam='latch: library cache' ela= 63 address=1398554576 number=214 tries=1 obj#=-1 tim=5581385020
*** 2008-01-23 06:58:26.269
WAIT #3: nam='latch: library cache' ela= 62 address=1398554576 number=214 tries=1 obj#=-1 tim=5593004724
*** 2008-01-23 06:58:48.346
WAIT #3: nam='latch: library cache' ela= 42 address=1398554576 number=214 tries=1 obj#=-1 tim=5615094241
WAIT #3: nam='latch: library cache' ela= 8 address=1398554576 number=214 tries=1 obj#=-1 tim=5615168161
...

For an average 60 second interval interval, the session had the following latch statistics:

Latch         Child# Level   Gets  Misses  Sleeps  Sleeps1
LIBRARY CACHE    1       5   529418     25      0       25
LIBRARY CACHE    2       5   539720     36      0       36
LIBRARY CACHE    3       5   519189     15      0       15
LIBRARY CACHE    4       5   516501     55      0       55
LIBRARY CACHE    5       5   524907   1744      4     1740

On the waits, P1 is 13985, P2 is 214, P3 is 0, Buf is 99, SQL Hash is 3250939240.

SELECT
  SQL_TEXT
FROM
  V$SQL
WHERE
  HASH_VALUE=3250939240

SQL_TEXT
-----------------------------------
select distinct owner from v$access

 

SELECT
  ID,
  SUBSTR(OPERATION,1,12) OPERATION,
  SUBSTR(OPTIONS,1,12) OPTIONS,
  SUBSTR(OBJECT_OWNER||'.'||OBJECT_NAME,1,20) OBJECT,
  SUBSTR(OBJECT_TYPE,1,13) OBJECT_TYPE,
  PARENT_ID,
  DEPTH,
  POSITION,
  CPU_COST
FROM
  V$SQL_PLAN_STATISTICS_ALL
WHERE
  HASH_VALUE=3250939240
  AND CHILD_NUMBER=1
ORDER BY
  ID;

ID OPERATION    OPTIONS      OBJECT               OBJECT_TYPE PARENT_ID      DEPTH   POSITION   CPU_COST
-- ------------ ------------ -------------------- ------------- ---------- ---------- ---------- ----------
 1 HASH UNIQUE       .                                                  0          1          1    2142850
 2 NESTED LOOPS              .                                          1          2          1    1115000
 3 NESTED LOOPS              .                                          2          3          1    1080000
 4 MERGE JOIN CARTESIAN    .                                            3          4          1     730000
 5 FIXED TABLE  FULL         SYS.X$KSUSE          TABLE (FIXED)         4          5          1     380000
 6 BUFFER SORT         .                                                4          5          2     350000
 7 FIXED TABLE  FULL         SYS.X$KGLDP          TABLE (FIXED)         6          6          1     350000
 8 FIXED TABLE  FIXED INDEX  SYS.X$KGLLK (ind:1)  TABLE (FIXED)         3          4          2       3500
 9 FIXED TABLE  FIXED INDEX  SYS.X$KGLOB (ind:1)  TABLE (FIXED)         2          3          2       3500

 

SELECT
  ID,
  ACCESS_PREDICATES,
  FILTER_PREDICATES
FROM
  V$SQL_PLAN_STATISTICS_ALL
WHERE
  HASH_VALUE=3250939240
  AND CHILD_NUMBER=1
ORDER BY
  ID;

ID ACCESS_PREDICATES FILTER_PREDICATES
-- ----------------- -----------------
 1
 2
 3
 4
 5                   "S"."INST_ID"=USERENV('INSTANCE')
 6
 7
 8                   ("L"."KGLLKUSE"="S"."ADDR" AND "L"."KGLLKHDL"="D"."KGLHDADR" AND "L"."KGLNAHSH"="D"."KGLNAHSH")
 9                   ("O"."KGLNAHSH"="D"."KGLRFHSH" AND "O"."KGLHDADR"="D"."KGLRFHDL")

—
Then check a different server:

The query eventually completed on the 32 bit version of Oracle 10.2.0.2 with the Oracle October 2006 CPU.

PARSE 1|CPU S     0.000000|CLOCK S    0.006227|ROWs 0
EXEC  1|CPU S     0.000000|CLOCK S    0.000201|ROWs 0
FETCH 2|CPU S 13112.828125|CLOCK S  926.981803|ROWs 6

Row Source Execution Plan:
       (Rows 6)   HASH UNIQUE (cr=0 pr=0 pw=0 time=568347223 us)
    (Rows 3463)    NESTED LOOPS  (cr=0 pr=0 pw=0 time=3464796755 us)
    (Rows 3463)     NESTED LOOPS  (cr=0 pr=0 pw=0 time=3464592419 us)
 (Rows 1613768)      MERGE JOIN CARTESIAN (cr=0 pr=0 pw=0 time=21019488 us)
     (Rows 236)       FIXED TABLE FULL X$KSUSE (cr=0 pr=0 pw=0 time=2376 us)
 (Rows 1613768)       BUFFER SORT (cr=0 pr=0 pw=0 time=12951356 us)
    (Rows 6838)        FIXED TABLE FULL X$KGLDP (cr=0 pr=0 pw=0 time=41073 us)
    (Rows 3463)      FIXED TABLE FIXED INDEX X$KGLLK (ind:1) (cr=0 pr=0 pw=0 time=13094082350 us)
    (Rows 3463)     FIXED TABLE FIXED INDEX X$KGLOB (ind:1) (cr=0 pr=0 pw=0 time=166548 us)

Note the merge Cartesian join between the 236 rows in X$KSUSE and the 1613768 rows from X$KGLDP.

The wait events:
0.03 seconds on latch: library cache

—

Then keep probing:

I may have found something that may help the OP – it hit me when I found very slow performance with the same SQL statement on 32 bit Oracle 10.2.0.3 and 11.1.0.6, after looking at the DBMS_XPLANs.

The DBMS_XPLAN on 10.2.0.3:

--------------------------------------------------------------------------­----------------------------------------------
| Id  | Operation                  | Name            | Starts | E-Rows | A-Rows |   A-Time   |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------­----------------------------------------------
|   1 |  HASH UNIQUE               |                 |      1 |    105 |      5 |00:02:51.06 |       |       |          |
|   2 |   NESTED LOOPS             |                 |      1 |    105 |   1131 |00:02:51.04 |       |       |          |
|   3 |    NESTED LOOPS            |                 |      1 |     10 |   1131 |00:02:50.39 |       |       |          |
|   4 |     MERGE JOIN CARTESIAN   |                 |      1 |    100 |    180K|00:00:01.27 |       |       |          |
|*  5 |      FIXED TABLE FULL      | X$KSUSE         |      1 |      1 |    236 |00:00:00.01 |       |       |          |
|   6 |      BUFFER SORT           |                 |    236 |    100 |    180K|00:00:00.55 | 36864 | 36864 |32768  (0)|
|   7 |       FIXED TABLE FULL     | X$KGLDP         |      1 |    100 |    763 |00:00:00.01 |       |       |          |
|*  8 |     FIXED TABLE FIXED INDEX| X$KGLLK (ind:1) |    180K|      1 |   1131 |00:02:48.31 |       |       |          |
|*  9 |    FIXED TABLE FIXED INDEX | X$KGLOB (ind:1) |   1131 |     10 |   1131 |00:00:00.64 |       |       |          |
--------------------------------------------------------------------------­----------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter("S"."INST_ID"=USERENV('INSTANCE'))
   8 - filter(("L"."KGLLKUSE"="S"."ADDR" AND "L"."KGLLKHDL"="D"."KGLHDADR" AND "L"."KGLNAHSH"="D"."KGLNAHSH"))
   9 - filter(("O"."KGLNAHSH"="D"."KGLRFHSH" AND "O"."KGLHDADR"="D"."KGLRFHDL"))

 
Note the MERGE JOIN CARTESIAN, and how the estimated rows compares with the actual rows.

The DBMS_XPLAN on 11.1.0.6:

select distinct owner from v$access 

--------------------------------------------------------------------------­-----------------------------------------------
| Id  | Operation                   | Name            | Starts | E-Rows | A-Rows |   A-Time   |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------­-----------------------------------------------
|   1 |  HASH UNIQUE                |                 |      1 |      1 |      6 |00:00:40.28 |   951K|   951K|  860K (0)|
|   2 |   NESTED LOOPS              |                 |      1 |      1 |   2342 |00:00:40.27 |       |       |          |
|   3 |    MERGE JOIN CARTESIAN     |                 |      1 |      1 |   2842K|00:00:11.37 |       |       |          |
|   4 |     NESTED LOOPS            |                 |      1 |      1 |  16721 |00:00:00.38 |       |       |          |
|   5 |      FIXED TABLE FULL       | X$KGLDP         |      1 |    100 |  16721 |00:00:00.05 |       |       |          |
|*  6 |      FIXED TABLE FIXED INDEX| X$KGLOB (ind:1) |  16721 |      1 |  16721 |00:00:00.21 |       |       |          |
|   7 |     BUFFER SORT             |                 |  16721 |      1 |   2842K|00:00:02.91 |  4096 |  4096 | 4096  (0)|
|*  8 |      FIXED TABLE FULL       | X$KSUSE         |      1 |      1 |    170 |00:00:00.01 |       |       |          |
|*  9 |    FIXED TABLE FIXED INDEX  | X$KGLLK (ind:1) |   2842K|      1 |   2342 |00:00:15.49 |       |       |          |
--------------------------------------------------------------------------­-----------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   6 - filter(("O"."KGLNAHSH"="D"."KGLRFHSH" AND "O"."KGLHDADR"="D"."KGLRFHDL"))
   8 - filter("S"."INST_ID"=USERENV('INSTANCE'))
   9 - filter(("L"."KGLLKUSE"="S"."ADDR" AND "L"."KGLLKHDL"="D"."KGLHDADR" AND "L"."KGLNAHSH"="D"."KGLNAHSH"))

The above executed more quickly, and the plan is slightly different, but the MERGE JOIN CARTESIAN is still present, as is the difference between the estimated and actual number of rows.

The fixed object stats must be wrong (I recall having a problem with that a couple years ago when perfoming the following)…

SQL> CONNECT / AS SYSDBA
SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS('ALL');
BEGIN DBMS_STATS.GATHER_FIXED_OBJECTS_STATS('ALL'); END;

*
ERROR at line 1:
ORA-20000: Insufficient privileges to analyze an object in Fixed Ob
ORA-06512: at "SYS.DBMS_STATS", line 17951
ORA-06512: at "SYS.DBMS_STATS", line 18404
ORA-06512: at "SYS.DBMS_STATS", line 18951
ORA-06512: at line 1

The same error occurs on Oracle 10.2.0.2, 10.2.0.3, and 11.1.0.6 as the internal user, SYS AS SYSDBA, and SYSTEM.

There must be another way:

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS',GATHER_FIXED=>TRUE)

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

The new DBMS_XPLANs:
10.2.0.3:

--------------------------------------------------------------------------­---------------------------------------------
| Id  | Operation                 | Name            | Starts | E-Rows | A-Rows |   A-Time   |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------­---------------------------------------------
|   1 |  HASH UNIQUE              |                 |      1 |      7 |      4 |00:00:00.09 |       |       |          |
|   2 |   NESTED LOOPS            |                 |      1 |   1822 |   1003 |00:00:00.08 |       |       |          |
|*  3 |    HASH JOIN              |                 |      1 |   1822 |   1003 |00:00:00.05 |   898K|   898K| 1099K (0)|
|*  4 |     HASH JOIN             |                 |      1 |   1822 |   1897 |00:00:00.03 |  1010K|  1010K|  639K (0)|
|*  5 |      FIXED TABLE FULL     | X$KSUSE         |      1 |    236 |    236 |00:00:00.01 |       |       |          |
|   6 |      FIXED TABLE FULL     | X$KGLLK         |      1 |   1822 |   1897 |00:00:00.01 |       |       |          |
|   7 |     FIXED TABLE FULL      | X$KGLDP         |      1 |   2892 |    649 |00:00:00.01 |       |       |          |
|*  8 |    FIXED TABLE FIXED INDEX| X$KGLOB (ind:1) |   1003 |      1 |   1003 |00:00:00.01 |       |       |          |
--------------------------------------------------------------------------­---------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("L"."KGLLKHDL"="D"."KGLHDADR" AND "L"."KGLNAHSH"="D"."KGLNAHSH")
   4 - access("L"."KGLLKUSE"="S"."ADDR")
   5 - filter("S"."INST_ID"=USERENV('INSTANCE'))
   8 - filter(("O"."KGLNAHSH"="D"."KGLRFHSH" AND "O"."KGLHDADR"="D"."KGLRFHDL"))

The MERGE JOIN CARTESIAN is gone and the execution time dropped from 2 minutes, 51 seconds to 0.09 seconds.

11.1.0.6:

--------------------------------------------------------------------------­----------------------------------------------
| Id  | Operation                  | Name            | Starts | E-Rows | A-Rows |   A-Time   |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------­----------------------------------------------
|   1 |  HASH UNIQUE               |                 |      1 |     19 |      1 |00:00:00.04 |  1037K|  1037K|  368K (0)|
|   2 |   NESTED LOOPS             |                 |      1 |   1139 |    134 |00:00:00.04 |       |       |          |
|   3 |    NESTED LOOPS            |                 |      1 |   1139 |    134 |00:00:00.03 |       |       |          |
|*  4 |     HASH JOIN              |                 |      1 |   1139 |   1144 |00:00:00.02 |  1010K|  1010K| 1205K (0)|
|*  5 |      FIXED TABLE FULL      | X$KSUSE         |      1 |    170 |    170 |00:00:00.01 |       |       |          |
|   6 |      FIXED TABLE FULL      | X$KGLLK         |      1 |   1139 |   1144 |00:00:00.01 |       |       |          |
|*  7 |     FIXED TABLE FIXED INDEX| X$KGLDP (ind:1) |   1144 |      1 |    134 |00:00:00.01 |       |       |          |
|*  8 |    FIXED TABLE FIXED INDEX | X$KGLOB (ind:1) |    134 |      1 |    134 |00:00:00.01 |       |       |          |
--------------------------------------------------------------------------­----------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("L"."KGLLKUSE"="S"."ADDR")
   5 - filter("S"."INST_ID"=USERENV('INSTANCE'))
   7 - filter(("L"."KGLLKHDL"="D"."KGLHDADR" AND "L"."KGLNAHSH"="D"."KGLNAHSH"))
   8 - filter(("O"."KGLNAHSH"="D"."KGLRFHSH" AND "O"."KGLHDADR"="D"."KGLRFHDL"))

The MERGE JOIN CARTESIAN is gone and the execution time dropped from 40.28 seconds to 0.04 seconds.

The OP may be able to run the same DBMS_STATS.GATHER_SCHEMA_STATS procedure to work around the problem.

—

How to determine if I collected statistics on the fixed tables?  A search on Metalink found this article from 2004:
https://metalink.oracle.com/metalink/plsql/f%3Fp%3D200:27:5000154048035945504::::p27_id,p27_show_header,p27_show_help:525959.996,1,1&usg=AFQjCNHJ_wI9tlazsGQ7AIUZ5RSlrp_8nw
(Edit: Note that the above link is gone in a Flash…  If anyone is able to find the message on the Oracle support site -the site  formerly known as Metalink, please let me know of the address.)

In the article, Jonathan Lewis mentioned that tab_stats$ could be checked.
Test database on 64 bit Windows:

SELECT
  *
FROM
  SYS.TAB_STATS$

The above returned about 582 rows after running:

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS',GATHER_FIXED=>TRUE);

I then checked a production database, and found no rows returned. Using the suggestion from the Metalink article:

EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(NULL);

The stats collection completed much more quickly, as it did not analyze the full SYS schema, and there were 582 rows returned by the query on SYS.TAB_STATS$.  It looks like this simple query may be used to determine if fixed object statistics need to be collected.