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.

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.

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.

The `V$SESSION_LONGOPS` is still a dynamic view available and widely used for performance tuning in Oracle 12c.
Here's why it remains relevant:

• Identifying long-running operations: It displays details of database operations exceeding a specific time threshold (usually 6 seconds by default), helping you pinpoint potential performance bottlenecks like long-running queries, backups, or DDL statements.
• Monitoring operation progress: Tracking metrics like elapsed time, completed work units, and total work units provides insights into the progress of these long-running tasks.
• Troubleshooting performance issues: Analyzing `V$SESSION_LONGOPS` data can help diagnose the cause of slow applications or database lag, allowing you to optimize queries, adjust parameters, or investigate further.
• Understanding resource consumption: You can identify sessions consuming high CPU, memory, or I/O resources, leading to informed decisions about workload distribution and resource allocation.

Limitations:

• It only captures operations exceeding the time threshold, which might miss shorter but impactful performance issues.
• The information provided might not be sufficient for in-depth analysis, requiring complementary diagnostic tools or techniques.

Alternatives:

• `V$ACTIVE_SESSION_HISTORY`tracks historical performance data for all sessions, enabling comprehensive analysis beyond long-running operations.
• `dbms_job.run_job` and `dbms_scheduler.jobs` offer job control and scheduling functionalities, providing insights into background processes not captured by `V$SESSION_LONGOPS`.

While new versions of Oracle Database introduce additional monitoring features, `V$SESSION_LONGOPS` remains a valuable tool for identifying and understanding long-running operations in Oracle 12c. Combining it with other methods and data sources can significantly enhance your performance analysis and tuning efforts.

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.