Re: Question about the 'A-Time' in dbms_xplan.display_cursor's output
- From: gengmao <gengmao@xxxxxxxxx>
- To: yong321@xxxxxxxxx
- Date: Wed, 25 Feb 2009 14:36:41 +0800
Thank you for your analysis, Yong.
The description "A-Time is DB time excluding *any *wait time" might be
wrong. It is not just only the CPU time.
It is more like a part of SQL execute elapsed time, which includes the
foreground waits time when performing a database user-level call. But I
couldn't find their direct connection.
I also found following description from oracle
document<http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2087.htm>
:
SQL execute elapsed time - Amount of elapsed time SQL statements are
executing. Note that for select statements this also includes the amount of
time spent performing fetches of query results.
Sounds like "SQL execute elapsed time" are close to the elapsed time of
execute + fetch in tkprof output.
-Eric
On Wed, Feb 25, 2009 at 1:35 AM, Yong Huang <yong321@xxxxxxxxx> wrote:
> 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
>
>
>
>
>
Other related posts:
