can be determined by using the sharable_memory column of the V$SQLAREA view, which contains all the SQLs in the shared pool. Every session that executes the same SQL will share the same shared SQL area.

The private SQL area is the memory area that contains data such as binding information and runtime buffers. Every session that executes a SQL statement will have its own individual private SQL area for every SQL that it executes. To summarize, for multiple sessions that are executing the same SQL statement, there will be one shared SQL area and multiple private SQL areas owned individually by each of the sessions. The private SQL area is further divided into a persistent area and the runtime area.

The area of memory in which we will be required to monitor memory usage is the Program Global Area (PGA). The PGA contains data and control information for a single process. The PGA is also referred to as the Process Global Area. The contents of the PGA vary depending on the type of connection, dedicated or multithreaded.

In both architectures, the PGA will always contain the stack space, which is the memory used to store the sessions variables, arrays, and other information.

In a dedicated server option, the PGA will store additional information related to the user's session—that is, the private SQL area and other session information is stored here. In a multi-threaded option, this information is located in the SGA.

You can monitor the PGA usage and assess the per-user memory requirement on the system. The PGA memory usage can be done by using the V$SESSTAT view. The statistic# value=20 corresponds to PGA memory usage. To monitor heavy memory users, use the script in Listing 22.23.

col value format 99999 heading "Memory |Kb"

Select sid,
  value/1024 value
  From v$sesstat
  Where statistic# =20
  Order By value desc

    Memory
      SID      Kb
---------- ------
       31    1165
      153     754
      142     733
      101     686
       70     362
       73     336
       26     306
       60     298
       82     297
       53     297
123     275
       44     272
      110     270

Having ascertained the session that consumes a high amount of PGA, you can proceed with the steps mentioned in the previous sections to find what each session is doing.l