• 博客访问: 114044
  • 博文数量: 73
  • 用 户 组: 普通用户
  • 注册时间: 2009-09-01 16:57
  • 认证徽章:
个人简介

暂无介绍

文章分类

全部博文(73)

文章存档

2015年(2)

2010年(51)

2009年(12)

2008年(5)

2006年(3)

我的朋友

分类: Linux操作系统

2010-07-05 10:29:02

Oracle 9i 整体性能优化概述草稿之四:调整磁盘I/O

5 调整磁盘I/O 52
5.1 数据文件I/O调整 52
5.1.1 测量数据文件IO 52
5.1.2 改进数据文件I/O 53
5.2 DBW0 56
5.2.1 测量DBW0性能 56
5.2.2 改进DBW0性能 57
5.3 单个段数据块I/O调整 58
5.3.1 测量段性能和调整 59
5.4 排序操作和临时段优化 62
5.4.1 排序 62
5.5 UNDO 64
5.5.1 测量UNDO I/O性能 65
5.5.2 优化undo segment 66


5 调整磁盘I/O
通过适当的确定SGA的大小来尽可能的减少物理I/O;在必要时,尽可能快的执行任何物理剩余的物理I/O。

磁盘 I/O的来源:
1) Database buffer cache 到datafile的DBW0写操作和逆向的用户读Server Process。
2) 写数据到UNDO上维护读一致的DBW0。
3) Redo log buffer到logfile的LGWR写操作。
4) 日志内容到归档的ARC0操作。
5) 应用的磁盘排序操作。

基础概 念:
段:Oracle的表或索引
范围:为段提供的存储实际数据的逻辑单位,是一个或多个连续的数据块。
Oracle块:具体存储 实际数据的Oracle单位,由一个或多个连续的操作系统块组成。
表空间:存储数据的逻辑文件。
数据文件:存储数据的物理文件。


5.1 数据文件I/O调整
5.1.1 测量数据文件IO
v$filestat,v$datafile,v$tempfile
 搜集至数据库启动以来所发生的
确定:init.ora parameters的timed_statistics设置为TRUE
select s.file#||'D',d.name,s.phyrds,s.phywrts,s.avgiotim,s.miniotim,s.maxiowtm,s.maxiortm
from v$filestat s,v$datafile d
where s.file# = d.file#
union
select s.file#||'D',t.name,s.phyrds,s.phywrts,s.avgiotim,s.miniotim,s.maxiowtm,s.maxiortm
from v$filestat s,v$datafile t
where s.file# = t.file#
order by 3 desc;
注:
s.phyrds:物理读次数
s.phywrts:物理写次数
s.avgiotim:执行I/O所花费的 平均时间(毫秒)
s.miniotim:执行I/O所花费的最短时间(毫秒)
s.maxiowtm:执行写I/O所花费的最长时间(毫 秒)
s.maxiortm:执行读I/O所花费的最长时间(毫秒)

 搜集某个时间以来所发生的
$ORACLE_HOME/rdbms/admin/utlbstat.sql
$ORACLE_HOME/rdbms/admin/utlestat.sql
SQL>Rem Sum IO operations over tablespaces
SQL>Rem I/O should be spread evenly across drives…
如上两段描述了表空间和数据文件分别的I/O信息。

 定时搜集statspack

5.1.2 改进数据文件I/O
5.1.2.1 保证非Oracle I/O不干涉数据文件I/O
不要将非Oracle文件与数据文件放在相同的磁 盘驱动器或逻辑卷上。否则不仅可能会存在对磁盘资源的竞争,而且此时的I/O统计数据不能反映真实的磁盘I/O数据。

检查:
使用操 作系统命令。

5.1.2.2 使用locally Managed tablespace减少I/O
使用LMT创建的表空间,不再 使用free list管理范围,从而减少free list 竞争。这样就不在需要访问SYSTEM表空间中的系统数据目录,而在表空间头使用位图的方法,能更快的分配表空间内的空间。

检查:
SQL>select tablespace_name,status,contents,extent_management from dba_tablespaces
where extent_management != 'LOCAL';

5.1.2.3 把数据文件均分到许多设备、逻辑卷和控制器上
1) 不应把任何应用程序段存储在SYSTEM表空间。保证数据库用户不使用SYSTEM作为缺省或临时表空间。由于执行递归的SQL存放在SYSTEM表空 间,所以注意SYSTEM表空间可能有频繁的读活动。
检查:
select owner,segment_name,segment_type,tablespace_name,bytes/1024/1024
from dba_segments where tablespace_name = 'SYSTEM'
and owner not in ('SYS','SYSTEM','OUTLN','WMSYS','ORDSYS','MDSYS');

select username,user_id,account_status,default_tablespace,temporary_tablespace,created,profile
from dba_users where username not in
('SYS','SYSTEM','OUTLN','WMSYS','ORDSYS','MDSYS','ORDPLUGINS')
and (default_tablespace = 'SYSTEM' or temporary_tablespace = 'SYSTEM');

