 |
|
Oracle Tips by Burleson |
The Oracle
v$workarea View
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. The v$sql_workarea_active
view can be used to quickly monitor the size of all large active work
areas.
See code depot for
full scripts
--
****************************************************************
-- Display PGA workarea details
--
-- Copyright (c) 2003 By Donald K. Burleson - All Rights reserved.
-- ****************************************************************
select
. . .
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 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 in the past, has used up to 6.5 megabytes.
This view is very
useful for assessing the current memory operations within Oracle. You
can use the SID column to join into the v$process and
v$session views for additional information about each task.
 |
For more details and scripts, see my new book "Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
|
|
For more details and scripts, see my new book "
Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot. |
|
