Still need help with Parsing/Recursive Calls

From: anil chada <anil.chada_at_oracle.com>
Date: 3 Apr 2002 11:25:44 -0800
Message-ID: <36503db6.0204031125.769d8588@posting.google.com>

Hi --

I am having trouble resolving performance problem with one of the PL/SQL program written by
developer. After reading about different types of parsing (hard parse, soft parse, and softer soft parse),
i added session_cached_cursors to init.ora file. Following are some cursor related init.ora
parameters i am using.

NAME                           VALUE


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

open_cursors                   500
cursor_space_for_time          FALSE
session_cached_cursors         100
cursor_sharing                 EXACT

After setting cursor cache parameter, i ran trace on the proram and i found one SQL stmt which is
taking lot of time during parsing. Following are the statistics related to that SQL stmt.

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

When i look at the totals at the end of the output file from trace i found following information.

---

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current  
     rows

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

---

Parse        1      0.73       4.30          0          0          0  
        0
Execute      1      0.00       0.04          0          0          0  
        0
Fetch        0      0.00       0.00          0          0          0  
        0

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

---

total        2      0.73       4.34          0          0          0  
        0

Misses in library cache during parse: 1
Misses in library cache during execute: 1

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current  
     rows

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

---

Parse    18740    184.64    2008.98          0          0          0  
        0
Execute 167919     29.78      34.97          0        118          0  
        1
Fetch   251701    392.54     527.57       8596   50197274       2788  

   140777
------- ------ -------- ---------- ---------- ---------- ----------

---

total 438360 606.96 2571.52 8596 50197392 2788

   140778

Misses in library cache during parse: 61 Misses in library cache during execute: 1

18548 user SQL statements in session.
  195 internal SQL statements in session. 18743 SQL statements in session.

---

If i look at the totals, looks like oracle is spending most of the time parsing recursive calls,
but when i look at the individual SQL statements, it looks like oracle spent most of the time parsing regular SQL stmt.

I also ran tkprof with SYS=YES option and i did not find any internal SQL statements with High Parsing time.

I am just having trouble understading where exactly the problem is... Can anyone shed some light on this issue?

if oracle is spending most of the time parsing recursive calls, then can anyone provide me with some ideas about, how to isolate the problem and fix it (apart from increasing share pool size)?

Any input is greatly appreciated.

Thanks
Anil Received on Wed Apr 03 2002 - 13:25:44 CST