Re: Blocker did not identified
Date: Sun, 31 Dec 2017 18:23:44 -0200
Message-ID: <CAJdDhaMi8bjh+dyCZdGHqTX9_So_=MockQwfZ7+omsdZ23_dkQ_at_mail.gmail.com>
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_at_hotmail.com>:
> 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_at_gmail.com> 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_at_gmail.com>
> 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_at_gmail.com>:
>>
>>> 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_at_hotmail.com>:
>>>
>>>> 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_at_gmail.com>
>>>> 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_at_gmail.com> 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>
>>>
>>>
>>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Sun Dec 31 2017 - 21:23:44 CET