(转 移对象:alter table .. move tablespace .. ;
alter index .. rebuild tablespace ..;)

2) 应设法把应用于各表空间的数据文件放到不同的磁盘驱动器、卷组或磁盘控制器上,最大限度减少磁盘争用;同时也要把非数据文件的数据库组件(如 controlfile,redo log file, archive file ..)进行分开,因为他们的I/O特征是不一样的。
%df -k
- 将系统、临时、回滚段和应用的表和索引分开。
- redo log和archive log分开。
- controlfile与datafile分开。
- dump file由于随机的,且操作不多,可忽略。
- 不在SYSTEM上设置回滚段和临时段。
- 可把段按功能或大小及应用进行分开。

5.1.2.4 在数据文件I/O发生时,尽可能快的完成全部数据文件I/O操作
1) 将高频率的I/O数据文件放到不同的磁盘驱动器和控制器上。
  
2) 条状化表空间。
把数据文件放在RAID设备上,已经隐含使用了条状化,不需要DBA做相关调整(只需要注意条宽度和条深度)。
手工条状 化:
创建一个由几个数据文件组成的表空间,每个数据文件放在不同的物理设备上,接着创建一个段,使它的范围被存储在这些数据文件上。
例:
创 建表空间
SQL>create tablespace app1_data
Datafile ‘/u01/oradata/prod/app1_data01.dbf’ size 5M
Extent management local
Autoextend on next 5M maxsize 2000M;
创建emp表的初始范围
SQL> create table emp (emp_id number,last_name varchar2(20))
Storage (initial 4M next 4M pctincrease 0)
Tablespace app1_data;
给app1_data 表空间添加数据文件
SQL>alter tablespace app1_data
Add datafile ‘/u02/oradata/prod/app1_data02.dbf’ size 5M;
手工分配emp表的新范围
SQL>alter table emp
Allocate extent (datafile ‘/u02/oradata/prod/app1_data02.dbf’ size 4M);

(调整的表的时候可以使用;但一般与管理 他的时间相比,手工条状化费时且好处不多)

3) 调整init.ora 的 DB_FILE_MULTIBLOCK_READ_COUNT。
  DB_FILE_MULTIBLOCK_READ_COUNT参数指定一 个用户server process在执行全表扫描时在单个I/O中读取的最多数据块个数。默认值是8。
通过增加该值,可以每次I/O访问更多的块,减少I/O次数。
(1, CBO可能会受该参数影响,认为使用全表扫描比使用索引有更少的I/O时候,会选择全表扫描而不使用索引
(2, 使用RAID条状时,应将该值设成条大小的一个倍数)。
  
  先确定应用程序执行全表扫描的频率:
SQL>select name,value from v$sysstat where name = 'table scans (long tables)';
   如果值很大,说明增加该值可能性能会有优化。

同时,
select * from v$session_longops where time_remaining > 0;
监视运行时间很长的选定操作相关的活动情况(每 当一个进程扫描了10,000个以上的 Oracle块时,table scan操作就会出现在该视图中,并不表示该操作一定是全表扫描),“time_remaining > 0”说明检查当前活动的。
如果需 要检查具体执行时间,可以使用DBMS_APPLICATION_INFO.SET_SESSION_LONG_OPS向 v$session_longops注册一个用户会话的方法了解。
(这个监视应发扬光大)

5.1.2.5 日志文件
建立大 小合适的日志文件以最小化竞争;
提供足够的日志文件组以消除等待现象;
将日志文件存放在独立的、能快速访问的存储设备上(日志文件可以创 建在裸设备上)。

日志文件以组的方式组织管理,每个组里的日志文件的内容完全相同。
5.1.2.6 归档日志文件
如果选择 归档模式,必须要有两个或两个以后的日志组,当从一个组切换到另一个组时,会引起两种操作:DBWn进行Checkpoint;一个日志文件进行归档。

归 档有时候会报错:
ARC0:Beginning to archive log# 4 seq# 2772
Current log# 3 seq# 2773……
ARC0: Failed to archive log# 4 seq# 2772
ARCH: Completed to archiving log#4 seq# 2772
建议init参数修改如下:
log_archive_max_processes=2
#log_archive_dest = ‘/u05/prodarch’
log_archive_dest_1 = "location=/u05/prodarch MANDATORY’
log_archive_dest_state_1 = enable

log_archive_dest_2 = "location=/u05/prodarch2 OPTIONAL reopen=10" (或其它目录)
log_archive_dest_state_2 = enable
log_archive_min_succeed_dest=1

log_archive_dest_state_3 = DEFER
log_archive_dest_state_4 = DEFER
log_archive_dest_state_5 = DEFER


5.2 DBW0
DBW0负责把database buffer cache写到数据文件上,在checkpoint和用户server process在database buffer cache搜索自由缓冲区时触发DBW0。
5.2.1 测量DBW0性能
 系统视图检查至数据库启动以来的数据
1)v$system_event
select event,total_waits,average_wait
from v$system_event
where event in
('buffer busy waits','db file parallel write','free buffer waits','write complete waits');
注释:
buffer busy waits:针对database buffer cache中的缓冲区等待正发生,可能包括DBW0写脏缓冲区效率不高所致。
db file parallel write:DBW0并行写遇到等到。可能是因为datafile驻留在一个速度慢的设备上引起,也可能DBW0的处理速度跟不上要求他写的那些请求时间 引起。
free buffer waits:用户server process在把脏缓冲区放到dirty list上,同时搜索LRU的一个自由缓冲区时遇到等待。
write complete waits:用户会话一直遇到DBW0从database buffer cache中写缓冲区等到。
如果查询的total_waits很 高,且不住增长,所以DBW0没有有效的执行操作。

