- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- 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 determine the approximate amount of SQL work left in Oracle?
Problem:
You want to know how much longer a long running SQL might ake to finish.
Solution
We can use “V$SESSION_LONGOPS" view to know the approximate time of a query left to execute. "V$SESSION_LONGOPS” view displays the status of various database operations that have been running for longer than six seconds. Please note that this view give you only a rough estimate of when a SQL might complete.
Example
select a.username ,a.opname ,b.sql_text ,to_char(a.start_time,'DD-MON-YY HH24:MI') start_time ,a.elapsed_seconds how_long ,a.time_remaining secs_left ,a.sofar ,a.totalwork ,round(a.sofar/a.totalwork*100,2) percent from v$session_longops a ,v$sql b where a.sql_address = b.address and a.sql_hash_value = b.hash_value and a.sofar <> a.totalwork and a.totalwork != 0;
In order, to view the results properly via SQLPLUS, set he below parameters first.
Example
SET LINESIZE 141 TRIMSPOOL ON PAGES 66 COL username FORMAT A8 HEAD "User|Name" COL opname FORMAT A16 HEAD "Operation|Type" COL sql_text FORMAT A33 HEAD "SQL|Text" TRUNC COL start_time FORMAT A15 HEAD "Start|Time" COL how_long FORMAT 99,990 HEAD "Time|Run" COL secs_left FORMAT 99,990 HEAD "Appr.|Secs Left" COL sofar FORMAT 9,999,990 HEAD "Work|Done" COL totalwork FORMAT 9,999,990 HEAD "Total|Work" COL percent FORMAT 999.90 HEAD "%|Done" select a.username ,a.opname ,b.sql_text ,to_char(a.start_time,'DD-MON-YY HH24:MI') start_time ,a.elapsed_seconds how_long ,a.time_remaining secs_left ,a.sofar ,a.totalwork ,round(a.sofar/a.totalwork*100,2) percent from v$session_longops a ,v$sql b where a.sql_address = b.address and a.sql_hash_value = b.hash_value? and a.sofar <> a.totalwork and a.totalwork != 0;
- Related Questions & Answers
- How to capture Oracle errors in PL/SQL?
- How to identify the SQL consuming more resources in Oracle?
- Block of PL/SQL in Oracle DBMS
- How to translate SQL data to XML in Oracle?
- How to validate the syntax of a Oracle dynamic SQL before execution ?
- How to identify SQL queries with the most waits in Oracle?
- How to monitor real time SQL execution statistics in Oracle?
- Difference between Oracle and SQL Server
- How to display a SQL execution progress along with execution plan in Oracle?
- Program to find the formatted amount of cents of given amount in Python
- Database Wars: MSSQL Server, Oracle PL/SQL and MySQL
- How to determine the Cost of Capital of a project?
- Complete and Approximate Equivalent Circuits of Induction Motor
- Difference between oracle golden gate and oracle active guard in the oracle
- How to determine the efficiency of an Induction Motor?