當(dāng)前位置：首頁(yè) > 编程资源 > 编程问答 >内容正文

编程问答

什么影响oracle io,Oracle IO问题解析

發(fā)布時(shí)間：2023/12/10 编程问答 21 豆豆

生活随笔收集整理的這篇文章主要介紹了什么影响oracle io,Oracle IO问题解析小編覺得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.

3.2 數(shù)據(jù)文件相關(guān)的IO事件

數(shù)據(jù)庫(kù)系統(tǒng)中的大多數(shù)的IO請(qǐng)求都是針對(duì)數(shù)據(jù)文件的。因此大多數(shù)情況下，與數(shù)據(jù)文件相關(guān)的IO事件是引起系統(tǒng)IO性能的主要原因。這些事件也是我們文章需要重點(diǎn)介紹的事件。下面分別針對(duì)不同事件介紹問(wèn)題的解決思路。

3.2.1 db file sequential read

這個(gè)事件是是最常見的IO等待事件。它一般發(fā)生在讀取單獨(dú)數(shù)據(jù)塊時(shí)，如讀取索引數(shù)據(jù)塊或者通過(guò)索引訪問(wèn)一個(gè)表數(shù)據(jù)塊，另外在讀取數(shù)據(jù)文件頭數(shù)據(jù)塊時(shí)也會(huì)發(fā)生db file sequential read等待事件。

當(dāng)發(fā)現(xiàn)這個(gè)等待事件成為系統(tǒng)等待事件中的主要事件，我們可以通過(guò)一下方法來(lái)處理：

3.2.1.1 優(yōu)化Top SQL

從statspack或者awr報(bào)告中的“SQL ordered by Reads”部分或者通過(guò)V$SQL視圖找出系統(tǒng)中的Top SQL，對(duì)SQL進(jìn)行調(diào)優(yōu)以減少IO請(qǐng)求。

當(dāng)SQL中存在Index Range Scan時(shí)，如果訪問(wèn)的索引的選擇性不好就會(huì)導(dǎo)致需要訪問(wèn)過(guò)多的數(shù)據(jù)塊，這時(shí)可以通過(guò)建立一個(gè)、或強(qiáng)制SQL使用一個(gè)已經(jīng)存在的選擇性更好的索引。這樣使我們?cè)L問(wèn)更少的數(shù)據(jù)塊來(lái)獲取到需要的數(shù)據(jù)。

SQL> select object_id, object_name

2 from t_test1

3 where owner = 'SYS'

4 and created > sysdate - 30;

no rows selected

Execution Plan

----------------------------------------------------------

Plan hash value: 4014220762

--------------------------------------------------------------------------------

| Time |

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 39 | 11 (0)

| 00:00:01 |

|* 1 | TABLE ACCESS BY INDEX ROWID| T_TEST1 | 1 | 39 | 11 (0)

| 00:00:01 |

|* 2 | INDEX RANGE SCAN | T_TEST1_IDX1 | 576 | | 1 (0)

| 00:00:01 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OWNER"='SYS' AND "CREATED">SYSDATE@!-30)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

658 consistent gets

45 physical reads

0 redo size

339 bytes sent via SQL*Net to client

374 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

0 rows processed

SQL> create index t_test1_idx2 on t_test1(owner, created);

Index created.

SQL> select object_id, object_name

2 from t_test1

3 where owner = 'SYS'

4 and created > sysdate - 30;

no rows selected

Execution Plan

----------------------------------------------------------

Plan hash value: 3417015015

---------------------------------------------------------------------------------

| Time |

---------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 49 | 1911 | 2 (0)

| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T_TEST1 | 49 | 1911 | 2 (0)

| 00:00:01 |

|* 2 | INDEX RANGE SCAN | T_TEST1_IDX2 | 49 | | 1 (0)

| 00:00:01 |

---------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OWNER"='SYS' AND "CREATED">SYSDATE@!-30)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

2 consistent gets

1 physical reads

0 redo size

339 bytes sent via SQL*Net to client

374 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

0 rows processed

如果索引存在碎片，那每個(gè)索引數(shù)據(jù)塊上的索引數(shù)據(jù)就更少，會(huì)導(dǎo)致我們需要訪問(wèn)更多的索引數(shù)據(jù)塊。這時(shí)，我們需要考慮重建索引來(lái)釋放碎片；

