ORACLE数据库学习之数据库的优化



                                             数据库的优化

概述

影响数据库性能的因素包括：系统、数据库、网络。

数据库的优化包括：优化数据库磁盘I/O、优化回滚段、优化Rrdo日志、优化系统全局区、优化数据库对象。

监控数据库的性能：

在init.ora参数文件中设置TIMED_STATISTICS=TRUE和在你的会话层设置ALTER SESSION SETSTATISTICS=TRUE。运行svrmgrl 用 connect internal注册，在你的应用系统正常活动期间，运行utlbstat.sql开始统计系统活动，达到一定的时间后，执行utlestat.sql停止统计。统计结果将产生在report.txt文件中。（utlbstat.sql utlestat.sql 一般存放在$ORACLE_HOME/RDBMS/ADMIN子目录下）

优化数据库磁盘I/O

检查系统的I/O问题

在UNIX系统中工具sar－d能检查整个系统的iostat（IO statistics），在NT系统上则使用性能监视器（Performance Monitor）.

反映oracle文件I/O的进程

文件

进程

LGWR

DBWN

ARCH

SMON

PMON

CKPT

Fore_ground

PQ Slave

数据库文件

 

Y

 

Y

Y

Y

Y

Y

Log文件

Y

 

 

 

 

 

 

 

归档文件

 

 

Y

 

 

 

 

 

控制文件

Y

Y

Y

Y

Y

Y

Y

Y

使用V$FILESTAT确定oracle数据文件I/O

SELECT NAME,PHYRDS,PHYWRTS FROM V$DATAFILE DF,V$FILESTAT FS WHEREDF.FILE#=FS.FILE# ;

使用分布I/O减少磁盘竞争

  • 将数据文件和redo log文件分开

  • Striping 表数据

  • 分开表和索引

  • 减少与oracle无关的磁盘I/O

避免动态空间管理

在创建如表或回滚段的数据库实体时，在数据库中会为这些数据分配空间，该空间被称为段。如果数据库操作引起数据增加并超出了分配的表空间，oracle会扩展该段，动态扩展会降低系统性能。

  • 确定动态扩展

    select name,value from v$sysstat wherename=’recursive calls’ ;

  • 分配分区

    确定实体的最大大小；

    选择存储参数值，使oracle分配足够大的分区，在创建实体时可以装入所有数据

  • 避免回滚段的动态空间管理

    回滚段大小由其存储参数所决定，回滚段必须能保存所有交易的回滚入口；

    使用settransaction 命令可以为回滚段赋予交易的合适的大小；

    对长的查询的修改数据，应赋予大的回滚段，以保持所有的回滚入口；

    对OLTP交易，由于频繁交易，每个交易只修改小量的数据，因此赋予小的回滚段。

  • 减少迁移和链接行

  1. 使用ANALYZE 收集迁移和链接行的信息；

  2. 查询输出表：chained_rows;

  3. 如果有许多迁移和链接行，就需要消除迁移行，方法如下：

  1. 创建与原表相同列的中间表，以保存迁移和链接行；

  2. 从原表中删除迁移和链接行；

  3. 将中间表中的行插入到原表中；

  4. 删除中间表

  1. 删除第一步收集的信息；

  2. 重新使用ANALYZE命令查询输出表

  3. 在输出表中出现的行都是链接行，只能通过增加数据块的大小来清除。

调整排序

内存中排序

使用动态表V$SYSSTAT的信息反映排序

SELECT NAME , VALUE FROM  V$SYSSTAT WHERE NAME IN (‘SORTS(MEMORY)’,’SORTS(DISK)’);

SORTS(MEMORY)－不需要使用I/O操作而完全在内存完成的排序数；

SORTS(DISK)－需要使用I/O操作与磁盘临时段才能完成数据的排序数目。

增大SORT_AREA_SIZE以避免磁盘排序

使用NOSORT创建非排序的索引

