Neither of the execution plans you have shown corresponds to the operation of
getting the next sequence value from DUAL. In fact they both belong to recursive
SQL statements (at recursive depth: 1). The second looks a bit like the
recursive SQL that would be generated when refreshing the sequence cache with
sequence numbers from the data dictionary, but the first one does not seem to be
related at all. I suspect that something like a file system full problem caused
the trace file to be corrupted so that TKPROF is giving you rubbish. If not, and
if you can reproduce this, then please send me the raw trace file as well as the
TKPROF output next time.


-----Original Message-----
Sent: Thursday, 17 August 2000 0:36


Look at the following two sequences. Why would one access method be
different from the other?

Sequence 1:

select calbck_s.nextval from dual

call     count       cpu    elapsed       disk      query    current       rows
------- ------  -------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.02       0.01          0          0          0          0
Execute      1      0.00       0.00          0          0          0          0
Fetch        1      0.00       0.00          0          1          5          1
------- ------  -------- ---------- ---------- ---------- ---------- ----------
total        3      0.02       0.01          0          1          5          1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 67  (APC_ACTV)   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      4  MERGE JOIN OUTER
      6   SORT JOIN
      4    TABLE ACCESS CLUSTER IND$
      4     INDEX UNIQUE SCAN (object id 3)
      2   SORT JOIN
      2    VIEW
      2     SORT GROUP BY
      2      TABLE ACCESS CLUSTER CDEF$
      4       INDEX UNIQUE SCAN (object id 27)


Sequence 2:

select reqipr_s.nextval from dual

call     count       cpu    elapsed       disk      query    current       rows
------- ------  -------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.01       0.00          0          0          0          0
Execute   1800      0.15       0.24          0          0          0          0
Fetch     1800      0.32       0.19          0       1800       7290       1800
------- ------  -------- ---------- ---------- ---------- ---------- ----------
total     3601      0.48       0.43          0       1800       7290       1800

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 44  (REQUEST_ACTV)   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  UPDATE SEQ$
      1   INDEX UNIQUE SCAN (object id 99)
      1  UPDATE SEQ$
      1   INDEX UNIQUE SCAN (object id 99)
      1  UPDATE SEQ$
      1   INDEX UNIQUE SCAN (object id 99)
      1  UPDATE SEQ$
      1   INDEX UNIQUE SCAN (object id 99)
      1  UPDATE SEQ$