Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Bringing down the load on OLTP db server

Re: Bringing down the load on OLTP db server

From: Greg Stark <greg-spare-1_at_mit.edu>
Date: Thu, 02 Nov 2000 06:21:12 GMT
Message-ID: <871ywux58b.fsf@HSE-MTL-ppp62507.qc.sympatico.ca>

andreyNSPAM_at_bookexchange.net (NetComrade) writes:

> We have a heavily queried OLTP db, but no heavy sql.
> We use bind variables
> We have sql with minimum IO
>
> 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?

What exactly are you trying to accomplish if all the queries respond quickly? It sounds like everything is working as intended :)

> 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)

High load can indicate either a lot of logical reads for cached blocks, or lots of sorts. Since you have little I/O I would look for redundant queries being executed needlessly. The old saw is "The most efficient query is the query you never execute" :) You couldI would start by looking at the SQL cache for queries with the most logical reads.

Perhaps you have a medium sized table (a few thousand records) that fits in cache that you're repeatedly scanning for small amounts of data? Or for an aggregate like min() or sum() of some column that you could cache precalculated either in the application or in separate table or materialized view?

-- 
greg
Received on Thu Nov 02 2000 - 00:21:12 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US