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