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)