判斷一個(gè)所以是否需要重建，我們介紹一個(gè)簡(jiǎn)單的方法：對(duì)一個(gè)索引進(jìn)行結(jié)構(gòu)分析后，如果該索引占用超過(guò)了一個(gè)數(shù)據(jù)塊，且滿足以下條件之一：B-tree樹的高度大于3；使用百分比低于75%；數(shù)據(jù)刪除率大于15%，就需要考慮對(duì)索引重建：

SQL> analyze index t_test1_idx1 compute statistics;

Index analyzed.

SQL> analyze index t_test1_idx1 validate structure;

Index analyzed.

SQL> select btree_space, -- if > 8192(塊的大小)

2 height, -- if > 3

3 pct_used, -- if < 75

4 del_lf_rows/(decode(lf_rows,0,1,lf_rows)) *100 as deleted_pct -- if > 20%

5 from index_stats;

BTREE_SPACE HEIGHT PCT_USED DELETED_PCT

----------- ---------- ---------- -----------

880032 2 89 0

如果使用的索引的聚簇因子(Clustering Factor)很大，說(shuō)明一條索引記錄指向多個(gè)數(shù)據(jù)塊，在返回結(jié)果時(shí)需要讀取更多的數(shù)據(jù)塊。通過(guò)重建表可以降低聚簇因子，因而可以在查找索引時(shí)減少表數(shù)據(jù)塊的訪問(wèn)塊數(shù)。

聚簇因子說(shuō)明了表數(shù)據(jù)的物理存儲(chǔ)位置相對(duì)于一個(gè)索引的排序性的符合程度。例如，一個(gè)非唯一索引是建立在A字段上的，如果表數(shù)據(jù)的存儲(chǔ)是以A字段的順序存儲(chǔ)的，則索引與數(shù)據(jù)的關(guān)系如下圖：

A1 A2 A2 A2 A2 A2 A3

A3 A3 A3 A3 A3 A4 A4

… 表數(shù)據(jù)

索引結(jié)構(gòu)

此時(shí)，索引的聚簇因子很低，從圖上看到，假如我們需要獲取A=A2的數(shù)據(jù)，只需要讀取一個(gè)數(shù)據(jù)塊就可以了；

相反，如果表數(shù)據(jù)物理存儲(chǔ)順序和索引順序相差很大，就會(huì)出現(xiàn)下面的情況：

A1 A2 A3 A3

A1 A4 A2 A5

… 表數(shù)據(jù)

索引結(jié)構(gòu)

A4 A3 A2 A1

A2 A3 A3 A5

這時(shí)該索引的聚簇因子就很大，可以看到，如果需要獲取A=A2的數(shù)據(jù)，我們需要讀取4塊或更多的數(shù)據(jù)塊。

對(duì)索引進(jìn)行分析后，我們可以從視圖DBA_INDEXES中獲取到索引的聚簇因子，字段名為Clustoring_Factor。如果一個(gè)索引是一張表主要被使用的索引(或者是該表的唯一索引)，且它的聚簇因子過(guò)高導(dǎo)致IO請(qǐng)求過(guò)高的話，我們可以考慮采取以下措施來(lái)降低IO：

1) 以索引字段的順序重建表以降低聚簇因子，可以用以下語(yǔ)句重建表(當(dāng)然，你還需要重建觸發(fā)器、索引等對(duì)象，還可能需要重建、重新編譯有關(guān)聯(lián)對(duì)象)：

CREATE new_table AS SELECT * FROM old_table ORDER BY A;

2) 建立基于索引字段IOT(索引表)。

如果該索引不是表的主要索引，只是被少量語(yǔ)句引用到，按照以上方式處理的話反而可能會(huì)使其他使用更加頻繁的索引的聚簇因子增大，導(dǎo)致系統(tǒng)性能更差。這時(shí)我們可以建立包含返回字段的索引，以避免“TABLE ACCESS BY INDEX ROWID”。如以下例子：

SQL> set autot trace

SQL> select status from t_test1

2 where owner = 'DEMO';

576 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 4014220762

--------------------------------------------------------------------------------

| Time |

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 576 | 6336 | 11 (0)

| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T_TEST1 | 576 | 6336 | 11 (0)

| 00:00:01 |

|* 2 | INDEX RANGE SCAN | T_TEST1_IDX1 | 576 | | 1 (0)

| 00:00:01 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OWNER"='DEMO')

Statistics

----------------------------------------------------------

465 recursive calls

0 db block gets

222 consistent gets

43 physical reads

0 redo size

