Skip to Main Content
  • Questions

  • Clarification about A-Time column in execution plan

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

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)

We're not taking comments currently, so please try again later if you want to add 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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

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