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.

There are a number of dynamic tables in the Oracle data dictionary. 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.