8368 bytes sent via SQL*Net to client

803 bytes received via SQL*Net from client

40 SQL*Net roundtrips to/from client

8 sorts (memory)

0 sorts (disk)

576 rows processed

SQL> create index t_test1_idx3 on t_test1(owner, status) compute statistics;

Index created.

SQL> select status from t_test1

2 where owner = 'DEMO';

576 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2736516725

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 576 | 6336 | 2 (0)| 00:00:01|

|* 1 | INDEX RANGE SCAN| T_TEST1_IDX3 | 576 | 6336 | 2 (0)| 00:00:01|

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("OWNER"='DEMO')

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

43 consistent gets

3 physical reads

0 redo size

8152 bytes sent via SQL*Net to client

803 bytes received via SQL*Net from client

40 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

576 rows processed

通過(guò)分區(qū)裁剪(partition pruning)技術(shù)來(lái)減少的SQL對(duì)數(shù)據(jù)塊的訪問(wèn)。

采用分區(qū)裁剪技術(shù)，Oracle優(yōu)化器會(huì)先分析FROM和WHERE字句，在建立訪問(wèn)分區(qū)列表時(shí)將那些不會(huì)被訪問(wèn)到的分區(qū)排除。例如，我們的表T_TEST1的owner字段的值有“SYS、SYSTEM、XDB、DEMO、TEST”，如果我們按照owner字段建立的是分區(qū)表：

CREATE TABLE t_test1

(object_id NUMBER(5),

object_name VARCHAR2(30),

owner VARCHAR2(20),

created DATE)

PARTITION BY LIST(owner)

(

PARTITION owner_sys VALUES('SYS', 'SYSTEM'),

PARTITION owner_xdb VALUES ('XDB'),

PARTITION owner_demo VALUES('DEMO'),

PARTITION owner_test VALUES('TEST'),

PARTITION owner_others VALUES(DEFAULT)

);

則對(duì)于以下語(yǔ)句：

select object_name

from t_test1

where owner in ('DEMO', 'TEST')

and created > sysdate - 30;

優(yōu)化器會(huì)先將分區(qū)owner_sys、owner_xdb、owner_others從分區(qū)訪問(wèn)列表中裁剪出去，只訪問(wèn)分區(qū)owner_demo和owner_test上的數(shù)據(jù)或者通過(guò)這兩個(gè)分區(qū)上的索引來(lái)訪問(wèn)數(shù)據(jù)。

3.2.1.2 處理非SQL導(dǎo)致的IO問(wèn)題

如果從statspack或者AWR報(bào)告中找不到明顯產(chǎn)生db file sequential read事件的SQL，則該等待事件可能是由于以下原因?qū)е碌?#xff1a;

熱點(diǎn)數(shù)據(jù)文件或磁盤

數(shù)據(jù)文件所在的磁盤IO負(fù)荷過(guò)重導(dǎo)致對(duì)IO請(qǐng)求反映慢，這時(shí)，我們可以通過(guò)statspack或AWR報(bào)告中的“File I/O Statistics”部分(或者通過(guò)V$FILESTAT視圖)來(lái)找到熱點(diǎn)磁盤:

Statspack report：

Tablespace Filename

------------------------ ----------------------------------------------------

Av Av Av Av Buffer Av Buf

Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)

-------------- ------- ------ ------- ------------ -------- ---------- ------

AFW_DATA /export/home/icssprd/data/data17/icssprd_afw_data_01

726 0 4.3 1.0 381 0 0

AFW_INDX /export/home/icssprd/data/data18/icssprd_afw_indx_01

1,741 0 6.3 1.0 2,104 0 0

CSS_AN_DATA /export/home/icssprd/data/data03/icssprd_css_an_data

200,649 5 1.8 3.2 24,192 1 0

/export/home/icssprd/data/data04/icssprd_css_an_data

242,462 6 1.6 3.1 26,985 1 3 6.7

CSS_AN_INDX /export/home/icssprd/data/data13/icssprd_css_an_indx

70,789 2 5.0 1.6 5,330 0 0

CSS_AUDIT_RESOURCES_DATA /export/home/icssprd/data/data10/icssprd_css_audit_r

2,394 0 0.6 1.0 1,781 0 0

CSS_AUDIT_RESOURCES_INDX /export/home/icssprd/data/data11/icssprd_css_audit_r

248 0 4.3 1.0 52 0 0

... ...

視圖：

SQL> select b.name, phyrds, phywrts

