ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 用存储过程封装awrrpt脚本(一)

用存储过程封装awrrpt脚本(一)

原创 Linux操作系统 作者:yangtingkun 时间:2011-03-18 23:57:13 0 删除 编辑

做了一个存储过程,封装了awrreport的功能。

 

 

通过输入指定时间点和目录名称,就可以在指定的位置生成awrhtml报告,对于RAC环境会同时生成所有节点的awr报告。

SQL> CREATE OR REPLACE PROCEDURE P_AWR_REPORT (P_BEGIN IN DATE, P_END IN DATE, D_DIR IN VARCHAR2) AS
  2   TYPE T_VARCHAR IS TABLE OF VARCHAR2(1500 CHAR) INDEX BY BINARY_INTEGER;
  3   V_REPORT T_VARCHAR;
  4   V_FILE UTL_FILE.FILE_TYPE;
  5   TYPE T_NUM IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  6   V_INSTANCE T_NUM;
  7   V_SNAP_BEG T_NUM;
  8   V_SNAP_END T_NUM;
  9   V_DBID NUMBER;
 10   V_DBNAME V$DATABASE.NAME%TYPE;
 11  BEGIN
 12   SELECT DBID, NAME
 13   INTO V_DBID, V_DBNAME
 14   FROM V$DATABASE;
 15  
 16   SELECT INSTANCE_NUMBER, MAX(SNAP_ID)
 17   BULK COLLECT INTO V_INSTANCE, V_SNAP_BEG
 18   FROM DBA_HIST_SNAPSHOT
 19   WHERE END_INTERVAL_TIME <= P_BEGIN
 20   AND DBID = V_DBID
 21   GROUP BY INSTANCE_NUMBER
 22   ORDER BY INSTANCE_NUMBER;
 23   SELECT DISTINCT INSTANCE_NUMBER,
 24    FIRST_VALUE(SNAP_ID) OVER(PARTITION BY INSTANCE_NUMBER ORDER BY ABS(CAST (END_INTERVAL_TIME AS DATE) - P_BEGIN))
 25   BULK COLLECT INTO V_INSTANCE, V_SNAP_BEG
 26   FROM DBA_HIST_SNAPSHOT
 27   WHERE DBID = V_DBID
 28   ORDER BY INSTANCE_NUMBER;
 29  
 30  
 31   FOR I IN V_INSTANCE.FIRST..V_INSTANCE.LAST LOOP
 32    BEGIN
 33     SELECT DISTINCT FIRST_VALUE(SNAP_ID) OVER(ORDER BY ABS(CAST (END_INTERVAL_TIME AS DATE) - P_END))
 34     INTO V_SNAP_END(I)
 35     FROM DBA_HIST_SNAPSHOT
 36     WHERE SNAP_ID > V_SNAP_BEG(I)
 37     AND DBID = V_DBID
 38     AND INSTANCE_NUMBER = V_INSTANCE(I);
 39    EXCEPTION
 40     WHEN NO_DATA_FOUND THEN
 41      V_INSTANCE.DELETE(I);
 42    END;
 43   END LOOP;
 44  
 45   IF V_INSTANCE.COUNT = 1
 46   THEN
 47    V_FILE := UTL_FILE.FOPEN(
 48     D_DIR,
 49     'awr_' || V_DBNAME || '_' || V_INSTANCE(1) || '_' || V_SNAP_BEG(1) || '_' || V_SNAP_END(1) || '.html',
 50     'w',
 51     32767);
 52  
 53    SELECT OUTPUT
 54    BULK COLLECT INTO V_REPORT
 55    FROM TABLE(
 56     DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
 57      V_DBID,
 58      V_INSTANCE(1),
 59      V_SNAP_BEG(1),
 60      V_SNAP_END(1),
 61      0));
 62    FOR I IN 1..V_REPORT.COUNT LOOP
 63     UTL_FILE.PUT_LINE(V_FILE, V_REPORT(I));
 64    END LOOP;
 65    UTL_FILE.FCLOSE(V_FILE);
 66   ELSE
 67    FOR I IN V_INSTANCE.FIRST..V_INSTANCE.LAST LOOP
 68     V_FILE := UTL_FILE.FOPEN(
 69      D_DIR,
 70      'awr_' || V_DBNAME || '_' || V_INSTANCE(I) || '_' || V_SNAP_BEG(I) || '_' || V_SNAP_END(I) || '.html',
 71      'w',
 72      32767);
 73  
 74     SELECT OUTPUT
 75     BULK COLLECT INTO V_REPORT
 76     FROM TABLE(
 77      DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
 78       V_DBID,
 79       V_INSTANCE(I),
 80       V_SNAP_BEG(I),
 81       V_SNAP_END(I),
 82       0));
 83     FOR I IN 1..V_REPORT.COUNT LOOP
 84      UTL_FILE.PUT_LINE(V_FILE, V_REPORT(I));
 85     END LOOP;
 86     UTL_FILE.FCLOSE(V_FILE);
 87    END LOOP;
 88   END IF;
 89  END;
 90  /

