RE: How to tune database with lots of very light queries
Date: Tue, 29 Apr 2008 10:12:16 -0600
With the presumption "From single SQL point of view, they are already perfect", what you should establish is
- Verify that you are not violating any response time SLAs
- How much head room do you need to have to plan for growth
- 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.
- Conversely, establish what your bottleneck is
- Look at structural options such as partitioning, table clustering, index organized tables and determine the yield
- 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
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
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
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.
http://www.freelists.org/webpage/oracle-l Received on Tue Apr 29 2008 - 11:12:16 CDT