In a daily day to day activities it is required for an Oracle Dba to check long running sessions and long running jobs and tune them or kill them if required.
In Oracle Database, a "long-running session" typically refers to a database session that has been active for an extended period of time. A session in the context of a database is a user's connection to the database, and it can be initiated by applications, users, or background processes.
Performance Impact: Long-running sessions can sometimes negatively impact the performance of the database. These sessions might hold locks on database objects for an extended duration, blocking other sessions from accessing those objects and potentially leading to deadlocks.
Resource Utilization: Long-running sessions consume database resources such as CPU, memory, and I/O for an extended period. This can affect the overall system's resource availability and may lead to resource contention.
Transaction Management: In a long-running session, it's important to manage transactions effectively. Long-running transactions can make it difficult to recover from failures and can result in large rollback segments if the transaction needs to be rolled back.
Connection Pooling: In some cases, long-running sessions can disrupt connection pooling mechanisms that are commonly used to manage database connections efficiently. If sessions are not released promptly, connection pools may become exhausted, impacting application scalability.
Diagnosis and Monitoring: Long-running sessions can be a sign of issues in the application or database design. Database administrators and developers need to monitor and diagnose long-running sessions to identify performance bottlenecks and optimize database operations.
To address long-running sessions, it's important to:
1.Review and optimize SQL queries and transactions to minimize their impact on the database.
2.Set appropriate timeouts for database connections and transactions.
3.Implement deadlock detection and resolution mechanisms.
4.Use database monitoring and profiling tools to identify and address long-running sessions proactively.
5.Consider implementing session or resource limits to prevent runaway sessions from consuming excessive resources.
6.Managing long-running sessions effectively is an important aspect of database performance tuning and application development to ensure that the database operates efficiently and reliably.
Below are some of the sql queries to find out if there is any long running session or long running jobs
****very good Query to check sql_id's monitor or executing currently*****
set lines 1000 pages 9999
column sid format 9999
column serial for 999999
column status format a15
column username format a10
column sql_text format a80
column module format a30
col program for a30
col SQL_EXEC_START for a20
SELECT * FROM
(SELECT status,inst_id,sid,SESSION_SERIAL# as Serial,username,sql_id,SQL_PLAN_HASH_VALUE,
MODULE,program,
TO_CHAR(sql_exec_start,'dd-mon-yyyy hh24:mi:ss') AS sql_exec_start,
ROUND(elapsed_time/1000000) AS "Elapsed (s)",
ROUND(cpu_time /1000000) AS "CPU (s)",
substr(sql_text,1,30) sql_text
FROM gv$sql_monitor where status='EXECUTING' and module not like '%emagent%'
ORDER BY sql_exec_start desc
);
SELECT SID, SERIAL#,OPNAME, CONTEXT, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM GV$SESSION_LONGOPS WHERE OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;
or
SET LINESIZE 100
SET PAGESIZE 100
SET TRIMOUT ON
SET TRIMSPOOL ON
COLUMN job_name FORMAT A20
COLUMN state FORMAT A15
COLUMN percent_completed FORMAT 999.99
SELECT sid, serial#, username, sql_id, start_time,
ROUND((sofar/totalwork)*100, 2) AS percent_completed
FROM v$session_longops
WHERE sofar <> totalwork;
SELECT s.sid, s.serial#, s.username, s.sql_id, l.start_time,
ROUND((l.sofar/l.totalwork)*100, 2) AS percent_completed,
q.sql_text
FROM v$session_longops l
JOIN v$sql q ON l.sql_id = q.sql_id
JOIN v$session s ON l.sid = s.sid AND l.serial# = s.serial#
WHERE l.sofar <> l.totalwork;
or
set lines 300
col TARGET for a40
col SQL_ID for a20
select SID,TARGET||OPNAME TARGET, TOTALWORK, SOFAR,TIME_REMAINING/60 Mins_Remaining, ELAPSED_SECONDS, SQL_ID from v$session_longops where TIME_REMAINING>0 order by TIME_REMAINING;
or
set lines 789 pages 789
COL OPNAME FOR A40
COL USERNAME FOR A15
col message for a70
select sid,serial#, to_char(START_TIME,'dd-mm-yy hh:mi:ss ') "Start Time", username, SQL_ID, OPNAME, message,SOFAR,TOTALWORK, round((SOFAR/TOTALWORK)*100) pct_completed,TIMESTAMP,TIME_REMAINING from v$session_longops where TOTALWORK>1 and sid in (select a.sid FROM gv$session a where a.status='ACTIVE') and SOFAR!=TOTALWORK ;
or
set lines 150
set pages 999
col username format a12
col message format a100
col strt format a10
col updtd format a10
col "%" format 999
break on username skip 1
select username,to_char(start_time,'dd hh24:mi') strt,to_char(last_update_time,'dd hh24:mi') updtd,round(sofar/(totalwork+1)*100) "%", MESSAGE from gv$session_longops where (SID,SERIAL#) IN (select sid,serial# from gv$session where status ='ACTIVE') order by username,strt;
