xplan bug with Jonathan Lewis Computing Index?

From: Henry Poras <henry_at_itasoftware.com>
Date: Fri, 29 Jun 2007 16:43:40 -0400
Message-ID: <01ae01c7ba8e$2cf255b0$3800040a@itasoftware.com>

I was running the Jonathan Lewis Oracle Computing Index ( http://www.miraclebenelux.nl/jloci.html ) which is just SQL high in CPU. It does connect-by using an IOT. When I looked at the execution plan using dbms_xplan.display_cursor (v. 10.2.0.3) something didn't look right. The IOT has 20,000 rows, I gathered stats on it, but the rows accessed, both in INDEX FULL SCAN and INDEX RANGE SCAN (access("N">PRIOR NULL) which should be everything) both showed 1000 rows.

Tracing with 10046 has INDEX FULL SCAN with 1 row, and INDEX RANGE SCAN with 19998 rows.

Since dbms_xplan should use real data, why the difference?

Different interpretation of Rows? a bug? anybody else see this?

Henry

DBMS_XPLAN.DISPLAY CURSOR HENRY @test > select *
  2 from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT

---

---

---

SQL_ID dh16y6cxddyz0, child number 0

---

select count(*) /* comment */ from ( select n from cpu_test_dummy
 connect by n > prior n start with n = 1 ) where rownum < 20000

Plan hash value: 3781677993

---

---

| Id  | Operation             | Name              | Rows  | Bytes | Cost
(%CPU)| Time     |


----------------------------------------------------------------------------

---------------

|   0 | SELECT STATEMENT      |                   |       |       |     2
(100)|          |
|   1 |  SORT AGGREGATE       |                   |     1 |       |
|          |
|*  2 |   COUNT STOPKEY       |                   |       |       |
|          |
|   3 |    VIEW               |                   |  1000 |       |     2
(0)| 00:00:01 |
|   4 |     CONNECT BY        |                   |       |       |
|          |
|*  5 |      INDEX UNIQUE SCAN| SYS_IOT_TOP_42136 |     1 |     4 |     1
(0)| 00:00:01 |
|   6 |      INDEX FULL SCAN  | SYS_IOT_TOP_42136 |  1000 |  4000 |     2
(0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN | SYS_IOT_TOP_42136 |  1000 |  4000 |     2

(0)| 00:00:01 |

---

---

Predicate Information (identified by operation id):

---

   2 - filter(ROWNUM<20000)
   5 - access("N"=1)
   7 - access("N">PRIOR NULL)



----------------------------------------------------------------------------

--


----------------------------------------------------------------------------

--

10046 TRACE

select count(*)
   from (
     select n
     from cpu_test_dummy
     connect by n > prior n
     start with n = 1 )
   where rownum < 20000

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        2     30.81      30.08          0      20027          0
1
------- ------  -------- ---------- ---------- ---------- ----------
----------
total        4     30.81      30.08          0      20027          0
1

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

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=20027 pr=0 pw=0 time=30086182 us)
  19999   COUNT STOPKEY (cr=20027 pr=0 pw=0 time=30497072 us)
  19999    VIEW  (cr=20027 pr=0 pw=0 time=30377070 us)
  19999     CONNECT BY  (cr=20027 pr=0 pw=0 time=30277071 us)
      1      INDEX UNIQUE SCAN SYS_IOT_TOP_42136 (cr=2 pr=0 pw=0 time=44
us)(object id 42137)
      1      INDEX FULL SCAN SYS_IOT_TOP_42136 (cr=2 pr=0 pw=0 time=32
us)(object id 42137)
  19998      INDEX RANGE SCAN SYS_IOT_TOP_42136 (cr=20023 pr=0 pw=0
time=446070 us)(object id 42137)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total
Waited
  ----------------------------------------   Waited  ----------
------------
  SQL*Net message to client                       2        0.00
0.00
  SQL*Net message from client                     2       37.31
37.31
****************************************************************************
****


--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jun 29 2007 - 15:43:40 CDT