[20180730]exadata与行链接.txt

[20180730]exadata与行链接.txt --//最近一段时间在看<expert oracle exadata>,智能扫描的三大优化方法是:字段投影,谓词过滤,存储索引.大多数智能扫描 --//优化方法的主要目标就是在执行扫描时减少需要传输回数据块服务端的数据量. --//而智能扫描的先决条件,满足三个基本条件: --//1.必须要是对象上的全扫描. --//2.扫描必须要使用oracle直接路径读取机制. --//3.对象必须存储在exadata存储中. --//有几种情况转换为块传输模式: --//1.一致性读取, --//2,行链接(注意:不是行迁移) --//3.存储服务器CPU太忙. --//正好生产系统有1张大表存在大量行链接.自己测试看看. 1.环境: xxxx> @ &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 2.测试: xxxx> select bytes/1024/1024/1024 GB ,BLOCKS from dba_segments where owner='XXXX_YYY' and segment_name='BIG_TAB';         GB     BLOCKS ---------- ---------- 207.549805   27203968 --//表占用207G. xxxx> set timing on xxxx> @ &r/viewsess 'table fetch continued row' NAME                       STATISTIC#      VALUE        SID -------------------------- ---------- ---------- ---------- table fetch continued row         417          0       2323 Elapsed: 00:00:00.00 xxxx> select /*+ full(a) */ count(*) from xxxxxx_yyy.big_tab a;   COUNT(*) ----------   44669678 Elapsed: 00:01:38.73 xxxx> @ &r/viewsess 'table fetch continued row' NAME                      STATISTIC#      VALUE        SID ------------------------- ---------- ---------- ---------- table fetch continued row        417          0       2323 Elapsed: 00:00:00.01 --//需要大约98秒完成查询.table fetch continued row的记数没有变化. xxxx> @ &r/desc xxxxxx_yyy.big_tab; Name          Null?    Type ------------- -------- ---------------------------- YZBXH         NOT NULL NUMBER(18) BRID          NOT NULL NUMBER(18) ZYH           NOT NULL NUMBER(18) YEPB          NOT NULL NUMBER(1) BRKS          NOT NULL NUMBER(4) BRBQ                   NUMBER(4) BRCH                   VARCHAR2(20) YDYZLB        NOT NULL NUMBER(4) XMLB          NOT NULL NUMBER(8) ... CAKEY                  VARCHAR2(2000) YZCA                   VARCHAR2(3000) TZ_CAKEY               VARCHAR2(2000) TZCA                   VARCHAR2(3000) HSCAKEY                VARCHAR2(2000) HSCA                   VARCHAR2(3000) TZ_HSCAKEY             VARCHAR2(2000) TZ_HSCA                VARCHAR2(3000) DYSJ                   DATE YZZXSJ                 VARCHAR2(80) ZXTZSJ                 DATE ZYDY                   NUMBER(1) FZLJ                   NUMBER(8) BRZH                   NUMBER(8) TZBRZH                 NUMBER(8) TJSJ                   DATE TQMXBZ                 NUMBER(1) --//顺便找靠前的可以为NULL的字段BRBQ. 注意看那些XXkey的字段,正是这些字段导致了大量的行链接与行迁移. xxxx> select sysdate from dual; SYSDATE ------------------- 2018-07-30 08:49:54 Elapsed: 00:00:00.01 xxxx> select /*+ full(a) */ count(BRBQ) from xxxxxx_yyy.big_tab a; select /*+ full(a) */ count(BRBQ) from xxxxxx_yyy.big_tab a                                                   * ERROR at line 1: ORA-01013: user requested cancel of current operation Elapsed: 00:03:09.84 xxxx> select sysdate from dual; SYSDATE ------------------- 2018-07-30 08:53:37 Elapsed: 00:00:00.00 xxxx> @ &r/viewsess 'table fetch continued row' NAME                      STATISTIC#      VALUE        SID ------------------------- ---------- ---------- ---------- table fetch continued row        417      53476       2323 Elapsed: 00:00:00.00 --//时间太长仅仅测试不到4分钟,放弃!!(注上个星期测试4个小时也没有出来报ora-01555错误,当时选择靠后的字段).说明可以大量的行 --//链接导致无法充分发挥exadata智能扫描的优势,转为块传输模式. --//如果在服务器上观察可以发现: # perf top -k /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle    PerfTop:    5431 irqs/sec  kernel:51.3%  exact:  0.0% [1000Hz cycles],  (all, 24 CPUs) ----------------------------------------------------------------------------------------------------------              samples  pcnt function                DSO              _______ _____ _______________________ _______________________________________________________              1125.00  5.3% kafger                  /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle               763.00  3.6% qertbFetchByRowID       /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle                            ~~~~~~~~~~~~~~~~                 740.00  3.5% kafgex1                 /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle               740.00  3.5% kcbgtcr                 /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle               607.00  2.8% kdifxs1                 /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle               477.00  2.2% qerixtFetch             /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle               451.00  2.1% expepr                  /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle               386.00  1.8% kdsgrp                  /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle --//出现许多qertbFetchByRowID函数调用. 3.顺便测试exadata的存储索引: --//重新登录: xxxx> @ &r/viewsess 'storage' NAME                                                                   STATISTIC#      VALUE        SID ---------------------------------------------------------------------- ---------- ---------- ---------- cell physical IO bytes saved by storage index                                 274          0       4821 xxxx> @ &r/viewsess 'table fetch continued row' NAME                                                                   STATISTIC#      VALUE        SID ---------------------------------------------------------------------- ---------- ---------- ---------- table fetch continued row                                                     417          0       4821 xxxx> set timing on xxxx> select /*+ full(a) */ count(*) from xxxxxx_yyy.big_tab a where ZXTZSJ between trunc(sysdate)-1 and trunc(sysdate)-1+1/86400;   COUNT(*) ----------          5 Elapsed: 00:47:05.16 --//仅仅取1秒的日期范围数据,竟然需要47分钟. xxxx> @ &r/viewsess 'table fetch continued row' NAME                                                                   STATISTIC#      VALUE        SID ---------------------------------------------------------------------- ---------- ---------- ---------- table fetch continued row                                                     417    3461046       4821 Elapsed: 00:00:00.00 xxxx> @ &r/viewsess 'storage' NAME                                                                     STATISTIC#        VALUE          SID ---------------------------------------------------------------------- ------------ ------------ ------------ cell physical IO bytes saved by storage index                                   274  15562743808         4821 --//15562743808/1024/1024/1024 = 14.49393463134765625 --//15562743808/1024/1024 = 14841.7890625 --//15562743808/8192 = 1899749块 --//竟然存储索引可以使用.我以为会失效.也许前面的部分数据是使用expdp导入的(我当时记忆5XG),可能发生行链接或者迁移的量很少.估计开始 --//实施也不规范,XXkey的字段都是NULL.我估计仅仅没有发生行迁移或者链接的存储单元1M的数据才有存储索引.(我的理解) --//看看与cell相关的记数变化情况: xxxx> @ &r/viewsess 'cell' NAME                                                                     STATISTIC#        VALUE          SID ---------------------------------------------------------------------- ------------ ------------ ------------ cell writes to flash cache                                                       58            0         4821 cell overwrites in flash cache                                                   59            0         4821 cell partial writes in flash cache                                               60            0         4821 cell physical IO interconnect bytes                                              64  33219425400         4821 cell physical IO bytes saved during optimized file creation                     271            0         4821 cell physical IO bytes saved during optimized RMAN file restore                 272            0         4821 cell physical IO bytes eligible for predicate offload                           273 222633656320         4821 cell physical IO bytes saved by storage index                                   274  15562743808         4821 cell physical IO bytes sent directly to DB node to balance CPU                  275            0         4821 cell smart IO session cache lookups                                             276            0         4821 cell smart IO session cache hits                                                277            0         4821 cell smart IO session cache soft misses                                         278            0         4821 cell smart IO session cache hard misses                                         279            0         4821 cell smart IO session cache hwm                                                 280            0         4821 cell num smart IO sessions in rdbms block IO due to user                        281            0         4821 cell num smart IO sessions in rdbms block IO due to open fail                   282            0         4821 cell num smart IO sessions in rdbms block IO due to no cell mem                 283            0         4821 cell num smart IO sessions in rdbms block IO due to big payload                 284            0         4821 cell num smart IO sessions using passthru mode due to user                      285            0         4821 cell num smart IO sessions using passthru mode due to cellsrv                   286            0         4821 cell num smart IO sessions using passthru mode due to timezone                  287            0         4821 cell num smart file creation sessions using rdbms block IO mode                 288            0         4821 cell num block IOs due to a file instant restore in progress                    289            0         4821 cell physical IO interconnect bytes returned by smart scan                      290   6552450168         4821 cell num bytes in passthru during predicate offload                             291            0         4821 cell num bytes in block IO during predicate offload                             292            0         4821 cell num fast response sessions                                                 293            0         4821 cell num fast response sessions continuing to smart scan                        294            0         4821 cell num smartio automem buffer allocation attempts                             295            1         4821 cell num smartio automem buffer allocation failures                             296            0         4821 cell statistics spare1                                                          297            0         4821 cell statistics spare2                                                          298            0         4821 cell statistics spare3                                                          299            0         4821 cell statistics spare4                                                          300            0         4821 cell statistics spare5                                                          301            0         4821 cell statistics spare6                                                          302            0         4821 cell scans                                                                      421            1         4821 cell blocks processed by cache layer                                            422     25661755         4821 cell blocks processed by txn layer                                              423     25661095         4821 cell blocks processed by data layer                                             424     25282464         4821 cell blocks processed by index layer                                            425            0         4821 cell commit cache queries                                                       426            0         4821 cell transactions found in commit cache                                         427            0         4821 cell blocks helped by commit cache                                              428            0         4821 cell blocks helped by minscn optimization                                       429     25647939         4821 chained rows skipped by cell                                                    430      3467111         4821 chained rows processed by cell                                                  431      1116529         4821 chained rows rejected by cell                                                   432      3467111         4821 cell simulated physical IO bytes eligible for predicate offload                 433            0         4821 cell simulated physical IO bytes returned by predicate offload                  434            0         4821 cell CUs sent uncompressed                                                      435            0         4821 cell CUs sent compressed                                                        436            0         4821 cell CUs sent head piece                                                        437            0         4821 cell CUs processed for uncompressed                                             438            0         4821 cell CUs processed for compressed                                               439            0         4821 cell IO uncompressed bytes                                                      440 207119351808         4821 cell index scans                                                                457            0         4821 cell flash cache read hits                                                      646      3138149         4821 58 rows selected. --//对于许多记数不是很熟悉,仅仅做一个记录. --//附上viewsess.sql脚本: set verify off column name format a70 SELECT b.NAME, a.statistic#, a.VALUE,a.sid   FROM v$mystat a, v$statname b  WHERE lower(b.NAME) like lower('%&1%') AND a.statistic# = b.statistic# ;  --and a.value>0;