OracleIO性能调优手册.docx

OracleIO性能调优手册.docx

《OracleIO性能调优手册.docx》由会员分享，可在线阅读，更多相关《OracleIO性能调优手册.docx（54页珍藏版）》请在冰豆网上搜索。

OracleIO性能调优手册

OracleI/O性能调优手册第-1-页，共-54-页Welcometocuishen’sITBlog:

OracleI/O性能调优手册

OracleI/O性能调优手册第-2-页，共-54-页Welcometocuishen’sITBlog:

数据库的作用就是实现对数据的管理和查询。

任何一个数据库系统，必然存在对数据的大量读或者写或者两种操作都大量存在。

I/O问题也往往是导致数据库性能问题的重要原因。

在这篇文章中，主要帮助大家在理解Oracle的读写操作机制的基础上，灵活解决遇到的各种常见的I/O问题。

1.Oracle结构

（图1）Oracle结构OracleI/O性能调优手册第-3-页，共-54-页Welcometocuishen’sITBlog:

1.1.Oracle实例（Instance）

（图1.1）Oracle实例一个Oracle实例:

A．是访问Oracledatabase的途径B．只能打开一个数据库C．由SGA内存区和一组后台进程组成

1.2.数据库文件

（图1.2）数据库文件OracleI/O性能调优手册第-4-页，共-54-页Welcometocuishen’sITBlog:

Oracle数据库的物理结构是硬盘上的一组文件。

主要有三类文件：

即数据文件,日志文件和控制文件。

◆数据文件包含数据库的实际数据，数据存于用户定义的表中，此外数据字典数据、回滚数据、索引等均存于数据文件中。

◆日志文件记录对数据库的所有修改，用于数据库的恢复。

◆控制文件记录日志文件和数据文件的信息，用于保证数据库文件的一致性和完整性。

归档日志文件是在线日志的拷贝，在归档模式下在线日志在重复使用之前必须归档。

2.Oracle中I/O的产生

I/O包括了读、写两部分，先介绍Oracle中写操作的产生。

2.1.写

介绍写操作之前，先简单的看下Oracle的物理结构：

Oracle的物理文件包括以下三种文件：

控制文件（ControlFiles）、重做日志文件（RedoLogFiles）、数据文件（datafiles）。

而数据文件中，根据功能的不同，还可以分为：

系统数据文件、用户数据文件、临时空间文件和回滚段文件。

另外，如果数据库的ArchiveLog模式被激活，还存在归档日志文件。

Oracle的I/O产生，就是对这些文件的数据读、写操作。

下面再详细看下几种主要写操作的产生及其过程。

2.1.1.控制文件

控制文件中记录了整个数据库的物理结构信息。

同时控制文件还记录系统和各个数据文件的SCN（SystemChangeNumber，关于SCN可以参见文章《OracleSCN机制详解》）信息，以用于数据恢复，因此数据文件上的SCN变化后，Oracle也会相应修改控制文件上的SCN信息。

2.1.2.RedoLog

在非直接写（DirectWrite）的情况下，事务中的写操作都会产生RedoLog，作为数据块异常关闭时的恢复记录。

同样，和写用户数据类似，RedoLog也不会被直接写入RedoLog文件，而是先写入LogBuffer中。

LogBuffer是一个可以循环重用的缓存区。

LGWR进程负责将LogBuffer中的记录写入RedoLogFile中去。

一旦LogBuffer中的条目被写入了RedoLog文件中，就可以被重用了。

为了保证事务尽快获得LogBuffer，LGWR进程一般会尽快将LogBuffer中的数据写入RedoLog文件中去。

在以下几种情况下，LGWR会将一个连续的LogBuffer写入RedoLog文件中去：

A．当一个事务提交（COMMIT）时。

B．每3秒钟写一次LogBuffer。

C．当LogBuffer满1/3时。

D．当DBWn进程将“脏”数据写入磁盘时。

OracleI/O性能调优手册第-5-页，共-54-页Welcometocuishen’sITBlog:

2.1.3.用户数据文件

