Database Reference
In-Depth Information
Notice this time that the
session xxx memory
and
session xxx memory max
values don't match. The
session
xxx memory
value represents how much memory we are using right now. The
session xxx memory max
value
represents the peak value we used at some time during our session while processing the query.
■
In these examples, I wouldn't expect that you'd see exactly the same numbers I've printed here. The amount of
memory used is affected by many things, such as Oracle version, the operating system and its features and capabilities,
the amount of data placed into the table
T
, and so on. you should expect variations in memory amounts, but the overall
picture will be the same.
Note
As you can see, our memory usage went up—we've done some sorting of data. Our UGA memory increased from
334KB to 718KB during the processing (max value) of our query. To perform our query and the sorting, Oracle allocated
a sort area for our session. Additionally, the PGA memory went from 876KB to 1196KB. Also, we can see that we did
3,000 writes and reads to and from temp (since the data we sorted could not have fit into 64KB, our
SORT_AREA_SIZE
).
By the time we finish our query and exhaust the result set, we can see that our PGA has shrunk somewhat
(note that in Oracle8
i
and before, you wouldn't expect to see the PGA shrink back at all; this is a new feature with
Oracle9
i
and later).
Let's retry that operation but play around with the size of our
SORT_AREA_SIZE
by
increasing it to 1MB. We'll log
out of the session we're monitoring and log back in, following the directions to increase our
SORT_AREA_SIZE
to 1MB.
Remember that in the other session we are monitoring from, you must run the
reset_stat.sql
script to start over.
As the beginning numbers are consistent (the output of the first
watch_stat.sql
should be the same in a new
session), I don't display them here—only the final results:
NAME KBYTES_WRITES DIFF_KBYTES_WRITES
-------------------------------------------------- ------------- ------------------
physical reads direct temporary tablespace 1043 1043
physical writes direct temporary tablespace 1043 1043
session pga memory 1196 320
session pga memory max 2732 1856
session uga memory 718 384
session uga memory max 1756 1422
6 rows selected.
As you can see, our UGA memory has grown considerably this time during the processing of our query. It
temporarily grew to about 1,700KB (a bit more than 1MB, our
SORT_AREA_SIZE
), but the amount of physical I/O we
had to do to sort this data dropped considerably as well (use more memory, swap to disk less often). We may have
avoided a multipass sort as well, a condition that happens when there are so many little sets of sorted data to merge
together that Oracle ends up writing the data to temp more than once. Now, let's go to an extreme here and use a 1GB
SORT_AREA_SIZE
:
NAME KBYTES_WRITES DIFF_KBYTES_WRITES
-------------------------------------------------- ------------- ------------------
physical reads direct temporary tablespace 0 0
physical writes direct temporary tablespace 0 0
session pga memory 1132 256
session pga memory max 11372 10496
session uga memory 654 320
session uga memory max 10631 10296
6 rows selected.