Database Reference
In-Depth Information
SQL> DESC v$sql_workarea_active
Name Null? Type
----------------------------------------- -------- ------------
WORKAREA_ADDRESS RAW(4)
OPERATION_TYPE VARCHAR2(20)
OPERATION_ID NUMBER
POLICY VARCHAR2(6)
SID NUMBER
QCINST_ID NUMBER
QCSID NUMBER
ACTIVE_TIME NUMBER
WORK_AREA_SIZE NUMBER
EXPECTED_SIZE NUMBER
ACTUAL_MEM_USED NUMBER
MAX_MEM_USED NUMBER
NUMBER_PASSES NUMBER
TEMPSEG_SIZE NUMBER
TABLESPACE VARCHAR2(31)
SEGRFNO# NUMBER
SEGBLK# NUMBER
I wrote a small Perl DBI program for closely monitoring the use of PGA work areas. The
Perl program executes a
SELECT
on
V$SQL_WORKAREA_ACTIVE
once per second and prints the results
to the screen. In addition to the session identifier (which corresponds to
V$SESSION.SID
), the
current and maximum work area sizes, and the size of temporary segments, the query also
retrieves a timestamp. All sizes are reported in MB. The
SELECT
statement used by the Perl
program is as follows:
SELECT sid, to_char(sysdate,'mi:ss') time,
round(work_area_size/1048576, 1) work_area_size_mb,
round(max_mem_used/1048576, 1) max_mem_used_mb, number_passes, nvl(tempseg_size/
1048576, 0) tempseg_size_mb
FROM v$sql_workarea_active
ORDER BY sid;
Now we have a large table and a monitoring tool. So we're all set to run some actual tests.
Since I'm the only tester using the instance, I might assume that the entire memory set aside
with
PGA_AGGREGATE_TARGET
will be available to me. As stated before, the segment size of the
table is about 150 MB, such that a
PGA_AGGREGATE_TARGET
setting of 256 MB should be more
than sufficient for an in-memory sort. So this is the value we will use:
SQL> ALTER SYSTEM SET pga_aggregate_target=256m;
System altered.
To start monitoring, set the
ORACLE_SID
and DBI environment variables (discussed further
in Chapter 22), then run
sql_workarea_active.pl
. The following example is from Windows. On
UNIX, use
export
to set environment variables.
