Re: Still need help with Parsing/Recursive Calls

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.

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.
Received on Sat Apr 06 2002 - 09:43:57 CST