CREATEINDEX INDEX_NAME ON TABLE TABLE_NAME(COLUMN_NAME) NOSORT ;

调整Checkpoints

一个checkpoint是oracle自动执行的一种操作，当检查点操作时，数据库中的所有缓冲区会写回磁盘，所有数据库的控制文件被更新。Checkpoint频繁发生会加快数据库的恢复，但是增加了I/O次数，会降低系统的性能。

调整LGWR和DBWn I/O

调整LGWRI/O

每次I/O写的大小依赖于LOG缓冲区的大小，该大小由LOG BUFFER 所设置，缓冲区太大会延迟写操作，太小可能导致频繁的小的I/O操作。如果I/O操作的平均大小很大，那么LOG文件就会成为瓶颈，可以使用STRIPE REDO LOG文件避免这个问题。

调整DBWNI/O

使用初始参数DB_WRITER_PROCESSES，可以创建多个数据库写进程。

调整竞争

由多个进程同时请求使用相同的资源时，就产生了竞争

确定竞争问题

视图V$RESOURCE_LIMIT提供了一些系统资源的使用限制。

如果系统存在无反应的现象，检查V$SYSTEM_EVENT，检查最大平均等待时间的事件；

如果存在过量的缓冲区等待，检查V$WAITSTAT，确定哪个类型的块有最多的等待次数和最长的等待时间，再查询V$SESSION_WAIT得到每个缓冲区的等待时间。

减少回滚段的竞争

通过检查V$WAITSTAT可以确定回滚段的竞争：

SELECT CLASS,COUNT FROM V$WAITSTAT WHERECLASS IN (‘SYSTEM UODO HEADER’,’SYSTEM UODO BLOCK’,’UODO HEADER’,’UODO BLOCK’);

减少调度进程的竞争

检查调度进程的busy率

SELECT NETWORK”PROTOCOL”, SUM(BUSY)/(SUM(BUSY)+SUM(IDLE))“TOTAL BUSY RATE” FROM V$DISPATCHER GROUP BY NETWORK ;

如果指定协议的调度进程忙的时间超过50％的有效工作时间，那么，增加调度进程可以提高使用该协议连接到oracle的性能。

检查调度进程相应队列的等待时间

SELECT NETWORK “PROTOCOL”DECODE(SUM(TOTALQ),0.’NO RESPONSES’,SUM(WAIT)/SUM(TOTALQ)||’HUNDREDTHS OFSECONDS’) “AVERAGE WAIT TIME PER RESPONSE” FROM V$QUEUE Q,V$DISPATCHER D WHEREQ.TYPE=’DISPATCHER’ AND Q.PADDR=D.PADDR GROUP BY NETWORK ;

增加调度进程：使用MTS_DISPATCHERS参数和ALTER_SYSTEM命令可以增加调度进程

减少共享服务器进程的竞争

共享服务器进程竞争可以由不断增加的请求等待时间所反映，使用如下查询：

select decode(totalq,0,’No Requests’,wait/totalq||’hundredths of seconds’)“Average Wait Time Per Requests” from v$queue where type=’COMMON’ ;

使用如下查询可以得到当前运行的共享服务进程数：

select count(*) “Shared Server Processes”from V$shared_servers where status!=’QUIT’;

oracle能自动增加共享服务进程，但是MTS_MAX_SERVERS的值可以更改。

减少redo log缓冲区latches竞争

在LGWR进程将redo入口从redo log缓冲区写入redo log文件后，该入口就会被新入口覆盖，供其他log的使用。

V$SYSSTAT中redo buffer allocation retries 反映用户进程等待redo log空间的次数：

Select name,value from v$sysstat wherename=’redo buffer allocation retries’ ;

redo buffer allocation retries的值应该接近0，如果该值持续增加，那么，说明进程需要等待缓冲区的空间。增大参数LOG_BUFFER的值可以增大redo log的大小。

确定redo log缓冲区latches竞争

redo分配latch；

redo复制latches。

