通过Real-Time SQL Monitoring来监视SQL语句




  • Oracle 11g数据库或更高版本具有一个新界面来监视长时间运行的SQL命令。该功能称为实时SQL监视。默认情况下,当SQL命令并行运行时,或者一次执行消耗了至少五秒钟的CPU或I / O时间时,将自动启动SQL监视。 MONITOR的SQL提示(hint)还可用于打开SQL语句的SQL监视。

    SQL监视要求:

    • 诊断和调整包许可证
    • STATISTICS_LEVEL参数将设置为 “TYPICAL” 或 “ALL”
    • CONTROL_MANAGEMENT_PACK_ACCESS参数设置为 “DIAGNOSTIC + TUNING”。

    实时SQL监视功能已获得诊断和调整包的许可。

    启动监视后,会将条目添加到动态性能V$SQL_MONITOR和V$SQL_PLAN_MONITOR视图。该条目跟踪为执行而收集的关键性能指标,包括运行时间,CPU时间,读写次数,I/O等待时间以及其他各种等待时间。这些统计信息在命令执行时几乎实时刷新,通常每秒一次。执行结束后,监视信息不会立即删除,而是会在V$SQL_MONITOR / V$SQL_PLAN_MONITOR视图中保留至少一分钟。该条目最终会被删除,因此可以在监视新命令时回收其空间。

     

    DBMS_SQLTUNE.REPORT_SQL_MONITOR

    REPORT_SQL_MONITOR函数用于返回特定SQL语句的SQL监视报告。 可以使用各种参数来标识SQL语句。
    该函数接受一些可选参数,最常见的参数是:

    • SQL_ID-感兴趣的查询的SQL_ID。 如果为NULL(默认值),则最后监视的语句为目标。
    • REPORT_LEVEL-报告中显示的信息量。
      基本允许值为“ NONE”,“ BASIC”,“ TYPICAL”或“ ALL”,
      默认值为“典型”,在大多数情况下足够了。
    • TYPE-用于显示报告的格式(“ TEXT”,“ HTML”,“ XML”或“ ACTIVE”)。
      “ ACTIVE”参数是Oracle 11g第2版中的新增参数,并使用HTML和Flash显示输出。 需要使用Internet连接才能使用“ ACTIVE”参数。
    • SESSION_ID-根据指定的SID定位查询的子集。
      在当前会话中使用SYS_CONTEXT('USERENV','SID')。
      默认值为NULL。

     

    以下是使用REPORT_SQL_MONITOR的示例:

    SET LONG 1000000
    SET LONGCHUNKSIZE 1000000
    SET LINESIZE 1000
    SET PAGESIZE 0
    SET TRIM ON
    SET TRIMSPOOL ON
    SET ECHO OFF
    SET FEEDBACK OFF

    SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => '<sql_id>', type => 'TEXT')
    AS report FROM dual;

     

     

    要概述V$SQL_MONITOR中的SQL命令,可以使用:

     

    SET LINESIZE 300
    COLUMN sql_text FORMAT A100
    SELECT sql_id, status, sql_text FROM v$sql_monitor;

     

    或者,您可以使用函数REPORT_SQL_MONITOR_LIST。
    对于上面的示例,这表明存在一个带有SQLID 0tqfh0cggfg0v的语句。
    现在我们可以使用以下命令获取报告:

    SET LONG 1000000
    SET FEEDBACK OFF
    spool monitor_sql.html
    SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id =>'0tqfh0cggfg0v',type=> 'HTML')
    AS report FROM dual;
    spool off

    选项为TEXT,HTML或ACTIVE。 对于并行查询,ACTIVE 选项是最佳选择。ACTIVE选项是在Oracle Datbase 11.2版本以后引入。

    注意:在下面的图像和/或文档内容中,所使用的用户信息和数据表示来自Oracle示例产品或Oracle数据库产品随附的公共文档中的虚拟数据。

     

    HTML的输出看起来像这样:

     

    TEXT的输出看起来像这样:
     
    ACTIVE的输出是这样的
    REPORT_SQL_MONITOR_LIST
    Oracle 11g第2版中添加了REPORT_SQL_MONITOR_LIST函数,以生成存储在V$SQL_MONITOR中的所有SQL的摘要。
    SET LONG 1000000
    SET LONGCHUNKSIZE 1000000
    SET LINESIZE 1000
    SET PAGESIZE 0
    SET TRIM ON
    SET TRIMSPOOL ON
    SET ECHO OFF
    SET FEEDBACK OFFSELECT DBMS_SQLTUNE.report_sql_monitor_list(type =>'TEXT',report_level => 'ALL') AS report FROM dual;
     SQL Monitoring 在 19c的版本中的增强
    由于数据库开发人员的主要工作是编写和调整SQL语句,因此,从Oracle Database 19c开始,Oracle允许数据库用户查看自己的实时SQL监视报告,而无需DBA特权或SELECT_CATALOG_ROLE。
    从Oracle Database 19c开始,Oracle Database包括未记录的V$视图,这些视图使数据库用户能够使用:
    无需SELECT_CATALOG_ROLE即可查看会话中执行的SQL和PL/SQL语句的计划和统计信息。
    没有SELECT_CATALOG_ROLE的用户将看不到其他用户的SQL执行统计信息和详细信息。
    新的V$视图集:
    V$ALL_SQL_MONITOR
    V$ALL_SQL_PLAN_MONITOR
    V$ALL_ACTIVE_SESSION_HISTORY
    V$ALL_SQL_PLAN
    主要场景如下:
    1.数据库用户可以生成和查看自己发布的SQL语句的SQL监视器报告,而无需授予任何其他特权。
    2.如果未授予用户SELECT_CATALOG_ROLE权限,则他们将无法生成和查看其他用户执行的SQL语句的SQL监视器报告。
    3.如果已授予用户SELECT_CATALOG_ROLE权限,则他们可以看到其他用户执行的SQL的SQL监视报告。
    您可以通过SQL*PLUS命令行通过DBMS_SQLTUNE.REPORT_SQL_MONITOR_LIST和DBMS_SQLTUNE.REPORT_SQL_MONITOR_LIST软件包或从Enterprise Manager(EM)生成和查看SQL监视报告,其大小仅为11g(请参阅文档ID 1229904.1和Doc ID 1380492.1)。
    测试用例:
    --1.create users
    conn / as sysdba--1-1.create Low-privileged users without DBA privilege
    --dep1 for SQL Statement using Tablesdrop user dep1 cascade;
    create user dep1 identified by <PASSWORD>;
    alter user dep1 quota unlimited on users;
    grant connect,resource to dep1;--2.prepare test data
    conn dep1/<PASSWORD>drop table testa_dep1;
    drop table testb_dep1;
    create table testa_dep1(c1 number, c2 char(100));
    create table testb_dep1(c1 number, c2 char(100));begin
    for i in 1 .. 200 loop
    for j in 1 .. 100 loop
    insert into testa_dep1 values(i,'A');
    commit;
    end loop;
    end loop;
    end;
    /begin
    for i in 1 .. 200 loop
    for j in 1 .. 100 loop
    insert into testb_dep1 values(i,'A');
    commit;
    end loop;
    end loop;
    end;
    /--2.Executing SQL and check SQL Monitor Active Report
    conn dep1/dep1select /*+ use_nl(a b) */ count(*)
    from testa_dep1 a, testb_dep1 b
    where a.c1=b.c1;

    --3.Generate and view SQL Monitor List and Active Report
    --should be able to view SQL monitor report of the SQL statements issued by user-self, without granting any additional privileges.
    --should not be able to view SQL monitor report of SQL statements issued by other users.

    --REPORT_SQL_MONITOR_LIST
    SET LONG 1000000
    SET LONGCHUNKSIZE 1000000
    SET LINESIZE 1000
    SET PAGESIZE 0
    SET TRIM ON
    SET TRIMSPOOL ON
    SET ECHO OFF
    SET FEEDBACK OFF
    spool monitor_list_sql_dep1_active.html
    SELECT DBMS_SQLTUNE.report_sql_monitor_list(type =>'ACTIVE',report_level => 'ALL') AS report FROM dual;
    spool off

    --REPORT_SQL_MONITOR
    set trimspool on
    set trim on
    set pages 0
    set linesize 1000
    set long 1000000
    set longchunksize 1000000
    spool monitor_sql_dep1.html
    select dbms_sqltune.report_sql_monitor(type=>'active') from dual;
    spool off

     

     

    注意:参考文档
    Reference:
    Oracle® Database SQL Tuning Guide 19c
    Generating and Accessing SQL Monitor Reports

    Speak Your Mind

    *