Oracle 11g rel 2 :- same sql’s multiple times

Thanks all of you who answered my question. I need to go out of country so could not able to test the solutions provided but it looks promising. I am closing this question for now and ask a new question if the same situation happened.

Oranew - the first post shows multiple ways to try to find queries that are executed multiple times.

Sanjeev's suggestion of checking alert logs is also a good idea if you are seeing real deadlocks since there should be ORA-00060 error messages written there along with pointers to trace files with more information.

Between these two posts it seems, to me anyway, every portion of your question has been answered.  If there is a problem, please be specific in what you tried and what happened that was not what you expected.

I've requested that this question be deleted for the following reason:

I am deleting as per Administrative comments to do so. (see notes below from Administrator:-

Oranew,

Firstly, thanks you dir returning to your question and attempting to choose it appropriately.
However, If there is not a solution then you should delete the question.
It's also ok to continue the question if you'd like to, as long as you see it through to the end.

I've reopened the question for you to decide what to do.

eenookami
CS Moderator

Delete doesn't seem reasonable.

As noted previously.  Multiple solutions were given.

Hi Sdstuber,
Thanks for your comments. I really appreciate your help but you just answered very very generic.
You have given the below sql's:-
select * from v$sql where executions > 100;   -- statements currently in your shared pool

select * from dba_hist_sqlstat where executions_total > 100;   -- historical statements

select * from dba_hist_sqltext;  -- use the sql_id from the previous query to find the statement text

With all of your sql's the sql which causing the dead lock will not be known. The blockers which is causing the dead lock could not be identified. there are more than 1750-2000 transactions going on in the environment so all these generic sql's will not help in identifying the real dead lock transaction id. There were more than 70 dead locks but the actual culprit may be  just one.  In the alert log the dead lock was coming and going and the new session was taking the dead lock . killing one session was taking over by the other dead lock sessions. I asked how to check from history about the session which actually caused the dead lock but i still don't have the answer. I am not sure why you are trying to argue on deleting this question. Actually, I don't like to accept the solution which is not worked so don't be defended on it. I believe the issue was not the dead lock but the blocking session which caused the many dead locks and not just one.

Yes, I agree. That was the actual behavior and the same query run again and  again and causes the continuous dead lock by different sessions.

between the trace files and the shared pool and awr queries above you should be able to identify which query (or queries) is/are the problem

if blocking locks take a while to become dead locks then the last query I posted should identify those.   I recommend using a fixed-width font when viewing the output of that query so you can see the visual hierarchy of which sessions block each other.  - again this won't identify dead locks but can help to identify sets of blocking sessions that might become deadlocks if they circle around.

Yes, the actual root cause was not the dead lock but the blocking session which causing the lock. The issue was from the application messaging which updates the same table and somehow the messaging was initially stopped working and when the application server bounced to correct the messaging issue that triggered all the queued messages to flow which was more than 7,000  and created the hang situation and we killed several sessions but ro no gain and the database restart solved the issue.

if it's a blocking lock then the query in ID: 40338887 should help you find it.  It is RAC-aware, so if you have multiple instances with sessions on different nodes blocking each other that query should find them.  It will also run on a single-node system just fine as well.

.

why the B?  if the answers are deficient you should ask for more information and allow sufficient time to attempt a reply before assigning a penalty.

Hi
I am not aware of that all the questions be honored as A but with your comments and also moderator 's email it looks like I made a mistake but  I apologize for not giving you A.

Moderator, can you please change this to A ?

Hi,
I have the same situation now. I used your query and I am getting the below error:-
SQL> /
ERROR:
ORA-01427: single-row subquery returns more than one row

SQL> l
  1  WITH blockers_and_blockees
  2        AS (SELECT *
  3              FROM gv$session
  4             WHERE COALESCE(blocking_instance, blocking_session) IS NOT NULL
  5                OR (inst_id, sid) IN (SELECT blocking_instance, blocking_session FROM gv$session))
  6       SELECT LPAD(' ', 3 * (LEVEL - 1)) || sid || DECODE(LEVEL, 1, ' root blocker') blocked_session,
  7              inst_id w_inst,
  8              serial#,
  9              status,
 10              event,
 11              username || ' - ' || osuser || ' - ' || machine user_info,
 12              (SELECT owner || '.' || object_name
 13                 FROM dba_objects
 14                WHERE object_id = b.row_wait_obj#)
 15                  waiting_on_object,
 16              CASE
 17                  WHEN row_wait_obj# > 0
 18                  THEN
 19                      DBMS_ROWID.rowid_create(
 20                          1,
 21                          row_wait_obj#,
 22                          row_wait_file#,
 23                          row_wait_block#,
 24                          row_wait_row#
 25                      )
 26              END
 27                  waiting_on_rowid,
 28              (SELECT sql_text
 29                 FROM v$sql s
 30  WHERE s.sql_id = b.sql_id)
 31                  current_sql
 32         FROM blockers_and_blockees b
 33   CONNECT BY PRIOR sid = blocking_session AND PRIOR inst_id = blocking_instance
 34*  START WITH blocking_session IS NULL
SQL>



no rows selected

SQL> /
ERROR:
ORA-01427: single-row subquery returns more than one row



no rows selected

SQL> /
ERROR:
ORA-01427: single-row subquery returns more than one row

it's probably the v$sql subquery

you can either remove it

it's not critical to the query, what it does is provide the text of what a session is currently running.
Often - BUT NOT ALWAYS - that statement is the statement either blocking or being blocked.


OR - if you want to keep it, then add a rownum filter as shown below

All sql text for a given id should be the same, so picking any of them is sufficient.


I also added the instance to the join

(SELECT sql_text
              FROM gv$sql s
             WHERE s.sql_id = b.sql_id
                  AND s.inst_id = b.inst_id
                  AND ROWNUM = 1)

Thanks. I can't test this now as the situation is gone now. Will test this and update when the situation comes back again.
Thanks again for your immediate attention and response.