Shared pool
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 32

Thread: Shared pool

  1. #1
    Join Date
    Jun 2001
    Posts
    30
    I had run this query on my database and got the following


    SQL> select to_number(v$parameter.value)value, v$sgastat.bytes,
    2 (v$sgastat.bytes/v$parameter.value)*100 "Percent Free"
    3 from v$sgastat,v$parameter
    4 where v$sgastat.name = 'free memory'
    5 and v$parameter.name = 'shared_pool_size';

    Shared Pool Size Free Bytes Percent Free
    ---------------- ---------------- ------------
    29,000,000 2,878,876 9.92715862

    Can someone please assist me with this problem. i am made to believe that the shared pool is small. To what value should I set the shared pool?
    PMS

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    how have you determined that is small with that simple result?

  3. #3
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Here is the basic rule:

    Check from init.ora the value for DB_BLOCK_BUFFERS. Calculate how much this makes in bytes. Say you use block size of 8K and DB_BLOCK_BUFFERS = 5000. Then you come with a value of 40000K. The SHARED_POOL_SIZE should generally be 50% up to 150% of the calculated value. So we are now in the interval (approx.) 20M-60M. Now comes the tricky part, 20M? 40M? 60M? It depends on the amount of stored PL/SQL (functions, packages, etc). If you have a lot of them go fot 60M shared pool. There are Oracle DBs without any stored procedures. Then you might downsize your shared pool to even 10-15% of the calculated value based on DB_BLOCK_BUFFERS.

    Was my explanation clear enough :-)


  4. #4
    Join Date
    Aug 2000
    Posts
    236
    Julian,

    What happens when I know that my buffer cache is not enough? What I mean is, because of poorly written SQL's and poor indexing done on a legacy system, a lot of full table scans are taking place and my buffer cache hit ratio is 50-60%. How do I go about calculating the right size of the SGA?
    I am in the process of fixing the full table scan problems but till then???

    Thanks,
    Nizar

  5. #5
    Join Date
    May 2001
    Location
    Delhi
    Posts
    340
    Hi,

    I am running SAP on Oracle 8.1.6 on NT.

    DB Size is 106 GB.

    I don't know much about SAP. How can I find abt the amount of PL/SQL stored.

    My SGA is sized at 400000000.
    buffers r 90000
    block size is 8k

    any views reg this.
    --------------------------
    The Time has come ....

  6. #6
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by nabaig
    Julian,

    What happens when I know that my buffer cache is not enough? What I mean is, because of poorly written SQL's and poor indexing done on a legacy system, a lot of full table scans are taking place and my buffer cache hit ratio is 50-60%. How do I go about calculating the right size of the SGA?
    I am in the process of fixing the full table scan problems but till then???

    Thanks,
    Nizar

    Increase first DB_BLOCK_BUFFERS until

    select 1 - (phy.value / (cur.value + con.value)) "BUFFER HIT RATIO (> 0.90 OK)"
    from v$sysstat cur, v$sysstat con, v$sysstat phy
    where cur.name = 'db block gets'
    and con.name = 'consistent gets'
    and phy.name = 'physical reads';

    returns more than 0.9 There should be some activity in the DB in order to get the correct ratio. It might take some days to get the BUFFER HIT RATIO in the high 90s. How big is your DB, how much RAM do you have and what is the current value of DB_BLOCK_BUFFERS?

    Ones you fixed the buffer's number, you can estimate the proper size of the shared pool.


  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    to calculate shared pool check v$db_object_cache, v$sqlarea


  8. #8
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    How can I find abt the amount of PL/SQL stored.
    For user SCOTT run:

    select ceil(sum(vsize(text))/1024) "PL/SWL in Kilobytes" from dba_source where owner='SCOTT';

    My SGA is sized at 400000000.
    buffers r 90000
    block size is 8k
    any views reg this.
    As I don't know anything about your DB, I could only say that the values a bit high but if you have the memory.... How much RAM do you have?


  9. #9
    Join Date
    May 2001
    Location
    Delhi
    Posts
    340
    Statistics

    RAM 2 GB

    Hit Ratio 96 - 97 % (Observed over a period of time)

    Now ?
    --------------------------
    The Time has come ....

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    extracted from ilt books

    SELECT SUM(sharable_mem)
    FROM v$sqlarea
    WHERE executions > 5
    /

    SELECT SUM(250 * users_opening)
    FROM v$sqlarea
    /

    SELECT SUM(sharable_mem)
    FROM v$db_object_cache
    WHERE type = 'PACKAGE'
    or type = 'PACKAGE BODY'
    or type = 'FUNCTION'
    or type = 'PROCEDURE'
    /

    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 = 15
    /


    sum all is your shared pol usage

    DONT set your shared pool too big if itīs not necessary, you will increase library cache lock latch contention if you do so

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