已归录
查看 PGA 和硬盘中排序的情况统计：
SQL> select * from V$SYSSTAT where name like '%sort%';
STATISTIC# NAME CLASS VALUE STAT_ID
---------- --------------- ---------- ---------- ----------
565 sorts (memory) 64 38313 2091983730
566 sorts (disk) 64 0 2533123502
567 sorts (rows) 64 141311 3757672740
在内存中排序的次数为38313，在硬盘排序的次数为0，有 141311 行进行了排序。
查看 PGA 总体的使用情况：
set lines 200;
set pages 200;
col name for a60;
select * from v$pgastat;查看每个进程使用 PGA 的情况：
SQL> select spid, program, pga_max_mem, pga_alloc_mem, pga_used_mem, pga_freeable_mem
from v$process;
SPID PROGRAM PGA_MAX_MEM PGA_ALLOC_MEM PGA_USED_MEM PGA_FREEABLE_MEM
------ ------------------------- ----------- ------------- ------------ ----------------
6161 Oracle@ocp.example.com 843920 843920 711088 0
6336 Oracle@ocp.example.com 13164688 2547856 1192968 1179648结合 v$session 视图可以查看每个会话使用 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 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;查看每个会话使用 PGA 各代码区域的情况：
select p.program,p.spid,pm.category,pm.allocated,pm.used,pm.max_allocated
from v$process p, v$process_memory pm
where p.pid=pm.pid;
PROGRAM SPID CATEGORY ALLOCATED USED MAX_ALLOCATED
--------------------- ----- --------------- ---------- ---------- -------------
oracle@ocp.example.com 11657 SQL 200188 86212 18563340
oracle@ocp.example.com 11657 PL/SQL 21508 17688 23592
oracle@ocp.example.com 11657 Freeable 65536 0 null
oracle@ocp.example.com 11657 Other 2160284 null 9091944