Database Reference
In-Depth Information
loop
null;
end loop;
end loop;
end;
/
exit
and here's the script (stored in a file named
stop.sql
) to stop these processes from running:
begin
dbms_alert.signal( 'WAITING', '' );
commit;
end;
/
To observe the differing amounts of RAM allocated to the session I was measuring, I initially ran the
SELECT
in
isolation—as the only session. I captured the statistics and saved them into the
SESS_STATS
table, along with the count
of active sessions. Then I added 25 sessions to the system (i.e., I ran the preceding benchmark script (
gen_load.sql
)
with the
for i in 1 .. 999999 loop
in 25 new sessions). I waited a short period of time—one minute for the system
to adjust to this new load—and then I created a new session and ran the single sort query from earlier, capturing of the
metrics the first time through the loop. I did this repeatedly, for up to 500 concurrent users.
■
On the apress web site for this topic, you can download the scripts used for this experiment. In the
ch04
directory, the
run.sql
script automates the test described in this section.
Tip
It should be noted that I asked the database instance to do an impossible thing here. As mentioned previously,
based on the first time we ran
watch_stat.sql,
each connection to Oracle—before even doing a single sort—
consumed a little more than .5MB of RAM. At 500 users, we would be very close to the
PGA_AGGREGATE_TARGET
setting just by having them all logged in, let alone actually doing any work! This drives home the point that the
PGA_AGGREGATE_TARGET
is just that: a target, not a directive. We can and will exceed this value for various reasons.
Now we are ready to report on the finding; for reasons of space, we'll stop the output at 275 users—since the data
starts to get quite repetitive:
EODA@ORA12CR1> column active format 999
EODA@ORA12CR1> column pga format 999.9
EODA@ORA12CR1> column "tot PGA" format 999.9
EODA@ORA12CR1> column pga_diff format 999.99
EODA@ORA12CR1> column "temp write" format 9,999
EODA@ORA12CR1> column "tot writes temp" format 99,999,999
EODA@ORA12CR1> column writes_diff format 9,999,999
EODA@ORA12CR1> select active,
2 pga,
3 "tot PGA",
4 "tot PGA"-lag( "tot PGA" ) over (order by active) pga_diff,
5 "temp write",
6 "tot writes temp",
7 "tot writes temp"-lag( "tot writes temp" ) over (order by active) writes_diff
