1 2 Previous Next 16 Replies Latest reply: Mar 27, 2013 8:18 AM by marksmithusa RSS

    Supervising degradataion of  I/O from database

    user622061
      Hello all,

      My aim is to supervise "I/O" performance from the database( not from OS tools) and alerting when the degradation ( = speed of IO) occurs.
      But, it is hard to understand some subtleties.
      I tried two manners : using v$filestat and v$session without a real success.

      1) method 1 : using v$filestat

      select trunc(v$filestat.readtim/100) "Read Time_s" , trunc(v$filestat.writetim/100) "Write Time_s"
      from v$filestat
      where file#=698;
      Read Time_s Write Time_s
      ----------- ------------
      197083 199667

      What does these values mean ??
      I'm expecting a known and usual value ( on all forum and discussion) between "2mn" and "15ms" depending on the performance of the server, disks and hardware.

      2) method 2 : using v$session
      col event for a30
      col wait_ms for 9999999
      select sid, event, wait_ms from (
      select sid, event, wait_time_micro, trunc(wait_time_micro/1000) ||'ms' wait_ms, seconds_in_wait,wait_time
      from v$session
      where event in ( 'db file sequential read','db file scattered read')
      order by 3 desc) where rownum < 6
      ;
      SID EVENT WAIT_MS
      ---------- ------------------------------ ------------------------------------------
      5249 db file sequential read 14ms
      848 db file sequential read 12ms
      7 db file sequential read 12ms
      291 db file sequential read 6ms
      1402 db file sequential read 5ms

      Ok, the speed seems to be as usual values, but is this query correct ?
      Can we say, that "sid=848" spend 12ms ( at least) to do a "db file sequential read" ?


      Maybe, both of that methods are wrong ... maybe, I don't understand something.

      Many thanks in advance.
        • 1. Re: Supervising degradataion of  I/O from database
          sb92075
          user622061 wrote:
          Hello all,

          My aim is to supervise "I/O" performance from the database( not from OS tools) and alerting when the degradation ( = speed of IO) occurs.
          Let us stipulate you can properly code this "alarm".
          what exactly will you do with this alarm after it is sounded?

          Handle:     user622061
          Status Level:     Newbie
          Registered:     Feb 25, 2008
          Total Posts:     48
          Total Questions:     22 (17 unresolved)


          I extend my condolences to you since you rarely get answers to your questions here.
          • 2. Re: Supervising degradataion of  I/O from database
            user622061
            Hello,
            It's very kind of you to check my history, instead to help to found an answer for my question :-) :-)
            If it's important to you here is the explanation :
            Yes, until today, I didnt' get attention to the two choice "yes" and "No" and I replied to thank the kind DBA who answered without choosing "Yes,..." by inattention. don't worry, all my questions ( except two are resolved) and myself I answered to another DBAs.

            About your technical question :
            "what exactly will you do with this alarm after it is sounded?"

            As every DBA knows, when the degradation of speed of IO occurs, ( in other terms, wait on IO increase), all sessions become slow.
            If we supervise degradation of IO speed or increase of wait on IO, we can search the responsible of this degradation ( sql_id, or something outside database ...etc).
            To do this we should know the "normal" speed of IO for our server ( as I said) usually between 2-15ms, and alerting when we reach some values ...

            Friendly.
            • 3. Re: Supervising degradataion of  I/O from database
              oralicious
              you'll want the enterprise manager here. you really will. if you use anything else, you're not doing it right

              http://docs.oracle.com/cd/E11882_01/server.112/e10897/montune.htm
              • 4. Re: Supervising degradataion of  I/O from database
                JohnWatson
                user622061 wrote:
                Hello all,

                My aim is to supervise "I/O" performance from the database( not from OS tools) and alerting when the degradation ( = speed of IO) occurs.
                But, it is hard to understand some subtleties.
                There are two pre-configured metrics that might do what you want, AVG_FILE_READ_TIME and AVG_FILE_WRITE_TIME. So all you need do is set an alert threshold on these with dbms_server_alert.set_threshold
                • 5. Re: Supervising degradataion of  I/O from database
                  sb92075
                  JohnWatson wrote:
                  user622061 wrote:
                  Hello all,

                  My aim is to supervise "I/O" performance from the database( not from OS tools) and alerting when the degradation ( = speed of IO) occurs.
                  But, it is hard to understand some subtleties.
                  There are two pre-configured metrics that might do what you want, AVG_FILE_READ_TIME and AVG_FILE_WRITE_TIME. So all you need do is set an alert threshold on these with dbms_server_alert.set_threshold
                  over what duration are these "average" values calculated?
                  • 6. Re: Supervising degradataion of  I/O from database
                    VamshiDamidi
                    Oracle Database is extension to your underlying DISK layout which means every request raised to Oracle ex: any sql will use complete IO provided by your DISK layout.

                    You can check IO and throughput of your storage by using below procedure

                    SET SERVEROUTPUT ON
                    DECLARE
                    lat INTEGER;
                    iops INTEGER;
                    mbps INTEGER;
                    BEGIN
                    -- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (<DISKS>, <MAX_LATENCY>, iops, mbps, lat);
                    DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 10, iops, mbps, lat);

                    DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
                    DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);
                    dbms_output.put_line('max_mbps = ' || mbps);
                    end;
                    /

                    Please run this procedure as SYS only when database is idle since this is used as benchmarking of your IO previously called as Oracle Orion IO bench marking tool

                    Now that you know the capacity of your system you might want to know current utilization of your database IO.

                    Below query should provide you with current IO utilization of your system

                    select sum(value)/1024/1024 total_io
                    from v$statname c, v$sesstat a
                    where a.statistic# = c.statistic#
                    and c.name in ('physical reads', 'physical writes',
                    'physical reads direct',
                    'physical reads direct (lob)',
                    'physical writes direct',
                    'physical writes direct (lob)');

                    In simple terms to know if you database is performing well you might want to check response time

                    Using below query will provide you with DB response time interms of CPU time spend to DB Time

                    select METRIC_NAME,
                    VALUE
                    from SYS.V_$SYSMETRIC
                    where METRIC_NAME IN ('Database CPU Time Ratio',
                    'Database Wait Time Ratio') AND
                    INTSIZE_CSEC =
                    (select max(INTSIZE_CSEC) from SYS.V_$SYSMETRIC);

                    High the CPU time value consider better is your system response and these values change every i believe every sec.


                    Thanks,
                    Vamshi .D
                    • 7. Re: Supervising degradataion of  I/O from database
                      jgarry
                      How do you know those various physical reads and writes are actually getting to a disk once they leave Oracle?
                      • 8. Re: Supervising degradataion of  I/O from database
                        J19
                        Hi,

                        can you try below sql query. May be it will help you.

                        set linesize 140
                        col spid for a6
                        col program for a35 trunc
                        col username for a15
                        select s.status, s.sid, p.spid SPID,to_char(s.LOGON_TIME,'DDMonYY HH24:MI') date_login,s.username,decode(nvl(p.background,0),1,bg.description, s.program ) program,
                        ss.value/100 CPU,physical_reads disk_io,(trunc(sysdate,'J')-trunc(logon_time,'J')) days,
                        round((ss.value/100)/(decode((trunc(sysdate,'J')-trunc(logon_time,'J')),0,1,(trunc(sysdate,'J')-trunc(logon_time,'J')))),2) cpu_per_day
                        from V$PROCESS p,V$SESSION s,V$SESSTAT ss,V$SESS_IO si,V$BGPROCESS bg
                        where s.paddr=p.addr and ss.sid=s.sid
                        and ss.statistic#=12 and si.sid=s.sid
                        and bg.paddr(+)=p.addr
                        and round((ss.value/100),0) > 10
                        order by 8;

                        Regards,
                        Jey
                        • 9. Re: Supervising degradataion of  I/O from database
                          sb92075
                          duly underwhelming!
                          SQL> connect / as sysdba 
                          Connected.
                          SQL> set linesize 140 
                          col spid FOR a6 
                          col program FOR a35 trunc 
                          col username FOR a15 
                          SELECT s.status, 
                                 s.sid, 
                                 p.spid                                           SPID, 
                                 To_char(s.logon_time, 'DDMonYY HH24:MI')         date_login, 
                                 s.username, 
                                 Decode(Nvl(p.background, 0), 1, bg.description, 
                                                              s.program)          program, 
                                 ss.value / 100                                   CPU, 
                                 physical_reads                                   disk_io, 
                                 ( Trunc(SYSDATE, 'J') - Trunc(logon_time, 'J') ) days, 
                          Round(( ss.value / 100 ) / ( 
                          Decode(( Trunc(SYSDATE, 'J') - Trunc(logon_time, 'J') ), 0, 1, 
                          ( 
                                                 Trunc(SYSDATE, 'J') - Trunc(logon_time, 'J') )) ), 2) 
                                                                           cpu_per_day 
                          FROM   v$process p, 
                                 v$session s, 
                                 v$sesstat ss, 
                                 v$sess_io si, 
                                 v$bgprocess bg 
                          WHERE  s.paddr = p.addr 
                                 AND ss.sid = s.sid 
                                 AND ss.statistic# = 12 
                                 AND si.sid = s.sid 
                                 AND bg.paddr(+) = p.addr 
                                 AND Round(( ss.value / 100 ), 0) > 10 
                          ORDER  BY 8; SQL> SQL> SQL> SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27  
                          
                          no rows selected
                          
                          SQL> 
                          • 10. Re: Supervising degradataion of  I/O from database
                            VamshiDamidi
                            VamshiDamidi wrote:
                            IO request raised by Oracle goes through various caching framework to see if request can be fulfilled before querying disk to pull requested data
                            Various frameworks:
                            DB Cache
                            unix filesystem Cache
                            SAN caching framework
                            FAST Cache
                            Considering DB Cache and to get more precise values about actual IO performed
                            Below query provides you with IO that being performed from memory
                            select sum(value) total_io
                            from v$statname c, v$sesstat a,
                            v$session b, v$bgprocess p
                            where a.statistic# = c.statistic#
                            and p.paddr = b.paddr
                            and b.sid = a.sid
                            and c.name in ('consistent gets', 'db block gets')
                            (physical reads + physical writes ) - ( consistent gets + block gets ) should give actual IO performed by system.
                            Gary -- Please add if i am miss something.
                            Thanks,
                            Vamshi .D

                            Edited by: VamshiDamidi on Mar 26, 2013 5:05 AM
                            • 11. Re: Supervising degradataion of  I/O from database
                              sb92075
                              VamshiDamidi wrote:
                              IO request raised by Oracle goes through various caching framework to see if request can be fulfilled before querying disk to pull requested data
                              Various frameworks:

                              DB Cache
                              unix filesystem Cache
                              SAN caching framework
                              FAST Cache

                              Considering DB Cache and to get more precise values about actual IO performed
                              Below query provides you with IO that being performed from memory

                              select sum(value) total_io
                              from v$statname c, v$sesstat a,
                              v$session b, v$bgprocess p
                              where a.statistic# = c.statistic#
                              and p.paddr (+) = b.paddr
                              and b.sid = a.sid
                              and c.name in ('consistent gets', 'db block gets')


                              (physical reads + physical writes ) - ( consistent gets + block gets ) should give actual IO performed by system.

                              Gary -- Please add if i am miss something.

                              Thanks,
                              Vamshi .D
                              once the results from your posted SQL are produced, what can or should be done next?
                              How to decide if a problem exists or not?

                              is 7456 a good value or one that requires action? if so, what action?

                              IMO, numbers alone are meaningless.
                              • 12. Re: Supervising degradataion of  I/O from database
                                J19
                                Hi sb92075

                                It is particularly useful when your database is under heavy load or heavy utilization.

                                Regards
                                Jey
                                • 13. Re: Supervising degradataion of  I/O from database
                                  oralicious
                                  Jey A wrote:
                                  Hi sb92075

                                  It is particularly useful when your database is under heavy load or heavy utilization.

                                  Regards
                                  Jey
                                  >
                                  Hi sb92075

                                  It is particularly useful when your database is under heavy load or heavy utilization.

                                  Regards
                                  Jey


                                  how is it useful? its just a number. maybe you had a process that was eating CPU and was bad but had another process that was eating IO and was good. What does that query return to help you?
                                  • 14. Re: Supervising degradataion of  I/O from database
                                    sb92075
                                    Jey A wrote:
                                    Hi sb92075

                                    It is particularly useful when your database is under heavy load or heavy utilization.

                                    Regards
                                    Jey
                                    in this case ignorance is bliss & you can rest satisfied with your results.
                                    1 2 Previous Next