This discussion is archived
3 Replies Latest reply: Nov 21, 2012 11:36 PM by Marwim RSS

Sys.dual Troubles - Screen Freezes

sharpe Newbie
Currently Being Moderated
Hi. We run Web Forms 10g against an Oracle 11g database and we have about 25-30 concurrent users running multiple instances of various forms applications at once. At least one or twice a week users will see their various web form application just lock up and freeze. Not all at once, but a user here or a user there. Not always while working with the same screen either.

While the freeze is happening we will check on their Oracle user session details and we are seeing that the application seems to be waiting on the statement:
select user
from sys.dual;
Does anyone know of any correlation between screen freezes and sessions getting stuck on this statement? Any insights would be greatly appreciated.

Edited by: sharpe on Nov 21, 2012 12:04 PM
  • 1. Re: Sys.dual Troubles - Screen Freezes
    MLBrown Journeyer
    Currently Being Moderated
    Steve -

    We were receiving some of the same problems and us developers had a heck of a time trying to figure out which object was actually the one with the lock on it. We would run different queries and see things like you were reporting. Then our DBA turned us onto Oracle Enterprise Manager Grid Control. We can navigate to the particular database and monitor any "Instance Locks - Blocking Locks". I'm sure you could run a SQL to see this same thing, but of course they don't tell you what that SQL is. I did some Googling and I came up with a query based on lots of different queries that seems be close to what Grid Control shows when a lock occurs. I don't guarantee anything with this query but here it is:
    SELECT L2.USERNAME,
           L2.SID,
           L2.SERIAL#,
           L2.MODE_HELD,
           L2.MODE_REQUESTED,
           O.name OBJECT_NAME
      FROM (SELECT /*+ ordered */
                   0,
                   S.sid,
                   S.serial#,
                   NVL (S.sql_id, 0),
                   DECODE (L.TYPE,
                           'TM', L.id1,
                           'TX', DECODE (L.request,
                                         0, NVL (LO.object_id, -1),
                                         S.row_wait_obj#
                                        ),
                           -1
                          ) AS object_id,
                   S.username,
                   S.row_wait_obj#,
                   S.row_wait_block#,
                   S.row_wait_row#,
                   S.row_wait_file#,
                   L.TYPE,
                   DECODE (L.lmode,
                           0, 'NONE',
                           1, 'NULL',
                           2, 'ROW SHARE',
                           3, 'ROW EXCLUSIVE',
                           4, 'SHARE',
                           5, 'SHARE ROW EXCLUSIVE',
                           6, 'EXCLUSIVE',
                           '?'
                          ) MODE_HELD,
                   DECODE (L.request,
                           0, 'NONE',
                           1, 'NULL',
                           2, 'ROW SHARE',
                           3, 'ROW EXCLUSIVE',
                           4, 'SHARE',
                           5, 'SHARE ROW EXCLUSIVE',
                           6, 'EXCLUSIVE',
                           '?'
                          ) MODE_REQUESTED,
                   L.id1,
                   L.id2,
                   L.ctime,
                   P.spid,
                   S.sql_hash_value
              FROM v$lock L,
                   v$session S,
                   v$process P,
                   (SELECT object_id,
                           session_id,
                           xidsqn
                      FROM v$locked_object
                     WHERE xidsqn > 0) LO
             WHERE S.sid = L.sid
               AND P.addr = S.paddr
               AND L.TYPE != 'MR'
               AND L.sid = LO.session_id(+)
               AND L.id2 = LO.xidsqn(+)) L2,
           sys.obj$ O,
           sys.user$ U
     WHERE O.obj#(+) = L2.object_id
       AND O.owner# = U.user#(+)
       AND O.name is not null
       AND L2.sid in (
             SELECT vh.sid
               FROM v$lock vh, v$lock vw, v$session vs, v$session vsw
              WHERE (vh.id1, vh.id2) IN (SELECT id1,
                                                id2
                                           FROM v$lock
                                          WHERE request = 0
                                         INTERSECT
                                         SELECT id1,
                                                id2
                                           FROM v$lock
                                          WHERE lmode = 0)
                AND vh.id1 = vw.id1
                AND vh.id2 = vw.id2
                AND vh.request = 0
                AND vw.lmode = 0
                AND vh.sid = vs.sid
                AND vw.sid = vsw.sid)
    The next time you get a lock, run this query and see what table shows up in the OBJECT_NAME column. That will be your culprit if this SQL is correct and that is a big if... You would be better off talking to your DBA and seeing if they can give you access to Grid Control (if it is installed).

    We moved to 11g recently and were experiencing many more locks than usual. One of our developers found something that said that the locking was changed in 11g which was causing us a problem. I hope I can explain this right, but if you have a PK-FK relationship and the FK column is not indexed then the PK table is locked until the user commits or does a rollback. Once we had this bit of information, everytime there was a lock we would look at Grid Control (or hopefully this query) and see which object was locked. We would then check the FKs in that table and if any of them contained non-indexed columns that referenced the PK we would index them and the problems went away.

    Hope this info helps.

    -mike
  • 2. Re: Sys.dual Troubles - Screen Freezes
    sharpe Newbie
    Currently Being Moderated
    Wow. This sounds amazingly helpful. I will take your advice and look into the FK non-indexed columns referring to a PK. Thanks so much.
  • 3. Re: Sys.dual Troubles - Screen Freezes
    Marwim Expert
    Currently Being Moderated
    Hello,

    Tom Kyte provided a script to search for unindexed foreign keys
    http://tkyte.blogspot.de/2009/10/httpasktomoraclecomtkyteunindex.html
    and an article from Oracle Magazin
    http://www.oracle.com/technetwork/issue-archive/2010/10-nov/o60asktom-176254.html

    Regards
    Marcus

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points