Hi ,
How to find the lock , blocks and waits in the oracle database these sqls will helpfull.
select a.sid, a.serial#, b.sql_text
from gv$session a,g v$sqlarea b
where a.sql_address=b.address
and a.sid = &sid
select a.sid, a.serial#, b.sql_text
from gv$session a, gv$sqlarea b
where a.sql_address=b.address
and a.sid = &sid
Other cell single block physical read: pmem cache 81,785,379 0.00 28,198.94 344.79us 12.81
1 User I/O read by other session 4,283,630 0.00 5,926.70 1.38ms 2.69
1 User I/O cell multiblock physical read 13,427,646 0.00 5,746.40 427.95us 2.61
1 User I/O cell smart table scan 23,662,938 14.94 3,403.67 143.84us 1.55
1 Concurrency enq: TX - index contention 46,174 0.00 2,367.85 51.28ms 1.08
select vs.sid ,vs.serial# "Serial#",vs.PROCESS "OS PROCESS"
,a.oracle_process_id "spid",vs.SQL_ID,VS.PREV_SQL_ID
from APPLSYS.fnd_Concurrent_requests a
,APPLSYS.fnd_concurrent_processes b,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs_tl c2,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_user f,v$session vs,v$process vp
where
a.controlling_manager = b.concurrent_process_id
and a.concurrent_program_id = c.concurrent_program_id
and a.program_application_id = c.application_id
and c2.concurrent_program_id = c.concurrent_program_id
and c2.application_id = c.application_id
and a.phase_code in ('I','P','R','T')
and a.requested_by = f.user_id
and b.queue_application_id = q.application_id
and b.concurrent_queue_id = q.concurrent_queue_id
and c2.language = 'US' and vs.process (+) = b.os_process_id
and vs.paddr = vp.addr (+) and a.request_id=&request_id
/
set lines 300 pages 50000
select sid,serial#,sql_id,prev_sql_id from gv$session where sid=1713;
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process,d.inst_id
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = XXXX;
select oracle_username,os_user_name,locked_mode,object_name,object_type
from v$locked_object lo,dba_objects do
where lo.object_id = do.object_id;
select obj.owner,obj.object_name,obj.OBJECT_TYPE,sess.sid,
sess.serial#, sess.status,sess.LOGON_TIME,sess.module,sess.sql_id,sess.ACTION,sess.process
from gv$locked_object lo,dba_objects obj ,v$session sess
where obj.object_id=lo.OBJECT_ID
and sess.sid=lo.SESSION_ID;
and object_name like '%MMC_GLB_GL2WEBCAS_AUDIT%';
select sql_text from v$sql where sql_id='0P9HP6SP7NJZ0';
SET LINESIZE 500
SET PAGESIZE 1000
COLUMN username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20
SELECT LPAD(' ', (level-1)*2, ' ') || NVL(s.username, '(oracle)') AS username,
s.osuser,
s.sid,
s.serial#,
s.lockwait,
s.status,
s.module,
s.machine,
s.program,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session s
CONNECT BY PRIOR s.sid = s.blocking_session
START WITH s.blocking_session IS NULL;
select obj.owner,obj.object_name,obj.OBJECT_TYPE,sess.sid,
sess.serial#, sess.status,sess.LOGON_TIME,sess.module,sess.sql_id,sess.ACTION,sess.process
from gv$locked_object lo,dba_objects obj ,v$session sess
where obj.object_id=lo.OBJECT_ID
and sess.sid=lo.SESSION_ID;
and object_name like '%PAY%';
select SQL_TEXT from DBA_HIST_SQLTEXT where sql_id='0P9HP6SP7NJZ0';
Performance->sql>sql tuning adviser->click on historical AWR
there you search for the SQLID
select sid,serial#,sql_id,prev_sql_id from gv$session where sid=1713;
get explain plan ..
SELECT * FROM table(dbms_xplan.display_awr(SQL_ID=>'0P9HP6SP7NJZ0'));
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('0P9HP6SP7NJZ0'));
Query to Check Lock is Table Level or Row Level:-
----------------------------------------------------
col session_id head 'Sid' form 9999
col object_name head "Table|Locked" form a30
col oracle_username head "Oracle|Username" form a10 truncate
col os_user_name head "OS|Username" form a10 truncate
col process head "Client|Process|ID" form 99999999
col owner head "Table|Owner" form a10
col mode_held form a15
select lo.session_id,lo.oracle_username,lo.os_user_name,
lo.process,do.object_name,do.owner,
decode(lo.locked_mode,0, 'None',1, 'Null',2, 'Row Share (SS)',
3, 'Row Excl (SX)',4, 'Share',5, 'Share Row Excl (SSX)',6, 'Exclusive',
to_char(lo.locked_mode)) mode_held
from gv$locked_object lo, dba_objects do
where lo.object_id = do.object_id
order by 5
/
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process,d.inst_id
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = xxx;
error : Routine AFPCMT encountered an ORACLE error. ORA-03114: not connected to ORACLE
Concurrent Processing R12 : All Concurrent Requests Fail With Database Errors ORA-03113 and ORA-07445 (Doc ID 945107.1)
AFPGRG Failed Due To ORA-03114 (Doc ID 2331973.1)
1. Check the load of the server using top command
2. Verify the mount space using df -hP
3. Check the tablespace size
4. Verify the status of the concurrent request using query:
select request_id ,phase_code, status_code from fnd_concurrent_requests where request_id ='333333';
5. Check which CM is running this request.
SELECT request_id, user_concurrent_program_name, status_code, phase_code, completion_text FROM apps.fnd_conc_req_summary_v WHERE request_id =&req_id ;
6. Confirm the Actual and target of the CM from above output
7. Confirm the Concurrent Manager Status and queue
8. Check the SID of the concurrent request using query:
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id FROM apps.fnd_concurrent_requests a,apps.fnd_concurrent_processes b,gv$process c,gv$session d WHERE a.controlling_manager = b.concurrent_process_id AND c.pid = .oracle_process_id AND b.session_id=d.audsid AND a.request_id =&req_id AND a.phase_code = 'R';
9. Query to get the row fetched using the SID from step 8.
column name format a30 word_wrapped
column vlu format 999,999,999,999
select b.name, a.value vlu from v$sesstat a, v$statname b where a.statistic# = b.statistic# and sid =&sid and a.value != 0 and b.name like '%row%' /
col sid format 999999
col username format a20
col osuser format a15
select a.sid, a.serial#,a.username, a.osuser, b.spid from v$session a, v$process b where .paddr= b.addr and a.sid='&sid' order by a.sid;
10. Check Database session status and what it is running
set echo off
set linesize 132
set verify off
set feedback off
set serveroutput on;
declare
SID number := 0 ;
SERIAL number := 0 ;
username varchar(20) := '';
Status varchar(8) := '';
machine varchar(10) := '';
terminal varchar(25) := '';
program varchar(30) := '';
Module varchar(30) := '';
Action varchar(20) := '';
sql_hash_value number := 0 ;
logontime varchar(30) := '';
last_call_et number := 0 ;
proc number := 0 ;
spid number := 0 ;
event varchar(30) := '';
state varchar(30) := '';
sql_text varchar(2000) := '';
cursor cur1 is
select a.sid sid,
a.serial# serial,
a.username username,
a.status status ,
a.machine machine,
a.terminal terminal,
a.program program,
a.module module,
a.action action,
a.sql_hash_value sql_hash_value,
to_char(a.logon_time,'DD-Mon-YYYY HH:MI:SS') logontime,
a.last_call_et last_call_et,
a.process proc,
b.spid spid,
sw.event event,
sw.state state
from gv$session a, gv$process b, gv$session_wait sw
where a.paddr=b.addr and a.inst_id=b.inst_id
and a.sid='&1'
and a.inst_id=sw.inst_id
and a.sid=sw.sid;
begin
DBMS_OUTPUT.PUT_LINE('-----------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE(' Database session detail for the shadow process ');
DBMS_OUTPUT.PUT_LINE('-----------------------------------------------------------------');
for m in cur1
loop
DBMS_OUTPUT.ENABLE(50000);
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE( 'SID............ : ' || m.sid );
DBMS_OUTPUT.PUT_LINE('SERIAL#........ : ' || m.serial );
DBMS_OUTPUT.PUT_LINE('USERNAME....... : ' || m.username );
DBMS_OUTPUT.PUT_LINE('STATUS......... : ' || m.status );
DBMS_OUTPUT.PUT_LINE( 'Machine........ : ' || m.machine );
DBMS_OUTPUT.PUT_LINE( 'Terminal....... : ' || m.terminal);
DBMS_OUTPUT.PUT_LINE( 'Program........ : ' || m.program );
DBMS_OUTPUT.PUT_LINE('Module......... : ' || m.module );
DBMS_OUTPUT.PUT_LINE( 'Action......... : ' || m.action );
DBMS_OUTPUT.PUT_LINE('SQL Hash Value. : ' || m.sql_hash_value );
DBMS_OUTPUT.PUT_LINE( 'Logon Time..... : ' || m.logontime );
DBMS_OUTPUT.PUT_LINE( 'Last Call Et... : ' || m.last_call_et );
DBMS_OUTPUT.PUT_LINE( 'Process ID..... : ' || m.proc );
DBMS_OUTPUT.PUT_LINE( 'SPID........... : ' || m.spid );
DBMS_OUTPUT.PUT_LINE('Session Waiting for event:'||m.event );
DBMS_OUTPUT.PUT_LINE('Session state ...........:'||m.state);
dbms_output.put_line('SQL_TEXT is..........:');
for rec in ( select sql_text from v$session s,v$sqltext v where
s.sql_hash_value=v.hash_value and
s.sql_address=v.address and s.sid=m.sid order by piece)
loop
dbms_output.put_line(rec.sql_text);
end loop;
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE(' ');
end loop;
end;
/
11. Check which sql id it is running, you can get these details using above steps.
12. You should check the sql_id history and can run sql advisory against the
sql_id. this will give you incase of the sql query is not correct or may have
to see the recommendation based on the sql advisory
13. Check the stale value of the tables which is used by the concurrent program.
select table_name, stale_stats, last_analyzed from dba_tab_statistics where
stale_stats='YES';
14. You may have to run the gather stats against those tables which are having stale value.
15. You can also use OEM and monitor the Session id.
column DATABASE format a25
col "GB" format 99,999.9
set pagesize 20
SELECT
disk_group_name
,SUBSTR(alias_path,2,INSTR(alias_path,'/',1,2)-2) Database
,ROUND(SUM(alloc_bytes)/1024/1024/1024,1) "GB"
,ROUND(SUM(alloc_bytes)/1024/1024,1) "MB"
FROM
(SELECT
SYS_CONNECT_BY_PATH(alias_name, '/') alias_path
,alloc_bytes, disk_group_name
FROM
(SELECT
g.name disk_group_name
, a.parent_index pindex
, a.name alias_name
, a.reference_index rindex
, f.space alloc_bytes
, f.type type
FROM
v$asm_file f RIGHT OUTER JOIN v$asm_alias a
USING (group_number, file_number)
JOIN v$asm_diskgroup g
USING (group_number)
)
WHERE type IS NOT NULL
START WITH (MOD(pindex, POWER(2, 24))) = 0
CONNECT BY PRIOR rindex = pindex
)
GROUP BY disk_group_name, SUBSTR(alias_path,2,INSTR(alias_path,'/',1,2)-2)
ORDER BY 1;
Thanks,
Srini
