分类： Oracle
2009-03-26 21:13:19
ora_perform.sql脚本是用来监测oracle数据库性能的工具。(该脚本附录在最后面)
使用方法：
将ora_perform.sql文件放入某一磁盘，如c: ；启动SQLPLUS，输入用户名/口令及连接名与你将要监测的数据库建立起连接；执行命令：@c:\ora_perform.sql （注意文件所在位置）。
需要输入参数：loops,interval的值。Loops指要做多少次监测操作；interval指定每次监测之间所间隔的时间，单位为秒。在监测过程中SQLPLUS呈现一种停止状态。你不用去管它，监测结束后结果后会被输出到SQLPLUS界面及c:\ora_perform_result.txt文件中。
建议在业务操作较为频繁的时候来做监测。loops的值大一些，如10次左右或更大；interval的值建议为900，也就是间隔时间为15分钟。这样整个监测过程需要花费大约10*900(秒)，即2.5个小时左右。结果输出后，取结果重覆出现频率较高的几组值进行分析。
结果说明：
Buffer Cache Hit Ratio
说明：数据缓冲区的命中率。SQL语句执行时，Server进程首先会去数据缓冲区中找返回给用户的数据值，当缓冲区中没有所要的数据时通过DBWR进程将数据从数据文件中读取写入数据冲区再传给用户。命中率是指未发生物理文件读取的数据请求在所有数据请求中所占比例。
正常值：>=90%
优化方法：
增加初始化参数：db_block_buffers的值。增加的前提是目前有足够的剩余物理内存。
设置多缓冲池。将缓冲池分为keep区，recycle区,default区，三个区的大小总合为db_block_buffers的值，修改init
...
DB_BLOCK_BUFFERS = 20000
DB_BLOCK_LRU_LATCHES = 6
BUFFER_POOL_KEEP=(BUFFERS:14000,LRU_LATCHES:1)
BUFFER_POOL_RECYCLE=(BUFFERS:2000,LRU_LATCHES:3)
...
注：buffer_pool_buffers=2000*3+14000*1=20000
keep区用于保留会再一次使用的对象；recycle区用于存放很少被重复使用的对象。所以我们可以指定经常重复使用的表、索引等对象的缓存区域为keep区，以减少I/O操作。指定方法如下：
alter table bd_accsubj storage(buffer_pool keep);
Library Cache Hit Ratio；Library Cache Reload Ratio
说明：library cache用于存放SQL、PLSQL及其分析树及他们的执行方案。Library cache hit ratio指所发送的SQL语句在library cache中能找到它的执行方案的机率；library cache reload指所发送的SQL语句在library cache曾经有过同样的语句及它的执行方案，但被移出了library cache，这些语句所占的比率即为library cache reload ratio。
正常值：Library Cache Hit Ratio>=90%
Library Cache Reload Ratio<=1% （最好为0）
优化方法：增加初始化参数：shared_pool_size的值。
Dictionary Cache Getmisses Ratio
说明：dictionary cache用于存放数据库对象如表、视图等的结构定义。当SQL语句中用到数据库对象时，server进程要去dictionary cache中对比该对象的定义，当找不到时会从数据文件中读取入dictionary cache，dictionary cache getmisses ratio反映的就是找不到的比率。
正常值：<15%
优化方法：增加初始化参数：shared_pool_size的值
Rollback Segment Wait Ratio
说明：事务在请求回滚段时发生等待的比率。
正常值：<1%
优化方法：增加新的回滚段。所有回滚段的存储参数应一致。
对于我们的应用建多少个回滚段合适呢？最大并发用户数除以4或除以2即可。对于oracle9i系统可自动管理回滚段，建库后无需建立新的回滚段。
Sorts to Disk Ratio
说明：当创建索引；SQL语句中含有order by ,group by子句；SQL语句中含有distinct,union,intersect,minus等操作时均会用到sort(排序)，这些排序会在SGA区中的sort area中进行。当sort area不够大时会将排序移入临时表空间文件中进行，这就会因发生了I/O操作而带来处理速度的降低。Sorts to disk ratio指进入临时表空间文件的排序占整个排序操作的比率。
正常值：<5%
优化方法：增加初始化参数sort_area_size及sort_area_retained_size的值。这两个参数的默认值为65536(字节)，应在监控过程中逐步增加这两个参数的值。
Cursor Usage Ratio
说明：该值是指监控过程中获得的数据库实际打开的游标数与初始化参数open_coursors值的比率。
正常值：<95%
优化方法：增加初始化参数open_cursors的值。调整应用代码及时关闭cursors来有效利用内存
Transaction Usage Ratio
说明：该值是指监控过程中获得的实际活动的事务数与初始化参数transactions值的比率。
正常值：<95%
优化方法：增加初始化参数transactions的值
Number of users awaiting lock
说明：反映了发生锁定等待的session（会话）数。
正常值：=0
解决办法：当遇到锁定等待时只能将产生锁定的session杀掉。可用dba studio工具中的“例程”->“会话”工具来做，也可用命令：alter system kill session ‘sid,serial#’; 来做。我们如何知道产生锁定的session的sid及serial#值呢？用以下命令来获得。
SELECT S.SID SESSION_ID, S.SERIAL# ,S.USERNAME, DECODE(L.LMODE, 0, ‘None’, 1, ‘Null’, 2, ‘Row-S (SS)’, 3, ‘Row-X (SX)’, 4, ‘Share’, 5, ‘S/Row-X (SSX)’, 6, ‘Exclusive’, TO_CHAR(L.LMODE)) MODE_HELD, DECODE(L.REQUEST, 0, ‘None’, 1, ‘Null’, 2, ‘Row-S (SS)’, 3, ‘Row-X (SX)’, 4, ‘Share’, 5, ‘S/Row-X (SSX)’, 6, ‘Exclusive’, TO_CHAR(L.REQUEST)) MODE_REQUESTED, O.OWNER||’.’||O.OBJECT_NAME||’ (‘||O.OBJECT_TYPE||’)’, S.TYPE LOCK_TYPE, L.ID1 LOCK_ID1, L.ID2 LOCK_ID2 FROM V$LOCK L, SYS.DBA_OBJECTS O, V$SESSION S WHERE L.SID = S.SID AND L.ID1 = O.OBJECT_ID;
Redo Log Space Waittime
说明：该值是指用户进程等待使用日志缓冲区空间的等待时间。
正常值：=0
优化方法：增加初始化参数log_buffer的值。
SGA Free Space Ratio
说明：该值显示了当前SGA区的空闲空间占SGA区的比率。
正常值：约为5%
优化方法：当该值大于10%以上时，可将数据库SGA区释放出一部份供其它应用追加内存；当该值小于5%时，说明SGA区较小。此时我们要结合上面提到的buffer cache、library cache、dictionary cache、redo log space项目来看哪些区上需要增加值。
性能监控ora_perform.sql脚本(winddows版本):
rem
rem Procedure perform.sql
rem
rem Description This PL/SQL script monitors a database.
rem The following are monitored :-
rem
rem Buffer Cache
rem Library Cache
rem Dictionary Cache
rem Rollback Segment Waits
rem Sorts to disk
rem Cursor Usage
rem Number of Locks
rem Redo Log Space Waits
rem session wait
rem SGA Free Space Ratio
rem
rem
rem Argument(s) Number of loops and interval seconds
rem
rem (Suggested loops >5 interval 900).
rem
rem Author wangshu, 28/03/2003
set echo off;
set serveroutput on size 10000;
spool c:\ora_perform_result.txt;
declare
/* Fetched from database */
v_fetch_consistent_gets number :=0;
v_fetch_db_block_gets number :=0;
v_fetch_physical_reads number :=0;
v_fetch_rollback_gets number :=0;
v_fetch_rollback_waits number :=0;
v_fetch_sorts_disk number :=0;
v_fetch_sorts_memory number :=0;
v_fetch_redo_space_waittime number :=0;
v_fetch_enqueue_waits number :=0;
v_fetch_library_pins number :=0;
v_fetch_library_pinhits number :=0;
v_fetch_library_reloads number :=0;
v_fetch_dictionary_gets number :=0;
v_fetch_dictionary_misses number :=0;
/* parameters from INIT.ORA */
v_open_cursors_parameter number;
v_transactions_parameter number;
/* Calculated values */
v_logical_reads number;
v_consistent_gets number;
v_db_block_gets number;
v_physical_reads number;
v_rollback_gets number;
v_rollback_waits number;
v_sorts_disk number;
v_sorts_memory number;
v_redo_space_waittime number;
v_enqueue_waits number;
v_library_pins number;
v_library_pinhits number;
v_library_reloads number;
v_dictionary_gets number;
v_dictionary_misses number;
/* Fetched from database */
v_open_cursors_current number;
v_transactions number;
v_total_locks number;
v_sga_free number;
v_sga_total number;
/* Store last values for calculations */
v_last_consistent_gets number;
v_last_db_block_gets number;
v_last_physical_reads number;
v_last_rollback_gets number;
v_last_rollback_waits number;
v_last_sorts_disk number;
v_last_sorts_memory number;
v_last_redo_space_waittime number;
v_last_enqueue_waits number;
v_last_library_pins number;
v_last_library_pinhits number;
v_last_library_reloads number;
v_last_dictionary_gets number;
v_last_dictionary_misses number;
/* Ratio */
v_buffer_cache_hit_ratio integer;
v_rollback_wait_ratio integer;
v_sorts_disk_ratio integer;
v_open_cursors_ratio integer;
v_library_pinhits_ratio integer;
v_library_reloads_ratio integer;
v_dictionary_cache_ratio integer;
v_transactions_ratio integer;
v_sga_free_ratio integer;
/* General */
v_counter integer;
v_interval integer;
v_date_time varchar2(20);
procedure db_output (message in varchar) is
begin
dbms_output.put_line(message);
end;
procedure get_param is
begin
select value into v_open_cursors_parameter from v$parameter where name = ‘open_cursors’;
select value into v_transactions_parameter from v$parameter where name = ‘transactions’;
end;
procedure get_stats is
begin
v_last_consistent_gets := v_fetch_consistent_gets;
v_last_db_block_gets := v_fetch_db_block_gets;
v_last_physical_reads := v_fetch_physical_reads;
v_last_library_pins := v_fetch_library_pins;
v_last_library_pinhits := v_fetch_library_pinhits;
v_last_library_reloads:=v_fetch_library_reloads;
v_last_dictionary_gets := v_fetch_dictionary_gets;
v_last_dictionary_misses := v_fetch_dictionary_misses;
v_last_rollback_gets := v_fetch_rollback_gets;
v_last_rollback_waits := v_fetch_rollback_waits;
v_last_sorts_disk := v_fetch_sorts_disk;
v_last_sorts_memory := v_fetch_sorts_memory;
v_last_enqueue_waits := v_fetch_enqueue_waits;
v_last_redo_space_waittime := v_fetch_redo_space_waittime;
select value into v_fetch_consistent_gets from v$sysstat where name = ‘consistent gets’;
select value into v_fetch_db_block_gets from v$sysstat where name = ‘db block gets’;
select value into v_fetch_physical_reads from v$sysstat where name = ‘physical reads’;
select sum(pinhits),sum(pins),sum(reloads) into v_fetch_library_pinhits,v_fetch_library_pins,v_fetch_library_reloads from v$librarycache;
select sum(gets),sum(getmisses) into v_fetch_dictionary_gets,v_fetch_dictionary_misses from v$rowcache;
select sum(waits),sum(gets) into v_fetch_rollback_waits,v_fetch_rollback_gets from v$rollstat;
select value into v_fetch_sorts_disk from v$sysstat where name = ‘sorts (disk)’;
select value into v_fetch_sorts_memory from v$sysstat where name = ‘sorts (memory)’;
select value into v_open_cursors_current from v$sysstat where name = ‘opened cursors current’;
select value into v_fetch_redo_space_waittime from v$sysstat where name = ‘redo log space wait time’;
select value into v_fetch_enqueue_waits from v$sysstat where name = ‘enqueue waits’;
select sum(xacts) into v_transactions from v$rollstat;
select count(lockwait) into v_total_locks from v$session where lockwait is not null;
select sum(bytes) into v_sga_total from v$sgastat;
select sum(bytes) into v_sga_free from v$sgastat where name=’free memory’;
end;
begin
get_param; /* Get Fixed parameters */
get_stats; /* Get Initial Values of statistics */
v_counter := &loops;
v_interval := &interval;
while v_counter > 0
loop
/* Sleep for more */
db_output(‘********************************************************’);
v_date_time := to_char(sysdate,’dd-mon-yy hh24:mi’);
db_output(‘Sleeping at ‘||v_date_time||’...’);
v_counter := v_counter - 1;
dbms_lock.sleep(v_interval);
/* Get statistics */
get_stats;
/* Check Buffer Cache Hit Ratio */
v_consistent_gets := v_fetch_consistent_gets - v_last_consistent_gets;
if v_consistent_gets < 0 then
v_consistent_gets := v_fetch_consistent_gets;
end if;
v_db_block_gets := v_fetch_db_block_gets - v_last_db_block_gets;
if v_db_block_gets < 0 then
v_db_block_gets := v_fetch_db_block_gets;
end if;
v_physical_reads := v_fetch_physical_reads - v_last_physical_reads;
if v_physical_reads < 0 then
v_physical_reads := v_fetch_physical_reads;
end if;
v_logical_reads := v_consistent_gets + v_db_block_gets;
if v_logical_reads < 1 then
v_logical_reads := 1;
end if;
v_buffer_cache_hit_ratio := (1-(v_physical_reads/v_logical_reads))*100;
db_output(‘Buffer Cache Hit Ratio is ‘||to_char(v_buffer_cache_hit_ratio)||’%’);
/* Check Library Cache */ v_library_pinhits := v_fetch_library_pinhits - v_last_library_pinhits;
if v_library_pinhits < 0 then
v_library_pinhits := v_fetch_library_pinhits;
end if;
v_library_pins := v_fetch_library_pins - v_last_library_pins;
if v_library_pins < 0 then
v_library_pins := v_fetch_library_pins;
end if;
if v_library_pins < 1 then
v_library_pins := 1;
end if;
v_library_reloads :=v_fetch_library_reloads-v_last_library_reloads;
if v_library_reloads <0 then
v_library_reloads :=v_fetch_library_reloads;
end if;
v_library_pinhits_ratio := ((v_library_pinhits * 100) / v_library_pins);
db_output(‘Library Cache Hit Ratio is ‘||to_char(v_library_pinhits_ratio)||’%’);
v_library_reloads_ratio :=((v_library_reloads*100)/v_library_pins); db_output(‘Library Cache Reload Ratio is ‘||to_char(v_library_reloads_ratio)||’%’);
/* Check Library Cache */ v_dictionary_misses := v_fetch_dictionary_misses - v_last_dictionary_misses;
if v_dictionary_misses < 0 then
v_dictionary_misses := v_fetch_dictionary_misses;
end if;
v_dictionary_gets := v_fetch_dictionary_gets - v_last_dictionary_gets;
if v_dictionary_gets < 0 then
v_dictionary_gets := v_fetch_dictionary_gets;
end if;
if v_dictionary_gets < 1 then
v_dictionary_gets := 1;
end if;
v_dictionary_cache_ratio := ((v_dictionary_gets * 100) / (v_dictionary_misses + v_dictionary_gets));
db_output(‘Dictionary Cache Hit Ratio is ‘||to_char(v_dictionary_cache_ratio)||’%’);
/* Check for Rollback segment waits */ v_rollback_waits := v_fetch_rollback_waits - v_last_rollback_waits;
if v_rollback_waits < 0 then
v_rollback_waits := v_fetch_rollback_waits;
end if;
v_rollback_gets := v_fetch_rollback_gets - v_last_rollback_gets;
if v_rollback_gets < 0 then
v_rollback_gets := v_fetch_rollback_gets;
end if;
if v_rollback_gets < 1 then
v_rollback_gets := 1;
end if;
v_rollback_wait_ratio := (v_rollback_waits * 100) / (v_rollback_gets);
db_output(‘Rollback Segment Wait Ratio is ‘||to_char(v_rollback_wait_ratio)||’%’);
/* Check sorts to disk */
v_sorts_disk := v_fetch_sorts_disk - v_last_sorts_disk;
if v_sorts_disk < 0 then
v_sorts_disk := v_fetch_sorts_disk;
end if;
v_sorts_memory := v_fetch_sorts_memory - v_last_sorts_memory;
if v_sorts_memory < 0 then
v_sorts_memory := v_fetch_sorts_memory;
end if;
if v_sorts_memory < 1 then
v_sorts_memory := 1;
end if;
v_sorts_disk_ratio := (v_sorts_disk * 100) / (v_sorts_disk + v_sorts_memory);
db_output(‘Sorts to Disk Ratio is ‘||to_char(v_sorts_disk_ratio)||’%’);
/* Check cursor usage */
v_open_cursors_ratio := (v_open_cursors_current * 100) / (v_open_cursors_parameter);
db_output(‘Cursor Usage Ratio is ‘||to_char(v_open_cursors_ratio)||’%’);
/* Check transaction usage */
v_transactions_ratio := (v_transactions * 100) / (v_transactions_parameter);
db_output(‘Transaction Usage Ratio is ‘||to_char(v_transactions_ratio)||’%’);
/* Check number of locks */
db_output(‘Number of users awaiting lock is ‘||to_char(v_total_locks));
/* Check for redo log space waits */
v_redo_space_waittime := v_fetch_redo_space_waittime - v_last_redo_space_waittime;
if v_redo_space_waittime < 0 then
v_redo_space_waittime := v_fetch_redo_space_waittime;
end if;
db_output(‘Redo Log Space Waittime is ‘||to_char(v_redo_space_waittime));
/* Check for SGA*/
v_sga_free_ratio:=round((v_sga_free*100)/(v_sga_total),0);
db_output(‘SGA Free Space Ratio is ‘||to_char(v_sga_free_ratio)||’%’);
end loop;
end;
/
spool off;
