Real-time SQL monitoring

Source: Internet
Author: User
Tags session id

Real-time SQL monitoring can monitor the performance of SQL while it is running. By default, real-time SQL monitoring starts automatically when a single SQL execution takes more than 5 seconds of CPU or I/O time or SQL parallel execution.

You can view the statistics for the SQL statement runtime through view V$sql_monitor, V$sql_plan_monitor.

More information can be obtained by combining the following views: V$active_session_history, V$session, V$session_longops, V$sql, V$sql_plan

Once monitoring is started, a entry is added to the V$sql_monitor, including performance statistics. At the end of execution, the corresponding entry will not be deleted immediately, but will remain for one minute. Unlike V$sql_monitor and V$sql, a entry in the former corresponds to a single executed statement, which is the cumulative result.

For those query SQL that executes the plan, if the implicit parameter "_sqlmon_max_planlines" is exceeded, the default is 300,sql monitor to reduce overhead (CPU and memory).
This parameter can be modified dynamically:

Sql> alter system set "_sqlmon_max_planlines" =500 Scope=both;

1.real-time SQL monitoring mainly contains the following aspects:

(1). SQL Plan monitoring real-time SQL monitoring also contains statistics that monitor each step of the execution plan. Can be viewed through v$sql_plan_monitor. The Save cycle for statistics is similar to V$sql_monitor. There are multiple entry for a SQL in V$sql_plan_monitor.

(2). Parallel execution monitoring parallel queries, parallel DDL, parallel DML are monitored automatically by real-time SQL monitoring.

2. Generate real-time SQL monitoring reports

The real-time SQL Monitoring report covers the following views:

Gv$sql_monitor, Gv$sql_plan_monitor, Gv$sql, Gv$sql_plan, Gv$active_session_history, Gv$session_longops

Dbms_sqltune.report_sql_monitor can return a monitoring report for a specified SQL

sql> var my_rept clob; Sql> begin  2     : my_rept: = Dbms_sqltune.report_sql_monitor (), #默认是text格式, and HTML, XML format  3  end;  4  /pl/sql procedure successfully completed. Sql> print:my_rept

To view the SQL Monitor report for a SQL in HTML format:

Set LONG 1000000 set FEEDBACK offspool monitor_sql.html SELECT dbms_sqltune.report_sql_monitor (sql_id = " 0tqfh0cggfg0v ',type=> ' HTML ') as report from Dual;spool off

View the SQL Monitor report for a SQL in text format:

SET LONG 1000000SET longchunksize 1000000SET linesize 1000SET PAGESIZE 0SET TRIM ONSET trimspool ONSET ECHO OFFSET Feedbac K offselect dbms_sqltune.report_sql_monitor (sql_id = ' <sql_id> ', type = ' TEXT ') as report from dual;

Example Text results:

Set long 10000000set longchunksize 10000000set linesize 200select dbms_sqltune.report_sql_monitor from dual; SQL Text----------------------------------------------------------------------------------------SELECT * FROM ( Select O_orderdate, sum (o_totalprice) from Orders O, LineItem l where L.l_orderkey = O.o_orde Rkey GROUP BY O_orderdate Order by O_orderdate) where RowNum <------------------------- ---------------------------------------------------------------Global Information status:executing# is performing I Nstance id:1 Session id:980 SQL id:br4m75c20p97h SQL execution id:16777219 Pl  A Hash value:2992965678 execution started:06/07/2007 08:36:42 first Refresh time:06/07/2007 08:36:46 last Refresh time:06/07/2007 08:40:02-------------------------------------------------------------------------------- ---|   Elapsed |    Cpu | IO |  Application |Other | Buffer | Reads | Writes | | Time (s) | Time (s) |  Waits (s) | Waits (s) |  Waits (s) |       Gets |        |     |-----------------------------------------------------------------------------------|     198 |       140 |        56 |     0.31 |  1.44 | 1195K |  1264K | 84630 |-----------------------------------------------------------------------------------SQL Plan Monitoring details# time Active (s): The duration of the step operation, in seconds # start active: The time in the execution plan relative to when SQL started executing, in seconds ========================= ==============================================================|         Id |   Operation |  Name | Rows |   Cost | Time |      Start | |                            |          | |       (Estim) | | Active (s) |    Active |=======================================================================================| 0 |          SELECT STATEMENT |         |  |           125K |        |    ||   1 |          COUNT Stopkey |         |       |           |        |||    2 |          VIEW |    |  2406 |           125K |        |    ||     3 |          SORT GROUP by Stopkey |    |  2406 |        125K |   99 | +101 | |      4 |          HASH JOIN |   |  8984K |       123K |    189 |      +12 | |                            |          |         |       |           |        |    ||       5 | INDEX FAST Full SCAN |   I_l_okey | 8984K |        63191 |     82 |      +1 | |                            |          |         |       |           |        |    ||       6 |          PARTITION RANGE All |  | 44913K |        57676 |    94 |    +84 | |        7 |          PARTITION HASH All |  | 44913K |        57676 |    94 |    +84 | |         8 | TABLE ACCESS Full |  ORDERS | 44913K |        57676 |    95 |      +84 | |                            |          |         |       |           |        |      ||                            |          |         |       |           |        | |======================================================================================= continuation of above table# starts: Indicates number of runs in execution Plan # Rows (Actual): Number of rows generated # activity (percent): The percentage of database time spent on the entire execution plan # activity Detail (Sample #): Displays the nature of the activity, such as CPU, wait events ========================   =============================================================== Starts | Rows | Memory | Temp |      Activity | Activity Detail |        Progress | |        (Actual) |      | |        (percent) |          (Sample #) |          |======================================================================================= 1 |        |      |           |                           |          |      |          1 |        |      |           |                           |          |      |          1 |        |      |           |                           |          |      |        1 |        0 |      |      | 4.02 |          Cpu (8) |      |   1 | 28130K | 10000K |     724M | 25.13 | Cpu (48) |        87% |  |        |      |           | |          Direct Path Read temp (2) |      |   1 |        32734K |      |     | 34.17 | Cpu (58) |        100% |          |        |      |           | |          Direct path Read (10) |      |   1 |        45000K |      |           |                           |          |     |   84 |        45000K |      |           |                           |          |    |   672 |        45000K |      |     | 36.68 |          Cpu (28) |        |          |        |      |           | |          Reliable message (3) |        |          |        |      |           | |          Direct path Read (42) | |=======================================================================================

List the SQL statements in V$sql_monitor:

SET linesize 300COLUMN sql_text FORMAT a100select sql_id, status, Sql_text from V$sql_monitor;

3. Turn on/Off real-time SQL monitoring
When the parameter statistic_level is set to typical, all, real-time SQL automatic monitoring is turned on automatically.
The control_management_pack_access is also set to diagnostic+tuning because SQL monitoring belongs to the tuning package component.

You can also use hint to turn on/off:

Select/+monitor+/from Dual;select/+no_monitor+/from dual;

Real-time SQL monitoring

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.