My Oracle Support Banner

Ultrp.sql taking more time to compile the invalid APP objects (Doc ID 2962698.1)

Last updated on OCTOBER 06, 2023

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.6 and later
Information in this document applies to any platform.

Symptoms

 UTLRP script taking more time to compile application related objects. From hang analyzer I can see below symptoms:

Chains most likely to have caused the hang:
[a] Chain 1 Signature: 'single-task message'<='library cache lock'   >>>>>>>>>> single-task message event causing the issue
    Chain 1 Signature Hash: 0x7f5164c9
[b] Chain 2 Signature: 'single-task message'
    Chain 2 Signature Hash: 0x5c8f902f
[c] Chain 3 Signature: 'single-task message'
    Chain 3 Signature Hash: 0x5c8f902f

===============================================================================
Non-intersecting chains:

-------------------------------------------------------------------------------
Chain 1:
-------------------------------------------------------------------------------
   Oracle session identified by:
   {
               instance: 1 (spadev01.spadev01)
                  os id: 17367300
             process id: 69, oracle@SV920559 (TNS V1-V3)
             session id: 1456
       session serial #: 15213
            module name: 0 (sqlplus@SV920559 (TNS V1-V3))
   }
   is waiting for 'library cache lock' with wait info:
   {
                     p1: 'handle address'=0x7000100b9d03fc0
                     p2: 'lock address'=0x700010072629200
                     p3: '100*mode+namespace'=0x579af00010002
           time in wait: 10 min 0 sec
          timeout after: 4 min 59 sec
                wait id: 372
               blocking: 0 sessions
         current sql_id: 3504341663
            current sql: begin dbms_utility.validate(:1,:2,:3,:4); end;
            short stack: ksedsts()+512<-ksdxfstk()+68<-ksdxcb()+892<-sspuser()+220<-__sighandler()<-thread_wait()+556<-sskgpwwait()+56<-skgpwwait()+268<-IPRA.$ksliwat()+1952<-kslwaitctx()+204<-ksfwaitctx()+40<-kglLockWait()+772<-kgllkal()+1580<-kglLock()+2216<-kglget()+408<-kglgob()+320<-kgldpo0()+960<-kgldpo()+80<-kgldon()+208<-pkldon()+96<-pkloud()+284<-IPRA.$phnnrl_name_resolve_by_loading()+484<-IPRA.$phngdl_get_defining_libunit()+160<-IPRA.$phnrpls_resolve_prefix_libscope()+248<-IPRA.$phnrp_resolve_prefix()+120<-phnr_resolve()+116<-IP
           wait history:
             * time between current wait and wait #1: 0.023191 sec
             1.       event: 'single-task message'
                time waited: 9 min 28 sec
                    wait id: 371            
             * time between wait #1 and #2: 3 min 24 sec
             2.       event: 'single-task message'
                time waited: 9 min 37 sec
                    wait id: 370            
             * time between wait #2 and #3: 3 min 8 sec
             3.       event: 'library cache load lock'
                time waited: 22 min 53 sec
                    wait id: 369              p1: 'object address'=0x7000100baed5b70
                                              p2: 'lock address'=0x700010072ba1dc8
                                              p3: '100*mask+namespace'=0x10003
   }
    

Changes

 DB upgraded from 11g to 19c

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.