[20180410]為什麼2個邏輯讀不一樣.txt
[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 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2152739/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20210301]為什麼邏輯讀這麼多.txt
- [20180425]為什麼走索引邏輯讀反而高.txt索引
- [20210220]gdb跟蹤邏輯讀2.txt
- 在Oracle中，什麼是物理讀和邏輯讀？Oracle
- 邏輯迴歸為什麼使用sigmod邏輯迴歸
- [20231124]奇怪的高邏輯讀4.txt
- [20230216]奇怪的高邏輯讀3.txt
- [20210224]fetch r=0算邏輯讀嗎.txt
- Scrapy中傳送請求的固定邏輯?為什麼要這樣寫?
- [20210219]全表掃描邏輯讀問題.txt
- 為什麼單身的人聞起來不一樣？
- [20221207]為什麼FORCE_MATCHING_SIGNATURE不一致.txt
- 01-什麼是邏輯？
- [20201204]為什麼返回2行記錄.txt
- [20220216]為什麼出現這樣的情況.txt
- [20210220]全索引掃描快速索引掃描的邏輯讀.txt索引
- 同樣一張圖片為什麼使用 ssim 對比後會不一樣？？
- [20200326]為什麼選擇這個索引.txt索引
- 網站快照標題描述為什麼與實際不一樣網站
- Defect和Bug有什麼不一樣？ -Nikita
- 02-邏輯學有什麼用？
- java短路邏輯運算子是什麼Java
- 為什麼在dpr為1和2的裝置上1px線的粗細不一樣 一個20px高的div高度一樣
- [20201208]為什麼返回2行記錄補充.txt
- oracle邏輯讀過程Oracle
- 什麼是EOS（不一樣的角度看柚子）
- [作業系統]訪問一個邏輯地址發生了什麼作業系統
- 幽默：什麼是業務邏輯程式碼？
- 計算機程式的思維邏輯 (20) – 為什麼要有抽象類？計算機抽象
- 《底層邏輯》讀後感
- echarts問什麼 tooptip引數值會不一樣？EchartsOOP
- 什麼是物理畫素和邏輯畫素？
- 每個專案都寫 helper? 為什麼不自己搞一個呢？給你一個不一樣的 PHP-tools 分享PHP
- 為什麼讀書？
- sql生成可讀性邏輯圖SQL
- [20220331]為什麼不使用索引.txt索引
- [20201203]為什麼不使用索引.txt索引
- [20181015]為什麼是3秒.txt