Adding hints to PL/SQL?

I have encountered an odd situation:

Some PL/SQL code I have is exhibiting some interesting behavior (copyrights unfortunately do not allow me to repost someone else's code). In a rather straightforward way it joins DBA_SEGMENTS and DBA_FREE_SPACE to determine if objects can extend via its NEXT_EXTENT value.

On one Oracle 9.2.0.6 instance this query runs just fine. On the other, however, it appears to "hang" (my client has always wound up killing the job before it completes).

We tried to determine the cause of this by creating some simple queries that join these two tables. Lo and behold, the same behavior was exhibited! So now we know it isn't the PL/SQL that is at fault.

Through a whim, I added a /* RULE */ hint to the plain SQL query, and guess what? It finished in a split-second on the instance where before it was appearing to hang!

So now I need to know how to modify the code I have on hand to accommodate the instance that's been exhibiting the problem. Can I simply add a /* RULE */ hint to the code? I've never seen that in PL/SQL before. I don't think changing the optimizer from CHOOSE to RULE is an option, and I don't believe it's good practice to ANALYZE and create statistics on SYS tables. So what am I left with?

If this is an FAQ, I heartily apologize.

Fred Received on Fri Dec 02 2005 - 17:22:44 CST