SQL performance Real-time SQL monitoring feature (real-time SQL monitoring)

• • Overview
  • Conditions of Use
  • Monitoring objects
  • Ways to view real-time SQL monitoring results
    • The following sub-packages for the Dbms_sqltune package
    • Dynamic View
    • Enterprise Managerem
  • Related parameters
  • Changes in each version
  • Examples of real-time SQL monitoring use
  • Reference

Overview

The real-time SQL monitoring feature (real-time SQL monitoring) is a feature of oracle11g that enables real-time monitoring of SQL performance in execution.

Conditions of Use

To use the real-time SQL monitoring feature (real-time SQL monitoring), you must meet several conditions

Monitoring objects

?并行执行的SQL文?消耗的CPU时间或I/O时间超过5秒的串行执行的SQL文?指定/*+ MONITOR*/ Hint的SQL（也通过/*+ NO_MONITOR */ Hint，来使某些SQL为监视对象外）

How to view real-time SQL monitoring results The following sub-packages for 1.dbms_sqltune packages

REPORT_SQL_MONITOR       ：实时SQL监控报告REPORT_SQL_MONITOR_LIST  ：（11.2以后）用于显示概要信息，同V$SQL_MONITOR的内容。

Reference:
Database PL/SQL Packages and Types Reference
>140 Dbms_sqltune
>>real-time SQL Monitoring Subprograms

2. Dynamic View

Real-time SQL monitoring can be done directly by querying related dynamic views.

V$SQL_MONITOR      ：实时SQL监控全体概要信息V$SQL_PLAN_MONITOR ：SQL的执行计划信息

Database Reference
>v$sql_monitor
>v$sql_plan_monitor

3.Enterprise Manager (EM)

Real-time SQL monitoring can be done via EM or EM Cloud control.

Related parameters

We can learn more about real-time SQL monitoring capabilities by looking at the parameters of the real-time SQL monitoring feature (real-time SQL monitoring) in the following ways.

(11.2.0.4 version) sql> select A.ksppinm "Parameter", A.ksppdesc "Description", B.KSPPSTVL "Value" from X$ksppi A, X$KSPPCV b  where a.indx = B.indx and a.ksppinm like '%_sqlmon% '; 2 3 4 Parameter Description value- ---------------------------------------------------------------------------------------------------------_sqlmon _threshold Cpu/io time threshold before a statement is monitored. 0 is disabled 5_sqlmon_max_plan Maximum number of plans entry so can be monitored.                  Defaults to $ per CPU 40_sqlmon_max_planlines number of plan lines beyond which a plan cannot be monitored 300_sqlmon_recycle_time Minimum Time (s) to wait before a plan entry can be recycled 60_sqlmo N_binds_xml_format format of column binds_xml in [g]v$sql_monitor default (12.1.0.2 version) Sql&gt ; Select A.ksppinm "Parameter", A.ksppdesc "DescriPtion ", B.KSPPSTVL" Value "from X$ksppi A, X$KSPPCV b where a.indx = B.indx and a.ksppinm like '%_sqlmon% ';                                                                      2 3 4 Parameter Description Value---------------------------------------------------------------------------------------------------------- --------------------------------------------_sqlmon_threshold Cpu/io time threshold before a statement is MO Nitored. 0 is disabled 5_sqlmon_max_plan Maximum number of plans entry so can be monitored.                     Defaults to $ per CPU 40_sqlmon_max_planlines number of plan lines beyond which a plan cannot be monitored                  300_sqlmon_recycle_time Minimum Time (s) to wait before a plan entry can be recycled                                   5★_sqlmon_binds_xml_format format of column binds_xml in [g]v$sql_monitor Default

Through the above output, I can see:

_sqlmon_threshold：串行执行的SQL文的监视阈值是5秒CPU/IO时间_sqlmon_max_plan ：V$SQL_MONITOR    中可以保存的执行计划个数（ CPU_COUNT*20）_sqlmon_max_planlines：可监视的最大执行计划行数（300行），当SQL的执行计划行数大于300行时，不会被监视。_sqlmon_recycle_time：监视对象结束后可以在V$SQL_MONITOR中保存的时间.                                   11g时为60秒；12c以后为5秒_sqlmon_binds_xml_format：视图V$SQL_MONITOR 的 binds_xml列的默认格式。

