pga内存诊断进阶-凯发app官方网站

1447481.1
vmstat 2 5 -sm
col name for a40
select name,round(value/1024/1024) mb from v$pgastat where name like '%pga%';
比较pga参数与total pga allocated差异，观察maxium pga used for auto wrokareas
select snap_id,round(value/1024/1024) pga_alloc_mb from dba_hist_pgastat where name='total pga allocated' and instance_number=1 order by 1;
确认是否缓慢增长
select a.sid,b.spid,b.pname,a.status,a.sql_id,a.last_call_et,round(b.pga_alloc_mem/1024/1024) pga_alloc_mb, a.event,a.state from v$session a ,v$process b where a.paddr=b.addr and b.pga_alloc_mem/1024/1024>100 order by pga_alloc_mb desc;
等待事件空闲的会话是否使用pga不释放
oradebug setospid &pid
oradebug dump heapdump 536870917
oradebug tracefile_name
转储对应进程的heapdump
egrep "heap dump heap|total heap size|permanet space" xxx.trc
查看内存占用{banned}最佳大的
v$process_memory
column category heading "category"
column allocated heading "allocated bytes"
column used heading "used bytes"
column max_allocated heading "max allocated bytes"
select pid, category, allocated, used, max_allocated
from v$process_memory
where pid = (select pid
from v$process
where addr= (select paddr
from v$session
where sid = &sid));
v$process_memory_detail
select low_optimal_size/1024 low_kb,
(high_optimal_size1)/1024 high_kb,
optimal_executions, onepass_executions, multipasses_executions
from v$sql_workarea_histogram
where total_executions != 0;
select optimal_count, round(optimal_count*100/total, 2) optimal_perc,
onepass_count, round(onepass_count*100/total, 2) onepass_perc,
multipass_count, round(multipass_count*100/total, 2) multipass_perc
from
(select decode(sum(total_executions), 0, 1, sum(total_executions)) total,
sum(optimal_executions) optimal_count,
sum(onepass_executions) onepass_count,
sum(multipasses_executions) multipass_count
from v$sql_workarea_histogram
where low_optimal_size >= 64*1024);
select to_number(decode(sid, 65535, null, sid)) sid,
operation_type operation,
trunc(expected_size/1024) esize,
trunc(actual_mem_used/1024) mem,
trunc(max_mem_used/1024) "max mem",
number_passes pass,
trunc(tempseg_size/1024) tsize
from v$sql_workarea_active
order by 1,2;
col workarea_address for a20
col operation_type for a20
col policy for a15
select *
from (select workarea_address, operation_type, policy, estimated_optimal_size
from v$sql_workarea
order by estimated_optimal_size desc)
where rownum <= 10;
col sql_text format a80 wrap
select sql_text, sum(onepass_executions) onepass_cnt,
sum(multipasses_executions) mpass_cnt
from v$sql s, v$sql_workarea wa
where s.address = wa.address
group by sql_text
having sum(onepass_executionsmultipasses_executions)>0;
col "o/1/m" format a10
col name format a20
select operation, options, object_name name, trunc(bytes/1024/1024) "input(mb)",
trunc(last_memory_used/1024) last_mem,
trunc(estimated_optimal_size/1024) optimal_mem,
trunc(estimated_onepass_size/1024) onepass_mem,
decode(optimal_executions, null, null,
optimal_executions||'/'||onepass_executions||'/'||
multipasses_executions) "o/1/m"
from v$sql_plan p, v$sql_workarea w
where p.address=w.address()
and p.hash_value=w.hash_value()
and p.id=w.operation_id()
and p.address='&addr'
and p.hash_value=&hashvalue;
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;
select name profile, cnt, decode(total, 0, 0, round(cnt*100/total)) percentage
from (select name, value cnt, (sum(value) over ()) total
from v$sysstat
where name
like 'workarea exec%');
822527.1
看前10 pga高的
column alme heading "allocated mb" format 99999d9
column usme heading "used mb" format 99999d9
column frme heading "freeable mb" format 99999d9
column mame heading "max mb" format 99999d9
column username format a15
column program format a22
column sid format a5
column spid format a8
set linesize 300
select * from (
select s.username, substr(s.sid,1,5) sid, p.spid, logon_time,
substr(s.program,1,22) program , s.process pid_remote,
s.status,
round(pga_used_mem/1024/1024) usme,
round(pga_alloc_mem/1024/1024) alme,
round(pga_freeable_mem/1024/1024) frme,
round(pga_max_mem/1024/1024) mame
from v$session s,v$process p
where p.addr=s.paddr
order by pga_max_mem,logon_time)
where rownum<11;
对某个进行检查
column category heading "category"
column allocated heading "allocated bytes"
column used heading "used bytes"
column max_allocated heading "max allocated bytes"
select pid, category, allocated, used, max_allocated
from v$process_memory
where pid = (select pid
from v$process
where addr= (select paddr
from v$session
where sid = &sid));
详细分析
alter session set events'immediate trace name pga_detail_get level &pid';
或
oradebug setmypid;
oradebug dump pga_detail_get &pid;
查看结果
select category, name, heap_name, bytes, allocation_count,
heap_descriptor, parent_heap_descriptor
from v$process_memory_detail
where pid = 10
and category = 'other';

阅读(14) | 评论(0) | 转发(0) |

上一篇：linux命令进阶

给主人留下些什么吧！~~

| | | | |

感谢所有关心和支持过chinaunix的朋友们