[20180410]为什么2个逻辑读不一样.txt

简介: [20180410]为什么2个逻辑读不一样.txt --//昨天做测试时,发现一个小问题,链接http://blog.itpub.net/267265/viewspace-2152693/ --//发现2个逻辑读不一样,做一些简单探究.

[20180410]为什么2个逻辑读不一样.txt

--//昨天做测试时,发现一个小问题,链接http://blog.itpub.net/267265/viewspace-2152693/
--//发现2个逻辑读不一样,做一些简单探究.

1.环境:
SCOTT@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

SCOTT@book> create table t1 as select * from all_objects order by DBMS_RANDOM.random;
Table created.

SCOTT@book> create table t2 as select * from t1 order by DBMS_RANDOM.random;
Table created.

--//分析略.Method_Opt => 'FOR ALL COLUMNS SIZE 1 '

2.建立测试脚本:
$ cat a3.sql
set term off
select  t2.* from t2 where  exists (select owner from t1 where t1.object_id=t2.object_id );
set term on

--//主要避免显示输出在屏幕太乱.

3.测试:

SCOTT@book> alter session set statistics_level=all;
Session altered.

SCOTT@book> show array
arraysize 200


SCOTT@book> @ a3
SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  7wjwu1wk8vu8q, child number 0
-------------------------------------
select  t2.* from t2 where  exists (select owner from t1 where
t1.object_id=t2.object_id )
Plan hash value: 3761397674
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |       |   677 (100)|          |  84765 |00:00:00.16 |    2849 |   2422 |       |       |          |
|*  1 |  HASH JOIN RIGHT SEMI|      |      1 |  84765 |  8526K|   677   (1)| 00:00:09 |  84765 |00:00:00.16 |    2849 |   2422 |  5236K|  2890K| 5675K (0)|
|   2 |   TABLE ACCESS FULL  | T1   |      1 |  84765 |   413K|   338   (1)| 00:00:05 |  84765 |00:00:00.02 |    1213 |   1211 |       |       |          |
|   3 |   TABLE ACCESS FULL  | T2   |      1 |  84765 |  8112K|   338   (1)| 00:00:05 |  84765 |00:00:00.03 |    1636 |   1211 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
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")

--//你可以发现t1的全表扫描逻辑读1213,而t2的全表扫描逻辑读是1636,而2个表实际上大小一样的.为什么出现这样的情况呢?

4.分析:
--//实际上看执行计划就明白了,T1表作为驱动表,全表扫描T1作为hash散列表,而T2查探如果符合条件输出(受arraay影响)
--//修改array参数就明白了:

SCOTT@book> set array 50
SCOTT@book> @ a3
SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  7wjwu1wk8vu8q, child number 0
-------------------------------------
select  t2.* from t2 where  exists (select owner from t1 where
t1.object_id=t2.object_id )
Plan hash value: 3761397674
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |       |   677 (100)|          |  84765 |00:00:00.23 |    4107 |   2422 |       |       |          |
|*  1 |  HASH JOIN RIGHT SEMI|      |      1 |  84765 |  8526K|   677   (1)| 00:00:09 |  84765 |00:00:00.23 |    4107 |   2422 |  5236K|  2890K| 5729K (0)|
|   2 |   TABLE ACCESS FULL  | T1   |      1 |  84765 |   413K|   338   (1)| 00:00:05 |  84765 |00:00:00.02 |    1213 |   1211 |       |       |          |
|   3 |   TABLE ACCESS FULL  | T2   |      1 |  84765 |  8112K|   338   (1)| 00:00:05 |  84765 |00:00:00.06 |    2894 |   1211 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------

--//可以发现t1的全表扫描逻辑读依旧是1213,而t2的全表扫描逻辑读是2894.
--//实际上对T1的逻辑读解决T1占用的块数.
SCOTT@book> select blocks from dba_tables where owner=user and table_name='T1';
    BLOCKS
----------
      1239

--//再简单做一个测试就明白了.
SCOTT@book> select /*+ full(t1) */ count(*) from t1;
  COUNT(*)
----------
     84765

SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  8txmkmay1nwxd, child number 0
-------------------------------------
select /*+ full(t1) */ count(*) from t1
Plan hash value: 3724264953
----------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |   338 (100)|          |      1 |00:00:00.03 |    1213 |   1211 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |            |          |      1 |00:00:00.03 |    1213 |   1211 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |  84765 |   338   (1)| 00:00:05 |  84765 |00:00:00.02 |    1213 |   1211 |
----------------------------------------------------------------------------------------------------------------------

