SQL Patch for a statement that already has two hints

user8740803

Hi all,

we are facing a problem after upgrading from 12.1 to 19c. We have a packaged statement like this:

INSERT /*+ nologging append */ INTO table SELECT /*+ some hints here */ ...

The SELECT performs poorly from time to time on 19c. The last time it was even terminated because PGA_AGGREGATE_LIMIT was exceeded. We did not see this behavior on 12.1, though. We already have enabled optimizer_adaptive_plans. So Oracle has plenty of plans for this statement but seems to fail to fetch the correcct plan from time to time. Since this DB is shared among multiple applications we came up with the idea to create an SQL patch that injects the optimizer hint optimizer_features_enable('12.1.0') into the statement to see if this changes anything (we can't change that on DB level).

However, the question is: Is it possible to patch the 2nd hint in that statement without messing around with the 1st one?

Regards,

Ralf

AndrewSayer

This is something you should be able to test for yourself on your development databases. That said, yes, the additional hint will not effect any existing hints.

BluShadow

Since upgrading to 19c... do you still need the original hints you had in 12g?

Rather than add more hints, it may be that you now need to remove what you had... After all, hints are usually there because you've had some issue with the optimizing of queries previously and haven't resolved that through other means (i.e. restructuring the query or changing database tables/indexes). As the optimizer has undoubtedly changed across the versions, it's likely the existing hints are counteracting the improvements Oracle has made to it.

First question when thinking about optimizer hints is... Do we really need them or can we identify the actual cause and resolve it through structural changes?

Optimizer hints are really there to help identify the causes of performance issues, not to be a permanent solution.