Apps DBA Daily Essential or Important SQL Queries

Apps DBA Daily Essential or Important SQL Queries

Daily Essential or Important SQL Queries for Oracle Apps DBA and DBA

Query to check Session stay:

If you observe any performance issues on our database, long running concurrent requests process. then first check the any events like "enq: TX - row lock contention","enq: TM Lock Contention",

"library cache pin" . by run the below query and identify the session id (SID)

set lines 120

set pages 1000

col event for a30

select sid, event, p1, p2, p3, p1raw from v$session_wait

where event not like '%messag%' and event not in ('pipe get','PL/SQL lock timer','Streams AQ: qmn slave idle wait','Streams AQ: waiting for time management or cleanup tasks','Streams AQ: qmn coordinator idle wait')

and (wait_time=0 or state='WAITING')

order by 2;