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

3 to 6 gets/exec? No complex joins? 10,000 executions per second?

It's pretty tough to beat 3 to 6 gets/exec. At least be working at the * statement* level. I would be quite surprised, though, if there were not some huge opportunities for performance improvement in the application code.

This sounds (almost exactly) like that I would expect with an application written by developers who do not understand *joins*, and code "around" their lack of understanding by using loops that issue tens of thousands of small queries where a single SQL statement could (should) have been used.

On the other hand, perhaps you are working with a lean, mean, high performance OLTP system where everything has been designed, tested, and tuned with immaculate care and uncommon skill. If this is the case, you can probably just put your feet on the desk and relax until somebody needs something recovered from backup. :-)

Okay. Probably not.

You have not said much about actual performance problems. Is the database meeting performance expectations? Which business functions, if any, are slower than they *should* be? Do you have a statspack report? Can you reduce PIOs by increasing the buffer cache or implementing a keep pool? Is your IO subsystem slower than it might be? Do you need more CPUs? Or is everything just fine? It *could* be...

On Tue, Apr 29, 2008 at 8:58 AM, Zhu,Chao <zhuchao_at_gmail.com> wrote:

> 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
>

-- 
Cheers,
-- Mark Brinsmead
Senior DBA,
The Pythian Group
http://www.pythian.com/blogs

--
http://www.freelists.org/webpage/oracle-l