Questions on Shared_Pool and DB Buffer
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Questions on Shared_Pool and DB Buffer

  1. #1
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620

    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.
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart
    Share on Google+

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    do yourself a favour and read the concept guide
    Share on Google+

  3. #3
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Originally posted by pando
    do yourself a favour and read the concept guide
    Very discouraging reply. I know these two questions are very silly. But, not all the silly questions have answers in the book.
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart
    Share on Google+

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    have you ever tried to read the concepts guide at all?
    Share on Google+

  5. #5
    Join Date
    Feb 2003
    Location
    INDIA
    Posts
    96
    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.
    Dilip Patel
    OCP 8i

    Catch me online at Yahoo: ddpatel256
    Share on Google+

  6. #6
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    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,
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart
    Share on Google+

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    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?
    Share on Google+

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by pando
    again have you ever tried to read the concepts guide at all?
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
    Share on Google+

  9. #9
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    No, Ellison.

    - Bill Gates
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart
    Share on Google+

  10. #10
    Join Date
    Aug 2002
    Posts
    17
    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.
    Share on Google+

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width