RE: Inconsistent SQL tuning results
- From: D'Hooge Freek <Freek.DHooge@xxxxxxxxx>
- To: "cary.millsap@xxxxxxxxxxxx" <cary.millsap@xxxxxxxxxxxx>, "michaeljmoore@xxxxxxxxx" <michaeljmoore@xxxxxxxxx>
- Date: Mon, 7 Feb 2011 23:53:17 +0100
One reason that you get better performance during succeeding runs, even after
clearing the buffer cache, is that the data is cached somewhere else then in
the db buffer cache.
This could be the filesystem cache or the san cache (if you are using a san).
The sql trace files will, in such cases, only show a faster response time for
your read operations (single block read, scattered read, direct .).
When using a tool like collectl on the same time, you would see for example a
growth in the filesystem cache during the first execution.
Regards,
Freek D'Hooge
Uptime
Oracle Database Administrator
email: freek.dhooge@xxxxxxxxx
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer: www.uptime.be/disclaimer
---
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Cary Millsap
Sent: maandag 7 februari 2011 18:58
To: michaeljmoore@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Inconsistent SQL tuning results
Answer: Trace it in both circumstances,
using http://method-r.com/downloads/doc_details/72-mastering-performance-with-extended-sql-trace
What to look for: Where is the difference in the time? Drill in from there.
Perhaps both executions use different SQL execution plans and do tremendously
different amounts of fetch work.
Perhaps the construction of the execution plan is causing the response time
difference.
You don't have to guess. The trace files will tell you.
If you have a hard time reading the trace data, send me a zip of the two raw
trace files (NOT tkprof output), and I'll help.
Cary Millsap
Method R Corporation
http://method-r.com
http://carymillsap.blogspot.com
On Mon, Feb 7, 2011 at 11:49 AM, Michael Moore <michaeljmoore@xxxxxxxxx> wrote:
I've been trying to tune a SQL statement but I get very inconsistent results.
I always start with:
alter system flush shared_pool;
alter system flush buffer_cache;
Then I run the SQL, but the first time I run it, it can take as much as 7
minutes. On the 2nd, 3rd, and 4th runs, it takes
40 sec, 49 sec, 35 sec respectively.
So my question is: What might account for the huge difference in run time
between the first run and successive runs?
Thanks,
Mike
--
//www.freelists.org/webpage/oracle-l
Other related posts:
