Discussions
Categories
- 196.9K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.4K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 546 SQLcl
- 4K SQL Developer Data Modeler
- 187.1K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.6K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 442 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
SQL Patch for a statement that already has two hints

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
-
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.
-
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.