This discussion is archived
7 Replies Latest reply: Aug 23, 2013 9:43 PM by automan RSS

Recursive query?

user13045898 Newbie
Currently Being Moderated

Hi,

we keep on seeing this sql in our database:

 

"SELECT LOCKID FROM DBMS_LOCK_ALLOCATED WHERE NAME = :B1 FOR UPDATE"

 

I think it is some kind of recursive sql that Oracle runs for some internal Oracle functions, rather than some application sql, but I am not DBA and do not fully understand it.

Could you explain briefly what it may be for and how if gets generated?

 

Thanks

  • 1. Re: Recursive query?
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated

    It's called from the dbms_lock package. Some part of your system is using "user-defined" locks.

    Off the top of my head, I think the only function that calls is it dbms_lock.allocate_unique, which converts an application "lock name" into an internal, unique "lock id".

     

    There's an examples on my blog if you want to see the sort of code you may find in your application somewhere: Synchronisation | Oracle Scratchpad

     

    Regards

    Jonathan Lewis


  • 2. Re: Recursive query?
    user13045898 Newbie
    Currently Being Moderated

    Thanks, am I correct to assume that it is something that Orcale does by "default" rather than some settings we applied I am not aware of?

  • 3. Re: Recursive query?
    Solomon Yakobson Guru
    Currently Being Moderated

    user13045898 wrote:

     

    Thanks, am I correct to assume that it is something that Orcale does by "default" rather than some settings we applied I am not aware of?

     

    I don't know if it is background process or one of auto tasks, but:

     

    SQL> startup
    ORACLE instance started.

    Total System Global Area 2405122048 bytes
    Fixed Size                  2257552 bytes
    Variable Size            1224740208 bytes
    Database Buffers         1157627904 bytes
    Redo Buffers               20496384 bytes
    Database mounted.
    Database opened.

    SQL> select  *
      2    from  v$version
      3  /

    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    PL/SQL Release 11.2.0.3.0 - Production
    CORE    11.2.0.3.0      Production
    TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
    NLSRTL Version 11.2.0.3.0 - Production

    SQL> set serveroutput on
    SQL> declare -- exclude me
      2      cursor v_cur
      3        is
      4          select  count(*) over() cnt,
      5                  sql_text
      6            from  v$sql
      7            where upper(sql_text) like '%DBMS_LOCK_ALLOCATED%'
      8              and sql_text not like '%-- exclude me%';
      9      v_cnt number;
    10  begin
    11      loop
    12        dbms_output.put_line('========== ' || to_char(sysdate,'hh24:mi') || ' ==========');
    13        for v_rec in v_cur loop
    14          dbms_output.put_line(v_rec.sql_text);
    15          v_cnt := v_rec.cnt;
    16        end loop;
    17        exit when v_cnt > 1;
    18        dbms_lock.sleep(60);
    19      end loop;
    20  end;
    21  /
    ========== 13:02 ==========
    ========== 13:03 ==========
    ========== 13:04 ==========
    ========== 13:05 ==========
    ========== 13:06 ==========
    ========== 13:07 ==========
    UPDATE DBMS_LOCK_ALLOCATED SET EXPIRATION = SYSDATE + (:B1 /86400) WHERE ROWID = :B2
    SELECT LOCKID FROM DBMS_LOCK_ALLOCATED WHERE NAME = :B1 FOR UPDATE

    PL/SQL procedure successfully completed.

    SQL>

     

    SY.

  • 4. Re: Recursive query?
    Mark D Powell Guru
    Currently Being Moderated

    Yes, user applications making use of the Oracle provided dbms_lock package is the likely source of the statements in question.  Note that normally user processes do not update the rdbms base tables, i.e, tables owned by user SYS.  So unless someone is doing something that is unsupported by Oracle then this SQL is from Oracle's internal processing.

     

    See http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_lock.htm#sthref5367 for information on the dbms_lock package.

     

    User locks are normally used to single thread acess.  In our case we like to use them to prevent more than one batch execution of a program from happening.

     

    Note Sy's example using dbms_lock.sleep()

     

    HTH -- Mark D Powell --

  • 5. Re: Recursive query?
    Solomon Yakobson Guru
    Currently Being Moderated

    MarkDPowell wrote:

     

    Note Sy's example using dbms_lock.sleep()

     

     

    Mark, DBMS_LOCK.SLEEP has nothing to do with it. It is used in each iteration while references to DBMS_LOCK_ALLOCATED start 5 min after startup. I can repeat the test without using DBMS_LOCK.SLEEP for a clean experiment:

     

    SQL> startup
    ORACLE instance started.

    Total System Global Area 2405122048 bytes
    Fixed Size                  2257552 bytes
    Variable Size            1224740208 bytes
    Database Buffers         1157627904 bytes
    Redo Buffers               20496384 bytes
    Database mounted.
    Database opened.
    SQL> set time on
    14:34:25 SQL> select rownum,sql_text from v$sql where upper(sql_text) like '%DBMS_LOCK_ALLOCATED%'
    14:34:29   2  /

        ROWNUM SQL_TEXT
    ---------- ----------------------------------------------------------------------------------------------------
             1 select rownum,sql_text from v$sql where upper(sql_text) like '%DBMS_LOCK_ALLOCATED%'

    14:34:30 SQL> select rownum,sql_text from v$sql where upper(sql_text) like '%DBMS_LOCK_ALLOCATED%'
    14:39:00   2  /

        ROWNUM SQL_TEXT
    ---------- ----------------------------------------------------------------------------------------------------
             1 UPDATE DBMS_LOCK_ALLOCATED SET EXPIRATION = SYSDATE + (:B1 /86400) WHERE ROWID = :B2
             2 SELECT LOCKID FROM DBMS_LOCK_ALLOCATED WHERE NAME = :B1 FOR UPDATE
             3 select rownum,sql_text from v$sql where upper(sql_text) like '%DBMS_LOCK_ALLOCATED%'

    14:39:00 SQL>

     

    As I said, I don't know if it is Oracle backgound process or some supplied and enabled autotasks but Oracle definitely is using DBMS_LOCK_ALLOCATED internally.

     

    SY.

  • 6. Re: Recursive query?
    user13045898 Newbie
    Currently Being Moderated

    thanks for all replies, I will go through suggestions

  • 7. Re: Recursive query?
    automan Newbie
    Currently Being Moderated

    Then, what is the final conclusion?

     

    1. Is this code, explicitly written into the application code,

    2. Is this code rise by oracle, as a (direct or indirect) consequence of anything written into the application code

    3. Is this a code raised by oracle in any way which could not be controlled by the programmer (nor application code)

Legend

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