年底了,要对负责的数据库进行,性能健康检查,特此记录.
*******************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(最后请求内存大小)<_SHARED_POOL_RESERVED_MIN_ALLOC,表示 在 shared pool中缺少连续内存,可以加减少_SHARED_POOL_RESERVED_MIN_ALLOC多放入一些对象,减少shared pool压力,适当加大shared_pool_size,SHARED_POOL_RESERVED_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
修改中……
*******************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(最后请求内存大小)<_SHARED_POOL_RESERVED_MIN_ALLOC,表示 在 shared pool中缺少连续内存,可以加减少_SHARED_POOL_RESERVED_MIN_ALLOC多放入一些对象,减少shared pool压力,适当加大shared_pool_size,SHARED_POOL_RESERVED_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
修改中……
编辑回复