In this article, I will be discussing various methods of tuning the SQL without rewriting the code. You can use these methods under certain circumstances like vendor packages where code change is not possible or you need to restore the performance of SQL query immediately if it starts performing poor due to plan changes.
I am using below example where I created a simple table MDSDBA.TEST_REWRITE_EQ from data of dba_users in one of the test environment. The examples used are purely for demonstration purposes and should not be confused with any other performance problems.
SQL> explain plan for select username from MDSDBA.TEST_REWRITE_EQ TEST_REWRITE_EQ;
Explained.
SQL> select * from table(dbms_xplan.display());
Plan hash value: 3048021686
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 495 | 4950 | 1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | TEST_REWRITE_EQ_NM_IDX | 495 | 4950 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
8 rows selected.
In the above example you can see that there is INDEX FULL SCAN which is good. Now just for the sake of demonstration I want to force full scan for the table TEST_REWRITE_EQ. I can use below three methods -
1.) Using the package declare_rewrite_equivalance.
2.) Using SQL plan baselines or stored outlines
3.) Using SQL patch.
METHOD I: DBMS_ADVANCED_REWRITE
SQL> begin
2 sys.dbms_advanced_rewrite.
3 declare_rewrite_equivalence
4 (
5 name => 'TEST_REWRITE_EQUIV',
6 source_stmt => 'select username from MDSDBA.TEST_REWRITE_EQ TEST_REWRITE_EQ',
7 destination_stmt => 'select /*+ FULL(TEST_REWRITE_EQ) */ username from MDSDBA.TEST_REWRITE_EQ TEST_REWRITE_EQ where 1=1',
8 validate => TRUE
9 /*rewrite_mode => 'TEXT_MATCH'*/
10 );
11 end;
12 /
PL/SQL procedure successfully completed.
SQL> explain plan for select username from MDSDBA.TEST_REWRITE_EQ TEST_REWRITE_EQ;
Explained.
SQL> select * from table(dbms_xplan.display());
Plan hash value: 3048021686
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 495 | 4950 | 1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | TEST_REWRITE_EQ_NM_IDX | 495 | 4950 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
8 rows selected.
As you can see, rewrite equivalence isn't used in our case and the reason being query_rewrite_enabled is set to FALSE.
SQL> sho parameter rewrite
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled string FALSE
query_rewrite_integrity string ENFORCED
SQL> alter session set query_rewrite_enabled=TRUE;
Session altered.
SQL> alter session set query_rewrite_integrity=TRUSTED;
Session altered
After setting the parameter query_rewrite_enabled to TRUE and query_rewrite_integrity to TRUSTED, you can see that now oracle is doing Full Table Scan instead of Index Full Scan.
SQL> explain plan for select username from MDSDBA.TEST_REWRITE_EQ TEST_REWRITE_EQ;
Explained.
SQL> select * from table(dbms_xplan.display());
Plan hash value: 3419488459
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 495 | 4950 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST_REWRITE_EQ | 495 | 4950 | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
8 rows selected.
#####################################################################################################
METHOD II: SQL Plan Baselines -
NON HINTED SQL details:
SQL_ID:1n1hcgypncsh4, PLAN_HASH_VALUE:3048021686
HINTED SQL details:
SQL_ID:3j238umtfht7y, PLAN_HASH_VALUE:3419488459
Create SQL Plan baseline on the non hinted Original SQL:
SQL> variable cnt number;
EXECUTE :cnt :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>'1n1hcgypncsh4'); SQL>
PL/SQL procedure successfully completed.
SQL> SELECT sql_handle, sql_text, plan_name, enabled FROM dba_sql_plan_baselines;
SQL_HANDLE SQL_TEXT PLAN_NAME ENA
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ---
SQL_4666a5f125a71327 select username from MDSDBA.TEST_REWRITE_EQ TEST_REWRITE_EQ SQL_PLAN_4ctp5y4kuf4t7ecfcc08f YES
Disable the existing Plan baseline using below code:
variable cnt number;
exec :cnt :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE(SQL_HANDLE => 'SQL_4666a5f125a71327',PLAN_NAME=> 'SQL_PLAN_4ctp5y4kuf4t7ecfcc08f',ATTRIBUTE_NAME => 'enabled',ATTRIBUTE_VALUE => 'NO');
Pin the execution plan of hinted SQL to original SQL handle -
exec :cnt:=dbms_spm.load_plans_from_cursor_cache(sql_id => '3j238umtfht7y',plan_hash_value => 3419488459,sql_handle => 'SQL_4666a5f125a71327');
SQL> select * from table(dbms_xplan.display());
Plan hash value: 3419488459
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 250 | 2500 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS STORAGE FULL| TEST_REWRITE_EQ | 250 | 2500 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Note
- SQL plan baseline "SQL_PLAN_4ctp5y4kuf4t7047d5bcc" used for this statement
12 rows selected.
#####################################################################################################################################
METHOD III: SQL Patch -
Drop the SQL plan baseline created earlier using below package -
SQL> exec :cnt:=dbms_spm.drop_SQL_PLAN_BASELINE(SQL_HANDLE => 'SQL_4666a5f125a71327');
PL/SQL procedure successfully completed.
SQL> explain plan for select username from MDSDBA.TEST_REWRITE_EQ TEST_REWRITE_EQ;
Explained.
SQL> select * from table(dbms_xplan.display());
Plan hash value: 3048021686
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 250 | 2500 | 1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | TEST_REWRITE_EQ_NM_IDX | 250 | 2500 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
8 rows selected.
BEGIN
SYS.DBMS_SQLDIAG_INTERNAL.i_create_patch(sql_text => 'select username from MDSDBA.TEST_REWRITE_EQ TEST_REWRITE_EQ',
hint_text => 'FULL(TEST_REWRITE_EQ)',
name => 'test_sql_patch');
END;
/
SQL> select * from table(dbms_xplan.display());
Plan hash value: 3419488459
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 250 | 2500 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS STORAGE FULL| TEST_REWRITE_EQ | 250 | 2500 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Another use of SQL patch is to fix SQLs errors as part of SQL repair advisor.
declare
l_task varchar2(1000);
begin
l_task := SYS.DBMS_SQLDIAG.create_diagnosis_task(
sql_id => '3j238umtfht7y',
task_name => 'sql_repair_task',
problem_type => DBMS_SQLDIAG.PROBLEM_TYPE_PERFORMANCE);
end;
/
where problem_type can take any of below values -
PROBLEM_TYPE_PERFORMANCE - The SQL is performing badly.
PROBLEM_TYPE_WRONG_RESULTS - The query appears to be giving inconsistent results.
PROBLEM_TYPE_COMPILATION_ERROR - The query fails to compile, even though it should be valid.
PROBLEM_TYPE_EXECUTION_ERROR - The query compiles, but results in a runtime error, probably due to a bad execution plan, or an execution plan that encounters a bug.
PROBLEM_TYPE_ALT_PLAN_GEN - The user wants the advisor to look for alternative plans.
exec SYS.DBMS_SQLDIAG.execute_diagnosis_task(task_name => 'sql_repair_task');
SQL> set serveroutput on size 1000000
declare
l_report clob;
begin
l_report := SYS.DBMS_SQLDIAG.report_diagnosis_task(task_name => 'sql_repair_task' );
dbms_output.put_line(l_report);
end;
/
SQL> 2 3 4 5 6 7
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : sql_repair_task
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 03/02/2015 15:19:06
Completed at : 03/02/2015 15:19:07
-------------------------------------------------------------------------------
Schema Name: MDSDBA
SQL ID : 3j238umtfht7y
SQL Text : select /*+ FULL(TEST_REWRITE_EQ) */ username from
MDSDBA.TEST_REWRITE_EQ TEST_REWRITE_EQ
-------------------------------------------------------------------------------
SQL patch was found to resolve the problem.
BEGIN
SYS.DBMS_SQLDIAG.accept_sql_patch(
task_name => 'sql_repair_task',
task_owner => 'SYS',
replace => TRUE);
END;
/
-------------------------------------------------------------------------------
If you are okay with the findings, then accept SQL patch -
BEGIN
SYS.DBMS_SQLDIAG.accept_sql_patch(
task_name => 'sql_repair_task',
task_owner => 'SYS',
replace => TRUE);
END;
/
No comments:
Post a Comment