Database Reference
In-Depth Information
Consider the following quick example. We'll create a package that can hold some persistent (global) data in the
server:
EODA@ORA12CR1> create or replace package demo_pkg
2 as
3 type array is table of char(2000) index by binary_integer;
4 g_data array;
5 end;
6 /
Package created.
Now we'll measure the amount of memory our session is currently using in the PGA/UGA (I used a dedicated
server in this example, so the UGA is a subset of the PGA memory):
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,526,568 1.5
session uga memory max 1,526,568 1.5
session pga memory 2,208,088 2.1
session pga memory max 2,208,088 2.1
Initially we are using about 2.1MB of PGA memory in our session (as a result of compiling a PL/SQL package,
running this query, etc.). Now, we'll run our query against
T
again using the same 256MB
PGA_AGGREGATE_TARGET
(this was done in an otherwise idle instance; we are the only session requiring memory right now):
EODA@ORA12CR1> set autotrace traceonly statistics;
EODA@ORA12CR1> select * from t order by 1,2,3,4;
72616 rows selected.
Statistics
----------------------------------------------------------
105 recursive calls
0 db block gets
1103 consistent gets
993 physical reads
0 redo size
3665844 bytes sent via SQL*Net to client
53795 bytes received via SQL*Net from client
4843 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
72616 rows processed
EODA@ORA12CR1> set autotrace off