--//count(*)记数,全表扫描逻辑读是1213.与前面符合.

$ cat a2.sql
set term off
select /*+ full(t2) */ owner from t2;
set term on

SCOTT@book> show array
arraysize 50

SCOTT@book> @ a2.sql
SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  bvvnmu4zqz3pj, child number 0
-------------------------------------
select /*+ full(t2) */ owner from t2
Plan hash value: 1513984157
-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |       |   338 (100)|          |  84765 |00:00:00.05 |    2894 |   1211 |
|   1 |  TABLE ACCESS FULL| T2   |      1 |  84765 |   496K|   338   (1)| 00:00:05 |  84765 |00:00:00.05 |    2894 |   1211 |
-----------------------------------------------------------------------------------------------------------------------------

--//逻辑读是2894,与前面设置array=50一致.

5.补充:
--//别人问的问题,是否可以实现执行如下语句时.
select  t2.* from t2 where  exists (select owner from t1 where t1.object_id=t2.object_id );
--//T2作为驱动表.测试看看.

--//再次执行a3.sql,取出执行计划outline:
SCOTT@book> @ &r/dpc '' outline
..
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$5DA710D3")
      UNNEST(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      FULL(@"SEL$5DA710D3" "T2"@"SEL$1")
      FULL(@"SEL$5DA710D3" "T1"@"SEL$2")
      LEADING(@"SEL$5DA710D3" "T2"@"SEL$1" "T1"@"SEL$2")
      USE_HASH(@"SEL$5DA710D3" "T1"@"SEL$2")
      SWAP_JOIN_INPUTS(@"SEL$5DA710D3" "T1"@"SEL$2")
      END_OUTLINE_DATA
  */

--//修改a3.sql脚本如下,不要SWAP_JOIN_INPUTS行.

$ cat a3.sql
set term off
select
/*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$5DA710D3")
      UNNEST(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      FULL(@"SEL$5DA710D3" "T2"@"SEL$1")
      FULL(@"SEL$5DA710D3" "T1"@"SEL$2")
      LEADING(@"SEL$5DA710D3" "T2"@"SEL$1" "T1"@"SEL$2")
      USE_HASH(@"SEL$5DA710D3" "T1"@"SEL$2")
      END_OUTLINE_DATA
  */
t2.* from t2 where  exists (select owner from t1 where t1.object_id=t2.object_id );
set term on

SCOTT@book> @ a3.sql
SCOTT@book> @ &r/dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  2rc0puqy3d5th, child number 0
-------------------------------------
select  /*+       BEGIN_OUTLINE_DATA       IGNORE_OPTIM_EMBEDDED_HINTS
     OPTIMIZER_FEATURES_ENABLE('11.2.0.4')       DB_VERSION('11.2.0.4')
      ALL_ROWS       OUTLINE_LEAF(@"SEL$5DA710D3")
UNNEST(@"SEL$2")       OUTLINE(@"SEL$1")       OUTLINE(@"SEL$2")
FULL(@"SEL$5DA710D3" "T2"@"SEL$1")       FULL(@"SEL$5DA710D3"
"T1"@"SEL$2")       LEADING(@"SEL$5DA710D3" "T2"@"SEL$1" "T1"@"SEL$2")
     USE_HASH(@"SEL$5DA710D3" "T1"@"SEL$2")       END_OUTLINE_DATA   */
t2.* from t2 where  exists (select owner from t1 where
t1.object_id=t2.object_id )

Plan hash value: 3077929639

-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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)|          |  84765 |00:00:00.27 |    4095 |   2422 |       |       |          |
|*  1 |  HASH JOIN SEMI    |      |      1 |  84765 |  8526K|  9112K|  1187   (1)| 00:00:15 |  84765 |00:00:00.27 |    4095 |   2422 |    13M|  2225K|   14M (0)|
|   2 |   TABLE ACCESS FULL| T2   |      1 |  84765 |  8112K|       |   338   (1)| 00:00:05 |  84765 |00:00:00.03 |    1213 |   1211 |       |       |          |
|   3 |   TABLE ACCESS FULL| T1   |      1 |  84765 |   413K|       |   338   (1)| 00:00:05 |  84765 |00:00:00.03 |    2882 |   1211 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------

