| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Shared Pool: Can it be filled *over* 100% ?
Hi Sybrand
> The query is just plain wrong, as I have demonstrated earlier today.
> Multiple records will be selected from v$sgastat, except the 'free
> memory' record. Those records will be summed *after* the cartesian
> product with v$parameter has been determined. Try it yourself.
I tried it and I saw no difference...
SQL> select (sum(a.bytes)/max(b.value))*100 Shared_Pool_Pct
2 from v$sgastat a, v$parameter b
3 where a.pool = 'shared pool'
4 and a.name != 'free memory'
5 and b.name = 'shared_pool_size';
SHARED_POOL_PCT
23.1099674
SQL> select sum(a.bytes)
2 from v$sgastat a
3 where a.pool = 'shared pool'
4 and a.name != 'free memory';
SUM(A.BYTES)
6785116
SQL> select b.value
2 from v$parameter b
3 where b.name = 'shared_pool_size';
VALUE
SQL> select (6785116/29360128)*100 Shared_Pool_Pct 2 from dual;
SHARED_POOL_PCT
23.1099674
Instead, as I wrote, the value specified by shared_pool_size is not the real size of the SGA. This fact lead to the wrong computation.
SQL> select b.value
2 from v$parameter b
3 where b.name = 'shared_pool_size';
VALUE
SQL> select sum(a.bytes)
2 from v$sgastat a
3 where a.pool = 'shared pool';
SUM(A.BYTES)
32375536
Anyway, such statistics are useless to know if the shared pool is correctly sized or not.
Chris
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
 |
 |