Re: Question about the 'A-Time' in dbms_xplan.display_cursor's output
Date: Tue, 24 Feb 2009 09:35:30 -0800 (PST)
Message-ID: <694021.5021.qm_at_web80605.mail.mud.yahoo.com>
DB Time excluding wait time is equivalent to DB CPU time, isn't it? It's unlikely that A-Time is DB CPU time. Here's my test on 10.2.0.4 Linux. Create a table at least a few MB in size. Make a new connection. Query v$sess_time_model for your own session. Then
alter session set statistics_level = all; -- avoid the problem lower rows have bigger values in plan output (see
Jonathan Lewis's blog)
select /*+ gather_plan_statistics */ count(*) from (select /*+ no_merge */ * from large);
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
Query v$sess_time_model again. You'll see the above
A-Time is between the delta for 'DB CPU' and delta
for 'DB time'. Since your session runs something
other than the big query, 'DB time', i.e.
"Amount of elapsed time (in microseconds) spent
performing Database user-level calls" includes those
SQLs' executions. Since the delta for "DB CPU"
is even smaller than A-Time in the shown plan statistics,
it's not possible A-Time is the CPU time. Instead
it's very likely A-Time is 'DB time',
*including* the DB wait time. Actually in my test, it's
also close to the delta for 'sql execute elapsed
time' in v$sess_time_model; most of the 'DB time' in this test case is 'sql execute elapsed time'.
Yong Huang
> The A-Time in display_cursor is same as the time in tkprof.
> PLAN_TABLE_OUTPUT
>
> | Id | Operation | Name | Starts |
E-Rows | A-Rows |
> A-Time | Buffers | Reads |
> ------------------------------
>
> |* 1 | COUNT STOPKEY | | 1 |
| 9999
> |00:00:00.24 | 791 | 127 |
> | 2 | TABLE ACCESS FULL| LARGE_TABLE | 1 |
9999 | 9999
> |00:00:00.22 | 791 | 127 |
>
> Rows Row Source Operation
> -------
> 9999 COUNT STOPKEY (cry1 pr7 pw=0 time$3582 us)
> 9999 TABLE ACCESS FULL LARGE_TABLE (cry1 pr7 pw=0
time"2295 us)
>
> Can I think A-Time is the db time excluding any wait
time?
>
> Thanks,
> Eric
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Feb 24 2009 - 11:35:30 CST