Long running queries in oracle causing Performance Issue.
Queries to monitoring
· This one shows SQL that is currently
"ACTIVE":-
select S.USERNAME,
s.sid, s.osuser, t.sql_id, 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 <> 'SYSTEM'
order by s.sid,t.piece
/
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 <> 'SYSTEM'
order by s.sid,t.piece
/
Long running sessions, -Information (users, Logon Time, Current time)
SELECT username,sid, serial#,
TO_CHAR(sysdate,'YYYY-MM-DD HH24:MI:SS') "CURRENT",
TO_CHAR(logon_time,'YYYY-MM-DD HH24:MI:SS') "LOGON",
(sysdate - logon_time)*24*60 "MINS"
FROM V$SESSION
WHERE (sysdate - logon_time)*24*60 > 1
AND username is not NULL;
Find out the following
- Which user is currently logged-on?
- Which SQL Query are they running?
- Which computer the user is logged on from?
- How long the query is running?
SELECT SUBSTR(SS.USERNAME,1,8) USERNAME, SS.OSUSER "USER", AR.MODULE || ' @ ' || SS.machine CLIENT, SS.PROCESS PID, TO_CHAR(AR.LAST_LOAD_TIME, 'DD-Mon HH24:MM:SS') LOAD_TIME, AR.DISK_READS DISK_READS, AR.BUFFER_GETS BUFFER_GETS, SUBSTR(SS.LOCKWAIT,1,10) LOCKWAIT, W.EVENT EVENT, SS.status, AR.SQL_fullTEXT SQL FROM V$SESSION_WAIT W, V$SQLAREA AR, V$SESSION SS, v$timer T WHERE SS.SQL_ADDRESS = AR.ADDRESS AND SS.SQL_HASH_VALUE = AR.HASH_VALUE AND SS.SID = w.SID (+) AND ss.STATUS = 'ACTIVE' AND W.EVENT != 'client message' ORDER BY SS.LOCKWAIT ASC, SS.USERNAME, AR.DISK_READS DESC;
Long running query information -USERNAME, SID, SERIAL, CURRENT TIME AND DATE, LOGON-DATE AND TIME, MINS
SELECT username,sid, serial#,
TO_CHAR(CURRENT_TIMESTAMP,'DD-MON-YYYY HH24:MI:SS') AS curr,
TO_CHAR(start_time,'DD-MON-YYYY HH24:MI:SS') AS logon,
(sysdate - start_time)*24*60 AS mins
FROM V$SESSION_LONGOPS
WHERE username is not NULL
AND (SYSDATE - start_time)*24*60 > 1 ;
The below one creates a trigger to trace the IP address of the user that is logged on.
select username||'('||sid||','||serial#||')' username, module, action, client_info from v$session where module||action||client_info is not null;
· This shows locks. Sometimes things are going slow, but
it's because it is blocked waiting for a lock:
select
object_name,
object_type,
session_id,
type,
-- Type or system/user
lock
lmode,
-- lock mode in which session holds lock
request,
block,
ctime
-- Time since current
mode was granted
from
v$locked_object,
all_objects, v$lock
where
v$locked_object.object_id = all_objects.object_id AND
v$lock.id1 = all_objects.object_id AND
v$lock.sid = v$locked_object.session_id
order by
session_id, ctime desc, object_name
/
·
This is a good one for finding long operations (e.g. full table scans). If it
is because of lots of short operations, nothing will show up.
COLUMN percent FORMAT 999.99
SELECT sid, to_char(start_time,'hh24:mi:ss') stime,
message,( sofar/totalwork)* 100 percent
FROM v$session_longops
WHERE sofar/totalwork < 1
/
Terminating
Sessions
Command :
ALTER SYSTEM KILL SESSION ‘7.15’
To identify
the SID and the SERIAL number query V$SESSION
SELECT SID,
SERIAL#, STATUS
FROM V$SESSION
WHERE USERNAME = 'USERNAME';
SID
SERIAL# STATUS
----- ---------
--------
7
15 ACTIVE
12
63 INACTIVE
A
session is ACTIVE when it is making a SQL call to Oracle
Database. A session is INACTIVE if it is not making a SQL call to the
database
Terminiation an inactive session
SELECT SID,SERIAL#,STATUS,SERVER
FROM V$SESSION
WHERE USERNAME = 'USERNAME';
SID SERIAL#
STATUS SERVER
----- -------- --------- ---------
7 15
INACTIVE DEDICATED
12 63
INACTIVE DEDICATED
2 rows selected.
ALTER
SYSTEM KILL SESSION '7,15';
Statement processed.
If you find an oracle process is
consuming a high amount of CPU, you can find the sql_text that is related to
that process id using the following code:
SELECT SQL_TEXT
FROM v$sql
WHERE sql_id = (SELECT sql_id FROM v$session WHERE paddr = (sELECT addr
FROM v$process
WHERE spid = '&process_id'));
FROM v$sql
WHERE sql_id = (SELECT sql_id FROM v$session WHERE paddr = (sELECT addr
FROM v$process
WHERE spid = '&process_id'));
You will need to find the process id using either
top (linux), topas -P (aix), or task manager (windows). Equally you can use
Enterprise Manager to find this information, by looking under the performance
monitor and any alerts regarding tuning / high CPU consumption.
FIND A LONG RUNNING
QUERY
SELECT TOP 10
ObjectName
= OBJECT_NAME(qt.objectid)
,DiskReads
= qs.total_physical_reads -- The worst reads, disk
reads
,MemoryReads
= qs.total_logical_reads --Logical Reads are memory
reads
,Executions
= qs.execution_count
,AvgDuration
= qs.total_elapsed_time / qs.execution_count
,CPUTime
= qs.total_worker_time
,DiskWaitAndCPUTime
= qs.total_elapsed_time
,MemoryWrites
= qs.max_logical_writes
,DateCached
= qs.creation_time
,DatabaseName
= DB_Name(qt.dbid)
FROM
sys.dm_exec_query_stats AS qs
CROSS APPLY
sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid =
db_id() -- Filter by current database
ORDER BY
qs.total_elapsed_time DESC
FIND A LONG RUNNING QUERY
SELECT username,sid, serial#,
2
TO_CHAR(CURRENT_TIMESTAMP,'HH24:MI:SS') AS curr,
3
TO_CHAR(start_time,'HH24:MI:SS') AS logon,
4
(sysdate - start_time)*24*60 AS mins
5 FROM V$SESSION_LONGOPS
6 WHERE
username is not NULL
7 AND (SYSDATE -
start_time)*24*60 > 1 ;
/*To Find SQL Text from SID*/
select a.sid,a.program,b.sql_text
from v$session a, v$sqltext b
where a.sql_hash_value = b.hash_value
and a.sid=429
order by a.sid,hash_value,piece;
/*To Find SQL Text from SID*/
select b.sid, b.serial#,b.username,a.sql_text, a.address, a.hash_value from v$sqltext a,v$session b where a.address=b.sql_address and b.sid='&SID';
/* This below query Gives you the SQL TEXT */ - Give the SID in the query that you found and execute it.
select a.sid,a.program,b.sql_text
from v$session a, v$sqltext b
where a.sql_hash_value = b.hash_value
and a.sid=281
order by a.sid,hash_value,piece;