一次只能有一个用户分配缓冲区中的空间，在分配了redo入口的空间后，用户进程将入口复制到缓冲区，其最大大小是由LOG_SMALL_ENTRY_MAX_SIZE指定。Redo复制latches的数目由参数LOG_SIMULTANEOUS_COPIES指定。

检查redo log活动

对redo log缓冲区的频繁访问可能导致redo log缓冲区latches竞争，降低系统性能。Oracle在动态表V$LATCH中收集了所有LATCH的统计信息。

其中：表v$latch反映willing-to-wait请求的列

gets－成功的willing-to-wait请求数；

misses－初始不成功的willing-to-wait请求数；

sleeps－请求不成功的等待时间；

表v$latch反映immediate请求的列：

immediate gets－成功的immediate请求数

immediate misses－不成功的immediate请求数

使用如下查询：

selectln.name,gets,misses,immediate_gets,immediate_misses from v$latch l,v$latchnameln where ln.name in (‘redo allocation ’,’redo copy’) and ln.latch#=l.latch# ;

可以计算出各类请求的等待率。

减少latch竞争

要减少redo allocation latch竞争，必须减少单个进程占用latch的时间。要减少这个时间，可以减少该redo allocationlatch的复制。减少LOG_SMALL_ENTRY_MAX_SIZE初始参数可以减少在redo allocation latch的redo入口的复制次数和大小。

减少redo copy Latches竞争可以用增加LOG_SIMULTANEOUS_COPIES的值来增加LATCH数，最多可以达到CPU的两倍。

减少Free List竞争

确定Free List竞争，可以使用以下几步：

  1. 检查V$WAITSTAT，确定DATA BLOCKS的竞争；

  2. 检查V$SYSTEM_EVENT,确定BUFFER BUSY WAITS，如果数值高，表明存在竞争；

  3. 在这种情况下，检查V$SESSION_WAIT查询每个缓冲区的忙等待、FILE、BLOCK及ID；

  4. 使用如下查询得到实体和FREE LIST的名称：

    SELECTSEGMENT_NAME,SEGMENT_TYPE FROM DBA_EXTENTS WHERE FILE_ID=file AND BLOCK BETWEENblock_id AND block_id+blocks ;

  5. 使用如下查询找到FREE LIST:

    SELECTSEGMENT_NAME,FREELISTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME=segment ANDSEGMENT_TYPE=type ;

    增加Free List

    要减少表的Free list的竞争，可以使用freelists参数重新创建表，方法有：

  1. 删除旧表，重新创建表；

  2. 通过从旧表选择数据插入到新表，删除旧表，重命名，完成表的重新创建；

  3. 使用Export 和Import，export旧表，删除旧表，import该表。

建立和优化数据库文件的方针：

为表和索引创建不同的表空间。

将表和索引的表空间放在不同的磁盘上。

将REDO日志和回滚段放在不同的磁盘上。

将Oracle可执行文件和数据库文件放在单独的磁盘上。

确定最常用的表、索引，以及他们的表空间，并放在单独的磁盘上。

不要在Oracle数据磁盘上安装其他第三方软件。

监控磁盘I/O的方法：

用以下查询语句可以得到各表空间读写次数，phyrds+phywrts即是磁盘I/O量。应按前面讲的方针调整数据文件的分布方式。

select name,phyrds,phywrts fromv$datafile,v$filestat wherev$datafile.file# = v$filestat.file#

结果如下：

NAME                                  PHYRDS   PHYWRTS

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

/u/oracle/OraHome/oradata/pb/system01.dbf     2511          8

/u/oracle/OraHome/oradata/pb/tools01.dbf          5         2

/u/oracle/OraHome/oradata/pb/rbs01.dbf          18        20

/u/oracle/OraHome/oradata/pb/temp01.dbf          4          2

/u/oracle/OraHome/oradata/pb/users01.dbf        45          5

/u/oracle/OraHome/oradata/pb/indx01.dbf          4          2

