Oracle Database - Current Performance
Posted: 11/2/2018 3:26:11 PMBy: PrintableKanjiEmblem
Times Read: 2,015
Likes: 0 Dislikes: 0
Topic: Programming: Web Applications
A few good queries to find out why Oracle is running slow...
- Show current table locks:
select
object_name,
object_type,
session_id,
type, -- Type of lock
lmode, -- lock mode
request,
block,
ctime -- Time elapsed
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
- Currently running queries:
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
- Long-running operations:
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
Rating: (You must be logged in to vote)