| |
 |
|
Oracle Tips by Burleson Consulting
|
Using the v$workarea views
in Oracle
Oracle also has two new views to show active work area space, the
v$sql_workarea and the v$sql_workarea_active views. The
v$sql_workarea_active view will display all of the work areas that
are currently executing in the instance. Note that small sorts
(under 65,535 bytes) are excluded from the view, but you can use the
v$sql_workarea_active view to quickly monitor the size of all large
active work areas.
select
to_number(decode(SID, 65535, NULL, SID)) sid,
operation_type OPERATION,
trunc(WORK_AREA_SIZE/1024) WSIZE,
trunc(EXPECTED_SIZE/1024) ESIZE,
trunc(ACTUAL_MEM_USED/1024) MEM,
trunc(MAX_MEM_USED/1024) "MAX MEM",
number_passes PASS
from
v$sql_workarea_active
order by
1,2;
Here is a sample listing from this script.
SID OPERATION WSIZE ESIZE MEM MAX MEM PASS
--- --------------------- ----- --------- --------- --------- ----
27 GROUP BY (SORT) 73 73 64 64 0
44 HASH-JOIN 3148 3147 2437 6342 1
71 HASH-JOIN 13241 19200 12884 34684 1
This output above shows that session 44 is running a hash-join whose
work area is running in one-pass mode. This work area is currently
using 2 megabytes of PGA memory and has used in the past up to 6.5
megabytes of PGA memory.
This view is very useful for viewing the current memory operations
within Oracle, and you can use the SID column to join into the
v$process and v$session views for additional information about each
task.
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
