According to this video of John Watson
▶ 13:39
https://www.youtube.com/watch?v=bcVbR_FPWKk
Apr 7, 2014 - Uploaded by SkillBuilders
In this free tutorial you will learn how to generate and read(interpret) an execution plan in Oracle Databases ...
I watched just now, it seems even Autotrace will not give the correct plan used to execute a query.
I thought with Autotrace, the query is actually executed and the plan we are shown AFTER execution, was the actual plan used.
Why show a different plan, AFTER executing the query??
But looks like to get the ACTUAL plan, we have to, it seems, execute the SQL with hint GATHER_PLAN_STATISTICS and
use function DBMS_XPLAN.DISPLAY_CURSOR get the actual plan from the library cache of the shared pool.
Why is this???
Also, we are supposed to start a tuning of 11 huge SQLs in a production system. What if we need to create new indexes, or drop existing ones in order to make the SQLs faster.
Is it wise to play-around with the live system by creating and dropping indexes in the live system? Or, should we replicate the live system in our own machines and do the tuning there?