[20181031]lob字段与布隆过滤.txt

来源：这里教程网时间：2026-03-03 12:11:36 作者：

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

编辑推荐：

返回列表

[20181031]lob字段与布隆过滤.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推