- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to monitor real time SQL execution statistics in Oracle?
Problem:
You want to monitor currently executing SQL statistics in Oracle.
Solution
If your database is Oracle Database 11g, you can use the following query to select from the “V$SQL_MONITOR” to monitor the near real time resource consumption of SQL queries.
The statistics in “V$SQL_MONITOR” are updated every second. This helps us to view the resource consumption as it updates. These statistics are gathered by default when a SQL statement runs in parallel or consumes more than 5 seconds of CPU or I/O time.
The “V$SQL_MONITOR" view includes a subset of statistics contained in the "V$SQL”, “V$SQLAREA", and "V$SQLSTATS” views.
The “V$SQL_MONITOR" view displays real-time statistics for each execution of a resource-intensive SQL statement, whereas "V$SQL”, “V$SQLAREA", and "V$SQLSTATS” contain over-all sets of statistics over multiple executions of a SQL statement.
Example
select * from ( select a.sid session_id ,a.sql_id ,a.status ,a.cpu_time/1000000 cpu_sec ,a.buffer_gets ,a.disk_reads ,b.sql_text sql_text from v$sql_monitor a ,v$sql b where a.sql_id = b.sql_id order by a.cpu_time desc) where rownum <=10;
- Related Articles
- How to display a SQL execution progress along with execution plan in Oracle?
- How to validate the syntax of a Oracle dynamic SQL before execution ?
- How to monitor temporary tablespace usage in Oracle?
- Execution time while running a SQL query in HANA Studio
- How to gather extended query execution stats in Oracle?
- How to capture Oracle errors in PL/SQL?
- How to translate SQL data to XML in Oracle?
- How to identify the SQL consuming more resources in Oracle?
- How to find PHP execution time?
- How to identify SQL queries with the most waits in Oracle?
- How to calculate elapsed/execution time in Java?
- How to measure the execution time in Golang?
- Block of PL/SQL in Oracle DBMS
- How to determine the approximate amount of SQL work left in Oracle?
- How to convert varchar “time” to real time in MySQL?