由于内存的读写效率比磁盘的读写效率高万倍，因此，为了降低I/Owait，Oracle会将数据cache在内存（BufferCache，对BufferCache的详细介绍可以参见《Oracle内存全面分析》）中，对数据的读写尽量在内存中完成。

当BufferCache中的数据缓存块被修改过了，它就被标记为“脏”数据。

根据LRU（LeastRecentlyUsed）算法，如果一个数据块最近很少被使用，它就称为“冷”数据块。

进程DBWn（系统中可以存在多个DBW进程，n为序号）负责将“冷”的“脏”数据写入数据文件中去。

DBWn进程会在以下两种情况下将“脏”数据写入磁盘中去：

A.当服务进程扫描一定数量（阀值）的BufferCache后还没有找到干净、可重用的缓存块后，它会通知DBWn进程将“脏”数据写入文件中去，以释放出空闲缓存。

B.当发生检查点（Checkpoint）时。

检查点的作用：

Ø将数据缓存中所有改变的数据块写入数据文件（不管事物是否提交）

Ø将数据文件的头部标为一致，并且在控制文件当中记录检查点序号。

Ø该检查点号与日志文件中SCN号对应。

Ø检查点操作的频繁程度可由数据库参数调节

2.1.4.临时表空间

当Oracle在执行一些SQL时，会需要一些临时空间来存储执行时产生的中间数据。

这些临时空间由Oracle从指定的临时表空间中分配给进程。

主要有三种情况会占用临时空间：

临时表/索引操作、排序和临时LOB对象操作。

A．临时表/索引：

在会话中，当第一次对临时表进行INSERT（包括CTAS）时，Oracle会从临时表空间中为临时表及其索引分配临时空间一存储数据。

B．排序：

任何会使用到排序的操作，包括JOIN、创建（重建）INDEX、ORDERBY、聚合计算（GROUPBY）以及统计数据收集，都可能使用到临时表空间。

排序操作首先会选择在内存中的SortArea进行（SortInMemory），一旦SortArea不足，则会使用临时空间进行排序操作（SortInDisk）。

看下面的例子：

SQL>altersessionsetsort_area_size=10000000;Sessionaltered.SQL>selectowner,object_namefromt_test1orderbyobject_id;47582rowsselected.ExecutionPlanPlanhashvalue:

1312425564Id|Operation|Name|Rows|Bytes|Cost（%CPU）|Time||0|SELECTSTATEMENT||47582|1486K|155（4）|00:

00:

02||1|SORTORDERBY||47582|1486K|155（4）|00:

00:

02||2|TABLEACCESSFULL|T_TEST1|47582|1486K|150

（1）|00:

00:

02|Statistics1recursivecalls0dbblockgets658consistentgetsOracleI/O性能调优手册第-6-页，共-54-页Welcometocuishen’sITBlog:

0physicalreads0redosize1566184bytessentviaSQL*Nettoclient35277bytesreceivedviaSQL*Netfromclient3174SQL*Netroundtripsto/fromclient1sorts（memory）0sorts（disk）47582rowsprocessedSQL>altersessionsetsort_area_size=10000;Sessionaltered.SQL>selectowner,object_namefromt_test1orderbyobject_id;47582rowsselected.ExecutionPlanPlanhashvalue:

1312425564|Id|Operation|Name|Rows|Bytes|TempSpc|Cost（%CPU）|Time||0|SELECTSTATEMENT||47582|1486K||1251

（1）|00:

00:

16||1|SORTORDERBY||47582|1486K|4136K|1251

（1）|00:

00:

16||2|TABLEACCESSFULL|T_TEST1|47582|1486K||150

（1）|00:

00:

02|Statistics6recursivecalls20dbblockgets658consistentgets629physicalreads0redosize1566184bytessentviaSQL*Nettoclient35277bytesreceivedviaSQL*Netfromclient3174SQL*Netroundtripsto/fromclient0sorts（memory）1sorts（disk）47582rowsprocessedC．临时LOB对象：

LOB对象包括BLOB、CLOB、NCLOB、和BFILE。

在PLSQL程序块中，如果定义了LOB变量，则这些LOB变量就是临时LOB对象。

