Database Software

Hi,

We have an application which contains quite a large schema of tables and packages. These are created in the database via a Makefile and a load of sqlplus commands. When we run this to a standalone 11.0.2.0.1 database there are no problems. When we attempt to create the schema on a 11.0.2.0.1 RAC database it hangs compiling one of the packages - it is always the same package. The sid of the process doing the compilation cannot be killed. No one else is using the database at all. I've carefully checked the packages and although there are some inter dependencies they are compiled in the correct order.

The process in question is marked as:

Current Wait Event library cache pin
Current Wait Class Concurrency
Wait Duration -1 (s)
P1 handle address null
P2 pin address 2465619524
P3 100*mode+namespace null
Object None

Current SQL 5vy6fjjgjhq88
Current SQL Command CREATE PACKAGE BODY
Last Call Duration 1:6:56 (hh:mm:ss)
SQL Trace DISABLED
Current SQL Trace Level 1
Trace With Wait Information DISABLED
Trace With Bind Information DISABLED
Open Cursors 16
Program sqlplus@betdevel (TNS V1-V3)
Service racdb.development.info
Current Module SQL*Plus
Current Action Unavailable

a select from gv$session_wait output for the sid in question shows

INST_ID,SID,SEQ#,EVENT,P1TEXT,P1,P1RAW,P2TEXT,P2,P2RAW,P3TEXT,P3,P3RAW,WAIT_CLASS_ID,WAIT_CLASS#,WAIT_CLASS,WAIT_TIME,SECONDS_IN_WAIT,STATE,WAIT_TIME_MICRO,TIME_REMAINING_MICRO,TIME_SINCE_LAST_WAIT_MICRO
1,148,187,'library cache pin','handle address',2597881556,'000000009AD886D4','pin address',2465619524,'0000000092F65E44','100*mode+namespace',336506392739842,'0001320D00010002',3875070507,4,'Concurrency',-1,4077,'WAITED SHORT TIME',150,null,4076731955

An oradebug hanganalyze 3 shows:

Chain 3:
-------------------------------------------------------------------------------
Oracle session identified by:
{
instance: 1 (racdb.racdb1)
os id: 8074
process id: 47, oracle@racnode1.xxx.local
session id: 148
session serial #: 1451
}
is not in a wait:
{
last wait: 73 min 40 sec ago
blocking: 0 sessions
current sql: <none: error encountered - unable to get kgl lock no-wait>
short stack: ksedsts()+379<-ksdxfstk()+19<-ksdxcb()+1585<-sspuser()+100<-qcuach()+19<-qcsjsel()+978<-qcsjsingle()+64<-qcsfpaj()+822<-qcsprfro()+1761<-qcsprfro_tree()+356<-qcsprfro_tree()+945<-qcsprfro_tree()+945<-qcsprfro_tree()+79<-qcspafq()+216<-qcspqbDescendents()+237<-qcspqb()+171<-qcsdrv()+231<-qcitrans()+774<-qcisem()+276<-ph2csql_analyze()+637<-ph2stm()+726<-ph2sms()+217<-ph2blo()+362<-ph2obl()+80<-ph2sbo()+1351<-ph2qcb()+83<-ph2dcl()+2150<-ph2itm()+50<-ph2its()+1873<-ph2blo()+237<-ph2obl()+80<-ph2uni()+3513<-ph2dr2()+2
wait history:
1. event: 'library cache pin'
time waited: 0.000150 sec
wait id: 186 p1: 'handle address'=0x9ad886d4
p2: 'pin address'=0x92f65e44
p3: '100*mode+namespace'=0x1320d00010002
* time between wait #1 and #2: 0.000009 sec
2. event: 'ges message buffer allocation'
time waited: 0.000001 sec
wait id: 185 p1: 'pool'=0x0
p2: 'request'=0x1
p2: 'request'=0x1
p3: 'allocated'=0x0
* time between wait #2 and #3: 0.000012 sec
3. event: 'ges message buffer allocation'
time waited: 0.000001 sec
wait id: 184 p1: 'pool'=0x0
p2: 'request'=0x1
p3: 'allocated'=0x0
}

Chain 3 Signature: <not in a wait>
Chain 3 Signature Hash: 0x673a0128

I've seen a lot of pages on the web about library cache pin but they nearly always suggest someone is using a package/procedure when it is compiled but I don't see how this can be the case here; I'm the only person using the db and I'm only trying to compile a package that previously did not exist.

I'm no Oracle expert so would appreciate any guidance.

UPDATE: the SQL being run only contains:

ALTER SESSION SET PLSQL_WARNINGS='DISABLE:PERFORMANCE';
CREATE OR REPLACE PACKAGE BODY xxx IS
-- loads of procedures and functions
END xxx;
/
show errors;
QUIT;

Thanks

Edited by: MartinJEvans on May 29, 2012 4:54 PM