RE: How to tune database with lots of very light queries

With the presumption "From single SQL point of view, they are already
perfect", what you should establish is

 

1.      Verify that you are not violating any response time SLAs
2.      How much head room do you need to have to plan for growth
3.      Establish bottlenecks - verify that CPU is the bottleneck using
10046 traces with the system loaded and unloaded - the difference would be
in the response time, all else being equal.
4.      Conversely, establish what your bottleneck is
5.      Look at structural options such as partitioning, table clustering,
index organized tables and determine the yield
6.      Evaluate alternatives - multiple servers, RAC, replicated data, work
load distribution, CPU (more/faster) with simple techniques or with more
advanced queuing anayses.

 

 

The problem as it is defined, or perhaps as I read it, is a bit open ended
and you should close this of with some exit criteria on the response times
and load (handle 15k queries/per sec with 5 seconds per query at peak load).

 

My 2 cents

-Krish

 

 

  _____  

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Zhu,Chao
Sent: Tuesday, April 29, 2008 8:58 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: How to tune database with lots of very light queries

 

We have  some databases with load profile like lots of light query (mostly
pk based),  each execution cost only 3-6 buffer_gets and no complex join
etc.
But due to excessive executions (10K+ query per second), load is also
driving pretty high.

Anyone has experience tune such kind of application/database?  From single
SQL point of view, they are already perfect. 

-- 
Regards
Zhu Chao
www.cnoug.org

Other related posts: