060611G _optimizer_null_aware_antijoin

[20170606]11G _optimizer_null_aware_antijoin.txt

--//上午测试_optimizer_null_aware_antijoin,发现自己不经意间又犯了一个低级错误,做1个记录.

1.环境:
SYS@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SYS@book> @ &r/hide _optimizer_null_aware_antijoin
NAME                           DESCRIPTION                   DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
------------------------------ ----------------------------- ------------- ------------- ------------
_optimizer_null_aware_antijoin null-aware antijoin parameter TRUE          TRUE          TRUE

2.做一个测试例子:
SCOTT@book> create table t1 as select * from dba_objects;
Table created.

SCOTT@book> create table t2 as select * from dba_objects;
Table created.

SCOTT@book> select count(*) from t2;
  COUNT(*)
----------
     86996

SCOTT@book> select count(*) from t1;
  COUNT(*)
----------
     86995
--//分析表略.
--//因为T1,T2建立有先后,这样T2的记录数量比T1记录的数量多1.注意1个特点允许NULL(这样建立的表),如果你使用all_objects来建立就情况不同了:
--//你可以查看定义desc all_objects, desc dba_objects.
SCOTT@book> @ &r/desc t1
   Name            Null? Type
   --------------- ----- --------------
1 OWNER                 VARCHAR2(30)
2 OBJECT_NAME           VARCHAR2(128)
3 SUBOBJECT_NAME        VARCHAR2(30)
4 OBJECT_ID             NUMBER
5 DATA_OBJECT_ID        NUMBER
6 OBJECT_TYPE           VARCHAR2(19)
7 CREATED               DATE
8 LAST_DDL_TIME         DATE
9 TIMESTAMP             VARCHAR2(19)
10 STATUS                VARCHAR2(7)
11 TEMPORARY             VARCHAR2(1)
12 GENERATED             VARCHAR2(1)
13 SECONDARY             VARCHAR2(1)
14 NAMESPACE             NUMBER
15 EDITION_NAME          VARCHAR2(30)

SCOTT@book> select * from t1 where object_name='T2';
no rows selected

SCOTT@book> select * from t2 where object_name='T2';
OWNER  OBJECT_NAME SUBOBJECT_  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED             LAST_DDL_TIME       TIMESTAMP           STATUS  T G S  NAMESPACE EDITION_NAME
------ ----------- ---------- ---------- -------------- ----------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------
SCOTT  T2                          90863          90863 TABLE       2017-06-06 16:12:27 2017-06-06 16:12:27 2017-06-06:16:12:27 VALID   N N N          1

SCOTT@book> select  * from t1 where OBJECT_ID=90863;
no rows selected

3.测试:
SCOTT@book> alter session set statistics_level=all;
Session altered.

SCOTT@book> select count(*) from t2 where object_id not in(select object_id from t1);
  COUNT(*)
----------
         0

SCOTT@book> select * from t2 where object_id not in(select object_id from t1);
no rows selected
--//why? 理论应该返回1行啊.

SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  45ppus55apd1z, child number 1
-------------------------------------
select * from t2 where object_id not in(select object_id from t1)
Plan hash value: 35395643
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |      1 |        |       |       |  1218 (100)|          |      0 |00:00:00.06 |    1244 |   1242 |       |       |          |
|*  1 |  HASH JOIN RIGHT ANTI NA|      |      1 |    870 | 89610 |  1448K|  1218   (1)| 00:00:15 |      0 |00:00:00.06 |    1244 |   1242 |  1696K|  1696K| 2503K (0)|
|   2 |   TABLE ACCESS FULL     | T1   |      1 |  86995 |   424K|       |   347   (1)| 00:00:05 |  86994 |00:00:00.02 |    1244 |   1242 |       |       |          |
|   3 |   TABLE ACCESS FULL     | T2   |      0 |  86996 |  8325K|       |   347   (1)| 00:00:05 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$5DA710D3
   2 - SEL$5DA710D3 / T1@SEL$2
   3 - SEL$5DA710D3 / T2@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("OBJECT_ID"="OBJECT_ID")
27 rows selected.

--//这才想起来以前的一个错误,不能这样查询,因为IN中的记录存在NULL,这样NULL<>NULL.应该改写如下:

SCOTT@book> select * from t2 where object_id not in(select object_id from t1 where t1.object_id is not null );
OWNER  OBJECT_NAME          SUBOBJECT_  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED             LAST_DDL_TIME       TIMESTAMP           STATUS  T G S  NAMESPACE EDITION_NAME
------ -------------------- ---------- ---------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------
SCOTT  T2                                   90863          90863 TABLE               2017-06-06 16:12:27 2017-06-06 16:12:27 2017-06-06:16:12:27 VALID   N N N          1

SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  9cn6amuwk12r4, child number 0
-------------------------------------
select * from t2 where object_id not in(select object_id from t1 where
t1.object_id is not null )
Plan hash value: 2323664790
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |      1 |        |       |       |  1218 (100)|          |      1 |00:00:00.15 |    2489 |   2484 |       |       |          |
|*  1 |  HASH JOIN RIGHT ANTI SNA|      |      1 |    870 | 89610 |  1448K|  1218   (1)| 00:00:15 |      1 |00:00:00.15 |    2489 |   2484 |  5416K|  2890K| 5574K (0)|
|*  2 |   TABLE ACCESS FULL      | T1   |      1 |  86993 |   424K|       |   347   (1)| 00:00:05 |  86993 |00:00:00.02 |    1244 |   1242 |       |       |          |
|   3 |   TABLE ACCESS FULL      | T2   |      1 |  86996 |  8325K|       |   347   (1)| 00:00:05 |  86996 |00:00:00.03 |    1245 |   1242 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$5DA710D3
   2 - SEL$5DA710D3 / T1@SEL$2
   3 - SEL$5DA710D3 / T2@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("OBJECT_ID"="OBJECT_ID")
   2 - filter("T1"."OBJECT_ID" IS NOT NULL)
--//注意执行计划中的HASH JOIN RIGHT ANTI SNA.

4.测试"_optimizer_null_aware_antijoin"=false看看.

SCOTT@book> alter session set "_optimizer_null_aware_antijoin"=false;
Session altered.

SCOTT@book> Select * from t2 where object_id not in(select object_id from t1 where t1.object_id is not null );
OWNER  OBJECT_NAME          SUBOBJECT_  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED             LAST_DDL_TIME       TIMESTAMP           STATUS  T G S  NAMESPACE EDITION_NAME
------ -------------------- ---------- ---------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------
SCOTT  T2                                   90863          90863 TABLE               2017-06-06 16:12:27 2017-06-06 16:12:27 2017-06-06:16:12:27 VALID   N N N          1
--//昏,那个慢受不了.......

SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  4ps64yjz4g7pu, child number 0
-------------------------------------
Select * from t2 where object_id not in(select object_id from t1 where
t1.object_id is not null )
Plan hash value: 2132554994
------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |  8847K(100)|          |      1 |00:04:06.56 |      53M|     67M|
|*  1 |  FILTER            |      |      1 |        |       |            |          |      1 |00:04:06.56 |      53M|     67M|
|   2 |   TABLE ACCESS FULL| T2   |      1 |  86996 |  8325K|   347   (1)| 00:00:05 |  86996 |00:00:00.05 |    1245 |   1242 |
|*  3 |   TABLE ACCESS FULL| T1   |  86995 |      2 |    10 |   116   (0)| 00:00:02 |  86994 |00:04:05.95 |      53M|     67M|
------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / T2@SEL$1
   3 - SEL$2 / T1@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( IS NULL)
   3 - filter(("T1"."OBJECT_ID" IS NOT NULL AND LNNVL("OBJECT_ID"<>:B1)))
29 rows selected.

3.换1个方式:

SCOTT@book> create table t11 as select * from all_objects;
Table created.

SCOTT@book> create table t22 as select * from all_objects;
Table created.
--//分析表略.

SCOTT@book> @ &r/desc t11
Name           Null?    Type
-------------- -------- ------------
OWNER          NOT NULL VARCHAR2(30)
OBJECT_NAME    NOT NULL VARCHAR2(30)
SUBOBJECT_NAME          VARCHAR2(30)
OBJECT_ID      NOT NULL NUMBER
DATA_OBJECT_ID          NUMBER
OBJECT_TYPE             VARCHAR2(19)
CREATED        NOT NULL DATE
LAST_DDL_TIME  NOT NULL DATE
TIMESTAMP               VARCHAR2(19)
STATUS                  VARCHAR2(7)
TEMPORARY               VARCHAR2(1)
GENERATED               VARCHAR2(1)
SECONDARY               VARCHAR2(1)
NAMESPACE      NOT NULL NUMBER
EDITION_NAME            VARCHAR2(30)

--// 注意这样定义的表OBJECT_ID是非空.