Changes in each version

11gR1:   推出该功能11gR2:   DBMS_SQLTUNE.REPORT_SQL_MONITOR程序包增加了 ‘ACTIVE‘ 参数，用于显示HTML 和Flash的输出结果12c:     监视对象结束后可以在V$SQL_MONITOR中保存的时间（_sqlmon_recycle_time），从60秒变为5秒

Copyright NOTICE: This article for Bo Master original article, reprint must indicate the source, I reserve all relevant powers! Http://blog.csdn.net/lukeunique

Examples of real-time SQL monitoring use

Test example: (11.2.0.4)

1. Prepare test tables and data

SQL> conn scott/tigerConnected.SQL>  drop table teacherwhat1;Table dropped.SQL>  drop table teacherwhat2;Table dropped.SQL> create table teacherwhat1(c1 number, c2 char(100));Table created.SQL> create table teacherwhat2(c1 number, c2 char(100));Table created.SQL>  begin   for i in 1 .. 400 loop     for j in 1 .. 300 loop       insert into teacherwhat1 values(i,‘A‘);       insert into teacherwhat2 values(i,‘B‘);       commit;     end loop;   end loop; end; /  2    3    4    5    6    7    8    9   10  PL/SQL procedure successfully completed.

2. Execute the SQL text

SQL> select /*+ use_nl(a b) */ count(*)from teacherwhat1 a, teacherwhat2 bwhere a.c1=b.c1;  2    3    COUNT(*)----------  36000000

3. View the sql_id of the Execute SQL text

SQL> SELECT sql_id, hash_value, substr(sql_text,1,40) sql_text  FROM  v$sql WHERE sql_text like ‘select /*+ use_nl(a b) */ count(*)%‘;  2    3  SQL_ID        HASH_VALUE------------- ----------SQL_TEXT--------------------------------------------------------------------------------dmtsu5j0r3pfn 1097979348select /*+ use_nl(a b) */ count(*) from

4. View real-time SQL monitoring results

4.1 Method 1: View real-time SQL monitoring results through the Dbms_sqltune.report_sql_monitor package.

4.1.1 Output in HTML form

SQL> spool sql_monitor.htmlSQL> SET LONG 1000000 SQL> SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id =>‘dmtsu5j0r3pfn‘,type=> ‘HTML‘) AS report FROM dual;REPORT--------------------------------------------------------------------------------

The result of the output in HTML form:

4.1.2 Output in text format

