| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Memory stats on 8i
Dear list,
While trying to retrieve some useful memory metrics from statspack data, I
discovered
a couple of disconcerting problems with both statspack and v$sysstat.
Here's an example of querying stats$sysstat for "session pga memory" and
"session
uga memory":
NAME SNAP_TIME BYTES
-------- ---------------- --------------------
Sess Mem 02/12/2004 14:15 5,283,446,030,900 02/12/2004 14:30 5,283,942,124,164 02/12/2004 14:45 5,284,433,165,904 02/12/2004 15:00 5,284,911,244,452 02/12/2004 15:15 5,285,416,815,816 02/12/2004 15:30 5,285,913,292,596 02/12/2004 15:45 5,286,408,666,940 02/12/2004 16:00 5,286,892,600,984 02/12/2004 16:15 5,287,399,692,508 02/12/2004 16:30 5,287,867,287,248 02/12/2004 16:45 5,288,350,093,876 02/12/2004 17:00 5,288,820,633,944 02/12/2004 17:15 5,289,315,381,036 02/12/2004 17:30 5,289,786,863,136
I doubt many windows boxes carry that much RAM around with them.
Thinking it might be a problem with statspack, a query on v$sysstat was then run:
17:22:10 CIMORAPROD - perfstat_at_dal3 SQL> l
1 select class,name, value
2 from v$sysstat
3 where name like '%&&statname%'
4* order by class,name
17:22:15 CIMORAPROD - perfstat_at_dal3 SQL> /
CLASS NAME VALUE
----- ---------------------------------------- --------------------
1 session pga memory 5,604,491,571,208 session pga memory max 5,631,672,698,788 session uga memory 10,122,362,780 session uga memory max 94,136,262,424 64 sorts (memory) 145,100,842
5 rows selected.
17:22:16 CIMORAPROD - perfstat_at_dal3 SQL>
No joy. Turns out this is due to a bug that was not fixed until some
version of 9i, and ostensibly
backported to 8.1.7.2. Apparently not, as this is an 8.1.7.4.1 database.
No problem, I think, just get the data from stats$sesstat;
17:25:02 CIMORAPROD - perfstat_at_dal3 SQL> select * from stats$sesstat;
no rows selected
17:25:22 CIMORAPROD - perfstat_at_dal3 SQL>
Even less joy.
It seems that no level of statspack snapshot will populate the session
tables.
A metalink search turns up the problem, but no solution.
This doesn't work in 9i either BTW.
If anyone has encountered this and found a work around, would you mind sharing.
Oh yeah, this is on top of the bug in 8.1.6 that gives highly erroneous
CPU numbers if
you happen to be using DBA_JOBS. ( a different database )
Thanks,
Jared
PS. The network stats appear to be good.
 |
 |