What is DB Time? Where has it gone?

DB Time is the time spent by the database server executing user calls. What is this composed of? CPU time, waiting time, and IO time.

The objective of all tuning is to reduce the amount of DB Time needed to accomplish a given workload. Analysis of where the DB Time is spent tells you what to tune. Do you need to attack the wait events that are preventing your CPUs from working, or reduce the the amount of work that they actually need to do?

A doc extract:

Quote:
DB time is the fundamental measure of database performance, and is the cumulative time spent by the database in processing user requests. It includes wait time and CPU time of all non-idle user sessions. DB time is displayed in the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.

Many DBAs incorrectly interpret this as meaning that DB Time is equal to DB CPU time plus non-idle waiting time. And it often is. Consider this extract from an AWR report,
Top 5 Timed Foreground Events

Event                               Waits	Time(s)	Avg wait (ms)	% DB time	Wait Class
db file sequential read	            4,533,553	26,890	6	        70.62	        User I/O
DB CPU	 	                                10,768	 	        28.28	 
unspecified wait event	            45,913	192	4	        0.50	        Other
log file sync	                    3,497	54	15	        0.14	        Commit
gc current block 2-way	            22,696	26	1	        0.07	        Cluster

Near perfect: DB CPU plus waits are near as dammit 100%. But it doesn't have to be. Consider this example:
Top 5 Timed Foreground Events

Event	                            Waits	Time(s)	Avg wait (ms)	% DB time	Wait Class
DB CPU	 	                                19,678	 	        15.50	 
direct path read temp	            1,454,115	3,823	3	        3.01	        User I/O
direct path read	            83,155	1,675	20	        1.32	        User I/O
PX qref latch	                    72,012,356	1,021	0	        0.80	        Other
db file sequential read	            57,506	450	8	        0.35	        User I/O

The DB CPU plus foreground waits comes to only about 20% of DB TIme! What is going on? Where has my DB Time gone?? A burning question for many DBAs. The answer is that these figures (which are, in effect, derived from a query against v$sys_time_model and v$system_event) show only DB CPU time and waiting time. They do not include time spent on IO. From the same report, here are the lines from the Instance Activity Statistics that are relevant:
physical read total bytes	                771,136,799,744	776,703,433.25	345,027,650.89
physical read total multi block requests	1,447,136	1,457.58	647.49
physical reads	                                23,531,016	23,700.88	10,528.42
physical reads cache	                        106,832	        107.60	        47.80
physical reads cache prefetch	                47,356	        47.70	        21.19
physical reads direct	                        23,424,152	23,593.24	10,480.60
physical reads direct (lob)	                11	        0.01	        0.00
physical reads direct temporary tablespace	7,801,537	7,857.85	3,490.62
physical reads prefetch warmup	                0	        0.00	        0.00
physical write IO requests	                1,242,020	1,250.99	555.71
physical write bytes	                        251,516,518,400	253,332,149.92	112,535,354.99
physical write total IO requests	        1,266,781	1,275.93	566.79
physical write total bytes	                251,704,821,248	253,521,812.08	112,619,606.82
the database is reading 776MB per second and writing 253MB per second. That's a respectable amount of IO, and that is where the DB Time is going. This activity is not showing up as significant foreground wait events, because there isn't much waiting: the foreground processes are working flat out on IO.

Why do so many people believe that DB TIme is only DB CPU time plus waiting time? Well, the Oracle Uni course material certainly doesn't help. This is from the 11g Database Tuning course:

Quote:
DB time = DB CPU time + DB wait time
which is not correct, as can be seen from a glance at many AWR reports.
The best description I have of the fact that DB Time is not just DB CPU and waiting but also IO is in this slide deck from John Beresniewicz (an authoritative source, working for Oracle Corp at the time):
http://www.perfvision.com/docs/JB_AAS.pdf
note slides 6, 10, 11, 32, and 33 in particular. That should prevent any more confusion.

To conclude: DB Time includes IO time, which is not captured in v$sys_time_model and therefore not shown in your AWR or Statspack reports. That is usually the answer to the burning question "where has my DB time gone?"
--
John Watson
Oracle Certified Master DBA
http://skillbuilders.com