Change a SQL Plan Using Oracle SQL Patch :
12/11/2024 Alireza Kamrani
Are you having trouble with a particular bad queries and your db optimizer isn't generating a good plan for it?
Don't worry, SQL Patch is here to help you tame these.
Forcefully change Optimar's behavior by Patching SQL using Hints.
Historical Problem:
Modifying SQL statements always is not possible in application codes side, for this problem you have some solutions such as SPM , SQL Patch.
Example:
Adding a hint via SQL patch, I’ve had success hinting just dynamic_sampling(2) but hinting first_rows_100 or all_rows should work.
exec dbms_output.put_line(
dbms_sqldiag.create_sql_patch(
sql_id=>'<SQLID>',
hint_text=>'dynamic_sampling(2)')
);
patch_name varchar2(100);
BEGIN
patch_name := sys.dbms_sqldiag.create_sql_patch(
sql_text=>' select employee_id from hr.employees where department_id = :var', hint_text=>'DYNAMIC_SAMPLING(4)',
name=>'TEST_SQL_PATCH1');
END;
/
By this way, you can edit a SQL statement on the fly without modifying application codes.
GOAL
In most applications, application code is static and cannot be modified by users.
If there is a performance issue with application SQL, one potential solution is to modify it adding hints to force the desired access path.
When the Application SQL statements cannot be modified, we have to look for better alternatives (such as SQL Plan Management Baselines etc.).
However, in a time critical situation these solutions may not be applicable and a faster solution may be required.
In a time critical situation, one such alternative is the "SQL Patch".
The SQL patch allows a user to "insert hints" into a query whose text cannot be edited due to the application constraints using an undocumented API called DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH()This undocumented function can be used in 12.1.
From 12.2 onwards, DBMS_SQLDIAG API has the function CREATE_SQL_PATCH documented to create SQL Patches.
SOLUTION
SQL Patch
This is a little different. A SQL Patch allows you to inject your own hints into a SQL Statement without changing the statement. It can be a good way to persuade the optimizer to get the execution plan you want.
It makes a lot of sense to use a SQL_PATCH to inject hints rather than change the code.
When you upgrade and want to test new optimizer features, you can just disable and re-enable the SQL_PATCHES
to run your testing, rather than go through the hassle of changing all of the application code
and then changing it back again if it doesn’t improve.
One case I has recently was where the code has a PARALLEL(16) hint embedded. To change the code would mean going back to the supplier, persuading them to remove the hint, taking a code release, fully testing it and releasing it.
đź’˘Using SQL Patch you can forcefully a Index , Parallel, Dynamic Sampling n, and any usedul HINT to considering by Optimizer.
Also you can remove a existing hint on you bad query,
For example to forcing removes Parallel(10) existing hint in your query, you can use a custom Patch as:
declare
x varchar2(100);
begin
x := dbms_sqldiag.create_sql_patch(
sql_id => '90fft4106zva'
, hint_text => q'[opt_param('optimizer_ignore_parallel_hints' 'true')]' ,
description => q'[opt_param('optimizer_ignore_parallel_hints' 'true')]'
, name => 'SQL_PATCH_TEST1');
end;
/
To avoid using specific Index on your query, we can force a Full Hint by finding OUTLINES :
♦️Find OUTLINES:
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('kop66mvdugg55', 0, FORMAT=>'OUTLINE BASIC NOTE'));
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.12.0')
DB_VERSION('19.12.0')
OPT_PARAM('optimizer_index_cost_adj' 1)
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "T"@"SEL$1" "MyIndex")
END_OUTLINE_DATA
*/
Now, create a SQL Patch with the full table scan hint.
The trick here is the hint must include query block names such as @”SEL$1″.
For example the output above has
INDEX(@"SEL$1" "T"@"SEL$1" "MyIndex")
Without it, the hint won’t be resolvable by the optimizer.
Use the PL/SQL block below to create the SQL Patch for
“select /*+ FULL(t) */ count(*) from emp t;”
As you can see, the hint text is
‘FULL(@”SEL$1″ “T”@”SEL$1”)‘
with @”SEL$1″ attached.
DECLARE
l_patch_name VARCHAR2(32767);
BEGIN
-- SQL ID
l_patch_name := SYS.DBMS_SQLDIAG.create_sql_patch(
sql_id => 'kop66mvdugg55',
hint_text => 'FULL(@"SEL$1" "T"@"SEL$1")',
name => 'kop66mvdugg55_patch');
END;
/
Another examples:
As an example, let's say that you have a SQL statement ('select * from emp') that performs poorly, but you have found that it works well with optimizer_features_enable set to "10.2.0.5". In a critical situation, where a SQL Plan Baseline is not possible, you could create a SQL Patch to set this as a hint in the SQL Statement as follows:
đź’˘For 12.1.0.2 only:
SQL> connect / as sysdba
SQL> begin
sys.dbms_sqldiag_internal.i_create_patch(sql_text=>'select * from emp', hint_text=>'optimizer_features_enable(''10.2.0.5'')',
name=> 'SQL_Patch_10.2.0.5');
end;
/
đź’˘From 12.2 onwards:
SQL> connect / as sysdba
SQL> variable x varchar2(100);
SQL> begin
x:=dbms_sqldiag.create_sql_patch(
sql_text=>'select * from emp', hint_text=>'optimizer_features_enable(''10.2.0.5'')',
name=> 'SQL_Patch_10.2.0.5');
end;
/
From 12.2 onwards, SQL_ID can be used to create the SQL Patch. Refer the following example:
SQL> connect / as sysdba
SQL> variable x varchar2(100);
SQL> exec :x:=dbms_sqldiag.create_sql_patch(
sql_id=>'3692ux1cb1z1u', hint_text=>'optimizer_features_enable(''11.2.0.3'')', name=> 'SQL_Patch_11.2.0.3');
♦️Once the SQL Patch is created for the SQL, there no need to modify the SQL code in the application code.
The optimizer uses the SQL Patch (embedded with the 10.2.0.5 optimizer hint) and gives good plan:
SQL> conn <user>/<passwd>
Connected.
SQL> set autot on
SQL> select * from emp;
Execution Plan
------------------------
Plan hash value: 2872589321
-------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 1 | 13 | 2 (0)| 00:00:01 |
-----------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- SQL patch "SQL_Patch_10.2.0.5" used for this statement
You can see that the patch has been used in the "Note" section of the plan.
Note: For hints that relate to objects that have the potential to be affected by the query block that they reside in, hints need to be provided in block specific format.
For example, you may be able to use a hint like /*+ FULL(A) */ directly in a SQL statement to refer to a table (or table alias) called "A".
However, for the hint to work in a SQL patch the hint needs to be specified with the relevant query block information i.e a /*+ FULL(A) */ hint might need to be supplied as : 'FULL(@"SEL$1" "A"@"SEL$1")'.
If you have already executed the SQL and have a target plan you can find the hint syntax it is using by using the 'OUTLINE' parameter of dbms_xplan.display_cursor using the following sql:
select * from table(dbms_xplan.display_cursor(
'&SQL_ID', &CHILD_NUMBER, 'OUTLINE');
For example:
SQL> select * from emp
SQL> SELECT sql_id, hash_value, child_number,substr(sql_text,1,40) sql_text
v$sql sql_text like 'select * from emp';
SQL_ID HASH_VALUE CHILD_NUMBER SQL_TEXT
---------- -------- ------------ -------------
bs8d6ggtfn2xz 4075424703 0
select * from emp
SQL> select * from table(dbms_xplan.display_cursor(
'bs8d6ggtfn2xz',0,'OUTLINE')) ;
PLAN_TABLE_OUTPUT
----------------------------
SQL_ID bs8d6ggtfn2xz, child number 0
-------------------------------------
select * from emp
Plan hash value: 2872589290
-------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS FULL| EMP | 14 | 532 | 3 (0)| 00:00:01 |
-------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
DB_VERSION('12.1.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "EMP"@"SEL$1")
END_OUTLINE_DATA
*/
In the "Outline Data" section, you can see the ' FULL(@"SEL$1" "EMP"@"SEL$1") ' hint.
Using the query block specific syntax, you would create a sqlpatch for the SQL in the following way:
begin
sys.dbms_sqldiag_internal.i_create_patch(sql_text=>'SELECT COUNT(*) FROM CUSTOMERS A', hint_text=>'FULL(@"SEL$1" "A"@"SEL$1")', name=> 'test_sqlpatch_full_hint');
end;
Remember that you will need to use aliases (if these are present in the query) and global query block names as appropriate. For more details about hints see:
Document 29236.1 QREF: SQL Statement HINTS
Giving another example of adding SQL patch hint like:
hint_text=>'OPT_PARAM(''_optimizer_squ_bottomup'' ''TRUE'')'.
Another example of adding multiple hints to SQL Patch:
SQL> conn / as sysdba
SQL> exec :x:=dbms_sqldiag.create_sql_patch(sql_text=>'select * from t1,t2 where t1.id=t2.id and t1.id=1000', hint_text=>'FULL(@"SEL$1" "T2"@"SEL$1") INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."ID")) LEADING(@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1") USE_NL(@"SEL$1" "T1"@"SEL$1") NLJ_BATCHING(@"SEL$1" "T1"@"SEL$1") NO_ADAPTIVE_PLAN', name=> 'test_sqlpatch_many_hints');
PL/SQL procedure successfully completed.
SQL> conn test/test
Connected.
SQL> select * from t1,t2 where t1.id=t2.id and t1.id=1000;
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
DB_VERSION('12.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T2"@"SEL$1")
INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."ID"))
LEADING(@"SEL$1" "T2"@"SEL$1"
"T1"@"SEL$1")
USE_NL(@"SEL$1" "T1"@"SEL$1")
NLJ_BATCHING(@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA
*/
Note
-----
- SQL patch "test_sqlpatch_many_hints" used for this statement
NOTE: We can see the hints mentioned in SQL patch are used.
Further Details:
SQL Patches can be either disabled or enabled using DBMS_SQLDIAG.ALTER_SQL_PATCH API. For example, the following command disables the SQL Patch:
EXEC DBMS_SQLDIAG.ALTER_SQL_PATCH(name=>'SQL_Patch_10.2.0.5', attribute_name=>'STATUS', attribute_value=>'DISABLED');
SQL Patch can be dropped permanently using the DBMS_SQLDIAG.DROP_SQL_PATCH() function:
EXEC DBMS_SQLDIAG.DROP_SQL_PATCH(name=>'SQL_Patch_10.2.0.5');
To find the hints associated to a SQL Patch, First identify the SQL signature and then use this signature to get the SQL patch information:
SQL> conn / as sysdba
Connected.
SQL> set numformat 9999999999999999
SQL> select EXACT_MATCHING_SIGNATURE from v$sql where sql_id = 'g59vz2u4cu404';
EXACT_MATCHING_SIGNATURE
---------------------------
2640606212120450132
Then pass the signature to below query to get the hints:
SQL> select cast(extractvalue(value(x), '/hint') as varchar2(500)) as outline_hints
from xmltable('/outline_data/hint'
passing (select xmltype(comp_data) xml
from sys.sqlobj$data
where signature = 2640606212120450132)) x;
OUTLINE_HINTS
-------------------------------
gather_plan_statistics
You can see above output shows the hint used as "gather_plan_statistics" for the example SQL.
Note if the SQL is not in memory/cache, then attempting to create the SQL Patch will fail with the following error:
ORA-56975: invalid SQL_ID
This can be checked further with the following:
select 'exists' from v$sql where sql_id='<SQL_ID>';
The V$SQL view contains a SQL_PATCH column that can be queried to indicate whether a particular SQL Statement has any SQL Patches associated with it.
You can see more details about existing SQL Patches in the DBA_SQL_PATCHES View.
The example above manually insert a hint into a SQL Patch, but the DBMS_SQLDIAG package provides the functionality to check SQL Statements and recommend patches accordingly.
For more details see:
Document 1509192.1 How To Use DBMS_SQLDIAG To Diagnose Various Query Issues
While it is true that you cannot specify SQL_ID as an argument to DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH until 12.2 or higher, here is a means of saving yourself a great deal of typing in 12.1.0.2 or lower, when the SQL statement in question is very lengthy.
(Note: This example assumes that you are licensed for the diagnostics pack, in order to query DBA_HIST_SQLTEXT.)
Substitute the SQL ID in question below, in place of <SQL_ID>.
VAR c CLOB
EXEC SELECT t.sql_text INTO :c FROM dba_hist_sqltext t WHERE t.sql_id = '<SQL_ID>' AND rownum = t.dbid = (SELECT d.dbid FROM v$database d);
EXEC sys.dbms_sqldiag_internal.i_create_patch(sql_text => :c, hint_text => 'optimizer_features_enable(''11.2.0.3'')', name => 'SQL_Patch_11.2.0.3');
DBMS_SQLDIAG Overview
In the rare case that a SQL statement fails with a critical error, you can run the SQL Repair Advisor to try to repair the failed statement.
This section covers the following topics:
- About the SQL Repair Advisor
- Running the SQL Repair Advisor
- Removing a SQL Patch
About the SQL Repair Advisor
You run the SQL Repair Advisor after a SQL statement fails with a critical error. The advisor analyzes the statement and in many cases recommends a patch to repair the statement. If you implement the recommendation, the applied SQL patch circumvents the failure by causing the query optimizer to choose an alternate execution plan for future executions.
Running the SQL Repair Advisor
You run the SQL Repair Advisor by creating and executing a diagnostic task using the CREATE_DIAGNOSIS_TASK and EXECUTE_DIAGNOSIS_TASK respectively.
The SQL Repair Advisor first reproduces the critical error and then tries to produce a workaround in the form of SQL patch.
Identify the problem SQL statement
Consider the SQL statement that gives a critical error:
DELETE FROM t t1 WHERE t1.a = 'a' AND ROWID <> (SELECT MAX(ROWID) FROM t t2 WHERE t1.a= t2.a AND t1.b = t2.b AND t1.d=t2.d) You use the SQL Repair advisor to repair this critical error.
Create a diagnosis task
Invoke DBMS_SQLDIAG. CREATE_DIAGNOSIS_TASK. You can specify an optional task name, an optional time limit for the advisor task, and problem type. In the example below, we specify the SQL text, the task name as 'error_task' and a problem type as
'DBMS_SQLDIAG.PROBLEM_TYPE_COMPILATION_ERROR'.
DECLARE
rep_out CLOB;
t_id VARCHAR2(50);
BEGIN
t_id := DBMS_SQLDIAG.CREATE_DIAGNOSIS_TASK(
sql_text => 'DELETE FROM t t1
WHERE t1.a = ''a'' AND ROWID <> (SELECT MAX(ROWID) FROM t t2
WHERE t1.a= t2.a AND t1.b = t2.b AND t1.d=t2.d)',
task_name => 'error_task',
problem_type =>DBMS_SQLDIAG.PROBLEM_TYPE_COMPILATION_ERROR);
Execute the diagnosis task
To execute the workaround generation and analysis phase of the SQL Repair Advisor, you call DBMS_SQLDIAG.EXECUTE_DIAGNOSIS_TASK with the task ID returned by the CREATE_DIAGNOSIS_TASK. After a short delay, the SQL Repair Advisor returns. As part of its execution, the SQL Repair Advisor keeps a record of its findings which can be accessed through the reporting facilities of SQL Repair Advisor.
SQL>DBMS_SQLDIAG.EXECUTE_DIAGNOSIS_TASK (t_id);
Report the diagnosis task
The analysis of the diagnosis task is accessed through dbms_sqldiag.report_diagnosis_task.
If the SQL Repair Advisor was able to find a workaround, it recommends a SQL Patch.
A SQL Patch is similar to a SQL profile but unlike the SQL Profile, it is used to workaround compilation or execution errors.
rep_out := DBMS_SQLDIAG.REPORT_DIAGNOSIS_TASK (t_id, DBMS_SQLDIAG.TYPE_TEXT);
DBMS_OUTPUT.PUT_LINE ('Report : ' || rep_out);
END;
/
Applying the patch
If a patch recommendation is present in the report, you can run the ACCEPT_SQL_PATCH command to accept the patch by invoking DBMS_SQLDIAG.ACCEPT_SQL_PATCH.
This procedure takes the task_name as an argument.
EXECUTE DBMS_SQLDIAG.ACCEPT_SQL_PATCH(task_name => 'error_task', task_owner => 'SYS', replace => TRUE);
Test the patch
Now that you have accepted the patch, you can rerun the SQL statement. This time, it will not give you the critical error. If you run 'explain plan' for this statement, you will see that a SQL patch was used to generate the plan.
DELETE FROM t t1 WHERE t1.a = 'a' AND ROWID <> (select max(rowid) FROM t t2
WHERE t1.a= t2.a AND t1.b = t2.b AND t1.d=t2.d);
Removing a SQL Patch
In a situation where you obtained an official patch from Oracle to fix an error, or upgraded to the next patchset or release of Oracle which included the fix for the error, you call DBMS_SQLDIAG.DROP_SQL_PATCH with the patch name to drop the SQL patch. The patch name can be obtained from the explain plan section or by querying the view DBA_SQL_PATCHES.
Note : Using Oracle SQL Plan Management (SPM), you can also apply any Hints to your SQL statement to tuning Queries,you can even change WHERE Clause on the fly without any change in application codes.
Alireza Kamrani: Database Box Group Owner
No comments:
Post a Comment