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

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

--

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>

ProQuest...Start here.

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
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.cnoug.org>

--

http://www.freelists.org/webpage/oracle-l Received on Tue Apr 29 2008 - 10:35:03 CDT