Oracle SQL Patch also referred to as 'SQL Patch' is a way of fixing the execution plan of a specific SQL statement with the help of a 'full hint'.
The SQL Patch makes it possible to guide Oracle optimizer to a desired optimal execution plan without the requirement for changing the application source code or other common tuning methods like statistics manipulation.
Furthermore, the 'full hint' specified in the SQL Patch would overwrite the 'normal hint' in the SQL statement. Therefore, the SQL Patch delivers the feasibility for fixing a suboptimal hint in the source code without makes any changes to it.
Mechanism of Oracle SQL Patch
Normalized SQL text (which means SQL text without the extra space and line breaks) is utilized as a unique identifier for a SQL Patch. During the process of parsing, in case an SQL Patch is found on the basis of its normalized SQL text, the full hint as listed in the SQL Patch will be applied so that the desired execution plan can be utilized.
Advantage of Oracle SQL Patch
Oracle SQL Patch is certainly more flexible as it is not anchored with one existing execution plan. If the full hint mentioned in the SQL Patch is working as per design (can be depicted by a simple EXPLAIN PLAN without executing the statement), the optimal execution plan will be developed.
Disadvantage of Oracle SQL Patch
A disadvantage of SQL Patch is that it requires a full hint instead of a normal hint. This hint is not so straightforward and it turns out to be challenging without proper testing.
Read Also Oracle SQL Patch FAQ