Oracle Currently Executing SQL via v$sqlarea Monitoring

calendar Sep 2, 2024 / · 9 min read · Performance Monitoring v$sqlarea v$sql v$session  ·
Share on: linkedin copy

Oracle Database: Show Currently Executing SQL Statements

Purpose

The help desk reports that the database is slow, and you open a session to look. You can see that sessions are connected, but you need to know what they are running. Session names do not tell you that. The SQL text does. It shows whether the load is a report, a batch job or one bad statement that someone started by mistake.

The query below reads V$SQLAREA and returns the text of every statement that at least one user is executing at the moment you run it. The view holds one row for each distinct SQL string in memory, so the result is a short list of statements and not a list of sessions. A DBA runs it first, before any deeper tool, to learn what kind of work the database is doing.

A third query joins V$SESSION to V$SQL. It adds the session ID, the user name and the start time of each running statement. Use the first query to see the workload. Use the third query when you must find who is running a statement. To list connected users only, see the page on connected sessions.

Code

 1-- 1. SQL text that at least one user is executing right now
 2select sql_text
 3from v$sqlarea
 4where users_executing > 0
 5/
 6
 7-- 2. The same statements with the full text, runtime and owner
 8select sql_id,
 9       users_executing,
10       executions,
11       round(elapsed_time / 1000000, 1) as elapsed_seconds,
12       sql_fulltext
13from v$sqlarea
14where users_executing > 0
15order by elapsed_time desc
16/
17
18-- 3. Which session runs which statement
19select s.sid,
20       s.serial#,
21       s.username,
22       s.sql_exec_start,
23       q.sql_id,
24       q.child_number,
25       q.sql_text
26from v$session s
27join v$sql q
28  on q.sql_id = s.sql_id
29 and q.child_number = s.sql_child_number
30where s.status = 'ACTIVE'
31  and s.username is not null
32order by s.sql_exec_start
33/

Code Breakdown

Query 1: the basic check

  • select sql_text: returns the sql_text column. This column holds the first thousand characters of the SQL text. The column sql_fulltext holds all characters.
  • from v$sqlarea: reads the dynamic performance view that displays statistics on shared SQL areas and contains one row per SQL string. It covers statements that are in memory, parsed and ready for execution.
  • where users_executing > 0: keeps only the statements that at least one user is running now. The column counts the total number of users executing the statement over all child cursors.
  • /: runs the statement in SQL*Plus and similar command-line tools.

Query 2: more detail for each statement

  • sql_id: the SQL identifier of the parent cursor in the library cache. Use it as the key when you look at a statement in other views.
  • executions: the total number of executions, totalled over all the child cursors. A high number with a low elapsed time is a normal short statement. A low number with a high elapsed time points to a long statement.
  • elapsed_time / 1000000: the elapsed time columns are in microseconds, so dividing by one million gives seconds. Keep in mind that this is time summed over all runs of the statement in the cache, not the time of the current run only.
  • sql_fulltext: a CLOB with all characters of the SQL text. Use it when a statement is longer than a thousand characters.
  • order by elapsed_time desc: puts the statements that have used the most time first.

Query 3: tie each statement to a session

  • from v$session s: the view displays session information for each current session.
  • s.status = 'ACTIVE': a session with this status is currently executing SQL.
  • s.sql_id and s.sql_child_number: the SQL identifier and the child number of the statement that the session is executing now. V$SQL has the columns sql_id and child_number, so the two views join on both columns.
  • s.sql_exec_start: the time when the execution of the current SQL started. It is null when sql_id is null.
  • s.username is not null: removes sessions without an Oracle user name from the list. Remove this filter if you also want to see background work.
  • join v$sql q: this view lists statistics on shared SQL areas without the GROUP BY clause, with one row for each child of the original SQL text.