2)v$sysstat
select name,value from v$sysstat
where name in ('redo log space requests','DBWR buffers scanned','DBWR lru scans');
注:
redo log space requests:表示在日志切换后,等候redo log可用的等待事件。(说明了redo log的组数,成员数,大小需要好好的考虑了)
DBWR buffers scanned:Database buffer cache中检查找出待写脏缓冲区的总数量。
DBWR lru scans:lru扫描次数。
Value以毫秒为单位。

select scanned.value/scans.value "avg.num. buffers scanned"
from v$sysstat scanned,v$sysstat scans
where scanned.name = 'DBWR buffers scanned'
and scans.name = 'DBWR lru scans';
如果"avg.num. buffers scanned"值很高或不断增长,说明DBW0在底效的执行写操作。

 STATSPACK
 Utlbstat.sql utlestat.sql

5.2.2 改进DBW0性能
两个 init.ora参数用于改进DBW0性能:
DBWR_IO_SLAVES:启动DBWR0的从属进程
DB_WRITER_PROCESSES: 启动DBWR同样的进程

注意:如果DBWR_IO_SLAVES非0,则DB_WRITER_PROCESSES设置无效。
至于是 启动从属进程,还是启动DBWRn进程,则需要根据具体等待时间是否需要做buffer cache管理等待等进行判断,即如果仅仅是写等待,则启动从属进程;如果是如free buffer waits,则启动DBWRn进程。

5.2.2.1 DBWR_IO_SLAVES
默认值是0,可以配置的最大值由操作系统决定。
如,Ora_i102_prod,i表示是从属进程,1表 示使用第一个适配器(一个内存池),03是使用1号适配置器的第3个进程。
从属DBWR只会执行写操作,不能完成如把缓冲区从LRU list转移到database buffer cache内的dirty list上。
DBWR工作原理:
在有写操作请求 时,DBWR0协调从属进程处理,如果所有可用的从属进程都忙,则启动一个从数进程处理(最多启动的从属进程不会超过本参数指定的数值),如果无法启动, 则产生等待事件。

注意:
在配置了该参数非0时,每当database writer产生I/O从属进程时,执行磁盘I/O的其他进程也会产生从属进程,如log writer、archiver、recovery manager。

5.2.2.2 DB_WRITER_PROCESSES
默认值是1,最大值是10。
本参数指定启动多少 个DBWR进程,而非启动DBWR的从属进程;
启动的DBWR进程,具有与DBWR0完成相同的功能。

5.2.2.3 Checkpoint
Checkpoint进行的操作:DBWn进行IO操作;CKPT更新数据文件头和控制文件。
经常进行 Checkpoint的结果:减少恢复所需的时间;降低了系统运行时的性能。

LGWR以循环的方式将日志写到各个日志组,当一个日志组满 时,oracle server必须进行一个Checkpoint,这意味着:DBWn将对应log覆盖的所有或部分脏数据块写进数据文件;CKPT更新数据文件头和控制文 件。如果DBWn没有完成操作而LGWR需要同一个文件,LGWR只能等待。
在OLTP环境下,如果SGA很大并且checkpoint的次数不 多,在Checkpoint的过程中容易出现磁盘竞争的状况,在这种情况下,经常进行Checkpoint可以减少每次Checkpoint涉及到的脏数 据块的数目。

调节Checkpoint次数的办法:
增大日志文件;增加日志组以增加覆盖的时间间隔。

5.3 单个段数据块I/O调整
调整目标:最小化检索被请求的应用数据而必须访问的块数量。

有两种块大小需要注意:
1) 主块:在数据库创建时由init.ora的db_block_size参数指定,至少用于创建SYSTEM和TEMP表空间。改变主块大小的唯一办法是重 新创建数据库。
一般默认win2000使用2k,SUN使用8k。
2) 局部块:在创建表空间时,指定blocksize关键字(如果没有指定blocksize关键字,则使用主块大小)。