SCOTT@book> select * from t22 where object_id not in(select object_id from t11);
OWNER  OBJECT_NAME          SUBOBJECT_  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED             LAST_DDL_TIME       TIMESTAMP           STATUS  T G S  NAMESPACE EDITION_NAME
------ -------------------- ---------- ---------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------
SCOTT  T22                                  90869          90869 TABLE               2017-06-06 16:33:29 2017-06-06 16:33:29 2017-06-06:16:33:29 VALID   N N N          1

SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  31yq53mnd3g7k, child number 1
-------------------------------------
select * from t22 where object_id not in(select object_id from t11)
Plan hash value: 3941821364
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |       |       |  1187 (100)|          |      1 |00:00:00.15 |    2427 |   2422 |       |       |          |
|*  1 |  HASH JOIN RIGHT ANTI|      |      1 |    848 | 87344 |  1408K|  1187   (1)| 00:00:15 |      1 |00:00:00.15 |    2427 |   2422 |  5236K|  2890K| 5538K (0)|
|   2 |   TABLE ACCESS FULL  | T11  |      1 |  84763 |   413K|       |   338   (1)| 00:00:05 |  84763 |00:00:00.02 |    1213 |   1211 |       |       |          |
|   3 |   TABLE ACCESS FULL  | T22  |      1 |  84764 |  8112K|       |   338   (1)| 00:00:05 |  84764 |00:00:00.03 |    1214 |   1211 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$5DA710D3
   2 - SEL$5DA710D3 / T11@SEL$2
   3 - SEL$5DA710D3 / T22@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("OBJECT_ID"="OBJECT_ID")

--//注意执行计划HASH JOIN RIGHT ANTI.我想起来以前10g我也遇到类型问题,将数据类型修改为NULL.

4.退出测试使用not exists的情况:

SCOTT@book> select * from t2 where not exists  (select 1 from t1  where t1.OBJECT_ID=t2.object_id);
OWNER  OBJECT_NAME SUBOBJECT_  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED             LAST_DDL_TIME       TIMESTAMP           STATUS  T G S  NAMESPACE EDITION_NAME
------ ----------- ---------- ---------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------
SCOTT  T2                          90863          90863 TABLE               2017-06-06 16:12:27 2017-06-06 16:12:27 2017-06-06:16:12:27 VALID   N N N          1
PUBLIC LOOPBACK                                         DATABASE LINK       2016-12-14 15:17:00                                         VALID   N N N
PUBLIC TEST040                                          DATABASE LINK       2017-01-09 09:14:26                                         VALID   N N N
--//这样返回3条.也就是这样查询注意空值的情况.也就是要加入t2.object_id is not null 条件,才会返回1条.

SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  4jx0g5ndct9vk, child number 0
-------------------------------------
select * from t2 where not exists  (select 1 from t1  where
t1.OBJECT_ID=t2.object_id)
Plan hash value: 1142061071
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |       |       |  1218 (100)|          |      3 |00:00:00.14 |    2489 |   2484 |       |       |          |
|*  1 |  HASH JOIN RIGHT ANTI|      |      1 |    870 | 89610 |  1448K|  1218   (1)| 00:00:15 |      3 |00:00:00.14 |    2489 |   2484 |  5416K|  2890K| 5759K (0)|
|   2 |   TABLE ACCESS FULL  | T1   |      1 |  86995 |   424K|       |   347   (1)| 00:00:05 |  86995 |00:00:00.02 |    1244 |   1242 |       |       |          |
|   3 |   TABLE ACCESS FULL  | T2   |      1 |  86996 |  8325K|       |   347   (1)| 00:00:05 |  86996 |00:00:00.02 |    1245 |   1242 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$5DA710D3
   2 - SEL$5DA710D3 / T1@SEL$2
   3 - SEL$5DA710D3 / T2@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

SCOTT@book> select * from t1 where object_id is null ;
OWNER  OBJECT_NAME          SUBOBJECT_  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED             LAST_DDL_TIME       TIMESTAMP           STATUS  T G S  NAMESPACE EDITION_NAME
------ -------------------- ---------- ---------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------
PUBLIC LOOPBACK                                                  DATABASE LINK       2016-12-14 15:17:00                                         VALID   N N N
PUBLIC TEST040                                                   DATABASE LINK       2017-01-09 09:14:26                                         VALID   N N N
--//正好2条object_id is null.

5.总结:
1.再次注意not in 与NULL的查询.
2.建立良好的约束,规避一些这样的问题.

时间： 2024-10-13 00:41:37

060611G _optimizer_null_aware_antijoin的相关文章

浅析_optimizer_null_aware_antijoin引发的SQL性能问题

