3 Replies Latest reply: Jul 24, 2013 10:45 AM by Pollocks01 RSS

    Library Cache Lock when Re-Registering an XSD

    Pollocks01
      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 Linux: Version 11.2.0.3.0 - Production
      NLSRTL Version 11.2.0.3.0 - Production
      
      

       

      If I "use" an xsd in one session, then if I try to re-register the xsd in another session, the re-register session is indefinitely waiting on a library cache lock:

       

      "use" the xsd is one session: 
      
      create or replace procedure sp_schema_validate_xml(pi_xml IN XMLType, 
                                                         pi_xml_schema IN VARCHAR2) 
      is 
        v_count number; 
        v_err_msg varchar2(4000); 
        v_xml xmltype; 
      begin 
            --do level 1 schema validation 
            if pi_xml.isSchemaValid(pi_xml_schema) = 0 then --level 1 vldtn failed. do level 2 
      
              begin --do level 2 vldtn 
                v_xml := pi_xml.createSchemaBasedXML(pi_xml_schema); 
      
                v_xml.schemaValidate(); 
               
              end; 
            
            end if; 
      
      end sp_schema_validate_xml; 
      / 
      
      
      create or replace directory pipedoc as '/home/oracle/testfiles/HUD/ERS_HUD_VM01/PIPE'; 
      
      begin 
        sp_schema_validate_xml(pi_xml => xmltype(bfilename('PIPEDOC', 'DecryptedFile-36805759.xml'),0), 
                               pi_xml_schema => 'PIPE/Power/V4.0/PIPEDocument.xsd' 
                              ); 
      end; 
      / 
      
      Now try to re-register the XSD in another session: 
      
      create or replace directory XSD as '/home/oracle/testfiles/HUD/ERS_HUD_VM01/PIPE/XSDs/Power'; 
      
      create or replace function f_exists_xsd(pi_list_item IN varchar2) return boolean 
        is 
          v_count pls_integer; 
        begin 
           
          select count(1) into v_count 
          from user_xml_schemas 
          where schema_url = pi_list_item; 
           
          if v_count = 0 then 
            return false; 
          else 
            return true; 
          end if; 
         
        end f_exists_xsd; 
      / 
      
      
      
      declare 
        v_xsd varchar2(100) := 'PIPE/Power/V4.0/PIPEDocument.xsd'; 
      begin 
        if f_exists_xsd(v_xsd) = true then 
           dbms_xmlschema.deleteschema(v_xsd, 
                                           dbms_xmlschema.DELETE_CASCADE_FORCE); 
        end if; 
                      
        dbms_xmlschema.registerSchema(schemaURL => v_xsd, 
                                         schemaDoc => xmltype(bfilename('XSD', 'PIPEDocument.xsd'), 0), 
                                         local => TRUE, 
                                         genTypes => FALSE, 
                                         genbean => FALSE, 
                                         genTables => FALSE, 
                                         force => FALSE, 
                                         owner => user); 
      end; 
      / 
      
      The session which is trying to re-register the XSD will wait indefinitely with a "library cache lock", regardless of whether the other session issues a commit or not. The lock only gets released when the other session disconnects. 
      
      I consider this to be an Oracle product defect but of course we'll have to workaround this because Oracle Support hasn't been forthcoming with help recently.
      

       

      I found a query previously which successfully identified the blocking session so that we could terminate the process:

       

      select 
      distinct 
         ses.ksusenum sid, ses.ksuseser serial#, ses.ksuudlna username,KSUSEMNM module, 
         ob.kglnaown obj_owner, ob.kglnaobj obj_name 
         ,lk.kgllkcnt lck_cnt, lk.kgllkmod lock_mode, lk.kgllkreq lock_req 
         , w.state, w.event, w.wait_Time, w.seconds_in_Wait 
      from 
       x$kgllk lk, x$kglob ob,x$ksuse ses 
        , v$session_wait w 
      where lk.kgllkhdl in 
      (select kgllkhdl from x$kgllk where kgllkreq >0 ) 
      and ob.kglhdadr = lk.kgllkhdl 
      and lk.kgllkuse = ses.addr 
      and w.sid = ses.indx 
      order by seconds_in_wait desc
      

       

      My issue is two-fold:

       

      1. The above query only works on instances which got upgraded from 10gR2 and not on those which were built fresh on 11Gr2 as seemingly the x$kgllk view and related synonyms are dropped in 11G (See Oracle support Doc ID 878623.1).
      2. Even if I can find alternate views to use, I don't like the thought of my deployment/upgrade script having to "blindly" kill a session in order to succeed in re-registering the XSD.

       

      How can I go about properly locking and freeing the XSD so as to avoid the library cache lock? Alternatively, is there a way to flush the library cache?

        • 1. Re: Library Cache Lock when Re-Registering an XSD
          Pollocks01

          I think that dbms_shared_pool.purge should work but it errors when I try to run it for a given XML Schema. The name I pass it is the INT_OBJNAME from user_xml_schemas:

           

          SQL> begin
            2   dbms_shared_pool.purge('XDB.XD4jxhTfWIJIjgQwEAAH92jA==');
            3  end;
            4  /
          begin
          *
          ERROR at line 1:
          ORA-00911: invalid character
          ORA-06512: at "SYS.DBMS_UTILITY", line 156
          ORA-06512: at "SYS.DBMS_SHARED_POOL", line 72
          ORA-06512: at "SYS.DBMS_SHARED_POOL", line 115
          ORA-06512: at line 2
          
          

           

          I've tried this as SYS and also as the schema object owner. I've tried it with and without the XDB. prefix.

           

          ideas?

          • 2. Re: Library Cache Lock when Re-Registering an XSD
            Pollocks01

            Hmm - I think I resolved this myself by considering something that Odie_63 or Marco Gralike had mentioned previously........

             

            I modified my schema validate procedure to free all resources and I no longer get the library cache lock:

             

            create or replace procedure sp_schema_validate_xml(pi_xml         IN XMLType,
                                                               pi_xml_schema  IN VARCHAR2)
            is
              v_count number;
              v_err_msg varchar2(4000);
              v_xml xmltype;
            begin    
                  --do level 1 schema validation
                  if  pi_xml.isSchemaValid(pi_xml_schema) = 0 then --level 1 vldtn failed. do level 2
            
            
                    begin --do level 2 vldtn
                      v_xml := pi_xml.createSchemaBasedXML(pi_xml_schema);
            
            
                      v_xml.schemaValidate();                            
                    
                    end;
                 
                  end if;          
                  
                  dbms_session.modify_package_state(dbms_session.free_all_resources);
            
            
            end sp_schema_validate_xml;
            /   
            
            • 3. Re: Library Cache Lock when Re-Registering an XSD
              Pollocks01

              ...and for completeness, dbms_session.modify_package_state(DBMS_SESSION.REINITIALIZE); also seems to work. THis is meant to be less of a performance hit than free_all_resources.