块大小可指定的值的范 围为db_nk_cache_size参数的n的数值(要求是操作系统块的整数倍;要求、不超过操作系统最大的I/O大小)。

select tablespace_name,block_size from dba_tablespaces;

一个范围是一组连续的Oracle块,当创建一个段时,将至少分配一个范围(叫初始范围)给段,初始范围的第一个块(也叫头部块)含有一个线路图,描述了 该范围内其他所有块的位置。

创建表空间,创建表,创建索引的所有可指定的参数和关键字,值得仔细研究一下。

一个Oracle块分为块头区、保留区(pctfree参考)和自由区(pctused参考)。
 块头区:每个块一般使用分配给他的50到200个字节来存储关于该块内容的一些头部信息。(包括:initrans产生在表创建时指定的 transaction slot,该块内含有的行的一个目录和管理该块需要的其他一般性头部信息)
 保留区和自由区:一个块,除了分配给块头区的外,剩下的都是保留区和自由区,其中pctfree参数指定整个块大小的百分之多少留做保留区存储行被更新, 剩下的都是可存储行的自由区。

块刚被分配的时候,是加在free list的最顶端等待被使用,当行被insert数据到自由区,整个块大小只剩下pctfree的时候,将从free list中被移走,不再插入新行。
此 时如果有对该块的update操作,则使用pctfree中指定的空闲空间来扩展;如果发生delete操作,则一旦删除数据行后,如果整个块的空闲空间 占整个块的pctused,则重新把该块放到free list的最顶端(当不连续的自由空间足于可以被insert一新行时,在执行insert前,Oracle会自动合并自由区)。

Pctfree (默认10%)和 pctused(默认40%),可以在段创建时指定,也可在段创建后alter,但对已存储了数据而又没有进入free list的块无效。

Oracle 建议的设置方法:
PCTFREE = 100*Avg.Update Size(bytes)/Avg.Row length
PCTUSED = 100 – PCTFREE – 100*Num.Rows in Table*(Avg.Row Length) / Block Size

select * from dba_tables; //查询表pctfree和pctused

5.3.1 测量段性能和调整
5.3.1.1 动态范围分配与性能
Oracle 9i自动使用management local局部管理表空间的方法,在数据文件头文件中使用位图来管理和分配范围,只简单的改变位图的值来表示数据文件内块的状态,只涉及极少的底归 I/O,可忽略一向。

而如果不是使用management local,则使用了free list,此时段的可用范围满了后,扩展新段,需要查询SYSTEM表空间的相关管理信息和范围空间的实际获取,会增加更多的I/O操作。只能经常检查, 范围使用到一定比例的表,手工分配更多的范围,但Oracle建议,为了性能,一个段的范围数量不应该超过1000个(而management local可以有数千个范围)。

SQL>select owner,table_name,1-(empty_blocks/(empty_blocks+blocks)) "%blocks used"
from dba_tables
where owner != 'SYS'
and 1-(empty_blocks/(empty_blocks+blocks)) > 0.95
order by 1;
查询出的表 需要手工分配了。(如果表没有经过analyze,则block和empty_blocks为空值)

SQL>alter table  app1.sales allocate extent;
手工扩展范围。
5.3.1.2 范围大小的确定与性能
越大的范围比越 小的范围提供更好一点的性能,因扩展的次数就少了;而且可以在初始范围头部由一个统一的块-----范围地图(extent map)中找出所以范围的位置。如果
db_file_multiblock_read_count的设置正确,读取范围的I/O次数就会减少。
   大范围的缺点:空间可能会浪费,及在需要一个范围时没有足够大的连续Oracle块可使用。

1) 有随机存取特性的OLTP,小块更好。
2) 小块减少块争用,因每块只含有教少的行。但增加database buffer cache开销,必须访问更多的块。
3) 小块对小行的排序更好。
4) 大块用在DSS比较好。大块增加块争用,并要求较大的database buffer cache大小。

5.3.1.3 行连接和行迁移
行连接和行迁移由块大小所引起。

行连接:插入的行超出Oracle块大小,该行将存储在两个或两个以上的块中,这 种横跨多块叫行连接。
对性能的影响是: 操作一行需要读取更多的块。
唯一处理办法:增加块大小或减少插入的大小。

行迁移: 只由update操作引起。如果更新的行大小超过pctfree指定的可用范围,Oracle会把该行完全迁移放到一个新块上,而在原来的块上保留一个指 向新块的指针。
对性能的影响:操作一行,至少需要读两个块。

 确定行连接或行迁移
检查的两种办法:
1) dba_tables中的chain_cnt列。
必须先执行analyze分析表该字段才有数据,而且不区分行连接或是行迁移。
SQL>select owner,table_name,chain_cnt from dba_tables where chain_cnt > 0;