前几天某客户联系我说之前我们进行存储迁移的系统,有个SQL跑的极慢,根本跑不出来结果.通过VPN登录看了下,SQL确认跑的很慢.开始我很难理解,我们仅仅是进行了存储迁移,数据库基本上没动,为什么会有SQL性能问题呢?  我们先来看看有问题的SQL: SYS@rptdb1> set autot traceonly exp SYS@rptdb1> select a.*,b.rate TAX_RATE,round(a.charge*b.rate/(1+b.rate),0) tax,a.charge-

052011GR2 _optimizer_null_aware_antijoin

[20150520]11GR2 _optimizer_null_aware_antijoin.txt --好久没写sql 优化的帖子: http://www.killdb.com/2015/04/19/_optimizer_null_aware_antijoin%E5%BC%95%E5%8F%91%E7%9A%84sql%E6%80%A7%E8%83%BD%E9%97%AE%E9%A2%98.html --参考这个链接自己重复测试看看在11G下的情况: 1.建立测试环境: SCOTT@test>

Oracle 12.1 RAC安装新主机，识别老存储和恢复数据库例子

具体步骤如下(因为先在12.1中测试,然后才在112.测试,因此这里的测试记录了发现的一些问题和处理方法,而11.2中模拟主机损坏,直接使用了这里的经验,因此没有任何报错信息): 1,安装12.1.0.2的GI软件,如果需要也apply最新的PSU,然后查看磁盘和磁盘组: [grid@lunarrac ~]$ kfod disks=all ds=true cluster=true status=true ----------------------------------------------

杀手SQL- 一条关于 &#039;Not in&#039; SQL 的优化案例

编辑手记:在 DBA 所优化的数据库环境中,绝大多数性能问题其实是由于 SQL 编写不当导致的.SQL 的世界无奇不有,今天我们一起见识一条让你绝对想吐血的杀手SQL. 某保险客户,ETL 耗时数个小时,我们做了sql report发现压力主要在其中一个SQL上. 单次执行时间:5788(秒) 单次逻辑读:10亿(块) 单次返回行数:21万(行) 我们首先看SQL语句,因为比较长,此处只节选部分的 查看其执行计划: 我们主要关注一下从7到16行:发现存在两次全表扫描.中间做了一次filter.

《Oracle高性能SQL引擎剖析：SQL优化与调优机制详解》一2.4　执行计划各个操作的含义

2.4 执行计划各个操作的含义 通常我们所说的执行计划操作包含两个部分:操作与其选项.例如,哈希关联反关联(HASH JOIN ANTI)中,哈希关联(HASH JOIN)是一种操作,"反"关联(ANTI)则是其选项:该操作还可以与其他选项(如"半"关联,SEMI)配合形成不同的执行计划操作. 执行计划中的操作数量非常多.我们下面列出的操作是Oracle 10gR2中的绝大多数操作.Oracle的每个版本都会有一些新的特性出现,而其中一些新特性又会带来新的操作,或者

20160318了解oracle版本升级的一些参数变化

[20160318]了解oracle版本升级的一些参数变化.txt --曾经写过一篇了解oracle版本升级后一些参数变化,可以通过如下连接了解: http://blog.itpub.net/267265/viewspace-1655594/ --实际上还有一个简单的方法就是提示opt_param('optimizer_features_enable' '11.2.0.1') ,然后顺便执行一条sql语句,查看执行计 --划就可以那些参数发生了变化.通过例子说明: 1.环境: SCOTT@boo

[20150515]简单了解某个版本有什么新特性

[20150515]简单了解某个版本有什么新特性.txt --如果想简单的了解某个版本的新特性,可以设置optimizer_features_enable SCOTT@test> show parameter optimizer_features_enable NAME                                 TYPE    VALUE ------------------------------------ ------- -------- optimizer_feat

1014OPT_PARAM OPTIMIZER_FEATURES_ENABLE

[20141014]OPT_PARAM与OPTIMIZER_FEATURES_ENABLE.txt http://www.itpub.net/thread-1495845-1-1.html http://space.itpub.net/267265/viewspace-723066 http://blog.itpub.net/267265/viewspace-752117/ http://blog.itpub.net/267265/viewspace-1298186/ --上午写了一篇blog,

Oracle 隐含参数

Oracle 隐含参数 点击(此处)折叠或打开 set pagesize 9999 set line 9999 col NAME format a40 col KSPPDESC format a50 col KSPPSTVL format a20 SELECT a.INDX,        a.KSPPINM NAME,        a.KSPPDESC,        b.KSPPSTVL FROM x$ksppi a,        x$ksppcv b WHERE a.INDX = b.