临时LOB对象被创建在临时表空间上，直到LOB数据被释放，或者会话结束。

2.1.5.回滚段

我们知道，一个事务在未被提交前，其做的任何修改都是可以被回滚（Rollback）的。

这些回滚数据就被放到回滚段（RollbackSegment）上。

此外，一致性读（ReadConsistency）、数据库恢复（Recover）都会用到回滚段。

任何数据块的修改都会被记录在回滚段中，甚至RedoLog也会产生回滚记录。

当任何一个非只读（只有OracleI/O性能调优手册第-7-页，共-54-页Welcometocuishen’sITBlog:

查询）的事务开始时，oracle会自动为其指定下一个可用的回滚段。

事务中任何数据变化都被写入回滚段中。

如果事务回滚，Oracle根据回滚段中的回滚记录将buffercache中的“脏”数据恢复，释放回滚段空间。

当事务被提交，由于要保证一致性读，Oracle并不会立即释放回滚段中的数据，而是会保留一段时间。

2.1.6.ArchiveLog

当Oracle的ArchiveLog模式被激活后，所有RedoLog数据都会被写入ArchiveLog文件中以便日后进行恢复。

当发生日志组切换时，ARCn（Archive进程，可以存在多个）进程就会将RedoLog文件拷贝到指定存储目录中去，成为ArchiveLog文件。

2.1.7.Direct-PathInsert

这里，我们还要介绍一种特殊的写操作——Direct-PathInsert（直接路径插入）。

Direct-PathInsert通过直接在表中已存在的数据后面添加数据，直接将数据写入数据文件中，而忽略掉了BufferCache。

我们前面提到，为了能在意外时恢复数据，每一个数据修改都会被记录到RedoLog中。

然而，由于RedoLog需要写入到物理文件中去，是一个比较消耗性能的操作。

为了提高性能，我们在批量写入数据时就可以通过Direct-PathInsert的指定NOLOGING的方式来避免写RedoLog。

有多种方法可以指定Direct-PathInsert：

A．CTAS（CREATETABLEASSELECT）B．SQL*Loader指定Direct参数。

C．在语句中指定APPEND提示。

2.2.读

2.2.1.物理读

产生物理读主要有以下几种情况：

第一次读取

当数据块第一次被读取到，Oracle会先将其从磁盘上读入BufferCache中，并将他们放在LRU（LastRecentlyUsed）链表的MRU（MostRecentlyUsed）端。

再次访问数据块时就可以直接从BufferCache中读取、修改了。

看以下例子：

SQL>selectowner,index_namefromt_test3;2856rowsselected.ExecutionPlanPlanhashvalue:

2878488296|Id|Operation|Name|Rows|Bytes|Cost（%CPU）|Time|

|0|SELECTSTATEMENT||2856|68544|22（0）|00:

00:

01|OracleI/O性能调优手册第-8-页，共-54-页Welcometocuishen’sITBlog:

|1|TABLEACCESSFULL|T_TEST3|2856|68544|22（0）|00:

00:

01|Statistics407recursivecalls32dbblockgets344consistentgets89physicalreads0redosize103888bytessentviaSQL*Nettoclient2475bytesreceivedviaSQL*Netfromclient192SQL*Netroundtripsto/fromclient9sorts（memory）0sorts（disk）2856rowsprocessedSQL>selectowner,index_namefromt_test3;2856rowsselected.Elapsed:

00:

00:

00.03ExecutionPlanPlanhashvalue:

2878488296|Id|Operation|Name|Rows|Bytes|Cost（%CPU）|Time||0|SELECTSTATEMENT||2856|68544|22（0）|00:

00:

01||1|TABLEACCESSFULL|T_TEST3|2856|68544|22（0）|00:

00:

01|Statistics0recursivecalls0dbblockgets276consistentgets0physicalreads0redosize103888bytessentviaSQL*Nettoclient2475bytesreceivedviaSQL*Netfromclient192SQL*Netroundtripsto/fromclient0sorts（memory）0sorts（disk）2856rowsprocessed

数据块被重新读入BufferCache