2) v$sysstat中table fetch continued row的出现。
SQL>select * from v$sysstat where name = 'table fetch continued row';

由于行迁移只在update时 出现,所以可以通过简单的删除、插入来纠正(纠正后剩下的肯定就只是行连接了):
1) export,delete或truncate,然后重新import该表。
2) 使用alter table .. move命令重建该表。
3) 找出并重新插入迁移行。例:
分析表,得出是否存在行连接或行迁移
SQL>analyze table xxxx compute statistics;
SQL>select table_name,chain_cnt from dba_tables where table_name = ‘xxxx’;
执行$ORACLE_HOME/rdbms/admin/ tulchain.sql文件创建chained_rows表。
使用list chained rows重新分析表。
SQL>analyze table xxxx list chained rows;
可在chained_rows中查找出有行连接或行迁移的行头等信息。现在把这部 分行备份出来。
备份行
SQL>create table temp_t as select * from xxxx
Where rowed in (select head_rowid from chained_rows);
删除行
SQL>delete from xxxx
where rowed in (select head_rowid from chained_rows);
恢复行
SQL>insert into xxxx select * from temp_t;
行迁移处理完成,此时再使用analyze table xxxx compute statistics;分析表后查看select table_name,chain_cnt from dba_tables where table_name = ‘xxxx’;的chain_cnt的数,都将是行连接的数目。


5.3.1.4 高水位(High Water Mark,HWM)与性能
HWM:创建段的时候分配范围,此时Oracle跟踪已用来存储段数据的最高块的ID,该ID就叫HWM。记录在 segment header block中,在segment创建的时候设定在segment的起始位置,当记录被插入的时候以5个block的增量增加,truncate可以重设 high water mark的位置,但delete不能。

性能影响:不管中间是否有空块,每次全表扫描都会把HWM以上所有块扫描一次 (很多空块可能就这样被扫描,读取了比实际数据更多的块)。

HWM的更改只有:
1) exp,drop或truncate,然后重新导入该表。
2) 使用alter table .. move重建该表(此时需要重建索引)。
有 一种情况:一个表被插入很大很大,此时HWM被扩展的很高,然后删除了这个表大量的数据,由于此时HWM不会改变,所以如果该表没有数据再被插入,则删除 后实际有数据的块到HWM之间的空间将被浪费掉。此时只有执行analyze 命令分析该表后,使用alter table xxxx deallocate unused;命令来把他们释放给表空间。

使用analyze分析表后,可以使用dba_tables的 empty_block来确定HWM块数量,使用
alter table tab_name deallocate unused;命令来释放给表空间。

(也可使用DBMS_SPACE.UNUSED_SPACE来确定)。分析后,dba_tables的 empty_blocks列只显示HWM以上有多少块,并不表示这些块是否有数据,blocks显示HWM以下的块,他们加在一起就是一共分给段的范围 了。

如果非要估计一个表的空块,则使用:
SQL> select blocks totol_blocks,round((t.avg_row_len * t.num_rows)/s.block_size,0) needed_blocks,
blocks - round((t.avg_row_len*t.num_rows)/s.block_size,0) free_blocks
from dba_tables t,dba_tablespaces s
Where t.tablespace_name = s.tablespace_name
And t.table_name = 'PERF_TCH_QJ'
And t.owner = 'PERF';

5.4 排序操作和临时段优化
5.4.1 排序
优化的最大目标是最大限度的减少排序操作,如不可避 免,则尽可能在内存中进行排序。(排序只在内存或磁盘(temp表空间)内进行)

导致排序的SQL:
1) ORDER BY
2) GROUP BY
3) SELECT DISTINCT
4) UNION
5) INTERSECT
6) MINUS
7) ANALYZE
8) CREATE INDEX
9) 表间非索引上的连接

每个用户server process,都会被分配一个指定大小的内存用来排序,如果要排序的内容小于该内存,则在内存排序;如果大于该内存,则内容会被分成组块,排序好的组块 被写入TEMP表空间,直到所有组块排序完成,此时再合并所有组块返回给用户。

分配给每个用户server process排序的内存大小受如下参数影响:
 SORT_AREA_SIZE
指定每个用户应留出多少内存空间用于排序。
其默 认值受操作系统影响,最小为6个Oracle块大小。
Share server模式中,该内存来自UGA。在专用服务器模式下,排序空间在PGA中.
如果没有建立large pool,UGA处于shared pool中,如果建立了large pool,UGA就处于large pool中,而PGA不在sga中,它是与每个进程对应单独存在的。
PGA:program global area,为单个进程(服务器进程或后台进程)保存数据和控制信息的内存区域。PGA与进程一一对应,且只能被起对应的进程读写,PGA在用户登录数据库 创建会话的时候建立。

 SORT_AREA_RETAINED_SIZE
