[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 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
