Re: Still need help with Parsing/Recursive Calls

In article <36503db6.0204040841.198dbb77_at_posting.google.com>, anil.chada_at_oracle.com says...
>
>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.

SQL done in PLSQL is counted as recursive SQL. The excessive parse time is coming from your query:

>> > call count cpu elapsed disk query current
>> > rows
>> > ------- ------ -------- ---------- ---------- ---------- ----------
>> > ----------
>> > Parse 13910 139.38 1517.04 0 0 0
>> > 0

if that is associated with your query and is called from PLSQL, its your excessive parse time thats in the recursive sql bit.

If you take that query, whatever it is, and just isolate it in sqlplus doing the following:

SQL> variable <define the binds>
SQL> alter session set sql_trace=true;
SQL> ..... the query .....
SQL> exit

and tkprof that -- whats the parse time on it. start from there.

Also -- if this is dynamic SQL in plsql (must be, else the parse count would be 1), consider using DBMS_SQL and parsing the cursor ONCE per session, not once per EXECUTION!

>
>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

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp