Re: How to test a query, that is slow only the first time it's executed
Date: Mon, 15 Nov 2010 09:20:30 -0800 (PST)
Message-ID: <b2434ece-2bdd-4e94-a6ef-1bd37dd0cd7e_at_y2g2000prf.googlegroups.com>
On Nov 15, 3: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
Please read the Battle Against Any Guess excerpt of http://www.nocoug.org/Journal/NoCOUG_Journal_201005.pdf paying particular attention to the Rainy Mondays story.
If you flush a buffer and it doesn't make a difference, you probably are misinterpreting the problem. A bind variable peeking issue might explain the problem, but you have to follow the evidence.
Just a few minutes ago I was looking at the dbconsole performance screen, which let me drill down to an obscure problem, letting me see a cpu binding issue that had been mysterious until I just visualized what is going on. Now I can ask management if it is worthwhile to rewrite - the hardest part is describing the issue to them, they don't quite get how the same exact query can be slow sometimes and not others, in a system that can evaluate costs of queries. (The generated code seems to be emulating a nested loops query for a join by doing a full table scan and grabbing rowids, rather than letting Oracle do a hash.)
jg
-- _at_home.com is bogus. 29. What is the most CPU-intensive thing you can do? A. Flush a buffer. B. Buff a flusher. C. Parse a Query. D. Queer a Parsey. E. Checkpoint.Received on Mon Nov 15 2010 - 11:20:30 CST