RE: How to tune database with lots of very light queries
Date: Tue, 29 Apr 2008 11:35:03 -0400
That can be a difficult position to be in. Short of buying more hardware, what can you do?
Well, it depends....one thing that may be worth looking at, (or not, it depends) is, how about turning some of those tables w/ pk based lookups into single table hash clusters? Start with the most heavily hit object. A correctly optimized single table hash cluster will allow you to do pk lookups at a cost of 1 buffer get.
Beyond that, I'm not sure how much more you can do....look at caching stuff at a tier external to the database, to reduce the frequency of execution, possibly a TimesTen front end (I have zero experience w/ it, but, if I were in your situation, it's something I'd at least be looking at).
Hope that helps,
Mark J. Bobak
Senior Database Administrator, System & Product Technologies ProQuest
789 E. Eisenhower, Parkway, P.O. Box 1346 Ann Arbor MI 48106-1346
+1.734.997.4059 or +1.800.521.0600 x 4059 mark.bobak_at_proquest.com<mailto:mark.bobak_at_il.proquest.com> www.proquest.com<http://www.proquest.com> www.csa.com<http://www.csa.com>
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Zhu,Chao
Sent: Tuesday, April 29, 2008 10: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 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.
http://www.freelists.org/webpage/oracle-l Received on Tue Apr 29 2008 - 10:35:03 CDT