SQLMonitorReport怎么用
這篇文章主要為大家展示了“SQL Monitor Report怎么用”，內(nèi)容簡而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領(lǐng)大家一起研究并學(xué)習(xí)一下“SQL Monitor Report怎么用”這篇文章吧。
創(chuàng)新互聯(lián)公司從2013年創(chuàng)立，是專業(yè)互聯(lián)網(wǎng)技術(shù)服務(wù)公司，擁有項目成都做網(wǎng)站、成都網(wǎng)站建設(shè)網(wǎng)站策劃，項目實(shí)施與項目整合能力。我們以讓每一個夢想脫穎而出為使命，1280元蘭陵做網(wǎng)站,已為上家服務(wù),為蘭陵各地企業(yè)和個人服務(wù),聯(lián)系電話:18982081108
SQL Monitor Report
1.SQL Monitor簡介
在Oracle Database 11g中，系統(tǒng)自動監(jiān)控符合以下條件的SQL，并收集執(zhí)行時的細(xì)節(jié)信息：
1）采用并行方式執(zhí)行
2）單次執(zhí)行消耗的CPU或IO超過5秒
3）通過使用/* +MONITOR*/ HINT的語句
系統(tǒng)收集的SQL信息會存儲在V$SQL_MONITOR、V$SQL_PLAN_MONITOR視圖中
2.SQL Monitor 參數(shù)設(shè)置
STATISTICS_LEVEL設(shè)置為：'TYPICAL'（缺?。┗蛘?'ALL'
CONTROL_MANAGEMENT_PACK_ACCESS設(shè)置為：'DIAGNOSTIC+TUNING'
3.SQL Monitor report獲取方法
SQL Monitoring可以采用以下3種方式展現(xiàn)：
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的方式顯示動態(tài)的報告，需要從oracle官網(wǎng)讀取相關(guān)聯(lián)的Javascript和Flash。
備注：
如果不能連到Internet又想看ACTIVE Report可以下載相關(guān)的庫文件到本地的HTTP服務(wù)器上，然后用BASE_PATH來制定庫文件的位置。
在本地HTTP服務(wù)器上創(chuàng)建目錄，然后下載下面的文件：
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
在調(diào)用函數(shù)時加上參數(shù)，比如：base_path => 'http://ipaddr/sqlmon'
4. SQL Monitor report生成實(shí)例
語法：
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.1Text文本格式
1） Sqlplus參數(shù)設(shè)置查看
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） 執(zhí)行模擬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） 展示報告內(nèi)容
4.2HTML格式
1）生成HTML類型報告
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.3Active格式
如不能聯(lián)網(wǎng)，需要下載相應(yīng)的flash組件、腳本，詳細(xì)見SQL Monitor report獲取方法
1）active類型報告生成
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',
BASE_PATH => 'http://ipaddr/sqlmon') AS report
FROM dual;
spool off
2）active類型報告展示
可以通過啟動http服務(wù)，將文件放置在發(fā)布目錄下，通過http://ipaddr/sqlmon/report_sql_monitor_active.html形式查看（需下載相應(yīng)的腳本和組件）
或者拿到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以上版本。此函數(shù)用于產(chǎn)生一個對監(jiān)控SQL的匯總頁，類似于EM中的“Monitored SQL Executions”。
常用參數(shù)：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以上版本。此函數(shù)用于根據(jù)各種條件參數(shù)（包括：start_time, end_time, duration, inst_id, dbid, event_detail,
bucket_max_count, bucket_interval, top_n, duration_stats），產(chǎn)生比使用REPORT_SQL_MONITOR更加詳細(xì)的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格式，調(diào)整為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怎么用”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對大家有所幫助，如果還想學(xué)習(xí)更多知識，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道！
當(dāng)前題目：SQLMonitorReport怎么用
鏈接分享：http://m.jiaotiyi.com/article/jddsgd.html