1. Trace - We run the trace based on
particular activity. We require to generate trace based on particular activity
like concurrent report, or form which is leading to time consumption. Trace can
be run at the below level
- Trace can be run at respective
Form Level
- Trace need to be enabled at
Concurrent Define window.
- Trace fetching based on Request
ID
Once trace file is generated. Reveiw the trace file to find
out at which level there performance issue for a Particular SQL ID as is
mentioned below.
· Parsing
· Fetching
· Execution
· Parsing
· Fetching
· Execution
2. AWR Report:
AWR Report will be run every 1 hour as scheduled by DBA team. For this report
we will be able to find out Top 10 SQL ID which is causing performance issue.
Based on SQL ID, we will generated
3. SQLT Report: For particular SQL ID we have to generate
SQLT Report. Which will help us to
identify the number indexes created on particular table or Query which is
causing for SQL Execution issue.
Oracle Enterprise
Management (OEM)
From Oracle
Enterprise Management (OEM) window we will be able to find the load on the server. As
per the graph below, Blue indicates high load on the server.
`
Blocking Queries:
select to_char(a.LOGON_TIME,'DDMonYY HH24:MM'),a.sid,
a.serial#,c.NAME, a.STATUS,a.ACTION from V$SESSION a ,DBA_BLOCKERS
b,AUDIT_ACTIONS c
where a.sid = b.holding_session and a.command = c.action;
select /*+ rule */ 'SID '||a.sid||' is blocking the sessions
'||b.sid from v$lock a, v$lock b where a.block=1 and b.request >0;
SELECT c.owner, c.object_name, c.object_type, b.SID,
b.serial#, b.status, b.osuser, b.machine
FROM
v$locked_object a, v$session b,
dba_objects c
WHERE b.SID = a.session_id AND a.object_id =
c.object_id AND b.sid in ( select
holding_session from DBA_BLOCKERS ) ;
SELECT c.owner, c.object_name, c.object_type, b.SID,
b.serial#, b.status,b.osuser, b.machine FROM
v$locked_object a, v$session b,
dba_objects c
WHERE b.SID = a.session_id AND a.object_id = c.object_id AND
b.sid in ( select holding_session from DBA_BLOCKERS ) ;
SELECT c.owner, c.object_name, c.object_type, b.SID,
b.serial#, b.status, b.osuser, b.machine FROM
v$locked_object a, v$session b,
dba_objects c
WHERE b.SID = a.session_id AND a.object_id =
c.object_id AND b.sid in ( select
holding_session from DBA_BLOCKERS ) ;
SELECT session_id
FROM DBA_LOCKS WHERE BLOCKING_OTHERS = 'Blocking';
select * from DBA_WAITERS;
select * from dba_blockers;
select * from v$lock where block=1;
SELECT DECODE (request, 0, 'Holder: ', 'Waiter: ') || sid
sess,inst_id,id1, id2,lmode,request, TYPE
FROM gv$LOCK WHERE
(id1, id2, TYPE) IN (SELECT id1, id2, TYPE FROM gv$LOCK WHERE request > 0)
ORDER BY id1, request;
SELECT 'alter system kill session ''' || s.sid || ',' ||
s.SERIAL# || ','||'@'||''|| i.instance_number || ''';' a,'ps -ef |grep
LOCAL=NO|grep ' || p.SPID SPID,
'kill -9 ' || p.SPID,s.status FROM gv$session s, gv$process
p,gv$instance i WHERE ( (p.addr(+) = s.paddr) AND (p.inst_id(+) = s.inst_id))
AND s.sid = &sid;
SELECT DISTINCT S1.USERNAME || '@' || S1.MACHINE
|| ' ( INST=' || S1.INST_ID || ' SID=' || S1.SID || ' ) IS
BLOCKING '
|| S2.USERNAME || '@' || S2.MACHINE || ' ( INST=' ||
S1.INST_ID || ' SID=' || S2.SID || ' ) OBJ_ID:' ||L1.ID1||'
OBJ_NAME:'||O.OBJECT_NAME
AS BLOCKING_STATUS
FROM GV$LOCK L1, GV$SESSION S1, GV$LOCK L2, GV$SESSION
S2,DBA_OBJECTS O
WHERE S1.SID=L1.SID AND S2.SID=L2.SID
AND S1.INST_ID=L1.INST_ID AND S2.INST_ID=L2.INST_ID
AND L1.ID1=OBJECT_ID
AND L1.ID1=O.OBJECT_ID
AND L1.BLOCK > 0 AND L2.REQUEST > 0
AND L1.ID1 = L2.ID1 AND L1.ID2 = L2.ID2;
SELECT
l1.sid || ' is
blocking ' || l2.sid blocking_sessions
FROM
gv$lock l1, gv$lock
l2
WHERE
l1.block = 1 AND
l2.request > 0
AND
l1.id1 = l2.id1 AND
l1.id2 = l2.id2;
