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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Zhu,Chao
Sent: Tuesday, April 29, 2008 8:58 AM
To: oracle-l_at_freelists.org
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

--

http://www.freelists.org/webpage/oracle-l Received on Tue Apr 29 2008 - 11:12:16 CDT