| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Info=> Help tuning the SGA...
Hi Kevin,
Thanks for responding! Actually, the reason I'm trying to tune is both,
because some users have reported calculations that they have to abort after
five minutes when normally the process takes about 5 seconds.
The ratio I reported before is not accurate because it varies from 40% to
80% during normal working hours. But mostly it is on the low side of that
range. I understand what you are saying though that if the users are
noticing a response time degradation then something is wrong.
I have been taking a Server side view of the tuning process I think.
Also using the monitoring tools that I have installed and scheduled, they
point to required improvement of some key RDBMS ratios.
I guess, it's the case of the squeaky wheel getting the oil.
I appreciate your comments. including another comment from another lister
who recommended that I look into tuning the SQL statements first.
I agree that this is the most challenging process because the aplication was
developed by consultants who no longer are associated with the company. I am
in a position to point out the SQLs that are not optimized. I have Spotlight
on Oracle and Embarcadero's SQL Tuner installed. I know these are just
tools. I still need to understand the key ratios and what impact they have
on performance.
I'll paste below the output from a script that I run which lists some key
database parameters and ratios before and after I made the changes to the
DB_BLOCK_BUFFERS parameter.
NAME VALUE ------------------------- --------------- shared_pool_size 16000000 db_block_buffers 1000 db_block_size 2048 sort_area_size 65536
Read Hit ratio
-47.87131
Gets Misses Hit Rate
--------- --------- ---------
324934 5009 98.481859
EXECUTIONS Execution Hits PHITRAT MISSES HITRAT ---------- -------------- --------- --------- ---------
386838 380829 98.446637 837 99.784098
shared Pool Size Free Bytes Percent Free
---------------- ---------------- ------------
16,000,000 894,784 5.5924
Free Bytes STATUS
---------------- --------
800,000 R-free
40 R-freea
92,320 free
10,978,668 freeabl
2,348,400 perm
3,538,816 recr
6 rows selected.
STATE COUNT(*)
--------- ---------
1 983
3 8
4 9
Block Status COUNT(*)
---------------------------------------- ---------
4 24
AVAILABLE 940
BEING USED 36
***********************
NAME VALUE
------------------------- ---------------
shared_pool_size 16000000
db_block_buffers 4000
db_block_size 2048
sort_area_size 65536
Read Hit ratio
-34.79965
Gets Misses Hit Rate
--------- --------- ---------
136726 2307 98.340682
EXECUTIONS Execution Hits PHITRAT MISSES HITRAT ---------- -------------- --------- --------- ---------
199921 197224 98.650967 28 99.985996
shared Pool Size Free Bytes Percent Free
---------------- ---------------- ------------
16,000,000 1,270,592 7.9412
Free Bytes STATUS
---------------- --------
800,000 R-free
40 R-freea
154,496 free
10,635,912 freeabl
3,495,892 perm
3,395,960 recr
6 rows selected.
STATE COUNT(*)
--------- ---------
1 3968
3 32
Block Status COUNT(*)
---------------------------------------- ---------
AVAILABLE 3816
BEING USED 184
Sorry about the paste.
Please comment on the negative Read Hit ratio!
Concerning network topology, we are using a TCP/IP WAN over a 256K fiber backbone and 256K regular copper & microwave backup. We are using 100Mbps NICs and switches. The Oracle clients however are all accessing the RDBMS over the WAN. Only the Developer 6.0 forms executables are located on a file server on their LAN. I know I need to get the big picture when it comes to tuning the Oracle database and the application.
I would apprciate your feedback on this challenge as I jump into unfamiliar territory.
Regards,
Denmark Weatherburne
>From: Kevin.Little_at_blueshieldca.com >To: denmark_weatherburne_at_hotmail.com, oracledba_at_lazydba.com >Subject: RE: Help tuning the SGA... >Date: Wed, 26 Sep 2001 11:48:30 -0700 > > > >The buffer hit ratio is very poor (around 70%). > >I've already increased the value of DB_BLOCK_BUFFERS from 1000 to 4000, > >but I still have not observed any significant improvement. Of course I'll > >try increasing the value again to see if it has an impact. However, I > >believe there are other related parameters that might have to be tuned as > >well in order to achieve the desired results. > >Could you experts and Gurus please give me some advice on approaching the > >SGA tuning process. > >Just remember, hit ratio is just a number... are you tuning because YOU >THINK 70% IS POOR or because your users are seeing bad response times. For >some applications, 70% hit ratio is not bad, or at least adequate. You >know >it also depends on the access pattern of the data and how recently you have >restarted your database. A system that is shut down for nightly cold >backups will have lower hit ratios since the cache needs to be repopulated >every day. Would raising the hit ratio by eliminating the backups be a >good >strategy? Well, ok, hot backups instead of cold backups would help keep >the >hit ratio up, but that isn't my point. Other operations may also clear >your >buffer cache, including FLUSH SHARED POOL or off hours batches that access >different tables & ranges of records that may push out the normal daily >contents of the buffer cache, so the next business morning there are low >hit >ratios as the buffer cache is repopulated, though usually that happens as >the first few users of the morning are getting on, when the load is lighter >and they mostly don't notice the 'bad hit ratios'. > >What really matters is your app user's perception of what is the response >time, which may be affected by several factors, including network topology >& >configuration, and whether the hit ratio is 70% or 90% may be irrelevant >when these other factors are considered. > >Other than that, if you have small reference tables that are very >frequently >used, such as in pull down menus, you can set those small tables to use the >CACHE attribute, and your after hours stuff will not step on them. > >Kevin Little > > > > _________________________________________________________________Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denmark Weatherburne INET: denmark_weatherburne_at_hotmail.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Wed Sep 26 2001 - 14:32:27 CDT
 |
 |