Bringing down the load on OLTP db server

From: NetComrade <andreyNSPAM_at_bookexchange.net>
Date: Wed, 01 Nov 2000 23:39:27 GMT
Message-ID: <381dd71c.8802076@news.earthlink.net>

We have a heavily queried OLTP db, but no heavy sql. We use bind variables
We have sql with minimum IO
Library Cache:
NAMESPACE Hit ratio pin hit ratio reloads
--------------- ---------- ------------- ----------------

SQL AREA               100            98        2,563,890
TABLE/PROCEDURE         99           100                1
BODY                    89            88                0
TRIGGER                100           100                0
INDEX                   30            28                0
CLUSTER                 98            98                0
OBJECT                 100           100                0
PIPE                   100           100                0

Data Dict:
Data Dict. Gets Data Dict. cache misses DATA DICT CACHE HIT RATIO
---------------- ----------------------- -------------------------

      47,596,959                   1,983                        99

Buffer hit: 99-100

Rollback Contention:

NAME                                WAITS       GETS     Ratio


------------------------------ ---------- ---------- ---------

SYSTEM                                  0        519    .00000
R01                                    18     246204    .00007
R02                                    26     250873    .00010
R03                                    25     248805    .00010
R04                                    24     236938    .00010
BIG_RBS                                12      74294    .00016

MTS Stats are OK.

The easy solution is of course to add CPUs, but with current budget constraints I need to tune this thing to the max. My next issue is tune the tuned SQL, but will it really matter if all SQL statements take under .1 secs to execute? Will it matter if all the SQL is already parsed to tune multijoin sql statements?

The load on our server goes up to 60 during busy times, and even though response times aren't horrible on the user end, the slowdown is noticable (takes few secs to get results back)

Thanx.

In case I forgot to mention:
We use Oracle 7.3.4 and 8.1.6 on Solaris 2.6, 2.7 boxes

Andrey Dmitriev	 eFax: (978) 383-5892  Daytime: (917) 750-3630
AOL: NetComrade	 ICQ: 11340726 remove NSPAM to email

Received on Wed Nov 01 2000 - 17:39:27 CST