Re: Still need help with Parsing/Recursive Calls

Is the sql using bind variables? Looks like it's not. If not using then:
set cursor_sharing to 'force' and not 'exact' and rerun.

-MS

anil.chada_at_oracle.com (anil chada) wrote in message news:<36503db6.0204031125.769d8588_at_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 - 19:34:42 CST