batstat oracle_oracle 数据库性能健康检查脚本

年底了,要对负责的数据库进行,性能健康检查,特此记录.

*******************os层检查

#检查oracle进程

ps -ef|grep ora|wc -l

crs_stat -t -v

top

glance

crsctl check cluster (cssd)

vmstat 3600 6

***********************查看基本信息

#database 概况信息检查

#检查database基本信息

select * from v$version;

select name ,open_mode,log_mode from v$database;

select instance_number,instance_name ,status from gv$instance;

show parameter cpu_c

show parameter block_size

select group#,thread#,members,bytes/1024/1024 from gv$log;

show sga

select count(*) from v$controlfile

select count(*) from v$tempfile;

select count(*) from v$datafile;

********************************查看数据文件信息

#检查表空间数据文件信息

col tablespace_name for a30

select tablespace_name , sum(bytes)/1024/1024 from dba_temp_files group by tablespace_name;

#检查表空间

SELECT TABLESPACE_NAME,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT FROM DBA_TABLESPACES;

#检查数据文件状态

select count(*),status from v$datafile group by status;

#检查表空间使用情况

select

f.tablespace_name,

a.total,

f.free,(a.total-f.free)/1024 "used SIZE(G)"

,round((f.free/a.total)*100) "% Free"

from

(select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files group by tablespace_name) a,

(select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space group by tablespace_name) f

WHERE a.tablespace_name = f.tablespace_name(+)

order by "% Free"

/

#查询临时segment使用情况

COL username FORMAT a10;

COL segtype FORMAT a10;

SELECT username, segtype, extents "Extents Allocated"

,blocks "Blocks Allocated"

FROM v$tempseg_usage;

#查看数据文件信息,若文件较多可以根据需要字段进行排序 输出top 10

SELECT fs.phyrds "Reads", fs.phywrts "Writes"

,fs.avgiotim "Average I/O Time", df.name "Datafile"

FROM v$datafile df, v$filestat fs WHERE df.file# = fs.file#;

#查看所有数据文件i/o情况,若文件太多,可以改写为top 10 select *( order by xx desc) where rownum<=10

COL ts FORMAT a10 HEADING "Tablespace";

COL reads FORMAT 999990;

COL writes FORMAT 999990;

COL br FORMAT 999990 HEADING "BlksRead";

COL bw FORMAT 999990 HEADING "BlksWrite";

COL rtime FORMAT 999990;

COL wtime FORMAT 999990;

SELECT ts.name AS ts, fs.phyrds "Reads", fs.phywrts "Writes"

,fs.phyblkrd AS br, fs.phyblkwrt AS bw

,fs.readtim "RTime", fs.writetim "WTime"

FROM v$tablespace ts, v$datafile df, v$filestat fs

WHERE ts.ts# = df.ts# AND df.file# = fs.file#

UNION

SELECT ts.name AS ts, ts.phyrds "Reads", ts.phywrts "Writes"

,ts.phyblkrd AS br, ts.phyblkwrt AS bw

,ts.readtim "RTime", ts.writetim "WTime"

FROM v$tablespace ts, v$tempfile tf, v$tempstat ts

WHERE ts.ts# = tf.ts# AND tf.file# = ts.file# ORDER BY 1;

#查看所有数据文件i/o情况(比率),若文件太多,可以改写为top 10 select *( order by xx desc) where rownum<=10

COL ts FORMAT a10 HEADING "Tablespace";

COL reads FORMAT 999990;

COL writes FORMAT 999990;

COL br FORMAT 999990 HEADING "BlksRead";

COL bw FORMAT 999990 HEADING "BlksWrite";

COL rtime FORMAT 999990;

COL wtime FORMAT 999990;

SELECT ts.name AS ts, fs.phyrds "Reads", fs.phywrts "Writes"

,fs.phyblkrd AS br, fs.phyblkwrt AS bw