/u/oracle/OraHome/oradata/pb/drsys01.dbf         4          2

 

7 rows selected.

 

优化回滚段

检测回滚段争用：

select class,count from v$waitstat

where class in ('undo header','undoblock','system undo header','system undo bolck')

CLASS                  COUNT

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

system undo header         0

undo header                0

undo block                 0

 

select sum(value) from v$sysstat where name in ('consistent gets','db blockgets')

 SUM(VALUE)

  ----------   

    20589

通过以下公式计算等待比率：

system header waits = system undo header /total

readssystem block waits = systemblock / total

readsrollback header waits =undo header / total reads

rollback block waits = undo block / total reads

若任何一个的比率大于1％则建议再创一个回滚段：

create rollback segment rbs21 tablespace rbs storage (inittial 10koptimal 20k next 10k maxextents 8) ;

避免动态分配空间

用以下语句检查回滚段的动态分配：

select name,shrinks from v$rollstat,v$rollname wherev$rollstat.usn=v$roll ;

NAME               SHRINKS

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

SYSTEM                 0

RBS0                  100

RBS1                    1

若动态分配次数较多可增大回滚段的初始容量。

ALTER ROLLBACK SEGMENT RBS0

STORAGE (inittial 20k optimal 40k next 10kmaxextents 8) ;

优化Redo日志

检测Redo日志缓冲区锁存：

select name,value from v$sysstat

where name = 'rado log space requests' ;

 

value值应接近0若较大则应加大INITXXX.ORA中的LOG_BUFFER项的值。

减少Redo日志转储I/O争用：

将Redo日志分布在不同磁盘中。

1.   优化系统全局区（SGA）

调整操作系统的内存需求

  • 减少页的换入换出；

  • 将SGA置于主存之中

        使用SGA的主要目的是为了在内存中存储数据，以利于快速访问。通过设置初始化参数PRE_PAGE_SGA=YES，在数据库启动时，可以将整个SGA读入内存，这样会减少在启动后ORACLE达到全部性能的总的时间。

    使用如下命令可以查看SGA所分配的内存以及其内部的结构：

    SVRMGR> SHOW SGA

    Total System Global Area                        107720688 bytes

    Fixed Size                                         69616bytes

    Variable Size                                   90701824bytes

    Database Buffers                                16777216 bytes

    Redo Buffers                                       172032 bytes

  • 为单个用户分配足够的内存

     

调整redo log缓冲区

   参数LOG_BUFFER指定了REDO LOG的缓冲区的保留大小。LOG写进程（LGWR）在该缓冲区被填充时总是运行的，在新的LOG进入缓冲区时，原来的LOG应已经写入磁盘。

调整私有sql和pl/sql区

  • 标识不必要的语法分析调用

  1. 在sql跟踪工具有效时运行应用

  2. 查看视图V$SQLAREA view

    SVRMGR> select sql_text,parse_calls,executions from v$sqlarea ;

    如果parse_calls值接近execution值，可能就是不断地对sql语句进行语法分析

  3. 执行如下查询：

    select *from v$statname where name in (‘parse_count(hard)’,’execute_count’);

    其结果类似于:

    statistic#,        name

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

  1. parse_count

  1. execute_count

    然后执行如下查询：

    select  * from v$sesstat  where statistics# in(90,100)  order by value , sid ;

  • 减少不必要的语法分析调用

    初始化参数open_cursors的最大值依赖于操作系统，最少值为5

调整共享池

数据字典或库快存的没有命中，其开销大大多于缓冲快存的没有命中，因此，首先应该为共享池分配足够的空间。

使用如下语句可以确定库快存和数据字典快存的命中率：

select（sum（pins-reloads））/sum（pins）“Lib Cache”from v$librarycache；

select（sum（gets-getmisses-usage-fixed））/sum（gets）“Row Cache”from v$librarycache;

共享池中的自由内存可以查看：

select * fromv$sgastat where name=’free memory’;

