Oracle性能查询

baroquesoul

重做日志
col member for a40
select a.group#,a.type,a.member,b.bytes,b.SEQUENCE#,b.BYTES,b.ARCHIVED,b.STATUS from v$logfile a,v$log b where a.group#=b.group#;

查看锁状态
col os_user_name for a10
col user_name for a10
col lock_type for a10
col object for a15
col owner for a12

select /*+ RULE */ ls.osuser os_user_name,   ls.username user_name,
decode(ls.type, 'RW', 'Row wait enqueue lock', 'TM', 'DML enqueue lock', 'TX',
'Transaction enqueue lock', 'UL', 'User supplied lock') lock_type,
o.object_name object,   decode(ls.lmode, 1, null, 2, 'Row Share', 3,
'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive', null)
lock_mode,    o.owner,   ls.sid,   ls.serial# serial_num,   ls.id1,   ls.id2   
from sys.dba_objects o, (   select s.osuser,    s.username,    l.type,   
l.lmode,    s.sid,    s.serial#,    l.id1,    l.id2   from v$session s,   
v$lock l   where s.sid = l.sid ) ls  where o.object_id = ls.id1 and    o.owner
<> 'SYS'   order by o.owner, o.object_name;


实例名称
select instance_name from v$instance;

SGA大小
show parameter sga

PGA大小
show parameter pga

缓冲区命中率
select (1 - (sum(decode(name, 'physical reads',value,0)) /
(sum(decode(name, 'db block gets',value,0)) +
sum(decode(name, 'consistent gets',value,0))))) * 100 "Hit Ratio"
from v$sysstat;

数据字典缓冲区命中率
select (1-(sum(getmisses)/sum(gets))) * 100 "Hit Ratio"
from v$rowcache;

库缓存命中率
select Sum(Pins) / (Sum(Pins) + Sum(Reloads)) * 100 "Hit Ratio"
from V$LibraryCache;

PGA内存排序命中率
select a.value "Disk Sorts", b.value "Memory Sorts",
round((100*b.value)/decode((a.value+b.value),0,1,(a.value+b.value)),2)
"Pct Memory Sorts"
from v$sysstat a, v$sysstat b
where a.name = 'sorts (disk)'
and b.name = 'sorts (memory)';

查看用户连接数
select count(*) from v$session;

PGA内存使用和自动分配的统计。
SELECT * FROM V$PGASTAT;

性能建议
SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
   ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
   ESTD_OVERALLOC_COUNT
   FROM v$pga_target_advice;


alter system set pga_aggregate_target=1g scope=both;

alter system set sga_max_size=2G scope=spfile;
alter system sga_target=0 scope=spfile;

alter database datafile '/oracle/db_home/ovodb/oralog/OPC_INDEX1_1.dbf' autoextend on next 1m maxsize 800m;