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: June 04, 2020 - 7:33 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.

and you rated our response

  (1 rating)

Reviews

June 03, 2020 - 2:23 pm UTC

Reviewer: A reader

Thanks a lot.
Chris Saxon

Followup  

June 04, 2020 - 7:33 am UTC

You're welcome

More to Explore

PL/SQL

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