Oracle Disk I/O Performans Ölçme

 merhaba

 ben oracle database uzmani degilim ancak asagidaki linkleri incelediniz mi? 

 

http://docs.oracle.com/cd/E11882_01/server.112/e10822/tdppt_realtime.htm

http://kevinclosson.wordpress.com/2006/12/07/using-oracle-disk-manager-to-monitor-database-io/

Merhaba,

Eğer ASM yapısı mevcutsa Oracle veritabanınızda  http://uguroracle.blogspot.de/2011/09/asm-disk-ve-disk-grubu-io-performansnn.html adresindeki yazımı okuyabilirsiniz I/O yük bilgisini görmek için.

Eğer ASM kullanmıyorsanız, aşağıdaki sorgu faydalı olacaktır. 2 farklı AWR snasphot aralığı girerek her bir tablespace için bir haftaya kadar I/O yükünün ortalamasını görebilirsiniz. Oldukça faydalı sonuçlar verecektir ortalama I/O bazında. Fiziksel I/O bulmak için LOGICAL READS kolonu bilgilerini es geçin...

SELECT owner, tablespace_name, table_name,    sum(logical_reads) as "LOGICAL READS",

       sum(logical_reads_ratio) as logical_reads_ratio,    sum(physical_reads) as "PHYSICAL READS",

       sum(physical_reads_ratio) as physical_reads_ratio,    sum(physical_writes) as "PHYSICAL WRITES",

       sum(physical_writes_ratio) as physical_writes_ratio

FROM (

select n.owner, n.tablespace_name,

       case when n.object_type = 'TABLE' then n.object_name

            when n.object_type = 'INDEX' then (SELECT table_name from dba_indexes WHERE index_name = n.object_name)

            when n.object_type = 'LOB' then (SELECT table_name from dba_lobs WHERE segment_name = n.object_name)

                                      else 'N/A' end as Table_name,

       n.object_name, n.object_type,  r.logical_reads,  round(r.logical_reads_ratio * 100, 2) logical_reads_ratio,

       r.physical_reads,  round(r.physical_reads_ratio * 100, 2) physical_reads_ratio,   r.physical_writes,

       round(r.physical_writes_ratio * 100, 2) physical_writes_ratio

  from dba_hist_seg_stat_obj n,

       (select *

        from (select e.dataobj#, e.obj#, e.dbid,  sum(e.logical_reads_delta) logical_reads,

                     ratio_to_report(sum(e.logical_reads_delta)) over () logical_reads_ratio,

                     sum(e.physical_reads_delta) physical_reads,

                     ratio_to_report(sum(e.physical_reads_delta)) over () physical_reads_ratio,

                     sum(e.physical_writes_delta) physical_writes,

                     ratio_to_report(sum(e.physical_writes_delta)) over () physical_writes_ratio

              from dba_hist_seg_stat e

              where e.snap_id > baslangıc_snapshot_id and e.snap_id <= bitis_sonuc_id

              group by e.dataobj#, e.obj#, e.dbid

              having sum(e.logical_reads_delta)   > 0 or  sum(e.physical_reads_delta)  > 0 or  sum(e.physical_writes_delta) > 0

              order by logical_reads desc) d

         ) r

 where n.dataobj# = r.dataobj#

   and n.obj#     = r.obj#

   and n.dbid     = r.dbid

   and owner !='SYS'

  ) c

group by owner, tablespace_name, table_name

 order by 4 desc;

 

Hocam bilgiler için teşekkürderim