1

We have a job using a dynamic query in production for years now and its execution took 5-6 seconds up until our last code deploy. Now the same query takes days to finish. It's a relatively simple nested select that joins 5-6 tables, the largest of which have a few hundred thousand rows (others are mostly small lookup tables).

This same query runs on different database instances very quickly (with much more data and exactly the same codebase).

I'm well aware that tools like explain plan only show predicted execution plan, but the explain plan on this particular instance is very poor (for comparisson, it doesn't use an index which seems to be used on all other instances).

My question is - without changes in query, table structure the query uses or a significant change in the amount of data read, why has the execution plan changed so drastically and how can I anticipate for such a change?

added info The query in question starts with a SELECT DISTINCT... We noticed that on this db instance, if we remove the distinct (which then doesn't necessarily give us the correct results) the execution plan is very similar to the original, much quicker one.

Edit: Satistics were run straight after the last code deploy.

  • Check the last time statistics were updated for all involved tables & indexes (even if the indexes don't appear in the final plan) – Mat Apr 22 '16 at 13:16
  • I'll edit the question, the statistics were updated straight after the code deploy. – eagerMoose Apr 22 '16 at 14:26
  • Do you have access to SQL tuning advisor? If so, what does it say? Have any changes been made to DB parameters like optimizer_index_caching and optimizer_index_cost_adj? – andba77 Apr 22 '16 at 14:43
  • This isn't answerable without a lot of information however you could try seeing if the query WITH the index would be quicker in this instance by using a HINT to force the index to be used. If that doesn't help then Oracle was correct not to use it and something about your data must have changed. You could have hit a tipping point in the ratio of one table to another or the cardinality could simply have changed. – BriteSponge Apr 25 '16 at 7:28
  • Using hints does help. Also, just selecting a rownum in an inner select helps. It's a really bizarre situation, we ended up forcing a certain execution plan and now the query is as fast as it was, but I was wondering what could have triggered the change. – eagerMoose Apr 25 '16 at 11:36
1

The Optimizer takes the parsed representation of SQL statement and Statistics to generate final execution plan with the lowest cost. During this process the Optimizer generates multiple plans and compares them. Execution plans may change as the Optimizer inputs(Parsed SQL Statement and Statistics) get changed.

Why Execution Plans Change

Execution plans can and do change as the underlying optimizer inputs change. EXPLAIN PLAN output shows how the database would run the SQL statement when the statement was explained. This plan can differ from the actual execution plan a SQL statement uses because of differences in the execution environment and explain plan environment. The Oracle Documentation clearly states that Execution plans can differ when we change Schemas and have changes in Costs.

If we run the same SQL statement in different database under different Schemas then also the resulting plan can be different. Even the schema and database is same but the Cost of the execution plan is different then also the optimizer can choose different execution plans. Bind variables, size of the data and its statistics, optimizer's parameters may influence the Cost.

Guessing why the Execution plan has been changed is difficult because we don't have the metadata(which are dynamic) of your database on which we have to query for further investigation and its time consuming task.

You can use SQL Tuning Adviser, SQLTXPLAN, SQL Trace etc to make it easier to find the elements affecting the execution plan.

For details: Oracle Database SQL Tuning Guide

  • 1
    Thanks for the detailed response. Why would the optimizer choose a terribly slow execution plan though? It opted not to use an index on a table (that it chose to use on a previous code version and on all other instances we installed this code to)? We're literally talking query times of seconds vs days. Also, we noticed a weird thing when using distinct in this particular query, I'll update my question so please have a look. – eagerMoose Apr 24 '16 at 9:07
  • All the things we say would be guess work as I already mentioned that we need details like Histogram of the columns that arebeing used in predicate, selectivity etc. Lets say we have got employees table and it has got 109 rows, if our predicate is where emp_id<=100 then most of the rows are going to be selected hence full table scan would be better than index scanning, if predicate is emp_id=1 then only one row is going to be selected hence optimizer may use index. Please study the guide and use the tools mentioned to investigate further. – JSapkota Apr 24 '16 at 10:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.