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.
 
 
Search WWH ::




Custom Search