Explaining the Explain Plan: Interpreting Execution Plans for SQL Statements

Presenter: Maria Colgan, Oracle Corporation

The execution plan for a SQL statement can often seem complicated and hard to understand. Determining if the execution plan you are looking at is the best plan you could get or attempting to improve a poorly performing execution plan can be a daunting task, even for the most experienced DBA or developer. This session examines the different aspects of an execution plan, from selectivity to parallel execution, and explains what information you should be gleaming from the plan and how it affects the execution. It offers insight into what caused the Optimizer to make the decision it did as well as a set of corrective measures that can be used to improve each aspect of the plan.

[derivexyz]

The best video I have ever seen on performance tuning. awesome explanation and the best way to engage attendees. Thanks Maria

[MohammedImran-ijtb]

Excellent. Covers all the basics of Explain Plan.

[vivekghag]

Execution plan explained perfectly. Thank you Maria

[marcellodias]

Great knowledge, good voice, very explanative.

[massefromla]

You are awesome, Maria . Many thanks .. well done 👍

[harshadocp]

Nice overview. Surprised that crowd did not even know basics like "join methods"

[С.Семенчук]

Documentation says there is more access paths than those 9 mentioned at 33:00. Those are clustered table access paths etc

[kevinzhang]

this is so excellent explanation. Where can I download the slide? Thanks!

[AreBaietii]

How can one generate the tree? Is the SQL Monitor a separate tool? Is it a feature within SQL Developer?

[taythiamjoo]

Just checking, when you mentioned at the last portion gather stats... do you mean by gather stats using the hint or gather stats of the tables and indexes

[girishkumar-icpp]

excellent Maria share awr analysis video

[camposgutierrezm]

hi, thank you so muh for sharing this issue, i hve a question, what's the meaning of INLIST ITERATOR?

[bahamas]

Thank you Maria for explaining Explain Plan beautifully. Please help me with one doubt. At around 56 min of the video, example no. 3 of Join Type.
i.) Employee table is having 107 rows. Department table is having 27 rows. Both tables having a small data set.
ii.) There are indexes on tables as there is primary-foreign key relationship.

My concern is why hasn't the optimizer gone for "Nested Loops" join method. Because we have indexes and small data set.
Why has optimizer selected "Hash" join?

Please help me understand.

Thank you!

[gopikanagal]

Can you please publish ASAP
understanding AWR & actions to take in Oracle 19c Also when to use sqlprofile/ash/addm interperting the sql section in AWR

[nenenartey]

Is the abominable name Toad or Pl-SQL Developer?