ID 1380492.1 Monitoring SQL statements with Real-Time SQL Monitoring
来源:互联网 发布:实时卫星云图软件 编辑:程序博客网 时间:2024/05/22 12:35
ID 1380492.1 Monitoring SQL statements with Real-Time SQL Monitoring
The Oracle 11g Database has a new interface to monitor long running SQL commands.The feature is called Real-Time SQL Monitoring. By default, SQL monitoring is automatically started when a SQL command runs parallel, or when it has consumed at least five seconds of the CPU or I/O time in a single execution.You can also use the MONITOR hint to switch on SQL Monitoring.
SQL monitoring requires the STATISTICS_LEVEL parameter to be set to 'TYPICAL' or 'ALL',
and the CONTROL_MANAGEMENT_PACK_ACCESS parameter set to 'DIAGNOSTIC+TUNING'.
The Real-Time SQL Monitoring feature is licensed with Diagnostics and Tuning Pack.
After monitoring is initiated, an entries are added to the dynamic performance V$SQL_MONITOR and V$SQL_PLAN_MONITOR views. This entry tracks key performance metrics collected for the execution, including the elapsed time, CPU time, number of reads and writes, I/O wait time, and various other wait times.These statistics are refreshed in near real time as the command executes, generally once every second.
After the execution ends, monitoring information is not deleted immediately, but is kept in the V$SQL_MONITOR/V$SQL_PLAN_MONITOR view for at least one minute.The entry is eventually deleted so its space can be reclaimed as new commands are monitored.
You can find more details in the attached white paper.
DBMS_SQLTUNE.REPORT_SQL_MONITOR
The REPORT_SQL_MONITOR function is used to return a SQL monitoring report for a specific SQL statement. The SQL statement can be identified using a variety of parameters.
The function accepts some optional parameters, the most common ones are:.
- SQL_ID - The SQL_ID of the query of interest. When NULL (the default) the last monitored statement is targeted.
- REPORT_LEVEL - The amount of information displayed in the report.
The basic allowed values are 'NONE', 'BASIC', 'TYPICAL' or 'ALL',
The default is 'TYPICAL' which is sufficent in most cases. - TYPE - The format used to display the report ('TEXT', 'HTML', 'XML' or 'ACTIVE').
The 'ACTIVE' parameter is new in Oracle 11g Release 2 and displays the output using HTML and Flash. An Internet connection is needed to use the 'ACTIVE' parameter. - SESSION_ID - Targets a subset of queries based on the specified SID.
Use SYS_CONTEXT('USERENV','SID') for the current session.
The default is NULL.
Following is an example using REPORT_SQL_MONITOR:
SET LONG 1000000SET LONGCHUNKSIZE 1000000SET LINESIZE 1000SET PAGESIZE 0SET TRIM ONSET TRIMSPOOL ONSET ECHO OFFSET FEEDBACK OFFSELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => '<sql_id>', type => 'TEXT')AS report FROM dual;
To get an overview what SQL commands are in V$SQL_MONITOR you can use:
or you can use the function REPORT_SQL_MONITOR_LIST.
SET LINESIZE 300
COLUMN sql_text FORMAT A100
SELECT sql_id, status, sql_text FROM v$sql_monitor;
or you can use the function REPORT_SQL_MONITOR_LIST.
For the above example this shows that there is a statement with the sql id 526mvccm5nfy4.
We can get now a report with the following command:
Global Information------------------------------ Status : DONE (ALL ROWS) Instance ID : 1 Session : SCOTT (18:407) SQL ID : 526mvccm5nfy4 SQL Execution ID : 16777216 Execution Started : 11/24/2011 15:43:11 First Refresh Time : 11/24/2011 15:43:11 Last Refresh Time : 11/24/2011 15:43:12 Duration : 1s Module/Action : SQL*Plus/- Service : SYS$USERS Program : sqlplus.exe Fetch Calls : 4Global Stats=====================================================================================| Elapsed | Cpu | IO | PL/SQL | Other | Fetch | Buffer | Read | Read || Time(s) | Time(s) | Waits(s) | Time(s) | Waits(s) | Calls | Gets | Reqs | Bytes |=====================================================================================| 0.24 | 0.02 | 0.22 | 0.00 | 0.01 | 4 | 198 | 16 | 144KB |=====================================================================================SQL Plan Monitoring Details (Plan Hash Value=1281390562)===================================================================================================================| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read || | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs |===================================================================================================================| 0 | SELECT STATEMENT | | | | 1 | +1 | 1 | 3 | || 1 | SORT ORDER BY | | 4 | 8 | 1 | +1 | 1 | 3 | || 2 | SORT GROUP BY | | 4 | 8 | 1 | +1 | 1 | 3 | || 3 | MERGE JOIN | | 14 | 6 | 1 | +1 | 1 | 14 | || 4 | TABLE ACCESS BY INDEX ROWID | DEPT | 4 | 2 | 1 | +1 | 1 | 4 | 1 || 5 | INDEX FULL SCAN | PK_DEPT | 4 | 1 | 1 | +1 | 1 | 4 | 1 || 6 | SORT JOIN | | 14 | 4 | 1 | +1 | 4 | 14 | || 7 | TABLE ACCESS FULL | EMP | 14 | 3 | 1 | +1 | 1 | 14 | 2 |===================================================================================================================SET LONG 1000000SET FEEDBACK OFFSELECT DBMS_SQLTUNE.report_sql_monitor(sql_id =>'526mvccm5nfy4',type=> 'TEXT')AS report FROM dual;
The active option is the best choice for parallel queries.
You can get this using the following:
spool monitor_sql.html
SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id =>'<sql_id>',type =>'active')
AS report FROM dual;
spool off
You will then need to open the spool file , remove the first lines before the first <html>.
Then you can open the file in a browser with an Internet connection to OTN.
REPORT_SQL_MONITOR_LIST
The REPORT_SQL_MONITOR_LIST function was added in Oracle 11g Release 2 to generate a summary of all SQL stored in V$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_list(type =>'TEXT',report_level => 'ALL') AS report FROM dual;
From:http://myoracleguide.com/msa/ID%201380492.1%20Monitoring%20SQL%20statements%20with%20Real-Time%20SQL%20Monitoring.htm
- ID 1380492.1 Monitoring SQL statements with Real-Time SQL Monitoring
- Monitoring SQL Statements with Real-Time SQL Monitoring (文档 ID 1380492.1)
- oracle11g Real Time SQL Monitoring
- Real-Time SQL Monitoring详解
- Real-Time SQL Monitoring using DBMS_SQLTUNE
- 【SQL Performance】实时SQL监控功能(Real-Time SQL Monitoring)
- Real-Time SQL Monitoring using DBMS_SQLTUNE (REPORT_SQL_MONITOR, REPORT_SQL_MONITOR_LIST and REPORT_
- Real Time Interactive IP LAN Monitoring with IPTraf Tool
- oratop - Utility for Near Real-time Monitoring of Databases, RAC and Single Instance (Doc ID 1500864
- Monitoring the Cluster in Real Time with CHM (Cluster Health Monitor)
- Inotify: Efficient, Real-Time Linux File System Event Monitoring
- Monitoring Clusters With SNMP
- Network monitoring with MRTG
- Process monitoring with OSSEC
- Monitoring with Thread
- Monitoring Tomcat with LoadRunner
- Monitoring with Ganglia
- monitoring with Prometheus
- 关于jquery获取input的value问题
- ODBC读取Excel错误 Microsoft Jet 数据库引擎找不到对象'Sheet1' 请确认对象是否存在,并正确地写出它的名称和路径
- EVC数据类型的转换(转的帖子,待测试)
- lamp|lnmp|lnamp|一键安装包
- 走出浮躁的泥沼:关于技术与工作
- ID 1380492.1 Monitoring SQL statements with Real-Time SQL Monitoring
- 在centos6.2安装gluster3.3.0
- linux多线程学习(一)
- modprobe和insmod的区别
- GridView中读出数据库中的数据
- Ubuntu Linux 入门(六):Ubuntu 常用软件推荐
- 快速搭建节日促销专题
- 浅谈数据库设计技巧
- linux多线程学习(二)——线程的创建和退出