Tuning SQL query without changing code
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;
/
