Tuesday, March 26, 2013

All Oracle Application server, Database books / study material / pdf / Documents

All Oracle  books / study material / pdf / Documents at one place.



Posted by at 1 comment:

Monday, March 18, 2013

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.
Posted by at No comments:

Friday, March 15, 2013

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.


Posted by at No comments:

Thursday, March 14, 2013

Oracle Tuning Hierarchy


Posted by at No comments:

Saturday, March 9, 2013

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
Posted by at No comments:

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.

Posted by at No comments:

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
Posted by at No comments:
Subscribe to: Posts (Atom)