Oracle databases have great ways of taking Oracle long running queries. I hope to add some examples of queries below. I would like to use some Oracle views for performance like V$SESSION, v$session_longops, and V$SQL. Oracle System view having such good records for details about long running queries in the database.
I just explain simple explanations about the above views for more understanding about queries. Please make sure you have the required privileges to access those System views.
- 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$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
1. Find Long Running SQL Queries
To find out the long running queries use V$SESSION_LONGOPS oracle view. As we describe above this is contains details about big operations running on a database.
-- SELECT SID,TARGET||OPNAME TARGET, TIME_REMAINING/60 Mins_Remaining,ELAPSED_SECONDS,SQL_ID , SERIAL#,OPNAME, CONTEXT, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM V$SESSION_LONGOPS WHERE OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK; --
The above query return is not completed and Oracle long running queries. Below are the details returned by view. You can change the query according to your needs.
- SID: Session ID associated with the operation.
- OPNAME: Operation name .
- TARGET: The object or target of the operation (e.g., table name, index name).
- TARGET_DESC: A description of the target.
- 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.
The above view returns all the records above the long running” threshold (6 seconds can be changed).
2. Querying AWR for historical long running SQL
-- select stat.sql_id, plan_hash_value, rpad(parsing_schema_name,10) "schema",elapsed_time_total/1000000 "seconds", elapsed_time_delta,disk_reads_delta, stat.executions_total, to_char(ss.end_interval_time,'dd-mm-yy hh24:mi:ss') "endtime", rpad(sql_text,40) text,ss.snap_id from dba_hist_sqlstat stat, dba_hist_sqltext txt, dba_hist_snapshot ss where stat.sql_id = txt.sql_id and stat.dbid = txt.dbid and ss.dbid = stat.dbid and ss.instance_number = stat.instance_number and stat.snap_id = ss.snap_id and parsing_schema_name not like 'sys%' and ss.begin_interval_time >= sysdate-40 and stat.elapsed_time_total/1000000 > 20 order by elapsed_time_total desc; --
AWR report contains a lot of details about the database. Those are historical records in specific periods. This is help to identify performance issues in databases and queries. There are some good articles here
3. Top Long-Running SQL Statements
-- SELECT sql_id, sql_text, executions, elapsed_time / 1000000 as elapsed_seconds FROM v$sql ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY; --
This query returns the first 10 records that take a lot of time to complete but the query should be available in the SQL pool. I ordered records in descending order by elapsed time.
4. Long-Running SQL Statements with High CPU Time
--
SELECT
sql_id,
sql_text,
executions,
cpu_time / 1000000 as cpu_seconds
FROM
v$sql
ORDER BY
cpu_time DESC
FETCH FIRST 10 ROWS ONLY;
--This is a similar type of query but sorted according to CPU time.
5. Identify Currently Active Sessions with Long Elapsed Time
--
SELECT
s.sid,
s.serial#,
s.username,
s.machine,
s.program,
s.status,
s.sql_id,
s.sql_child_number,
s.sql_exec_start,
q.sql_text,
q.elapsed_time
FROM
v$session s
JOIN
v$sql q ON s.sql_id = q.sql_id
WHERE
s.status = 'ACTIVE'
ORDER BY
elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;
--This query contains the SQL text and some SQL-related pieces of information may help to identify easily if there are your queries available. I’ll add some helping queries to fetch SQL text and other related session details.
Find Sql_id from v$session view
-- select sql_id from v$session where sid='618'; --
Find SQL text using sql_id
-- select sql_fulltext from V$sql where sql_id='bwwerwe7y9xn8grx' --
I added details of columns in v$session_longops but I would like to add details of columns in V$SESSION and V$SQL.
Column list of V$SESSION
- SID: Session ID, a unique identifier for each session.
- SERIAL#: Serial number for the session.
- USERNAME: Oracle username associated with the session.
- MACHINE: Client machine name from which the session is connected.
- PROGRAM: Name of the program or application that initiated the session.
- STATUS: Current status of the session (e.g., ACTIVE, INACTIVE, KILLED).
- LOGON_TIME: Time when the session was established.
- LAST_CALL_ET: Elapsed time since the last call.
- SQL_ID: SQL identifier for the currently executing SQL statement.
- SQL_CHILD_NUMBER: Child number for the currently executing SQL statement.
Column list of V$SQL
- SQL_ID: Unique identifier for a SQL statement.
- SQL_TEXT: The text of the SQL statement.
- PARSE_CALLS: Number of parse calls for the SQL statement.
- DISK_READS: Number of disk reads performed by the SQL statement.
- BUFFER_GETS: Number of buffer gets (logical reads) for the SQL statement.
- ROWS_PROCESSED: Number of rows processed by the SQL statement.
- ELAPSED_TIME: Elapsed time for the SQL statement.
- LAST_LOAD_TIME: Timestamp when the SQL statement was most recently loaded into the shared pool.
This contains Oracle long running queries and if you would interested in how to check running queries in Oracle there are separate ways. This post may help to identify some performance problems. If you need to check some deadlock or slow queries before adding some index or creating an index in the database identifying those queries is crucial.
https://www.dba-oracle.com/t_find_long_running_sql_queries.htm
