Logon table is locking oftenly

We have one table master table is getting locked very often , Master table which containing user credentials to logon the application.

Troubleshooting:

SQL> analyze table scott.master validate structure cascade;
Table analyzed.
SQL> EXEC dbms_stats.gather_table_stats('SCOTT','MASTER',cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL>

 

 

EnableD 10046 event as below:

alter session set timed_statistics = true;
alter session set max_dump_file_size = ‘unlimited’;
alter session set tracefile_identifier=’10046′;

#start tracing:
alter session set events ‘10046 trace name context forever, level 12’ ;
#Execute update

update scott.master set deptno=30 where empno=7369;

#stop tracing:
alter session set events ‘10046 trace name context off’ ;

wait for 30 minutes before killing the session.

A trace will be generated in USER_DUMP_DEST which name will be like *10046*.trc .

Reviewed the trace file

login to second session as / as sysdba

Use the following query or EM or OEM,
you can use any methoed to find the above session that is spinning

Find the Oracle Process ID and the OS Process ID

set linesize 133
set pagesize 100
col sid format 99999
col orapid JUSTIFY CENTER HEADING 'UNIX or|WINDOWS|*ORAPID*' format 99999999
col ospid JUSTIFY CENTER HEADING 'UNIX|*OSPID*' format a7
col username format a10
col machine format a20
col terminal format a20
col program format a20
SELECT s.sid
, p.pid as "ORAPID" -- setorapid for UNIX/WINDOWS
, p.spid as "OSPID" -- OR setospid for UNIX
, s.username
, s.osuser
, s.machine
, s.terminal
, s.program 
FROM v$process p, v$session s 
WHERE p.addr=s.paddr
order by p.spid;

 Must be taken during time of problem.

-- Even if there is not an error,
-- this will still collect needed information.
--
connect / as sysdba or internal
oradebug setorapid <...ORAPID for stuck process...> <<< from above query, get info of query that is spinning
oradebug unlimit
oradebug tracefile_name
oradebug dump errorstack 3 
oradebug dump errorstack 3
oradebug dump errorstack 3
oradebug dump errorstack 3
oradebug dump errorstack 3
oradebug dump errorstack 3
oradebug dump errorstack 3
oradebug dump errorstack 3
oradebug dump errorstack 3
oradebug dump errorstack 3
exit

Trace file contents

waiting for ‘read by other session’ wait_time=0, seconds since wait started=1519
file#=40, block#=6e284, class#=1
blocking sess=0x(nil) seq=52
Dumping Session Wait History
for ‘read by other session’ count=1 wait_time=0.011447 sec
file#=40, block#=6e284, class#=1
for ‘read by other session’ count=1 wait_time=0.078368 sec
file#=40, block#=6e284, class#=1
for ‘read by other session’ count=1 wait_time=0.977545 sec
file#=40, block#=6e284, class#=1
for ‘read by other session’ count=1 wait_time=0.977543 sec
file#=40, block#=6e284, class#=1
for ‘read by other session’ count=1 wait_time=0.977551 sec
file#=40, block#=6e284, class#=1
for ‘read by other session’ count=1 wait_time=0.977550 sec
file#=40, block#=6e284, class#=1
for ‘read by other session’ count=1 wait_time=0.977542 sec
file#=40, block#=6e284, class#=1
for ‘read by other session’ count=1 wait_time=0.977544 sec

 

From
"Read By Other Session" Wait Event (Doc ID 732891.1)
This means the block is being read by someone else and is "blocking" this session.

Also, found
Lots of 'read by other session' On The Same Block After Applying 10.2.0.5 Patchset On Linux Platforms When AIO Is In Use (Doc ID 1166003.1)
Finally resolved:  This was a bug and patch for that is Patch 9949948 is available on top of 10.2.0.5. Fix is included starting 10.2.0.5.2 PSU
Advertisements

About Sher khan

Senior Oracle DBA, Oracle 10g , Oracle 11g OCE ,Oracle 12C and Oracle 11g OPN certified Specialist, OCP Certified in Oracle 9i,10g , 11g and 12C. I am working in Oracle technology since 2004. Currently working in U.A.E Email: sher487@hotmail.com
This entry was posted in Performance and Tuning. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s