Re: Still need help with Parsing/Recursive Calls

Thanks for the reply.
I checked and the sql statement is using bind variables.

I am just wondering that the statistics are showing differently totals section compared to details sections for recursive and non recursive calls.

Thanks
Anil

qcom_at_attbi.com (MS) wrote in message news:<a2b6d46b.0204031734.422b2465_at_posting.google.com>...
> 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 Thu Apr 04 2002 - 10:41:13 CST