Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

SQL Patch for a statement that already has two hints

user8740803Apr 29 2022

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

Comments

odie_63

Hi,

Simple example :

If the count() function returns 11, then 11 div 10 = 1.1 (datatype xs:decimal), and 1.1 is not a valid value for the sequence iterator, it must be an integer.

At this point you have different options depending on how you want to round the value to an integer :

  • using fn:round()
  • using fn:floor()
  • using fn:ceiling()
  • casting to xs:integer directly : for $v in 1 to xs:integer(fn:count($anyType1/*:adad) div 10)

Your choice.

1 - 1

Post Details

Added on Apr 29 2022
#performance, #sql
2 comments
539 views