Printable View
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
Very discouraging reply.:D I know these two questions are very silly. But, not all the silly questions have answers in the book. :rolleyes:Quote:
Originally posted by pando
do yourself a favour and read the concept guide
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,
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 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,
:DQuote:
Originally posted by pando
again have you ever tried to read the concepts guide at all?
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.
Why would someone ask question when the documentation explains all about the question with all the details you can expect?
Here is my 2 cents:
I had been to Disney World, Orlando, USA 3 times. It is a huge place where millions of people visit every day from all over world. The theme park distribute a very good map to all the people that explains where the RESTROOMS are located. Despite reading the map and finding the location, many people ask the workers in the park “Where is the restroom?. The workers never replied to any one, “Go and Read the Map”. He/She will promptly guide the visitors, and some times he/she event accompany with the visitor to the closest to the location..
The GM of the theme park once said in a TV interview that “Where is the Rest Room?” is the highest number of questions asked by the visitors. And the workers are trained to be patience to answer the question in a polite manner.
That is why these theme parks are so successful, and attract people all over the world.
When someone asks a question, the seniors should try to answer the question or leave a place to others who can do the job.
Tamil
First, I dont think I am a senior
Second, I left others to answer, I did not tell others not to answer
Third, you cannot compare disney workers with internet surfers (to start with one gets paid and the other not)
Fourth, it takes 30 seconds to find the answe in the documentation
Fifth, it's simply laziness if you dont even dare to look at documentation
Sixth, a forum is not a school
Seventh, a forum is not a Q&A place (try that on metalink forum and see how many days they take to answer you)
Eighth, I am just surprised how someone with over 500 posts would ask how shared pool works and call himself dba but of course that's my surprise
Nineth, one expresses what they think, if you cannot take it then that's your problem, I did not offend anyone, I only ask them to look the documentation first
Hi Pando and all other posters to this forum,
This way, people will not get any help from this forum in case of real emergency because at that time, there will not be any time to go and read documentations and this forum only can provide quick help.
The success of this forum greatly depends on all the users, whether novice or experienced.
I can show you users with 1000+ posts asking questions which are not easily found in the Docs but some questions just arise out of curiosity.
Pando, I don’t say that you are philosophically wrong to ask users to read the docs first you cannot say it in any forum. You can say it to your juniors at work. The way of your saying is very harsh and discouraging.
I hope that you would use better lenguage in future in this forum.
Thanks others for support,
Folks,
I would like to take the liberty to close this thread. Thanks for all your thoughts and comments. Please lets all lend hands to one another and share our knowledge.
Peace,
Sam