当然，如果共享池满了并不一定存在问题，只要上面所说的比率接近于1，就不需要增加共享池大小；

如果自由内存接近于0而且库快存或数据字典快存的命中率小于0.95，那么需要增加共享池的大小。

  • 调整Library Cache

  1. 检查库快存的活动

    select sum(pins) “Executions”,sum(reloads)”CacheMisses while Executing” from v$librarycache ;

    “Executions”列指明sql语句，pl/sql块和实体定义被访问执行了的次数，”Cache Misses whileExecuting”指明其中没有命中的次数。

  2. 减少库快存的非命中

  1. 分配更多的库快存（可以增加初始化参数shared pool size的值；为了利用增加的共享sql区，增加初始化参数open cursors的值）；

  2. 尽可能使用标准的sql语句（sql语句或pl/sql块必须一致，包括字符和空格；sql语句或pl/sql块中对schema实体的引用必须解析到同一schema的同一对象；sql语句中试用的变量的名字和数据类型必须匹配；sql语句必须使用相同的优化方法和优化目标）；

    尽可能使用标准的sql语句，策略：

  • 语句中尽量使用变量而不要使用常量

  • 确保应用用户不会改变优化方法和目标

  • 标准化的变量命名和空格转换

  • 尽可能使用存储过程

  1. 使用cursor_space_for_time加速共享sql区的访问：该参数指定是否共享sql区可以释放，如果为false（默认值），一个共享sql区就可以被释放；如果为true，一个共享的sql区只存在所有与其相关的游标关闭后才可以被释放。

    如果库快存在语句执行时有非命中，不要将其设置为true，否则对新的sql语句将没有空间。

  • 调整Data Dictionary Cache

  1. 监视数据字典快存的活动

    select sum(gets) “Data Dictionary Gets”,sum(getmisses) “Data Dictionary Cache Get Misses” from v$rowcache ;

  2. 减少数据字典快存的非命中

    对频繁访问的数据字典快存没命中和命中比应少于10％－15％。

    要增加数据字典快存可用的内存数，需要增加初始参数shared_pool_size的值。

  • 调整多线程服务器的共享池

    查询动态表v$sesstat可以确定会话信息的大小：

    select sum(value)||‘bytes’”Total memory for all sessions” from v$sesstat, v$statname wherename=’session uga memort’ and v$sesstat statistic#=v$statname.statistic#;

    显示信息如下：

    Total memory for all sessions

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

    157125   bytes

        结果指明当前分配给所有会话的内存。

  • 调整共享池的保留空间

    shared_pool_reserved_size为大的存储保留的shared_pool_size总量；

    shared_pool_reserved_min_alloc－控制保留内存的分配；

    要创建一个保留列表，shared_pool_reserved_size必须大于shared_pool_reserved_min_alloc。

优化共享池：

用以下语句检测命中率：

selectsum(pins)"pinned",sum(reloads)"reloaded" fromv$librarycache

 

  pinned  reloaded

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

   2474      17

 

命中率＝pinned/reloaded

 

若命中率低可增大INIT.ORA中SHARED_POOL_SIZE的值。

 