指定:排序完成后,如果排序区还含有需要返回给用户的排 序行,则用该参数指定留给这些行的内存容量。
默认与SORT_AREA_SIZE的值一样大,最小可为2个Oracle块。(执行 parallel query并行查询需要的大小可能会大于几倍的SORT_AREA_SIZE大小)
 PGA_AGGREGRATE_TARGET
在使用SORT_AREA_SIZE指定了排序内存后,就可以使用该参数指定所有用户一共最多可以使 用的排序内存总大小。
默认为0,有效值为10M到4000G。
 WORKAREA_SIZE_POLICY
指定排序内存是明确 管理还是隐含管理。
= AUTO   
Oracle自动管理排序区内存分配,只确保不超过PGA_AGGREGRATE_TARGET大 小。
= MANUAL 
每个用户的排序区大小等效于SORT_AREA_SIZE大小。

5.4.1.1 测量排序
v$sort_segment
SQL>select mem.value/(mem.value+disk.value) "in-memory sort ratio"
from v$sysstat mem,v$sysstat disk
where mem.name = 'sorts (memory)'
and disk.name = 'sorts (disk)';
测量内存排序占总排序的比率―――95%以上表示正常。

5.4.1.2 优化排序
 避免使用引起排序的SQL语句
使用unicon all 代替union将避免重叠值所正常需要的排序。
对ORDER BY 和GROUP BY字段进行索引可以最小话排序。
保证所有外键被索引,减少主、外键连接排序。
CREATE INDEX .. NOSORT指定创建索引时不排序。
ANALYZE使用estimate选项代替compute选项;考虑使用analyze .. for columns命令只分析与应用有关的列。

 调整init.ora参数鼓励内存排序
增加SORT_RATE_SIZE的大小需要 特别注意:太大很多用户同时排序可能会对服务器内存形成负面影响,在初始化排序之前,该内存区不会分配给会话;开始排序后,要有足够的内存区分配给排序。
在 加大SORT_RATE_SIZE的同时,减少SORT_AREA_RETAINED_SIZE能帮助减少过度使用内存。
使用Parallel Query环境,将使用的内存良将会是SORT_RATE_SIZE*2*并行度。Oracle建议:1MB对于使用Parallel Query的OLTP环境是合适的。

 正确利用临时表空间,改进与排序有关的I/O读操作
创建临时表空间(多创建一些临时表空间 分给不同的用户使用):
SQL>create temporary tablespace TEMP
Tempfile ‘/u01/oradata/prod/temp01.dbf’ size 500M
Autoextend on next 50M maxsize 2000M
Extent management local
Uniform. size 64K;
指定 uniform. size等于(SORT_RATE_SIZE + 1个块)* N ,使得一次I/O可以完成一个排序组块写入。

Select tablespace_name,current_users,total_extents,used_extents,extent_hits,max_used_blocks,max_sort_blocks FROM v$sort_segment ;
Column Description
CURRENT_USERS Number of active users
TOTAL_EXTENTS Total number of extents
USED_EXTENTS Extents currently allocated to sorts
EXTENT_HITS Number of times an unused extent was found in the pool
MAX_USED_BLOCKS Maximum number of used blocks
MAX_SORT_BLOCKS Maximum number of blocks used by an individual sort

临时表空间的配置:
A、initial/next设置为 sort_area_size的整数倍,允许额外的一个block作为segment的header
B、pctincrease=0
C、 基于不同的排序需要建立多个临时表空间
D、将临时表空间文件分散到多个磁盘上

修改系统默认临时表空间:
SQL>alter database default temporary tablespace temp;
指定所有系统用户的缺省临时表空间为temp;不再 使用SYSTEM。

当前活动排序的用户,使用的临时表空间和排序的块数
select user,tablespace,blocks from v$sort_usage order by blocks;

排序所用表空间, 当前排序用户数,最多使用的排序块数
select tablespace_name,current_users,max_sort_blocks from v$sort_segment;

直 接找出当前产生排序的SQL语句:
SQL> select sess.username,sql.sql_text,sort.blocks
from v$session sess,v$sqltext sql,v$sort_usage sort
where sess.serial# = sort.session_num
and sort.sqladdr = sql.address
and sort.sqlhash = sql.hash_value
and sort.blocks > 10

5.5 UNDO
Oracle绝对禁止一个用户查看另一个用户未提交的事务数据。
启 动一个DML事务时,已修改数据的象前版本被缓存在database buffer cache,再有一个缓冲副本被写入一个回退段(undo segment)上。

Undo segment三个重要目的:
1) DML用户发布rollback命令,则可用来恢复数据原状态。
2) 其他用户访问DML用户发布commit前的原数据,则提供一个已修改数据的读一致性视图。
3) 在实例恢复期间,用来rollback一个在实例故障刚发生前进行的未提交的事务。

