Oracle实例操作经验总结数据库优化3.docx

资源描述

Oracle实例操作经验总结数据库优化3.docx

《Oracle实例操作经验总结数据库优化3.docx》由会员分享，可在线阅读，更多相关《Oracle实例操作经验总结数据库优化3.docx（14页珍藏版）》请在冰点文库上搜索。

Oracle实例操作经验总结数据库优化3.docx

Oracle实例操作经验总结数据库优化3

CINMS3.5应用系统

基于ORACLE数据库优化方案

2002．7．19

1.Oracle初始化参数配置（initSID.ora）2

DB_FILES=2002

PROCESSES2

DB_FILE_MULTIBLOCK_READ_COUNT2

DML_LOCKS=5002

DB_BLOCK_SIZE=81922

SHARED_POOL_SIZE3

DB_BLOCK_BUFFERS3

LOG_BUFFER5

LOG_SIMULTANEOUS_COPIES=2*thenumberofcpus5

JAVA_POOL_SIZE=05

SORT_AREA_SIZE=41943046

log_checkpoint_interval=redologfile大小后面加06

optimizer_mode=rule6

log_simultaneous_copies=2*cpu数6

shared_pool_reserved_size=10%*shared_pool_size6

db_block_lru_latches=2*cpu数6

buffer_pool_keep=（buffers:

1000,lru_latches:

2）6

2．数据库监控7

2．1数据与索引分开7

2.2rollbacksegment个数设置7

2．3Tables&Indexes定义8

2．4用户数据不能存放于系统表空间中9

2．5索引使用情况9

3.表keep到内存10

4、将procedure/functionkeep到内存中10

5．redologfile大小调整11

1.Oracle初始化参数配置（initSID.ora）

DB_FILES=200

ORACLES可以同时打开的数据文件个数

PROCESSES

确定可同时与Oracle相联结的操作系统进程的数量。

这一参数必须包括用于后台的5个进程和用于每个用户的一个进程。

系统的连接产生的进程一旦达到这最大数，系统将出错误提示用户连接数已满。

此配置参数参考数据字典V$RESOURCE_LIMIT，如果此参数设置过大，易引起‘logfilesync’竟争。

DB_FILE_MULTIBLOCK_READ_COUNT

ORACLE执行全表扫描读取时，每次读出的数据块数，此参数设置，使系统在全表扫描时，可以减少I/O次数，提高性能，db_file_multiblock_read_count*db_block_size为ORACLE在全表扫描时一次读出的数据大小。

如果db_block_size=2k，则设DB_FILE_MULTIBLOCK_READ_COUNT=256

如果db_block_size=8k，则设DB_FILE_MULTIBLOCK_READ_COUNT=64

DML_LOCKS=500

当对表进行DML操作时（insert,update,delete），需要获得LOCK，DML_LOCKS指DML操作时产生锁的最大个数，

DB_BLOCK_SIZE=8192

ORACLE以数据块为基本单位与系统进行I/O操作，块越大，I/O次数越少，数据存取效率越高。

注意：

安装ORACLE8数据库时，此参数要修改，方法如下：

1、安装ORACLE8软件

2、在$ORACLE_HOME/rdbms/install/rdbms/initx.orc文件中加入db_block_size=8192

3、创建数据库

安装ORACLE8I数据库时，运行安装程序，对于DB_BLOCK_SIZE大小有介面提示，缺省8192

SHARED_POOL_SIZE

SharedMemoryPool由DictionaryCache和LibraryCache组成。

DictionaryCache用来存放Oracle的数据字典信息。

每一条SQL语句都需要数据字典。

当要求的数据字典不在高速缓存中，就会引起递归的SQL调用，发生磁盘I/O。

LibraryCache用来存放SQL和PL/SQL语句以及他们的执行计划。

计算Datadictionary命中率：

如果thehitratiois<90%,则增加SHARED_POOL_SIZE大小

column"DataDict.Gets"format999,999,999

column"DataDict.cachemisses"format999,999,999

columnratioformat99.99heading"DatadictCachehitratio"

selectsum（gets）"DataDict.Gets",

sum（getmisses）"DataDict.cachemisses",

（1-（sum（getmisses）/sum（gets）））*100ratio

fromv$rowcache;

计算Librarycachemiss命中率：

如果themissratiois>1%,则增加SHARED_POOL_SIZE大小

column"LIBRARYCACHEMISSRATIO"format99.9999

column"executions"format999,999,999

column"Cachemisseswhileexecuting"format999,999,999

columnratioformat99.9999heading"librarycachemissratio"

selectsum（pins）"executions",sum（reloads）"Cachemisseswhileexecuting",

（（（sum（reloads）/sum（pins））））ratio

fromv$librarycache;

DB_BLOCK_BUFFERS

确定系统全局区（SGA）的缓冲区快存里的缓冲区数量，数据库buffercache存放数据信息。

当Oracle需要一块数据，它首先检查BlockBufferCache,如果找不到，则需移相应的数据BufferCache。

计算BufferCachehit命中率：

如果hitratiois<80%,则增加DB_BLOCK_BUFFERS大小

column"logical_reads"format99,999,999,999

column"phys_reads"format999,999,999,999

column"phy_writes"format999,999,999

columnratioformat999.99heading"BufferHitRatio"

selecta.value+b.value"logical_reads",

c.value"phys_reads",

d.value"phy_writes",

100*（（a.value+b.value）-c.value）/

