[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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-2218012/
扫一扫
4385
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
