[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;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2168917/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20190120]行連結遷移與dml.txt
- [20210331]Disk file operations IO與exadata.txt
- [20180402]行連結行遷移與ITL槽6.txt
- [20180725]exadata的hcc壓縮與dml更新.txt
- [20201007]exadata儲存索引.txt索引
- [20200111]淺談exadata oltp系統的優化.txt優化
- [20200228]如何判斷伺服器是exadata.txt伺服器
- [20231210]執行計劃與繫結變數.txt變數
- exadata與安迪比爾定理
- 【連結 1】與靜態連結庫連結
- Oracle 行遷移 & 行連結的檢測與消除Oracle
- [20180828]exadata--豆腐渣系統的保護神.txt
- [20180810]exadata--豆腐渣系統的保護神.txt
- 硬連結與軟連結詳解
- [20200218]連線串與專用模式.txt模式
- [20210126]bash ln建立軟連結問題.txt
- Oracle資料庫中資料行遷移與行連結Oracle資料庫
- 連結串列入門與插入連結串列
- 軟連結與硬連結的區別
- [20180930]in list與繫結變數.txt變數
- [20180912]PLSLQ與繫結變數.txt變數
- TXT文字Log日誌分割工具(附工具連結)
- 動態連結庫與靜態連結庫
- ln命令：軟連結與硬連結的區別與應用
- [20221018]本地執行與遠端執行.txt
- Oracle Exadata與SGA快取記憶體CQOracle快取記憶體
- Linux常用基本命令(軟連結與硬連結 )Linux
- [20190531]如何實現與執行.txt
- 連結串列與遞迴遞迴
- 陣列與連結串列陣列
- Exadata混合列壓縮功能與INSERT語句BF
- 深入學習之連結與總結
- 在連結與執行地址不同時gdb的除錯方法除錯
- [20190111]執行計劃走位與.txt
- [20180327]行遷移與ITL浪費.txt
- [20220414]toad與繫結變數peek.txt變數
- [20200326]繫結變數抓取與NULL值.txt變數Null
- [20210120]in list與繫結變數個數.txt變數