Questions on Shared_Pool and DB Buffer

Hi all,

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.

do yourself a favour and read the concept guide

Quote:

---

Originally posted by pando
do yourself a favour and read the concept guide

---

Very discouraging reply.:D I know these two questions are very silly. But, not all the silly questions have answers in the book. :rolleyes:

have you ever tried to read the concepts guide at all?

please check where your Shared pool getting utilised by using following script.
---------------
set serveroutput on;

declare
object_mem number;
shared_sql number;
cursor_mem number;
mts_mem number;
used_pool_size number;
free_mem number;
pool_size varchar2(512);
begin

-- 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
where s.statistic#=n.statistic#
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';

-- Display results
dbms_output.put_line ('Obj mem: '||to_char (object_mem) || ' bytes');
dbms_output.put_line ('Shared sql: '||to_char (shared_sql) || ' bytes');
dbms_output.put_line ('Cursors: '||to_char (cursor_mem) || ' bytes');
dbms_output.put_line ('MTS session: '||to_char (mts_mem) || ' bytes');
dbms_output.put_line ('Free memory: '||to_char (free_mem) || ' bytes ' || '('
|| to_char(round(free_mem/1024/1024,2)) || 'M)');
dbms_output.put_line ('Shared pool utilization (total): '||
to_char(used_pool_size) || ' bytes ' || '(' ||
to_char(round(used_pool_size/1024/1024,2)) || 'M)');
dbms_output.put_line ('Shared pool allocation (actual): '|| pool_size ||'
bytes ' || '(' || to_char(round(pool_size/1024/1024,2)) || 'M)');
dbms_output.put_line ('Percentage Utilized: '||to_char
(round(used_pool_size/pool_size*100)) || '%');
end;
/
----------------------------------------------

After that you may be able to find out which area to concentrate.

For Buffer pool you can check following views,

V$buffer_pool, v$buffer_pool_statistics

cheers, :) Dilip.

Hi Dilip Patel,

Thank you very much for your positive reply to my thread.

Hi Pando,

According to your philosophy, nobody would need to create any thread in any online forum, if everyone would read each and every book or if every solution is given in the book.

Please do not discourage users to use this or any forum.

Hope, you do not use same stick for everyone here.

Thanks,

Quote:

---

Originally posted by samdba
Hi Dilip Patel,

Thank you very much for your positive reply to my thread.

Hi Pando,

According to your philosophy, nobody would need to create any thread in any online forum, if everyone would read each and every book or if every solution is given in the book.

Please do not discourage users to use this or any forum.

Hope, you do not use same stick for everyone here.

Thanks,

---

you first read the docs, if you dont understand go to Metalink, if you still cant get it you can try here, and again have you ever tried to read the concepts guide at all?

Quote:

---

Originally posted by pando
again have you ever tried to read the concepts guide at all?

---

:D

No, Ellison. ;)

- Bill Gates :)

Pando,

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.