Check current space
select * from v$parameter where name like '%shared_pool%'; -- 3099794371
--by default do not have to define, (define to reserve memory)
select * from v$parameter where name like '%sga%';
- sga target = sga max target (usual) = 5368709120
SELECT p.name, to_number(p.value) psize, s.bytes fspace, (s.bytes/p.value) * 100 pfree
FROM v$parameter p, v$sgastat s
WHERE p.name = 'shared_pool_size'
AND s.pool = 'shared pool'
AND s.name = 'free memory';
select * from v$sgastat where name='free memory';
(1/4/8 hr to check free memory)
shared pool free memory 1381982232
large pool free memory 250773504
java pool free memory 34659264
streams pool free memory 32893648
11g? memory_target?
- sga+ pga combine
Flush Shared Pool
When testing the SQL it is a good idea to periodically flush the shared pool to remove older execution plans.
alter system flush shared_pool;
There are cases where flushing the Oracle shared pool can dramatically improve performance. This is normally true in an environment where the Oracle application issues a large amount of non-reusable SQL statements. The library cache becomes floored with non-reusable SQL and there will be significant slowdowns as Oracle futilely parses incoming SQL looking for a pre-parsed matching statement.