SQL Monitor Report怎么用

这篇文章主要为大家展示了“SQL Monitor Report怎么用”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“SQL Monitor Report怎么用”这篇文章吧。 SQL Monitor Report在Oracle Database 11g中，系统自动监控符合以下条件的SQL，并收集执行时的细节信息：
1）采用并行方式执行2）单次执免费主机域名行消耗的CPU或IO超过5秒3）通过使用/* +MONITOR*/ HINT的语句系统收集的SQL信息会存储在V$SQL_MONITOR、V$SQL_PLAN_MONITOR视图中STATISTICS_LEVEL设置为：’TYPICAL’（缺省）或者 ‘ALL’CONTROL_MANAGEMENT_PACK_ACCESS设置为：’DIAGNOSTIC+TUNING’SQL Monitoring可以采用以下3种方式展现：1）EM：Performance ——>右下角的SQL Monitoring ——>Monitored SQL Executions2）SQL Developer：Tools ——>Monitor
SQL3）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/sqlmoncd
/var/www/html/sqlmonwget
–mirror –no-host-directories –cut-dirs=1
http://download.oracle.com/otn_software/emviewers/scripts/flashver.jswget
–mirror –no-host-directories –cut-dirs=1 http://download.oracle.com/otn_software/emviewers/scripts/loadswf.jswget
–mirror –no-host-directories –cut-dirs=1
http://download.oracle.com/otn_software/emviewers/scripts/document.jswget
–mirror –no-host-directories –cut-dirs=1 http://download.oracle.com/otn_software/emviewers/sqlmonitor/11/sqlmonitor.swf在调用函数时加上参数，比如：base_path
=> ‘http://ipaddr/sqlmon’语法： DBMS_SQLTUNE.REPORT_SQL_MONITOR()FUNCTION
REPORT_SQL_MONITOR RETURNS CLOBArgument Name Type In/Out Default?——————————
———————– —— ——–SQL_ID VARCHAR2 IN DEFAULTSESSION_ID NUMBER IN DEFAULTSESSION_SERIAL NUMBER IN DEFAULTSQL_EXEC_START DATE IN DEFAULTSQL_EXEC_ID NUMBER IN DEFAULTINST_ID NUMBER IN DEFAULTSTART_TIME_FILTER DATE IN DEFAULTEND_TIME_FILTER DATE IN DEFAULTINSTANCE_ID_FILTER NUMBER IN DEFAULTPARALLEL_FILTER VARCHAR2 IN DEFAULTPLAN_LINE_FILTER NUMBER IN DEFAULTEVENT_DETAIL VARCHAR2 IN DEFAULTBUCKET_MAX_COUNT NUMBER IN DEFAULTBUCKET_INTERVAL NUMBER IN DEFAULTBASE_PATH VARCHAR2 IN DEFAULTLAST_REFRESH_TIME DATE IN DEFAULTREPORT_LEVEL VARCHAR2 IN DEFAULTTYPE VARCHAR2 IN DEFAULTSQL_PLAN_HASH_VALUE NUMBER IN DEFAULT1） 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+TUNING2） 执行模拟SQL [oracle@node4 sqlmonitor]$ sqlplus -S
/nologconn /as sysdba;select /* +moniotr*/* from
scott.dept where deptno
DEPTNO
DNAME LOC———- ————–
————- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO3） 从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=104） 生成text类型报告 SET LONG 1000000SET LONGCHUNKSIZE 1000000SET LINESIZE 1000SET PAGESIZE 0SET TRIM ONSET TRIMSPOOL ONSET ECHO OFFSET FEEDBACK OFF
spool report_sql_monitor_text.txtSELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR( SQL_ID =>
’74qqqwntwzxb1′, TYPE => ‘TEXT’, REPORT_LEVEL => ‘ALL’) AS REPORTFROM dual;spool off5） 展示报告内容 1）生成HTML类型报告 SET LONG 1000000SET LONGCHUNKSIZE 1000000SET LINESIZE 1000SET PAGESIZE 0SET TRIM ONSET TRIMSPOOL ONSET ECHO OFFSET FEEDBACK OFFspool report_sql_monitor_html.htmlSELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR( SQL_ID =>
’74qqqwntwzxb1′, TYPE => ‘HTML’, REPORT_LEVEL => ‘ALL’) AS REPORTFROM dual;spool off2）html类型报告展示
如不能联网，需要下载相应的flash组件、脚本，详细见SQL Monitor report获取方法1）active类型报告生成 SET LONG 1000000SET LONGCHUNKSIZE 1000000SET LINESIZE 1000SET PAGESIZE 0SET TRIM ONSET TRIMSPOOL ONSET ECHO OFFSET FEEDBACK OFFspool report_sql_monitor_active.htmlSELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR( SQL_ID => ’74qqqwntwzxb1′, TYPE => ‘ACTIVE’, REPORT_LEVEL => ‘ALL’, BASE_PA免费主机域名TH =>
‘http://ipaddr/sqlmon’) AS reportFROM dual;spool off2）active类型报告展示 可以通过启动http服务，将文件放置在发布目录下，通过http://ipaddr/sqlmon/report_sql_monitor_active.html形式查看（需下载相应的脚本和组件）
或者拿到windows本地查看
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

以上是“SQL Monitor Report怎么用”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注云技术行业资讯频道！

相关推荐: Redis中redis-cluster需要注意哪些地方

本篇内容主要讲解“Redis中redis-cluster需要注意哪些地方”，感兴趣的朋友不妨来看看。本文免费主机域名介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“Redis中redis-cluster需要注意哪些地方”吧!1.收到150告警，rd…