--//这样T2作为驱动表.而Id=2变成了HASH JOIN SEMI,而不是HASH JOIN RIGHT SEMI.
--//主要差别实际上在于生成hash表大小,先扫描T1,实际上全表扫描,仅仅需要object_id字段就ok了,这样生成的hash表要小.E-Bytes=413K亦可以说明问题.

--//手工写提示如下,也可以实现:
$ cat a5.sql
set term off
select  /*+ LEADING(T2 T1) USE_HASH(T1) */ t2.* from t2 where  exists (select 1 from t1 where t1.object_id=t2.object_id );
set term on
--//注:我把owner换成1.

SCOTT@book> @ a5.sql
SCOTT@book> @ &r/dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  7gdpjgm7h1u9j, child number 0
-------------------------------------
select  /*+ LEADING(T2 T1) USE_HASH(T1) */ t2.* from t2 where  exists
(select 1 from t1 where t1.object_id=t2.object_id )

Plan hash value: 3077929639

-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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)|          |  84765 |00:00:00.28 |    4095 |   2422 |       |       |          |
|*  1 |  HASH JOIN SEMI    |      |      1 |  84765 |  8526K|  9112K|  1187   (1)| 00:00:15 |  84765 |00:00:00.28 |    4095 |   2422 |    13M|  2225K|   14M (0)|
|   2 |   TABLE ACCESS FULL| T2   |      1 |  84765 |  8112K|       |   338   (1)| 00:00:05 |  84765 |00:00:00.03 |    1213 |   1211 |       |       |          |
|   3 |   TABLE ACCESS FULL| T1   |      1 |  84765 |   413K|       |   338   (1)| 00:00:05 |  84765 |00:00:00.04 |    2882 |   1211 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------

目录
相关文章
|
存储 C++
C++中txt文件的读、写操作介绍
C++中txt文件的读、写操作介绍
311 0
|
索引
[20180425]为什么走索引逻辑读反而高.txt
[20180425]为什么走索引逻辑读反而高.txt --//别人问的问题,自己测试看看,开始以为array设置太小.还是通过例子说明问题. 1.环境: SCOTT@book> @ ver1 PORT_STRING                    VE...
1013 0
|
SQL Oracle 关系型数据库
[20180319]直接路径读特例12c.txt
[20180319]直接路径读特例12c.txt --//昨天的测试突然想起以前遇到的直接路径读特例,在12c重复测试看看. 1.环境: SCOTT@test01p> @ ver1 PORT_STRING                    VERSION...
958 0
|
缓存 网络协议 关系型数据库
[20180316]共享服务模式和直接路径读.txt
[20180316]共享服务模式和直接路径读.txt --//在共享服务器模式下,执行计划不会选择直接路径读,通过例子证明. 1.环境: SYS@book> @ &r/ver1 PORT_STRING          VERSION    BANNER...
855 0
|
SQL Oracle 关系型数据库
[20171115]关于逻辑读的疑问.txt
[20171115]关于逻辑读的疑问.txt --//有网友指出[20150209]为什么少1个逻辑读.txt,链接:http://blog.itpub.net/267265/viewspace-1430902/ --//如何验证是这样操作的.
959 0
|
缓存 关系型数据库 Oracle
[20171120]11G关闭直接路径读.txt
[20171120]11G关闭直接路径读.txt --//今天做filesystemio_options参数测试时,遇到一个关于直接路径读的问题. --//如果看以前的博客介绍,设置"_serial_direct_read"=never或者events '10949 trace name context forever';就可以关闭直接路径读.
1008 0
|
缓存 数据库管理
[20170810]直接路径读特例2.txt
[20170810]直接路径读特例2.txt --//以前在11.2.0.4下测试,出现直接路径读的一种特殊情况,就是使用rowid访问数据块(使用between). --//参考链接:http://blog.
808 0
|
SQL 缓存 数据库管理
[20170308]直接路径读特例.txt
[20170308]直接路径读特例.txt --//昨天测试一些sql语句时,无意中发现出现直接路径读的一种特殊情况,就是使用rowid访问数据块(使用between).
744 0
[20160330]关于连接顺序3.txt
[20160330]关于连接顺序3.txt --关于连接顺序,曾经写过两篇blog,链接如下: http://blog.itpub.net/267265/viewspace-1991306/ http://blog.
767 0