分享程序员开发的那些事...
更新时间：2022-09-13 23:20:06
[20150520]11GR2 _optimizer_null_aware_antijoin.txt
--好久没写sql 优化的帖子:
--参考这个链接自己重复测试看看在11G下的情况:
1.建立测试环境:
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
create table t1
as select
cast(rownum as int) a,
cast(rownum+10 as int) b,
cast(dbms_random.string('i',10) as varchar2(10)) c
from dual connect by level
create table t2
as select
cast(rownum as int) a,
cast(rownum+10 as int) b,
cast(dbms_random.string('i',10) as varchar2(10)) c
from dual connect by level
exec sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't1',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false)
PL/SQL procedure successfully completed.
exec sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't2',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false)
PL/SQL procedure successfully completed.
2.测试:
select /*SQL_1*/ c from t1 where a not in (select a from t2) ;
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 1war6t5g1w5g9, child number 0
-------------------------------------
select /*SQL_1*/ c from t1 where a not in (select a from t2)
Plan hash value: 895956251
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 40633 (100)| | 20 |00:00:03.69 | 190K|
|* 1 | FILTER | | 1 | | | | | 20 |00:00:03.69 | 190K|
| 2 | TABLE ACCESS FULL| T1 | 1 | 10000 | 146K| 8 (0)| 00:00:01 | 10000 |00:00:00.01 | 37 |
|* 3 | TABLE ACCESS FULL| T2 | 10000 | 1 | 4 | 8 (0)| 00:00:01 | 9980 |00:00:03.62 | 190K|
---------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T1@SEL$1
3 - SEL$2 / T2@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NULL)
3 - filter(LNNVL("A":B1))
--使用filter,逻辑读高达190K,效率低下.一般这种语句我会改写成not exists测试:
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 36gqqz0c26g8d, child number 0
-------------------------------------
select /*SQL_1*/ c from t1 where not exists (select a from t2 where
t2.a=t1.a)
Plan hash value: 629543484
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 17 (100)| | 20 |00:00:00.08 | 73 | | | |
|* 1 | HASH JOIN RIGHT ANTI| | 1 | 20 | 380 | 17 (6)| 00:00:01 | 20 |00:00:00.08 | 73 | 1517K| 1517K| 1872K (0)|
| 2 | TABLE ACCESS FULL | T2 | 1 | 9980 | 39920 | 8 (0)| 00:00:01 | 9980 |00:00:00.01 | 36 | | | |
| 3 | TABLE ACCESS FULL | T1 | 1 | 10000 | 146K| 8 (0)| 00:00:01 | 10000 |00:00:00.01 | 37 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
2 - SEL$5DA710D3 / T2@SEL$2
3 - SEL$5DA710D3 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T2"."A"="T1"."A")
--很明显逻辑读73,以前有许多帖子讨论not in 与not exists那个好那个差,实际上现在许多情况下根本不需要考虑这些细节,oracle的查
--询转化很好的选择最优的执行方式.
3.使用sql profile看看:
--我写的脚本:
$ cat sp1.sql
set verify off
set long 20000000
set longchunksize 20000000
column report_tuning_task format a300
declare
a varchar2(200);
begin
a := dbms_sqltune.create_tuning_task(task_name=>'tuning &1',description=>'tuning sql_id=&1',scope=>dbms_sqltune.scope_comprehensive,time_limit=>1800,sql_id=>'&1');
dbms_sqltune.execute_tuning_task( a );
end;
/
prompt
prompt =================================================================================================================================================
prompt tuning sql_id=&1 : report
prompt =================================================================================================================================================
select dbms_sqltune.report_tuning_task('tuning &1') report_tuning_task FROM dual;
prompt =================================================================================================================================================
prompt if finished,drop tuning task , run:
prompt execute dbms_sqltune.drop_tuning_task('tuning &1')
prompt if accept sql profile, run:
prompt execute dbms_sqltune.accept_sql_profile(task_name => 'tuning &1', replace => TRUE ,name=>'tuning &1');;
prompt execute dbms_sqltune.accept_sql_profile(task_name => 'tuning &1', replace => TRUE, name=>'tuning &1', FORCE_MATCH=>True)
prompt if drop or alter sql profile ,run :
prompt execute dbms_sqltune.drop_sql_profile(name => 'tuning &1')
prompt execute dbms_sqltune.alter_sql_profile(name => 'tuning &1',attribute_name=>'STATUS',value=>'DISABLED')
prompt =================================================================================================================================================
prompt
prompt
set serveroutput off
SCOTT@test> @sp1 1war6t5g1w5g9
PL/SQL procedure successfully completed.
=================================================================================================================================================
tuning sql_id=1war6t5g1w5g9 : report
=================================================================================================================================================
REPORT_TUNING_TASK
-------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : tuning 1war6t5g1w5g9
Tuning Task Owner : SCOTT
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 05/20/2015 09:35:56
Completed at : 05/20/2015 09:36:03
-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID : 1war6t5g1w5g9
SQL Text : select /*SQL_1*/ c from t1 where a not in (select a from t2)
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 99.95%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'tuning
1war6t5g1w5g9', task_owner => 'SCOTT', replace => TRUE);
Validation results
------------------
The SQL profile was tested by executing both its plan and the original plan
and measuring their respective execution statistics. A plan may have been
only partially executed if the other could be run to completion in less time.
Original Plan With SQL Profile % Improved
------------- ---------------- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time (s): 3.733955 .082431 97.79 %
CPU Time (s): 3.734431 .082387 97.79 %
User I/O Time (s): 0 0
Buffer Gets: 190911 72 99.96 %
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 20 20
Fetches: 20 20
Executions: 1 1
Notes
-----
1. Statistics for the original plan were averaged over 1 executions.
2. Statistics for the SQL profile plan were averaged over 10 executions.
2- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
The optimizer could not unnest the subquery at line ID 1 of the execution
plan.
Recommendation
--------------
- Consider replacing "NOT IN" with "NOT EXISTS" or ensure that columns used
on both sides of the "NOT IN" operator are declared "NOT NULL" by adding
either "NOT NULL" constraints or "IS NOT NULL" predicates.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 895956251
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9999 | 146K| 40633 (2)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| T1 | 10000 | 146K| 8 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 1 | 4 | 8 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( NOT EXISTS (SELECT /*+ FULL ("T2") */ 0 FROM "T2" "T2"
WHERE LNNVL("A":B1)))
3 - filter(LNNVL("A":B1))
2- Using SQL Profile
--------------------
Plan hash value: 2739594415
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 1900 | 17 (6)| 00:00:01 |
|* 1 | HASH JOIN RIGHT ANTI NA| | 100 | 1900 | 17 (6)| 00:00:01 |
| 2 | TABLE ACCESS FULL | T2 | 9980 | 39920 | 8 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T1 | 10000 | 146K| 8 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"="A")
-------------------------------------------------------------------------------
=================================================================================================================================================
if finished,drop tuning task , run:
execute dbms_sqltune.drop_tuning_task('tuning 1war6t5g1w5g9')
if accept sql profile, run:
execute dbms_sqltune.accept_sql_profile(task_name => 'tuning 1war6t5g1w5g9', replace => TRUE ,name=>'tuning 1war6t5g1w5g9');
execute dbms_sqltune.accept_sql_profile(task_name => 'tuning 1war6t5g1w5g9', replace => TRUE, name=>'tuning 1war6t5g1w5g9', FORCE_MATCH=>True)
if drop or alter sql profile ,run :
execute dbms_sqltune.drop_sql_profile(name => 'tuning 1war6t5g1w5g9')
execute dbms_sqltune.alter_sql_profile(name => 'tuning 1war6t5g1w5g9',attribute_name=>'STATUS',value=>'DISABLED')
=================================================================================================================================================
--注意看
Recommendation
--------------
- Consider replacing "NOT IN" with "NOT EXISTS" or ensure that columns used
on both sides of the "NOT IN" operator are declared "NOT NULL" by adding
either "NOT NULL" constraints or "IS NOT NULL" predicates.
--注意看以上提示,再次说明一些约束对控制执行计划的好处执行计划.执行如下稳定执行计划:
SCOTT@test> execute dbms_sqltune.accept_sql_profile(task_name => 'tuning 1war6t5g1w5g9', replace => TRUE ,name=>'tuning 1war6t5g1w5g9');
PL/SQL procedure successfully completed.
SCOTT@test> select /*SQL_1*/ c from t1 where not exists (select a from t2 where t2.a=t1.a) ;
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 1war6t5g1w5g9, child number 0
-------------------------------------
select /*SQL_1*/ c from t1 where a not in (select a from t2)
Plan hash value: 2739594415
-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 17 (100)| | 20 |00:00:00.08 | 73 | | | |
|* 1 | HASH JOIN RIGHT ANTI NA| | 1 | 100 | 1900 | 17 (6)| 00:00:01 | 20 |00:00:00.08 | 73 | 1517K| 1517K| 1852K (0)|
| 2 | TABLE ACCESS FULL | T2 | 1 | 9980 | 39920 | 8 (0)| 00:00:01 | 9980 |00:00:00.01 | 36 | | | |
| 3 | TABLE ACCESS FULL | T1 | 1 | 10000 | 146K| 8 (0)| 00:00:01 | 10000 |00:00:00.01 | 37 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
2 - SEL$5DA710D3 / T2@SEL$2
3 - SEL$5DA710D3 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"="A")
Note
-----
- SQL profile tuning 1war6t5g1w5g9 used for this statement
--说明SQL PROFILE已经起作用.注意一些细节,这个执行计划与not exists是有一点小小的不同.HASH JOIN RIGHT ANTI NA.
4.看看sql profile使用的提示:
SCOTT@test> @hide _optimizer_null_aware_antijoin
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
------------------------------- ------------------------------ -------------- -------------- -------------
_optimizer_null_aware_antijoin null-aware antijoin parameter TRUE FALSE FALSE
$ cat spext.sql
/* Formatted on 2015/4/10 17:03:49 (QP5 v5.252.13127.32867) */
column hint format a150
column name format a30
SELECT EXTRACTVALUE (VALUE (h), '.') AS hint,so.name
FROM SYS.sqlobj$data od
,SYS.sqlobj$ so
,TABLE
(
XMLSEQUENCE
(
EXTRACT (XMLTYPE (od.comp_data), '/outline_data/hint')
)
) h
WHERE so.NAME in ( 'profile &&1', 'tuning &&1','switch tuning &&1')
AND so.signature = od.signature
AND so.CATEGORY = od.CATEGORY
AND so.obj_type = od.obj_type
AND so.plan_id = od.plan_id;
SCOTT@test> @spext 1war6t5g1w5g9
HINT NAME
-------------------------------------- ------------------------------
OPTIMIZER_FEATURES_ENABLE(default) tuning 1war6t5g1w5g9
--昏,提示仅仅是OPTIMIZER_FEATURES_ENABLE(default).
5.最后测试作者提到的隐含参数:
--drop sql profile.
SCOTT@test> execute dbms_sqltune.drop_sql_profile(name => 'tuning 1war6t5g1w5g9')
PL/SQL procedure successfully completed.
SCOTT@test> alter session set "_optimizer_null_aware_antijoin"=true;
Session altered.
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
--------------------------------------
SQL_ID 1war6t5g1w5g9, child number 1
-------------------------------------
select /*SQL_1*/ c from t1 where a not in (select a from t2)
Plan hash value: 2739594415
-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 17 (100)| | 20 |00:00:00.08 | 73 | | | |
|* 1 | HASH JOIN RIGHT ANTI NA| | 1 | 20 | 380 | 17 (6)| 00:00:01 | 20 |00:00:00.08 | 73 | 1517K| 1517K| 1886K (0)|
| 2 | TABLE ACCESS FULL | T2 | 1 | 9980 | 39920 | 8 (0)| 00:00:01 | 9980 |00:00:00.01 | 36 | | | |
| 3 | TABLE ACCESS FULL | T1 | 1 | 10000 | 146K| 8 (0)| 00:00:01 | 10000 |00:00:00.01 | 37 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
2 - SEL$5DA710D3 / T2@SEL$2
3 - SEL$5DA710D3 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"="A")
SCOTT@test> alter session set "_optimizer_null_aware_antijoin"=false;
Session altered.
6.加入非NULL看看情况如何?
SCOTT@test> alter table t1 modify a not null;
Table altered.
SCOTT@test> alter table t2 modify a not null;
Table altered.
SCOTT@test> select /*SQL_1*/ c from t1 where a not in (select a from t2) ;
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
--------------------------------------
SQL_ID 1war6t5g1w5g9, child number 0
-------------------------------------
select /*SQL_1*/ c from t1 where a not in (select a from t2)
Plan hash value: 629543484
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 17 (100)| | 20 |00:00:00.08 | 73 | | | |
|* 1 | HASH JOIN RIGHT ANTI| | 1 | 20 | 380 | 17 (6)| 00:00:01 | 20 |00:00:00.08 | 73 | 1517K| 1517K| 1830K (0)|
| 2 | TABLE ACCESS FULL | T2 | 1 | 9980 | 39920 | 8 (0)| 00:00:01 | 9980 |00:00:00.01 | 36 | | | |
| 3 | TABLE ACCESS FULL | T1 | 1 | 10000 | 146K| 8 (0)| 00:00:01 | 10000 |00:00:00.01 | 37 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
2 - SEL$5DA710D3 / T2@SEL$2
3 - SEL$5DA710D3 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"="A")
--注意id1:HASH JOIN RIGHT ANTI.
--总结:
--可以发现选择很好的执行计划,正像作者讲的那样,数据结构设计在这里起了很关键的作用,如果在一些细节上处理好,就可以避免许多性
--能问题.另外注意not in,not exists,或者in,exist的语句里面的sql语句,如果存在性能问题,可以试着在这些字段上确定是否为非空,
--加入一些约束也许能改变执行机会,取得好的优化效果.