实例级别的监控,一直开启并且低开销:
创新互联公司成立于2013年,我们提供高端成都网站建设公司、成都网站制作、成都网站设计、网站定制、营销型网站、小程序设计、微信公众号开发、网站推广服务,提供专业营销思路、内容策划、视觉设计、程序开发来完成项目落地,为葡萄架企业提供源源不断的流量和订单咨询。
建立基础表:
create table sys.sesstat_history tablespace EOL as SELECT c.username, c.osuser, a.sid, c.serial#, c.paddr, c.process, c.logon_time, a.statistic#, b.name, a.value, SYSDATE AS logoff_timestamp FROM v$sesstat a, v$statname b, v$session c WHERE 1 = 2 create table sys.session_event_history tablespace EOL as SELECT b.sid, b.SERIAL#, b.username, b.osuser, b.paddr, b.process, b.logon_time, b.type, a.event, a.total_waits, a.total_timeouts, a.time_waited, a.average_wait, a.max_wait, SYSDATE AS logoff_timestamp FROM v$session_event a, v$session b WHERE 1 = 2
创建触发器:
CREATE OR REPLACE TRIGGER sys.logoff_trig BEFORE logoff ON DATABASE DECLARE logoff_sid PLS_INTEGER; logoff_time DATE := SYSDATE; BEGIN SELECT sid INTO logoff_sid FROM v$mystat WHERE rownum < 2; INSERT INTO sys.session_event_history (sid, serial#, username, osuser, paddr, process, logon_time, TYPE, event, total_waits, total_timeouts, time_waited, average_wait, max_wait, logoff_timestamp) SELECT b.sid, b.serial#, b.username, b.osuser, b.paddr, b.process, b.logon_time, b.type, a.event, a.total_waits, a.total_timeouts, a.time_waited, a.average_wait, a.max_wait, SYSDATE AS logoff_timestamp FROM v$session_event a, v$session b WHERE a.sid = b.sid AND b.username = login_user AND b.sid = logoff_sid; INSERT INTO sys.sesstat_history (username, osuser, sid, serial#, paddr, process, logon_time, statistic#, NAME, VALUE, logoff_timestamp) SELECT c.username, c.osuser, a.sid, c.serial#, c.paddr, c.process, c.logon_time, a.statistic#, b.name, a.value, logoff_time FROM v$sesstat a, v$statname b, v$session c WHERE a.statistic# = b.statistic# AND a.sid = c.sid AND b.name IN ('CPU used where call started', 'CPU used by this session', 'recursive cpu usage', 'parse time cpu') AND c.sid = logoff_sid AND c.username = login_user; END;
查询消耗CPU的等待事件排名:
SQL> SELECT * FROM (SELECT a.sid, a.serial#, a.event, a.total_waits FROM session_event_history a ORDER BY a.time_waited DESC) WHERE rownum < 100; 2 3 4 5 SID SERIAL# EVENT TOTAL_WAITS ---------- ---------- ------------------------------------------------------------ ----------- 1858 8391 SQL*Net message from client 147692 1437 52565 SQL*Net message from client 34305 584 52801 SQL*Net message from client 85105 585 40229 SQL*Net message from client 163331 874 3263 SQL*Net message from client 77519 1285 21797 SQL*Net message from client 19041 861 25015 SQL*Net message from client 194 726 9275 SQL*Net message from client 66724 1717 1935 SQL*Net message from client 92394 1014 883 SQL*Net message from client 34455 21 10841 SQL*Net message from client 28685
名称栏目:使用DATABASELogoff收集oracle等待事件信息
分享地址:/article30/iioiso.html
成都网站建设公司_创新互联,为您提供关键词优化、、面包屑导航、网站收录、虚拟主机、软件开发
声明:本网站发布的内容(图片、视频和文字)以用户投稿、用户转载内容为主,如果涉及侵权请尽快告知,我们将会在第一时间删除。文章观点不代表本网站立场,如需处理请联系客服。电话:028-86922220;邮箱:631063699@qq.com。内容未经允许不得转载,或转载时需注明来源: 创新互联