[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

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

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

2018-07-30 08:53:37

table fetch continued row 417 53476 2323

--//時間太長僅僅測試不到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

table fetch continued row 417 0 4821

xxxx> select /*+ full(a) */ count(*) from xxxxxx_yyy.big_tab a where ZXTZSJ between trunc(sysdate)-1 and trunc(sysdate)-1+1/86400;

Elapsed: 00:47:05.16

--//僅僅取1秒的日期範圍資料,竟然需要47分鐘.

table fetch continued row 417 3461046 4821

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'

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 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;