,fs.readtim "RTime", fs.writetim "WTime"

FROM v$tablespace ts, v$datafile df, v$filestat fs

WHERE ts.ts# = df.ts# AND df.file# = fs.file#

UNION

SELECT ts.name AS ts, ts.phyrds "Reads", ts.phywrts "Writes"

,ts.phyblkrd AS br, ts.phyblkwrt AS bw

,ts.readtim "RTime", ts.writetim "WTime"

FROM v$tablespace ts, v$tempfile tf, v$tempstat ts

WHERE ts.ts# = tf.ts# AND tf.file# = ts.file# ORDER BY 1;

#获取top 10 热segment

col objct_name for a30

select * from

(select

ob.owner, ob.object_name, sum(b.tch) Touchs

from x$bh b , dba_objects ob

where b.obj = ob.data_object_id

and b.ts# > 0

group by ob.owner, ob.object_name

order by sum(tch) desc)

where rownum <=10

#判断物理读最多的object

select * from (select owner,object_name,value from v$segment_statistics where statistic_name='physical reads' order by value desc) where rownum<=10

#查看热点数据文件(从单块读取时间判断)

SELECT t.file_name,

t.tablespace_name,

round(s.singleblkrdtim / s.singleblkrds, 2) AS CS,

s.READTIM,

s.WRITETIM

FROM v$filestat s, dba_data_files t

WHERE s.file# = t.file_id and rownum<=10 order by cs desc

******************************查看redo

#检查日志切换频率

select sequence#,to_char(first_time,'yyyymmdd_hh24:mi:ss') firsttime,round((first_time-lag(first_time) over(order by first_time))*24*60,2) minutes from v$log_history where first_time > sysdate - 1 order by first_time ,minutes;

#检查lgwr i/o性能 (time_waited/total_waits：表示平均lgwr写入完成时间 若>1表示写入过慢)

select total_waits,time_waited,average_wait,time_waited/total_waits as avg from v$system_event where event = 'log file parallel write';

#检查与redo相关性能指标

select name,value from v$sysstat where name like '%redo%';

#查询redo block size

select max(lebsz) from x$kccle;

#查看redo allocation latch

col name for a30

select name,gets,misses,misses/gets from v$latch_children where name='redo allocation';

#查看与redo相关等待事件

col event format a40

select event,total_waits,total_timeouts,average_wait from v$system_event where upper(event) like'%REDO%';

#查看session redo event

select event,total_waits,total_timeouts,average_wait from v$session_event where upper(event) like'%REDO%';

#查看user commit次数

select to_number(value,99999999999) from v$sysstat where name='user commits';

#查看系统运行时间

select (sysdate - startup_time)*24*60*60 as seconds from v$instance

#计算出每秒用户提交次数

select user_commit次数/系统运行时间 from dual;

#计算出每个事务平均处理多少个redo block

select value from v$sysstat where name = 'redo blocks written';

select a.redoblocks/b.trancount from (select value redoblocks from v$sysstat where name='redo blocks written') a ,(select value trancount from v$sysstat where name='user commits') b

'

***************************sga,pga,命中率

# sga,pga,命中率

#检查sga

show sga

select * from v$sga;

#查看buffer cache 命中率

select 1-(sum(decode(name, 'physical reads', value, 0))/

(sum(decode(name, 'db block gets', value, 0))+

(sum(decode(name, 'consistent gets', value, 0))))) "Buffer Hit Ratio"

from v$sysstat;

#查看buffer cache建议

select size_for_estimate, estd_physical_read_factor, to_char(estd_physical_reads,99999999999999999999999) as"estd_physical_reads" from v$db_cache_advice where name = 'DEFAULT';

COL pool FORMAT a10;

SELECT (SELECT ROUND(value/1024/1024,0) FROM v$parameter

WHERE name = 'db_cache_size') "Current Cache(Mb)"

,name "Pool", size_for_estimate "Projected Cache(Mb)"

