Memory Structures - Expert Oracle Database Architecture - page 132

■ The watch_stat.sql script must be run in the same session as the reset_stat.sql script; reset_stat.sql
sets the :sid bind variable necessary for the MERGE statement. alternatively, you can execute exec :sid := <sid>,
where <sid> is the SID you observe in your testing.

Note

So, before we begin we can see that we have about 334KB of data in the UGA and 876KB of data in the PGA. The
first question is, “How much memory are we using between the PGA and UGA?” That is, are we using 334KB + 876KB
of memory, or are we using some other amount? This is a trick question, and one that you can't answer unless you
know whether the monitored session with SID 23 was connected to the database via a dedicated server or a shared
server—and even then it might be hard to figure out. In dedicated server mode, the UGA is totally contained within
the PGA, in which case we would be consuming 876KB of memory in our process or thread.
In shared server mode, the UGA is allocated from the SGA, and the PGA is allocated in O/S memory private to the
shared server process. So, in shared server mode, by the time we get the last row from the preceding query, the shared
server process may be in use by someone else. That PGA isn't “ours” anymore, so technically we are using 334KB of
memory (except when we are actually running the query, at which point we are using 1210KB of memory between
the combined PGA and UGA). In this case, I used a dedicated server (it would be impossible to do the test accurately
otherwise) and we are using a grand total of 876KB of memory in the combined PGA and UGA. So, let's now run the
first big query in session 23, which is using manual PGA memory management in dedicated server mode. We just
have to go back to that session where we ran run_query.sql and hit enter to start the query running:

■ Since we haven't set a SORT_AREA_RETAINED_SIZE , its reported value will be zero, but its used value will
match SORT_AREA_SIZE .

Note

EODA@ORA12CR1> set termout off
EODA@ORA12CR1> prompt run @watch_stat in another session here!
run @watch_stat in another session here!
EODA@ORA12CR1> pause

The point where you see set termout off is the point where the large query is executing, we told SQL*plus to
run the query but not print on the screen (it would take a while for over 70,000 lines to be printed). Now if we run our
watch_stat.sql script again in the second session, we'll see something like the following:

EODA@ORA12CR1> @watch_stat
6 rows merged.

NAME KBYTES_WRITES DIFF_KBYTES_WRITES
-------------------------------------------------- ------------- ------------------
physical reads direct temporary tablespace 3000 3000
physical writes direct temporary tablespace 3000 3000
session pga memory 1196 320
session pga memory max 1260 384
session uga memory 654 320
session uga memory max 718 384
6 rows selected.