Skip to Main Content

Finding Shared_pool_size

YockeeApr 14 2022

Hello experts,
I have difficulties in finding the size of Shared_pool_size. Is it from v$sgastat or from v$parameter ?
The queries from both parameters gives out different results.

For v$sgastat, I get 5.6 GB. The query i use :
SELECT pool, sum(bytes)/1024/1024/1024 FROM v$sgastat
WHERE pool = 'shared pool' GROUP BY pool;
Just incase you need the free shared pool, I get 2.3 GB. The query for free shared pool :
SELECT * FROM v$sgastat
WHERE name = 'free memory' AND pool = 'shared pool';

Whereas, for shared pool using v$parameter, I get 2.9 GB. The query i use :
select NAME, VALUE/1024/1024/1024 from v$parameter where name='shared_pool_size';

I am using Oracle 11.2 G with AMM enabled, with SGA_TARGET 14 GB and MEMORY_TARGET 26 GB.

thanks for any advice.

Post Details