Re: Still need help with Parsing/Recursive Calls

In article <36503db6.0204060743.75337b4f_at_posting.google.com>, anil.chada_at_oracle.com says...
>
>Tom & Bass --
>
>Thanks for your input.
>Some clarifications regarding your questions.......
>
>The program is calling a function (part of a package), and the SQL
>statement causing trouble is part of that package. I thought oracle
>classifies regular SQL as non-recursive calls. I did not know that it
>puts them under recursive call if they are part of a package or
>function.
>
>I also checked that the SQL is not dynamic sql, and as tom suggested i
>tested the SQL statement by itself (i substibuted some dummy values
>for bind variables), and there were no recursive calls. That is why i
>was little bit confused when oracle is saying, it spent most of the
>time for recursive call, when i saw no recurive calls while testing
>individual SQL statement.

well, lets see the CODE and the full TKPROF that shows the statement in question.

If it is not dynamically executed, I don't see why its getting parsed more then once.

We need to see the statement that is getting parsed more then once and the code that contains that statement.

>
>I also checked the segment sizes for the table involved in the query,
>and maximum number of extents i found was 22. While the program was
>running i monitored v$sqlarea and same SQL statement popped up on the
>top when i ordered by parse_calls and disk_reads.
>
>I never thought of waits acually.......
>I will check the session waits and post the results as soon as
>possible.
>
>Once again thanks for your time and help
>Anil
>
>
>
>
>bchorng_at_yahoo.com (Bass Chorng) wrote in message
>news:<bd9a9a76.0204041609.5f771f5d_at_posting.google.com>...
>> Note that the difference between your cpu and elapsed time. That means
>> the database spent a lot of time waiting. You should also check
>> v$session_event to determine how oracle spent its wait time on this
>> session. The total time spent in all events waited should be about
>> the same to your total elapsed time minus cpu time.
>>
>> If you tune this SQL you can only improve within that 139 seconds,
>> however if you tune your database, you can improve within that 1517 seconds -
>> a much better payback. I would be more interested in why it takes that
>> long to wait.
>>
>> Also the reason it has high recursive time might be because this SQL
>> statement comes from a function, procedure or package. Oracle categorizes
>> these SQLs as recursive calls although they are really regular SQLs. So
>> that might misled you too. I will be *very* surprised if they are
>> indeed recursive calls as the ratio is way too high. The only close
>> possibility is you have LOTS of extents.

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