xplan bug with Jonathan Lewis Computing Index?

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

Other related posts: