some scripts of pga

[Lian...@gmail.com]

-- display status of pga
-- (goal: cache hit percentage = 100)

SELECT *
FROM v$pgastat;

---------------------------------------------------------------------------

-- display the pga and uga of each session

SELECT s.SID SID, s.username oracle_username, s.osuser os_username,
s.program session_program, s.machine session_machine,
(SELECT ss.VALUE
FROM v$sesstat ss, v$statname sn
WHERE ss.SID = s.SID
AND sn.statistic# = ss.statistic#
AND sn.NAME = 'session pga memory') session_pga_memory,
(SELECT ss.VALUE
FROM v$sesstat ss, v$statname sn
WHERE ss.SID = s.SID
AND sn.statistic# = ss.statistic#
AND sn.NAME = 'session pga memory max')
session_pga_memory_max,
(SELECT ss.VALUE
FROM v$sesstat ss, v$statname sn
WHERE ss.SID = s.SID
AND sn.statistic# = ss.statistic#
AND sn.NAME = 'session uga memory') session_uga_memory,
(SELECT ss.VALUE
FROM v$sesstat ss, v$statname sn
WHERE ss.SID = s.SID
AND sn.statistic# = ss.statistic#
AND sn.NAME = 'session uga memory max')
session_uga_memory_max
FROM v$session s
ORDER BY session_pga_memory DESC;

---------------------------------------------------------------------------

-- display the pga used by processes
-- just for checking top 10 used processes

SELECT p.pid, p.spid, p.program, p.pga_used_mem, p.pga_alloc_mem,
p.pga_freeable_mem, p.pga_max_mem, pm.category, pm.allocated,
pm.used, pm.max_allocated
FROM (SELECT *
FROM (SELECT p.pid, p.spid, p.program, p.pga_used_mem,
p.pga_alloc_mem, p.pga_freeable_mem,
p.pga_max_mem
FROM v$process p
ORDER BY p.pga_alloc_mem DESC, p.pga_used_mem DESC)
WHERE ROWNUM <= 10) p,
v$process_memory pm
WHERE p.pid = pm.pid
ORDER BY p.pga_alloc_mem DESC, p.pga_used_mem DESC, p.pid,
pm.category;

---------------------------------------------------------------------------

-- display the memory allcoated for each category of each process

SELECT *
FROM v$process_memory
ORDER BY pid, category;

---------------------------------------------------------------------------

-- display workarea executions statistic
-- (goal: percentage of workarea executions - optimal = 100)

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%');