How often does oracle revise a query plan?

When running a performance benchmark for our data processing application we start with a FOO_TABLE empty and then insert records from one thread while in another thread we select the same records from processing using a query like:

select * from FOO_TABLE where ID > ?

in conjunction with:

stmt.setMaxRows(5000);

in Java to limit the number of records selected in one chunk. (We don't want to use BETWEEN here because the IDs have gaps). And we keep processing chunks of 5000 until the test is stopped.

Now, the performance of our application degrades over time and when I checked what happens on the Oracle side, I was surprised to notice that the query plan for "select * from FOO_TABLE where ID > ?" does a table scan instead of using the PK index on ID.

After restarting our application (but without truncating the table) Oracle came back to reason and used the PK index.

So, my explanation was that Oracle thought it's a good idea to scan the table when it was nearly empty but then never revised this query plan. This brings me to my question: How often does oracle revise a query plan?

Was it because I restarted our application? I have some doubts about this, since开发者_开发技巧 we recycle our pooled connections after 1 hour (therefore no connection can be older than 1 h).

Was it because a certain amount of time had passed?

How would you force oracle to not do a scan even when the table is nearly empty?

Environment information: - oracle 11g - jdbc client (java 6)

UPDATE 10/25/2011: I did a regression test on Oracle 10g and the problem is the same, so it's neither caused nor fixed by the dynamic cursor sharing. As Mark mentioned initially, the plan does not get revised unless there is a major event like structural changes or re-computing the table stats.

Eventually I've added a hint to force access by the PK, but I think the optimizer should have been able to figure this out. If there's a PK that matches the search criteria, then go ahead and use even for small tables (where the performance difference is insignificant anyway).

What version of Oracle?

Generally,

select * from FOO_TABLE where ID > ?

would get hard parsed if the statement was not already in the shared pool. This would be the time that the execution plan would get generated.

After that, the execution plan wouldn't change unless something caused it to be invalidated. (drop/add an index to the table,drop a column from the table, recompute stats on the table, etc).

11g has adaptive cursor sharing (which is why I asked the version of Oracle), and, without getting into a lot of details, it will peek at bind variable values and determine if a plan change is necessary, based on a new bind value.

I think this is a case of stale table statistics. Adaptive cursor sharing aside, Oracle will only see the new rows from a optimizer perspective when new statistics are collected. Some time after this happens a new plan will be generated.

For this query, the hint you used is harmless. Usually it's best to solve the underlying problem rather than hinting. A first rows hint may also have worked while expressing intent.

If a query is inefficient because you are using stale statistics, as appears to be the case here, then the answer is generally to regather statistics.

You can generally rely on Oracle to detect that statistics are stale and to regather them only for the appropriate objects, but you can also check DBA_TAB_MODIFICATIONS if table monitoring is switched on to see if a high number of changes has occurred since statistics were last gathered.

If you have tables which fluctuate widely in the number of rows quite frequently - for example in a table which stages bulk data for later processing -- then a good tactic is to delete and lock statistics for the table and rely on optimiser dynamic sampling for an estimate of the rows to be returned.

Adaptive cursor sharing is a built-in feature of the optimizer, starting with 11.1. What version of Oracle are you running? (Full version number?) I'd expect later versions of 11g, i.e., 11.2.0.2, 11.2.0.3 to be better behaved.

A discussion of the ins and outs of adaptive cursor sharing is probably outside the scope of this forum, but, see here for a good discussion of it: http://blogs.oracle.com/optimizer/entry/update_on_adaptive_cursor_sharing

Also, use the search feature on that blog for more posts on the same subject, as well as many other optimizer topics. That blog is actually written by the optimizer development team at Oracle, so it's an excellent resource.