Re: Slow Query in PL/SQL Block

Sybrand Bakker wrote:
> On 14 Aug 2005 11:01:32 -0700, brijeshmathew_at_gmail.com wrote:
>
> >Hi Daniel
> >
> >Thanks for the reply.
> >I am using a stored procedure, that returns a resultset, that I am
> >using for reporting purposes. I have just taken the extract of my code,
> >just to show where it is consuming lot of execution time, however, if I
> >do an equivalent query, which is not actually my requirement here, it
> >works faster.
> >Thing is , for each item in the cursor, i need to calculate some
> >values, like I showed in my original post. I hope I am making myself
> >clear.
> >You said to use collections, but I am not sure how to do that in this
> >particular case that I showed you. Can you give me some example. ?
> >
> >Thanks
> >
> >Brijesh Mathew
>
> Apart from using collections (which can be looked up in the PL/SQL
> manual)
> queries in PL/SQL run by default in all_rows mode. If your instance is
> in FIRST_ROWS mode, that might explain the difference.
> Did you compare execution plans?
>
>
> --
> Sybrand Bakker, Senior Oracle DBA

Hi Sybrand

Thanks for your suggestion. The optimizer mode for my instance is set to CHOOSE. Would you suggest me to change that and see whether that would solve the problem. ? Would that affect the performance of other SQL queries and other areas. ?
I tried putting a FIRST_ROWS hint in the query in my PL/SQL block query, however that did not give any positive results. What I am doing in my stored procedure is, fetch some items into a cursor from a master table, and for each item, calculate some totals from a trasaction table, which has one million rows. Some of these are calculations are infact done by stored functions, which I call in my loop.
Would there be a better approach to this. ? Received on Mon Aug 15 2005 - 00:16:35 CDT