Shulou(Shulou.com)06/01 Report--

There is the term DB time in AWR, so what is DB time?

The Oracle10gR2 official documentation gives a detailed explanation (Oracle10gPerformance Tuning Guide 5.1.1.2 Time Model Statistics)

The most important of the time model statistics is DB time. This statistics represents the total time spent in

Database calls and is an indicator of the total instance workload. It is calculated by aggregating the CPU and

Wait times of all sessions not waiting on idle wait events (non-idle user sessions).

According to the official documentation, DB time=DB CPU+ DB waiting time (no-idle time)

So let me pick a session to verify it.

SQL > select sid,stat_id,stat_name,value/1000000/60 from V$sess_TIME_MODEL where sid=194

SID STAT_ID STAT_NAME VALUE/1000000/60

-

194 3649082374 DB time 33.5681982

194 2748282437 DB CPU 32.9633455

194 4157170894 background elapsed time 0

194 2451517896 background cpu time 0

194 4127043053 sequence load elapsed time 0

194 1431595225 parse time elapsed. 0005325

194 372226525 hard parse elapsed time. 000498433

194 2821698184 sql execute elapsed time 33.5674656

194 1990024365 connection management call elapsed time. 000047933

194 1824284809 failed parse elapsed time 0

194 4125607023 failed parse (out of shared memory) elapsed time 0

194 3138706091 hard parse (sharing criteria) elapsed time 0

194 268357648 hard parse (bind mismatch) elapsed time 0

194 2643905994 PL/SQL execution elapsed time. 0000051

194 290749718 inbound PL/SQL rpc elapsed time 0

194 1311180441 PL/SQL compilation elapsed time 0

194 751169994 Java execution elapsed time 0

194 1159091985 repeated bind elapsed time. 0000001

194 2411117902 RMAN cpu time (backup/restore) 0

19 rows selected.

Elapsed: 00:00:01.61

SQL > select (sysdate-logon_time) * 24060 minutes, username,machine,sid,serial#,event,p1,p2,p3

2 from v$session where sid=194

MINUTES USERNAME MACHINE SID SERIAL# EVENT P1 P2 P3

34.4833333 WHSUSRGL NA/BDC-KALIDO001 194 28391 direct path read tem 202 89709 1

P

Elapsed: 00:00:01.15

Session 194has been connected to the database for 34.4833333 minutes, DB time=33.5681982, CPU time=32.9633455

Now let's check ASH. We know that ASH is sampled every 1 second.

SQL > select count (*)

2 from v$active_session_history ash, v$event_name enm

3 where ash.event#=enm.event#

4 and SESSION_ID=194

COUNT (*)

-

forty-seven

So the 194s session waited about 47 seconds, that is, about. 78333333 minutes

SQL > select 47max 60 from dual

47/60

-

. 783333333

SQL > select 32.9633455room.783333333 from dual

32.9633455mm. 783333333

-

33.7466788

DB CPU + wait time = 33.7466788, but there is little difference between them in DB time=33.5681982, which basically shows

DB time=DB CPU+ DB waiting time (no-idle time)

Note: DB time in AWR is equal to the sum of all session DB time (excluding background processes)

SQL > select SESSION_ID,NAME,P1,P2,P3

2 from v$active_session_history ash, v$event_name enm

3 where ash.event#=enm.event#

4 and SESSION_ID=194

SESSION_ID NAME P1 P2 P3

194 db file sequential read 202 106634 1

194 db file sequential read 202 53541 1

194 db file sequential read 202 204387 1

194 db file sequential read 202 242316 1

194 db file sequential read 202 251848 1

194 db file sequential read 202 201689 1

194 db file scattered read 45 480505 16

194 db file scattered read 44 169145 16

194 db file scattered read 45 32489 16

194 db file scattered read 44 316585 16

194 db file scattered read 46 255817 16

194 db file scattered read 47 204105 16

194 db file scattered read 44 236633 16

194 db file scattered read 46 222761 16

194 db file scattered read 44 232969 16

194 db file scattered read 44 230489 16

194 db file scattered read 44 227673 16

194 db file scattered read 45 177785 16

194 db file scattered read 44 292121 16

194 db file scattered read 45 136041 16

194 db file scattered read 28 17177 16

194 db file scattered read 47 233513 16

194 db file scattered read 44 222233 16

194 db file scattered read 46 211289 16

194 db file scattered read 47 152889 16

194 db file scattered read 45 164921 16

194 db file scattered read 47 149609 16

194 db file scattered read 44 312873 16

194 db file scattered read 45 157289 16

194 db file scattered read 45 155225 16

194 db file sequential read 50 30400 1

194 db file sequential read 43 8763 1

194 db file scattered read 36 518441 16

194 db file scattered read 37 504992 4

194 db file scattered read 38 195257 16

194 db file scattered read 37 209776 9

194 db file scattered read 41 119561 16

194 db file scattered read 39 62041 16

194 db file scattered read 41 4765 2

194 db file scattered read 38 438857 16

194 read by other session 38 335097 1

194 latch: cache buffers chains 1.3835E+19 122 0

194 db file sequential read 27 52122 1

194 db file sequential read 41 140105 1

194 read by other session 36 97230 1

194 db file scattered read 38 17369 16

194 db file sequential read 37 1889 1

47 rows selected.

When looking at AWR, you can compare DB time with the sampling interval. If the DB time is higher than the sampling interval, it means that the database is heavily loaded.

Similarly, you can compare DB time with DB CPU. If DB time is much higher than DB CPU, there are obvious performance problems in the database.

Because there is too much waiting time.

Reference: http://blog.csdn.net/robinson1988/article/details/6077944

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.