SQL> select distinct sid from v$mystat
2 /
SID
----------
6
SQL>
Here is its CPU usage as seen from another session (in blue). The CPU used when call started and CPU used by this session figures are in hundredths of a second. The DB CPU figure is in microseconds:
SQL> select
2 (select value from v$sesstat a, v$statname b
3 where sid = 6
4 and a.statistic# = b.statistic#
5 and name = 'CPU used when call started') as CUWCS,
6 (select value from v$sesstat a, v$statname b
7 where sid = 6
8 and a.statistic# = b.statistic#
9 and name = 'CPU used by this session') as CUBTS,
10 (select value
11 from v$sess_time_model
12 where sid = 6
13 and stat_name = 'DB CPU') as "DB CPU"
14 from dual
15 /
CUWCS CUBTS DB CPU
---------- ---------- ----------
41669 41669 416810000
SQL>
The first session runs a query:
SQL> set timing on
SQL> select count(*) from dba_tables a, dba_tables b
2 /
While this is going on, the second session monitors it every 30 seconds The CPU used when call started figure stays the same while the SQL is running. This is reasonable. The CPU used by this session figure goes up a little then stays the same. It is not clear what this is measuring at all. The DB CPU figure appears to be updated regularly:
SQL> /
CUWCS CUBTS DB CPU
---------- ---------- ----------
41669 41752 441270000
SQL> /
CUWCS CUBTS DB CPU
---------- ---------- ----------
41669 41752 470980000
SQL> /
CUWCS CUBTS DB CPU
---------- ---------- ----------
41669 41752 494630000
SQL> /
CUWCS CUBTS DB CPU
---------- ---------- ----------
41669 41752 522780000
SQL>
The first session’s query finishes:
SQL> select count(*) from dba_tables a, dba_tables b
2 /
COUNT(*)
----------
9369721
Elapsed: 00:02:25.93
SQL>
Then the figures in the second session match again:
SQL> /
CUWCS CUBTS DB CPU
---------- ---------- ----------
54891 54891 549030000
SQL>
No comments:
Post a Comment