Re: How to test a query, that is slow only the first time it's executed
From: onedbguru <onedbguru_at_yahoo.com>
Date: Wed, 17 Nov 2010 17:33:13 -0800 (PST)
Message-ID: <ebbf19ee-3b07-465d-93fb-ba465f2c8578_at_g25g2000yqn.googlegroups.com>
On Nov 15, 6:51 am, Maikku <kangasm..._at_netti.fi> wrote:
> Hi,
> I found many posts about this problem, but didn't find a simple
> answer.
>
> I use 10.2 database. I have couple of complex queries/database
> procedures that are very slow when I run them first time a day. Second
> run is fast. When I try to optimize the queries I want to get the
> slowest run and I've tried ALTER SYSTEM FLUSH SHARED POOL and ALTER
> SYSTEM FLUSH BUFFER_CACHE but they don't seem to help. Is there a
> simple way to do this (cannot restart the database or make tablespace
> offline) ?
>
> Regards,
> Maija-Leena
Date: Wed, 17 Nov 2010 17:33:13 -0800 (PST)
Message-ID: <ebbf19ee-3b07-465d-93fb-ba465f2c8578_at_g25g2000yqn.googlegroups.com>
On Nov 15, 6:51 am, Maikku <kangasm..._at_netti.fi> wrote:
> Hi,
> I found many posts about this problem, but didn't find a simple
> answer.
>
> I use 10.2 database. I have couple of complex queries/database
> procedures that are very slow when I run them first time a day. Second
> run is fast. When I try to optimize the queries I want to get the
> slowest run and I've tried ALTER SYSTEM FLUSH SHARED POOL and ALTER
> SYSTEM FLUSH BUFFER_CACHE but they don't seem to help. Is there a
> simple way to do this (cannot restart the database or make tablespace
> offline) ?
>
> Regards,
> Maija-Leena
Move this to a TEST system that you can bounce as necessary. Having second and subsequent executions run fast indicates that not much else is going on in your system and the results get cached and remain cached for long periods of time. The alternative to the test system/ database would be to run your test before doing anything else.
set time on
set autotrace on
execute your query.
Also, monitor CPU, disk and memory usage during this timeframe. Execute awr reports for the time period - (set snap interval to something small - like 5 minutes - to capture additional statistics. Received on Wed Nov 17 2010 - 19:33:13 CST