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

I fully second Dan on this. It's very easy to get a lot of "perfect" small queries. Just write lots of functions:

get_ename(empno in emp.empno%type)
get_job(empno in emp.empno%type)
etc.

Then use the functions everywhere ...

Stéphane Faroult

Daniel Fink wrote:
> 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