 |
 |
********************************************************************************
* Description: SQL for Querying PGA Utilization Divided by Individule Component
* Compatiablity: RDBMS 10g, 11g, 12c
* Date: 05:28 PM EST, 03/08/2017
********************************************************************************
SQL> select * from v$pgastat;
NAME VALUE UNIT
---------------------------------------------------------------- ---------- ------------
aggregate PGA target parameter 3221225472 bytes
aggregate PGA auto target 201326592 bytes
global memory bound 322119680 bytes
total PGA inuse 8764224512 bytes
total PGA allocated 9005279232 bytes
maximum PGA allocated 9835001856 bytes
total freeable PGA memory 180027392 bytes
process count 112
max processes count 167
PGA memory freed back to OS 3.4382E+12 bytes
total PGA used for auto workareas 1363968 bytes
maximum PGA used for auto workareas 962768896 bytes
total PGA used for manual workareas 0 bytes
maximum PGA used for manual workareas 4358144 bytes
over allocation count 408339
bytes processed 7.3911E+12 bytes
extra bytes read/written 5.1519E+11 bytes
cache hit percentage 93.48 percent
recompute count (total) 557404
SQL> select pid, spid, substr(username,1,20) "USER" , substr(program,1,30) "Program", PGA_USED_MEM, PGA_ALLOC_MEM, PGA_FREEABLE_MEM, PGA_MAX_MEM
from v$process where pga_alloc_mem=(select max(pga_alloc_mem) from v$process where program like '%SMON%');
PID SPID USER Program PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
---------- ------------------------ --------------- ------------------------------ ------------ ------------- ---------------- -----------
14 7872 SYSTEM ORACLE.EXE (SMON) 335361474 341786386 0 341786386
Your Comments