SQL Monitor Report总结学习

deadknight9

已于 2024-07-08 09:20:33 修改

阅读量919

点赞数 36

分类专栏： Oracle 文章标签： oracle

于 2024-07-08 09:08:34 首次发布

本文链接：https://blog.csdn.net/zengxiangbo/article/details/140258680

版权

Oracle 专栏收录该内容

78 篇文章 0 订阅

订阅专栏

1. SQL Monitor简介

在Oracle Database 11g中，系统自动监控符合以下条件的SQL，并收集执行时的细节信息：

1）采用并行方式执行

2）单次执行消耗的CPU或IO超过5秒

3）通过使用/* +MONITOR*/ HINT的语句

系统收集的SQL信息会存储在V$SQL_MONITOR、V$SQL_PLAN_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

spool report_sql_monitor_active.html

SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(

SQL_ID => 'g59kf70w1dpku',

TYPE => 'ACTIVE',

REPORT_LEVEL => 'ALL') AS report

FROM dual;

spool off

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 REPORT_SQL_DETAIL_HTML.HTML

SELECT dbms_sqltune.REPORT_SQL_DETAIL(SQL_ID => 'g59kf70w1dpku',

TYPE => 'active',

report_level => 'ALL') AS report

FROM dual;

SPOOL OFF

2. SQL Monitor 参数设置(默认就是开启的)

STATISTICS_LEVEL设置为：'TYPICAL'（缺省）或者 'ALL'

CONTROL_MANAGEMENT_PACK_ACCESS设置为：'DIAGNOSTIC+TUNING'

3. SQL Monitor report获取方法

SQL Monitoring可以采用以下3种方式展现：

1）EM：Performance ——>右下角的SQL Monitoring ——>Monitored SQL Executions

2）SQL Developer：Tools ——>Monitor SQL

3）DBMS_SQLTUNE包 ——> DBMS_SQLTUNE.report_sql_monitor

其报告格式有：'TEXT'，'HTML'，'XML' ，'ACTIVE'，其中'ACTIVE'只在11g R2以后才支持，使用HTML和Flash的方式显示动态的报告，需要从oracle官网读取相关联的Javascript和Flash。

备注：

如果不能连到Internet又想看ACTIVE Report可以下载相关的库文件到本地的HTTP服务器上，然后用BASE_PATH来制定库文件的位置。

在本地HTTP服务器上创建目录，然后下载下面的文件：

mkdir -p /var/www/html/sqlmon

cd /var/www/html/sqlmon

wget --mirror --no-host-directories --cut-dirs=1 http://download.oracle.com/otn_software/emviewers/scripts/flashver.js

wget --mirror --no-host-directories --cut-dirs=1 http://download.oracle.com/otn_software/emviewers/scripts/loadswf.js

wget --mirror --no-host-directories --cut-dirs=1 http://download.oracle.com/otn_software/emviewers/scripts/document.js

wget --mirror --no-host-directories --cut-dirs=1 http://download.oracle.com/otn_software/emviewers/sqlmonitor/11/sqlmonitor.swf

在调用函数时加上参数，比如：base_path => 'http://ipaddr/sqlmon'

4.SQL Monitor report生成实例

语法：

DBMS_SQLTUNE.REPORT_SQL_MONITOR()

FUNCTION REPORT_SQL_MONITOR RETURNS CLOB

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

SQL_ID VARCHAR2 IN DEFAULT

SESSION_ID NUMBER IN DEFAULT

SESSION_SERIAL NUMBER IN DEFAULT

SQL_EXEC_START DATE IN DEFAULT

SQL_EXEC_ID NUMBER IN DEFAULT

INST_ID NUMBER IN DEFAULT

START_TIME_FILTER DATE IN DEFAULT

END_TIME_FILTER DATE IN DEFAULT

INSTANCE_ID_FILTER NUMBER IN DEFAULT

PARALLEL_FILTER VARCHAR2 IN DEFAULT

PLAN_LINE_FILTER NUMBER IN DEFAULT

EVENT_DETAIL VARCHAR2 IN DEFAULT

BUCKET_MAX_COUNT NUMBER IN DEFAULT

BUCKET_INTERVAL NUMBER IN DEFAULT

BASE_PATH VARCHAR2 IN DEFAULT

LAST_REFRESH_TIME DATE IN DEFAULT

REPORT_LEVEL VARCHAR2 IN DEFAULT

TYPE VARCHAR2 IN DEFAULT

SQL_PLAN_HASH_VALUE NUMBER IN DEFAULT

4.1 Text文本格式

1） Sqlplus 参数设置查看

show parameter statistics_level;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

statistics_level string TYPICAL

show parameter CONTROL_MANAGEMENT_PACK_ACCESS;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

control_management_pack_access string DIAGNOSTIC+TUNING

2）执行模拟SQL

[oracle@node4 sqlmonitor]$ sqlplus -S /nolog

conn /as sysdba;

select /* +moniotr*/* from scott.dept where deptno<=30;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

3）从v$sql_monitor获取模拟SQL信息

col sql_text for a60;

set line 200;

set pagesize 20000;

select sql_id,sql_text from v$sql_monitor where sql_text like '%scott.dept%';

SQL_ID SQL_TEXT

------------- ------------------------------------------------------------

74qqqwntwzxb1 select /*+ Monitor*/ * from scott.dept where deptno=10

4）生成text类型报告

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 report_sql_monitor_text.txt

SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(

SQL_ID => '74qqqwntwzxb1',

TYPE => 'TEXT',

REPORT_LEVEL => 'ALL') AS REPORT

FROM dual;

spool off

5）展示报告内容

4.2 HTML格式

1）生成HTML类型报告（生成到登录sqlplus的目录）

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 report_sql_monitor_html.html

SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(

SQL_ID => '74qqqwntwzxb1',

TYPE => 'HTML',

REPORT_LEVEL => 'ALL') AS REPORT

FROM dual;

spool off

2）html类型报告展示

4.3 Active格式

如不能联网，需要下载相应的flash组件、脚本，详细见SQL Monitor report获取方法

1）active类型报告生成（生成到登录sqlplus的目录）

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 report_sql_monitor_active.html

SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(

SQL_ID => '74qqqwntwzxb1',

TYPE => 'ACTIVE',

REPORT_LEVEL => 'ALL') AS report

FROM dual;

spool off

2）active类型报告展示

可以通过启动http服务，将文件放置在发布目录下，通过http://ipaddr/sqlmon/report_sql_monitor_active.html形式查看（需下载相应的脚本和组件）

或者拿到windows本地查看

5. SQL Monitor report其他方法使用

1）DBMS_SQLTUNE.REPORT_SQL_MONITOR_LIST

FUNCTION REPORT_SQL_MONITOR_LIST RETURNS CLOB

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

SQL_ID VARCHAR2 IN DEFAULT

SESSION_ID NUMBER IN DEFAULT

SESSION_SERIAL NUMBER IN DEFAULT

INST_ID NUMBER IN DEFAULT

ACTIVE_SINCE_DATE DATE IN DEFAULT

ACTIVE_SINCE_SEC NUMBER IN DEFAULT

LAST_REFRESH_TIME DATE IN DEFAULT

REPORT_LEVEL VARCHAR2 IN DEFAULT

AUTO_REFRESH NUMBER IN DEFAULT

BASE_PATH VARCHAR2 IN DEFAULT

TYPE VARCHAR2 IN DEFAULT

需要Oracle 11g R2以上版本。此函数用于产生一个对监控SQL的汇总页，类似于EM中的“Monitored SQL Executions”。

常用参数：TYPE和REPORT_LEVEL，用法与REPORT_SQL_MONITOR类似。

例如：

conn /as sysdba;

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 REPORT_SQL_MONITOR_LIST.HTML

SELECT dbms_sqltune.report_sql_monitor_list(

type => 'HTML',

report_level => 'ALL') AS report

FROM dual;

SPOOL OFF

2）DBMS_SQLTUNE.REPORT_SQL_DETAIL

FUNCTION REPORT_SQL_DETAIL RETURNS CLOB

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

SQL_ID VARCHAR2 IN DEFAULT

SQL_PLAN_HASH_VALUE NUMBER IN DEFAULT

START_TIME DATE IN DEFAULT

DURATION NUMBER IN DEFAULT

INST_ID NUMBER IN DEFAULT

DBID NUMBER IN DEFAULT

EVENT_DETAIL VARCHAR2 IN DEFAULT

BUCKET_MAX_COUNT NUMBER IN DEFAULT

BUCKET_INTERVAL NUMBER IN DEFAULT

TOP_N NUMBER IN DEFAULT

REPORT_LEVEL VARCHAR2 IN DEFAULT

TYPE VARCHAR2 IN DEFAULT

DATA_SOURCE VARCHAR2 IN DEFAULT

END_TIME DATE IN DEFAULT

DURATION_STATS NUMBER IN DEFAULT

需要Oracle 11g R2以上版本。此函数用于根据各种条件参数（包括：start_time, end_time, duration, inst_id, dbid, event_detail, bucket_max_count, bucket_interval, top_n, duration_stats），产生比使用REPORT_SQL_MONITOR更加详细的SQL报告。

例如：

conn /as sysdba;

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 REPORT_SQL_DETAIL_HTML.HTML

SELECT dbms_sqltune.REPORT_SQL_DETAIL(SQL_ID => '74qqqwntwzxb1',

TYPE => 'active',

report_level => 'ALL') AS report

FROM dual;

SPOOL OFF

ERROR:

ORA-13971: Component "sql_detail" unknown

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95

ORA-06512: at "SYS.DBMS_REPORT", line 166

ORA-06512: at "SYS.DBMS_REPORT", line 612

ORA-06512: at "SYS.DBMS_REPORT", line 1079

ORA-06512: at "SYS.DBMS_REPORT", line 1135

ORA-06512: at "SYS.DBMS_SQLTUNE", line 20101

ORA-06512: at line 1

（上述错误在指定html格式，调整为active格式即可）

可以针对topSQL

conn /as sysdba;

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 REPORT_SQL_DETAIL.HTML

SELECT dbms_sqltune.report_sql_detail(top_n => 5,

TYPE => 'active',

report_level => 'ALL') AS report

FROM dual;

SPOOL OFF

deadknight9

关注

36
点赞
踩
21

收藏

觉得还不错? 一键收藏
0
评论
SQL Monitor Report总结学习

此函数用于根据各种条件参数（包括：start_time, end_time, duration, inst_id, dbid, event_detail, bucket_max_count, bucket_interval, top_n, duration_stats），产生比使用REPORT_SQL_MONITOR更加详细的SQL报告。系统收集的SQL信息会存储在V$SQL_MONITOR、V$SQL_PLAN_MONITOR视图中。3）从v$sql_monitor获取模拟SQL信息。
复制链接

扫一扫

专栏目录