,ROUND(100-estd_physical_read_factor,0) "Cache Hit Ratio%"

FROM v$db_cache_advice

WHERE block_size = (SELECT value FROM v$parameter

WHERE name = 'db_block_size')

ORDER BY 3;

#查看pga

show parameter pga

#查看cache 池

show parameter cache

#查看buffer cache中defalut pool 命中率

select name,1-(physical_reads)/(consistent_gets+db_block_gets)

from v$buffer_pool_statistics;

#检查shared pool

show parameter shared

#检查shared pool中library cache

select namespace,pinhitratio from v$librarycache;

#检查整体命中率(library cache)

select sum(pinhits)/sum(pins) from v$librarycache;

select sum(pins) "hits",

sum(reloads) "misses",

sum(pins)/(sum(pins)+sum(reloads)) "Hits Ratio"

from v$librarycache;

#检查shered pool free space

SELECT * FROM V$SGASTAT

WHERE NAME = 'free memory'

AND POOL = 'shared pool';

#检查row cache(数据字典缓冲区)命中率

#当执行一个dml或ddl都会造成对数据字典的递归修改

column updates format 999,999,999

SELECT parameter

, sum(gets)

, sum(getmisses)

, 100*sum(gets - getmisses) / sum(gets) pct_succ_gets

, sum(modifications) updates

FROM V$ROWCACHE

WHERE gets > 0

GROUP BY parameter;

