Tuning the Program Global Area and the User Global Area

In this recipe, we will see the Program Global Area (PGA) and the User Global Area (UGA) and how to tune them for maximum performance.

The PGA is used to store real values of bind variables, sort areas, and cursor state information. In a dedicated server environment this area is in private user memory. Only in a shared-server environment the session stack space remains in the PGA, while session data and cursor state are moved into the shared pool.

How to do it...

The following steps will demonstrate tuning the PGA and UGA:

1. Connect to Oracle Enterprise Manager as SYSDBA.

2. Go to Advisor Central.

3. Choose Memory Advisors.

4. Choose the PGA palette to show or change the size of PGA.

5. Connect to SQL*Plus as SYSDBA:

   CONNECT / AS SYSDBA
   

6. Show the parameters related to cursors:

   SHOW PARAMETER CURSOR
   

7. Query for the total session memory:

   SELECT SUM(VALUE) AS "session uga memory"
   FROM V$MYSTAT, V$STATNAME
   WHERE V$STATNAME.NAME = 'session uga memory'
   AND V$MYSTAT...