1

I am getting different execution PATHS for a query execution through:

  1. SQL Developer> Explain plan
  2. SQL Developer> Tools> Monitor SQL> Monitored SQL Execution Details (feature of OEM)

The first option shows indexes being used. However, the second option does not cover those indexes during actual execution.

Note: I cannot run these queries in the tool since the PRODUCT I'm using creates and executes them on the fly (I know the queries are exactly same because I can view the queries in the execution monitor). That's why I specifically need to know which result is correct. Or is there a way I can track the specific index usage.

user3090427
  • 65
  • 5

1 Answers1

0

Explains are the theoretical plan. Real time SQL monitoring, which you're referring to when you talk about 'Monitor SQL' shows the actual plan, as it executes.

You can also ask SQL Developer to show you the cached plan that was most likely used to execute the statement last. In version 4.0 and higher, use the drop-down control on the Explain button to see those.

I discuss this here http://www.thatjeffsmith.com/archive/2013/07/explain-plan-and-autotrace-enhancements-in-oracle-sql-developer-4/

thatjeffsmith
  • 20,522
  • 6
  • 37
  • 120