I have two questions regarding Shared pool and DB Buffer.
1. Our Shared Pool Size is around 550MB, sometimes, in OEM performance monitor, I see that free Shared Pool goes below than 10M. I want to know how does Oracle manage in such scenarios.
Do we need to manually Flush it out?
2. How to check how much free database buffer cache is left? Is there any table, which can show free current DB buffer?
Thanks in Adv.
To handle yourself, use your head. To handle others, use your heart
-- Stored objects (packages, views)
select sum(sharable_mem) into object_mem from v$db_object_cache;
-- Shared SQL -- need to have additional memory if dynamic SQL used
select sum(sharable_mem) into shared_sql from v$sqlarea;
-- User Cursor Usage -- run this during peak usage.
-- assumes 250 bytes per open cursor, for each concurrent user.
select sum(250*users_opening) into cursor_mem from v$sqlarea;
-- For a test system -- get usage for one user, multiply by # users
-- select (250 * value) bytes_per_user
-- from v$sesstat s, v$statname n
-- where s.statistic# = n.statistic#
-- and n.name = 'opened cursors current'
-- and s.sid = 25; -- where 25 is the sid of the process
-- MTS memory needed to hold session information for shared server users
-- This query computes a total for all currently logged on users (run
-- during peak period). Alternatively calculate for a single user and
-- multiply by # users.
select sum(value) into mts_mem from v$sesstat s, v$statname n
and n.name='session uga memory max';
-- Free (unused) memory in the SGA: gives an indication of how much memory
-- is being wasted out of the total allocated.
select sum(bytes) into free_mem from v$sgastat
where name = 'free memory';
-- For non-MTS add up object, shared sql, cursors and 30% overhead.
used_pool_size := round(1.3*(object_mem+shared_sql+cursor_mem));
-- For MTS add mts contribution also.
-- used_pool_size := round(1.3*(object_mem+shared_sql+cursor_mem+mts_mem));
select value into pool_size from v$parameter where name='shared_pool_size';
Maybe after 5500 posts, you need to take a break to regain your sense of patience and helpfulness.
Besides many years as an Oracle and SQL Server DBA, I actively participate in several boards for car and boat engine mechanics, which are my hobbies.
Yes, we often see posts by newbies asking which direction to turn a wrench to loosen a spark plug. Sure, it makes me grit my teeth and want to tell them RTFM, but; that's not being helpful or inviting them to keep using the board. Once they learn something they can help others...but not if you insult them and make them feel unwanted.
This is not the first thread where I have noticed you being abrupt with posters. Come on, be nice to them.