• 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'));

  • 相关阅读:
    C# winform 学习(三)
    (Java实现) 均分纸牌
    (Java实现) 拦截导弹
    Delphi从内存流中判断图片格式(好多相关文章)
    Qt之QTableView显示富文本(使用了QAbstractTextDocumentLayout和QTextDocument)
    MAC和PHY的区别(网线上传递的是模拟信号)
    Qt之模型/视图(自定义按钮)(重绘QStyleOptionButton)
    QQ音乐的请求
    Log4j、Log4j 2、Logback、SFL4J、JUL、JCL的比较
    LRU Cache
  • 原文地址:https://www.cnblogs.com/feiyun8616/p/8044560.html
Copyright © 2020-2023  润新知