how to check running queries in Oracle | Performance Tips

If you have a problem with how to check running queries in Oracle? The answer is using System views like V$SESSION, V$SQLTEXT_WITH_NEWLINES, and V$SQL to retrieve information about running queries now. Use active sessions to retrieve the required information.

V$SESSION : View is a dynamic performance view for providing information about sessions that are connected with the Oracle database. This is available in ACTIVE and INACTIVE sessions that are currently used.

V$SQL: This view includes all the SQL statements currently available in the shared SQL pool. This view contains information like elapsed time, executions, and many other information.

V$SQLTEXT_WITH_NEWLINES: This is similar to the V$SQL view and this contains full SQL text for more readability.

V$SESSION_LONGOPS: this is more on a performance-related view. This contains long-running operations like table creations, table rebuilding, large data lord, and heavy operations. This helps to check the progress of some operations and their related information. This contains some information below

• OPNAME: Operation name.
• SOFAR: The number of work units completed so far.
• TOTALWORK: The total number of work units for the operation.
• UNITS: The unit of measurement for work (e.g., rows, blocks).
• START_TIME: The start time of the operation.

We can use the Above views to find out current executive quarries in the database. That may help to improve performance sometimes. Because sometimes we need to find out long-running queries or never-ending queries. I will show some small examples below to retrieve related information.

Query 1: Basic Session Information:

This is basic information about current sessions in the database. how to check running queries in Oracle? Wait this does not include SQL text use V$SQL to retrieve SQL-related information by joining each table using SQL_ID.

--
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.machine,
    s.program,
    s.status
FROM
    v$session s
WHERE
    s.status = 'ACTIVE';
--

• SID: Session ID.
• SERIAL#: Serial number.
• USERNAME: Oracle username.
• MACHINE: your machine ID from which the user is connected.
• PROGRAM: Program executing the SQL.
• STATUS: Status of the session (e.g., ACTIVE).

Query 2: Detailed SQL Information:

This provides some additional information about SQL statements and whether SQL is currently running or not.

--
SELECT
    s.username,
    s.sid,
    s.serial#,
    s.machine,
    s.program,
    s.status,
    s.sql_id,
    s.sql_child_number,
    s.sql_exec_start,
    q.sql_text
FROM
    v$session s
JOIN
    v$sql q ON s.sql_id = q.sql_id
WHERE
    s.status = 'ACTIVE';
--

• SQL_ID: ID of the SQL. This is used to join the table
• SQL_EXEC_START: Time when the SQL execution started.
• SQL_TEXT: The text of the SQL statement.

Query 3: Currently Executing SQL Statements:

This is the focus of this article you can find currently running queries by or already started.

--
SELECT
    s.username,
    s.sid,
    s.serial#,
    s.machine,
    s.program,
    s.status,
    s.sql_id,
    s.sql_child_number,
    s.sql_exec_start,
    q.sql_text
FROM
    v$session s
JOIN
    v$sql q ON s.sql_id = q.sql_id
WHERE
    s.status = 'ACTIVE'
    AND s.sql_exec_start IS NOT NULL;
--

Query 4: Top SQL Statements by Elapsed Time:

This shows all the queries that have higher elapsed time. I will retrieve the first 10 records only

--
SELECT
    sql_text,
    executions,
    elapsed_time / 1000000 as elapsed_seconds,
    cpu_time / 1000000 as cpu_seconds,
    (elapsed_time - cpu_time) / 1000000 as wait_seconds
FROM
    v$sql
ORDER BY
    elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;
--

• ELAPSED_SECONDS: Elapsed time for the SQL statement.
• CPU_SECONDS: CPU time consumed by the SQL statement.
• WAIT_SECONDS: Wait time for the SQL statement.

This is helping to improve the performance of the database. Sometimes queries have more execution time in the database in this case we can use the above query to find out more elapsed time on the database.

Sometimes we create an index on a table to retrieve information quickly. So before adding indexes may need to find out running queries in the pool these queries help to improve them. Watch our performance tips on Oracle.

Query 5: Currently Executing SQL Statements with Details:

--
select s.username,
    s.sid,
    s.serial#,
    s.machine,
    s.program,
    s.status,
    s.sql_id,
    s.sql_child_number,
    s.sql_exec_start,s.module,sql_text
from v$sqltext_with_newlines t,V$SESSION s
where t.address = s.sql_address
and t.hash_value = s.sql_hash_value
and s.status = 'ACTIVE'
and s.username is not null
 AND s.sql_exec_start IS NOT NULL
order by s.sid,t.piece;
--

I have added some examples to find out currently running SQL statements and sessions. I hope these queries help to solve the problem of how to check running queries in Oracle.  

Note: you may need additional privileges to view the above information. Also, some Oracle versions may not be available for all the above views.