Databases Reference
In-Depth Information
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.STATISTIC# = V$STATNAME.STATISTIC#;
8.
Query for the session UGA memory:
SELECT SUM(VALUE) AS "session uga memory max"
FROM V$SESSTAT, V$STATNAME
WHERE V$STATNAME.NAME = 'session uga memory max'
AND V$SESSTAT.STATISTIC# = V$STATNAME.STATISTIC#;
How it works...
Private information about the user session, such as private data and cursor state are stored
in the UGA. The UGA is located in the PGA when using dedicated server environments, and
inside the Shared Pool when using shared servers.
