Re: How to test a query, that is slow only the first time it's executed
From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Mon, 15 Nov 2010 07:16:49 -0800 (PST)
Message-ID: <2e5197a6-2e55-43b5-8e90-8008b7bf34db_at_j9g2000vbr.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: Mon, 15 Nov 2010 07:16:49 -0800 (PST)
Message-ID: <2e5197a6-2e55-43b5-8e90-8008b7bf34db_at_j9g2000vbr.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
If you cannot bounce the instance then you probably should not be flushing the buffer cache and shared pool either since both actions can have a negative impact on performance.
Looking at the run time statistics as menitoned should give you a good idea of what is going on.
The tool you are using to look at the queries was not mentioned but I suggest you consider using sqlplus for the time tests if you are using a third-party tool.
HTH -- Mark D Powell -- Received on Mon Nov 15 2010 - 09:16:49 CST