Tools available for monitoring Database Operations

If you run into performance issues with your Oracle database, you will need a way to monitor the operations of the database. Oracle includes two tools to examine the way that the SQL submitted to the database is operating and one new dynamic table to help you understand certain long-running database operations.

• EXPLAIN PLAN: The EXPLAIN PLAN utility lets you query the optimizer as to which execution plan is going to be used for a particular query. The EXPLAIN PLAN command is followed by a SQL query, and it returns a table that lists each step in the execution plan, the database objects it will use for that step, and the methods used to accomplish that step.
• TKPROF: Whereas EXPLAIN PLAN is used to query the optimizer dynamically, the TKPROF utility works off a SQL trace file. The SQL Trace utility gathers and stores a variety of statistics for database operations as they are occurring. The TKPROF utility gives you the ability to retrieve the information from the trace file, nicely formatted and organized. Part of the information you can retrieve includes the query execution plan, as well as information on the amount and type of resources used by a query. You can also sort the output from TKPROF, so you can use this tool to determine which queries are taking up the most resources or time.

V$SESSION_LONGOPS displays the status of various operations that run for longer than 6 seconds (in absolute time). These operations currently include many backup and recovery functions, statistics gathering, and query execution, and more operations are added for every Oracle release.
V$SESSION_LONGOPS is still used as a dynamic performance view in Oracle 19c. It remains a valuable tool for monitoring the progress of long-running operations within the database.
What is V$SESSION_LONGOPS? `V$SESSION_LONGOPS` is a dynamic performance view that provides details about operations that run for a long time, such as:

• Full Table Scans
• Parallel Queries
• Hash Joins
• Index Builds
• RMAN Backup and Recovery
• Sorting and Table Moves

Is It Still Relevant in Oracle 19c?
Yes, it continues to be "actively maintained" and provides real-time insights into session progress. It is particularly useful in Enterprise Manager (OEM) Performance Hub and SQL Monitoring to track "session activities and execution status".
How to Use It in Oracle 19c

1. Checking Long-Running Operations

   SELECT SID, SERIAL#, OPNAME, TOTALWORK, SOFAR, 
          (SOFAR/TOTALWORK)*100 AS PERCENT_COMPLETE, TIME_REMAINING, ELAPSED_SECONDS
   FROM V$SESSION_LONGOPS
   WHERE SOFAR <> TOTALWORK;
       

   • SOFAR <> TOTALWORK ensures you see only active operations.
   • PERCENT_COMPLETE helps track progress.
2. Monitoring Specific SQL Queries

   SELECT SID, SERIAL#, SQL_ID, OPNAME, ELAPSED_SECONDS, TIME_REMAINING
   FROM V$SESSION_LONGOPS
   WHERE SQL_ID = 'your_sql_id_here';
       

   • Helps track the progress of a specific SQL statement.
3. Identifying Long-Running RMAN Jobs

   SELECT SID, SERIAL#, OPNAME, MESSAGE, TIME_REMAINING, ELAPSED_SECONDS
   FROM V$SESSION_LONGOPS
   WHERE OPNAME LIKE 'RMAN%';
       

   • Useful for monitoring RMAN backup and restore operations.

When Does V$SESSION_LONGOPS Update?

• Only certain long-running operations appear in this view.
• The update frequency depends on Oracle’s internal execution tracking.
• Some short transactions or operations with low execution time may not appear.

Alternative Monitoring Options in Oracle 19c

1. V$SESSION - To monitor active sessions and SQL execution:

   • SELECT SID, SERIAL#, STATUS, SQL_ID, EVENT, SECONDS_IN_WAIT
     FROM V$SESSION
     WHERE STATUS = 'ACTIVE';
             

2. V$SQL_MONITOR - For real-time SQL execution details:

   • SELECT SQL_ID, STATUS, ELAPSED_TIME, CPU_TIME, QUEUE_DURATION
     FROM V$SQL_MONITOR;
             

3. DBA_HIST_ACTIVE_SESS_HISTORY (ASH Analytics)
   • Provides historical performance insights.

Conclusion `V$SESSION_LONGOPS` is still used in Oracle 19c for tracking long-running operations. However, for more real-time insights, tools like V$SQL_MONITOR and ASH Analytics are also recommended.

There are a number of dynamic tables in the Oracle data dictionary and these tables are updated while the database is running. All these tables begin with the prefix V$. In Oracle, there is a new dynamic table for monitoring some long-running operations, such as backup and recovery operations. This table is called V$ SESSION_LONGOPS. The table includes columns such as:

1. UNITS: The unit of measurement for the work
2. TOTALWORK: The total number of work units estimated for the job
3. SOFAR: The number of work units completed so far
4. LASTUPDATETIME: The last time the table was updated
5. ELAPSEDTIME: The total time elapsed since the operation began

This view displays the status of various operations that run for longer than 6 seconds (in absolute time). These operations currently include many backup and recovery functions, statistics gathering, and query execution, and more operations are added for every Oracle release. To monitor query execution progress, you must be using the cost-based optimizer and you must:

1. Set the TIMED_STATISTICS or SQL_TRACE parameter to true
2. Gather statistics for your objects with the ANALYZE statement or the DBMS_STATS package

You can add information to this view about application-specific long-running operations by using the DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS procedure.