Below Query help to find out PGA Allocated Memory for an Oracle Session.
select
to_char(ssn.sid, '9999') || ' – ' || nvl(ssn.username,
nvl(bgp.name, 'background')) || nvl(lower(ssn.machine), ins.host_name) "Session_Details",
to_char(prc.spid, '999999999') pid_thread,
to_char((se1.value / 1024) / 1024, '999g999g990d00') current_size_mb,
to_char((se2.value / 1024) / 1024, '999g999g990d00') maximum_size_mb
from
gv$statname stat1,
gv$statname stat2,
gv$session ssn,
gv$sesstat se1,
gv$sesstat se2,
gv$bgprocess bgp,
gv$process prc,
gv$instance ins
where stat1.name = 'session pga memory'
and stat2.name = 'session pga memory max'
and se1.sid = ssn.sid
and se2.sid = ssn.sid
and se2.statistic# = stat2.statistic#
and se1.statistic# = stat1.statistic#
and ssn.paddr = bgp.paddr(+)
and ssn.paddr = prc.addr(+) ;
Thanks,
Jyothish
select
to_char(ssn.sid, '9999') || ' – ' || nvl(ssn.username,
nvl(bgp.name, 'background')) || nvl(lower(ssn.machine), ins.host_name) "Session_Details",
to_char(prc.spid, '999999999') pid_thread,
to_char((se1.value / 1024) / 1024, '999g999g990d00') current_size_mb,
to_char((se2.value / 1024) / 1024, '999g999g990d00') maximum_size_mb
from
gv$statname stat1,
gv$statname stat2,
gv$session ssn,
gv$sesstat se1,
gv$sesstat se2,
gv$bgprocess bgp,
gv$process prc,
gv$instance ins
where stat1.name = 'session pga memory'
and stat2.name = 'session pga memory max'
and se1.sid = ssn.sid
and se2.sid = ssn.sid
and se2.statistic# = stat2.statistic#
and se1.statistic# = stat1.statistic#
and ssn.paddr = bgp.paddr(+)
and ssn.paddr = prc.addr(+) ;
Thanks,
Jyothish
No comments:
Post a Comment