2 from V$FILESTAT a, V$DATAFILE b

3 where a.file# = b.file#;

NAME

--------------------------------------------------------------------------------

PHYRDS PHYWRTS

---------- ----------

C:\ORACLE\PRODUCT\10.2.0\ORADATA\EDGAR\DATAFILE\O1_MF_SYSTEM_20TFOB4Q_.DBF

132767 11565

C:\ORACLE\PRODUCT\10.2.0\ORADATA\EDGAR\DATAFILE\O1_MF_UNDOTBS1_20TFQP78_.DBF

1943 19924

C:\ORACLE\PRODUCT\10.2.0\ORADATA\EDGAR\DATAFILE\O1_MF_SYSAUX_20TFSGC6_.DBF

659458 100811

... ...

找到熱點(diǎn)數(shù)據(jù)文件(磁盤)后，我們可以考慮將數(shù)據(jù)文件轉(zhuǎn)移到性能更高的存儲(chǔ)設(shè)備上去，或者利用我們上述說(shuō)的條帶化、RAID等存儲(chǔ)技術(shù)來(lái)均衡IO負(fù)荷。

熱點(diǎn)數(shù)據(jù)段

從Oracle9.2開始，出現(xiàn)了數(shù)據(jù)段的概念。每個(gè)表和索引都存儲(chǔ)在自己的數(shù)據(jù)段中。我們可以通過(guò)視圖V$SEGMENT_STATISTICS查找物理讀最多的段來(lái)找到熱點(diǎn)數(shù)據(jù)段。通過(guò)對(duì)熱點(diǎn)段的分析，考慮采用重建索引、分區(qū)表等方式來(lái)降低該數(shù)據(jù)段上的IO負(fù)荷。

SQL> select owner, object_name, tablespace_name, object_type, value

2 from V$SEGMENT_STATISTICS

3 where statistic_name = 'physical reads'

4 order by value desc;

OWNER OBJECT_NAME

------------------------------ ------------------------------

TABLESPACE_NAME OBJECT_TYPE VALUE

------------------------------ ------------------ ----------

SYS CONTEXT$

SYSTEM TABLE 71

SYS I_CONTEXT

SYSTEM INDEX 70

... ...

另外，我們還可以根據(jù)視圖v$session_wait中的P1(熱點(diǎn)段所在的數(shù)據(jù)文件號(hào))、P2(發(fā)生db file sequential read事件的起始數(shù)據(jù)塊)、P3(數(shù)據(jù)塊的數(shù)量，db file sequential read讀取數(shù)據(jù)塊數(shù)量為1)來(lái)定位出熱點(diǎn)段：

先找出文件號(hào)、起始數(shù)據(jù)塊、數(shù)據(jù)塊數(shù)量：

SQL> select p1 "fileid", p2 "block_id", p3 "block_num"

2 from v$session_wait

3 where event = 'db file sequential read';

fileid block_id block_num

---------- ---------- ----------

396 44869 1

然后根據(jù)找出的文件號(hào)、起始數(shù)據(jù)塊、數(shù)據(jù)塊數(shù)量來(lái)定位出數(shù)據(jù)段：

SQL> select

2 segment_name "Segment Name",

3 segment_type "Segment Type",

4 block_id "First Block of Segment",

5 block_id+blocks "Last Block of Segment"

6 from dba_extents

7 where &fileid = file_id

8 and &block_id >= block_id

9 and &block_id <= block_id+blocks;

Enter value for fileid: 396

old 7: where &fileid = file_id

new 7: where 396 = file_id

Enter value for block_id: 44869

old 8: and &block_id >= block_id

new 8: and 44869 >= block_id

Enter value for block_id: 44869

old 9: and &block_id <= block_id+blocks

new 9: and 44869 <= block_id+blocks

Segment Name

--------------------------------------------------------------------------------

Segment Type First Block of Segment Last Block of Segment

------------------ ---------------------- ---------------------

CSS_TP_SHMT_QUEUE_ACTIVITY

TABLE 44841 44873

3.2.1.3 調(diào)整Buffer Cache

如果系統(tǒng)中即不存在性能有問(wèn)題的SQL語(yǔ)句，而且所有磁盤的IO負(fù)載也比較均衡(不存在熱地磁盤)，則我們需要考慮增加Buffer Cache來(lái)降低磁盤IO請(qǐng)求。

