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;
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;
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
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;
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执行统计信息和详细信息。
无需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
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监视报告。
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
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
注意：参考文档
Speak Your Mind