SELECT (SUM(GETS - GETMISSES - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;

#查看Shared pool latch(多池技术)

col parameter for a20

col session for a20

select a.ksppinm "Parameter",

b.ksppstvl "Session Value",

c.ksppstvl "Instance Value"

from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c

where a.indx = b.indx and a.indx = c.indx

and a.ksppinm = '_kghdsidx_count';

#每个子shared pool由 单独的shared pool latch保护 查看 他们的命中率

shared pool latch，用于shared pool空间 回收分配使用的latch

col name format a15

select addr,name,gets,misses,1-misses/gets from v$latch_children where name='shared pool';

#查看shared pool建议

column c1 heading 'Pool |Size(M)'

column c2 heading 'Size|Factor'

column c3 heading 'Est|LC(M) '

column c4 heading 'Est LC|Mem. Obj.'

column c5 heading 'Est|Time|Saved|(sec)'

column c6 heading 'Est|Parse|Saved|Factor'

column c7 heading 'Est|Object Hits' format 999,999,999

SELECT shared_pool_size_for_estimate c1,shared_pool_size_factor c2,

estd_lc_size c3,estd_lc_memory_objects c4,estd_lc_time_saved c5,

estd_lc_time_saved_factor c6,to_char(estd_lc_memory_object_hits,99999999999) c7 FROM V$SHARED_POOL_ADVICE;

#使用 v$shared_pool_advice 算不同shared pool大小情况下，响应时间，S单位

SELECT 'Shared Pool' component,

shared_pool_size_for_estimate estd_sp_size,

estd_lc_time_saved_factor parse_time_factor,

CASE

WHEN current_parse_time_elapsed_s + adjustment_s < 0 THEN

0

ELSE

current_parse_time_elapsed_s + adjustment_s

END response_time

FROM (SELECT shared_pool_size_for_estimate,

shared_pool_size_factor,

estd_lc_time_saved_factor,

a.estd_lc_time_saved,

e.VALUE / 100 current_parse_time_elapsed_s,

c.estd_lc_time_saved - a.estd_lc_time_saved adjustment_s

FROM v$shared_pool_advice a,

(SELECT * FROM v$sysstat WHERE NAME = 'parse time elapsed') e,

(SELECT estd_lc_time_saved FROM v$shared_pool_advice

WHERE shared_pool_size_factor = 1) c)

/

#查看shared pool中 各种类型的chunk的大小数量

SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ,

To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG SIzE"

FROM X$KSMSP GROUP BY KSMCHCLS;

#查看是否有库缓冲有关的等待事件

select sid,seq#,event,p1,p1raw,p2,p2raw,p3,p3raw,state from v$session_wait where event like 'library%';

#row cache命中率

SELECT 'Dictionary Cache Hit Ratio ' "Ratio"

,ROUND((1 - (SUM(GETMISSES) / SUM(GETS))) * 100,2)||'%'

"Percentage"

FROM V$ROWCACHE;

#library cache中详细比率信息

SELECT 'Library Lock Requests' "Ratio"

, ROUND(AVG(gethitratio) * 100, 2)

||'%' "Percentage" FROM V$LIBRARYCACHE

UNION

SELECT 'Library Pin Requests' "Ratio", ROUND(AVG(pinhitratio)

* 100, 2)

||'%' "Percentage" FROM V$LIBRARYCACHE

UNION

SELECT 'Library I/O Reloads' "Ratio"

, ROUND((SUM(reloads) / SUM(pins)) * 100, 2)

||'%' "Percentage" FROM V$LIBRARYCACHE

UNION

SELECT 'Library Reparses' "Ratio"

, ROUND((SUM(reloads) / SUM(pins)) * 100, 2)

||'%' "Percentage" FROM V$LIBRARYCACHE;

#查询sga中各个pool 情况

COL name FORMAT a32;

SELECT pool, name, bytes FROM v$sgastat

WHERE pool IS NULL

OR pool != 'shared pool' OR (pool = 'shared pool'

AND (name IN('dictionary cache','enqueue','library

cache','parameters',

'processes','sessions','free memory')))

ORDER BY pool DESC NULLS FIRST, name;

SELECT * FROM V$SGAINFO;

#查看使用shard_pool保留池情况

SELECT request_misses, request_failures, free_space

FROM v$shared_pool_reserved;

#Oracle专门从共享池内置出一块区域来来分配内存保持这些大块。这个保留共享池的默认大小是共享池的5%(_shared_pool_reserved_pct 5 控制) oracle建设置为10%。大小通过参数SHARED_POOL_RESERVED_SIZE改。它是从共享池中分配，不是直接从SGA中分配的，它是共享池的保留部分，专门用于存储大块段

#shared pool中内存大于_SHARED_POOL_RESERVED_MIN_ALLOC 将放入shared pool保留池,保留池维护一个单独的freelist,lru，并且不会在lru列表存recreatable类型chunks，普通shared pool的释放与shared pool保留池无关。

#关于设置SHARED_POOL_RESERVED_SIZE

#1.如果 系统出现ora-04031,发现 请求内存都是大于 _SHARED_POOL_RESERVED_MIN_ALLOC (default 10GR2 4400) ,且v$shared_pool_reserved中有大量 REQUEST_MISSES(并且可以 看下 LAST_MISS_SIZE ) 表示 SHARED_POOL_RESERVED_SIZE太小了 需要大的内存的请求失败,那么需要加大 SHARED_POOL_RESERVED_SIZE

#2.如果ora-04031请求内存出现在4100-4400并造成shared pool lru合并,老化换出内存 ,可以 调小 _SHARED_POOL_RESERVED_MIN_ALLOC 让此部分内存 进入 shared reserved pool,相应的加大SHARED_POOL_RESERVED_SIZE

#3.从v$shared_pool_reserved来判断, 如果REQUEST_FAILURES>0(出现过ora-04031)且LAST_FAILURE_SIZE(最后请求内存大小)>_SHARED_POOL_RESERVED_MIN_ALLOC 表示shared reserved pool 缺少连续内存,可以加大SHARED_POOL_RESERVED_SIZE,减少 _SHARED_POOL_RESERVED_MIN_ALLOC 少放对象,并相对加大shared_pool_size

#要是反过来 REQUEST_FAILURES>0(出现过ora-04031)且LAST_FAILURE_SIZE(最后请求内存大小)#从library中获取 执行次数最多的top 10 sql 查看他们的具体信息 (未含解析次数)

COL execs FORMAT 9999990

COL parses FORMAT 99990

COL fetches FORMAT 999990

COL loads FORMAT 9990

COL invalids FORMAT 9999990

COL i/o FORMAT 99990

COL hits FORMAT 9999990

COL rows FORMAT 999990

COL sorts FORMAT 9990

COL sql FORMAT a32

SELECT * FROM(

SELECT executions "Execs"--Executions

,parse_calls "Parses"--Parses

,fetches "Fetches"--Fetches

,loads"Loads"--Loads and reloads

,invalidations "Invalids"--Invalidations

,disk_reads "I/O"--I/O

,buffer_gets "Hits"--Buffer hits

,rows_processed "Rows"--Rows

,sorts"Sorts"--Sorts

,sql_text "SQL"

FROM v$sqlarea

ORDER BY executions DESC

) WHERE ROWNUM <= 10;

#查询还保留在library cache中，解析次数和执行次数最多的sql(解析*执行)

COL sql_text FORMAT A38;

SELECT * FROM(

SELECT parse_calls*executions "Product", parse_calls

"Parses"

,executions "Execs", sql_text FROM v$sqlarea ORDER BY 1 DESC)

WHERE ROWNUM <= 10;

#查看shared pool中,row cache详细信息

SELECT COUNT "Entries", GETS "Requests", GETMISSES "Misses"

,MODIFICATIONS "DML Activity", parameter "Area"

FROM v$rowcache;

#DBMS_SHARED_POOL.[UN]KEEP (object, type);

#查看shared pool建议

SELECT (SELECT ROUND(value/1024/1024,0) FROM v$parameter

WHERE name = 'shared_pool_size') "Current Mb"

, shared_pool_size_for_estimate "Projected Mb"

, ROUND(shared_pool_size_factor*100) "%"

, ESTD_LC_SIZE "Library Mb"

, ESTD_LC_TIME_SAVED "Parse Savings"

,to_char(ESTD_LC_MEMORY_OBJECT_HITS,'999999999999') "Hits"

FROM v$shared_pool_advice

ORDER BY 1;

#查看library cache 内存分配情况(对哪类对象)

SELECT lc_namespace "Library"

,LC_INUSE_MEMORY_OBJECTS "Objects"

,LC_INUSE_MEMORY_SIZE "Objects Mb"

,LC_FREEABLE_MEMORY_OBJECTS "Freeable Objects"

,LC_FREEABLE_MEMORY_SIZE "Freeable Mb"

FROM v$library_cache_memory;

#查看pga

show parameters area_size

# 查看pga

SELECT * FROM v$pgastat;

查看pga建议

SELECT (SELECT ROUND(value/1024/1024,0) FROM v$parameter

WHERE name = 'pga_aggregate_target') "Current Mb"

, ROUND(pga_target_for_estimate/1024/1024,0) "Projected Mb"

, ROUND(estd_pga_cache_hit_percentage) "%"

FROM v$pga_target_advice

ORDER BY 2;

#Database read buffer cache hit ratio =

#1 – (physical reads / (db block gets + consistent gets))

SELECT to_char(value,'9999999999999'), name FROM V$SYSSTAT WHERE name IN

('physical reads', 'db block gets', 'consistent gets');

#查询数据库命中率

SELECT 'Database Buffer Cache Hit Ratio ' "Ratio"

, ROUND((1-

((SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'physical reads')

/ ((SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'db block gets')

+ (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'consistent gets')

))) * 100)||'%' "Percentage"

FROM DUAL;

# alter table xx cache

#查看cache中所有pool,命中情况

COL pool FORMAT a10;

SELECT a.name "Pool", a.physical_reads, a.db_block_gets

, a.consistent_gets

,(SELECT ROUND((1-(physical_reads / (db_block_gets + consistent_gets)))*100)

FROM v$buffer_pool_statistics

WHERE db_block_gets+consistent_gets != 0

AND name = a.name) "Ratio"

FROM v$buffer_pool_statistics a;

#ALTER TABLE(INDEX) xx STORAGE(BUFFER_POOL KEEP);

# 查看数据库cache或keep了哪些object

COL table_name FORMAT A16

COL index_name FORMAT A16

SELECT table_name AS "Table", NULL, buffer_pool, cache FROM

user_tables

WHERE buffer_pool != 'DEFAULT' OR TRIM(cache)='Y'

UNION

SELECT table_name, index_name, NULL, buffer_pool FROM

user_indexes

WHERE buffer_pool != 'DEFAULT'

ORDER BY 1, 2 NULLS FIRST;

#取消cache或keep(keep pool)

#ALTER TABLE XX NOCACHE;

#SELECT 'ALTER INDEX '||index_name||' STORAGE(BUFFER_POOL DEFAULT);'

#FROM USER_INDEXES WHERE BUFFER_POOL!='DEFAULT';

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

#检查undo

show parameter undo_

#检查undo rollback segment 使用情况

select name ,rssize,extents,latch,xacts,writes,gets,waits from v$rollstat a,v$rollname b where a.usn=b.usn order by waits desc;

select a.redoblocks/b.trancount from (select value redoblocks from v$sysstat where name='redo blocks written') a ,(select value trancount from v$sysstat where name='user commits') b;

#计算每秒钟产生的undoblk数量

select sum(undoblks)/sum((end_time-begin_time)*24*60*60) from v$undostat;

#Undospace=UR*UPS*blocksize + overload(10%),计算undo tablespace大小

show parameter block_size

show parameter undo_retention

#select undo_retention*每秒产生undoblk数量*block_size/1024/1024/1024+ (1+1undo_retention*每秒产生undoblk数量*block_size/1024/1024/1024*0.1) from dual;

#查询undo具体信息

COL undob FORMAT 99990;

COL trans FORMAT 99990;

COL snapshot2old FORMAT 9999999990;

SELECT undoblks "UndoB", txncount "Trans"

,maxquerylen "LongestQuery", maxconcurrency "MaxConcurrency"

,ssolderrcnt "Snapshot2Old", nospaceerrcnt "FreeSpaceWait"

FROM v$undostat;

#查询rollback 段详细信息(收缩次数,扩展次数,平均活动事务等)

COL RBS FORMAT a4;

SELECT n.name "RBS", s.extends "Extends", s.shrinks

"Shrinks"

,s.wraps "Wraps", s.aveshrink "AveShrink"

,s.aveactive "AveActive"

FROM v$rollname n JOIN v$rollstat s USING(usn)

WHERE n.name != 'SYSTEM';

#查询当前rollback segment使用情况

COL RBS FORMAT a4;

SELECT n.name "RBS", s.status, s.waits, s.gets, to_char(s.writes,'9999999999999')

,s.xacts "Active Trans"

FROM v$rollname n JOIN v$rollstat s USING(usn)

WHERE n.name != 'SYSTEM';

#查询使用rollback segment时等待比率

SELECT ROUND(SUM(waits/gets)*100,2)||'%' "Contention" FROM

v$rollstat;

#查询使用rollback segment时等待比率及其平局活动事务数

COL contention FORMAT 9999999990;

SELECT AVG(xacts) "Trans per RBS"

,ROUND(SUM(waits/gets)*100,2)||'%' "Contention"

FROM v$rollstat;

******************************** 查看其它性能指标信息(等待事件,latch等,排序,解析,index使用率)

#查看数据库中行chain

SELECT 'Chained Rows ' "Ratio"

, ROUND(

(SELECT SUM(value) FROM V$SYSSTAT

WHERE name = 'table fetch continued row')

/ (SELECT SUM(value) FROM V$SYSSTAT

WHERE name IN ('table scan rows gotten', 'table fetch by

rowid'))

* 100, 3)||'%' "Percentage"

FROM DUAL;

#在内存中排序比率(最优排序)

SELECT 'Sorts in Memory ' "Ratio"

, ROUND(

(SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'sorts (memory)')

/ (SELECT SUM(value) FROM V$SYSSTAT

WHERE name IN ('sorts (memory)', 'sorts (disk)')) * 100, 5)

||'%' "Percentage"

FROM DUAL;

#查询解析比率

SELECT 'Soft Parses ' "Ratio"

, ROUND(

((SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'parse count (total)')

- (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'parse count (hard)'))

/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'execute count')

* 100, 2)||'%' "Percentage"

FROM DUAL

UNION

SELECT 'Hard Parses ' "Ratio"

, ROUND((SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'parse count (hard)')

/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'execute count') * 100, 2)||'%' "Percentage"

FROM DUAL

UNION

SELECT 'Parse Failures ' "Ratio"

, ROUND((SELECT SUM(value) FROM V$SYSSTAT

WHERE name = 'parse count (failures)')

/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'parse count (total)')* 100, 5)||'%' "Percentage" FROM DUAL;

#查看与latch有关的event信息

COL event FORMAT a20;

COL waits FORMAT 9999990;

COL timeouts FORMAT 99999990;

COL average FORMAT 99999990;

SELECT event "Event", time_waited "Total Time", total_waits

"Waits"

,average_wait "Average", total_timeouts "Timeouts"

FROM V$SYSTEM_EVENT

WHERE event = 'latch free'

ORDER BY EVENT;

#查看数据库中查询时主要访问方式,获取大表小表访问比率(2个表的访问算法不同)

#table scans (long tables)过多的话,一般db file scattered read比较显著

#_small_table_threshold来定义大表和小表的界限。缺省为2%的Buffer数量 ,>这个参数为大表

#default 大表的全表扫描会被置于LRU的末端(最近最少使用,冷端)，以期尽快老化(让其尽快换出buffer cache)，减少Buffer的占用

#查看大表小表扫描对应的值

SELECT value, name FROM V$SYSSTAT WHERE name IN

('table fetch by rowid', 'table scans (short tables)'

, 'table scans (long tables)');

SELECT 'Short to Long Full Table Scans' "Ratio"

, ROUND(

(SELECT SUM(value) FROM V$SYSSTAT

WHERE name = 'table scans (short tables)')

/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name IN

('table scans (short tables)', 'table scans (long tables)'))

* 100, 2)||'%' "Percentage"

