I often have to display executions plans on Oracle 11 for tuning SQL purpose. After executing a SQL statement, I show the execution plan using the following query :
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format=>'ALLSTATS LAST +cost +bytes'));
It works fine. But sometimes there is something wrong within the execution plan : on nested loops operations, I got sometimes an elapsed time that is bigger than the total elapsed time of the whole process. Here is an example :
I don't understand why. The query has spent exactly 1 minute 52 secs. So, the nested loops greather than 3 minutes are wrong.
Can you please help me understand ?