• 确定最耗CPU用户、语句


    SET line 800
    SET pages 100
    col sid format 9990
    col SERIAL# format 999990
    col top_io_machine format a8
    col top_io_program format a10
    col top_io_sql format a50
    SET colsep |


    SELECT /*+ rule */
     aa.sid      AS "sid",
     aa.serial#  AS "SERIAL#",
     aa.VALUE    AS "CPU",
     bb.VALUE    AS "MEN-PGA",
     cc.VALUE    AS "I/O-Phy-read",
     cc.machine  AS top_io_machine,
     cc.program  AS top_io_program,
     cc.sql_text AS top_io_sql
      FROM (SELECT d.sql_text,   c.machine,
                   c.program,    c.sid,
                   c.serial#,    a.NAME,
                   b.VALUE
              FROM v$statname a, v$sesstat b, v$session c, v$sqlarea d
             WHERE NAME = 'CPU used by this session'
               AND a.statistic# = b.statistic#
               AND c.sid = b.sid
               AND c.status = 'ACTIVE'
               AND d.address = c.sql_address) aa,
           (SELECT d.sql_text,   c.machine,
                   c.program,    c.sid,
                   c.serial#,    a.NAME,
                   b.VALUE
              FROM v$statname a, v$sesstat b, v$session c, v$sqlarea d
             WHERE NAME = 'session pga memory'
               AND a.statistic# = b.statistic#
               AND c.sid = b.sid
               AND c.status = 'ACTIVE'
               AND d.address = c.sql_address) bb,
           (SELECT d.sql_text,   c.machine,
                   c.program,    c.sid,
                   c.serial#,    a.NAME,
                   b.VALUE
              FROM v$statname a, v$sesstat b, v$session c, v$sqlarea d
             WHERE NAME = 'physical reads'
               AND a.statistic# = b.statistic#
               AND c.sid = b.sid
               AND c.status = 'ACTIVE'
               AND d.address = c.sql_address) cc
     WHERE aa.sid = bb.sid
       AND aa.sid = cc.sid
       AND aa.serial# = bb.serial#
       AND aa.serial# = cc.serial#
     ORDER BY 5 ASC, 4 ASC, 3 ASC

  • 相关阅读:
    javascript 事件冒泡
    Java 理论与实践: 正确使用 Volatile 变量
    Concurrency,Java 并发
    POJ2379 ACM Rank Table 模拟题
    HDU1711Number Sequence KMP
    POJ1061 青蛙的约会 扩展GCD
    HDU2523 SORT AGAIN HASH
    HDU2087剪花布条 KMP
    HDU3736 Cyclic Nacklace KMP
    HDU1709The Balance 母函数
  • 原文地址:https://www.cnblogs.com/jasonsfu/p/1156053.html
Copyright © 2020-2023  润新知