********************************************************************************
* 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