IWETHEY v. 0.3.0 | TODO
1,095 registered users | 0 active users | 0 LpH | Statistics
Login | Create New User
IWETHEY Banner

Welcome to IWETHEY!

New Would that help for a small result set?
the query normally returns < 10 rows. It's a way for production to track the "hot jobs", of which there should hopefully not be many.
Darrell Spice, Jr.                      [link|http://spiceware.org/gallery/ArtisticOverpass|Artistic Overpass]\n[link|http://www.spiceware.org/|SpiceWare] - We don't do Windows, it's too much of a chore
New It might
The optimizer relies on statistics to figure out how much work it will need to do. When it can't project very well it has to be pessimistic. If you're doing something that it can't project very well (which I'd think calling PL/SQL would be), it may be using an internal estimate of rows returned and space used which bears no relation to what you actually wind up with.

In that case setting the sort size shouldn't hurt because (but check this with a DBA!) Oracle doesn't actually reserve the space until it needs it. You're just telling it how much space it should be prepared to reserve. So you're just fooling it into using the query plan that you want.

Also if you have competent DBAs, then you should be talking to them for another reason. There are techniques that they can use to get a particular query plan to be used then lock it in. (The key phrase is "stored outlines".) That is a better solution, because even if you get it to use the right plan now, you have no way of knowing when changing statistics will cause Oracle to switch its evaluation of the right query plan to a much worse one.

Cheers,
Ben
I have come to believe that idealism without discipline is a quick road to disaster, while discipline without idealism is pointless. -- Aaron Ward (my brother)
New I'll see how it works then
we don't have a DBA so I'll read up on it and check with my boss. He knows a bit more about Oracle than I do. He's leaving in a couple months so I suspect I'll be the "acting DBA".

Thanx!
Darrell Spice, Jr.                      [link|http://spiceware.org/gallery/ArtisticOverpass|Artistic Overpass]\n[link|http://www.spiceware.org/|SpiceWare] - We don't do Windows, it's too much of a chore
Expand Edited by SpiceWare July 27, 2005, 08:30:41 PM EDT
     Oracle Optimization help? - (SpiceWare) - (5)
         I blew out the query - (SpiceWare) - (4)
             One trick that might work with the original... - (ben_tilly) - (3)
                 Would that help for a small result set? - (SpiceWare) - (2)
                     It might - (ben_tilly) - (1)
                         I'll see how it works then - (SpiceWare)

We lived in Arizona, and the skies always had little fluffy clouds in them.
50 ms