Skip to Main Content

DBMS_XPLAN.display_cursor shows wrong elapsed time on nested loops

User_HMTR3Aug 24 2018 — edited Aug 26 2018

Hi,

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 :

Screenshot - 24.08.2018 , 15_59_22.png

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 ?

Thanks.

Best regards.

MS

This post has been answered by Jonathan Lewis on Aug 25 2018
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked due to inactivity on Sep 23 2018
Added on Aug 24 2018
8 comments
280 views