Oracle memory questions....
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Oracle memory questions....

  1. #1
    Join Date
    Oct 2000
    Posts
    103
    1) What tables/columns can I look at to find out how much memory each user connection is using and also to sum them.

    2) Is memory used for client connections taken from the shared_pool?

    3) What does boosting the FIXED_SGA affect?

    4) In the following query does "free memory" refer to what's available in the shared pool???


    select NAME "PARAMETER",
    to_char(BYTES, '999,999,999,999') "VALUE"
    from v$sgastat
    where name = 'free memory';

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    For checking the memory you could use the views that I had mentioned in the following thread. [url]http://ora.dbasupport.com/forums/showthread.php?threadid=4324[/url]


    Sam

  3. #3
    Join Date
    Oct 2000
    Posts
    103

    Unhappy

    well the reason I am asking these questions is... several weeks ago a few users were getting oracle errors stating that thier client could not access somenumber of bytes to create an oracle connection( it was an ora-###) . I looked it up and it said to increase the shared pool size( which was set at 18000000) , after monitoring the free memory for about a week( with the query "select NAME "PARAMETER",
    to_char(BYTES, '999,999,999,999') "VALUE"
    from v$sgastat
    where name = 'free memory';") I found that free memory was usualy between 3 MB and as low as 100K I increased it to shared_pool_size = 72000000. But a couple clients have gotten the error again and as I check free memory again I have found that that it is varying from 19mb free to less then 1 mb free. I would have thought that increasing the HARED POOL_SIZE by such a great amount would have kept me from seeing this error again for a loooong time. From looking at the other post I am wondering if I need to also increase my db_block_buffers which is currently set at 6400 .
    Also the app that is getting the error is written in Access, and we also have several VB apps and a 2 C++ apps.

    Thanks TONS,
    SM

  4. #4
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    check the code that runs in the SGA. It looks loke the query had been poorly written. Other things are that you could pin the freq. used objects in the sga and reduce the reloads. Are the tables they are using is analyzed, if not analyze then and check for chained rows and etc. Run an audit on the perticular schema and check the audit trail for more details.

    Good luck,
    sam

  5. #5
    Join Date
    Oct 2000
    Posts
    103
    What do you think about increasing my db_block_buffers which is currently set at 6400 to 12800. What is the ratio of shared_pool_size to db_block_buffers, or are they not related?

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095

    There is no relationship between db_block_buffers and shared_pool other than they both live in the SGA.

    db_block_Buffers controls the amount of memory you will use for data caching. For example, an update statement. In order to evaluate whether you need to increase your db_block_buffers, you should examine your hit ratio:
    select (1-(sum(decode(name,'physical reads',value,0)) /
    (sum(decode(name,'db block gets',value,0))+
    sum(decode(name,'consistent gets',value,0))))) * 100 "buff hit"
    from v$sysstat

    The hit ratio should be above 98%.


    shared_pool controls the amount of memory you will use for other operations; library cache, object cache, etc.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  7. #7
    Join Date
    Feb 2000
    Location
    New York,U.S.A.
    Posts
    245
    You could also try with
    SQL>alter system flush shared_pool;
    It will reclaim some memory back for you.

  8. #8
    Join Date
    Oct 2000
    Posts
    103
    thanks for your help, how about these questions?


    1) Is memory used for client connections taken from the shared_pool?


    2) In the following query does "free memory" refer to what's available in the shared pool???

    select NAME "PARAMETER",
    to_char(BYTES, '999,999,999,999') "VALUE"
    from v$sgastat
    where name = 'free memory';


    Thanks tons
    SM

  9. #9
    Join Date
    Feb 2000
    Location
    New York,U.S.A.
    Posts
    245
    The answer to your question NO2 is Yes. Here the 'free memory' refers to shared_pool.
    I have similar problem with my database too. I have to do alter system flush shared_pool almost everyday. Alternatives are to increase shared_pool, but it constrains by the server memory. Since we don't have enough memory on server, I have to flush memory.Of course, you have to tune your sql statements make it as optimal and efficient as possible.


  10. #10
    Join Date
    Jul 2000
    Location
    Oxford, OH
    Posts
    117
    What is the Oracle error you are getting?

    Could you please post your initialization file and
    how much memory your machine has?

    Also run something like:

    select table_name, num_rows, blocks
    from dba_tables
    where owner not in ('SYS','SYSTEM')

    You might want to consider using recycle and keep
    pools as well. Is this an OLTP database or a DSS
    system?

    What is your db_block_size? The total size of your
    SGA is db_block_size * db_block_buffers. What is
    your current hit rate for your caches?

    You may want to run a bstat/estat during times of high
    activity for a couple of hours and examine the results.
    There are also some Oracle parameters you can specifiy
    that will give you some extended statistics to determine
    the best size for db_block_buffers (DB_BLOCK_LRU_STATISTICS and DB_BLOCK_LRU_EXTENDED_STATISTICS). Make sure that you have the parameter TIMED_STATISTICS = TRUE.

    Joe
    _________________________
    Joe Ramsey
    Senior Database Administrator
    dbaDirect, Inc.
    (877)687-3227

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