Database Reference
In-Depth Information
16 from (
17 select active,
18 name,
19 case when name like '%ga mem%' then round(value/1024/1024,1) else value end val
20 from sess_stats
21 where active < 275
22 )
23 group by active
24 ))
This part of the query retrieved the records from the table of metrics when there were less than 275 active
sessions, converted the metrics for memory (UGA/PGA memory) from bytes into megabytes, and then pivoted—
turned rows into columns—on the four interesting metrics. Once we got those four metrics in a single record, we used
analytics (the
LAG()
function specifically) to add to each row the prior rows' total observed PGA and total observed
I/O to temp so we could easily see the incremental differences in these values. Back to the data—as you can see,
when I had a few active sessions, my sorts were performed entirely in memory. For an active session count of 1 to
somewhere less than 50, I could sort entirely in memory. However, by the time I had 50 users logged in and actively
sorting, the database started reining in the amount of memory I was allowed to use at a time. It would have taken a
couple of minutes before the amount of PGA being used fell back within acceptable limits (the 256MB request), but
eventually it would at these low concurrent user levels. The amount of PGA memory allocated to the session we were
watching dropped from 15.2MB to 7.7MB and settled on around 5.2MB (remember, parts of that PGA are not for work
area (sorting) allocations, but are for other operations; just the act of logging in created a .5MB PGA allocation). The
total PGA in use by the system remained within tolerable limits until somewhere around 126 users. At that point, I
started to exceed on a regular basis the
PGA_AGGREGATE_TARGET
, and continued to do so until the end of the test. I gave
the database instance in this case an impossible task; the very act of having 126 users, most executing PL/SQL, plus
the sort they were all requesting, just did not fit into the 256MB of RAM I had targeted. It simply could not be done.
Each session therefore used as little memory as possible, but had to allocate as much memory as it needed. By the
time I finished this test, the active sessions were using a total of about 560MB of PGA memory—as little as they could.
You should, however, consider what that output would look like under a manual memory management situation.
Suppose the
SORT_AREA_SIZE
had been set to 5MB. The math is very straightforward: each session would be able to
perform the sort in RAM (or virtual memory as the machine ran out of real RAM), and thus would consume 6MB to
7MB of RAM per session (the amount used without sorting to disk in the previous single-user case). The memory use
would look something like this:
EODA@ORA12CR1> column total_pga format 9,999
EODA@ORA12CR1> with data(users)
2 as
3 (select 1 users from dual
4 union all
5 select users+25 from data where users+25 <= 275)
6 select users, 7 my_pga, 7*users total_pga
7 from data
8 order by users
9 /
