Oracle性能查询

baroquesoul 发表于 2011-12-23 02:12

重做日志 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;

页: [1]

Chinaunix's Archiver

Oracle性能查询