Oracle SQL Patch FAQ's
1) How can the user verify in case Oracle SQL Patch already exists?
Please utilize the script SQL_ProfilesBaselinesAndPatches_11g+.txt from Note 1438410 to list down all existing SQL Patches. This script also showcases the existing SQL Profile and SQL Plan Baselines. If the user only wants to show the SQL Patches, the user needs to change the BASIS_INFO part of the script as listed below:
( SELECT
-1 DBID,
-1 INSTANCE_NUMBER, /* -2 for all instances, -1 for current instance */
' ' DISPLAY_BASELINES,
' ' DISPLAY_PROFILES,
'X' DISPLAY_PATCHES,
'ALL' DATA_SOURCE /* AWR, CURRENT, ALL */
FROM
DUAL
)
2) Explains the benefits and disadvantage of Oracle SQL Patch, specifically in comparison with Oracle SQL Plan Baseline?
Oracle SQL Plan Baseline also referred t as 'SQL Plan Baseline' has already provided the same function to freeze up the execution plan of only one SQL statement. There is one limitation of the SQL Plan Baseline is that it is anchored to both the normalized SQL text along with an existing execution plan.
For instance, if the user requires tune one SQL statement with
SQL _ID = bad_sql_id
SQL text = original_text
plan_hash_value = bad_plan
User has to add a hint, execute the hinted statement to make sure a good execution plan already exists in the cursor cache:
SQL _ID = good_sql_id
SQL text = hinted_text
plan_hash_value = good_plan
Then the below procedure can be used to create a SQL Plan Baseline:
dbms_spm.load_plans_from_cursor_cache
( sql_id=>'bad_sql_id',
plan_hash_value=>'good_plan',
sql_text=>'original_text',
fixed=>'YES');
It is visible that the SQL Plan Baseline binds the execution plan to a normalized SQL text. The first restriction of SQL Plan Baseline is that a good execution plan requires to be exist physically in the cursor cache which is impractical also e.g. user has to execute the good statement with literals at least once.
For executing the statement with literals leads to another potential problem related to the SQL Plan Baseline: utilizing literals may sometimes produce a slightly separate execution plan in comparison with the original statement by utilizing a bind variable.
Check the below-listed SQL statement alongwith its execution plan as an eg:
SELECT * FROM T WHERE A BETWEEN :A0 AND :A1;
SELECT STATEMENT (plan_hash_value = xxx)
FILTER
Filter predicates: :A0<=:A1
TABLE ACCESS BY INDEX ROWID (T)
INDEX RANGE SCAN (T~1)
If the user executes this statement with literals, the execution plan will subsequently change:
SELECT * FROM T WHERE A BETWEEN 'A' AND 'D';
SELECT STATEMENT (plan_hash_value = yyy)
TABLE ACCESS BY INDEX ROWID (T)
INDEX RANGE SCAN (T~1)
User will be able to notice that the FILTER step vanishes because Oracle is well aware of the relationship of literals 'A' and 'D' and the FILTER is not required.
If now the user uses the SQL Plan Baseline for binding plan_hash_value = yyy to the original statement text, it will ultimately fail because of the two execution plans mismatch.
On the other hand, 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.
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.
In summary, the table listed below the advantage and disadvantage of SQL Patch in comparison with SQL Plan Baseline:
|
|
Advantage |
Disadvantage |
| SQL Plan Baseline
|
Not required to specify full hint
|
Bonded to the existing execution plan hence not so flexible
|
| SQL Patch
|
Anchored only to normalized SQL text hence more flexible
|
The Need to understand the syntax of full hint
|
3) Does the user require additional licenses or the SQL diag pack for using the Oracle SQL Patch?
No additional licenses are required for using the SQL Repair Advisor or SQL patches. SQL Repair Advisor is accessible as part of Oracle Database Enterprise Edition.
4) How can the user manipulate the Oracle SQL Patch? (Create/Drop/Transport)
Before Oracle 12.2, SQL Patch can only be fashioned through an undocumented process sys.dbms_sqldiag_internal.i_create_patch
As of Oracle 12.2, a standard process is available: dbms_sqldiag.create_sql_patch
hence all depending on the Oracle version, various methods have been explained below:
CREATE SQL Patch
Run the below PL/SQL commands for creating a SQL Patch.
User is required to alter the customizing section as per the comment.
Before Oracle 12.2:
declare
-- ### Customizing section start #################################################################
vName varchar2(30) :='<SQL Patch Name>'; /* Name of the SQL Patch */
vDescription varchar2(500) :='<SQL Patch Description>'; /* Description of the SQL Patch */
vSQL_ID varchar2(13) :='<SQL_ID>'; /* SQL_ID of the statement */
vHintText varchar2(100) :='<Hint Text>'; /* The full hint text including query block name */
vStatus varchar2(8) :='ENABLED'; /* ENABLED or DISABLED */
-- ### Customizing section end ###################################################################
vSQLText CLOB;
begin
begin
select sql_fulltext into vSQLText from v$sql where sql_id=vSQL_ID and rownum < 2;
exception
when others then
select sql_text into vSQLText from dba_hist_sqltext where sql_id=vSQL_ID and rownum < 2;
end;
vDescription := vHintText||' '||vdescription;
sys.dbms_sqldiag_internal.i_create_patch(
sql_text => vSQLText,
hint_text => vHintText,
description => vDescription,
name => vName);
dbms_sqldiag.alter_sql_patch(vName,'STATUS',vStatus);
end;
As of Oracle 12.2:
declare
-- ### Customizing section start #################################################################
vName varchar2(30) :='<SQL Patch Name>'; /* Name of the SQL Patch */
vDescription varchar2(500) :='<SQL Patch Description>'; /* Description of the SQL Patch */
vSQL_ID varchar2(13) :='<SQL_ID>'; /* SQL_ID of the statement */
vHintText varchar2(100) :='<Hint Text>'; /* The full hint text including query block name */
vStatus varchar2(8) :='ENABLED'; /* ENABLED or DISABLED */
-- ### Customizing section end ###################################################################
vSQLText CLOB;
begin
vDescription := vHintText||' '||vdescription;
dbms_sqldiag.create_sql_patch(
sql_id => vSQL_ID,
hint_text => vHintText,
description => vDescription,
name => vName);
dbms_sqldiag.alter_sql_patch(vName,'STATUS',vStatus);
end;
/
DROP SQL Patch
begin
dbms_sqldiag.drop_sql_patch(name => '<SQL Patch Name>');
end;
/
Transport SQL Patch
A user first has to create a staging table for storing the SQL patch via:
DBMS_SQLDIAG.CREATE_STGTAB_SQLPATCH
On the source system, utilize the procedure DBMS_SQLDIAG.PACK_STGTAB_SQLPATCH for packing all SQL Patches into the staging table.
Export/Import the staging table from the source system to the target system.
On the target system, user should utilize the process DBMS_SQLDIAG.UNPACK_STGTAB_SQLPATCH for unpacking the SQL Patches from the staging table to the data dictionary.