[20181031]lob欄位與布隆過濾.txt
[20181031]lob欄位與布隆過濾.txt
--//今天8月份遇到的問題,連結:[20180828]exadata--豆腐渣系統的保護神.txt=>http://blog.itpub.net/267265/viewspace-2213256/
1.環境:
SYS@xxxx1> @ &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
SELECT XXXXXX_YYY.EMR_BL03.*, XXXXXX_YYY.EMR_BL_BL01.BLMC
FROM XXXXXX_YYY.EMR_BL03
LEFT JOIN XXXXXX_YYY.EMR_BL_BL01
ON XXXXXX_YYY.EMR_BL03.BLBH = XXXXXX_YYY.EMR_BL_BL01.BLBH
WHERE XXXXXX_YYY.EMR_BL_BL01.BRBH = '00366441';
--//執行計劃,選擇布隆過濾,當時awr顯示需要54.27秒,IO佔49.51秒.
Plan hash value: 40434530
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 215K(100)| |
| 1 | HASH JOIN | | 19 | 27645 | 215K (1)| 00:43:02 |
| 2 | JOIN FILTER CREATE | :BF0000 | 19 | 817 | 16 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMR_BL_BL01 | 19 | 817 | 16 (0)| 00:00:01 |
| 4 | INDEX RANGE SCAN | I_EMR_BL_BL01_BRBH_CJSJ | 19 | | 3 (0)| 00:00:01 |
| 5 | JOIN FILTER USE | :BF0000 | 3968K| 5343M| 215K (1)| 00:43:01 |
| 6 | TABLE ACCESS STORAGE FULL | EMR_BL03 | 3968K| 5343M| 215K (1)| 00:43:01 |
----------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$C8875FE2
3 - SEL$C8875FE2 / EMR_BL_BL01@SEL$1
4 - SEL$C8875FE2 / EMR_BL_BL01@SEL$1
6 - SEL$C8875FE2 / EMR_BL03@SEL$2
zzzzz> @ &r/desc XXXXXX_YYY.EMR_BL03
Name Null? Type
----- -------- ----------------------------
WDBH NOT NULL NUMBER(18)
ZYMZ NOT NULL NUMBER(2)
BLBH NOT NULL NUMBER(18)
WDLX NOT NULL NUMBER(4)
WDNR BLOB
zzzzz> select segment_name,bytes/1024/1024/1024 Gb from DBA_SEGMENTS where segment_name='EMR_BL03';
SEGMENT_NAME GB
-------------------- ----------
EMR_BL03 12.2724609
zzzzz> select segment_name,bytes/1024/1024/1024 gb from dba_segments where segment_name in
(select segment_name from DBA_LOBS where table_name='EMR_BL03');
SEGMENT_NAME GB
------------------------------ ----------
SYS_LOB0000087717C00005$$ 102.436523
--//以上內容是當時的記錄.
--//我以前大概測試過我們現在使用的exadata,select /*+ full(a) */ count(*) from big_table a;IO最大吞吐量大約2.5GB/s.
--//(102.436523+12.2724609)/2.5 = 45.88359356,這樣非常接近.
--//主要原因:索引建立不合適,EMR_BL03存在索引IDX_EMR_BL03_BLBH.欄位包括ZYMZ, BLBH, WDLX.而且執行計劃沒有index skip scan.
--//當時遇到的困惑:
1.建立索引不合適,但是為什麼沒有選擇index skip scan.這個探究放棄.
2.即使選擇布隆過濾,選擇全表掃描EMR_BL03,讀取大量的lob資訊不應該.如果選擇hash join呢,會出現怎樣的情況呢?
2.測試:
--//今天主要探究第2個問題:
--//查詢不包括BLOB欄位,因為正確的索引已經建立.要繼續探究只能加入提示/*+ full(EMR_BL03) */.
xxxx> set timing on
xxxx> alter session set statistics_level=all;
Session altered.
--//採用bloom 過濾,查詢不包括lob欄位.
SELECT /*+ full(EMR_BL03) */ XXXXXX_YYY.EMR_BL03.WDBH
,XXXXXX_YYY.EMR_BL03.ZYMZ
,XXXXXX_YYY.EMR_BL03.BLBH
,XXXXXX_YYY.EMR_BL03.WDLX
,XXXXXX_YYY.EMR_BL_BL01.BLMC
FROM XXXXXX_YYY.EMR_BL03
LEFT JOIN XXXXXX_YYY.EMR_BL_BL01
ON XXXXXX_YYY.EMR_BL03.BLBH = XXXXXX_YYY.EMR_BL_BL01.BLBH
WHERE XXXXXX_YYY.EMR_BL_BL01.BRBH = '00366441';
Elapsed: 00:00:06.32
--//注:查詢不包含lob欄位.執行需要7秒!!執行計劃如下:
Plan hash value: 40434530
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 435K(100)| | 57 |00:00:05.23 | 1655K| 1655K| | | |
|* 1 | HASH JOIN | | 1 | 19 | 1159 | 435K (1)| 01:27:07 | 57 |00:00:05.23 | 1655K| 1655K| 2226K| 2226K| 1129K (0)|
| 2 | JOIN FILTER CREATE | :BF0000 | 1 | 19 | 817 | 16 (0)| 00:00:01 | 56 |00:00:00.01 | 44 | 0 | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| EMR_BL_BL01 | 1 | 19 | 817 | 16 (0)| 00:00:01 | 56 |00:00:00.01 | 44 | 0 | | | |
|* 4 | INDEX RANGE SCAN | I_EMR_BL_BL01_BRBH_CJSJ | 1 | 19 | | 3 (0)| 00:00:01 | 56 |00:00:00.01 | 3 | 0 | 1025K| 1025K| |
| 5 | JOIN FILTER USE | :BF0000 | 1 | 8118K| 139M| 435K (1)| 01:27:06 | 7220 |00:00:05.23 | 1655K| 1655K| | | |
|* 6 | TABLE ACCESS STORAGE FULL | EMR_BL03 | 1 | 8118K| 139M| 435K (1)| 01:27:06 | 7220 |00:00:05.22 | 1655K| 1655K| 1025K| 1025K| 3085K (0)|
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$C8875FE2
3 - SEL$C8875FE2 / EMR_BL_BL01@SEL$1
4 - SEL$C8875FE2 / EMR_BL_BL01@SEL$1
6 - SEL$C8875FE2 / EMR_BL03@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMR_BL03"."BLBH"="EMR_BL_BL01"."BLBH")
4 - access("EMR_BL_BL01"."BRBH"='00366441')
6 - storage(SYS_OP_BLOOM_FILTER(:BF0000,"EMR_BL03"."BLBH"))
filter(SYS_OP_BLOOM_FILTER(:BF0000,"EMR_BL03"."BLBH"))
--//buffers=1655K.
--//做10046跟蹤:
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
enq: KO - fast object checkpoint 3 0.04 0.04
reliable message 1 0.00 0.00
cell smart table scan 3678 0.08 3.38
SQL*Net message from client 2 4.40 4.40
********************************************************************************
--//可以發現enq: KO - fast object checkpoint,這是採用smart scan,必須先把髒塊寫盤,執行時間主要消耗在cell smart table scan,SQL*Net message from client.
3.測試2:
--//採用bloom 過濾,查詢包括lob欄位.
SELECT /*+ full(EMR_BL03) */ XXXXXX_YYY.EMR_BL03.*, XXXXXX_YYY.EMR_BL_BL01.BLMC
FROM XXXXXX_YYY.EMR_BL03
LEFT JOIN XXXXXX_YYY.EMR_BL_BL01
ON XXXXXX_YYY.EMR_BL03.BLBH = XXXXXX_YYY.EMR_BL_BL01.BLBH
WHERE XXXXXX_YYY.EMR_BL_BL01.BRBH = '00366441';
Elapsed: 00:00:46.77
--//執行需要47,與我前面看到基本一致.
--//執行計劃如下:
Plan hash value: 40434530
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 435K(100)| | 57 |00:00:44.96 | 1655K| 1655K| | | |
|* 1 | HASH JOIN | | 1 | 19 | 27284 | 435K (1)| 01:27:07 | 57 |00:00:44.96 | 1655K| 1655K| 2211K| 2211K| 1123K (0)|
| 2 | JOIN FILTER CREATE | :BF0000 | 1 | 19 | 817 | 16 (0)| 00:00:01 | 56 |00:00:00.01 | 44 | 0 | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| EMR_BL_BL01 | 1 | 19 | 817 | 16 (0)| 00:00:01 | 56 |00:00:00.01 | 44 | 0 | | | |
|* 4 | INDEX RANGE SCAN | I_EMR_BL_BL01_BRBH_CJSJ | 1 | 19 | | 3 (0)| 00:00:01 | 56 |00:00:00.01 | 3 | 0 | 1025K| 1025K| |
| 5 | JOIN FILTER USE | :BF0000 | 1 | 8118K| 10G| 435K (1)| 01:27:06 | 7220 |00:00:44.95 | 1655K| 1655K| | | |
|* 6 | TABLE ACCESS STORAGE FULL | EMR_BL03 | 1 | 8118K| 10G| 435K (1)| 01:27:06 | 7220 |00:00:44.95 | 1655K| 1655K| 1025K| 1025K| |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$C8875FE2
3 - SEL$C8875FE2 / EMR_BL_BL01@SEL$1
4 - SEL$C8875FE2 / EMR_BL_BL01@SEL$1
6 - SEL$C8875FE2 / EMR_BL03@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMR_BL03"."BLBH"="EMR_BL_BL01"."BLBH")
4 - access("EMR_BL_BL01"."BRBH"='00366441')
6 - storage(SYS_OP_BLOOM_FILTER(:BF0000,"EMR_BL03"."BLBH"))
filter(SYS_OP_BLOOM_FILTER(:BF0000,"EMR_BL03"."BLBH"))
--//buffers=1655K,大約1655*1024*8192/1024/1024/1024 = 12.9296875G,看來我以前的判斷有誤.並不會大量讀取lob資訊.
--//為什麼需要46秒呢?
xxxx> select * from dba_objects where owner='XXXXXX_YYY' and object_name='EMR_BL03';
OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME
------------ -------------------- ---------- ---------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------
XXXXXX_YYY EMR_BL03 87717 87717 TABLE 2014-10-28 18:54:49 2018-08-30 09:53:17 2014-10-28:18:54:49 VALID N N N 1
xxxx> select owner,column_name,segment_name,index_name from dba_lobs where owner='XXXXXX_YYY' and table_name='EMR_BL03';
OWNER COLUMN_NAME SEGMENT_NAME INDEX_NAME
------------ -------------------- ------------------------------ ------------------------------
XXXXXX_YYY WDNR SYS_LOB0000087717C00005$$ SYS_IL0000087717C00005$$
xxxx> select * from dba_objects where owner='XXXXXX_YYY' and object_name in ('SYS_LOB0000087717C00005$$','SYS_IL0000087717C00005$$');
OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME
------------ ------------------------------ ---------- ---------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------------------------
XXXXXX_YYY SYS_IL0000087717C00005$$ 87719 87719 INDEX 2014-10-28 18:54:49 2014-10-28 18:54:49 2014-10-28:18:54:49 VALID N Y N 4
XXXXXX_YYY SYS_LOB0000087717C00005$$ 87718 87718 LOB 2014-10-28 18:54:49 2014-10-28 18:54:49 2014-10-28:18:54:49 VALID N Y N 8
--//可以確定OBJ# = 87717,87718,87719
# grep "direct path read" /u01/app/oracle/diag/rdbms/xxxx/xxxx1/trace/xxxx1_ora_74497.trc | grep obj#=87717|wc
7099 106485 950291
# grep "direct path read" /u01/app/oracle/diag/rdbms/xxxx/xxxx1/trace/xxxx1_ora_74497.trc | grep obj#=87718|wc
23 345 2730
# grep "direct path read" /u01/app/oracle/diag/rdbms/xxxx/xxxx1/trace/xxxx1_ora_74497.trc | grep obj#=87719|wc
0 0 0
SELECT /*+ full(EMR_BL03) */ XXXXXX_YYY.EMR_BL03.*, XXXXXX_YYY.EMR_BL_BL01.BLMC
FROM XXXXXX_YYY.EMR_BL03
LEFT JOIN XXXXXX_YYY.EMR_BL_BL01
ON XXXXXX_YYY.EMR_BL03.BLBH = XXXXXX_YYY.EMR_BL_BL01.BLBH
WHERE XXXXXX_YYY.EMR_BL_BL01.BRBH = '00366441'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 58 4.95 37.06 1655745 1655859 0 57
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 60 4.95 37.06 1655745 1655859 0 57
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
57 57 57 HASH JOIN (cr=1655859 pr=1655745 pw=0 time=37062551 us cost=435533 size=27284 card=19)
56 56 56 JOIN FILTER CREATE :BF0000 (cr=44 pr=0 pw=0 time=253 us cost=16 size=817 card=19)
56 56 56 TABLE ACCESS BY INDEX ROWID EMR_BL_BL01 (cr=44 pr=0 pw=0 time=226 us cost=16 size=817 card=19)
56 56 56 INDEX RANGE SCAN I_EMR_BL_BL01_BRBH_CJSJ (cr=3 pr=0 pw=0 time=35 us cost=3 size=0 card=19)(object id 88921)
7220 7220 7220 JOIN FILTER USE :BF0000 (cr=1655815 pr=1655745 pw=0 time=37051920 us cost=435495 size=11308964632 card=8118424)
7220 7220 7220 TABLE ACCESS STORAGE FULL EMR_BL03 (cr=1655815 pr=1655745 pw=0 time=37049166 us cost=435495 size=11308964632 card=8118424)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
library cache lock 1 0.00 0.00
library cache pin 1 0.00 0.00
SQL*Net message to client 59 0.00 0.00
SQL*Net message from client 59 36.30 36.32
enq: KO - fast object checkpoint 3 0.01 0.01
reliable message 1 0.00 0.00
direct path read 7099 0.27 32.60
********************************************************************************
--//這裡並沒有包含lob的direct path read.
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 58 4.95 37.06 1655745 1655859 0 57
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 62 4.95 37.06 1655745 1655859 0 57
Misses in library cache during parse: 0
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 174 0.00 0.00
SQL*Net message from client 174 36.30 40.62
library cache lock 1 0.00 0.00
library cache pin 1 0.00 0.00
enq: KO - fast object checkpoint 3 0.01 0.01
reliable message 1 0.00 0.00
direct path read 7122 0.27 32.76
gc cr block 2-way 1 0.00 0.00
SQL*Net more data to client 5 0.00 0.00
--//也就是從某種意思講直接路徑讀導致exadata採用塊傳輸模式,沒有充分發揮exadata smart scan的作用.
4.測試3:
--//關閉布隆過濾.查詢不包括lob欄位:
SELECT /*+ OPT_PARAM('_bloom_filter_enabled' 'false') full(EMR_BL03) */ XXXXXX_YYY.EMR_BL03.WDBH
,XXXXXX_YYY.EMR_BL03.ZYMZ
,XXXXXX_YYY.EMR_BL03.BLBH
,XXXXXX_YYY.EMR_BL03.WDLX
,XXXXXX_YYY.EMR_BL_BL01.BLMC
FROM XXXXXX_YYY.EMR_BL03
LEFT JOIN XXXXXX_YYY.EMR_BL_BL01
ON XXXXXX_YYY.EMR_BL03.BLBH = XXXXXX_YYY.EMR_BL_BL01.BLBH
WHERE XXXXXX_YYY.EMR_BL_BL01.BRBH = '00366441';
Elapsed: 00:00:03.99
--//執行時間4秒.有點點詫異的是比方法1快一點點.
Plan hash value: 1372458871
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 435K(100)| | 57 |00:00:03.98 | 1655K| 1655K| | | |
|* 1 | HASH JOIN | | 1 | 19 | 1159 | 435K (1)| 01:27:07 | 57 |00:00:03.98 | 1655K| 1655K| 2226K| 2226K| 1284K (0)|
| 2 | TABLE ACCESS BY INDEX ROWID| EMR_BL_BL01 | 1 | 19 | 817 | 16 (0)| 00:00:01 | 56 |00:00:00.01 | 44 | 0 | | | |
|* 3 | INDEX RANGE SCAN | I_EMR_BL_BL01_BRBH_CJSJ | 1 | 19 | | 3 (0)| 00:00:01 | 56 |00:00:00.01 | 3 | 0 | 1025K| 1025K| |
| 4 | TABLE ACCESS STORAGE FULL | EMR_BL03 | 1 | 8118K| 139M| 435K (1)| 01:27:06 | 8397K|00:00:02.50 | 1655K| 1655K| 1025K| 1025K| 3085K (0)|
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$C8875FE2
2 - SEL$C8875FE2 / EMR_BL_BL01@SEL$1
3 - SEL$C8875FE2 / EMR_BL_BL01@SEL$1
4 - SEL$C8875FE2 / EMR_BL03@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMR_BL03"."BLBH"="EMR_BL_BL01"."BLBH")
3 - access("EMR_BL_BL01"."BRBH"='00366441')
5.測試4:
--//關閉布隆過濾.查詢包括lob欄位:
SELECT /*+ OPT_PARAM('_bloom_filter_enabled' 'false') full(EMR_BL03) */ XXXXXX_YYY.EMR_BL03.*, XXXXXX_YYY.EMR_BL_BL01.BLMC
FROM XXXXXX_YYY.EMR_BL03
LEFT JOIN XXXXXX_YYY.EMR_BL_BL01
ON XXXXXX_YYY.EMR_BL03.BLBH = XXXXXX_YYY.EMR_BL_BL01.BLBH
WHERE XXXXXX_YYY.EMR_BL_BL01.BRBH = '00366441';
Elapsed: 00:00:35.65
--//執行計劃如下:
Plan hash value: 1372458871
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 435K(100)| | 57 |00:00:33.94 | 1655K| 1655K| | | |
|* 1 | HASH JOIN | | 1 | 19 | 27284 | 435K (1)| 01:27:07 | 57 |00:00:33.94 | 1655K| 1655K| 2211K| 2211K| 1270K (0)|
| 2 | TABLE ACCESS BY INDEX ROWID| EMR_BL_BL01 | 1 | 19 | 817 | 16 (0)| 00:00:01 | 56 |00:00:00.01 | 44 | 0 | | | |
|* 3 | INDEX RANGE SCAN | I_EMR_BL_BL01_BRBH_CJSJ | 1 | 19 | | 3 (0)| 00:00:01 | 56 |00:00:00.01 | 3 | 0 | 1025K| 1025K| |
| 4 | TABLE ACCESS STORAGE FULL | EMR_BL03 | 1 | 8118K| 10G| 435K (1)| 01:27:06 | 8397K|00:00:32.16 | 1655K| 1655K| 1025K| 1025K| |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$C8875FE2
2 - SEL$C8875FE2 / EMR_BL_BL01@SEL$1
3 - SEL$C8875FE2 / EMR_BL_BL01@SEL$1
4 - SEL$C8875FE2 / EMR_BL03@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMR_BL03"."BLBH"="EMR_BL_BL01"."BLBH")
3 - access("EMR_BL_BL01"."BRBH"='00366441')
--//同類比較也比第2種方法快.
--//按照道理採用布隆過濾在exadata的儲存層完成應該更快一些,也許是全表掃描的原因.
6.總結:
--//看來我以前分析有誤,我以前一直以為oracle這種方法要掃描全部lob段,實際上不是.主要問題是採用direct path read沒有smart scan快.
--//實際上exadata要充分使用smart scan,採用direct path read是前提,規避行連結或者行遷移也是關鍵因素.使用lob相當於一部分資料出現行連結.
--//導致執行計劃中等待事件direct path read,這樣許多工作無法交給儲存層完成,轉移到了服務端,也就是快傳輸模式.無法充分發揮exadata的作用與優勢.
--//方法1,方法3就是沒有讀取lob欄位,可以充分發揮smart scan的最佳化,執行時間一個7秒,1個4秒.還有一點點就是採用布隆過濾的有點慢,我記得exadata書中講布隆計算在儲存層實現的.
--//也許這個全表掃描真的很消耗儲存層資源.
--//另外有點意外的是lob直接路徑讀僅僅抓到1次.
xxxx> SELECT sql_id FROM V$ACTIVE_SESSION_HISTORY WHERE event = 'direct path read' AND sample_time >= TRUNC (SYSDATE) AND current_obj# = 87718;
SQL_ID
-------------
26zqdq622vt0m
xxxx> @ &r/sqlid 26zqdq622vt0m
SQL_ID SQLTEXT
------------- -------------------------------------------------------------------------------
26zqdq622vt0m SELECT /*+ full(EMR_BL03) */ XXXXXX_YYY.EMR_BL03.*, XXXXXX_YYY.EMR_BL_BL01.BLMC
FROM XXXXXX_YYY.EMR_BL03
LEFT JOIN XXXXXX_YYY.EMR_BL_BL01
ON XXXXXX_YYY.EMR_BL03.BLBH = XXXXXX_YYY.EMR_BL_BL01.BLBH
WHERE XXXXXX_YYY.EMR_BL_BL01.BRBH = '00366441'
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2218012/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 布隆過濾器過濾器
- 布隆過濾器 與 Redis BitMap過濾器Redis
- Redis布隆過濾器分析與總結Redis過濾器
- 淺談布隆過濾器過濾器
- Redis-布隆過濾器Redis過濾器
- 布隆過濾器（Bloom Filter）過濾器OOMFilter
- 大白話布隆過濾器過濾器
- Guava的布隆過濾器Guava過濾器
- 布隆過濾器 Bloom Filter過濾器OOMFilter
- Bloom Filter 布隆過濾器OOMFilter過濾器
- [20210208]lob欄位與查詢的問題.txt
- [20181020]lob欄位的索引段.txt索引
- 詳解布隆過濾器原理與實現過濾器
- victoriaMetrics庫之布隆過濾器過濾器
- Redis 應用-布隆過濾器Redis過濾器
- PHP實現布隆過濾器PHP過濾器
- Redis 中的布隆過濾器Redis過濾器
- Xor過濾器：比布隆Bloom過濾器更快，更小過濾器OOM
- 5分鐘掌握布隆過濾器過濾器
- 還有人不懂布隆過濾器嗎？過濾器
- 布隆過濾器(Bloom Filter)詳解過濾器OOMFilter
- 從快取穿透聊到布隆過濾器快取穿透過濾器
- 演算法(3)---布隆過濾器原理演算法過濾器
- 布隆過濾器-使用場景的思考過濾器
- Redis詳解（十三）------ Redis布隆過濾器Redis過濾器
- 布隆過濾器的原理及應用過濾器
- [20181022]lob欄位的lobid來之那裡.txt
- 品味布隆過濾器的設計之美過濾器
- LevelDB 學習筆記1：布隆過濾器筆記過濾器
- 實現布隆過濾器的三種方式過濾器
- 面試官問：什麼是布隆過濾器？面試過濾器
- Redis快取穿透解決方案--布隆過濾器Redis快取穿透過濾器
- 詳解布隆過濾器的原理和實現過濾器
- 布隆過濾器解決快取穿透問題過濾器快取穿透
- 布隆過濾器實戰【防止快取擊穿】過濾器快取
- 那些有趣的演算法之布隆過濾器演算法過濾器
- 一文徹底弄清Redis的布隆過濾器Redis過濾器
- 布隆過濾器(BloomFilter)原理 實現和效能測試過濾器OOMFilter