Re: Deadlock and ORA-0600 ocurred yesterday

Hi Sayan,

Now I got the object , running it:

create directory TRACE_DIR as
'/home/oracle/app/oracle/diag/rdbms/sgc10pr/sgc10pr/trace/'; select bfilename('TRACE_DIR','sgc10pr_ora_12256726.trc') trc from dual;

Rows waited on:

  Session 793: obj - rowid = 00068AAB - AACEdvABJAAJXCkAAo
  (dictionary objn - 428715, file - 73, block - 2453668, slot - 40)
  Session 295: obj - rowid = 00068AAB - AACEdvABJAAJXCkAAP
  (dictionary objn - 428715, file - 73, block - 2453668, slot - 15)

SELECT CBILLSGCPR_SCH.CAB_CHARGE_ACTION FROM DBA_OBJECTS X WHERE X.OBJECT_ID = 428715; Issue solved and confirmed.

Many thanks.
Eriovaldo

2018-03-24 22:50 GMT-03:00 Sayan Malakshinov <xt.and.r_at_gmail.com>:

> Eriovaldo,
>
> That's great, but I wonder how can you be sure that you got snaps relevent
> to this deadlock, because it can be very short and there is a chance that DBA_HIST_ACTIVE_SESS_HISTORY
> doesn't contain snapshots with this locks.
> Moreover, I'd recommend to remove predicate sql_opname not in
> ('SELECT'), because SELECT FOR UPDATE can cause deadlock too.
> Btw, it seems you have syssdba privileges, so you can easily "create
> directory" and read tracefile using bfile.
> For example (for your dir path and trace filename):
> create directory TRACE_DIR as '/home/oracle/app/oracle/diag/
> rdbms/sgc10pr/sgc10pr/trace/';
> select bfilename('TRACE_DIR','sgc10pr_ora_12256726.trc') trc from dual;
>
> Also you haven't provided your oracle version, so just in case if it is
> Oracle 12.2, you can use special view V$DIAG_TRACE_FILE_CONTENTS:
> https://antognini.ch/2016/09/sql-trace-in-oracle-database-exadata-
> express-cloud-service/
> For example:
> select payload from V$DIAG_TRACE_FILE_CONTENTS where trace_filename = '
> sgc10pr_ora_12256726.trc';
>
> On Sun, Mar 25, 2018 at 4:34 AM, Eriovaldo Andrietta <
> ecandrietta_at_gmail.com> wrote:
>
>> Hello,
>>
>> I got the sql_id involved :
>>
>> select *
>> from DBA_HIST_ACTIVE_SESS_HISTORY
>> where sample_time between TO_DATE('23/03/2018 18:47:00','DD/MM/YYYY
>> HH24:MI:SS') and
>> TO_DATE('23/03/2018 18:47:35','DD/MM/YYYY
>> HH24:MI:SS')
>> and sql_opname not in ('SELECT')
>> and blocking_session IS NOT NULL;
>>
>>
>> Thanks for all answers.
>>
>> Eriovaldo
>>
>>
>>
>> 2018-03-24 21:53 GMT-03:00 Mladen Gogala <gogala.mladen_at_gmail.com>:
>>
>>> Trust the query for what? Deadlocks leave trace files. Get someone to
>>> send you the trace file and the story is over. With the query below, you
>>> will get the list of all tables modified within 6 PM and 7 PM today. BTW, a
>>> good practice is to flush the monitoring info to the table, using
>>> DBMS_STATS call. How will that help you with the deadlock? Another way
>>> would be to query ASH (active session history) and find all sessions with
>>> blocking id not null between 6 PM and 7 PM. All you need to locate is a
>>> closed graph: A is waiting on B, B is waiting on C and C is waiting on A.
>>> Every deadlock is a closed graph. That will give you SQL statements
>>> involved and presumably help you to solve the problem. The problem with
>>> deadlocks is that they are usually application logic errors. It is not
>>> possible to have deadlock is all the applications lock row in the tables in
>>> the same order. If all applications first lock table A, then table B and
>>> then table C, there will be no deadlocks. However, if some applications are
>>> locking tables in different orders, you may encounter a deadlock.
>>>
>>> Regards
>>>
>>> On 03/24/2018 05:54 PM, Eriovaldo Andrietta wrote:
>>>
>>> Hi,
>>>
>>> There were a error ORA-0600 yesterday caused by a deadlock.
>>>
>>> I don´t have acesss to the alert of the database server.
>>>
>>> Can I trust on this query ?
>>>
>>> select table_name,inserts,updates,deletes,truncated,timestamp
>>> from dba_tab_modifications
>>> where timestamp > TO_DATE('23/03/2018 18:00:00','DD/MM/YYYY HH24:MI_SS')
>>> and timestamp < TO_DATE('23/03/2018 19:00:00','DD/MM/YYYY HH24:MI_SS')
>>>
>>> With this query I got a list of tables.
>>>
>>>
>>> Is there another way to know what is the table that were root cause of
>>> the error ?
>>>
>>> Great
>>> Eriovaldo
>>>
>>>
>>> --
>>> Mladen Gogala
>>> Database Consultant
>>> Tel: (347) 321-1217
>>>
>>>
>>
>
>
> --
> Best regards,
> Sayan Malakshinov
> Oracle performance tuning engineer
> Oracle ACE Associate
> http://orasql.org
>

--
http://www.freelists.org/webpage/oracle-l