4 Replies Latest reply: Jul 2, 2012 7:35 AM by Yann39 RSS

    library cache pin when accessing apex_application_page_regions

    Yann39
      Hi,

      Oracle 11.2.0.3.0, APEX 4.1.1.00.23.

      I am trying to compile a procedure that access apex_application_page_regions to get the region source of a report :
      BEGIN
           -- get report region source
           SELECT region_source
                INTO l_source
                FROM apex_application_page_regions
            WHERE application_id = p_app_id
                 AND page_id = p_app_page_id
                 AND static_id = p_report; 
           [...]
      END;
      I can't compile the procedure. After some time I get ORA-04021: timeout occured while waiting to lock object.

      From v$session :
      STATUS     SERVER          PROGRAM          SQL_EXEC_START          BLOCKING_SESSION_STATUS     BLOCKING_INSTANCE     BLOCKING_SESSION     EVENT               P1TEXT          WAIT_CLASS     STATE
      ACTIVE     DEDICATED     plsqldev.exe     07/02/2012 09:12:52     VALID               1               971               library cache pin     handle address     Concurrency     WAITING
      I am forced to ask developers to close all APEX instances to kill sessions, to be able to compile the procedure...

      Is there any way to bypass this ?

      Thank you.

      Yann.
        • 1. Re: library cache pin when accessing apex_application_page_regions
          Joel_C
          Yann39 wrote:
          Hi,

          Oracle 11.2.0.3.0, APEX 4.1.1.00.23.

          I am trying to compile a procedure that access apex_application_page_regions to get the region source of a report :
          BEGIN
               -- get report region source
               SELECT region_source
                    INTO l_source
                    FROM apex_application_page_regions
                WHERE application_id = p_app_id
                     AND page_id = p_app_page_id
                     AND static_id = p_report; 
               [...]
          END;
          I can't compile the procedure. After some time I get ORA-04021: timeout occured while waiting to lock object.

          From v$session :
          STATUS     SERVER          PROGRAM          SQL_EXEC_START          BLOCKING_SESSION_STATUS     BLOCKING_INSTANCE     BLOCKING_SESSION     EVENT               P1TEXT          WAIT_CLASS     STATE
          ACTIVE     DEDICATED     plsqldev.exe     07/02/2012 09:12:52     VALID               1               971               library cache pin     handle address     Concurrency     WAITING
          I am forced to ask developers to close all APEX instances to kill sessions, to be able to compile the procedure...

          Is there any way to bypass this ?

          Thank you.

          Yann.
          I previously had a problem similar to this. (Un)fortunately, the issue appeared to solve itself...

          Library Cache Pin Wait Event (within the context of APEX)
          • 2. Re: library cache pin when accessing apex_application_page_regions
            Yann39
            Hi Joel,

            I saw your post, I think the issue does not really "solve itself", the problem disappear as soon as the blocking session ends or get killed.

            I think we can passby it, for example by runing the blocking code inside an "execute immediate" statement, so it will not try any lock at compile time, but it's a bit a makeshift job...

            I don't really understand why Oracle is trying to lock the object, as I only try to compile a procedure with a SELECT statement.
            • 3. Re: library cache pin when accessing apex_application_page_regions
              Joel_C
              Have you tried dropping the object altogether?

              Do other DB objects have a dependency on the object you are trying to compile?

              What is the object incidentally - is it the body of a package, a standalone function/procedure or even a trigger?

              edit: I see you've actually said its a procedure. Why not trying packaging it up to see if that makes a difference.

              Edited by: Joel_C on 02-Jul-2012 11:58
              • 4. Re: library cache pin when accessing apex_application_page_regions
                Yann39
                It is just a simple procedure, out of a package because it is called from an APEX URL, and need to be public.
                It gets the region source of a report, parse and execute it using DBMS_SQL, and write content to a CSV file.

                However, I was wrong, the problem occured because I use some APEX_COLLECTIONS in the query, and it seems they are not considered when the procedure is called from the URL (or maybe when executed via DBMS_SQL). So the query returned all records (arround 1 million) instead of some undreds, it is why the first time I run it (this morning), it took more than 3 hours to complete ,and it was locked.
                I don't saw it because I forgot to look at APEX_PUBLIC_USER sessions...

                I can compile without any problem now, I simply have to be careful because I don't have privileges for killing sessions.

                Thank you for trying to help Joel.