SQL调优和性能监控工具SQL Monitor

11g中引入的新的动态性能视图v$monitor

1、确认参数

SQL> show parameter statistics_level;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      TYPICAL
SQL> show parameter control_management_pack_access;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access       string      DIAGNOSTIC+TUNING
SQL> 

这两个参数为前提：
Statistics_level必须设置为TYPICAL或者ALL
Control_management_pack_access设置为DIAGNSTIC+TUNING

2、确认要监控的SQL ID

执行的SQL会记录在v$monitor视图中，加/*+ monitor */强制监控
数据量大并行（/* +parallel(t,4) */）执行的SQL也会记录在视图中
SQL> select /*+ monitor */ type, count(*) from v$lock group by type;

TY   COUNT(*)
-- ----------
KD          1
RD          1
RT          1
RS          1
AE          2
CF          1
XR          1
MR          8
PW          1
KT          1
TS          1

11 rows selected.

SQL> select sql_id,sql_text from v$sql_monitor where sql_text like '%v$lock%';

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
02qa4khbuqv92
select /*+ monitor */ type, count(*) from v$lock group by type


SQL> 

3、查看SQL生成的报告

可以通过dbms_sqltune.report_sql_monitor指定多种形式生成报告（TEXT、HTML、ACTIVE、XML）
set long 1000000
set longchunksize 100000
set linesize 1000
set pagesize 0
set trim on
set trimspool on
set echo off
set feedback off
spool report_sql_monitor.txt
SQL> select dbms_sqltune.report_sql_monitor(sql_id=>'02qa4khbuqv92', type=>'TEXT', report_level=>'ALL') as report from dual;
SQL Monitoring Report

SQL Text
------------------------------
select /*+ monitor */ type, count(*) from v$lock group by type

Global Information
------------------------------
Status              :  DONE (ALL ROWS)
Instance ID         :  1
Session             :  SYS (12:45)
SQL ID              :  02qa4khbuqv92
SQL Execution ID    :  16777216
Execution Started   :  01/10/2023 11:28:27
First Refresh Time  :  01/10/2023 11:28:28
Last Refresh Time   :  01/10/2023 11:28:31
Duration            :  4s
Module/Action       :  sqlplus@myoracle (TNS V1-V3)/-
Service             :  SYS$USERS
Program             :  sqlplus@myoracle (TNS V1-V3)
Fetch Calls         :  2

Global Stats
========================================
| Elapsed |   Cpu   |  Other   | Fetch |
| Time(s) | Time(s) | Waits(s) | Calls |
========================================
|    4.00 |    3.99 |     0.01 |     2 |
========================================

SQL Plan Monitoring Details (Plan Hash Value=2823859538)
=============================================================================================================================================
| Id |         Operation         |    Name    |  Rows   | Cost |   Time    | Start  | Execs |   Rows   |  Mem  | Activity | Activity Detail |
|    |                           |            | (Estim) |      | Active(s) | Active |       | (Actual) | (Max) |   (%)   |   (# samples)   |
=============================================================================================================================================
|  0 | SELECT STATEMENT          |            |         |      |         1 |     +4 |     1 |       11 |       |         |                 |
|  1 |   HASH GROUP BY           |            |       1 |    1 |         2 |     +4 |     1 |       11 |    1M |    20.00 | Cpu (1)        |
|  2 |    HASH JOIN              |            |       1 |      |         3 |     +2 |     1 |       19 |   31M |    60.00 | Cpu (3)        |
|  3 |     MERGE JOIN CARTESIAN  |            |     100 |      |         3 |     +2 |     1 |     325K |       |         |                 |
|  4 |      FIXED TABLE FULL     | X$KSUSE    |       1 |      |         3 |     +2 |     1 |      248 |       |         |                 |
|  5 |      BUFFER SORT          |            |     100 |      |         3 |     +2 |   248 |     325K | 65536 |         |                 |
|  6 |       FIXED TABLE FULL    | X$KSQRS    |     100 |      |         1 |     +2 |     1 |     1312 |       |         |                 |
|  7 |     VIEW                  | GV$_LOCK   |      10 |      |         1 |     +4 |     1 |       20 |       |         |                 |
|  8 |      UNION-ALL            |            |         |      |         1 |     +4 |     1 |       20 |       |         |                 |
|  9 |       FILTER              |            |         |      |         1 |     +4 |     1 |       20 |       |         |                 |
| 10 |        VIEW               | GV$_LOCK1  |       2 |      |         1 |     +4 |     1 |       20 |       |         |                 |
| 11 |         UNION-ALL         |            |         |      |         1 |     +4 |     1 |       20 |       |         |                 |
| 12 |          FIXED TABLE FULL | X$KDNSSF   |       1 |      |           |        |     1 |          |       |         |                 |
| 13 |          FIXED TABLE FULL | X$KSQEQ    |       1 |      |         1 |     +4 |     1 |       20 |       |         |                 |
| 14 |       FIXED TABLE FULL    | X$KTADM    |       1 |      |           |        |     1 |          |       |         |                 |
| 15 |       FIXED TABLE FULL    | X$KTATRFIL |       1 |      |           |        |     1 |          |       |         |                 |
| 16 |       FIXED TABLE FULL    | X$KTATRFSL |       1 |      |           |        |     1 |          |       |         |                 |
| 17 |       FIXED TABLE FULL    | X$KTATL    |       1 |      |           |        |     1 |          |       |         |                 |
| 18 |       FIXED TABLE FULL    | X$KTSTUSC  |       1 |      |           |        |     1 |          |       |         |                 |
| 19 |       FIXED TABLE FULL    | X$KTSTUSS  |       1 |      |           |        |     1 |          |       |         |                 |
| 20 |       FIXED TABLE FULL    | X$KTSTUSG  |       1 |      |           |        |     1 |          |       |         |                 |
| 21 |       FIXED TABLE FULL    | X$KTCXB    |       1 |      |           |        |     1 |          |       |         |                 |
=============================================================================================================================================

SQL> spool off
也可以选择HTML格式
SQL> select dbms_sqltune.report_sql_monitor(sql_id=>'02qa4khbuqv92', type=>'HTML', report_level=>'ALL') as report from dual;
<html>
<head>
	<title> SQL Monitor Report </title>
	<style type="text/css">
     	body, table, input, select, textarea
    	{font:normal normal 8pt Verdana,Arial;text-decoration:none;
      	color:#000000; empty-cells:show;}
     	.s8 {font-size:8pt;color:#006699}
     	.s9 {font-size:10pt;color:#006699}
     	.s10 {font-size:14pt;color:#006699;}
     	.s16 {border-width : 1px; border-color : #CCCC99;
          	border-style: solid; color:#006699;font-size:8pt;
         	background-color:#CCCC99; empty-cells:show;}
    	.s17 {border-width : 1px; border-color : #CCCC99;
          	border-style: solid; font-size:8pt;
         	background-color:#E8E8E6; empty-cells: show }
    	.s17a {border-width : 1px; border-color : #BDCCC3;
          	border-style: solid; font-size:8pt;
          	background-color:#F5F5F5; empty-cells: show}
    	.s17b {border-width : 1px; border-color : #BDCCC3;
           	border-style: solid; font-size:8pt;
           	background-color:#F1F5DD; empty-cells: show}
    	.s27 {border-width : 1px; border-color : #CCCC99;
          	border-style: solid;}
...
...

我这里生成了HTML的代码，利用工具转换成可视化页面

结合SQL Monitor Report，我们就可以关注其中存在的一些问题，例如通常会根据最右边CPU和wait的activity，找到SQL执行计划中资源消耗较高的步骤。