Suppose you have a SQL statement that runs a long time, and you want to be able to monitor the progress of the statement and find out when it will finish.
By viewing information for a long-running query in the V$SESSION_LONGOPS data dictionary view, you can gauge about when a query will finish. Let’s say you are running the following query, with join conditions, against a large table:
SELECT last_name, first_name FROM employees_big
WHERE last_name = 'EVANS';
With a simple query against the V$SESSION_LONGOPS view, you can quickly get an idea of how long the query will execute, and when it will finish:
SELECT username, target, sofar blocks_read, totalwork total_blocks,
round(time_remaining/60) minutes
FROM v$session_longops
WHERE sofar <> totalwork
and username = 'HR';
USERNAME TARGET BLOCKS_READ TOTAL_BLOCKS MINUTES
----------------- ----------------------------- ---------------------- ----------------------- --------------
HR HR.EMPLOYEES_BIG 81101 2353488 10
As the query progresses, you can see the BLOCKS_READ column increase, as well as the MINUTES column decrease. It is usually necessary to place the WHERE clause to eliminate rows that have been completed, which is why in the foregoing query it asked for rows where the SOFAR column did not equal TOTALWORK.
How It Works
In order to be able to monitor a query within the V$SESSION_LONGOPS view, the following requirements apply:
This view can contain information on SELECT statements, DML statements such as UPDATE, as well as DDL statements such as CREATE INDEX. Some common operations that find themselves in the V$SESSION_LONGOPS view include table scans, index scans, join operations, parallel operations, RMAN backup operations, sort operations, and Data Pump operations.
By viewing information for a long-running query in the V$SESSION_LONGOPS data dictionary view, you can gauge about when a query will finish. Let’s say you are running the following query, with join conditions, against a large table:
SELECT last_name, first_name FROM employees_big
WHERE last_name = 'EVANS';
With a simple query against the V$SESSION_LONGOPS view, you can quickly get an idea of how long the query will execute, and when it will finish:
SELECT username, target, sofar blocks_read, totalwork total_blocks,
round(time_remaining/60) minutes
FROM v$session_longops
WHERE sofar <> totalwork
and username = 'HR';
USERNAME TARGET BLOCKS_READ TOTAL_BLOCKS MINUTES
----------------- ----------------------------- ---------------------- ----------------------- --------------
HR HR.EMPLOYEES_BIG 81101 2353488 10
As the query progresses, you can see the BLOCKS_READ column increase, as well as the MINUTES column decrease. It is usually necessary to place the WHERE clause to eliminate rows that have been completed, which is why in the foregoing query it asked for rows where the SOFAR column did not equal TOTALWORK.
How It Works
In order to be able to monitor a query within the V$SESSION_LONGOPS view, the following requirements apply:
- The query must run for six seconds or greater.
- The table being accessed must be greater than 10,000 database blocks.
- TIMED_STATISTICS must be set or SQL_TRACE must be turned on.
- The objects within the query must have been analyzed via DBMS_STATS or ANALYZE.
This view can contain information on SELECT statements, DML statements such as UPDATE, as well as DDL statements such as CREATE INDEX. Some common operations that find themselves in the V$SESSION_LONGOPS view include table scans, index scans, join operations, parallel operations, RMAN backup operations, sort operations, and Data Pump operations.
No comments:
Post a Comment