调整快速缓存

  • 利用缓存的命中率评价快速缓存的活动

    select name,value from v$sysstat where name in (‘dbblock gets’,’consistent_gets’,’physical reads‘);

    db block gets,consistent_gets其和为数据请求总数，physicalreads为数据请求导致访问磁盘数据文件的总数。

    增加db_block_buffers应该遵循如下规则：

  • 缓存命中率低于0.9；

  • 前面增加的db_block_buffers有效

  • 提高缓冲区命中率

    如果缓冲命中率低，如小于60％或70％，那么需要增加缓冲区中的缓存数。方法是增大db_block_buffers的值。

    表v$recent_bucket的每一行反映在缓存中增加一个缓冲区的相应性能值，其中rownum列比可能增加到缓存的缓冲区数小1；count列通过增加缓冲区数rownum＋1到缓存，增加的缓冲区命中。

    查询v$recent_bucket视图：select sum(count) ach from v$recent_bucket where rownum<20 ;

    使用如下公式计算命中率（ACH为增加的命中次数）：HitRatio=1-(physical reads-ACH/(db block gets＋consistent gets) );

    组合v$recent_bucket视图中的行，使用如下语句：select 250*TRUNC(ROWNUM/250)+1||’to’||250*(TRUNC(ROWNUM/250)+1) “Interval”,sum(count)”BufferCache Hits” from v$recent_bucket group by trunc(ROWNUM/250) ;

  • 在命中率高时，删除不必要的缓存

    减少参数DB_BLOCK_BUFFERS的值可以使缓存变小，（最小值为4）。

    视图v$CURRENT_BUCKET包含对一个小缓存的性能统计，其列描述如下：

    ROWNUM—缓存中可能的缓冲区数

    COUNT—对缓冲区数ROWNUM的缓存命中数

    初始参数DB_BLOCK_LRU_STATISTICS控制视图V$CURRENT_BUCKET的统计，默认值为FALSE，意味着不收集统计数据。将其设置为TRUE，视图V$CURRENT_BUCKET将收集统计。

    查询V$CURRENT_BUCKET视图，如果当前缓存为100，如果想知道在90时，缓存非命中的增加数，使用如下语句：

    SELECTSUM(COUNT) ACM  FROM V$CURRENT_BUCKET  WHERE ROWNUM>=90 ;

    计算命中率（ACM为增加的非命中次数）：HitRatio=1-(physical reads + ACM/(db block gets+consistent gets));

    也可以将100个缓冲区分为4个25个缓冲区段：

    select25*TRUNC(ROWNUM/25)+1||’to’||25*(TRUNC(ROWNUM/25+1)”Interval”,sum(count)”BufferCache Hits” from v$CURRENT_BUCKETwhere  rownum>0 group byTRUNC(ROWNUM/25));

    其中INTERVAL为缓冲区段，BUFFER CACHE HITS为对应段的缓存命中率。

优化数据库缓冲区高速缓存:

select name,value from v$sysstat where namein ('db block gets','consistent gets','physical reads') ;

NAME              VALUE

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

db block gets         3700

consistent gets      17603

physical reads         523

 

计算公式：

Hit Ratio = 1-(physical reads / ( db blockgets + consisten gets)) 

若命中率低可以加大INITXXX.ORA中DB_BLOCK_BUFFER的值。

 

