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

On Nov 15, 11:51*am, Maikku <kangasm... (AT) netti (DOT) fi> wrote:
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.

On Nov 15, 6:51*am, Maikku <kangasm... (AT) netti (DOT) fi> wrote:
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 --

On Nov 15, 3:51*am, Maikku <kangasm... (AT) netti (DOT) fi> wrote:
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
--
@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.

On Nov 15, 6:51*am, Maikku <kangasm... (AT) netti (DOT) fi> wrote:
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.

Maikku:

Trace it the first time using a 10046 trace and put it thru a resource
profiler.

Try reading Optimizing Oracle Performance by Cary Millsap.