Path: news.easynews.com!easynews!cyclone.swbell.net!pln-e!spln!dex!extra.newsguy.com!newsp.newsguy.com!drn
From: Thomas Kyte <tkyte@oracle.com>
Newsgroups: comp.databases.oracle.server
Subject: Re: Still need help with Parsing/Recursive Calls
Date: 4 Apr 2002 12:42:01 -0800
Organization: Oracle
Lines: 178
Message-ID: <a8idqp02b5a@drn.newsguy.com>
References: <36503db6.0204031125.769d8588@posting.google.com> <a2b6d46b.0204031734.422b2465@posting.google.com> <36503db6.0204040841.198dbb77@posting.google.com>
NNTP-Posting-Host: p-719.newsdawg.com
X-Newsreader: Direct Read News 2.91
Xref: easynews comp.databases.oracle.server:142089
X-Received-Date: Thu, 04 Apr 2002 14:04:10 MST (news.easynews.com)

In article <36503db6.0204040841.198dbb77@posting.google.com>,
anil.chada@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@attbi.com (MS) wrote in message
>news:<a2b6d46b.0204031734.422b2465@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@oracle.com (anil chada) wrote in message
>>news:<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

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