sqlmonitor for oracle,华丽丽的SQL报告：Oracle SQL Monitor Report

华丽丽的SQL报告：Oracle SQL Monitor Report

这个报告会通过OTN获得展现的框架和JS脚本，如果不能连接到公网，你可以在本地构建相应的文件，我在自己的站点保存了这些脚本：

mkdir -p eygle.com/sqlmon

cd eygle.com/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

这样我在生成SQL报告时，就可以调用自己网站的脚本文件。

通过v$sql_monitor视图可以找到已经被记录的SQL_ID:

SQL> select sql_id from v$sql_monitor;

SQL_ID

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

6rqxj647ut9pn

f4kcr0dn9rv6z

f6cz4n8y72xdc以下是通过脚本调用，生成了一个报告：

[eygle@enmoteam2 ~]$ sqlplus "/ as sysdba" @eygle.sql

SQL*Plus: Release 11.2.0.3.0 Production on Thu Sep 6 15:01:55 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options在脚本 eygle.sql 中定义了SQL_ID ,通过这个SQL_ID 生成了ACTIVE REPORT：

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.htm

SELECT DBMS_SQLTUNE.report_sql_monitor(

sql_id       => '6rqxj647ut9pn',

type         => 'ACTIVE',

report_level => 'ALL',

base_path    => 'http://www.eygle.com/sqlmon') AS report

FROM dual;

SPOOL OFF这样生成的报告较以前的SQL Report更为直观。

以上优化的SQL，优化之前的执行计划有184个步骤，非常复杂：

关于这个复杂SQL的演示，可以从以下链接获得：

http://www.eygle.com/sqlmon/report_sql_monitor_demo.htm

Kaya 在以前ACOUG的活动中曾经讲到过这个新特性，以下是2个来自Exadata中的测试SQL展现：

http://www.eygle.com/sqlmon/before.html

http://www.eygle.com/sqlmon/after.html

关于这个新特性的参考链接：

http://www.os2ora.com/use-sql-monitor-report-to-tune-and-diagnose-sql/

http://www.os2ora.com/fantastic-11gr2-sql-monitor-report/

http://www.oracle-base.com/articles/11g/real-time-sql-monitoring-11gr1.php

http://docs.oracle.com/cd/E11882_01/appdev.112/e16760/d_sqltun.htm

http://www.oracle.com/technetwork/database/focus-areas/manageability/sqlmonitor-084401.html

By eygle on 2012-09-07 08:30 |

Comments (0) |

SQL.PLSQL | 3043 |