FROM DUAL

UNION

SELECT 'Short Table Scans ' "Ratio"

, ROUND(

(SELECT SUM(value) FROM V$SYSSTAT

WHERE name = 'table scans (short tables)')

/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name IN

('table scans (short tables)', 'table scans (long tables)', 'table fetch by rowid'))

* 100, 2)||'%' "Percentage"

FROM DUAL

UNION

SELECT 'Long Table Scans ' "Ratio"

, ROUND(

(SELECT SUM(value) FROM V$SYSSTAT

WHERE name = 'table scans (long tables)')

/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name

IN ('table scans (short tables)', 'table scans (long tables)', 'table fetch by rowid'))

* 100, 2)||'%' "Percentage"

FROM DUAL

UNION

SELECT 'Table by Index ' "Ratio"

, ROUND(

(SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'table fetch by rowid')

/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name

IN ('table scans (short tables)', 'table scans (long tables)'

, 'table fetch by rowid'))

* 100, 2)||'%' "Percentage"

FROM DUAL

UNION

SELECT 'Efficient Table Access ' "Ratio"

, ROUND(

(SELECT SUM(value) FROM V$SYSSTAT WHERE name

IN ('table scans (short tables)','table fetch by rowid'))

/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name

IN ('table scans (short tables)', 'table scans (long tables)'

, 'table fetch by rowid'))

