If you use a vendor package with Oracle, you may be one of the thousands of shops that struggle with optimizing and tuning vendor systems that you did not write and cannot change. So, how do you tune SQL when you cannot access the SQL source code.
In traditional SQL tuning, changes are made to re-writing the SQL statement into a more efficient form or by changing the execution plan by adding hints to the select clause. With SQL that hides inside a compiled program, changing the SQL is impossible. In other cases, the software vendor may explicitly prohibit any changes to the source code, you must come up with a creative way to tune the SQL without touching the source code.
Oracle provides a “trick” technique for tuning SQL when you cannot “touch” the source code. Cases where you cannot change the SQL source code include dynamically generated SQL, SQL inside 3rd party vendor packages, and compiled programs with embedded SQL.
Read more below to see examples of tuning SQL when you cannot touch the source code:
..more..
How tune Oracle SQL remotely
