- Trending Categories
- Data Structure
- Operating System
- C Programming
- 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?
You want to monitor currently executing SQL statistics in Oracle.
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.
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;
- 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 find PHP execution time?
- Real-Time Embedded Systems
- How to identify the SQL consuming more resources in Oracle?
- How to convert varchar “time” to real time in MySQL?
- Difference between Oracle and SQL Server
- How to calculate elapsed/execution time in Java?
- How to measure the execution time in Golang?
- Block of PL/SQL in Oracle DBMS