0

We have an Oracle query in a report that use to run in less than 1 second. Now takes 8 minutes.

  • I've tried rebuilding all the indexes it uses. No impact.
  • The query has not been changed in months.
  • I personally tested the query on both an older clone of the production db and a new clone. It ran fast on the one and slow on the other. So it's real.
  • The explain plan on the query is perfect. No reason why it shouldn't be lightning fast.

I'm running out of ideas. Any suggestions?

Improve this question
2
  • 1
    Are you saying that the query plan is the same in both cases? If so, I would tend to guess that the query plan you're generating isn't actually the one being used for some reason. Can you see the actual query plan being used in both cases (from AWR tables, for example, if you are licensed for that)? Where does tkprof show that time is really being spent?
    – Justin Cave
    Nov 21, 2016 at 19:27
  • Thanks Justin for getting me on the right train of thought! (See answer below) I couldn't actually use the analysis tools because I was afraid to run it again in production and in test the query was running something over 57 minutes (when I killed it earlier today). So started messing with hints that might restore the explain plan order and it worked. :-) Thanks again.
    – Jeff
    Nov 21, 2016 at 19:52

1 Answer 1

Reset to default
0

Justin Cave, I think you are right. It's using something other than the explain plan.

I added the hint /*+ LEADING (table_alias, table_alias) */ referencing the first two tables in the explain plan and boom, it's running like lightning again.

Improve this answer
1
  • 3
    In most cases hints are like band-aids, they only last so long. Your problem might be lack of or incorrect stats on the objects too. So, while LEADING is solving your immediate concern, you might want to take a deeper look and see if data retention in your tables has changed? have you been able to create stats appropriately? or if you need to restructure your SQL so it works better.
    – Raj
    Nov 21, 2016 at 20:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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