Skip to Main Content
  • Questions
  • Clarification about A-Time column in execution plan

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Giuseppe.

Asked: May 20, 2020 - 6:39 am UTC

Answered by: Connor McDonald - Last updated: May 22, 2020 - 12:48 am UTC

Category: SQL - Version: Oracle RDBMS 12.2

Viewed 100+ times

You Asked

Hi Experts,
I've the following execution plan. As you can see, the "A-Time" of operation 3 is bigger than its parent. Operation 2 behave in the same way with it's parent.
Furthermore operation 0 has only 0.03 second.
How can this happens? The "A-Time" of a parent does not contains the elapsed of its children?

Thanks in advance.

--------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      1 |00:00:00.03 |     824 |
|   1 |  SORT AGGREGATE     |      |      1 |      1 |      1 |00:00:00.03 |     824 |
|*  2 |   HASH JOIN         |      |      1 |     32 |  75808 |00:00:00.06 |     824 |
|*  3 |    TABLE ACCESS FULL| T1   |      1 |     32 |  80016 |00:00:00.41 |     443 |
|   4 |    TABLE ACCESS FULL| T2   |      1 |    151K|    151K|00:00:00.01 |     378 |
--------------------------------------------------------------------------------------

and we said...

You are correct that A-Time is *meant* to be the consolidation of all of its children.

However, for efficiency reasons, when we use the gather_plan_statistics hint (or statistics level is ALL), we are taking *samples* of the data throughout the execution to come up with this figure.

As queries get longer, the effects of sampling diminish.

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.