Oracle 11g real-time SQL monitoring

Oracle 11g real-time SQL monitoring

Oracle,

In-depth analysis of Oracle,
Reading Notes April 9 th, 2010

Author: dbtan | [indicate the source and author information in hyperlink form during reprinting]
Link: http://www.dbtan.com/2010/04/real-time-sql-monitoring-in-oracle11g.html

Oracle 11g real-time SQL monitoring:

As mentioned above, in Oracle Database 11g, the V $ session view adds some new fields, includingSQL _exec_startAndSQL _exec_idThese two fields actually represent a new feature of Oracle 11g:Real-time SQL monitoring).

In versions earlier than Oracle 11g, SQL statements that run for a long time can be monitoredV $ session_longopsWhen the execution time of an operation exceeds 6 seconds, it is recorded in V $ session_longops, generally, operations such as full table scan, full index scan, hash join, and parallel query can be monitored.In Oracle 11g, when SQL is executed in parallel, it will be immediately monitored in real time, or when a single SQL process is running, if the consumption of CPU or I/O time exceeds 5 seconds, it will also be monitored. Monitoring data is recorded inV $ SQL _monitorIn the view, you can also use Oracle
Package added for 11gDbms_monitorTo actively monitor and deploy SQL statements.

Let's take a look at the structure of the main view v $ SQL _monitor:

Sys @ ccdb> DESC v $ SQL _monitor
Name null? Type
--------------------------------------------------
Key number
Status varchar2 (19)
First_refresh_time date
Last_refresh_time date
Refresh_count number
SID number
Process_name varchar2 (5)
SQL _id varchar2 (13)
SQL _exec_start date
SQL _exec_id number
SQL _plan_hash_value number
SQL _child_address raw (8)
Session_serial # Number
Px_server # Number
Px_server_group number
Px_server_set number
Px_qcinst_id number
Px_qcsid number
Elapsed_time number
Cpu_time number
Fetches number
Buffer_gets number
Disk_reads number
Direct_writes number
Application_wait_time number
Concurrency_wait_time number
Cluster_wait_time number
User_io_wait_time number
Plsql_exec_time number
Java_exec_time number

Note that SQL _exec_id is the source of the new field in the V $ session view.. This view also records important information such as SQL cpu_time and buffer_gets, which is of great help in diagnosing SQL Performance problems. Combined with the V $ SQL _monitor view and the V $ SQL _plan_monitor view, you can further query the SQL Execution Plan and other information. Other views such as V $ active_session_history, V $ session, V $ session_longops, V $ SQL, and V $ SQL _plan can be combined to obtain more information about SQL.

The information collected by V $ SQL _monitor is refreshed every second, which is close to real-time. When SQL Execution is completed, the information will not be deleted from V $ SQL _monitor immediately and will be retained for at least 1 minute, the execution plan information in the V $ SQL _plan_monitor view is also updated every second. When SQL Execution is completed, they are also retained for at least 1 minute.

Real-time SQL monitoring requires statistics_level to set the initialization parameter to typical or all:

Sys @ ccdb> show parameter statistics_level
Name type value
----------------------------------------------
Statistics_level string typical
Sys @ ccdb> select statistics_name, session_status, system_status, activation_level, session_settable
2 from V $ statistics_level
3 where statistics_name = 'SQL monitoring ';
Statistics_name session_status system_status activation_level session_s
----------------------------------------------------------------------------
SQL monitoring enabled typical Yes

At the same time, the control_management_pack_access parameter must be diagnostic + tuning (which is the default setting ):

Sys @ ccdb> show parameter control_manage
Name type value
----------------------------------------------------------------------
Control_management_pack_access string diagnostic + Tuning

In the preceding settings, the database starts automatic real-time SQL monitoring. Oracle also provides hints to enforce SQL Execution monitoring or disable monitoring. The two hints are monitor and no_monitor.

To use real-time monitoring for an SQL statement, rewrite the SQL statement as follows:
Select/* + Monitor */Count (*) from E where SAL> 5000;
Specify not to perform real-time monitoring:
Select/* + No_monitor */Count (*) from E where SAL> 5000;

You can view the generated monitoring information in the database.Dbms_sqltunePackage:

Sys @ ccdb> set long 10000000
Sys @ ccdb> set longchunksize 10000000
Sys @ ccdb> set linesize 200
Sys @ ccdb> select dbms_sqltune.report_ SQL _monitor from dual;
Report_ SQL _monitor
-----------------------------------
SQL Monitoring Report

SQL text
--------------------------------------------------------------
--------------------------------------------------------------

Global Information
Status: done (all rows)
Instance id: 1
Session ID 982
SQL ID: gn3h2qrqdfwru
SQL Execution ID: 16777216
Plan hash value: 2959114426
Execution started: 02/28/2010 14:02:37
First refresh time: 02/28/2010 14:02:41
Last refresh time: 02/28/2010 14:02:42

--------------------------------------------------------------------
| Elapsed | CPU | Io | Other | fetch | buffer | reads |
| Time (s) | waits (s) | CILS | gets |
--------------------------------------------------------------------
| 3.46 | 0.13 | 2.38 | 0.94 | 1 | 1752 | 1214 |
--------------------------------------------------------------------

SQL plan monitoring details
========================================================== ========================================================== ========================================================== ================================
| ID | operation | Name | rows | cost | time | START | starts | rows | activity detail |
| (Estim) | active (s) | (actual) | (percent) | (sample #) |
========================================================== ========================================================== ========================================================== ================================
| 0 | SELECT statement | 1 | + 5 | 1 | 1 |
| 1 | sort aggregate | 1 | + 5 | 1 | 1 |
| 2 | approximate OPC aggregate | 2 | + 4 | 1 | 58610 |

Report_ SQL _monitor
Certificate --------------------------------------------------------------------------------------------------------------------------------------------------
| 3 | table access full | 5 | + 1 | 1 | 86645 | 100.00 | CPU (1) |
| DB file scattered read (3) |
========================================================== ========================================================== ========================================================== ================================

This is another enhancement in Oracle Database Automation diagnosis.

-The end-