Andrew,
Thanks for the answer.
Here is the query that I used during the problematic period.
This query did not show to me the "BLOCKER", showed only the "BLOCKED".
The strange is :
When I killed all locked sessions (300) with the application out of
running, the application was started again, and only one new session was in
lock, again (the one that was running by application) , and the query below
, still showed only the BLOCKED.
select
a.sid
, a.status || ' - '|| b.username status
, a.id1
, a.id2
, objects1.OBJECT_NAME
, objects2.OBJECT_NAME
from (
select vl.sid
, decode(vl.block,1,'',0,' BLOCKED',vl.block) Status
, vl.id1
, vl.id2
from gv$lock vl
where vl.kaddr in ( select lockwait
from gv$session
where lockwait is not null )
union
select vl.sid
, decode(vl.block,1,'BLOCKER',0,'',vl.block) Status
, vl.id1
, vl.id2
from gv$lock vl
where vl.id1 is not null
and vl.id2 is not null
and vl.block = 1
) a
, gv$session b
, dba_objects objects1
, dba_objects objects2
where a.sid=b.SID
and a.id1 = objects1.OBJECT_ID (+)
and a.id2 = objects2.OBJECT_ID (+)
order by id1, a.status desc
/
Jure,
Thanks for the suggestion.
I have the row# and the sql_id.
I think that now I can query DBA_HIST_ACTIVE_SESS_HISTORY to check
something.
Well, the session 1484, is one of its I kiiled.
So, running the query below I can see that at the problematic period there
was some others session blocking it, as showed below:
select distinct dbh.SESSION_ID, dbh.SESSION_SERIAL#,
dbh.BLOCKING_SESSION, dbh.BLOCKING_SESSION_SERIAL#
from DBA_HIST_ACTIVE_SESS_HISTORY dbh
where sql_id like 'dztv%' and dbh.CURRENT_ROW# = 139 -- It is the line
locked. The table locked has only 2 lines. It simulates a sequence :(
and dbh.sql_exec_start > to_date('29/12/2017 17:00:00','dd/mm/yyyy
hh24:mi:ss')
and dbh.sql_exec_start < to_date('29/12/2017 23:59:00', 'dd/mm/yyyy
hh24:mi:ss')
and dbh.session_id in (1484);
SESSION_ID SESSION_SERIAL# BLOCKING_SESSION
BLOCKING_SESSION_SERIAL#
1484 30742 2208 0
1484 61185 531
13455
1484 61185 531 0
1484 61185 531
60231
1484 61185 531
61173
1484 30742 2208
29796
1484 30742 2208
7319
1484 61185 531
41968
I need to improve this query trying to get the main BLOCKER.
But it does not help in case of similar problem, by the time configured for
dba_hist refresh.
Regards
Eriovaldo
2017-12-31 13:38 GMT-02:00 Dominic Brooks <dombrooks@xxxxxxxxxxx>:
Have had quite a few different scenarios.
But struggling to remember anything other than those involving XA
transactions where something has happened between the two phases of commit
and there is no longer a session attached to the transaction.
A transaction doesn’t need a session and with XA transactions, sessions
can actually attach/detach to the transaction programmatically.
Sent from my iPhone
On 31 Dec 2017, at 14:02, Andy Sayer <andysayer@xxxxxxxxx> wrote:
Eriovaldo,
What was the actual result of the query you originally posted? What was
the value for the blocking_session? I can't see any reason, from what
you've shared, that the problem was exotic enough to require anything other
than this value (it's the SID of the blocker)
"I used a lot of queries that show blocker and locked but without sucess
to see the blocker."
Like what? Either something was wrong with the queries or your
interpretation of them was wrong. If you share them, the results and your
interpretation then I'm sure someone can explain the problem.
Anecdotally, I've never had a session waiting on a lock where the blocking
session wasn't reported by v$session.blocking_session - of course,
sometimes there's a chain of blockers but it's never been too difficult to
follow the chain.
Regards and happy new year,
Andrew
On 31 December 2017 at 12:47, Eriovaldo Andrietta <ecandrietta@xxxxxxxxx>
wrote:
Thansk Gogala for the answer.
I think this link : https://github.com/dombrooks
/Oracle-Monitoring-Scripts/blob/master/transactions.sql
<https://nam03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fdombrooks%2FOracle-Monitoring-Scripts%2Fblob%2Fmaster%2Ftransactions.sql&data=02%7C01%7C%7Cef0bbc07b8674e249efa08d55057281c%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636503257619226967&sdata=7ZpL853kKA5usjYBcKnCvugbZ5NpWmQb74q8dffp6RA%3D&reserved=0>
sent by Dominic can help on the next time that it occurs.
Thanks
Eriovaldo
2017-12-31 9:49 GMT-02:00 Mladen Gogala <gogala.mladen@xxxxxxxxx>:
All locks are identified in V$LOCK table. You may be looking for an
event which functions as a lock, like waiting for a checkpoint to complete,
but there aren't any locks which are not identified in V$LOCK.
Regards
On 12/30/2017 05:32 PM, Eriovaldo Andrietta wrote:
Tks Dominic,
This is exactly I am looking for:
What is the way to check lock that is not identified by v$session and
v$lock and v$transactions ..
Regards
Eriovaldo
2017-12-30 9:22 GMT-02:00 Dominic Brooks <dombrooks@xxxxxxxxxxx>:
Also... technically it’s transactions not sessions which hold locks. In
some niche circumstances, that can make a difference in how to identify.
Sent from my iPhone
On 30 Dec 2017, at 11:02, Niall Litchfield <niall.litchfield@xxxxxxxxx>
wrote:
Is this a RAC database? You'll need to look also at the instance and
blocking_status columns in v$session to rely on the blocking_session column
in v$session. https://docs.oracle.com/database/121/REFRN/GUID-2
8E2DC75-E157-4C0A-94AB-117C205789B9.htm#REFRN30223
<https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdocs.oracle.com%2Fdatabase%2F121%2FREFRN%2FGUID-28E2DC75-E157-4C0A-94AB-117C205789B9.htm%23REFRN30223&data=02%7C01%7C%7Ce5abce7a2d1649633ac408d54f74d750%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636502285600939613&sdata=YN6Yr5ej5RLhXTthDuzXIHv%2BTq2KGXX%2FFTJsFT93Rjc%3D&reserved=0>
IIRC EM12c also is not RAC aware in its blocking sessions page.
On Sat, Dec 30, 2017 at 3:07 AM, Eriovaldo Andrietta <
ecandrietta@xxxxxxxxx> wrote:
Hello,
I got an issue related to lock.
Now the daabase was re-started and the issue is solved.
But during investigation I did not get sucess to identify who were
locking a table.
A table is used like this:
SELECT ID FROM TABLE_BLA
where id = :1
for upate;
The query did not return the result and looking for lock using this
query :
select s.sid, s.blocking_session, do.object_name,
row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#,
ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
from v$session s, dba_objects do
where s.ROW_WAIT_OBJ# = do.OBJECT_ID
I saw the row_wait_obj#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK# and
ROW_WAIT_ROW# values.
In this situation how can I find the blocker?
or
I used a lot of queries that show blocker and locked but without
sucess to see the blocker.
Regards
Eriovaldo
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
<https://eur02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.orawin.info&data=02%7C01%7C%7Ce5abce7a2d1649633ac408d54f74d750%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636502285600939613&sdata=mftmtNck9%2B16o4DQ2s0i3IDbgJgmSbOyXrzJtazs5B4%3D&reserved=0>
--
Mladen Gogala
Oracle DBAhttp://mgogala.freehostia.com ;
<https://nam03.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmgogala.freehostia.com&data=02%7C01%7C%7Cef0bbc07b8674e249efa08d55057281c%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636503257619226967&sdata=F5f8XLjWg0d26dKWULZtZ685iatpeEiMg3A1sx2RH%2B4%3D&reserved=0>
