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

Let's start off with some assumptions (if these are wrong, they will show you tasks that you need to complete) 1) The queries are causing the high load (cpu time where load is defined as cpu utilization)
2) The cpu time is caused by buffer gets 3) The queries are properly formed (using bind variables where required and literals where performance dictates) 4) The queries are not recursive queries Oracle uses for parsing/management

You have to ask "Why are these queries being executed?" 1) If they are part of a custom function, development needs to go back to the drawing board so that they are not being used 2) If they are called as part of an application checking for data, perhaps the application can cache non-volatile data at the application server level
3) Determine the business purpose for the queries and see if the business process can be 'tuned'.

You may need to throw more hardware at the issue, but you should first try to determine if the executions are really needed.

Regards,
Daniel Fink

-- 
Daniel Fink

Oracle Performance, Diagnosis and Training

OptimalDBA    http://www.optimaldba.com
Oracle Blog   http://optimaldba.blogspot.com


Zhu,Chao 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 <http://www.cnoug.org>

> ------------------------------------------------------------------------

>

> No virus found in this incoming message.

> Checked by AVG. 

> Version: 7.5.524 / Virus Database: 269.23.6/1403 - Release Date: 4/29/2008 7:26 AM

>   



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