mysql查询数据库负载_使用shell脚本查看数据库负载情况

set linesize 200

col begin_time format a35

col end_time format a35

col elapsed_time format 99999999.999

col workload_per format a20

SELECT

begin_time,end_time,

elapsed_time,

dbtime,

trunc(dbtime/decode(elapsed_time,0,1,elapsed_time),2)*100||'%' workload_per

from

(

select

B.SNAP_ID||' ** '||B.END_INTERVAL_TIME begin_time,

E.SNAP_ID||' ** '||E.END_INTERVAL_TIME end_time,

EXTRACT(DAY FROM E.END_INTERVAL_TIME - B.END_INTERVAL_TIME) * 1440 +

EXTRACT(HOUR FROM E.END_INTERVAL_TIME - B.END_INTERVAL_TIME)* 60 +

EXTRACT(MINUTE FROM E.END_INTERVAL_TIME -B.END_INTERVAL_TIME) +

EXTRACT(SECOND FROM E.END_INTERVAL_TIME -B.END_INTERVAL_TIME) / 60  elapsed_time,

db_time.dbtime

FROM DBA_HIST_SNAPSHOT B, DBA_HIST_SNAPSHOT E,

(

SELECT b.snap_id begin_snap, e.snap_id end_snap ,

round((sum(e.value) -

sum(b.value)) / 1000000 /60,2) dbtime

FROM DBA_HIST_SYS_TIME_MODEL e, DBA_HIST_SYS_TIME_MODEL b,

(

select min(snap_id)  begin_snap,max(snap_id)  end_snap from DBA_HIST_SNAPSHOT where begin_interval_time > sysdate -1

and EXTRACT(HOUR FROM END_INTERVAL_TIME) between $1 and $2

) temp_snap

WHERE

e.STAT_NAME = 'DB time'

and b.snap_id=temp_snap.begin_snap

and e.snap_id =temp_snap.end_snap

AND b.STAT_NAME = 'DB time'

group by e.snap_id,b.snap_id

) db_time

WHERE b.begin_interval_time > sysdate -1

and EXTRACT(HOUR FROM e.END_INTERVAL_TIME) between $1  and $2

and b.snap_id=db_time.begin_snap

and e.snap_id=db_time.end_snap

)

/

EOF

exit

在此基础上如果要查看每个小时的数据库负载情况，可以略做一些改进。

脚本showdbtimerpt.sh

sqlplus -s $DB_CONN_STR@$SH_DB_SID <

prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

set head off

set pages0

set feedback off

set serveroutput on

spool showdbtimerpt_tmp.sh

begin

for i in $1..$2 loop

dbms_output.put_line('ksh showdbtime  '||i||' '||(i+1));

end loop;

end;

/

spool off;

EOF

clear

echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

echo BEGIN_TIME------------------------- END_TIME--------------------------- ELAPSED_TIME- BTIME----- WORKLOAD_PER--------

echo ----------------------------------- ----------------------------------- ------------- ---------- --------------------

ksh showdbtimerpt_tmp.sh

rm showdbtimerpt_tmp.sh

exit

运行结果类似下面的样子。

BEGIN_TIME                                       END_TIME                                  ELAPSED_TIME       DBTIME      WORKLOAD_PER

----------------------------------- ----------------------------------- ------------- ---------- --------------------

201 ** 21-MAY-14 06.07.33.893 PM    201 ** 21-MAY-14 07.07.33.893 PM             60          120            200%

201 ** 21-MAY-14 07.07.33.893 PM    201 ** 21-MAY-14 08.07.33.893 PM             60          150            250%

201 ** 21-MAY-14 08.07.33.893 PM    201 ** 21-MAY-14 09.07.33.893 PM             60          240            400%

201 ** 21-MAY-14 09.07.33.893 PM    201 ** 21-MAY-14 10.07.33.893 PM             60          60              100%

201 ** 21-MAY-14 10.07.33.893 PM    201 ** 21-MAY-14 11.07.33.893 PM             60          120            200%

可以生成每个时间段的负载报表，这样就一目了然了。可以有针对性的根据负载进行相关的性能抓取。