前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >通过shell脚本监控日志切换频率 (94天)

通过shell脚本监控日志切换频率 (94天)

作者头像
jeanron100
发布2018-03-14 11:34:12
9000
发布2018-03-14 11:34:12
举报
文章被收录于专栏:杨建荣的学习笔记

在数据库遇到性能问题的时候,可能从io,cpu等角度能够下手找到性能瓶颈,日志的切换也是影响性能的一个因素,如果日志切换台频繁,等待时间就会在日志相关的事件上,从数据库的角度来说,肯定是io的瓶颈。 可以通过如下的shell脚本来得到一个简单的报告。

代码语言:javascript
复制
---脚本如下:
sqlplus -s $DB_CONN_STR@$SH_DB_SID<<EOF

set linesize    150
set pages       100
set feedback    off
set verify      off


col dbname      new_value dbname
col time_stamp  new_value time_stamp
col timestamp_np        noprint
col year_np             noprint
col month_np            noprint
col mon                 for a3
col day                 for a2


ttitle off
SELECT name dbname, substr(to_char(sysdate,'YYYY-Mon-DD HH24:MI:SS'),1,20)
       time_stamp
FROM v\$database
/


col bytes format 9,999,999,999,999
col member format a60


select group#,thread#,sequence#,members,bytes/1024/1024 size_MB,archived,status
from v\$log order by 1,2;


ttitle left "Redo Switch times per hour" center "&dbname" right "&time_stamp" 
set term on
col tps00 for 999 head "00"
col tps01 for 999 head "01"
col tps02 for 999 head "02"
col tps03 for 999 head "03"
col tps04 for 999 head "04"
col tps05 for 999 head "05"
col tps06 for 999 head "06"
col tps07 for 999 head "07"
col tps08 for 999 head "08"
col tps09 for 999 head "09"
col tps10 for 999 head "10"
col tps11 for 999 head "11"
col tps12 for 999 head "12"
col tps13 for 999 head "13"
col tps14 for 999 head "14"
col tps15 for 999 head "15"
col tps16 for 999 head "16"
col tps17 for 999 head "17"
col tps18 for 999 head "18"
col tps19 for 999 head "19"
col tps20 for 999 head "20"
col tps21 for 999 head "21"
col tps22 for 999 head "22"
col tps23 for 999 head "23"


SELECT *
FROM
( SELECT substr(year_np,1,8)  timestamp_np,
       substr(year_np,5,2) Mon, substr(year_np,7,2) Day,
       sum(decode(substr(year_np,9,2),'00',cnt,0))   tps00,
       sum(decode(substr(year_np,9,2),'01',cnt,0))   tps01,
       sum(decode(substr(year_np,9,2),'02',cnt,0))   tps02,
       sum(decode(substr(year_np,9,2),'03',cnt,0))   tps03,
       sum(decode(substr(year_np,9,2),'04',cnt,0))   tps04,
       sum(decode(substr(year_np,9,2),'05',cnt,0))   tps05,
       sum(decode(substr(year_np,9,2),'06',cnt,0))   tps06,
       sum(decode(substr(year_np,9,2),'07',cnt,0))   tps07,
       sum(decode(substr(year_np,9,2),'08',cnt,0))   tps08,
       sum(decode(substr(year_np,9,2),'09',cnt,0))   tps09,
       sum(decode(substr(year_np,9,2),'10',cnt,0))   tps10,
       sum(decode(substr(year_np,9,2),'11',cnt,0))   tps11,
       sum(decode(substr(year_np,9,2),'12',cnt,0))   tps12,
       sum(decode(substr(year_np,9,2),'13',cnt,0))   tps13,
       sum(decode(substr(year_np,9,2),'14',cnt,0))   tps14,
       sum(decode(substr(year_np,9,2),'15',cnt,0))   tps15,
       sum(decode(substr(year_np,9,2),'16',cnt,0))   tps16,
       sum(decode(substr(year_np,9,2),'17',cnt,0))   tps17,
       sum(decode(substr(year_np,9,2),'18',cnt,0))   tps18,
       sum(decode(substr(year_np,9,2),'19',cnt,0))   tps19,
       sum(decode(substr(year_np,9,2),'20',cnt,0))   tps20,
       sum(decode(substr(year_np,9,3),'21',cnt,0))   tps21,
       sum(decode(substr(year_np,9,3),'22',cnt,0))   tps22,
       sum(decode(substr(year_np,9,2),'23',cnt,0))   tps23
  FROM (SELECT to_char(first_time,'YYYYMMDDHH24') year_np,count(*) cnt
        FROM v\$log_history where first_time>sysdate -15
        GROUP BY to_char(first_time,'YYYYMMDDHH24')
       )      
  GROUP BY substr(year_np,1,8), substr(year_np,5,2), substr(year_np,7,2)
)
ORDER BY timestamp_np
/
<<EOF
exit;
本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2014-06-05,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 杨建荣的学习笔记 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
数据库
云数据库为企业提供了完善的关系型数据库、非关系型数据库、分析型数据库和数据库生态工具。您可以通过产品选择和组合搭建,轻松实现高可靠、高可用性、高性能等数据库需求。云数据库服务也可大幅减少您的运维工作量,更专注于业务发展,让企业一站式享受数据上云及分布式架构的技术红利!
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档