Help tuning the SGA...

From: Denmark Weatherburne <denmark_weatherburne_at_hotmail.com>
Date: Wed, 26 Sep 2001 10:20:58 -0700
Message-ID: <F001.00399707.20010926093031@fatcity.com>

Hi DBA's/Developers,

I've been monitoring our production Oracle database (8.0.5.0.0) on NT with Spotlight on Oracle. I've also read some documents relating to Oracle database tuning. I have identified some components that need to be tuned. One of them is the SGA.
This is the current configuration:
SVRMGR> show sga

Total System Global Area                         26808320 bytes
Fixed Size                                          49152 bytes
Variable Size                                    18493440 bytes
Database Buffers                                  8192000 bytes
Redo Buffers                                        73728 bytes

SVRMGR>
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.
Our client/server application might be categorized as OLTP with some batch processing near the end of the workday. These are actually summary reports of cash collections during the day which are used for reconciling the bank deposits and for internal accounting control. However, it uses some full table scans and it reads lots of rows as a result the hit ratio drops to (4%) in some cases. This is a learning process for me, so I would appreciate some helpful advice.

Thanks for your time.
Regards!

Denmark Weatherburne
"Knowledge is power, but it is only useful if it is shared!" This is after increasing

---

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 - 12:20:58 CDT