-
Shakeeb Petkar wrote a new post, Checking tablespace size 7 years, 9 months ago
The following query helps us to check the total size and free space of individual tablespaces in a tabular format
set linesize 1200
set pagesize 1200
column tablespace_name format a20 heading […] -
Shakeeb Petkar wrote a new post, Solving blocking sessions 7 years, 9 months ago
Cause:
Blocking sessions occur when one session holds an exclusive lock on an object and doesn’t release it before another session wants to update the same data. This will block the second until the first one has done its work.
From the view of the user it will look like the application completely hangs while waiting for the first session to release its lock. You will often have to identify these sessions in order to improve your application performance to avoid as many blocking sessions as possible.
Solution:
The following query shows all the blocking sessions which can help you to identify the problem.
col WAIT_CLASS for a12;
select blocking_session,sid,serial#,wait_class,seconds_in_wait,status from v$session where blocking_session is not NULL order by blocking_session;BLOCKING_SESSION SID SERIAL# WAIT_CLASS SECONDS_IN_WAIT STATUS
—————- ———- ———- ———— ————— ——–
5290 5527 590 Application 2146 ACTIVE
5438 5024 5728 Application 118 ACTIVE
5438 5369 4674 Application 2025 ACTIVE
5438 5251 2139 Application 2072 ACTIVE
5499 5438 2805 Application 2073 ACTIVE
5508 5901 4208 Application 6468 ACTIVE
5898 5290 4533 Application 2149 ACTIVE
5901 5019 11812 Application 5163 ACTIVE
5901 5135 1236 Application 6045 ACTIVE
5901 4934 1797 Application 792 ACTIVE
5901 5947 583 Application 1985 ACTIVE
5901 5204 4373 Application 241 ACTIVE
5901 5751 2989 Application 2171 ACTIVE
5901 5752 3331 Application 3641 ACTIVE
5993 5646 544 Commit 0 ACTIVE
5993 5486 3187 Commit 0 ACTIVE16 rows selected.
In this case, we find that session 5901 is blocking 7 other sessions.
select sid,serial#,event,module,action,status,program,blocking_session,last_call_et from v$session where sid=&sid;
Enter value for sid: 5901SID SERIAL# EVENT MODULE ACTION STATUS PROGRAM BLOCKING SESSION LAST_CALL_ET
———————————————————————————–
5901 4208 enq: TX – row lock contention WSHINTERFACE Concurrent Request ACTIVE 5508 6493From the above query we find that session 5901 is being blocked by session 5508.
select sid,serial#,event,module,action,status,program,blocking_session,last_call_et from v$session where sid=&sid;
Enter value for sid: 5508SID SERIAL#
———- ———-
EVENT
—————————————————————-
MODULE
————————————————
ACTION STATUS
——————————– ——–
PROGRAM BLOCKING_SESSION LAST_CALL_ET
———————————————— —————- ————
5508 7300
SQL*Net message from client
OEXOETEL
FRM:CLIMAM:Receiptionist Parts – INACTIVE
7554From the above query we find that session 5508 has become inactive.
In this case, we find that session 5901 is blocking 7 other sessions which in turn has been blocked by session 5508 and has been for 7554 seconds.
So, we kill this inactive session.
To kill the session from within Oracle, the sid and serial# values of the relevant session can then be substituted into the following statement:SQL> alter system kill session ‘sid,serial#’;
alter system kill session'5508,7300';System altered.
This command tells the specified session to rollback any un-committed changes and release any acquired resources before terminating cleanly. In some situations, this cleanup processing may take a considerable amount of time, in which case the session status is set to “marked for kill” until the process is complete and not killed immediately, the alter system kill session command can be forced by adding the immediate keyword:
SQL> alter system kill session ‘sid,serial#’ immediate;
-
Shakeeb Petkar wrote a new post, How to find Blocking sessions. 7 years, 9 months ago
Cause
Blocking sessions occur when one sessions holds an exclusive lock on an object and doesn’t release it before another sessions wants to update the same data. This will block the second until the first one has done its work.Please execute the below query to find the blocking sessions and kill if necessary.
Solution
col WAIT_CLASS for a12;
select blocking_session,
sid,
serial#,
wait_class,
seconds_in_wait,status
from
v$session
where
blocking_session is not NULL
order by
blocking_session;
-
Archives
- June 2015
- April 2015
- December 2014
- November 2014
- September 2014
- August 2014
- April 2014
- March 2014
- February 2014
- November 2013
- September 2013
- August 2013
- June 2013
- February 2013
- January 2013
- December 2012
- November 2012
- October 2012
- August 2012
- July 2012
- May 2012
- January 2012
- December 2011
- October 2011
- September 2011
- August 2011
- July 2011
- June 2011
- May 2011
- March 2011
- February 2011
- January 2011
- December 2010
- August 2010
- June 2010
-
Calendar
July 2022 M T W T F S S « Jun 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 -
Meta