Sample Text:         

Oracle Views for PGA Management

Oracle9i has introduced several new views and new columns in existing views to aid in assessing the internal allocation of RAM memory in Oracle.  The following new v$ views can be used to monitor RAM memory usage of dedicated Oracle connections:

 ·         v$process  - Three new columns are added in Oracle for monitoring PGA memory usage.  The new columns are called pga_used_mem, pga_alloc_mem, and pga_max_mem.

 ·         v$sysstat  - There are many new statistics rows, including work area statistics for optimal, one-pass, and multi-pass.

 ·         v$pgastat  - This new view shows internals of PGA memory usage for all background processes and dedicated connections.

 ·         v$sql_plan  - This exciting new view contains execution plan information for all currently executing SQL.  This is a tremendous tool for the performance-tuning professional who must locate suboptimal SQL statements.

 ·         v$workarea  - This new view provides detailed cumulative statistics on RAM memory usage for Oracle9i connections.

 ·         v$workarea_active  - This new view shows internal RAM memory usage information for all currently executing SQL statements.

Let’s take a closer look at these new Oracle9i features and scripts, which allow you to see detailed RAM memory usage. 

The v$sysstat View

 The following query gives the total number of work area executions and the percentage of time they were executed since the database instance was started, in these three modes:

See code depot for full scripts

The output of this query might look like the following:

PROFILE                             CNT        PERCENTAGE
----------------------------------- ---------- ----------
workarea executions - optimal             5395         95
workarea executions - onepass              284          5
workarea executions - multipass              0          0

This output of this query is used to tell the DBA when to dynamically adjust pga_aggregate_target .  In general, the value of pga_aggregate_target should be increased when multi-pass executions are greater than zero, and reduced whenever the optimal executions are 100 percent.

The v$pgastat View

The v$pgastat view provides instance-level summary statistics on the PGA usage and the automatic memory manager.  The following script provides excellent overall usage statistics for all Oracle9i connections:

See code depot for full scripts

Order now from the publisher and get 40% off the retail price!

You also receive immediate online access to the code depot!

Only $9.95

Buy it Now!