（a.value+b.value）ratio

fromv$sysstata,v$sysstatb,v$sysstatc,v$sysstatd

wherea.name='dbblockgets'and

b.name='consistentgets'and

c.name='physicalreads'and

d.name='physicalwrites';

备注：

以下数据源于2002.7.18，DB_BLOCK_BUFFERS、SHARED_POOL_SIZE参数调整原则如下：

如果系统内存4G，请参照石家庄，山东，南京生产系统进行配置。

石家庄

山东

南京

shared_pool_size

419430400

314572800

db_block_buffers

157286

714400

716800

db_block_size

8192

2048

DatadictCachehitratio

99.93

99.85

99.96

Librarycachemissratio

.0050

.0019

.0023

Bufferhitratio

90.46

85.49

85.43

如果系统内存2G，请参照郑州，长春,长沙进行配置。

郑州

长春

长沙

shared_pool_size

402653184

412435456

212435456

db_block_buffers

65536

81920

180000

db_block_size

8192

2048

DatadictCachehitratio

99.45

98.91

99.88

Librarycachemissratio

.0019

.0005

.0039

Bufferhitratio

93.82

85.00

95.86

如果系统内存1G，请参照兰州、新疆、湖北、杭州进行配置。

湖北

新疆

兰州

杭州

shared_pool_size

104857600

82488000

268435456

82488000

db_block_buffers

204800

50000

32768

50000

db_block_size

2048

8192

DatadictCachehitratio

99.80

99.89

99.27

99.78

Librarycachemissratio

.0036

.0032

.0005

.0012

Bufferhitratio

85.20

99.41

99.8

87.37

LOG_BUFFER

RedoLogbuffer也是SGA区中一个重要的组成部分，它记录对数据库做的改动。

所有的redo项在被LGWR进程写之前都放在LogBuffer中。

通过ORACLE的SQL语句进行监控：

redobufferallocationretries应该小于1%的redoentries

SELECTname,value

FROMv$sysstat

WHEREnameIN（‘redobufferallocationretries’,’redoentries’）;

如果系统内存1G，LOG_BUFFER=1048576

如果系统内存2G，LOG_BUFFER=2093152

如果系统内存4G，LOG_BUFFER=5242880

LOG_SIMULTANEOUS_COPIES=2*thenumberofcpus

Redobuffercopylatches同时写logentries最大数目，对于多CPU,设置redocopylatches为2倍CPU数。

缺省值cpu_count（cpu个数）

注：

此参数适用于ORACLE8，ORACLE8i中此参数已废弃。

JAVA_POOL_SIZE=0

JAVA缓冲池，为JAVA命令提供语法分析，不用可以关掉

注意：

此参数仅ORACLE8I中存在

SORT_AREA_SIZE=4194304

其大小是否合适，从如下可以进行诊断：

selectdisk.value"Disk",mem.value"Mem",（disk.value/mem.value）*100"Ratio"

fromv$sysstatmem,v$sysstatdisk

wheremem.name='sorts（memory）'

anddisk.name='sorts（disk）';

如果Theratioofdisksortstomemorysorts>5%,则增加sort_area_size大小。

备注：

此参数设置源于南京ratio=0.06%，如果对数据库系统有导入数据操作，此参数可以调到100M左右，以加快导入速度。

log_checkpoint_interval=redologfile大小后面加0

如果LOG_CHECKPOINT_INTERVAL大于redolog文件大小，则Oracle执行alogswitch时checkpoint发生.

如果redologfile10m则log_checkpoint_interval=100000000

optimizer_mode=rule

（从索引中满足查询请求）

log_simultaneous_copies=2*cpu数

（指定redobuffercopylatches同时写logentries的最大数目）

shared_pool_reserved_size=10%*shared_pool_size

（将占用内存较大的procedure/functionKEEP到内存中所预留的空间）

db_block_lru_latches=2*cpu数

（应用系统lrulatches数目）

buffer_pool_keep=（buffers:

1000,lru_latches:

2）

（将频繁调用小表keep在dbbuffercache中所预留的空间）

2．数据库监控

2．1数据与索引分开

建立表的索引时要使用INDEXTABLESPACE，查看表，索引使用表空间情况见脚本，

setpagesize66

columnownerformat'a12'

columnsegment_nameformat'a20'heading'Segment|Name'

columnsegment_typeformat'a10'heading'Segment|Type'

columnused_spaceformat999,999.999heading'Used|Space（M）'

columntablespace_nameformat'a20'heading'Tablespace|Name'

breakontablespace_nameskip1

selecttablespace_name,owner,segment_name,segment_type,bytes/1000000used_space

fromdba_segments

wherebytes>9999999

orderbytablespace_name,used_spacedesc;

2.2rollbacksegment个数设置

prompt***********************************************************************

prompt**

prompt**B）Listrollbacksegmentscontention

prompt**

prompt**Action:

Ifwait_pctis>1%,consideraddingrollbacksegments.

prompt**

prompt***********************************************************************

colnameformata30heading"RollbackSegment"

colwait_pctformat999.99heading"Wait"

selectname,waits,gets,waits/（waits+gets）wait_pct

fromv$rollstat,v$rollname

wherev$rollstat.usn=v$rollname.usn;

调整原则：

回滚段个数为10到20个，根据应用系统业务处理量来划分回滚段区大小为2m,4m,5m

createrollbacksegmentrbs01　tablespaceCINMS_RBS