The performance of Oracle is determined by 4 factors: the CPU, internal memory, disk access and network performance. The latter, network performance, is only relevant if data are written or read from a network device. In scheme, it looks like:
Let us look at the CPU. The most important data can be derived from this sql:
select sql_text,
to_char(LAST_ACTIVE_TIME,'YYYY-MM-DD HH24:MI:SS') LAST_ACTIVE_TIME ,
cpu_time/1000000 cpu_time,
elapsed_time/1000000 elapsed_time,
rows_processed
from v$sqlarea
where last_active_time > SYSDATE-0.001
order by last_active_time desc
;
This query uses a view v$sqlarea that contains most important CPU indicators.
This query provides the time that is spent is cpu. It is provided in seconds. Likewise, the elapsed time is provided in seconds.
The rows_processed field is an interesting one. It indicates the records that are sent to the user. If a summary is required, whereby the summary is given in one record, we see rows_processed = 1. If we have a setting in the client whereby only 50 records are returned, we see
rows_processed = 50.
If we fire 2 SQL statements with the same texts, we see that rows_processed, cpu_time are added across the two queries. This implies that a SQL summary will show rows_processed = 2 as two SQL statements are run, each summary providing one rows_processed.
Additional information can be derived from another query that provides CPU per session:
SELECT n.username, t.name, s.sid, s.value/1000 FROM
v$sesstat s,
v$statname t,
v$session n
WHERE
s.statistic# = t.statistic# AND n.sid = s.sid
AND t.name='CPU used by this session' ORDER BY s.value desc;
This provides CPU used in milli seconds (hence the division by 1000) as split per user session. This then shows which user consumes most cpu. Very roughly, the results from this query should be aligned to the earlier query that provides cpu time per query.
A final word. One may ask if the cpu is overloaded when running a query This can be checked by monitoring the temperature in the core. A nice programme is “Core Temp” that may warn you for overheated cores.