TKPROF: Long CPU time on Execute

Giganews Newsgroups
Subject: TKPROF: Long CPU time on Execute
Posted by:  Pete (pkolt…@hotmail.com)
Date: 16 Apr 2004

I've got the following results from a trace.  The SELECT statement
I've posted here wasn't the worst performing, but it is the simplest
SELECT in the file, and it's still pretty bad!

What is puzzling me is the very high CPU and Elapsed time on Execute.
Massively higher than on the Fetch.  I know that an elapsed time that
is much longer than the CPU time indicates that the session was
waiting for something, but that isn't the case here.

As you can see; 1.33 seconds CPU on the Fetch, but 1484 seconds on the
Execute!  I thought that it was only Inserts and Updates that did a
lot of work on the Execute?

SELECT *
FROM
XXX_CALENDAR  WHERE CALENDAR_DATE = :b1  AND CALENDAR_TYPE = 'P'  AND
  STATE_CODE = :b2

call    count      cpu    elapsed      disk      query    current
    rows
------- ------  -------- ---------- ---------- ---------- ----------
----------
Parse        0      0.00      0.00          0          0          0
        0
Execute  8701  1484.77    1955.71          0          0          0
        0
Fetch    8701      1.33      1.24          0      26103          0
      281
------- ------  -------- ---------- ---------- ---------- ----------
----------
total    17402  1486.10    1956.95          0      26103          0
      281

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

Rows    Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT  GOAL: CHOOSE
      0  TABLE ACCESS  GOAL: ANALYZED (BY INDEX ROWID) OF
              'ELSIS_CALENDAR'
      0    INDEX  GOAL: ANALYZED (RANGE SCAN) OF
'I_CALENDAR$STATE_DATE'
              (NON-UNIQUE)

Replies