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

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Giuseppe.

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

Last updated: June 04, 2020 - 7:33 am UTC

Version: Oracle RDBMS 12.2

Viewed 1000+ 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 Connor 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.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, June 03, 2020 - 2:23 pm UTC

Thanks a lot.
Chris Saxon
June 04, 2020 - 7:33 am UTC

You're welcome

More to Explore

PL/SQL demos

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

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library