Parse count and elapsed time

From: anil chada <anil.chada_at_oracle.com>
Date: 29 Mar 2002 09:12:01 -0800
Message-ID: <36503db6.0203290912.2b22c27e@posting.google.com>

Hi --

We ran trace for one long running program, and i am little bit confused by looking at the statistics for one SQL statement. Following is the TKPROF output for the SQL statement.

call     count       cpu    elapsed       disk      query    current  
     rows

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

---

Parse    13910    139.38    1517.04          0          0          0  
        0
Execute  13910      7.43       8.87          0          0          0  
        0
Fetch    13910    254.43     315.83       1126   35943544          0  

    13910
------- ------ -------- ---------- ---------- ---------- ----------

---

total 41730 401.24 1841.74 1126 35943544 0

    13910

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

If we look at the above numbers, oracle spent most of the time parsing the SQL statement.
I thought, if there are no library cache misses (TKPROF has cache misses as 1) then oracle should parse the statement only once. I am just wondering why oracle is parsing the statement so many time? Is there anyway i can fix the problem?

The SQL statement has three bind variable.

Thanks for your time and help
Anil Received on Fri Mar 29 2002 - 11:12:01 CST