Table of Contents

Oracle Long Running Queries | Health Check Now

Share on facebook
Share on twitter
Share on linkedin
Share on email
Share on whatsapp
Share on pinterest
Share on print
Oracle Long Running Queries

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.

oracle COALESCE vs NVL performance
NVL or COALESCE
4 pillars of OOP
4 Pillars of OOP
connect by prior in Oracle performance issue
Connect by Prior in Oracle Performance Issue

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

Share on facebook
Share on twitter
Share on linkedin
Share on email
Share on whatsapp
Share on pinterest
Share on print

Leave a Comment

Your email address will not be published. Required fields are marked *

Related Posts

You May Like
Subscribe to our Newsletter
Related