目录
目录 (1)
数据库的优化 (2)
概述 (2)
监控数据库的性能： (2)
优化数据库磁盘I/O (2)
建立和优化数据库文件的方针： (6)
监控磁盘I/O的方法： (7)
优化回滚段 (7)
检测回滚段争用： (7)
通过以下公式计算等待比率： (8)
若任何一个的比率大于1％则建议再创一个回滚段： (8)
避免动态分配空间 (8)
优化R EDO日志 (8)
检测Redo日志缓冲区锁存： (8)
减少Redo日志转储I/O争用： (8)
优化系统全局区（SGA） (9)
优化数据库缓冲区高速缓存:....................................................................... 错误！未定义书签。
优化共享池：................................................................................................ 错误！未定义书签。
优化数据字典高速缓存： (14)
优化游标： (15)
注释：以上增加SGA分配的调整以SGA不被换出实存为限，否则SGA部分换出实存反而降低Oracle性能。 (15)
优化数据库对象： (15)
管理表的动态分配。 (15)
避免链接行。................................................................................................ 错误！未定义书签。
优化索引存储参数。 (16)
数据库的优化
概述
影响数据库性能的因素包括：系统、数据库、网络。
数据库的优化包括：优化数据库磁盘I/O、优化回滚段、优化Rrdo日志、优化系统全局区、优化数据库对象。
监控数据库的性能：
在init.ora参数文件中设置TIMED_STATISTICS=TRUE 和在你的会话层设置ALTER SESSION SET STATISTICS=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的进程
使用V$FILESTAT确定oracle数据文件I/O
SELECT NAME,PHYRDS,PHYWRTS FROM V$DATAFILE DF,V$FILESTAT FS WHERE
DF.FILE#=FS.FILE# ;
使用分布I/O减少磁盘竞争
●将数据文件和redo log文件分开
●Striping 表数据
●分开表和索引
●减少与oracle无关的磁盘I/O
避免动态空间管理
在创建如表或回滚段的数据库实体时，在数据库中会为这些数据分配空间，该空间被称为段。如果数据库操作引起数据增加并超出了分配的表空间，oracle会扩展该段，动态扩展会降低系统性能。
●确定动态扩展
select name,value from v$sysstat where name=?recursive calls? ;
●分配分区
确定实体的最大大小；
选择存储参数值，使oracle分配足够大的分区，在创建实体时可以装入所有数据
●避免回滚段的动态空间管理
回滚段大小由其存储参数所决定，回滚段必须能保存所有交易的回滚入口；
使用set transaction 命令可以为回滚段赋予交易的合适的大小；
对长的查询的修改数据，应赋予大的回滚段，以保持所有的回滚入口；
对OLTP交易，由于频繁交易，每个交易只修改小量的数据，因此赋予小的回滚段。
●减少迁移和链接行
1.使用ANALYZE 收集迁移和链接行的信息；
2.查询输出表：chained_rows;
3.如果有许多迁移和链接行，就需要消除迁移行，方法如下：
A.创建与原表相同列的中间表，以保存迁移和链接行；
B.从原表中删除迁移和链接行；
C.将中间表中的行插入到原表中；
D.删除中间表
4.删除第一步收集的信息；
5.重新使用ANALYZE命令查询输出表
6.在输出表中出现的行都是链接行，只能通过增加数据块的大小来清除。
调整排序
内存中排序
使用动态表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创建非排序的索引
CREATE INDEX INDEX_NAME ON TABLE TABLE_NAME(COLUMN_NAME) NOSORT ;
调整Checkpoints
一个checkpoint是oracle自动执行的一种操作，当检查点操作时，数据库中的所有缓冲区会写回磁盘，所有数据库的控制文件被更新。Checkpoint频繁发生会加快数据库的恢复，但是增加了I/O次数，会降低系统的性能。
调整LGWR和DBWn I/O
调整LGWR I/O
每次I/O写的大小依赖于LOG缓冲区的大小，该大小由LOG BUFFER 所设置，缓冲区太大会延迟写操作，太小可能导致频繁的小的I/O操作。如果I/O操作的平均大小很大，那么LOG文件就会成为瓶颈，可以使用STRIPE REDO LOG文件避免这个问题。
调整DBWN I/O
使用初始参数DB_WRITER_PROCESSES，可以创建多个数据库写进程。
调整竞争
由多个进程同时请求使用相同的资源时，就产生了竞争
确定竞争问题
视图V$RESOURCE_LIMIT提供了一些系统资源的使用限制。
如果系统存在无反应的现象，检查V$SYSTEM_EVENT，检查最大平均等待时间的事件；
如果存在过量的缓冲区等待，检查V$WAITSTAT，确定哪个类型的块有最多的等待次数和最长的等待时间，再查询V$SESSION_WAIT得到每个缓冲区的等待时间。
减少回滚段的竞争
通过检查V$WAITSTAT可以确定回滚段的竞争：
SELECT CLASS,COUNT FROM V$WAITSTAT WHERE CLASS 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 OF SECONDS?) “AVERAGE WAIT TIME PER RESPONSE” FROM V$QUEUE Q,V$DISPATCHER D WHERE
Q.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 where name=?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请求数
使用如下查询：
select https://www.doczj.com/doc/5c18919875.html,,gets,misses,immediate_gets,immediate_misses from v$latch l,v$latchname ln where https://www.doczj.com/doc/5c18919875.html, in (…redo allocation ?,?redo copy?) and https://www.doczj.com/doc/5c18919875.html,tch#=https://www.doczj.com/doc/5c18919875.html,tch# ;
可以计算出各类请求的等待率。
减少latch竞争
要减少redo allocation latch竞争，必须减少单个进程占用latch的时间。要减少这个时间，可以减少该redo allocation latch的复制。减少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的名称：
SELECT SEGMENT_NAME,SEGMENT_TYPE FROM DBA_EXTENTS WHERE
FILE_ID=file AND BLOCK BETWEEN block_id AND block_id+blocks ;
5.使用如下查询找到FREE LIST:
SELECT SEGMENT_NAME,FREELISTS FROM DBA_SEGMENTS WHERE
SEGMENT_NAME=segment AND SEGMENT_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 from v$datafile,v$filestat where v$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','undo block','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 block gets')
SUM(VALUE)
----------
20589
通过以下公式计算等待比率：
system header waits = system undo header / total
reads system block waits = system block / total
readsrollback header waits = undo header / total reads
rollback block waits = undo block / total reads
若任何一个的比率大于1％则建议再创一个回滚段：
create rollback segment rbs21 tablespace rbs storage (inittial 10k optimal 20k next 10k maxextents 8) ;
避免动态分配空间
用以下语句检查回滚段的动态分配：
select name,shrinks from v$rollstat,v$rollname where v$https://www.doczj.com/doc/5c18919875.html,n=v$roll ; NAME SHRINKS
---------------- ----------
SYSTEM 0
RBS0 100
RBS1 1
若动态分配次数较多可增大回滚段的初始容量。
ALTER ROLLBACK SEGMENT RBS0
STORAGE (inittial 20k optimal 40k next 10k maxextents 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 69616 bytes
Variable Size 90701824 bytes
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
----------------------------------
100parse_count
90execute_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 * from v$sgastat where name=?free memory?;
当然，如果共享池满了并不一定存在问题，只要上面所说的比率接近于1，就不需要增加
共享池大小；
如果自由内存接近于0而且库快存或数据字典快存的命中率小于0.95，那么需要增加共享池的大小。
●调整Library Cache
1．检查库快存的活动
select sum(pins) “Executions”,sum(reloads)”Cache Misses while Executing” from
v$librarycache ;
“Executions”列指明sql语句，pl/sql块和实体定义被访问执行了的次数，”Cache Misses while Executing”指明其中没有命中的次数。
2．减少库快存的非命中
1、分配更多的库快存（可以增加初始化参数shared pool size的值；为了利用增加的共享
sql区，增加初始化参数open cursors的值）；
2、尽可能使用标准的sql语句（sql语句或pl/sql块必须一致，包括字符和空格；sql语句
或pl/sql块中对schema实体的引用必须解析到同一schema的同一对象；sql语句中试
用的变量的名字和数据类型必须匹配；sql语句必须使用相同的优化方法和优化目
标）；
尽可能使用标准的sql语句，策略：
●语句中尽量使用变量而不要使用常量
●确保应用用户不会改变优化方法和目标
●标准化的变量命名和空格转换
●尽可能使用存储过程
3、使用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 where name=?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。
优化共享池：
用以下语句检测命中率：
select sum(pins)"pinned",sum(reloads)"reloaded" from v$librarycache
pinned reloaded
---------- ---------
2474 17
命中率＝pinned/ reloaded
若命中率低可增大INIT.ORA中SHARED_POOL_SIZE的值。
调整快速缓存
●利用缓存的命中率评价快速缓存的活动
select name,value from v$sysstat where name in (…db block gets?,?consistent_gets?,?physical reads‘);
db block gets, consistent_gets其和为数据请求总数，physical reads为数据请求导致访问磁盘数据文件的总数。
增加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为增加的命中次数）：Hit Ratio=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)”Buffer Cache 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时，缓存非命中的增加数，使用如下语句：
SELECT SUM(COUNT) ACM FROM V$CURRENT_BUCKET WHERE ROWNUM>=90 ;
计算命中率（ACM为增加的非命中次数）：Hit Ratio=1-(physical reads + ACM/(db block gets+consistent gets));
也可以将100个缓冲区分为4个25个缓冲区段：
select
25*TRUNC(ROWNUM/25)+1||?to?||25*(TRUNC(ROWNUM/25+1)”Interval”,sum(count)”Buffe r Cache Hits” from v$CURRENT_BUCKET where rownum>0 group by
TRUNC(ROWNUM/25));
其中INTERVAL为缓冲区段，BUFFER CACHE HITS为对应段的缓存命中率。
优化数据库缓冲区高速缓存:
select name,value from v$sysstat where name in ('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 block gets + consisten gets))
若命中率低可以加大INITXXX.ORA中DB_BLOCK_BUFFER的值。
调整多缓冲池
●多缓冲池特性概述
可以使用keep缓冲池来维护缓存中的实体，使用recycle缓冲池来防止实体占用缓存中不必要的空间；
可以将大的数据段置于独立的recycle缓存中，将小的数据段置于独立的keep缓存中。
●何时使用多缓冲池
1．查找oracle内部实体数据段的数目：
select data_object_id,object_type from user_objects where object_name=?
2．查找对应该段名的缓存中的缓冲区数：
select count(*) buffers from x$bh where obj=
3．查找实例中的缓冲区总数：
select value “total buffers ” from v$parameter where name=?db_block_buffers? ;
4．计算当前有指定段使用的缓存所占的比率:
%cache used by segment_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.K EEP缓冲池
使用keep缓冲池的目的是将实体保留在内存，避免I/O操作
select physical_reads,block_gets,consistent_gets from v$buffer_pool statistics where
name=?KEEP?;
计算缓冲池命中率的公式：Hit Ratio=1-physical_reads/(block_gets+consistent_gets)
2.R ecycle缓冲池
使用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竞争：
select child#,sleeps/gets ratio from v$latch_children where name=?cache buffers lru chain? ;
每个LRU latch的非命中率应少于1％，任何大于1％的latch说明存在竞争，通过如下语句查找出来：
select name from v$buffer_pool_statistics where lo_setid<=child_latch_number and
hi_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)"Read Requests",sum(getmisses)"Reads not in Memory" from v$rowcache ;
Read Requests Reads not in Memory
------------- -------------------
4764 145
命中率＝Read Requests/ 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性能。
优化数据库对象：
管理表的动态分配。
SELECT TABLESPACE_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、
OracleSQL性能优化方法 Oracle性能优化方法(SQL篇) (1) 1综述 (2) 2表分区的应用 (2) 3访咨询Table的方式 (3) 4共享SQL语句 (3) 5选择最有效率的表名顺序 (5) 6WHERE子句中的连接顺序． (6) 7SELECT子句中幸免使用’*’ (6) 8减少访咨询数据库的次数 (6) 9使用DECODE函数来减少处理时刻 (7) 10整合简单,无关联的数据库访咨询 (8) 11删除重复记录 (8) 12用TRUNCATE替代DELETE (9) 13尽量多使用COMMIT (9) 14运算记录条数 (9) 15用Where子句替换HA VING子句 (9) 16减少对表的查询 (10) 17通过内部函数提高SQL效率 (11) 18使用表的不名(Alias) (12) 19用EXISTS替代IN (12) 20用NOT EXISTS替代NOT IN (13) 21识不低效执行的SQL语句 (13) 22使用TKPROF 工具来查询SQL性能状态 (14) 23用EXPLAIN PLAN 分析SQL语句 (14) 24实时批量的处理 (16)
1综述 ORACLE数据库的性能调整是个重要，却又有难度的话题，如何有效地进行调整，需要通过反反复复的过程。在数据库建立时，就能依照顾用的需要合理设计分配表空间以及储备参数、内存使用初始化参数，对以后的数据库性能有专门大的益处，建立好后，又需要在应用中不断进行应用程序的优化和调整，这需要在大量的实践工作中不断地积存体会，从而更好地进行数据库的调优。 数据库性能调优的方法 ●调整内存 ●调整I/O ●调整资源的争用咨询题 ●调整操作系统参数 ●调整数据库的设计 ●调整应用程序 本文针对应用程序的调整，来讲明对数据库性能如何进行优化。 2表分区的应用 关于海量数据的表，能够考虑建立分区以提高操作效率。建立分区一样以关键字为分区的标志，也能够以其他字段作为分区的标志，但效率不如关键字高。建立分区的语句在建表时能够进行讲明： create table TABLENAME(
（O管理）ORACLESL性能优化(内部培训资料)
ORACLESQL性能优化系列(一) 1.选用适合的ORACLE优化器 ORACLE的优化器共有3种: a.RULE(基于规则) b.COST(基于成本) c.CHOOSE(选择性) 设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS.你当然也在SQL句级或是会话(session)级对其进行覆盖. 为了使用基于成本的优化器(CBO,Cost-BasedOptimizer),你必须经常运行analyze命令,以增加数据库中的对象统计信息(objectstatistics)的准确性. 如果数据库的优化器模式设置为选择性(CHOOSE),那么实际的优化器模式将和是否运行过analyze命令有关.如果table已经被analyze过,优化器模式将自动成为CBO,反之,数据库将采用RULE形式的优化器. 在缺省情况下,ORACLE采用CHOOSE优化器,为了避免那些不必要的全表扫描(fulltablescan),你必须尽量避免使用CHOOSE优化器,而直接采用基于规则或者基于成本的优化器.
2.访问Table的方式 ORACLE采用两种访问表中记录的方式: a.全表扫描 全表扫描就是顺序地访问表中每条记录.ORACLE采用一次读入多个数据块(databaseblock)的方式优化全表扫描. b.通过ROWID访问表 你可以采用基于ROWID的访问方式情况,提高访问表的效率,,ROWID包含了表中记录的物理位置信息..ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系.通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高. 3.共享SQL语句 为了不重复解析相同的SQL语句,在第一次解析之后,ORACLE将SQL语句存放在内存中.这块位于系统全局区域SGA(systemglobalarea)的共享池(sharedbufferpool)中的内存可以被所有的数据库用户共享.因此,当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同,ORACLE就能很快获得已经被解析的语句以及最好的执行路
ORACLE 数据库性能优化 参考书目： 《ORACLE 9i Database Performance Tuning Guide and Reference》《ORACLE 9i Database Reference》 《ORACLE 9i SQL Reference》 《ORACLE 9i Database Administrator’s Guide》
一、数据库实例创建过程参数确定 在创建数据库实例过程中，需要确定以下几个参数： 1. 数据块大小（DB_BLOCK_SIZE） 该参数指明了ORACLE所处理的数据存贮于数据文档以及SGA内存中的数据块大小。 该参数的可选择的范围为:4k,8k,16k,32k,64k。对于OLTP系统而言，取值可以为4K或8K，对于DSS系统而言，则可以取较大的数据，如32K或64K 建议统一取8K（即8192） 说明 DB_BLOCK_SIZE的大小将影响创建表时的EXTENT的大小。例如指定db_block_size=16K,某表空间的EXTENT MANAGEMENT 为local autoallocate，则其系统将extent的大小最小指定为1M.所以将可能导致空间的浪费。 2. 字符集(Character set) 该参数确定数据库以何种字符集来存贮CHAR以及V ARCHAR、V ARCHAR2等字符类型的值。对于ORACLE数据字典中的字符（如表及字段的COMMENT 内容）具有同样的作用。因此需要考虑如字符集的使用。对于国际项目，因为数据库中的comment内容（包括表及字符、存贮过程中的中文字符等内容）可能性需要以中文存贮，而用户业务数据使用的字符可能性是使用本地的语言，基于此，该参数需要选择支持UNICODE的字符编码的字符集。目前ORACLE9i支持以下二种UNICODE字符集： ?UTF8 ?AL32UTF8 建议统一取AL32UTF8
Oracle SQL性能优化方法探讨 Oracle性能优化方法(SQL篇) (1) 1综述 (2) 2表分区的应用 (2) 3访问Table的方式 (3) 4共享SQL语句 (3) 5选择最有效率的表名顺序 (5) 6WHERE子句中的连接顺序． (6) 7SELECT子句中幸免使用’*’ (6) 8减少访问数据库的次数 (6) 9使用DECODE函数来减少处理时刻 (7) 10整合简单,无关联的数据库访问 (8) 11删除重复记录 (8) 12用TRUNCATE替代DELETE (9) 13尽量多使用COMMIT (9) 14计算记录条数 (9) 15用Where子句替换HAVING子句 (9) 16减少对表的查询 (10) 17通过内部函数提高SQL效率 (11)
18使用表的不名(Alias) (12) 19用EXISTS替代IN (12) 20用NOT EXISTS替代NOT IN (13) 21识不低效执行的SQL语句 (13) 22使用TKPROF 工具来查询SQL性能状态 (14) 23用EXPLAIN PLAN 分析SQL语句 (14) 24实时批量的处理 (16)
1综述 ORACLE数据库的性能调整是个重要，却又有难度的话题，如何有效地进行调整，需要通过反反复复的过程。在数据库建立时，就能依照顾用的需要合理设计分配表空间以及存储参数、内存使用初始化参数，对以后的数据库性能有专门大的益处，建立好后，又需要在应用中不断进行应用程序的优化和调整，这需要在大量的实践工作中不断地积存经验，从而更好地进行数据库的调优。 数据库性能调优的方法 ●调整内存 ●调整I/O ●调整资源的争用问题 ●调整操作系统参数 ●调整数据库的设计 ●调整应用程序 本文针对应用程序的调整，来讲明对数据库性能如何进行优化。 2表分区的应用 关于海量数据的表，能够考虑建立分区以提高操作效率。建
1.ORACLE的优化器共有3种 A、RULE (基于规则) b、COST (基于成本) c、CHOOSE (选择性) 设置缺省的优化器，可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明，如RULE，COST，CHOOSE，ALL_ROWS，FIRST_ROWS 。你当然也在SQL句级或是会话(session)级对其进行覆盖。 为了使用基于成本的优化器(CBO，Cost-Based Optimizer) ，你必须经常运行analyze 命令，以增加数据库中的对象统计信息(object statistics)的准确性。 如果数据库的优化器模式设置为选择性(CHOOSE)，那么实际的优化器模式将和是否运行过analyze 命令有关。如果table已经被analyze过，优化器模式将自动成为CBO ，反之，数据库将采用RULE 形式的优化器。 在缺省情况下，ORACLE采用CHOOSE优化器，为了避免那些不必要的全表扫描(full table scan) ，你必须尽量避免使用CHOOSE优化器，而直接采用基于规则或者基于成本的优化器。 2.访问Table的方式 ORACLE 采用两种访问表中记录的方式： A、全表扫描 全表扫描就是顺序地访问表中每条记录。ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描。 B、通过ROWID访问表 你可以采用基于ROWID的访问方式情况，提高访问表的效率，ROWID包含了表中记录的物理位置信息。ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系。通常索引提供了快速访问ROWID的方法，因此那些基于索引列的查询就可以得到性能上的提高。 3.共享SQL语句 为了不重复解析相同的SQL语句，在第一次解析之后，ORACLE将SQL语句存放在内存中。这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享。因此，当你执行一个SQL语句(有时被称为一个游标)时，如果它和之前的执行过的语句完全相同，ORACLE就能很快获得已经被解析的语句以及最好的执行路径。ORACLE的这个功能大大地提高了SQL 的执行性能并节省了内存的使用。 可惜的是ORACLE只对简单的表提供高速缓冲(cache buffering)，这个功能并不适用于多表连接查询。 数据库管理员必须在init.ora中为这个区域设置合适的参数，当这个内存区域越大，就可以保留更多的语句，当然被共享的可能性也就越大了。 当你向ORACLE提交一个SQL语句，ORACLE会首先在这块内存中查找相同的语句。这里需要注明的是，ORACLE对两者采取的是一种严格匹配，要达成共享，SQL语句必须完全相同(包括空格，换行等)。 数据库管理员必须在init.ora中为这个区域设置合适的参数，当这个内存区域越大，就可以保留更多的语句，当然被共享的可能性也就越大了。 共享的语句必须满足三个条件： A、字符级的比较：当前被执行的语句和共享池中的语句必须完全相同。 B、两个语句所指的对象必须完全相同： C、两个SQL语句中必须使用相同的名字的绑定变量(bind variables)。 4.选择最有效率的表名顺序(只在基于规则的优化器中有效) ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表(基础表driving table)将被最先处理。在FROM子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。当ORACLE处理多个表时，会运用排序及合并的方式连接它们。首先，扫描第一个表(FROM子句中最后的那个表)并对记录进行派序，然后扫描第二个表(FROM子句中最后第二个表)，最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。 如果有3个以上的表连接查询，那就需要选择交叉表(intersection table)作为基础表，交叉表是指
ORACLE的优化器共有3种 A、RULE (基于规则) b、COST (基于成本) c、CHOOSE (选择性) 设置缺省的优化器，可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明，如RULE，COST，CHOOSE，ALL_ROWS，FIRST_ROWS 。你当然也在SQL句级或是会话(session)级对其进行覆盖。 为了使用基于成本的优化器(CBO， Cost-Based Optimizer) ，你必须经常运行analyze 命令，以增加数据库中的对象统计信息(object statistics)的准确性。 如果数据库的优化器模式设置为选择性(CHOOSE)，那么实际的优化器模式将和是否运行过analyze命令有关。如果table已经被analyze过，优化器模式将自动成为CBO ，反之，数据库将采用RULE形式的优化器。 在缺省情况下，ORACLE采用CHOOSE优化器，为了避免那些不必要的全表扫描(full table scan) ，你必须尽量避免使用CHOOSE优化器，而直接采用基于规则或者基于成本的优化器。 2.访问Table的方式 ORACLE 采用两种访问表中记录的方式： A、全表扫描 全表扫描就是顺序地访问表中每条记录。ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描。 B、通过ROWID访问表 你可以采用基于ROWID的访问方式情况，提高访问表的效率， ROWID 包含了表中记录的物理位置信息。ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系。通常索引提供了快速访问ROWID的方法，因此那些基于索引列的查询就可以得到性能上的提高。 3.共享SQL语句 为了不重复解析相同的SQL语句，在第一次解析之后，ORACLE将SQL语句存放在存中。这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的存可以被所有的数据库用户共享。因此，当你执行一个SQL语句(有时被称为一个游标)时，如果它和之前的执行过的语句完全相同， ORACLE就能很快获得已经被解析的语句以及最好的执行路径。ORACLE的这个功能大提高了SQL的执行性能并节省了存的使用。 可惜的是ORACLE只对简单的表提供高速缓冲(cache buffering)，这个功能并不适用于多表连接查询。
y物理模型CheckList （Oracle，性能） 1. 系统级优化 数据库参数配置 合理分配SGA及其内部参数（经验值如下）： SGA=phy*（60%-80%） Share pool=SAG*45% DB Cache=SGA*45% Log Buffer: 1~3M 注：Oracle9i在Windows下有bug，是由Windows下的SGA最大 值有2G的限制造成的 注意调整process和open cursor参数，这两个参数直接影响 数据库的session量 分离表和索引：将表和索引建立在不同的表空间，决不要将 不属于Oracle内部系统的对象存放到SYSTEM表空间。同 时，确保数据表空间和索引表空间置于不同的硬盘，减少I/O 竞争； 如果是企业版数据库，大表可以考虑采取分区存储措施，提 高系统的性能； 优化Export和Import工作：使用较大的BUFFER(比如10MB , 10,240,000)可以提高EXPORT和IMPORT的速度 定期分析查询计划，提高数据库的性能；
2. 索引相关 要对经常查询的字段建立索引，但是由于索引管理的开销， 在增删改操作频繁的情况下避免建立不必要的索引； 对于只读或者接近只读的场合，如数据仓库，对于势值比较 小的列可以考虑使用bitmap索引； 如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引. 3. SQL相关 Oracle的From子句表的顺序：记录越多的表放在越前面 （左）； Oracle的where子句表达式的顺序：过滤掉最大数目记录的条 件放到where子句的末尾； Select子句中避免使用‘*’，增加了查询表的列的开销； 在执行结果等效的情况下，使用Truncate代替Delete； 为了在查询过程中要尽量使用索引，对于like语句避免使用 右匹配或者中间匹配的模糊查询； 将过滤条件尽可能放到Where子句中，而不是放到Having子 句中； 在SQL语句中，要减少对表的查询，特别是在含有子查询的 SQL子句中； 使用表的别名可以减少解析的时间并避免引起歧义； 使用exists替代in； 用NOT EXISTS替代NOT IN； 通常情况下，采用表连接的方式比exists更有效率； 当提交一个包含一对多表信息(比如部门表和雇员表)的查询
个人理解，数据库性能最关键的因素在于IO，因为操作内存是快速的，但是读写磁盘是速度很慢的，优化数据库最关键的问题在于减少磁盘的IO，就个人理解应该分为物理的和逻辑的优化，物理的是指oracle产品本身的一些优化，逻辑优化是指应用程序级别的优化物理优化： 一、优化内存
V$ROWCACHE视图结构
3.管理员可以通过下述语句来查看数据缓冲区的使用情况 select name,value from v$sysstat where name in ('db block gets', 'consistent gets ', 'physical reads'); 数据缓冲区使用命中率（physical reads除以db block gets加consistent gets之和)一定要小于10%，否则需要增加数据缓冲区大小 4.管理员可以通过执行下述语句，查看日志缓冲区的使用情况 select name,value from v$sysstat where name in ('redo entries','redo log space requests') 根据查询出的结果可以计算出日志缓冲区的申请失败率：requests除以entries 申请失败率应该解决与0，否则说明日志缓冲区开设太小，需要增加Oracle数据库的日志缓冲区 二、物理I/0的优化 1.在磁盘上建立数据文件前首先运行磁盘碎片整理程序 为了安全地整理磁盘碎片，需关闭打开数据文件的实例，并且停止服务。如果有足够的连续磁盘空间建立数据文件，那么就容易避免数据文件产生碎片。 2.不要使用磁盘压缩（Oracle文件不支持磁盘压缩） 3.不要使用磁盘加密
ORACLE SQL性能优化 我要讲的题目是Oracle SQL性能优化，只是Oracle性能优化中的一项。Oracle的性能优化包含很多方面，比如调整物理存取，调整逻辑存取，调整内存使用，减少网络流量等。这里选择SQL性能优化是因为这部分内容我们测试人员最容易接触到，另外开发人员写SQL脚本时有时很随意，不知不觉就会造成程序性能上的下降。 1．选择最有效率的表名顺序(只在基于规则的优化器中有效) ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基 础表.当ORACLE处理多个表时, 会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描 第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出 的记录与第一个表中合适记录进行合并. 例如: 表 TAB1 16,384 条记录 表 TAB2 1 条记录 选择TAB2作为基础表 (最好的方法) select count(*) from tab1,tab2 执行时间0.96秒 选择TAB2作为基础表 (不佳的方法)
select count(*) from tab2,tab1 执行时间26.09秒 如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表. 例如: EMP表描述了LOCATION表和CATEGORY表的交集. SELECT * FROM LOCATION L , CATEGORY C, EMP E WHERE E.EMP_NO BETWEEN 1000 AND 2000 AND E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN 将比下列SQL更有效率 SELECT * FROM EMP E , LOCATION L , CATEGORY C WHERE E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN AND E.EMP_NO BETWEEN 1000 AND 2000 2．WHERE子句中的连接顺序． ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.
--数据库巡检或性能优化方法各异，但首要的是要发现数据库性能瓶颈，系统自带的statspack,或awr太耗时， --以下是本人常用的方法，共享之 --1、查询数据库等待事件top10,关注前前几个等待事件，关注前三个等待事件是否有因果或关联关系 --oracle 9i select t2.event,round(100*t2.time_waited/(t1.w1+t3.cpu),2) event_wait_percent from ( SELECT SUM(time_waited) w1 FROM v$system_event WHERE event NOT IN ('smon timer','pmon timer','rdbms ipc message','Null event','parallel query dequeue','pipe get', 'client message','SQL*Net message to client','SQL*Net message from client','SQL*Net more data from client', 'dispatcher timer','virtual circuit status','lock manager wait for remote message','PX Idle Wait', 'PX Deq: Execution Msg','PX Deq: Table Q Normal','wakeup time manager','slave wait','i/o slave wait', 'jobq slave wait','null event','gcs remote message','gcs for action','ges remote message','queue messages') ) t1, (select * from ( select t.event,t.total_waits,t.total_timeouts,t.time_waited,t.average_wait,rownum num from (select event,total_waits,total_timeouts,time_waited,average_wait from v$system_event where event not in ('smon timer','pmon timer','rdbms ipc message','Null event','parallel query dequeue','pipe get', 'client message','SQL*Net message to client','SQL*Net message from client','SQL*Net more data from client', 'dispatcher timer','virtual circuit status','lock manager wait for remote message','PX Idle Wait', 'PX Deq: Execution Msg','PX Deq: Table Q Normal','wakeup time manager','slave wait','i/o slave wait', 'jobq slave wait','null event','gcs remote message','gcs for action','ges remote message','queue messages') order by time_waited desc ) t) where num<11) t2, (SELECT VALUE CPU FROM v$sysstat WHERE NAME LIKE 'CPU used by this session' ) t3 --oracle10g select t2.event,round(100*t2.time_waited/(t1.w1+t3.cpu),2) event_wait_percent from ( SELECT SUM(time_waited) w1 FROM v$system_event WHERE event NOT IN ('smon timer','pmon timer','rdbms ipc message','Null event','parallel query dequeue','pipe get','client message','SQL*Net message to client','SQL*Net message from client','SQL*Net more data from client','dispatcher timer','virtual circuit status','lock manager wait for remote message','PX Idle Wait','PX Deq: Execution Msg','PX Deq: Table Q Normal','wakeup time manager','slave wait', 'i/o slave wait','jobq slave wait','null event','gcs remote message','gcs for action','ges remote
个人理解，数据库性能最关键的因素在于IO，因为操作存是快速的，但是读写磁盘是速度很慢的，优化数据库最关键的问题在于减少磁盘的IO，就个人理解应该分为物理的和逻辑的优化，物理的是指oracle产品本身的一些优化，逻辑优化是指应用程序级别的优化 物理优化： 一、优化存
3.管理员可以通过下述语句来查看数据缓冲区的使用情况 select name,value from v$sysstat where name in('db block gets','consistent gets','physica l reads'); 数据缓冲区使用命中率（physical reads除以db block gets加consistent gets之和)一定要小于10%，否则需要增加数据缓冲区大小 4.管理员可以通过执行下述语句，查看日志缓冲区的使用情况 select name,value from v$sysstat where name in ('redo entries','redo log space requests') 根据查询出的结果可以计算出日志缓冲区的申请失败率：requests除以entries 申请失败率应该解决与0，否则说明日志缓冲区开设太小，需要增加Oracle数据库的日志缓冲区 二、物理I/0的优化 1.在磁盘上建立数据文件前首先运行磁盘碎片整理程序 为了安全地整理磁盘碎片，需关闭打开数据文件的实例，并且停止服务。如果有足够的连续磁盘空间建立数据文件，那么就容易避免数据文件产生碎片。 2.不要使用磁盘压缩（Oracle文件不支持磁盘压缩） 3.不要使用磁盘加密 加密像磁盘压缩一样加了一个处理层，降低磁盘读写速度。如果担心自己的数据可能泄露，可以使用dbms_obfuscation包和label security选择性地加密数据的敏感部分 4.使用RAID raid使用应注意： 选择硬件raid超过软件raid；日志文件不要放在raid5卷上，因为raid5读性能高而写性能差；把日志文件和归档日志放在与控制文件和数据文件分离的磁盘控制系统上 5.分离页面交换文件到多个磁盘物理卷 跨越至少两个磁盘建立两个页面文件。可以建立四个页面文件并在性能上受益，确保所有页面文件的大小之和至少是物理存的两倍。
理解Oracle的日志机制 ? Oracle的日志是用来记录用户对数据库的改变，这样，当出现服务器硬件故障或者用户错误而丢失数据时，可以通过重做这些日志来恢复已提交的事务，Oracle日志机制包含以下组件： ?日志缓存SGA的一部分，用于缓存服务器进程产生的日志，包括DML和DDL； ? LGWR进程这个后台进程负责将日志缓存的数据写到联机日志文件，每个实例只有一个； ?数据库检查点检查点用于同步数据文件和日志文件，一个检查点事件的完成，代表在这个事件开始之前发生的所有对数据文件的改变都已实际记录到了数据文件，数据库在这个时间点是一致的，在实例恢复的时候，只有在最后一个检查点之后的日志才需要重做； ?联机日志文件用于存放从日志缓存中写出的日志数据，每个数据库最少需要两个日志文件，当前日志文件填满以后，发生日志切换，然后才可以继续写下一个日志文件； ?日志归档LGWR写满所有组的联机日志文件以后，会回头再写第一个组的日志文件，在非归档模式下，被重用的日志文件中的日志会被丢弃，在归档模式下，日志文件被重用前会被ARC0进程复制到归档日志文件； ? 一些可选的日志机制，如归档和Standby，因为附加的I/O会降低系统的性能，同时提供了可靠的灾难恢复能力，不建议因这些性能的下降而关闭生产系统的归档功能。 调整日志缓存 ? 日志缓存的管理机制可以类似理解成一个漏斗，日志数据不断地从漏斗上方加入，然后偶尔打开漏斗下方的开关将加入的数据清空，这个开关就是LGWR进程，为了日志缓存有空间容纳不断加进来的日志数据，LGWR在下面列出的任何一个条件下都会执行写出日志缓存的操作： ?应用程序发出Commit命令时； ?三秒间隔已到时； ?日志缓存三分之一满时； ?日志缓存达到1M时； ?数据库检查点发生时； ? 测量日志缓存的性能通过服务器进程放置日志条到日志缓存时发生等待的次数和时间来测量； Select Name, Value From V$sysstat Where Name In ('redo entries', 'redo buffer allocation retries','redo log space requests'); redo entries 服务器进程放进日志缓存的日志条的总数量； redo buffer allocation retries 服务器放置日志条时必须等待然后再重试的次数； redo log space requests LGWR进程写出日志缓存时等待日志切换的次数； 这个查询用于计算日志缓存重试率，这个比率应该小于百分之一； Select Retries.Value / Entries.Value "Redo log Buffer Retry Ratio" From V$sysstat Entries, V$sysstat Retries Where https://www.doczj.com/doc/5c18919875.html, = 'redo entries' And https://www.doczj.com/doc/5c18919875.html, = 'redo buffer allocation retries'; 这个查询用来显示哪些会话的LGWR正在进行写等待；
（1）选择最有效率的表名顺序(只在基于规则的优化器中有效)： ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表 driving table)将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表. （2） WHERE子句中的连接顺序．： ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE 子句的末尾. （3） SELECT子句中避免使用 * ： ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间 （4）减少访问数据库的次数： ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等； （5）在SQL*Plus , SQL*Forms和Pro*C中重新设置ARRAYSIZE参数, 可以增加每次数据库访问的检索数据量 ,建议值为200 （6）使用DECODE函数来减少处理时间： 使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表. （7）整合简单,无关联的数据库访问： 如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系) （8）删除重复记录： 最高效的删除重复记录方法 ( 因为使用了ROWID)例子： DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID)FROM EMP X WHERE X.EMP_NO = E.EMP_NO); （9）用TRUNCATE替代DELETE： 当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短. 译者按: TRUNCATE只在删除全表适 用,TRUNCATE是DDL不是DML) （10）尽量多使用COMMIT： 只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求
1.选用适合的ORACLE优化器 ORACLE的优化器共有3种: 1.RULE(基于规则) 2.COST(基于成本) 3.CHOOSE(选择性) 设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如 RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS . 你当然也在SQL 句级或是会话(session)级对其进行覆盖. 为了使用基于成本的优化器(CBO, Cost-Based Optimizer) , 你必须经常运行analyze 命令,以增加数据库中的对象统计信息(object statistics)的准确性. 如果数据库的优化器模式设置为选择性(CHOOSE),那么实际的优化器模式将和是否运行过analyze命令有关. 如果table已经被analyze过, 优化器模式将自动成为CBO , 反之,数据库将采用RULE形式的优化器. 在缺省情况下,ORACLE采用CHOOSE优化器, 为了避免那些不必要的全表扫描(full table scan) , 你必须尽量避免使用CHOOSE优化器,而直接采用基于规则或者基于成本的优化器. 2.访问Table的方式 ORACLE 采用两种访问表中记录的方式: 1.全表扫描
全表扫描就是顺序地访问表中每条记录. ORACLE 采用一次读入多个数据块(database block)的方式优化全表扫描. 2.通过ROWID访问表 你可以采用基于ROWID的访问方式情况,提高访问表的效率, , ROWID包含了表中记录的物理位置信息..ORACLE 采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系. 通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高. 3.共享SQL语句 为了不重复解析相同的SQL语句,在第一次解析之后, ORACLE将SQL语句存放在内存中.这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享. 因此,当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同, ORACLE就能很快获得已经被解析的语句以及最好的执行路径. ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用. 可惜的是ORACLE只对简单的表提供高速缓冲(cache buffering) ,这个功能并不适用于多表连接查询.
ORACLE SQL性能优化系列 0ORACLE的优化器共有3种: a. RULE (基于规则) b. COST (基于成本) c. CHOOSE (选择性) 设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS . 你当然也在SQ L句级或是会话(session)级对其进行覆盖. 为了使用基于成本的优化器(CBO, Cost-Based Optimizer) , 你必须经常运行analyze 命令,以增加数据库中的对象统计信息(object statist ics)的准确性. 如果数据库的优化器模式设置为选择性(CHOOSE),那么实际的优化器模式将和是否运行过analyze命令有关. 如果table已经被analyze过, 优化器模式将自动成为CBO , 反之,数据库将采用RULE形式的优化器. 在缺省情况下,ORACLE采用CHOOSE优化器, 为了避免那些不必要的全表扫描(full table scan) , 你必须尽量避免使用CHOOSE优化器,而直接采用基于规则或者基于成本的优化器. 1.访问Table的方式 ORACLE 采用两种访问表中记录的方式: a.全表扫描 全表扫描就是顺序地访问表中每条记录. ORACLE采用一次读入多个数据块(dat abase block)的方式优化全表扫描. b.通过ROWID访问表
你可以采用基于ROWID的访问方式情况,提高访问表的效率, , ROWID包含了表中记录的物理位置信息..ORACLE采用索引(INDEX)实现了数据和存放数据的物 理位置(ROWID)之间的联系. 通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高. 2.共享SQL语句 为了不重复解析相同的SQL语句,在第一次解析之后, ORACLE将SQL语句存放在内存中.这块位于系统全局区域SGA(system global area)的共享池(shared bu ffer pool)中的内存可以被所有的数据库用户共享. 因此,当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同, ORACLE 就能很快获得已经被解析的语句以及最好的执行路径. ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用. 可惜的是ORACLE只对简单的表提供高速缓冲(cache buffering) ,这个功能并不适用于多表连接查询. 数据库管理员必须在init.ora中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性也就越大了. 当你向ORACLE 提交一个SQL语句,ORACLE会首先在这块内存中查找相同的语句. 这里需要注明的是,ORACLE对两者采取的是一种严格匹配,要达成共享,SQL语句必须完全相同(包括空格,换行等). 共享的语句必须满足三个条件: A. 字符级的比较: 当前被执行的语句和共享池中的语句必须完全相同. 例如: SELECT * FROM EMP;
知也无涯 Oracle RAC 11g r2查询太慢 --------------------------------------------------- Oracle RAC 11g r2查询太慢 Problem Description --------------------------------------------------- Redhat 5 双机 测试1：双实例，ASM磁盘组包含3个磁盘（SAN）。在其中一个实例中执行：SELECT c.operaccount || ':' || c.PASSWORD || '@' || a.PATH, a.dll, a.description, '1.gif' FROM hcs2000.dllnames a, hcs2000.operdllnames b, hcs2000.operaccount c WHERE a.dllnameid = b.dllnameid AND b.operid = c.operid AND upper(c.operaccount) = USER ORDER BY a.dllnameid; 第一次查询，25秒。第二次查询，3秒。第三次查询，1.6秒。过10分钟后查询，26秒。 测试2：在其中一台主机上创建基于ASM磁盘组的单个实例， 第一次查询，14秒。第二次查询，3秒。第三次查询，0.7秒。第四次查询，3.5秒。 测试3：在其中一台主机上创建基于文件系统的单个实例， 第一次查询，5秒。第二次查询，2.2秒。第三次查询，2.1秒。 测试4：在PC的VMware虚拟机里面单实例查询，只需0.001秒或0秒。 测试1中的查询太慢了，请问怎么查看问题原因，如何调优？ Dear customer, Array请您执行以下动作： 如果可以，请在您提到的4个场景下都生成以下文件，并请添加您 的说明后，作为附件更新到SR上： ACTION PLAN -----------------------