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 ?
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
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...*/
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.
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 ?
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.