回滚段由范围组成,这个范围由5个或5个以上的 Oracle块组成。
回滚段工作方式:
1) 在一个回滚段内,以环形方式循环使用范围,直到段装满。由用户的commit或rollback命令发布后释放回滚段内的范围。
2) 同一个undo segment可以存储很多个像前版本,同时,一个像前版本只会存储在一个undo segment而不会因空间等任何问题连接到其他undo segment。
3) 如果一个undo segment中的一个范围启动了一个事务的像前版本,且逐渐增长装满了该范围,此时会环绕到下一个临近的范围继续使用空间,此时如果下一个临近范围已经 被其他事务占领,则绝对不会跳过该临近范围查找其他可用范围,而是会在这个范围之间创建一个新的范围来使用。
4) 一个undo segment能处理的事务个数,取决于Oracle块大小。
5) 每个数据库都至少有一个回滚段(系统回滚段),一旦创建了其他回滚段,则该回滚段将只用于处理数据目录读一致性和事务控制。
6) set transaction use rollback segment命令可以申请一个指定的回滚段。
7) LOB列不使用undo segment,而使用创建时分配给表的空间来存储像前版本。

5.5.1 测量UNDO I/O性能
5.5.1.1 undo segment头部的争用
Oracle 使用undo segment头部块中一个事务表来跟踪使用他的那些事务,其内容通常被缓存在database buffer cache中以便被搜索。OLTP上很有可能会因为访问这个头部发生等待。

SQL> select event,total_waits,time_waited,average_wait
 from v$system_event where event like '%undo%' and event like '%slot%';
average_wait:平均每毫秒 等待的次数,等于0或接近于0最好。

SQL>select class,count from v$waitstat
where class in ('undo header','system undo header');
count:等候访问undo segment头部的次数。理想情况下,该值等于0或接近于0最好。

SQL>select n.name,s.usn,
decode(s.waits,0,1,1-(s.waits/s.gets)) "RBS Header Get Ratio"
from v$rollstat s,v$rollname n
where s.usn = n.usn
order by usn;
RBS Header Get Ratio:等于1或接近1最好,至少为95%。

5.5.1.2 undo segment范围的争用
SQL>select class,count from v$waitstat
where class ='system undo block' ;
count: 系统回滚段的范围的块争用次数


SQL>select w.count,w.count/s.value as wait_ratio from v$waitstat w,v$sysstat s
where w.class = 'undo block'
and s.name = 'consistent gets';
count:非系统回滚段的范围的块争用次数
wait_ratio: 回滚等待率,如果超过1%,则需要调整了。

5.5.1.3 undo segment的环绕
SQL> select n.name,s.usn,
decode(s.waits,0,1,1-(s.waits/s.gets)) "RBS Header Get Ratio",s.wraps
from v$rollstat s,v$rollname n
where s.usn = n.usn
order by usn;
s.wraps:该回滚段被环绕到下一个范围的次数。次数太多表示段范围可能太 小。

5.5.1.4 undo segment的动态范围分配
事务的像前版本在undo segment中发生环绕,而下一个范围已经分配给其他事务的像前版本,此时会在他们之间动态创建一个范围来给该事务的像前版本环绕。应避免以减少 I/O。

SQL> select event,total_waits,time_waited,average_wait
from v$system_event where event = 'undo segment extension';
time_waited: 表示动态分配的等待次数。若值很高或不断增长,说明undo segment太少或太小。

SQL>select n.name,s.usn,s.extends,
decode(s.waits,0,1,1-(s.waits/s.gets)) "RBS Header Get Ratio",s.wraps
from v$rollstat s,v$rollname n
where s.usn = n.usn
order by usn;
s.extends:被动态添加的范围数。若经常发生动态添加,则说明undo segment可能太小。


5.5.2 优化undo segment
优化目标:
1) 用户不用等待,就始终可找到undo segment来使用。
2) 用户始终能得到完成事务所需要的读一致性视图。
3) 回滚段不会引起过多的I/O。

一般就是:
1) 消除对undo segment header或block的争用。
2) 尽量最小化undo segment的扩充和环绕。
3) 避免undo segment用尽。
4) 始终拥有为用户提供一致性视图的undo segment。

做法:
1) 添加更多的undo segment 。
2) 增大现有undo segment。
3) 明确管理大事务的undo segment。
4) undo segment需求最小化。
5) 使用自动管理功能。

5.5.2.1 增加更多的undo segment
最好把新添加的undo segment放在另外的磁盘的表空间内。对undo要求最多的是delete,其次是update,最后是insert。

Oracle建 议,为每4个并发事务创建一个undo segment,最多只能添加20个undo segment.

