• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 889
  • Last Modified:

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

Oracle 11g rel 2 :- How to know that same sql’s got executed multiple times
I am having dead lock in one of our Oracle 11g rel 2 Instance.
It looks like the same jobs being kicked off simultaneously and it is the cause of the dead lock.
How can I check yesterday’s sql’s from history or AWR that the same sql’s executed many times?
I also need how many times a particular sql got executed. Is there a way to get it ? Experts !
0
Oranew
Asked:
Oranew
  • 9
  • 8
3 Solutions
 
sdstuberCommented:
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
0
 
Sanjeev LabhCommented:
Do you just want to check out the number of times the queries are executed or are you interested to get the users and queries involved in a deadlock scenario.

If you are interested in solving deadlock then you should check the alert log as deadlocks get logged in alert logs. Alongwith the log there will be trace dumps of the deadlock and if you check these out then you can know what is causing the deadlock.
0
 
OranewAuthor Commented:
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.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
sdstuberCommented:
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.
0
 
OranewAuthor Commented:
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
0
 
sdstuberCommented:
Delete doesn't seem reasonable.

As noted previously.  Multiple solutions were given.
0
 
OranewAuthor Commented:
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.
0
 
sdstuberCommented:
There are multiple parts to your question and you just expanded it.

1 - The title question itself - find queries executed many times - I answered this, if you need more detail, please ask but I think the queries I provided are fairly self-explanatory

2 - find deadlocks - Sanjeev answered this

If you need more help tracking down the queries and objects post one of the trace files indicated by the alert log.

3 - find blocking statements - this is new but related.  You can use this query to find sessions blocking other sessions before they become deadlocks.

WITH blockers_and_blockees
     AS (SELECT *
           FROM gv$session
          WHERE COALESCE(blocking_instance, blocking_session) IS NOT NULL
             OR (inst_id, sid) IN (SELECT blocking_instance, blocking_session FROM gv$session))
    SELECT LPAD(' ', 3 * (LEVEL - 1)) || sid || DECODE(LEVEL, 1, ' root blocker') blocked_session,
           inst_id w_inst,
           serial#,
           status,
           event,
           username || ' - ' || osuser || ' - ' || machine user_info,
           (SELECT owner || '.' || object_name
              FROM dba_objects
             WHERE object_id = b.row_wait_obj#)
               waiting_on_object,
           CASE
               WHEN row_wait_obj# > 0
               THEN
                   DBMS_ROWID.rowid_create(
                       1,
                       row_wait_obj#,
                       row_wait_file#,
                       row_wait_block#,
                       row_wait_row#
                   )
           END
               waiting_on_rowid,
           (SELECT sql_text
              FROM v$sql s
             WHERE s.sql_id = b.sql_id)
               current_sql
      FROM blockers_and_blockees b
CONNECT BY PRIOR sid = blocking_session AND PRIOR inst_id = blocking_instance
START WITH blocking_session IS NULL;


If you're trying to find a session that is in a deadlock you can't.  Oracle automatically detects deadlocks and kills the initiating session.    This isn't a fix but it releases one side of a deadlock so it can continue.  If your application then reconnects and tries the same operation again you can get in a cycle of continual deadlocks.
0
 
OranewAuthor Commented:
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.
0
 
sdstuberCommented:
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.
0
 
OranewAuthor Commented:
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.
0
 
sdstuberCommented:
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.
0
 
OranewAuthor Commented:
.
0
 
sdstuberCommented:
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.
0
 
OranewAuthor Commented:
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 ?
0
 
OranewAuthor Commented:
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
0
 
sdstuberCommented:
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)
0
 
OranewAuthor Commented:
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.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now