Database Reference
In-Depth Information
When this watch_stat.sql script is run from the SQL*Plus command line, we'll see a listing of the PGA and
UGA memory statistics for the session, as well as temporary I/O. Now, if you run the script “ @run_query 65536 ” in a
session, you'll see output like this:
EODA@ORA12CR1> @run_query 65536
Connected.
EODA@ORA12CR1> set serveroutput off
EODA@ORA12CR1> set echo on
EODA@ORA12CR1> column sid new_val SID
EODA@ORA12CR1> select sid from v$mystat where rownum = 1;
SID
----------
23
EODA@ORA12CR1> alter session set workarea_size_policy=manual;
Session altered.
EODA@ORA12CR1> alter session set sort_area_size = &1;
old 1: alter session set sort_area_size = &1
new 1: alter session set sort_area_size = 65536
Session altered.
EODA@ORA12CR1> prompt run @reset_stat &SID and @watch_stat in another session here!
run @reset_stat 23 and @watch_stat in another session here!
EODA@ORA12CR1> pause
We can see the SID of this new session (23), and we've set our PGA memory management to manual and our
SORT_AREA_SIZE to 65,536 (64KB). Now the script tells us to run the two other scripts in another session, so we'll
do that:
EODA@ORA12CR1> @reset_stat 23
Table dropped.
Table created.
PL/SQL procedure successfully completed.
EODA@ORA12CR1> @watch_stat
6 rows merged.
NAME KBYTES_WRITES DIFF_KBYTES_WRITES
-------------------------------------------------- ------------- ------------------
physical reads direct temporary tablespace 0 0
physical writes direct temporary tablespace 0 0
session pga memory 876 0
session pga memory max 876 0
session uga memory 334 0
session uga memory max 334 0
6 rows selected.
 
Search WWH ::




Custom Search