如何利用 windows bat 脚本制作一个oracle 性能状态监控工具

###利用 windows bat 脚本制作一个oracle 性能状态监控工具

####choice /t 2 /d y /n >nul 表示每隔2秒定时执行

@echo off

:start
sqlplus -S system/oracle@10.80.18.201/fiona @c:/1.sql

choice /t 2 /d y /n >nul
cls

goto start

########################################c:/1.sql 文本内容##################

###########1.sql 查询数据库的脚本可以根据自己关注的点，自定义#####################

set line 300 pagesize 20

col dbro for a11
col dbts for a8

SELECT /*+ OPT_PARAM('_optimizer_adaptive_plans','false') */ /*+ NO_MONITOR */
 *
  FROM (SELECT /*+ OPT_PARAM('_optimizer_adaptive_plans','false') */
         round(sum(value),2) taas
          from gv$sysmetric
         where metric_name = 'Database Time Per Sec'
           and group_id = 3),
       (SELECT /*+ OPT_PARAM('_optimizer_adaptive_plans','false') */
         count(*) sess, count(distinct username) duser
          from gv$session
         where type <> 'BACKGROUND'
           and username is not null
           and SCHEMA# != 0),
       (SELECT /*+ OPT_PARAM('_optimizer_adaptive_plans','false') */
         to_char(max(end_time), 'hh24:mi:ss') dbts, sum(value) spga
          from gv$sysmetric
         where metric_name = 'Total PGA Allocated'
           and group_id = 3),
       (select (sysdate - startup_time) * 86400 uptm from v$instance),
       (SELECT /*+ OPT_PARAM('_optimizer_adaptive_plans','false') */
         SUM(VALUE) scpu, count(distinct inst_id) inst
          from gv$osstat
         where stat_name = 'NUM_CPUS'),
       (SELECT /*+ OPT_PARAM('_optimizer_adaptive_plans','false') */
         SUM(VALUE) ssga
          from gv$sga),
       (SELECT /*+ OPT_PARAM('_optimizer_adaptive_plans','false') */
         SUM(VALUE) prob
          from GV$DIAG_INFO
         where NAME = 'Active Problem Count'),
       (select sum(fra) reco
          from (select SPACE_USED / GREATEST(SPACE_LIMIT, 1) * 100 fra
                  from V$RECOVERY_FILE_DEST
                union
                select 0 fra from dual)),
       (select initcap(substr(sys_context('USERENV', 'DATABASE_ROLE'),
                              -7,
                              16)) dbro
          from dual),
       (select sum(ar) dgar
          from (SELECT /*+ OPT_PARAM('_optimizer_adaptive_plans','false') */
                 sofar ar
                  from gv$recovery_progress
                 where type = 'Media Recovery'
                   and ITEM = 'Active Apply Rate'
                   and rownum = 1
                union
                select 0 ar from dual)),
       (SELECT /*+ OPT_PARAM('_optimizer_adaptive_plans','false') */
         max(VALUE) pgag
          from gv$pgastat
         where name = 'aggregate PGA target parameter');

SELECT /*+ OPT_PARAM('_optimizer_adaptive_plans','false') */ /*+ NO_MONITOR */
inid,
round(hcpu,2) hcpu,
round(sgfr,2) sgfr,
round(utps,2) utps,
round(ucps,2) ucps,
round(saas,2) saas,
round(mbps,2) mbps,
round(ssrt,2) ssrt,
round(iorl,2) iorl,
round(load,2) load,
round(upga,2) upga,
round(aspq,2) aspq,
round(dbcp,2) dbcp,
round(dbwa,2) dbwa,
round(iops,2) iops,
round(asct,2) asct,
round(isct,2) isct,
round(cpas,2) cpas,
round(ioas,2) ioas,
round(waas,2) waas,
round(dcpu,2) dcpu,
round(ncpu,2) ncpu,
round(logr,2) logr,
round(phyr,2) phyr,
round(phyw,2) phyw,
round(temp,2) temp,
round(dbtm,2) dbtm
  FROM (SELECT /*+ OPT_PARAM('_optimizer_adaptive_plans','false') */
         inst_id inid,
         sum(decode(metric_name, 'CPU Usage Per Sec', value, 0)) dcpu,
         sum(decode(metric_name, 'Host CPU Utilization (%)', value, 0)) hcpu,
         sum(decode(metric_name, 'I/O Megabytes per Second', value, 0)) mbps,
         sum(decode(metric_name, 'SQL Service Response Time', value, 0)) ssrt,
         sum(decode(metric_name,
                    'Average Synchronous Single-Block Read Latency',
                    value,
                    0)) iorl,
         sum(decode(metric_name, 'Current OS Load', value, 0)) load,
         sum(decode(metric_name, 'Active Parallel Sessions', value, 0)) aspq,
         sum(decode(metric_name, 'Database CPU Time Ratio', value, 0)) dbcp,
         sum(decode(metric_name, 'Database Wait Time Ratio', value, 0)) dbwa,
         sum(decode(metric_name, 'I/O Requests per Second', value, 0)) iops
          from gv$sysmetric
         where metric_name in
               ('CPU Usage Per Sec', 'Host CPU Utilization (%)',
                'I/O Megabytes per Second', 'SQL Service Response Time',
                'Average Synchronous Single-Block Read Latency',
                'Current OS Load', 'Active Parallel Sessions',
                'Database CPU Time Ratio', 'Database Wait Time Ratio',
                'I/O Requests per Second')
           and group_id = 2
         group by inst_id),
       (SELECT /*+ OPT_PARAM('_optimizer_adaptive_plans','false') */
         inst_id id1,
         sum(decode(metric_name, 'Shared Pool Free %', value, 0)) sgfr,
         sum(decode(metric_name, 'User Transaction Per Sec', value, 0)) utps,
         sum(decode(metric_name, 'User Calls Per Sec', value, 0)) ucps,
         sum(decode(metric_name, 'Average Active Sessions', value, 0)) saas,
         sum(decode(metric_name, 'Total PGA Allocated', value, 0)) upga,
         sum(decode(metric_name, 'Logical Reads Per Sec', value, 0)) logr,
         sum(decode(metric_name, 'Physical Reads Per Sec', value, 0)) phyr,
         sum(decode(metric_name, 'Physical Writes Per Sec', value, 0)) phyw,
         sum(decode(metric_name, 'Temp Space Used', value, 0)) temp,
         sum(decode(metric_name, 'Database Time Per Sec', value, 0)) dbtm
          from gv$sysmetric
         where metric_name in
               ('Shared Pool Free %', 'User Transaction Per Sec',
                'User Calls Per Sec', 'Logical Reads Per Sec',
                'Physical Reads Per Sec', 'Physical Writes Per Sec',
                'Temp Space Used', 'Database Time Per Sec',
                'Average Active Sessions', 'Total PGA Allocated')
           and group_id = 3
         group by inst_id),
       (SELECT id2,
               sum(asct) asct,
               sum(isct) isct,
               sum(cpas) cpas,
               sum(ioas) ioas,
               sum(waas) waas
          FROM (SELECT /*+ OPT_PARAM('_optimizer_adaptive_plans','false') */
                 inst_id id2,
                 sum(DECODE(status, 'ACTIVE', 1, 0)) asct,
                 count(*) isct,
                 sum(DECODE(status, 'ACTIVE', decode(WAIT_TIME, 0, 0, 1), 0)) cpas,
                 sum(DECODE(status,
                            'ACTIVE',
                            decode(wait_class, 'User I/O', 1, 0),
                            0)) ioas,
                 sum(DECODE(status,
                            'ACTIVE',
                            decode(WAIT_TIME,
                                   0,
                                   decode(wait_class, 'User I/O', 0, 1),
                                   0),
                            0)) waas
                  from gv$session
                 where type <> 'BACKGROUND'
                   and username is not null
                   and SCHEMA# != 0
                 group by inst_id
                UNION ALL
                SELECT /*+ OPT_PARAM('_optimizer_adaptive_plans','false') */
                 inst_id id2, 0 asct, 0 isct, 0 cpas, 0 ioas, 0 waas
                  from gv$instance)
         group by id2),
       (SELECT /*+ OPT_PARAM('_optimizer_adaptive_plans','false') */
         inst_id id3, TO_NUMBER(VALUE) ncpu
          from gv$osstat
         where stat_name = 'NUM_CPUS')
 WHERE id1 = inid
   and id2 = inid
   and id3 = inid
   and ROWNUM <= 5
 ORDER BY dbtm desc;
 
 
 col event for a40
 col wclas for a10
 
host echo "########TOP event######################################################"
SELECT /*+ OPT_PARAM('_optimizer_adaptive_plans','false') */ /*+ NO_MONITOR */
          event,
          totwa,
          twsec,
          round(avgms,2) as avgms,
          ROUND(RATIO_TO_REPORT(twsec) OVER() * 100) pctwa,
          wclas,
          evtid
           FROM (SELECT /*+ OPT_PARAM('_optimizer_adaptive_plans','false') */
                  SW.EVENT,
                  SUM(SE.TOTAL_WAITS) totwa,
                  SUM(SE.TIME_WAITED) / 100 twsec,
                  SUM(SE.TIME_WAITED) /
                  (GREATEST(SUM(SE.TOTAL_WAITS), 1) * 10) avgms,
                  SW.WAIT_CLASS wclas,
                  SW.EVENT# evtid
                   FROM GV$SESSION_WAIT_CLASS SE
                   JOIN GV$SESSION SW ON SE.INST_ID = SW.INST_ID
                                     AND SE.SID = SW.SID
                  WHERE SE.wait_class != 'Idle'
                    AND SW.wait_class != 'Idle'
                  GROUP BY SW.EVENT, SW.WAIT_CLASS, SW.EVENT#
                 HAVING SUM(SE.TOTAL_WAITS) > 0
                 UNION
                 SELECT /*+ OPT_PARAM('_optimizer_adaptive_plans','false') */
                  'DB CPU' event,
                  0 totwa,
                  SUM(VALUE) / 100 twsec,
                  0 avgms,
                  NULL wclas,
                  19 evtid
                   FROM GV$SESSTAT se
                   JOIN GV$SESSION s2 ON se.INST_ID = s2.INST_ID
                                     AND se.SID = s2.SID
                  WHERE se.STATISTIC# = 19
                    AND se.value > 0
                    AND s2.wait_class != 'Idle'
                  ORDER BY twsec desc)
          WHERE ROWNUM < 6; 

 exit