Database Reference
In-Depth Information
As you can see, the sort was done entirely in memory, and in fact if we peek at our session's PGA/UGA usage,
we can see how much we used:
EODA@ORA12CR1> select a.name, to_char(b.value, '999,999,999') bytes,
2 to_char(round(b.value/1024/1024,1), '99,999.9' ) mbytes
3 from v$statname a, v$mystat b
4 where a.statistic# = b.statistic#
5 and a.name like '%ga memory%';
NAME BYTES MBYTES
------------------------------ ------------ ---------
session uga memory 1,854,008 1.8
session uga memory max 11,213,280 10.7
session pga memory 2,470,232 2.4
session pga memory max 12,104,024 11.5
We see 11.5MB of RAM being used, in the ballpark range of the 15MB we observed earlier in the prior test for
sorting. Now we'll fill up that CHAR array we have in the package (a CHAR data type is blank-padded so each of these
array elements is exactly 2,000 characters in length):
EODA@ORA12CR1> begin
2 for i in 1 .. 200000
3 loop
4 demo_pkg.g_data(i) := 'x';
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
If we then measure our session's current PGA utilization, we find something similar to the following:
EODA@ORA12CR1> select a.name, to_char(b.value, '999,999,999') bytes,
2 to_char(round(b.value/1024/1024,1), '99,999.9' ) mbytes
3 from v$statname a, v$mystat b
4 where a.statistic# = b.statistic#
5 and a.name like '%ga memory%';
NAME BYTES MBYTES
------------------------------ ------------ ---------
session uga memory 469,569,304 447.8
session uga memory max 469,569,304 447.8
session pga memory 470,921,560 449.1
session pga memory max 470,921,560 449.1
Now, that is memory allocated in the PGA that the instance itself can't control. We already exceeded the PGA_
AGGREGATE_TARGET set for the entire instance in this single session— and there is quite simply nothing the database can
do about it. It would have to fail our request if it did anything, and it will do that only when the OS reports back that
there is no more memory to give (ORA-04030). If we wanted, we could allocate more space in that array and place
more data in it, and the instance would just have to do it for us.
 
Search WWH ::




Custom Search