I'm getting different execution plans for the same query running on the same Oracle 12cR2 instance. The query uses only literals (no variable binding) and takes about 10 seconds when I run it from my client via TOAD and takes abount 12 hours when it is run via the PL/SQL package it belongs to.

To make the comparison I copied the SQL_FULLTEXT column from the V$SQL view showing the query run via the package and pasted it in the TOAD editor.

The only difference I can notice about the two scenarios is the PL/SQL package performs some INSERT/UPDATES on tables from which the SQL query read from: these are regular tables on which we locked the statistics because of the transient nature of the data they store.

Here are the execution plans

How would you investigate this issue?

  • 1
    Have you tried to unlock statistics and see if it makes a difference? All planners of all databases use them to decide which plan is better... If this information is not available or not up-to-date, you can expect inefficient behaviour.
    – joanolo
    Jun 17, 2017 at 16:53
  • I haven't, but just because I can't figure out what was actually happening. I mean, statistics have been locked and I counted on dynamic sampling to gather the statistics during SQL parsing (and according to the explain plan output, dynamic sampling took place)
    – Max
    Jun 18, 2017 at 8:27
  • Check your client NLS settings
    – Philᵀᴹ
    Jun 18, 2017 at 9:59
  • Could you please further elaborate your point? I run the script altering the NLS settings in my Toad session as it is defined in nls_database_parameters but I still get different plans.
    – Max
    Jun 18, 2017 at 11:03


Your Answer

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

Browse other questions tagged or ask your own question.