过程已创建。

创建一个目录,就可以运行这个过程:

SQL> CREATE DIRECTORY D_OUTPUT AS 'D:\SQL';

目录已创建。

SQL> BEGIN
  2  P_AWR_REPORT(TO_DATE('2010-12-31 10', 'YYYY-MM-DD HH24'),
  3  TO_DATE('2010-12-31 11', 'YYYY-MM-DD HH24'),
  4  'D_OUTPUT');
  5  END;
  6  /

PL/SQL 过程已成功完成。

检查对应的目录,可以看到,report文件已经创建:

D:\>cd sql

D:\sql>dir
 
驱动器 D 中的卷没有标签。
 
卷的序列号是 F4E9-218A

 D:\sql 的目录

2011/01/04  23:40   

          .
2011/01/04  23:40   
          ..
2011/01/04  23:40           233,586 awr_YTK102_1_118_119.html
               1
个文件        233,586 字节
               2
个目录  8,983,412,736 可用字节

D:\sql>type awr_ytk102_1_118_119.html|more
AWR Report for DB: YTK102, Inst: ytk102, Snaps: 118-119</TITL<BR>E><style. type="text/css">body.awr {font:bold 10pt Arial,Helvetica,Geneva,sans-se<BR>rif;color:black; background:White;}<BR>pre.awr  {font:8pt Courier;color:black; background:White;}h1.awr   {font:bold 20<BR>pt Arial,Helvetica,Geneva,sans-serif;color:#336699;background-color:White;border<BR>-bottom:1px solid #cccc99;margin-top:0pt; margin-bottom:0pt;padding:0px 0px 0px<BR>0px;}<BR>h2.awr   {font:bold 18pt Arial,Helvetica,Geneva,sans-serif;color:#336699;backgro<BR>und-color:White;margin-top:4pt; margin-bottom:0pt;}<BR>h3.awr {font:bold 16pt Arial,Helvetica,Geneva,sans-serif;color:#336699;backgroun<BR>d-color:White;margin-top:4pt; margin-bottom:0pt;}li.awr {font: 8pt Arial,Helveti<BR>ca,Geneva,sans-serif; color:black; background:White;}<BR>th.awrnobg {font:bold 8pt Arial,Helvetica,Geneva,sans-serif; color:black; backgr<BR>ound:White;padding-left:4px; padding-right:4px;padding-bottom:2px}th.awrbg {font<BR>:bold 8pt Arial,Helvetica,Geneva,sans-serif; color:White; background:#0066CC;pad<BR>ding-left:4px; padding-right:4px;padding-bottom:2px}<BR>td.awrnc {font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;background:Whit<BR>e;vertical-align:top;}<BR>td.awrc    {font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;background:#F<BR>FFFCC; vertical-align:top;}a.awr {font:bold 8pt Arial,Helvetica,sans-serif;color<BR>:#663300; vertical-align:top;margin-top:0pt; margin-bottom:0pt;}<BR></style></HEAD><BODY class='awr'><BR>WARNING: Since the DB Time is less than one second, there was<BR>         minimal foreground activity in the snapshot period.<BR>.<BR>.<BR>.</FONT></SPAN></P> <P style="TEXT-INDENT: 21pt; MARGIN: 0cm 0cm 0pt" class=MsoNormal><SPAN style="FONT-FAMILY: 宋体; mso-ascii-theme-font: minor-fareast; mso-fareast-font-family: 宋体; mso-fareast-theme-font: minor-fareast; mso-hansi-theme-font: minor-fareast" lang=EN-US><FONT size=3> </FONT></SPAN></P> <P style="TEXT-INDENT: 21pt; MARGIN: 0cm 0cm 0pt" class=MsoNormal><SPAN style="FONT-FAMILY: 宋体; mso-ascii-theme-font: minor-fareast; mso-fareast-font-family: 宋体; mso-fareast-theme-font: minor-fareast; mso-hansi-theme-font: minor-fareast" lang=EN-US><FONT size=3> </FONT></SPAN></P></DIV> </div> <p style="clear:both;"></p> <p class="translate"> 来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-689953/,如需转载,请注明出处,否则将追究法律责任。 </p> </div> </div> <div class="preview-footer clearfix"> <!--已点赞、收藏--> <div class="icon-praise" id="praise" onclick="blogObj.praise($(this),'/praise/')"><span class="text"></span><span class="num">0</span></div> <div class="icon-collect" id="collect" onclick="blogObj.praise($(this),'/favour/')"><span class="text"></span><span class="num">0</span></div> <div class="share-group fr"> <div class="bdsharebuttonbox bdshare-button-style0-16" data-bd-bind="1522216707615"> <span class="fl">分享到:</span> <a href="javascript:;" class="bds_tsina" data-cmd="tsina" title="分享到新浪微博"></a> <a href="javascript:;" class="bds_sqq" data-cmd="sqq" title="分享到QQ好友"></a> <a href="javascript:;" class="bds_qzone" data-cmd="qzone" title="分享到QQ空间"></a> <a href="javascript:;" class="bds_weixin" data-cmd="weixin" title="分享到微信"></a> </div> <script> window._bd_share_config = { "common": { "bdSnsKey": {}, "bdText": "", "bdDesc": "", "bdMini": "", "bdMiniList": false, "bdPic": "", "bdStyle": "0", "bdSize": "32" }, "share": {"bdSize": 16} }; with (document) 0[(getElementsByTagName('head')[0] || body).appendChild(createElement('script')).src = 'http://bdimg.share.baidu.com/static/api/js/share.js?v=89860593.js?cdnversion=' + ~(-new Date() / 36e5)]; if ($(window).width() < 1320) { if ($(window).width() < 1200) { $(".sideslip").css({ "left": "20px", "margin-left": 0 }) } else { $(".sideslip").css({ "margin-left": "-590px" }) } }</script> </div> </div> <div class="article-context"> <div class="fl"> <span class="color77">上一篇:</span> <a href="http://blog.itpub.net/4227/viewspace-689817/">ORA-600(krvxbpx20)错误</a> </div> <div class="fr"> <span class="color77">下一篇:</span> <a href="http://blog.itpub.net/4227/viewspace-690002/">用存储过程封装awrrpt脚本(二)</a> </div> </div> <input type="hidden" id="blogId" value="689953"> <!--评论开始--> <div class="blog-comment"> <div class="new-comment"> <input type="hidden" id="hid" name="hid" value="689953"> <input type="hidden" name="_token" value="XfzmhJnPQpnw5jT79RIRWrsgEMHassSn2j1AO1bd"> <a class="avatar" href="javascript:;"> <img src="http://blog.itpub.net/images/user_pic_default.png"></a> <!--用户未登录--> <div class="sign-container"> <span>请登录后发表评论</span> <a class="btn btn-sign"> <a class="layui-btn ml30" onclick="blog.login(event)">登录</a> </a> </div> <!--用户已登录--> <textarea placeholder="请写下你的评论…" onfocus="blogObj.textareaFocus($(this),200)" maxlength=200></textarea> </div> <div class="comment-list"> <div class="top-title"><span class="all-comment">全部评论</span> <span class="colorbb ml20"></span> </div> <div class="comment-items" id="comment_items"> <script type="text/template" id='blog_template'> <%for(var i=0;i <items.length;i++){%> <div class="comment-item"> <div class="author"> <a target="_blank" href="/<%=items[i].uid%>" class="avatar"> <img src="<%=items[i].headimg%>" alt=""> </a> <div class="info"> <div class="name"><a target="_blank" href="/<%=items[i].uid%>"><%=items[i].username%></a> <span class="fr reply-btn" onclick="blogObj.newComment($(this),'<%=items[i].username%>',<%=items[i].id%>)">回复</span></div> <div class="time colorbb"><%=items[i].createtime%></div> </div> </div> <p class="comment-wrap"><%=items[i].content%></p> <%if(items[i].items.items.length) { %> <div class="sub-comment-list"> <%for(var j=0;j<items[i].items.items.length;j++){%> <div class="sub-comment"> <div class="color77 time-reply"> <span class="time"><%=items[i].items.items[j].createtime%></span> <span class="reply-btn fr" onclick="blogObj.newComment($(this),'<%=items[i].items.items[j].username%>',<%=items[i].items.items[j].id%>)">回复</span> </div> <p><a href="/<%=items[i].uid%>"><%=items[i].items.items[j].username%></a>   回复   <a href="/<%=items[i].touid%>"><%=items[i].items.items[j].tousername%></a>: <span class="content"><%=items[i].items.items[j].content%></span></p> </div> <%}%> <%if(items[i].items.total > 5) { %> <div class="more-comment"><span class="sub-comment-count">还有<i class="count"><%=items[i].items.total-5%></i>条评论</span><span class="more-comment-btn" onclick=blogObj.loadSubComment($(this),<%=items[i].id%>) data-count=1 data-flag=true>点击查看</span></div> <%}%> </div> <%}%> </div> <%}%> </script> </div> </div> </div> <!--评论结束--> </div> <script> </script> <div class="fr w290"> <!--作者信息开始--> <!--作者信息开始--> <div class="author-info right-fixed "> <div class="icon-expert"></div> <div class="head-img"> <a href="http://blog.itpub.net/4227/"><img src="http://account.itpub.net/api/avatar.php?uid=4227" alt=""></a> </div> <div class="author-name"><a href="http://blog.itpub.net/4227/">yangtingkun</a></div> <div class="author-intro"> 暂无介绍 </div> <p class="register-time"><span class="color77">注册时间:</span>2007-12-29</p> <ul class="tree-list clearfix"> <li> <div class="item-tt">博文量</div> <a href="http://blog.itpub.net/4227/"><span class="item-num blognum">1954</span></a> </li> <li> <div class="item-tt">访问量</div> <div class="item-num blogviewnum">10636271</div> </li> </ul> </div> <!--作者信息结束--> <script> var data = {uid: 4227}; $._ajax({ url: '/getAuthorInfo/', data: data, type: 'get', dataType: 'json', success: function (data) { if (data.code == 200) { var data = data.data; $(".blognum").text(data.blognum); $(".blogviewnum").text(data.visitednum); } } }); </script> <!--作者信息结束--> <!--博文推荐开始--> <div class="blog-choice right-fixed"> <h3 class="choice-title">最新文章</h3> <ul class="newul"> <li class="new-item"> <a href="http://blog.itpub.net/4227/viewspace-1060809/" class="clearfix"> 20130725 上海OOW第四日 </a> </li> <li class="new-item"> <a href="http://blog.itpub.net/4227/viewspace-1060793/" class="clearfix"> 20130724 上海OOW第三日 </a> </li> <li class="new-item"> <a href="http://blog.itpub.net/4227/viewspace-1060792/" class="clearfix"> 20130723 上海OOW第二日 </a> </li> <li class="new-item"> <a href="http://blog.itpub.net/4227/viewspace-1060790/" class="clearfix"> 20130722 上海OOW开幕 </a> </li> <li class="new-item"> <a href="http://blog.itpub.net/4227/viewspace-1060789/" class="clearfix"> 20130721 OOW媒体发布会 </a> </li> <li class="new-item"> <a href="http://blog.itpub.net/4227/viewspace-1060788/" class="clearfix"> 密码延迟验证导致的系统HANG住 </a> </li> <li class="new-item"> <a href="http://blog.itpub.net/4227/viewspace-1060787/" class="clearfix"> IP地址被清空导致实例重启 </a> </li> <li class="new-item"> <a href="http://blog.itpub.net/4227/viewspace-1060762/" class="clearfix"> ORA-7445(kjbcrcomplete)错误 </a> </li> <li class="new-item"> <a href="http://blog.itpub.net/4227/viewspace-1060754/" class="clearfix"> Streams AQ: qmn coordinator waiting for slave to start等待 </a> </li> <li class="new-item"> <a href="http://blog.itpub.net/4227/viewspace-1060746/" class="clearfix"> ORA-600(13310)错误 </a> </li> <ul> </div> <!--大牛精选结束--> </div> </div> <div class="icon-back-top" onclick="$('html,body').animate({scrollTop:0},'slow');"></div> <!--main部分结束--> <!--百度推送--> <script> (function(){ var bp = document.createElement('script'); var curProtocol = window.location.protocol.split(':')[0]; if (curProtocol === 'https') { bp.src = 'https://zz.bdstatic.com/linksubmit/push.js'; } else { bp.src = 'http://push.zhanzhang.baidu.com/push.js'; } var s = document.getElementsByTagName("script")[0]; s.parentNode.insertBefore(bp, s); })(); </script> <!--2020-10-24 22:31:47--> <!--footer部分开始--> <div class="blog-footer"> <div class="w1200 pr"> <div class="footer-links"> <a class="icon-blog icon-wx mr30"> <div class="wx-qrcode"> <img src="http://edu.itpub.net/images/qrcode.jpg" alt=""> </div> </a> <a href="https://weibo.com/itpub2001?from=myfollow_all" class="icon-blog icon-wb" target="_blank"></a> </div> <p class="footer-nav"> <a href="http://www.it168.com/bottomfile/it168.shtml" target="_blank">支持我们</a> <a href="http://www.it168.com/bottomfile/tgzn.shtml" target="_blank">作者招募</a> <a href="http://www.it168.com/bottomfile/sytk.shtml" target="_blank">用户协议</a> <a href="http://blog.itpub.net/31509949/viewspace-2157750/" target="_blank">FAQ</a> <a href="http://edu.itpub.net/contactus.html" target="_blank">Contact Us</a> <script src="https://s22.cnzz.com/z_stat.php?id=1274521965&web_id=1274521965" language="JavaScript"></script> </p> <p>北京盛拓优讯信息技术有限公司. 版权所有  <a style="color:#777777;" target="_blank" href="http://beian.miit.gov.cn">京ICP备09055130号-4</a>  北京市公安局海淀分局网监中心备案编号:11010802021510</p> <p>广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员</p> </div> </div> <!--footer部分结束--> <script> var _hmt = _hmt || []; (function() { var hm = document.createElement("script"); hm.src = "https://hm.baidu.com/hm.js?5016281862f595e78ffa42f085ea0f49"; var s = document.getElementsByTagName("script")[0]; s.parentNode.insertBefore(hm, s); })(); </script> <!-- END STAT PV --> </body> </html>