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

As mentioned above, are you using dedicated or shared connections? If you're using dedicated connections I'm assuming they're pooled at the app server end? Are these connections persistent? And if they're persistent are they only kept alive for a certain number of queries before being dropped and reestablished? I've seen apps before where a connection would only execute a certain number of queries before beging recycled and in a scenario like yours that would cause lots of new connections to be established every second which can kill any box.

Finn

On 4/29/08, Stephane Faroult <sfaroult_at_roughsea.com> wrote:
>
> 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
>
>
>

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