As an oracle DBA, you may have come across situations where you are left with no other options of tuning the query because of certain application dependencies and then you have to push hints through outlines, sql patch or sql profiles. I had to deal with an interesting situation where it becomes quite tricky while pushing the hints.
First of all as a performance tuning DBA, this question should always come in your mind that why do I have to use hints at all? Why oracle optimizer is not able to pick the optimal execution plan at the first place. But there are always cases when optimizer can't simply pick right driving table, or joing order or index due to various constraints. In my case, I was dealing with nearly 2000 lines of code, with 40 WITH clause subquery blocks and each subquery had 14 tables getting joined together. Due to business urgency, code change was not an option. And in this case, first thing you need to make sure that your stats are accurate, you have histograms on the columns containing skewed data and you have extended stats on the columns combinations determined by dbms_stats.seed_col_usage() procedure and you have selective indexes.
Even after that, with large number of tables and temporary table transformation due to query sub factoring, optimizer may end up picking undesired driving table or unselective index. And things become more worse when due to present of hierarchies, optimizer has to do cardinality guess which doesn't fit accurate. I will discuss on these topics in more detail in separate blogs.
So challenge here was to determine query blocks where optimizer didn't pick right driving table and ended up in doing partition range all. Thankfully, you can use format=>OUTLINE in dbms_xplan.display method to see the optimizer outline hints. From there you can check for LEADING hint and find out which block is not using desired driving table in LEADING hint. Once you have these, you can arrange your tables as per the desired format in LEADING hint and all you have to do is to create a SQL profile or patch. Here is the code I used to tune required queries -
DECLARE
sql_text clob ;
description varchar2(1000):='Temporary sql patch for BI report';
name varchar2(1000) :='Sql_Patch';
output varchar2(1000);
sqlpro_attr SYS.SQLPROF_ATTR;
l_count number:=0;
begin
select outline_hints
bulk collect
into
sqlpro_attr
from
(select 'LEADING(@"SEL$1" "T597999"@"SEL$1" "T1841902"@"SEL$1" "T543491"@"SEL$1" "T572337"@"SEL$1" "T1841904"@"SEL$1" "T547300"@"SEL$1")' as outline_hints from dual
union all
select 'LEADING(@"SEL$2" "T597999"@"SEL$2" "T1841902"@"SEL$2" "T543491"@"SEL$2" "T572337"@"SEL$2" "T1841904"@"SEL$2" "T547300"@"SEL$2")' as outline_hints from dual
union all
select 'LEADING(@"SEL$3" "T597999"@"SEL$3" "T1841902"@"SEL$3" "T543491"@"SEL$3" "T572337"@"SEL$3" "T1841904"@"SEL$3" "T547300"@"SEL$3")' as outline_hints from dual
union all
select 'LEADING(@"SEL$4" "T597999"@"SEL$4" "T1841902"@"SEL$4" "T543491"@"SEL$4" "T572337"@"SEL$4" "T1841904"@"SEL$4" "T547300"@"SEL$4")' as outline_hints from dual);
for l1 in
(select a.sql_id, c.sql_text, a.FORCE_MATCHING_SIGNATURE from sys.wrh$_sqlstat A, dba_hist_snapshot B, sys.wrh$_sqltext C
where B.BEGIN_INTERVAL_TIME >= trunc(sysdate) - 7 AND
A.SNAP_ID = B.SNAP_ID and A.instance_number = B.instance_number and
A.PARSING_SCHEMA_NAME='XXRPT_READONLY' and
C.SQL_ID=A.SQL_ID and
((ROUND(((a.ELAPSED_TIME_DELTA/1000000)/a.EXECUTIONS_DELTA),5) > 60 and a.EXECUTIONS_DELTA <> 0) OR
(ROUND((a.ELAPSED_TIME_DELTA/1000000),5) > 60 and a.EXECUTIONS_DELTA = 0))
) loop
if DBMS_LOB.INSTR(l1.SQL_TEXT, 'T547300.ACCT_PERIOD_END_DT_WID = 10120140831000.0', 1, 1) > 0 or
DBMS_LOB.INSTR(l1.SQL_TEXT, 't547300.acct_period_end_dt_wid = t597999.row_wid', 1, 1) > 0 or
DBMS_LOB.INSTR(l1.SQL_TEXT, 't547300.acct_period_end_dt_wid = 10120140831000.0', 1, 1) > 0 or
DBMS_LOB.INSTR(l1.SQL_TEXT, 'T547300.ACCT_PERIOD_END_DT_WID = T597999.ROW_WID', 1, 1) > 0
then
dbms_output.put_line(l1.sql_id ||'-'|| l1.FORCE_MATCHING_SIGNATURE);
begin
l_count:= l_count + 1;
output := SYS.DBMS_SQLTUNE_INTERNAL.I_CREATE_SQL_PROFILE(
SQL_TEXT => l1.sql_text,
PROFILE_XML => DBMS_SMB_INTERNAL.VARR_TO_HINTS_XML(sqlpro_attr),
NAME => name || l_count,
DESCRIPTION => description,
CATEGORY => 'DEFAULT',
CREATOR => 'SYS',
VALIDATE => TRUE,
TYPE => 'PATCH',
FORCE_MATCH => TRUE,
IS_PATCH => TRUE);
dbms_output.put_line(output);
exception when others then
dbms_output.put_line('SQLERRM-' || sqlerrm);
end;
end if;
end loop;
end;
/
In the above code, I am passing the collection of hints in sqlpro_attr which is collection type , converting the set of hints into XML format and passing it into DBMS_SQLTUNE_INTERNAL.I_CREATE_SQL_PROFILE.
For finding the problematic SQL I am using dbms_lob.instr to check for matching text having elapsed time more than 60 seconds and creating SQL Profile on the text. As you can see that there are four subquery blocks (SEL$1, SEL$2,SEL$3,SEL$4) resulted in poor performance, so all my LEADING hints are referring to those blocks with the desired order of tables to be joined.
And thats it, so no matter how big the query is, as long as you know the optimal join order and subquery block having issues, you can pass as many hints using the method I mentioned above to create SQL profiles on hinted SQL Text.
Let me know your thoughts or if you have any questions.
Thursday, October 22, 2015
Monday, March 30, 2015
Ask me Oracle
Just opening this thread for addressing day today oracle performance issues or any questions you have. Next time I will do a better job by organizing the content and will probably have separate website altogether.
Monday, March 2, 2015
Tuning SQL query without changing code
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;
/
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;
/
Wednesday, January 21, 2015
Index Joins in Oracle database
In normal scenario if you have multiple indexes on the columns on the table oracle optimizer would prefer one of the available indexes based on clustering factor of the index and other factors like use of order by clause on the indexed column etc.I will just show an example where I used index join method to eliminate table access by index row id and was able to retrieve data from indexes only.
Table has five columns and there are two separate indexes XYZ_I1 and XYZ_I2 which on COL1 and COL2 respectively. COL1 and COL2 contain sequence values and table XYZ has around 10000 records.
SQL> desc xyz
Name Null? Type
------------------------------------------------------------------- -------- ---------------------------------------------
COL1 NUMBER
COL2 NUMBER
COL3 NUMBER
COL4 NUMBER
COL5 NUMBER
SQL> explain plan for select /*+INDEX_JOIN(xyz XYZ_I1 XYZ_I2)*/ col1, col2 from xyz where col1=1 and col2=2 order by col1, col2;
Explained.
SQL> select * from table(dbms_xplan.display());
Plan hash value: 364115606
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 3 (34)| 00:00:01 |
|* 1 | VIEW | index$_join$_001 | 1 | 8 | 3 (34)| 00:00:01 |
|* 2 | HASH JOIN | | | | | |
|* 3 | INDEX RANGE SCAN| XYZ_I1 | 1 | 8 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN| XYZ_I2 | 1 | 8 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
As you can clearly see from explain plan that oracle was able to fetch required data by joining two indexes and there is no table access by index row id.
Other efficient approach could be creating a composite index on (col1,col2) and fetching the data from leaf blocks of one single index.
SQL>create index XYZ_I3 on XYZ(COL1, COL2);
SQL>index created.
SQL> explain plan for select /*+INDEX_JOIN(xyz XYZ_I1 XYZ_I2)*/ col1, col2 from xyz where col1=1 and col2=2 order by col1, col2;
Explained.
Table has five columns and there are two separate indexes XYZ_I1 and XYZ_I2 which on COL1 and COL2 respectively. COL1 and COL2 contain sequence values and table XYZ has around 10000 records.
SQL> desc xyz
Name Null? Type
------------------------------------------------------------------- -------- ---------------------------------------------
COL1 NUMBER
COL2 NUMBER
COL3 NUMBER
COL4 NUMBER
COL5 NUMBER
Now if I execute query (select col1, col2 from xyz where col1=1 and col2=2 order by col1, col2), under this condition Oracle can pick either of below two paths and oracle cannot use both the indexes-
a.) Table access by Index RowID XYZ using Index Range Scan of XYZ_I1 or
b.) Table access by Index RowID XYZ using Index Range Scan of XYZ_I2
SQL> explain plan for select col1, col2 from xyz where col1=1 and col2=2 order by col1, col2;
Explained.
SQL> select * from table(dbms_xplan.display());
Plan hash value: 3398653176
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| XYZ | 1 | 8 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | XYZ_I1 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
But if you closely look upon where clause criteria and columns being fetched, you would find that we are trying to fetch only columns col1 and col2 data of which exist in the indexes itself. So, is there a way to join these two indexes and retrieve the data from indexes itself? Yes, there is and this is what so called as index joins.
Explained.
SQL> select * from table(dbms_xplan.display());
Plan hash value: 364115606
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 3 (34)| 00:00:01 |
|* 1 | VIEW | index$_join$_001 | 1 | 8 | 3 (34)| 00:00:01 |
|* 2 | HASH JOIN | | | | | |
|* 3 | INDEX RANGE SCAN| XYZ_I1 | 1 | 8 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN| XYZ_I2 | 1 | 8 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
As you can clearly see from explain plan that oracle was able to fetch required data by joining two indexes and there is no table access by index row id.
Other efficient approach could be creating a composite index on (col1,col2) and fetching the data from leaf blocks of one single index.
SQL>create index XYZ_I3 on XYZ(COL1, COL2);
SQL>index created.
SQL> explain plan for select /*+INDEX_JOIN(xyz XYZ_I1 XYZ_I2)*/ col1, col2 from xyz where col1=1 and col2=2 order by col1, col2;
Explained.
SQL> explain plan for select col1, col2 from xyz where col1=1 and col2=2 order by col1, col2;
Explained.
SQL> select * from table(dbms_xplan.display());
Plan hash value: 3398653124
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN | XYZ_I3 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Hope I am able to explain the concept of index joins and you find this article useful. Please let me know for any questions.
Subscribe to:
Posts (Atom)