oracle ora工具脚本,ORACLE DBA实践之五十：ora_perform.sql脚本——oracle监测、诊断、优化工具...

Oracle优化说明

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.ora文件加入设置，例如下：

...

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

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;

v_open_cursors_parameter number;

v_transactions_parameter number;

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;

v_open_cursors_current number;

v_transactions number;

v_total_locks number;

v_sga_free number;

v_sga_total number;

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;

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;

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_stats;

v_counter := &loops;

v_interval := &interval;

while v_counter > 0

loop

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_stats;

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)||’%’);

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)||’%’);

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)||’%’);

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)||’%’);

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)||’%’);

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)||’%’);

v_transactions_ratio := (v_transactions * 100) /

(v_transactions_parameter);

db_output(‘Transaction Usage Ratio is

‘||to_char(v_transactions_ratio)||’%’);

db_output(‘Number of users awaiting lock is

‘||to_char(v_total_locks));

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));

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;