Re: How to test a query, that is slow only the first time it's executed
From: Rob Burton <burton.rob_at_gmail.com>
Date: Mon, 15 Nov 2010 05:24:48 -0800 (PST)
Message-ID: <2808185c-7ca6-4cca-b15c-df634ff4826b_at_j2g2000yqf.googlegroups.com>
On Nov 15, 11: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: Mon, 15 Nov 2010 05:24:48 -0800 (PST)
Message-ID: <2808185c-7ca6-4cca-b15c-df634ff4826b_at_j2g2000yqf.googlegroups.com>
On Nov 15, 11: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
In general just tracing the session/query - (ie set trace on/tkprof) or doing something like set autotrace on, should be good enough for optimising the query. The symptoms you describe where even flushing the buffer cache doesn't slow the query points to the filesystem buffering the blocks you are querying. As long as something like autotrace shows the difference as physical reads for slow query and few or no physical reads for the fast query, that should be enough info with the execution plan to allow you to optimise if necessary. Received on Mon Nov 15 2010 - 07:24:48 CST