Are pga stats correct ?

From: Ranko Mosic <ranko.mosic_at_gmail.com>
Date: Fri, 10 Jun 2005 21:59:05 -0400
Message-ID: <367369f105061018597ca9a3c@mail.gmail.com>

Hi all,=20
I am running some scripts from Chapter 4 of Kytes forthcoming book .=20 I want to exactly measure PGA usage.
Array is created:
SQL> l
  1 create or replace package demo_pkg
  2 as
  3 type array is table of char(1) index by binary_integer;   4 g_data array;
  5 end;
  6*

SQL>
SQL>
SQL> /

Package created.
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D

Memory used checked:=20
SQL> get check_mem
  1 select a.name, to_char(b.value, '999,999,999') value   2 from v$statname a, v$mystat b
  3 where a.statistic# =3D b.statistic#   4* and a.name like '%ga memory%';
  /
SQL>

NAME                                                             VALUE
---------------------------------------------------------------- ----------=
--
session uga memory                                                    610,4=
32
session uga memory max                                                610,4=
32
session pga memory                                                  1,088,0=
76
session pga memory max                                              1,088,0=

76

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

Now we populate array with 5 elements, one byte in size each: SQL> get pack_array
  1 begin
  2 for i in 1 .. 5
  3 loop
  4 demo_pkg.g_data(i) :=3D 'x';
  5 end loop;
  6* end;
SQL> /
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

We expect pga growing by 5 bytes, give or take, right ?=20 Check memory used again:=20

SQL> @check_mem

NAME                                                             VALUE
---------------------------------------------------------------- ----------=
--
session uga memory                                                    610,4=
32
session uga memory max                                                675,8=
96
session pga memory                                                  1,022,5=
40
session pga memory max                                              1,153,6=

12

Session pga memory DECREASED; pga memory max went up by 65536 bytes, much more then expected couple of bytes. What gives ?=20
Regards, Ranko.

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jun 10 2005 - 22:04:13 CDT