导视设计师,旧版优化大师,wordpress 网站排名优化,is_page wordpress3.2 数据文件相关的IO事件数据库系统中的大多数的IO请求都是针对数据文件的。因此大多数情况下#xff0c;与数据文件相关的IO事件是引起系统IO性能的主要原因。这些事件也是我们文章需要重点介绍的事件。下面分别针对不同事件介绍问题的解决思路。3.2.1 db file sequential r…3.2 数据文件相关的IO事件数据库系统中的大多数的IO请求都是针对数据文件的。因此大多数情况下与数据文件相关的IO事件是引起系统IO性能的主要原因。这些事件也是我们文章需要重点介绍的事件。下面分别针对不同事件介绍问题的解决思路。3.2.1 db file sequential read这个事件是是最常见的IO等待事件。它一般发生在读取单独数据块时如读取索引数据块或者通过索引访问一个表数据块另外在读取数据文件头数据块时也会发生db file sequential read等待事件。当发现这个等待事件成为系统等待事件中的主要事件我们可以通过一下方法来处理3.2.1.1 优化Top SQL从statspack或者awr报告中的“SQL ordered by Reads”部分或者通过V$SQL视图找出系统中的Top SQL对SQL进行调优以减少IO请求。当SQL中存在Index Range Scan时如果访问的索引的选择性不好就会导致需要访问过多的数据块这时可以通过建立一个、或强制SQL使用一个已经存在的选择性更好的索引。这样使我们访问更少的数据块来获取到需要的数据。SQL select object_id, object_name2 from t_test13 where owner SYS4 and created sysdate - 30;no rows selectedExecution Plan----------------------------------------------------------Plan hash value: 4014220762--------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| 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(OWNERSYS AND CREATEDSYSDATE!-30)Statistics----------------------------------------------------------0 recursive calls0 db block gets658 consistent gets45 physical reads0 redo size339 bytes sent via SQL*Net to client374 bytes received via SQL*Net from client1 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)0 rows processedSQL create index t_test1_idx2 on t_test1(owner, created);Index created.SQL select object_id, object_name2 from t_test13 where owner SYS4 and created sysdate - 30;no rows selectedExecution Plan----------------------------------------------------------Plan hash value: 3417015015---------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| 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(OWNERSYS AND CREATEDSYSDATE!-30)Statistics----------------------------------------------------------1 recursive calls0 db block gets2 consistent gets1 physical reads0 redo size339 bytes sent via SQL*Net to client374 bytes received via SQL*Net from client1 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)0 rows processed如果索引存在碎片那每个索引数据块上的索引数据就更少会导致我们需要访问更多的索引数据块。这时我们需要考虑重建索引来释放碎片判断一个所以是否需要重建我们介绍一个简单的方法对一个索引进行结构分析后如果该索引占用超过了一个数据块且满足以下条件之一B-tree树的高度大于3使用百分比低于75%数据删除率大于15%就需要考虑对索引重建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 33 pct_used, -- if 754 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)很大说明一条索引记录指向多个数据块在返回结果时需要读取更多的数据块。通过重建表可以降低聚簇因子因而可以在查找索引时减少表数据块的访问块数。聚簇因子说明了表数据的物理存储位置相对于一个索引的排序性的符合程度。例如一个非唯一索引是建立在A字段上的如果表数据的存储是以A字段的顺序存储的则索引与数据的关系如下图RB1B2B3A3A4A5A1 A2 A2 A2 A2 A2 A3A3 A3 A3 A3 A3 A4 A4A2A1… 表数据索引结构此时索引的聚簇因子很低从图上看到假如我们需要获取AA2的数据只需要读取一个数据块就可以了相反如果表数据物理存储顺序和索引顺序相差很大就会出现下面的情况RB1B2B3A3A4A5A1 A2 A3 A3A1 A4 A2 A5A2A1… 表数据索引结构A4 A3 A2 A1A2 A3 A3 A5这时该索引的聚簇因子就很大可以看到如果需要获取AA2的数据我们需要读取4块或更多的数据块。对索引进行分析后我们可以从视图DBA_INDEXES中获取到索引的聚簇因子字段名为Clustoring_Factor。如果一个索引是一张表主要被使用的索引(或者是该表的唯一索引)且它的聚簇因子过高导致IO请求过高的话我们可以考虑采取以下措施来降低IO1) 以索引字段的顺序重建表以降低聚簇因子可以用以下语句重建表(当然你还需要重建触发器、索引等对象还可能需要重建、重新编译有关联对象)CREATE new_table AS SELECT * FROM old_table ORDER BY A;2) 建立基于索引字段IOT(索引表)。如果该索引不是表的主要索引只是被少量语句引用到按照以上方式处理的话反而可能会使其他使用更加频繁的索引的聚簇因子增大导致系统性能更差。这时我们可以建立包含返回字段的索引以避免“TABLE ACCESS BY INDEX ROWID”。如以下例子SQL set autot traceSQL select status from t_test12 where owner DEMO;576 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 4014220762--------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| 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(OWNERDEMO)Statistics----------------------------------------------------------465 recursive calls0 db block gets222 consistent gets43 physical reads0 redo size8368 bytes sent via SQL*Net to client803 bytes received via SQL*Net from client40 SQL*Net roundtrips to/from client8 sorts (memory)0 sorts (disk)576 rows processedSQL create index t_test1_idx3 on t_test1(owner, status) compute statistics;Index created.SQL select status from t_test12 where owner DEMO;576 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 2736516725--------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|--------------------------------------------------------------------------------| 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(OWNERDEMO)Statistics----------------------------------------------------------1 recursive calls0 db block gets43 consistent gets3 physical reads0 redo size8152 bytes sent via SQL*Net to client803 bytes received via SQL*Net from client40 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)576 rows processed通过分区裁剪(partition pruning)技术来减少的SQL对数据块的访问。采用分区裁剪技术Oracle优化器会先分析FROM和WHERE字句在建立访问分区列表时将那些不会被访问到的分区排除。例如我们的表T_TEST1的owner字段的值有“SYS、SYSTEM、XDB、DEMO、TEST”如果我们按照owner字段建立的是分区表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));则对于以下语句select object_namefrom t_test1where owner in (DEMO, TEST)and created sysdate - 30;优化器会先将分区owner_sys、owner_xdb、owner_others从分区访问列表中裁剪出去只访问分区owner_demo和owner_test上的数据或者通过这两个分区上的索引来访问数据。3.2.1.2 处理非SQL导致的IO问题如果从statspack或者AWR报告中找不到明显产生db file sequential read事件的SQL则该等待事件可能是由于以下原因导致的热点数据文件或磁盘数据文件所在的磁盘IO负荷过重导致对IO请求反映慢这时我们可以通过statspack或AWR报告中的“File I/O Statistics”部分(或者通过V$FILESTAT视图)来找到热点磁盘:Statspack reportTablespace Filename------------------------ ----------------------------------------------------Av Av Av Av Buffer Av BufReads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)-------------- ------- ------ ------- ------------ -------- ---------- ------AFW_DATA /export/home/icssprd/data/data17/icssprd_afw_data_01726 0 4.3 1.0 381 0 0AFW_INDX /export/home/icssprd/data/data18/icssprd_afw_indx_011,741 0 6.3 1.0 2,104 0 0CSS_AN_DATA /export/home/icssprd/data/data03/icssprd_css_an_data200,649 5 1.8 3.2 24,192 1 0/export/home/icssprd/data/data04/icssprd_css_an_data242,462 6 1.6 3.1 26,985 1 3 6.7CSS_AN_INDX /export/home/icssprd/data/data13/icssprd_css_an_indx70,789 2 5.0 1.6 5,330 0 0CSS_AUDIT_RESOURCES_DATA /export/home/icssprd/data/data10/icssprd_css_audit_r2,394 0 0.6 1.0 1,781 0 0CSS_AUDIT_RESOURCES_INDX /export/home/icssprd/data/data11/icssprd_css_audit_r248 0 4.3 1.0 52 0 0... ...视图SQL select b.name, phyrds, phywrts2 from V$FILESTAT a, V$DATAFILE b3 where a.file# b.file#;NAME--------------------------------------------------------------------------------PHYRDS PHYWRTS---------- ----------C:\ORACLE\PRODUCT\10.2.0\ORADATA\EDGAR\DATAFILE\O1_MF_SYSTEM_20TFOB4Q_.DBF132767 11565C:\ORACLE\PRODUCT\10.2.0\ORADATA\EDGAR\DATAFILE\O1_MF_UNDOTBS1_20TFQP78_.DBF1943 19924C:\ORACLE\PRODUCT\10.2.0\ORADATA\EDGAR\DATAFILE\O1_MF_SYSAUX_20TFSGC6_.DBF659458 100811... ...找到热点数据文件(磁盘)后我们可以考虑将数据文件转移到性能更高的存储设备上去或者利用我们上述说的条带化、RAID等存储技术来均衡IO负荷。热点数据段从Oracle9.2开始出现了数据段的概念。每个表和索引都存储在自己的数据段中。我们可以通过视图V$SEGMENT_STATISTICS查找物理读最多的段来找到热点数据段。通过对热点段的分析考虑采用重建索引、分区表等方式来降低该数据段上的IO负荷。SQL select owner, object_name, tablespace_name, object_type, value2 from V$SEGMENT_STATISTICS3 where statistic_name physical reads4 order by value desc;OWNER OBJECT_NAME------------------------------ ------------------------------TABLESPACE_NAME OBJECT_TYPE VALUE------------------------------ ------------------ ----------SYS CONTEXT$SYSTEM TABLE 71SYS I_CONTEXTSYSTEM INDEX 70... ...另外我们还可以根据视图v$session_wait中的P1(热点段所在的数据文件号)、P2(发生db file sequential read事件的起始数据块)、P3(数据块的数量db file sequential read读取数据块数量为1)来定位出热点段先找出文件号、起始数据块、数据块数量SQL select p1 fileid, p2 block_id, p3 block_num2 from v$session_wait3 where event db file sequential read;fileid block_id block_num---------- ---------- ----------396 44869 1然后根据找出的文件号、起始数据块、数据块数量来定位出数据段SQL select2 segment_name Segment Name,3 segment_type Segment Type,4 block_id First Block of Segment,5 block_idblocks Last Block of Segment6 from dba_extents7 where fileid file_id8 and block_id block_id9 and block_id block_idblocks;Enter value for fileid: 396old 7: where fileid file_idnew 7: where 396 file_idEnter value for block_id: 44869old 8: and block_id block_idnew 8: and 44869 block_idEnter value for block_id: 44869old 9: and block_id block_idblocksnew 9: and 44869 block_idblocksSegment Name--------------------------------------------------------------------------------Segment Type First Block of Segment Last Block of Segment------------------ ---------------------- ---------------------CSS_TP_SHMT_QUEUE_ACTIVITYTABLE 44841 448733.2.1.3 调整Buffer Cache如果系统中即不存在性能有问题的SQL语句而且所有磁盘的IO负载也比较均衡(不存在热地磁盘)则我们需要考虑增加Buffer Cache来降低磁盘IO请求。在8i主要是根据缓存命中率(Buffer Cache Hit Ratio)来调整buffer cache。当Buffer Cache调整到一定大小对命中率没什么影响了时就没有必要在增大Buffer Cache了。可以通过以下语句来查看Buffer Cache命中率SQL select 1-(physical_reads)/(consistent_getsdb_block_gets)2 from v$buffer_pool_statistics;1-(PHYSICAL_READS)/(CONSISTENT_GETSDB_BLOCK_GETS)--------------------------------------------------.95628981在9i中可以利用statspack report中的Buffer Cache建议部分来调整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 EstimateSize for Size Buffers for Est Physical EstimatedP Estimate (M) Factr Estimate Read Factor Physical Reads--- ------------ ----- ---------------- ------------- ------------------D 304 .1 37,715 9.18 5,928,235,496D 608 .2 75,430 6.88 4,443,709,043D 912 .3 113,145 5.73 3,699,496,220D 1,216 .4 150,860 3.87 2,502,670,372D 1,520 .5 188,575 2.32 1,499,049,228D 1,824 .6 226,290 1.70 1,099,326,418D 2,128 .7 264,005 1.41 912,042,579D 2,432 .8 301,720 1.22 790,925,174D 2,736 .9 339,435 1.09 703,357,378D 2,992 1.0 371,195 1.00 645,905,997D 3,040 1.0 377,150 0.99 636,992,420D 3,344 1.1 414,865 0.90 583,996,250D 3,648 1.2 452,580 0.84 542,063,246D 3,952 1.3 490,295 0.79 508,261,496D 4,256 1.4 528,010 0.74 480,472,150D 4,560 1.5 565,725 0.71 455,533,563D 4,864 1.6 603,440 0.67 434,743,759D 5,168 1.7 641,155 0.64 416,285,837D 5,472 1.8 678,870 0.62 400,208,242D 5,776 1.9 716,585 0.60 385,785,401D 6,080 2.0 754,300 0.57 365,597,932-------------------------------------------------------------这里Est Physical Read Factor是估算的从磁盘物理读取次数与从buffer cache中读取的次数的比值。从意见估算的图表中当Buffer Cache的增长对该因子影响不大时则说明无需在增大Buffer Cache我们就可以去相应临界点的大小作为Buffer Cache的大小。上述例子中我们可以考虑设置Buffer Cache大小为2992M。在Oracle10g中引入了新的内存管理特性——自动共享内存管理(Automatic Shared Memory Management ASMM)。基于这一特性oracle能够自动根据当前的负荷计算出最优的Buffer Cache大小。关于ASMM可以参见文章《Oracle内存全面分析》的SGA_TARGET部分。我们可以采用多尺寸缓冲池技术将热点数据段(表或索引)KEEP在缓冲池中SQL alter table t_test1 storage(buffer_pool keep);Table altered.关于多尺寸缓冲的更多内容可以参考文章《Oracle内存全面分析》的“多缓冲池部分”部分。3.2.1.4 Housekeep历史数据对于一些被频繁访问到的大表我们需要定期对其做housekeep将一些不用的、老的数据从表中移除以减少访问的数据块。定期对含有时间轴的Transaction表做housekeep是降低IO负载的重要措施。