* 100, 2)||'%' "Percentage"

FROM DUAL;

#index使用比率

col name for a30

SELECT to_char(value,'999999999999999999999'), name FROM V$SYSSTAT WHERE name IN

('table fetch by rowid', 'table scans (short tables)'

, 'table scans (long tables)')

OR name LIKE 'index fast full%' OR name = 'index fetch by

key';

SELECT 'Index to Table Ratio ' "Ratio" , ROUND(

(SELECT SUM(value) FROM V$SYSSTAT

WHERE name LIKE 'index fast full%'

OR name = 'index fetch by key'

OR name = 'table fetch by rowid')

/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name IN

('table scans (short tables)', 'table scans (long tables)')

),0)||':1' "Result"

FROM DUAL

#等待class

col wait_class for a30

SELECT wait_class, COUNT(wait_class) FROM v$system_event

GROUP BY wait_class ORDER BY 1;

##下面将查询到不含idle event的,其他重要event,但需要建立一个过滤函数

##CREATE OR REPLACE FUNCTION IdleEvent(pEvent IN VARCHAR2

##DEFAULT NULL)

##RETURN VARCHAR2 IS

##CURSOR cIdleEvents IS

##SELECT name FROM v$event_name

##WHERE wait_class = 'Idle'

##OR name LIKE '%null%' OR name LIKE '%timer%'

