Our last post on SQL patches generated a lot of comments and questions. Today I will address a couple of questions that are quick to answer. For those that require more discussion, we’ll post more details in the next few weeks.
1) Do we need additional licenses or the SQL diag pack to use this? No. No additional licenses are needed to use SQL Repair Advisor or SQL patches. SQL Repair Advisor is available as part of Oracle Database Enterprise Edition 11g.
2) Can I supply a SQL_ID instead of SQL text? The API requires that the SQL text be passed in. Keep in mind that the SQL patch will be applied to SQL statements that match this SQL text after normalization. We use the same normalization that is used for SQL profiles, where the whitespace and the case (of non-literals) are normalized.
Here’s a quick example to show what I mean. In the last post, we showed an example SQL patch created with an index hint, and saw the patch kick in for this query:
If we add some whitespace, and change the case of some of the query, we will still use the SQL patch:
So even though the SQL ID of the two queries is different, they both use the SQL patch. This is why you cannot supply a SQL ID — we aren’t associating the patch with a SQL ID, but a (normalized) SQL text.
You can get a copy of the script I used to generate this post here.