• sql-monitore 的bug 。


    http://www.mamicode.com/info-detail-1659243.html

    存储过程无法做 sql -monitor ,

    而存储过程跑的sql (只能通过awr 报告来看sql_id),则也不 可以做sql-monitor.

    因为 以下语句只能看到 存储过程的sql_id.

    SELECT status, KEY, SID, sql_id, elapsed_time, cpu_time, fetches, buffer_gets,disk_reads FROM v$sql_monitor where status=‘EXECUTING‘ 

    1.那么及时执行 但是可以从v$sql_monitor看到相关的信息,于是想生成一个sql monitor report:,也无法生成存储过程的sql-monitor ,

    SQL> SELECT dbms_sqltune.report_sql_monitor(

    sql_id => ‘cygs51q4a5tm3‘,

    report_level => ‘ALL‘,

    type=>‘TEXT‘)  from dual;

    DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_ID=>‘CYGS51Q4A5TM3‘,REPORT_LEVEL=>‘ALL‘,TYPE=>‘TEXT‘)

    -----------------------------------------------------------------------------------------------------------

    SQL Monitoring Report

    生成的是空的,试了好几次,还把text格式改成html格式,也是空的。

    select sql.sql_text sql_text,
    t.USED_UREC Records,
    t.USED_UBLK Blocks,
    (t.USED_UBLK * 8192 / 1024) KBytes
    from v$transaction t, v$session s, v$sql sql
    where t.addr = s.taddr
    and s.sql_id = sql.sql_id
    order by t.used_ublk desc;

     2.而存储过程跑的sql (只能通过awr 报告来看sql_id),则也不 可以做sql-monitor. 要1的结果来跑,仍然是空的,只能使用 抓cursor 方法获取。也算是个 sql-monitor 的Bug. 

    SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('cygs51q4a5tm3', format=>'ALL'));   

    或者通过SQLHC 来进行收集。

    ##############

    SELECT A.USERNAME,
    A.SID,
    A.SERIAL#,
    A.OPNAME,
    A.TARGET,
    A.START_TIME,
    A.LAST_UPDATE_TIME,
    C.OSUSER,
    C.MACHINE,
    C.PROGRAM,
    ROUND (A.SOFAR * 100 / TOTALWORK, 0) || '%' AS JINDU,
    A.TIME_REMAINING,
    A.ELAPSED_SECONDS,
    B.SQL_TEXT
    FROM GV$SESSION_LONGOPS A,
    GV$SQL B,
    GV$SESSION C
    WHERE A.SQL_ADDRESS = B.ADDRESS
    AND A.SQL_HASH_VALUE=B.HASH_VALUE
    AND A.SID = C.SID
    AND C.STATUS = 'ACTIVE'

    SELECT A.USERNAME,
    A.SID,
    A.SERIAL#,
    A.OPNAME,
    A.TARGET,
    A.START_TIME,
    A.LAST_UPDATE_TIME,
    C.OSUSER,
    C.MACHINE,
    C.PROGRAM,
    ROUND(A.SOFAR * 100 / TOTALWORK, 0) || '%' AS JINDU,
    A.TIME_REMAINING,
    A.ELAPSED_SECONDS,
    B.SQL_TEXT
    FROM GV$SESSION_LONGOPS A, GV$SQL B, GV$SESSION C
    WHERE A.SQL_ADDRESS = B.ADDRESS
    AND A.SQL_HASH_VALUE = B.HASH_VALUE
    AND A.SID = C.SID
    AND C.STATUS = 'ACTIVE'
    AND C.username='IGRSVISE'

    col sql_id format a20
    col key format 99999999999999999
    SELECT status, KEY, SID, sql_id, elapsed_time, cpu_time, fetches, buffer_gets,disk_reads FROM v$sql_monitor where status='EXECUTING';


    select plan_line_id, plan_operation, plan_options starts, output_rows
    from v$sql_plan_monitor
    where key=193273569471;


    SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('cygs51q4a5tm3', format=>'ALL'));

  • 相关阅读:
    Reflections 0.9.12 Caused by: java.lang.IllegalStateException: zip file closed
    关于使用GATEWAY后静态资源失效问题
    Gbase8.7 和shardingsphere4.1.1适配
    统信UOS笔记(V20)
    mysql笔记
    axios 设置全局取消请求
    arr 转 tree
    云知声 面试题
    百度 二面 99 乘法表
    百度 无人车 面试
  • 原文地址:https://www.cnblogs.com/feiyun8616/p/8044560.html
Copyright © 2020-2023  润新知