在8i，主要是根據(jù)緩存命中率(Buffer Cache Hit Ratio)來(lái)調(diào)整buffer cache。當(dāng)Buffer Cache調(diào)整到一定大小，對(duì)命中率沒什么影響了時(shí)，就沒有必要在增大Buffer Cache了。可以通過(guò)以下語(yǔ)句來(lái)查看Buffer Cache命中率：

SQL> select 1-(physical_reads)/(consistent_gets+db_block_gets)

2 from v$buffer_pool_statistics;

1-(PHYSICAL_READS)/(CONSISTENT_GETS+DB_BLOCK_GETS)

--------------------------------------------------

.95628981

在9i中，可以利用statspack report中的Buffer Cache建議部分來(lái)調(diào)整Buffer Cache的大小。

Buffer Pool Advisory for DB: ICSSPRD Instance: icssprd End Snap: 259

-> Only rows with estimated physical reads >0 are displayed

-> ordered by Block Size, Buffers For Estimate

Size for Size Buffers for Est Physical Estimated

P Estimate (M) Factr Estimate Read Factor Physical Reads

--- ------------ ----- ---------------- ------------- ------------------

D 304 .1 37,715 9.18 5,928,235,496

D 608 .2 75,430 6.88 4,443,709,043

D 912 .3 113,145 5.73 3,699,496,220

D 1,216 .4 150,860 3.87 2,502,670,372

D 1,520 .5 188,575 2.32 1,499,049,228

D 1,824 .6 226,290 1.70 1,099,326,418

D 2,128 .7 264,005 1.41 912,042,579

D 2,432 .8 301,720 1.22 790,925,174

D 2,736 .9 339,435 1.09 703,357,378

D 2,992 1.0 371,195 1.00 645,905,997

D 3,040 1.0 377,150 0.99 636,992,420

D 3,344 1.1 414,865 0.90 583,996,250

D 3,648 1.2 452,580 0.84 542,063,246

D 3,952 1.3 490,295 0.79 508,261,496

D 4,256 1.4 528,010 0.74 480,472,150

D 4,560 1.5 565,725 0.71 455,533,563

D 4,864 1.6 603,440 0.67 434,743,759

D 5,168 1.7 641,155 0.64 416,285,837

D 5,472 1.8 678,870 0.62 400,208,242

D 5,776 1.9 716,585 0.60 385,785,401

D 6,080 2.0 754,300 0.57 365,597,932

-------------------------------------------------------------

這里，Est Physical Read Factor是估算的從磁盤物理讀取次數(shù)與從buffer cache中讀取的次數(shù)的比值。從意見估算的圖表中，當(dāng)Buffer Cache的增長(zhǎng)對(duì)該因子影響不大時(shí)，則說(shuō)明無(wú)需在增大Buffer Cache，我們就可以去相應(yīng)臨界點(diǎn)的大小作為Buffer Cache的大小。上述例子中，我們可以考慮設(shè)置Buffer Cache大小為2992M。

在Oracle10g中，引入了新的內(nèi)存管理特性——自動(dòng)共享內(nèi)存管理(Automatic Shared Memory Management ASMM)?；谶@一特性，oracle能夠自動(dòng)根據(jù)當(dāng)前的負(fù)荷計(jì)算出最優(yōu)的Buffer Cache大小。關(guān)于ASMM，可以參見文章《Oracle內(nèi)存全面分析》的SGA_TARGET部分。

我們可以采用多尺寸緩沖池技術(shù)將熱點(diǎn)數(shù)據(jù)段(表或索引)KEEP在緩沖池中：

SQL> alter table t_test1 storage(buffer_pool keep);

Table altered.

關(guān)于多尺寸緩沖的更多內(nèi)容，可以參考文章《Oracle內(nèi)存全面分析》的“多緩沖池部分”部分。

3.2.1.4 Housekeep歷史數(shù)據(jù)

對(duì)于一些被頻繁訪問(wèn)到的大表，我們需要定期對(duì)其做housekeep，將一些不用的、老的數(shù)據(jù)從表中移除，以減少訪問(wèn)的數(shù)據(jù)塊。定期對(duì)含有時(shí)間軸的Transaction表做housekeep是降低IO負(fù)載的重要措施。

總結(jié)

以上是生活随笔為你收集整理的什么影响oracle io,Oracle IO问题解析的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。

ORACLE
io

上一篇：广发银泰联名信用卡积分规则及用途
下一篇：广发银泰信用卡怎么提额？提额失败怎么办