SQL Monitor的使用

SQL Monitor参考文档

https://docs.oracle.com/cd/E11882_01/server.112/e41573/instance_tune.htm#PFGRF94543

使用sql monitor的先决条件

By default, SQL monitoring automatically starts when a SQL statement runs parallel, or when it has consumed at least 5 seconds of CPU or I/O time in a single execution.

The SQL monitoring feature is enabled by default when the STATISTICS_LEVEL initialization parameter is either set to ALL or TYPICAL (the default value). Additionally, the CONTROL_MANAGEMENT_PACK_ACCESS parameter must be set to DIAGNOSTIC+TUNING (the default value) because SQL monitoring is a feature of the Oracle Database Tuning Pack. SQL monitoring starts automatically for all long running queries.

-- 测试， 对表t1 开并行，然后查询表t1

alter table t1 parallel 4;
-- 执行sql 
select * from t1;

-- 查看SQL MOnitor结果

SYS@test>select dbms_sqltune.report_sql_monitor from dual;

REPORT_SQL_MONITOR
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL Monitoring Report

SQL Text
------------------------------
select * from t1

Global Information
------------------------------
 Status              :  DONE (ALL ROWS)
 Instance ID         :  1
 Session             :  SYS (1:5)
 SQL ID              :  27uhu2q2xuu7r
 SQL Execution ID    :  16777218
 Execution Started   :  05/20/2018 14:33:57
 First Refresh Time  :  05/20/2018 14:33:57
 Last Refresh Time   :  05/20/2018 14:35:54
 Duration            :  117s
 Module/Action       :  sqlplus@test.us.oracle.com (TNS V1-V3)/-
 Service             :  SYS$USERS
 Program             :  sqlplus@test.us.oracle.com (TNS V1-V3)
 Fetch Calls         :  314575

Global Stats
=========================================================================================
| Elapsed |   Cpu   |    IO    | Concurrency |  Other   | Fetch | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   | Waits(s) | Calls |  Gets  | Reqs | Bytes |
=========================================================================================
|    6.07 |    3.05 |     0.07 |        0.04 |     2.91 |  315K |   7263 |  107 |  56MB |
=========================================================================================

Parallel Execution Details (DOP=4 , Servers Allocated=4)
==================================================================================================================================
|      Name      | Type  | Server# | Elapsed |   Cpu   |    IO    | Concurrency |  Other   | Buffer | Read | Read  | Wait Events |
|                |       |         | Time(s) | Time(s) | Waits(s) |  Waits(s)   | Waits(s) |  Gets  | Reqs | Bytes | (sample #)  |
==================================================================================================================================
| PX Coordinator | QC    |         |    3.21 |    0.79 |          |        0.04 |     2.38 |      5 |      |     . |             |
| p000           | Set 1 |       1 |    0.69 |    0.57 |     0.02 |             |     0.10 |   1820 |   27 |  14MB |             |
| p001           | Set 1 |       2 |    0.69 |    0.57 |     0.01 |             |     0.12 |   1798 |   26 |  14MB |             |
| p002           | Set 1 |       3 |    0.75 |    0.56 |     0.03 |             |     0.15 |   1820 |   27 |  14MB |             |
| p003           | Set 1 |       4 |    0.73 |    0.56 |     0.01 |             |     0.16 |   1820 |   27 |  14MB |             |
==================================================================================================================================

SQL Plan Monitoring Details (Plan Hash Value=2494645258)
===============================================================================================================================================
| Id |       Operation        |   Name   |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  | Activity | Activity Detail |
|    |                        |          | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes |   (%)    |   (# samples)   |
===============================================================================================================================================
|  0 | SELECT STATEMENT       |          |         |      |       118 |     +0 |     5 |       5M |      |       |    50.00 | Cpu (1)         |
|  1 |   PX COORDINATOR       |          |         |      |       118 |     +0 |     5 |       5M |      |       |          |                 |
|  2 |    PX SEND QC (RANDOM) | :TQ10000 |      5M |  544 |       116 |     +2 |     4 |       5M |      |       |    50.00 | Cpu (1)         |
|  3 |     PX BLOCK ITERATOR  |          |      5M |  544 |       116 |     +2 |     4 |       5M |      |       |          |                 |
|  4 |      TABLE ACCESS FULL | T1       |      5M |  544 |       116 |     +2 |    52 |       5M |  107 |  56MB |          |                 |
===============================================================================================================================================


SYS@test>

ENd