脚本：监控Oracle中正在运行的SQL
I
ITPUB约 3 分钟
ITPUB约 3 分钟一个输出的例子如下：col inst_sid heading "INST_ID|:SID" format a7col username format a10col machine format a12col sql_exec_start heading "SQL|START|D HH:MM:SS" format a11col sql_id format a13col sql_text format a40col event format a33col wait_sec heading "WAIT|(SEC)" format 99999set linesize 200select ses.inst_id||chr(58)||ses.sid as inst_sid,username,(sysdate - sql_exec_start) day(1) to second(0) as sql_exec_start,ses.sql_id,substr(sql.sql_text,1,40) sql_text,substr(case time_since_last_wait_microwhen 0 then (case wait_class when 'Idle' then 'IDLE: '||event else event end)else 'ON CPU'end,1,33) event,(case time_since_last_wait_microwhen 0 then wait_time_microelse time_since_last_wait_microend) /1000000 wait_secfrom gv$session ses,gv$sqlstats sqlwhere ses.inst_id||chr(58)||ses.sid <> sys_context ('USERENV','INSTANCE')||chr(58)||sys_context ('USERENV','SID')and username is not nulland status='ACTIVE'and ses.sql_id=sql.sql_id (+)order by sql_exec_start,username,ses.sid,ses.sql_id;
INST_SID USERNAME SQL_EXEC_START SQL_ID SQL_TEXT EVENT WAIT_SEC1:1699 YUAN +00 00:00:00.000000 4nq95bucaf3s1 select sum(l_extendedprice) / 7.0 as avg IDLE: PX Deq: Table Q Normal 0.0147541:730 YUAN +00 00:00:01.000000 04pfkq1nb6tu5 select ps_partkey, sum(ps_supplycost * p ON CPU 0.0281031:2909 YUAN +00 00:00:01.000000 2w6ykk7f8apgj select o_orderpriority, count(*) as orde IDLE: PX Deq: Table Q Normal 0.0133661:6778 YUAN +00 00:00:01.000000 04pfkq1nb6tu5 select ps_partkey, sum(ps_supplycost * p IDLE: PX Deq: Execution Msg 0.5371511:2061 YUAN +00 00:00:02.000000 dwr9nd8gqqrj4 select ps_partkey, sum(ps_supplycost * p IDLE: PX Deq Credit: need buffer 0.8289451:2180 YUAN +00 00:00:02.000000 2jnuqfkprzgya select o_year, sum(case when nation = 'U direct path read 0.0007011:6660 YUAN +00 00:00:03.000000 06pst1u6b434j select * from (select l_orderkey, sum(l_ IDLE: PX Deq Credit: need buffer 0.0001251:7021 YUAN +00 00:00:03.000000 dkhax46cjukju select nation, o_year, sum(amount) as su direct path read 0.0008371:1578 YUAN +00 00:00:05.000000 36vzwcqw6zr81 select * from (select c_name, c_custkey, IDLE: PX Deq: Execution Msg 0.5219071:2182 YUAN +00 00:00:14.000000 7bsgdav4drm1u select nation, o_year, sum(amount) as su IDLE: PX Deq: Execution Msg 8.0831471:1095 YUAN +00 00:00:19.000000 1n4x29ku1t0zj select * from (select s_name, count(*) a IDLE: PX Deq: Table Q Normal 0.00149411 rows selected.