为了更准确的确定到底需要多大的 undo segment,可以跟踪用户使用的undo segment大小:
//查询当前用户所使用的undo segment大小
SQL>select s.osuser,s.username,t.used_ublk
from v$session s,v$transaction t
where s.taddr = t.addr;
t.used_ublk:单位为Oracle块,*block size等于该用户将使用的size。
//查询某个大事务的具体使用大小。
1) 只保留一个undo segment online,其他的都offline。
Alter rollback segment xxxxx offline;
2) 统计当前所使用了的undo segment大小。
Select n.name,s.sun,s.writes from v$rollname n,v$rollstat s
Where n.usn = s.usn and name != ‘SYSTEM’;
s.writes: 有多少字节的数据被写到该rollback segment上了。
3) 执行遇到回滚问题的大事务。
如:delete from alarminfo;
4) 重新执行“2)”的统计,使用新查询的s.writes减去(-)“2)”中查询出来的s.writes值,就是“3)”中事务所要使用的undo segment大小.

如果设置的undo segment大小是按大事务来设置,可能会浪费很多空间,只需要明确管理undo segment就可以了。

典型错误:ORA-01555 SNAPSHOT TOO OLD
一个修改事务很长时间未提交,别人查的 时候在undo segment中找到了一个像前版本得到一致性读,别人还在查询到该修改前,最先修改的人提交了,并且此时undo segment中因为接受了commit而不守护该范围,此范围被其他事务写了。
这时候就会发生这种错误,只需要在查一次就可以了。
防 范:
1) 表上发生小事务时候,设法避免运行时间很长的查询。
2) 增加undo segment的大小和数量。

一般设 置:initial=512k,next=512k,minextents=20,这样就会创建一个10M的undo segment.

5.5.2.2 明确管理大事务的undo segment
创建一个很大的undo segment,专用于处理特定的事务.
由于Oracle会自动把 任务分配给undo segment,所以一般创建完和使用完后,需要手工把他们offline.

1) 创建大回滚段.
Create private rollback segment rbs_for_large_tran
Storage (initial 10M next 10M) tablespace rbs;
2) 直到在作业开始前,把rbs_for_large_tran 联机.
Alter rollback segment rbs_for_large_tran online;
或:execute dbms_transaction.use_rollback_segment(‘rbs_for_large_tran’);
3) 启动作业.
Delete from alarminfo;
4) 一旦"3)"执行完,马上在另外一个窗口把该rbs_for_large_tran段offline.
Alter rollback segment rbs_for_large_tran offline;

注意:在作业中的任何commit,都将导 致rbs_for_large_tran脱机.若脱机了,需重新联机.


5.5.2.3 undo segment需求最小化
最 大限度的减少写往undo segment的项目数量和大小。
如:
imp的时候使用commit=y.
exp的时候不要使用 consistent选项.
Sql*loader时设置适当的commit值.

5.5.2.4 使用自动管理功能
Oracle 9i的新功能,通过配置init.ora,让Oracle自动来进行管理undo segment(Oracle推荐)。
 undo_management
=auto //使用undo 自动管理(AUM)
=manual //不使用AUM。
 undo_retention
单位是秒.指定一个像前版本在commit后被保存的时间.(减少ORA-01555错误)
 undo_suppress_errors
FALSE,TRUE.指定是否抑制在RBU中可用的命令。
 undo_tablespace
指定用于AUM的表空间名.
(同一时间,只能有一个undo tablespace在线,也必须有一个undo tablespace在线.
如果数据库未创建而undo_management=auto,则 系统自动创建一个SYS_UNDOTBS表空间来使用)

创建的语法:
create undo tablespace undo_tbs
datafile ‘/u01/oradata/prod/undo01.dbf’ size 500M
autoextend on
next 5M maxsize 2000M;
(不能指定初始范围和下一个范围大小,因系统要自己指定)

估计 undo tablespace大小的公式:
Undo space = (undo_retention * (undo blocks per second * db_block_size)) + db_block_size;


删除一个大的undo tbs:
8) 创建一个新的undo tbs undo_tbs02.
9) SQL>alter system setundo_tablespace=undo_tbs02;
此时,新的事务会使用undo_tbs02,而以前的事务,依然会继续使用 undo_tbs.
10) 待Undo_tbs上的所有事务commit或rollback,且超过了undo_retention指定的时间后,drop tablespace删除该undo tbs.
(此时注意,如果drop 了undo_tbs,此时任何发生在undo_tbs的像前读都要报错,此时最好发生在alter命令前的所有事物都commit了或rollback 了.)

SQL>select u.begin_time,u.end_time,
t.name "undo_tbs_name",
u.undoblks "blocks_used",
u.txncount "transactions",
u.maxquerylen "longest query",
u.expblkreucnt "expired blocks"
from v$undostat u,v$tablespace t
where u.undotsn = t.ts#;

查询统计时间内,被undo使用的Oracke块数,发生的事务数,最长的查询时间,在需要查一致性时有多少块已经被覆盖 (出现>0的数表示ORA-01555就很可能发生).

阅读(1014) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册