脚本：监控Oracle中正在运行的SQL

数据运维 2024-04-26 Escape 手机阅读

这是一个监控Oracle中正在运行的SQL的脚本，有需要的请收藏，运行时直接复制和粘贴即可。

    col inst_sid heading "INST_ID|:SID" format a7
    col username format a10
    col machine format a12
    col sql_exec_start heading "SQL|START|D HH:MM:SS" format a11
    col sql_id format a13
    col sql_text format a40
    col event format a33
    col wait_sec heading "WAIT|(SEC)" format 99999
    set linesize 200


    select 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_micro
    when 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_micro
    when 0 then wait_time_micro
    else time_since_last_wait_micro
    end) 1000000 wait_sec
    from gv$session ses,gv$sqlstats sql
    where ses.inst_id||chr(58)||ses.sid sys_context ('USERENV','INSTANCE')||chr(58)||sys_context ('USERENV','SID')
    and username is not null
    and 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_SEC
      1:1699 YUAN +00 00:00:00.000000 4nq95bucaf3s1 select sum(l_extendedprice) 7.0 as avg IDLE: PX Deq: Table Q Normal 0.014754
      1:730 YUAN +00 00:00:01.000000 04pfkq1nb6tu5 select ps_partkey, sum(ps_supplycost * p ON CPU 0.028103
      1:2909 YUAN +00 00:00:01.000000 2w6ykk7f8apgj select o_orderpriority, count(*) as orde IDLE: PX Deq: Table Q Normal 0.013366
      1:6778 YUAN +00 00:00:01.000000 04pfkq1nb6tu5 select ps_partkey, sum(ps_supplycost * p IDLE: PX Deq: Execution Msg 0.537151
      1:2061 YUAN +00 00:00:02.000000 dwr9nd8gqqrj4 select ps_partkey, sum(ps_supplycost * p IDLE: PX Deq Credit: need buffer 0.828945
      1:2180 YUAN +00 00:00:02.000000 2jnuqfkprzgya select o_year, sum(case when nation = 'U direct path read 0.000701
      1:6660 YUAN +00 00:00:03.000000 06pst1u6b434j select * from (select l_orderkey, sum(l_ IDLE: PX Deq Credit: need buffer 0.000125
      1:7021 YUAN +00 00:00:03.000000 dkhax46cjukju select nation, o_year, sum(amount) as su direct path read 0.000837
      1:1578 YUAN +00 00:00:05.000000 36vzwcqw6zr81 select * from (select c_name, c_custkey, IDLE: PX Deq: Execution Msg 0.521907
      1:2182 YUAN +00 00:00:14.000000 7bsgdav4drm1u select nation, o_year, sum(amount) as su IDLE: PX Deq: Execution Msg 8.083147
      1:1095 YUAN +00 00:00:19.000000 1n4x29ku1t0zj select * from (select s_name, count(*) a IDLE: PX Deq: Table Q Normal 0.001494




      11 rows selected.

      欢迎关注我的公众号，一起学习数据库技术👇

      欢迎加我的微信，拉你进数据库微信群👇

      推荐文章👇

      国外的程序员没有35岁魔咒吗？

      试看号主的拙作《MySQL 8.0运维与优化》（清华大学出版社）

      托业890分的Oracle ACE为您翻译国际大佬的雄文（合集）

      又考了一个Oracle优化的OCP，交一下作业