Database Reference
In-Depth Information
Table 4-1.
The Samples Produced by MMNL for the Load Illustrated by Figure
4-4
Sample ID
Timestamp
Session
SQL ID
Activity
20
06:28:09
1
gd90ygn1j4026
CPU
20
06:28:09
2
5m6mu5pd9w028
CPU
21
06:28:10
1
gd90ygn1j4026
CPU
21
06:28:10
2
5m6mu5pd9w028
CPU
22
06:28:11
1
gd90ygn1j4026
User I/O
22
06:28:11
2
5m6mu5pd9w028
CPU
23
06:28:12
1
gd90ygn1j4026
User I/O
23
06:28:12
2
5m6mu5pd9w028
CPU
24
06:28:13
1
7ztv2z24kw0s0
CPU
24
06:28:13
2
5m6mu5pd9w028
CPU
25
06:28:14
2
5m6mu5pd9w028
CPU
27
06:28:16
1
d9gdx5a4gc13y
CPU
28
06:28:17
1
1uaz41wrxw03k
User I/O
29
06:28:18
1
1uaz41wrxw03k
CPU
30
06:28:19
1
1uaz41wrxw03k
User I/O
31
06:28:20
1
1uaz41wrxw03k
User I/O
Based on the data shown in Table
4-1
, it's possible to derive the following information:
•
Session 1 was active for about 83% of the time (10 samples out of 12 seconds) and executed at
least four distinct SQL statements. Half of the processing time (5 samples out of 10) was spent
on CPU, and the other half doing disk I/O operations.
•
Session 2 was active for about 50% of the time (6 samples out of 12 seconds). During that time
it was always on CPU, executing a SQL statement identified by the ID
5m6mu5pd9w028
.
•
Session 3 and session 4 were 100% idle (0 samples out of 12 seconds).
aCtIVe SeSSION hIStOrY BUFFer
The buffer used to store active session history in the SGa is automatically dimensioned when the database
instance starts. Oracle's design goal is to hold one hour of activity in memory. To know how large the buffer is and
how much time it stores information for, you can execute the following queries:
SQL> SELECT pool, bytes
2 FROM v$sgastat
3 WHERE name = 'ASH buffers';
