TKPROF: Long CPU time on Execute

From: Pete <pkolton_at_hotmail.com>
Date: 16 Apr 2004 09:20:32 -0700
Message-ID: <ed168c92.0404160820.7b66201@posting.google.com>

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)

Received on Fri Apr 16 2004 - 11:20:32 CDT