Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Pradeep.

Asked: December 02, 2017 - 1:26 pm UTC

Last updated: December 13, 2017 - 1:57 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hello Team,

Quite confused with elapsed time definition.

elapsed time is equal to db time.

but DBtime= cpu time + wait time.

so its correct or not.

somewhere i found elapsed time is nothing but wall time.

plz comment on above statement.


Plz consider one example

I have octa core cpu, i fired one sql without parallel and below its statistics..

CPU TIME=40 sec
WAIT TIME=20 sec
DB-time=60.
what will be elapsed time for this?

same query fired with 4 parallel then what will be elapsed time.

Now final question..

how to find elapsed time for any specific sql or plsql.

kindly explain me.

CPU_TIME,WALL_TIME,WAIT_TIME,NON-IDEAL WAIT TIME,ELAPSED TIME,DB TIME.

Thanks
Pradeep


and Connor said...

DB time is time the database was effectively "active" serving a request for you.

So for a database-specific operation, for a *single* session, it would be the same elapsed time.

For an application it would be different.

eg

Application takes 5 seconds to return a user request. 2 seconds was database running SQL.

So

elapsed = 5 seconds
db time = 2 seconds.

Even if we focus just solely on the database, DB time represents *all* database time for a given time period

So lets consider another example:

9am: Session 1 does 5 seconds of work (cpu + wait) on the database
9am: Session 2 does 5 seconds of work (cpu + wait) on the database
9am: Session 3 does 5 seconds of work (cpu + wait) on the database

Each session hence has an elapsed time of 5 seconds

But the db time from 9am to 9:00:05 is 15 seconds, ie, total amount of database working time spent


Rating

  (1 rating)

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

Comments

DB time

Pradeep prajapati, December 08, 2017 - 11:28 am UTC

DB time is actual includes wait timing.

actual work time is cpu time if am not wrong.

last question need to cover i.e. how to find elapsed time for any specific sql query.

if in PLSQL any sql is problematic then how to find.

Thanks
Pradeep
Connor McDonald
December 13, 2017 - 1:57 am UTC

"actual work time is cpu time"

Really depends on your definition. For example I/O is "wait time" but I would still call that actual work.

"if in PLSQL any sql is problematic then how to find. "

I would start with v$sqlstats and/or AWR reports.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.