调整多缓冲池

  • 多缓冲池特性概述

    可以使用keep缓冲池来维护缓存中的实体，使用recycle缓冲池来防止实体占用缓存中不必要的空间；

    可以将大的数据段置于独立的recycle缓存中，将小的数据段置于独立的keep缓存中。

  • 何时使用多缓冲池

  1. 查找oracle内部实体数据段的数目：

    selectdata_object_id,object_type from user_objects where object_name=’<segment_name>’;

  2. 查找对应该段名的缓存中的缓冲区数：

    selectcount(*)  buffers  from x$bh where obj=<data_object_id> ;

  3. 查找实例中的缓冲区总数：

    select value “totalbuffers ” from v$parameter where name=’db_block_buffers’ ;

  4. 计算当前有指定段使用的缓存所占的比率:

    %cache used bysegment_name=buffers(step 2)/buffers(step 3);

  • 使用多缓冲池调节缓存

    减少I/O操作；

    隔离缓存中的实体；

    限制实体在缓存中的一个部分

  • 使多缓冲池生效

    定义新缓冲池：使用初始参数BUFFER_POOL_NAME来定义缓冲池，每个缓冲池可以指定两个属性：缓冲池中的缓冲区数；分配该缓冲池的LRU latches数。

    用于定义缓冲池的初始参数有：

    BUFFER_POOL_KEEP－定义keep缓冲池

    BUFFER_POOL_RECYCLE－定义recycle缓冲池

    DB_BLOCK_BUFFERS－定义数据库实例的缓冲区数

    DB_BLOCK_LRU_LATCHES－定义数据库实例的LRU latches数

     

    Oracle8定义了三个缓冲池：keep，recycle和default。其中default缓冲池是已经存在的。

  • 使用多缓冲池

    为一个实体建立一个默认缓冲池，语法为：

    BUFFER_POOL{KEEP| RECYCLE | DEFAULT}

  • 如何为各缓冲池设置大小

  1. KEEP缓冲池

    使用keep缓冲池的目的是将实体保留在内存，避免I/O操作

    selectphysical_reads,block_gets,consistent_gets from v$buffer_pool statistics wherename=’KEEP’;

    计算缓冲池命中率的公式：Hit Ratio=1-physical_reads/(block_gets+consistent_gets)

  2. Recycle缓冲池

    使用recycle缓冲池的目的是为了清除内存中的不再使用的数据块，如果“free buffer waits”统计数总是很高，可以确定recycle缓冲池过小：

    确定recycle缓冲池大小的办法：使recycle缓冲池失效运行系通到稳定状态，查看default缓冲池中由本来需要置于recycle缓冲池中数据段的缓冲区数，除以4，该结果就可以用作recycle缓冲池的大小。

  3. 确定数据段置于keep还是recycle缓冲池

    对于至少两倍DEFAULT缓冲池大小，并且占用系统整个I/O的一定百分比的数据段，那么最好放置于recycle缓冲池；

    对于大小小于DEFAULT缓冲池的10％，并且占用至少系统整个I/O的1％的数据段，那么最好放置于keep缓冲池；

    如果对表空间超过一个段时，可以通过查询V$SESSION_WAIT确定每个段的I/O操作。

  • 如何识别和减少LRU Latch竞争

        LRU latches规划了那些缓存中最近最少使用的缓冲区列表，使用DB_BLOCK_LRU_LATCHES参数可以设置数据库实例中的latches总数。

    可以通过如下语句确定系统中是否有latch竞争：

    selectchild#,sleeps/gets ratio from v$latch_children where name=’cache buffers lruchain’ ;

    每个LRU latch的非命中率应少于1％，任何大于1％的latch说明存在竞争，通过如下语句查找出来：

    select namefrom v$buffer_pool_statistics where lo_setid<=child_latch_number andhi_setid>=child_latch_numbers ;

    通过增加系统的LATCHES总数和相关缓冲池的LATCHES数可以减少LRU latch竞争。所允许的LATCHES最大值应少于：number_of_cpus*2*3和number_of_buffers/50

调整排序区

   如果经常进行大的排序，应增大参数SORT_AREA_SIZE的值，将参数SORT_AREA_SIZE和SORT_AREA_RETAINED_SIZE联合使用，更有利于使用大的排序区。

重新分配内存

   在重新定义了oracle内存结构、调整了库快存、数据字典快存和快速缓存后，如果减少了这些结构中某一个结构的内存使用，就可能需要将内存分配到其他结构中；

在改变了oracle内存结构以后，oracle的内存需求也会改变。

减少整个内存使用

采用如下方法：

增加可用内存总数；

减少内存使用

优化数据字典高速缓存：

select sum(gets)"ReadRequests",sum(getmisses)"Reads not in Memory" from v$rowcache ;

 

Read Requests  Reads not in Memory

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

 4764             145

命中率＝ReadRequests/ Reads not in Memory

若低于85％应增加 SHARED_POOL_SIZE

优化游标：

可用以下语句检测游标的命中率：

select * from v$session_cursor_cache ;

select * from v$system_cursor_cache ;

优化游标应综合考虑，若打开的游标过多则应用程序需要的内存增大，可能发生共享池内存被换出到虚存从而影响性能。以下三个INIT.ORA中的参数可影响游标的性能：

CLOSED_CACHED_OPEN_CURSORS 指示oracle当执行commit或rollback语句时，是否显式的关闭游标。如果游标需要反复使用可以设为false，否则应为true从而减少内存占用。

CURSOR_SPACE_FOR_TIME若为true指示oracle只要在sql共享区中有打开的游标就一直保存该区域，若其值为false oracle可将其换出内存，即使游标仍打开。只有共享池能保存所有打开的游标时才将其设为true。

OPEN CURSORS设定一个会话可同时打开的游标数。

注释：以上增加SGA分配的调整以SGA不被换出实存为限，否则SGA部分换出实存反而降低Oracle性能。

优化数据库对象：

管理表的动态分配。

SELECTTABLESPACE_NAME,SEGMENT_NAME,SUM(BYTES),COUNT(*) EXT_QUAN FROM DBA_EXTENT_NAME WHERE TABLESPACE_NAME=’USERS’AND SEGMENT_TYPE=’TABLE’ GROUP BY TABLE_NAME,SEGMENT_NAME ;

统计各表的动态分配次数，若分区太多说明next参数设置的太低了。

优化索引存储参数。

设置适当的PCTFREE、PCTUSED值。

 

**********************************************************************************

 

 优化SGA

什么是SGA（系统全局区）

1、系统全局区（SGA）是一个分配给Oracle的包含一个 Oracle实例的数据库的控制信息内存段。

　主要包括数据库高速缓存(the database buffer cache)，

　重演日志缓存（the redo log buffer），

　共享池（the shared pool），

　数据字典缓存（the data dictionary cache）以及其它各方面的信息。

 

db_block_buffers

1、数据高速缓冲区

2、访问过的数据都放在这一片内存区域，该参数越大，Oracle在内存中找到相同数据的可能性就越大，也即加快了查询速度。

3、db_block_buffers以块为单位，假如DB_BLOCK_SIZE=2K，db_block_buffers=3200，则占用内存=3200*2K=6400K。

 

share_pool_size

1、SQL共享缓冲池

2、该参数是库高速缓存和数据字典的高速缓存。

 

Log_buffer

1、重演日志缓冲区

 

sort_area_size

1、排序区

 

processes

1、同时连接的进程数

 

global_names

1、如果“数据库链路名”和它所要连接的“数据库”拥有相同的名字，则设置global_names = TRUE，

　否则，设置global_names = FALSE

 

db_block_size

1、数据库块大小

2、Oracle默认块为2KB，太小了，因为如果我们有一个8KB的数据，则2KB块的数据库要读4次盘，才能读完，

　而8KB块的数据库只要1次就读完了，大大减少了I/O操作。

3、数据库安装完成后，就不能再改变db_block_size的值了，只能重新建立数据库

　并且建库时，要选择手工安装数据库。

 

open_links

1、同时打开的链接数

 

dml_locks

1、用户一次可对表设定锁的最大数目

2、如果有三个用户修改６个表，则需１８个DML锁来实现并行操作，如果设定DML_LOCKS不够大，操作时执行将中断，你可以通过你的应用程序的操作规模和最大的并行使用的用户数来估算系统所需要的DML_LOCKS的值，但该值的大小对数据库的其他性能没有影响，所以一般都把它设得很大，远超过实际中可能达到的值，如果在系统运行中经常发生表锁死的现象，就应该考虑加大该值。

 

open_cursors

1、打开光标数

2、这个参数可以同时被每个用户进程拥有，这个参数的最大值依操作系统不同而不同。

3、在创建一个包含很多表的合成视图时，如果此参数很小，则视图创建会不成功。

 

dbwr_io_slaves

1、后台写进程数

 

优化数据库磁盘I/O

  1. 为表和索引分别创建不同表空间

  2. 将表和索引的表空间置于不同的磁盘上

  3. 将redo日志和回滚段存于不同的磁盘上

  4. 将oracle可执行文件和数据文件放在单独的磁盘上

  5.  

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/wanghang/p/6299431.html