Key Points

  • Dynamic performance views show the present. V$SQLAREA, V$SQL and V$SESSION describe what is in memory now. They are not a history. Query 3 lists only sessions that are executing SQL when you run it.
  • One row for each SQL string or for each child. V$SQLAREA has one row for each SQL string. V$SQL has one row for each child of the original SQL text. If two sessions run the same text with different child cursors, V$SQL shows two rows and V$SQLAREA shows one.
  • The text column is cut at 1,000 characters. sql_text is VARCHAR2(1000). For a long statement, select sql_fulltext. In V$SQL, the full text can be retrieved with this column instead of joining with the V$SQLTEXT view.
  • Long statements show their cost while they run. In V$SQL, statistics are normally updated at the end of query execution. For long running queries, they are updated every 5 seconds.
  • Elapsed time is database time. The elapsed_time column in V$SQL displays database time, not wall clock time. In some cases it can exceed the duration of the query, for example with parallel queries.
  • A statement with users_executing above zero is not always slow. The number says that someone is running it. It does not say how long it has run. Read the start time in query 3 for that.

Insights and Best Practices

Start with the text, then go to the session

The first query answers the question "what is the load?". Read the list from top to bottom. Look for statements you do not recognise, statements with no bind variables that repeat with different literals, and statements that appear in large numbers. After that, run query 3 to find the owner. This order saves time because the first query does not need a join.

Use sql_id as the key for the next step

Once you know the sql_id of a suspect statement, you can look at its plan. The DISPLAY_CURSOR function of the DBMS_XPLAN package displays the actual execution plan used to run a query stored in the cursor cache. Its sql_id parameter is the SQL ID of the statement in the cursor cache, and if you omit it, the last cursor executed by the session is displayed. The user needs SELECT or READ privileges on the fixed views V$SQL_PLAN, V$SESSION and V$SQL_PLAN_STATISTICS_ALL. This is the usual path from "which statement" to "why is it slow".

Compare the last active time

The view also has the column last_active_time, which is the time at which the query plan was last active. If you want to see statements that ran recently but are not running now, remove the users_executing filter and sort by this column.

Run it more than once

One run is a photograph of one moment. Run the query three times with a gap of a few seconds. A statement that appears each time is part of the steady load. A statement that appears once is probably short. Use sql_exec_start in query 3 to confirm: the same start time across runs means the same execution, while a new start time means a new execution of the same text.

Keep output small

On a busy system the result can be long. Wrap the query in an outer select and filter on rownum to keep the top rows. In SQL*Plus, use set long 2000 before you select sql_fulltext, so the CLOB does not print cut short.

Read-only means safe, but not free

All three queries only read. They do not change the database. Run them by hand when needed and do not put them in a tight loop.

When to Run This

  • Users report a slow application and you do not yet know which statement is the cause.
  • CPU use on the server is high and you want to see which SQL is running.
  • A batch job is late and you want to check that its statements are still active.
  • You see many sessions in the session list and want to group them by statement.
  • You have a sql_id from an alert or a ticket and want to see whether it is running now.
  • After a deployment, to confirm that the new code runs the statements you expect.

Troubleshooting Common Issues

The first query returns no rows. No statement had a user executing it at that moment. Run it again while the problem is happening. If sessions seem stuck, check V$SESSION for their wait event instead.

You get "table or view does not exist". The account cannot see the V$ views. Ask the DBA team for read access to the dynamic performance views. Use a role or a grant that your site approves.

Query 3 returns fewer rows than the number of active sessions. A session that has no sql_id at that moment cannot join. The session also has a prev_sql_id column with the SQL identifier of the last statement executed, which you can use for a session that just finished a call.

The same text appears twice in query 3. Two sessions run the same statement, or one statement has two child cursors. Compare child_number and sid to see which case it is.

The text is cut off. Select sql_fulltext and set a larger long value in SQL*Plus.

The elapsed time looks too high. The column is database time in microseconds, and for parallel queries it can exceed the duration of the query.

References

Posts in this series