Re: Bringing down the load on OLTP db server

NetComrade wrote:
>
> 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

Take a look at v$system_event to see what's possibly causing the most pain ....

HTH

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."