Oracle system statistic average by day

Oracle system statistic average by hour


select      to_char (begin_interval_time, 'day' ) snap_time, avg(value) avg_value
from        dba_hist_sysstat
natural join
            dba_hist_snapshot
where
            stat_name = '&stat_name'
group by    to_char(begin_interval_time, 'day')
order by  
        decode(
        to_char (begin_interval_time, 'day'),
        'sunday', 1,
        'monday', 2,
        'tuesday', 3,
        'wednesday', 4,
        'thursday', 5,
        'friday', 6,
        'saturday', 7
          );

Query uses dba_hist_sysstat view to show average values by day of week.

Oracle system statistic average by hour

select      to_char (begin_interval_time, 'hh24' ) snap_time, avg(value) avg_value
from        dba_hist_sysstat
natural join
            dba_hist_snapshot
where
            stat_name = '&stat_name'
group by    to_char(begin_interval_time, 'hh24')
order by    to_char (begin_interval_time, 'hh24');

Query uses dba_hist_sysstat view to show average values by hour or the day.

Oracle Tuning Hierarchy

Improving SQL Statement Tuning with Automatic SQL Tuning

This tutorial describes how to benefit from Automatic SQL Tuning to automatically tune your high loaded SQL statements.

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/manage/ast/ast.htm

SQL usage & statistics in the shared pool

v$sqlarea contains statistics about SQL in the shared pool and its usage statistics

select * from
                     ( select sql_text,
                                 cpu_time/1000000000 cpu_time,
                                 elapsed_time/1000000000 elapsed_time,
                                 disk_reads,
                                 buffer_gets,
                                 rows_processed
                       from v$sqlarea
                       order by cpu_time desc, disk_reads desc
                     )
where rownum   < 21

Query shows SQL statement by their CPU usage.

CPU usage statistics (CPU Time)

CPU usage is important for response time analysis.
Response Time = Service Time + Wait Time.
Service Time = CPU Parse Time/CPU Recursive Time/CPU Other
If CPU usage shows large response time, the database should be tuned according to CPU usage.

Query show the CPU time utilization by the database since last startup of database.

CPU-Time

Select
          name,
          value
from   v$sysstat
where upper (name) like '%CPU%';
CPU statistics from v$sysstat, database 9i R2

Temporary space used by SQL call in current session (oracle 10g)

select
        sql_text,
        sid,
        c.username,
        machine,
        tablespace,
        extents,
        blocks
from
        sys.v_$sort_usage a,
        sys.v_$sqlarea b,
        sys.v_$session c
where
        a.sqladdr = b.address and
        a.sqlhash = b. hash_value and
        a.session_addr = c.saddr
order by sid;

We can check, SQL call current session information and details. How much temporary space SQL call is using.