sql> spool sql_monitor.txtsql> SET LONG 1000000SET longchunksize 1000000SET linesize 1000SET PAGESIZE 0SET TRIM ONSE T trimspool ONSET ECHO OFFSET FEEDBACK offsql> sql> sql> sql> sql> sql> sql> sql> sql> SELECT D Bms_sqltune.report_sql_monitor (sql_id = ' dmtsu5j0r3pfn ', type = ' TEXT ') as report from dual; SQL monitoring Reportsql Text------------------------------Select/*+ use_nl (a b) */COUNT (*) from TEACHERWHAT1 A, teacher          WHAT2 b where A.c1=b.c1global information------------------------------status:done (all ROWS) Instance ID : 1 Session:scott (125:181) SQL ID:DMTSU5J0R3PFN SQL execution id:16777217 Execution started:07/14/2016 13:30:25 First refresh time:07/14/2016 13:30:33 last refresh time:07/14/2016 13             : 41:55 duration:690s module/action:sql*plus/-service:sys$users Program : [email protected] (TNS v1-v3) Fetch CaLls:1global stats===============================================================|   Elapsed | Cpu |  Concurrency | Other | Fetch | Buffer | | Time (s) |  Time (s) | Waits (s) | Waits (s) |  Calls |     Gets |===============================================================|     690 |        656 |       0.03 |     33 |   1 | 220M |===============================================================sql Plan monitoring Details (plan Hash Value= 1112930440) ==================================================================================================== ===============================|       Id |     Operation |  Name | Rows |   Cost | Time | Start |   Execs | Rows | Activity |    Activity Detail | |                       |              | |      (Estim) | | Active (s) |       Active | |   (Actual) |   (%)    | (# samples) |====================================================================================================  ===============================| 0 | SELECTSTATEMENT |         |      |       |     683 |     +8 |        1 |          1 |                 |  ||   1 |              SORT AGGREGATE |       |      1 |       |     683 |     +8 |        1 |          1 |                 |  ||    2 |              NESTED LOOPS |    |  189M |       57M |     683 |     +8 |      1 |          36M |                 |  ||     3 | TABLE ACCESS Full |    TEACHERWHAT1 |  112K |       512 |     683 |     +8 |     1 |          120K |                 |  ||     4 | TABLE ACCESS Full |    TEACHERWHAT2 |  1677 |       511 |     689 |  +2 |      120K |   36M | 100.00 | Cpu (689) |=====================================================================================================  ==============================sql> Spool offsql>

4.2. Through the Dynamic View V sq l m oNIToRand thev Sql_plan_monitor to view real-time SQL monitoring results.

sql> SET linesize 1000SET PAGESIZE 200SET TRIM ONSET trimspool ONSET ECHO ONSET FEEDBACK onsql> sql> sql> sql& Gt sql> sql> sql> Select Last_refresh_time, status, Sid, Sql_id, Sql_plan_hash_value, Elapsed_time, Cpu_time, Fetch  ES, Buffer_gets, disk_reads from V$sql_monitor where sql_id= ' DMTSU5J0R3PFN '; 2 3 last_refr STATUS SID sql_id sql_plan_hash_value elapsed_time cpu_time fetches BUFF Er_gets disk_reads--------------------------------------------------------------------------------------------- ------------------------------14-jul-16 done (all ROWS) DMTSU5J0R3PFN 2473516258 260492225 2 48014296 1 90001200 014-jul-16 done (all ROWS) DMTSU5J0R3PFN 1112930440 68 9653997 656385214 1 219601830 to the rows selected. Sql> Select plan_line_id, plan_operation | | "| | Plan_options operation,starts, Output_rows, Last_refresh_time,io_intErconnect_bytes,plan_cpu_costfrom v$sql_plan_monitor where sql_id= ' DMTSU5J0R3PFN ' ORDER by plan_line_id; 2 3 4 plan_line_id operation starts output_rows LAST_REFR I O_interconnect_bytes plan_cpu_cost-----------------------------------------------------------------------------                                                       ------------------------------------------------------------0 SELECT STATEMENT                                                       1 1 14-jul-16 0 0 0 SELECT STATEMENT 1 1 14-jul-16 0 0 1 SOR           T AGGREGATE 1 1 14-jul-16 0           1 SORT AGGREGATE 1 1 14-jul-16 0                           2 NESTED LOOPS                                1 18750000 14-jul-16 0 2.5143E+13 2 NESTED LOOPS           1 36000000 14-jul-16 0 3.4007E+12      3 TABLE ACCESS full 1 120000 14-jul-16 0                     30259980 3 TABLE ACCESS full 1 75000 14-jul-16 0 65184623 4 TABLE ACCESS full 120000 3                                                  6000000 14-jul-16 0 30260000 4 TABLE ACCESS full 75000 18750000 14-jul-16 0 6702679310 rows selected.  Sql>

Copyright NOTICE: This article for Bo Master original article, reprint must indicate the source, I reserve all relevant powers! Http://blog.csdn.net/lukeunique

Reference

Database PL/SQL Packages and Types Reference
>140 Dbms_sqltune

Oracle? Databaseリファレンス11gリリース2 (11.2) b56311-12
>v$sql_plan_monitor

Oracle Blogs
The Data Warehouse Insider

Oracle Database 11g:real-time SQL Monitoring
Http://www.oracle.com/technetwork/database/manageability/sqlmonitor-084401.html

SQL performance Real-time SQL monitoring feature (real-time SQL monitoring)