Inconsistent SQL tuning results

From: Michael Moore <michaeljmoore_at_gmail.com>
Date: Mon, 7 Feb 2011 09:49:09 -0800
Message-ID: <AANLkTimVZ6Je4uNP70D8+VcGFzS_xeDj2iASdz8ny72P_at_mail.gmail.com>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Feb 07 2011 - 11:49:09 CST