用python了一个监控ORACLE逻辑读等参数的小程序

原创 Linux操作系统作者：wei-xh 时间：2011-08-11 20:31:54 0 删除编辑

import os
import cx_Oracle
import time
def getcon():
connection= cx_Oracle.connect('system/a35baba@crmgtest');
return connection;

conn=getcon();
sql="""select name, trunc(sum(value), 2)
from (select decode(name,
                      'physical read IO requests',
                      'Oracle IOPS',
                      'physical write IO requests',
                      'Oracle IOPS',
                      'redo writes',
                      'Oracle IOPS',
                      'physical read bytes',
                      'Oracle MBPS',
                      'physical write bytes',
                      'Oracle MBPS',
                      'redo size',
                      'Oracle MBPS') as name,
               decode(name,
                      'physical read IO requests',
                      value,
                      'physical write IO requests',
                      value,
                      'redo writes',
                      value,
                      'physical read bytes',
                      value / 1024 / 1024,
                      'physical write bytes',
                      value / 1024 / 1024,
                      'redo size',
                      value / 1024 / 1024) as value
          from v$sysstat
         where name in
               ('physical read IO requests', 'physical write IO requests',
                'physical read bytes', 'physical read total bytes',
                'physical write bytes', 'physical write total bytes',
                'physical read total IO requests',
                'physical write total IO requests', 'redo writes',
                'redo size'))
where name is not null
group by name
union all
SELECT b.name, a.value
FROM v$sysstat a, v$statname b
where a.statistic# = b.statistic#
   and b.name in ('consistent gets', 'execute count', 'parse count (hard)',
        'parse count (total)', 'redo size', 'redo writes',
        'sorts (disk)', 'sorts (memory)', 'sorts (rows)',
        'user commits', 'Oracle IOPS', 'Oracle MBPS')"""
y={'consistent gets':1, 'execute count':1, 'parse count (hard)':1,'parse count (total)':1, 'redo size':1, 'redo writes':1,'sorts (disk)':1, 'sorts (memory)':1, 'sorts (rows)':1,'user commits':1, 'Oracle IOPS':1, 'Oracle MBPS':1}
m=y.copy()
while 1:
    cursor=conn.cursor();
    cursor.execute(sql);
    row=cursor.fetchall();
    n=cursor.rowcount;

    for x in row:
          n=n-1
          y[x[0]]=x[1]
          if n==0:
               print 'consistent gets        %15d' % (y['consistent gets']-m['consistent gets'])
               print 'execute count          %15d' % (y['execute count']-m['execute count'])
               print 'parse count (hard)     %15d' % (y['parse count (hard)']-m['parse count (hard)'])
               print 'parse count (total)    %15d' % (y['parse count (total)']-m['parse count (total)'])
               print 'redo size              %15d' % (y['redo size']-m['redo size'])
               print 'redo writes            %15d' % (y['redo writes']-m['redo writes'])
               print 'sorts (disk)           %15d' % (y['sorts (disk)']-m['sorts (disk)'])
               print 'sorts (memory)         %15d' % (y['sorts (memory)']-m['sorts (memory)'])
               print 'sorts (rows)           %15d' % (y['sorts (rows)']-m['sorts (rows)'])
               print 'user commits           %15d' % (y['user commits']-m['user commits'])
               print 'Oracle IOPS            %15d' % (y['Oracle IOPS']-m['Oracle IOPS'])
               print 'Oracle MBPS            %15.2f' % (y['Oracle MBPS']-m['Oracle MBPS'])
    m=y.copy()
    time.sleep(1.5);
    os.system('cls')