##OR name LIKE '%SQL*Net%' OR name LIKE '%rdbms ipc%'

##OR name LIKE '%ispatcher%' OR name LIKE '%virtual

##circuit%'

##OR name LIKE '%PX%' OR name LIKE '%pipe%'

##OR name LIKE '%message%' OR name LIKE 'jobq%'

##OR name LIKE 'Streams%';

##BEGIN

##FOR rIdleEvent in cIdleEvents LOOP

##IF pEvent = rIdleEvent.name THEN

##RETURN NULL;

##END IF;

##END LOOP;

##RETURN pEvent;

##EXCEPTION WHEN OTHERS THEN

##DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE));

##END;

##/

##ALTER FUNCTION IdleEvent COMPILE;

##/

##

##查询重要event

##COL event FORMAT a30;

##COL waits FORMAT 9999990;

##COL timeouts FORMAT 99999990;

##COL average FORMAT 99999990;

##SELECT event "Event", time_waited "Total Time", total_waits

##"Waits",average_wait "Average", total_timeouts "Timeouts"

##FROM v$system_event WHERE IdleEvent(event) IS NOT NULL

##ORDER BY event;

##

##每个等待event所占整体比例

##COL percentage FORMAT 9999999990;

##SELECT event "Event", total_waits "Waits", time_waited "Total

##Time"

##,TO_CHAR(

##(time_waited /

##(SELECT SUM(time_waited) FROM v$system_event

##WHERE IdleEvent(event) IS NOT NULL)

##)*100, 990.99) "Percentage"

##FROM v$system_event WHERE IdleEvent(event) IS NOT NULL ORDER

##BY event;

#检查系统中当前等待事件

col event for a30

select sid,event,p1,p2,p3,p1text,WAIT_TIME,SECONDS_IN_WAIT from v$session_wait where event not like 'SQL%' and event not like 'rdbms%';

#检查数据库中无效对象

SELECT owner, object_type,count(object_name) FROM dba_objects WHERE status= 'INVALID' group by owner,object_type;

#检查是否有禁用约束

SELECT owner, constraint_name, table_name, constraint_type, status

FROM dba_constraints

WHERE status ='DISABLE' and constraint_type='P'

#检查是否有禁用trigger

col owner for a10

col taigger_name for a10

cok table_name for a30

col table_name for a30

SELECT owner, trigger_name, table_name, status FROM dba_triggers WHERE status = 'DISABLED';

****************生成awr

cd $ORACLE_hOME/rdbms/admin

SQL> @awrrpt.sql