Use shell to monitor long time query
来源:互联网 发布:淘宝店铺模块 编辑:程序博客网 时间:2024/06/05 00:43
In some datawarehouse project, some query may run a very long time; Also in OLTP project, the user may keep blocked and hang in database. The purpose of this article is to identify all the query running a long time.
--a container
CREATE TABLE long_query_log(sid NUMBER, serial# NUMBER, osuser VARCHAR2(30), sql_text VARCHAR2(1000));
CREATE INDEX i_osuser_idx ON long_query_log(osuser);
CREATE INDEX i_sqltext_idx ON long_query_log(sql_text);
--shell scripts
#Env part
CONNECT_STR=customer/cusd2t@wmomsd2; export CONNECT_STR
LOG_DIR=/home/rxyu/logs; export LOG_DIR
LOG_FILE=$LOG_DIR/monitor/monitor_rxyu_`date '+%Y_%m_%d'`.log; export LOG_FILE
REPORT_FILE=$LOG_DIR/reports/report_rxyu_`date '+%Y_%m_%d'`.lst; export REPORT_FILE
#app body
sqlplus -s $CONNECT_STR <<EOF >>$LOG_FILE
TRUNCATE TABLE long_query_log;
MERGE INTO long_query_log orig
USING (SELECT a.sid, a.serial#, a.osuser, b.sql_text
FROM v/$session a, v/$sqlarea b
WHERE a.sql_address = b.address
AND a.username = 'CUSTOMER'
AND a.osuser = 'rxyu') get
ON (orig.sid = get.sid AND orig.serial# = get.serial#)
WHEN NOT MATCHED THEN
INSERT (sid, serial#, osuser, sql_text)
VALUES
(get.sid, get.serial#, get.osuser, get.sql_text);
set serveroutput on
set linesize 150
column sid format 999999
column serial format 999999
column osuser format A10
column sql_text format A100 wrapped
spool $REPORT_FILE
select * from long_query_log;
spool off
EOF
--after work
--From DB
SELECT * FROM long_query_log;
--From file of linux
~>less ~/logs/reports/report_rxyu_2010_06_03.lst
SID SERIAL# OSUSER SQL_TEXT
------- ---------- ---------- ----------------------------------------------------------------------------------------------------
262 2559 rxyu select * from wcu_line_item a, wcu_line_item b
291 64065 rxyu MERGE INTO long_query_log orig USING (SELECT a.sid, a.serial#, a.osuser, b.sql_text FROM v$
session a, v$sqlarea b WHERE a.sql_address = b.address AND a.username = 'CUSTOMER'
AND a.osuser = 'rxyu') get ON (orig.sid = get.sid AND orig.serial# = get.serial#) WHEN NO
T MATCHED THEN INSERT (sid, serial#, osuser, sql_text) VALUES (get.sid, get.serial#, get.osu
ser, get.sql_text)
- Use shell to monitor long time query
- long running query monitor with last_call_et
- How to use time...
- Time of check to time of use
- how-to-use-grahite-and-grafana-to-monitor-spark
- Use Shell to Insert Oracle
- How to use the Automatic Database Diagnostic Monitor (ADDM)
- use tptp to monitor java application performance (1)
- Use Prometheus+Grafana to monitor the Internet backbone connectivity quality
- Use ELK stack to monitor syslog from network devices
- How To Use the C Run-Time
- Use gprof to visualize code running time
- Use explicit instantiation to minimize compile time and link time;
- Ten Ways To Say Long Time No See
- sudo command last a long time to execute
- Why Django paginator take long time to page?
- how to use the .htaccess shell attack
- [bash] how to use bash-shell array
- 非.aspx/.aspx.cs 中引用Response,Request等使用类
- BREW资源BAR文件格式分析
- jquery.autocomplete.js用法(转载)
- U盘光盘启动PE安装Xp系统的200%成功经验 分享
- 消息的变迁
- Use shell to monitor long time query
- IsMultiThread
- ubuntu/linux flash中文乱码 的解决
- 正则在JS中的使用
- atl 组件应用程序使用的注意事项
- OnClientClick 不执行的两种常见问题
- 正则在JS中的使用
- 非线性方程求解
- 正则在JS中的使用