[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语句,如果存在性能问题,可以试着在这些字段上确定是否为非空, --加入一些约束也许能改变执行机会,取得好的优化效果.