ALTER SESSION SET SORT_AREA_SIZE=10000000

Sometimes Oracle will switch query plans if it thinks that the amount of temporary space needed for the best one might pass a certain threshold. The above command lets you change that threshold.

Of course if every query does that and uses the space, then your server will fall over due into heavy swap. So discuss with a DBA before using this.

Cheers,
Ben

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