DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4


  1. #1
    Join Date
    Jan 2001
    Urgent help please !!!

    One of our Customer has done some testing on Performance of our product(their database) due to some problems they are facing:
    Here is what he has written to me:
    I tested that if you read the full table of every Production database tables from disk,
    it took 5.87 seconds total.
    If you read the full table of every production database tables from memory,
    it took 4.37 seconds total.
    OP10 has 20G database data.
    You have great chance to get written to disk unless we purchase another 10G memory
    just for this database. There is a new feature starts with Oracle 8.1.6 to setup separate
    shared pool for some table(s). But it is not necessary to save 0.25 second and prevent
    other application to reuse this memory.

    Is what he saying is true ? Should one have all their tables cached in memory
    what are the repercations of doing this ?
    He says:
    According to bstat and estat

    db block gets 159642
    consistent gets 3168442
    physical reads 109259

    1-(109259/(159642+3168442))=96 % ======> our hit ratio from 8AM to 9 AM today.

    According to script that I sent him he says, the hit ratio is 72.74 %,
    which is not accurate. If the database was shut down and restarted,
    the hit ratio is zero, since every block you want to use is from disk.
    here are the scripts that I sent him from Oracle Performance Tuining(Richard Niemiec):
    /*check data buffering. */
    select name, value
    from v$sysstat
    where name in('db block gets', 'consistent gets', 'physical reads');

    /*Divide phys reads by the sum of the gets, then subtract the result from 1.
    The value should be greater than 95%
    If not, increase db_block_buffers (consider adding to the db_block_buffer init.ora parameter)
    buffer 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 "Hit Ratio"
    from v$sysstat;

    ------------------------ ----------
    db block gets 8093058
    consistent gets 65852307
    physical reads 20153634

    Hit Ratio

    Are the scripts that I sent him are wrong/ inaccurate ?
    Is it true that hit ratio would be zero as soon as you start the database ?

    thanks a lot in advance

  2. #2
    Join Date
    Nov 2000
    Yes, the hit rate will be artificially low if you just start the database. Like you say, every block will have to be read from disk initially. The queries you had them run look viable.

    Should all the tables be cached? Hey, if you have the RAM, that would be great. However, it's usually not very practical.

    Yes, Oracle 8.1.x has a feature that will allow you to setup "keep" and "recycle" buffer pools. See ( [url]http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76965/c06memor.htm#11257[/url] ) for explanation of what each does.

    Jeff Hunter

  3. #3
    Join Date
    Jan 2001
    If the scripts that I sent him are correct and hit ratio should be low when you start the database then why my scripts returned hit ratio to be 72 % shouldn't that be zero or near zero ? Is it ever possible to get it to zero ?

    Is their any other thing else I can test for him/ or ask him to do to improve their performance apart from PL/Sql code ?

    thanks again

  4. #4
    Join Date
    Nov 2000
    When Oracle reads from disk, he gets more than one database block at a time. Some of the blocks needed for the query may be read into the buffer cache. Oracle would then read those blocks from cache instead of performing a physical I/O.

    If your db_block_buffers were really small (like = 1), you might be able to get it around 0.
    Jeff Hunter

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