What can make a simple query that normally runs in split seconds using the primary key turn into a 19 minute full scan? Well, many things, but in this case non of the usual suspects. The primary key was usable and rebuilding it made no difference. We updated the already recent statistics to no avail. There was no SQL Profile defined and the query did not use hints. It turned out that at some point the parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES had been set and as a result Oracle had happily collected baselines in order to prevent performance regressions, as the sales message goes. Deleting the baseline fixed the problem and the optimizer immediately switched to an index range scan on the primary key.

SQL baselines may serve a purpose, but if you don’t trust the optimizer to do the right thing, why would you trust it to collect baselines automatically in order to guide itself in the future? Beware of OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES and either create the baselines under controlled circumstances or let the optimizer do its job and correct it with profiles where necessary!

Categories: Oracle
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: