Re: Adding hints to PL/SQL?

Fred wrote:
> 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

Go for it, unless FIRST_ROWS, ALL_ROWS etc help.

I don't think it's a PL/SQL issue specifically though. Received on Fri Dec 02 2005 - 18:41:49 CST