9 Replies Latest reply: May 29, 2010 10:35 AM by 737189 RSS

    Blocking Session -  blocked SQL -> SELECT SYSDATE   FROM SYS.DUAL

    713347
      Oracle 10.0.0.4g

      When database execute some big and long queries/operations my system is slow and some users wait, can’t work (they work with some Oracle forms applications ) because I often have blocking session.
      I found up that this blocking sessions block only this query of another user:
      SELECT SYSDATE   FROM SYS.DUAL

      Or:

      +10-АВГ-2009 08:51:10 User X1 ( SID= 222 ) with the statement: SELECT ... is blocking the SQL statement on Y1 ( SID=333 ) blocked SQL -> SELECT SYSDATE FROM SYS.DUAL+

      When I kill one of the blocking session another session take his place and do the same:
      +10-АВГ-2009 08:53:10 User X2 ( SID= 444 ) with the statement: SELECT ... is blocking the SQL statement on Y2 ( SID=555 ) blocked SQL -> SELECT SYSDATE FROM SYS.DUAL+

      When long queries finished everything is OK.

      Please Help Me!!!
        • 1. Re: Blocking Session -  blocked SQL -> SELECT SYSDATE   FROM SYS.DUAL
          21205
          A SELECT statement blocking?... hmmm

          Unless there is a FOR UPDATE in that statement, this should not block.
          • 2. Re: Blocking Session -  blocked SQL -> SELECT SYSDATE   FROM SYS.DUAL
            Billy~Verreynne
            user10921739 wrote:
            Oracle 10.0.0.4g

            When database execute some big and long queries/operations my system is slow and some users wait, can’t work (they work with some Oracle forms applications ) because I often have blocking session.
            I found up that this blocking sessions block only this query of another user:
            SELECT SYSDATE   FROM SYS.DUAL
            What is the wait state of the session being blocked? Have a look at [V$SESSION_WAIT|http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2094.htm].

            It is strange that a select of a function against DUAL will block... also, this would seem to be less then ideal code. A Forms or PL/SQL client can simply assign SYSDATE to a local variable, e.g.
               ..
               d date;
            begin
              d := sysdate;
              ..
            end;
            There is no need for a slow an expensive SQL context switch from the PL engine to the SQL engine in order to get the current date and time.
            • 3. Re: Blocking Session -  blocked SQL -> SELECT SYSDATE   FROM SYS.DUAL
              713347
              Now I have and something like this:

              +11-АВГ-2009 10:07:24 User Y2 ( SID= 323) with the statement: SELECT SYSDATE   FROM SYS.DUAL is blocking the SQL statement on X2 ( SID=400 ) blocked SQL -> SELECT SYSDATE   FROM SYS.DUAL+

              +11-АВГ-2009 10:00:36 User Y1 ( SID= 555 ) with the statement: SELECT SYSDATE   FROM SYS.DUAL is blocking the SQL statement on X1 ( SID=888 ) blocked SQL -> DELETE ...+
              • 4. Re: Blocking Session -  blocked SQL -> SELECT SYSDATE   FROM SYS.DUAL
                713347
                Now I get this:

                11-АВГ-2009 13:36:49 User S ( SID= 435 ) with the statement: UPDATE ... is blocking the SQL statement on B ( SID=376 ) blocked SQL -> SELECT SYSDATE FROM SYS.DUAL
                11-АВГ-2009 13:36:57 User S ( SID= 435 ) with the statement: SELECT ... is blocking the SQL statement on B ( SID=376 ) blocked SQL -> SELECT SYSDATE FROM SYS.DUAL


                from v$session_wait:

                SID - 376
                WAIT_CLASS - Application
                EVENT - enq: TM - contention
                STATE - WAITING
                WAIT_TIME - 0
                SECONDS_IN_WAIT - 114
                • 5. Re: Blocking Session -  blocked SQL -> SELECT SYSDATE   FROM SYS.DUAL
                  21205
                  could it be?..... is someone altering DUAL?
                  • 6. Re: Blocking Session -  blocked SQL -> SELECT SYSDATE   FROM SYS.DUAL
                    Billy~Verreynne
                    user10921739 wrote:

                    11-АВГ-2009 13:36:49 User S ( SID= 435 ) with the statement: UPDATE ... is blocking the SQL statement on B ( SID=376 ) blocked SQL -> SELECT SYSDATE FROM SYS.DUAL
                    And this output is from? (need to make pretty sure that the blocking session and SQL are correctly reported).
                    SID - 376
                    WAIT_CLASS - Application
                    EVENT - enq: TM - contention
                    STATE - WAITING
                    WAIT_TIME - 0
                    SECONDS_IN_WAIT - 114
                    Does the blocking process step outside of PL/SQL and SQL using ext_proc or Java? (in which case the event being waited on will not be shown and the last PL/SQL and SQL event will be seen, incorrectly, as the blocking event).

                    What is the current SQL for this session?

                    A SELECT of a SQL function using DUAL should not be run into contention IMO... unless there are more to this. Thus my questions above.
                    • 7. Re: Blocking Session -  blocked SQL -> SELECT SYSDATE   FROM SYS.DUAL
                      713347
                      SELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS')
                      || ' User '
                      ||s1.username
                      || '@'
                      || s1.machine
                      || ' ( SID= '
                      || s1.sid
                      || ' ) with the statement: '
                      || sqlt2.sql_text
                      ||' is blocking the SQL statement on '
                      || s2.username
                      || '@'
                      || s2.machine
                      || ' ( SID='
                      || s2.sid
                      || ' ) blocked SQL -> '
                      ||sqlt1.sql_text AS blocking_status
                      FROM v$lock l1, v$session s1, v$lock l2 ,
                      v$session s2,v$sql sqlt1, v$sql sqlt2
                      WHERE s1.sid =l1.sid
                      AND s2.sid =l2.sid AND sqlt1.sql_id= s2.sql_id AND sqlt2.sql_id= s1.prev_sql_id AND l1.BLOCK =1
                      AND l2.request > 0 AND l1.id1 = l2.id1 AND l2.id2 = l2.id2


                      and

                      SELECT wait_class, event, sid, state, wait_time, seconds_in_wait
                      FROM v$session_wait
                      where sid in ('376')
                      ORDER BY wait_class, event, sid;
                      • 8. Re: Blocking Session -  blocked SQL -> SELECT SYSDATE   FROM SYS.DUAL
                        713347
                        I create ASH report:_


                        Top User Events
                        Avg Active
                        Event Event Class % Activity Sessions
                        ----------------------------------- --------------- ---------- ----------
                        enq: TM - contention Application 55.87 0.96
                        db file sequential read User I/O 18.87 0.32
                        CPU + Wait for CPU CPU 16.33 0.28
                        db file scattered read User I/O 3.02 0.05
                        -------------------------------------------------------------

                        Top Event P1/P2/P3 Values

                        Event % Event P1 Value, P2 Value, P3 Value % Activity
                        ------------------------------ ------- ----------------------------- ----------
                        Parameter 1 Parameter 2 Parameter 3
                        -------------------------- -------------------------- --------------------------
                        enq: TM - contention 55.87 "xxxxxxxxxxxxxxxxxxxx" 38.35
                        name|mode object # table/partition


                        "1111111111","xxxxxxx","0" 17.44


                        db file sequential read 19.21 "xxxxxxxxxxxxxxx’’ 0.00
                        file# block# blocks

                        db file scattered read 3.03 "xxxxxxxxxxxxxxxxxxxxxx’’ 0.01
                        file# block# blocks


                        Top SQL Statements …………..

                        SQL ID Planhash % Activity Event % Event
                        ------------- ----------- ---------- ------------------------------ ----------
                        fnxxxxxxxxx N/A 25.09 enq: TM - contention 23.47
                        ** SQL Text Not Available **

                        N/A 25.09 db file sequential read 1.19
                        ** SQL Text Not Available **

                        byxxxxxxxxxxxxx 1111111 10.11 enq: TM - contention 7.43
                        SELECT SYSDATE FROM SYS.DUAL

                        db file sequential read 2.10

                        fnxxxxxxxxx 11111111111 2.57 enq: TM - contention 2.16
                        ** SQL Text Not Available **

                        Top DB Objects

                        Object ID % Activity Event % Event
                        --------------- ---------- ------------------------------ ----------
                        Object Name (Type) Tablespace
                        ----------------------------------------------------- -------------------------
                        11111 10.33 enq: TM - contention 10.30
                        XXXXXXXXXXXXXXXXXXXXXXXX (INDEX) CC

                        99999 10.18 enq: TM - contention 10.16
                        XXXXXXXXXXXXXXXXXXXXXXXXX (INDEX) IND

                        933333 6.67 enq: TM - contention 6.55
                        FFFFFFFFFFFFFFFF (TABLE) T3

                        114545 3.88 enq: TM - contention 3.85
                        RRRRRRRRRRRRRRRRRRRRRR (INDEX) JJJ

                        1136664 2.96 enq: TM - contention 2.93
                        FFFFFFFFFFFFFFFFFFFFFFFFF (INDEX) G



                        How to found sql text that is not available ** SQL Text Not Available **?
                        What to do whit this Top DB Objects that have enq: TM - contention event?
                        And how to solve this problem?
                        • 9. Re: Blocking Session -  blocked SQL -> SELECT SYSDATE   FROM SYS.DUAL
                          737189
                          Just to citate (for those who didn't found your parallel question on other forum):

                          This issue is fixed in Oracle10g with the new "fast dual" execution plan, but you can do a trick in earlier releases of Oracle to reduce consistent gets (logical I/O) from three to zero.

                          There are two ways to cache the dual fixed table in Oracle; this, take your pic:

                          Fix approach One:

                          connect sys/mypass as sysdba;
                          create view x_$dual as select * from x$dual ;

                          grant select on x_$dual to public;

                          connect my_schema_owner/mypassword;

                          create synonym DUAL for sys.x_$dual ;

                          Fix approach Two:

                          1. Allocate a small keep pool if it doesn’t exist.

                          2. From the sys user issue “alter table dual cache storage(buffer_pool keep);

                          3. This will drop the consistent gets from accessing dual from 3 to 0.

                          4. The problem is dual is always an FTS so it is placed at the end of the LRU and is aged out fast. By marking it as cached and placing it in the Keep pool this is avoided. In order to get to 0 CR I had to use both cache and the keep option to make this work.