oracle 监听 bat,如何利用 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