如果有新的数据需要被读入BufferCache中，而BufferCache又没有足够的空闲空间，Oracle就根据LRU算法将LRU链表中LRU端的数据置换出去。

当这些数据被再次访问到时，需要重新从磁盘读入。

SQL>selectowner,table_namefromt_test22whereowner='SYS';718rowsselected.OracleI/O性能调优手册第-9-页，共-54-页Welcometocuishen’sITBlog:

ExecutionPlanPlanhashvalue:

1900296288|Id|Operation|Name|Rows|Bytes|Cost（%CPU）|Time||0|SELECTSTATEMENT||99|2178|10（0）|00:

00:

01||1|TABLEACCESSBYINDEXROWID|T_TEST2|99|2178|10（0）|00:

00:

01||*2|INDEXRANGESCAN|T_TEST2_IDX1|99||1（0）|00:

00:

01|PredicateInformation（identifiedbyOperationid）2-access（"OWNER"='SYS'）Statistics0recursivecalls0dbblockgets145consistentgets0physicalreads0redosize21690bytessentviaSQL*Nettoclient902bytesreceivedviaSQL*Netfromclient49SQL*Netroundtripsto/fromclient0sorts（memory）0sorts（disk）718rowsprocessedSQL>select*fromt_test1;--占用BufferCache47582rowsselected.ExecutionPlanPlanhashvalue:

1883417357|Id|Operation|Name|Rows|Bytes|Cost（%CPU）|Time||0|SELECTSTATEMENT||47582|3996K|151

（2）|00:

00:

02||1|TABLEACCESSFULL|T_TEST1|47582|3996K|151

（2）|00:

00:

02|Statistics195recursivecalls0dbblockgets3835consistentgets5physicalreads0redosize5102247bytessentviaSQL*Nettoclient35277bytesreceivedviaSQL*Netfromclient3174SQL*Netroundtripsto/fromclient5sorts（memory）0sorts（disk）47582rowsprocessedOracleI/O性能调优手册第-10-页，共-54-页Welcometocuishen’sITBlog:

SQL>selectowner,table_namefromt_test22whereowner='SYS';718rowsselected.ExecutionPlanPlanhashvalue:

1900296288|Id|Operation|Name|Rows|Bytes|Cost（%CPU）|Time||0|SELECTSTATEMENT||99|2178|10（0）|00:

00:

01||1|TABLEACCESSBYINDEXROWID|T_TEST2|99|2178|10（0）|00:

00:

01||*2|INDEXRANGESCAN|T_TEST2_IDX1|99||1（0）|00:

00:

01|PredicateInformation（identifiedbyOperationid）:

2-access（"OWNER"='SYS'）Statistics0recursivecalls0dbblockgets145consistentgets54physicalreads0redosize21690bytessentviaSQL*Nettoclient902bytesreceivedviaSQL*Netfromclient49SQL*Netroundtripsto/fromclient0sorts（memory）0sorts（disk）718rowsprocessed

全表扫描

当发生全表扫描（FullTableScan）时，用户进程读取表的数据块，并将他们放在LRU链表的LRU端（和上面不同，不是放在MRU端）。

这样做的目的是为了使全表扫描的数据尽快被移出。

因为全表扫描一般发生的频率较低，并且全表扫描的数据块大部分在以后都不会被经常使用到。

而如果你希望全表扫描的数据能被cache住，使之在扫描时放在MRU端，可以通过在创建或修改表（或簇）时，指定CACHE参数。

2.2.2.逻辑读

逻辑读指的就是从（或者试图从）BufferCache中读取数据块。

按照访问数据块的模式不同，可以分为即时读（CurrentRead）和一致性读（ConsistentRead）。

注意：

逻辑I/O只有逻辑读，没有逻辑写。

即时读

即时读即读取数据块当前的最新数据。

任何时候在BufferCache中都只有一份当前数据块。

即时读通常发OracleI/O性能调优手册第-11-页，共-54-页Welcometocuishen’sITBlog:

生在对数据进行修改、删除操作时。

这时，进程会给数据加上行级锁，并且标识数据为“脏”数据。

SQL>select*fromt_test1whereowner='SYS'forupdate;22858rowsselected.Execut