Re: hanging package compile
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Wed, 17 Mar 2010 21:18:00 +0100
Message-ID: <4ba138fd$0$22916$e4fe514c_at_news.xs4all.nl>
Op 17-3-2010 18:57, Martin schreef:
>> Op 17-3-2010 18:36, Martin schreef:
>>
>>
>>
>>> On Mar 17, 5:29 pm, Shakespeare<what..._at_xs4all.nl> wrote:
>>>> Op 17-3-2010 18:22, Martin schreef:
>>
>>>>> On Mar 17, 1:06 pm, ddf<orat..._at_msn.com> wrote:
>>>>>> On Mar 17, 4:48 am, Martin<martin.j.ev..._at_gmail.com> wrote:
>>
>>>>>>> On Mar 16, 5:47 pm, "Gerard H. Pille"<g..._at_skynet.be> wrote:
>>
>>>>>>>> Martin wrote:
>>>>>>>>> We have a large schema and packages that compile find on Oracle 11.1.
>>>>>>>>> When I try the same procedure to build our schema and packages on a
>>>>>>>>> new Oracle 11.2 on a new box it hangs during the compilation of a
>>>>>>>>> package:
>>
>>>>>>>>> sqlplus -S xxx/..._at_a.b.c:1521/sid @xxx_body.sql
>>
>>>>>>>>> SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
>>>>>>>>> id1, id2, lmode, request, type
>>>>>>>>> FROM V$LOCK
>>>>>>>>> WHERE (id1, id2, type) IN
>>>>>>>>> (SELECT id1, id2, type FROM V$LOCK WHERE request>0)
>>>>>>>>> ORDER BY id1, request
>>
>>>>>>>>> shows nothing and
>>
>>>>>>>>> select * from v$sql where users_executing> 0
>>
>>>>>>>>> shows:
>>
>>>>>>>>> BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END;
>>>>>>>>> select TIME_WAITED_MICRO from V$SYSTEM_EVENT where event = 'Shared IO
>>>>>>>>> Pool Memory'
>>>>>>>>> select 1 from obj$ where name='DBA_QUEUE_SCHEDULES'
>>
>>>>>>>>> I'm not an expert Oracle DBA. Any ideas?
>>
>>>>>>>>> Martin
>>
>>>>>>>> The package you are trying to compile is in use?
>>
>>>>>>> I don't see how. I have stopped and started the database and there is
>>>>>>> only me using the database and I'm not doing anything. Also, the
>>>>>>> package did not exist in the database before I started compiling it.
>>
>>>>>>> Martin- Hide quoted text -
>>
>>>>>>> - Show quoted text -
>>
>>>>>> No code posted so we can't see what Oracle is trying to access and yet
>>>>>> you expect us to know why your database is 'throwing fits' with this
>>>>>> package body ... the crystal balls are out of service, the Ouija
>>>>>> boards are of little use and parlor tricks won't answer that
>>>>>> question. Post more information if you really want an answer, and if
>>>>>> you can't post the actual code you could provide SOME information on
>>>>>> what this package does and which tables/views it accesses (especially
>>>>>> data dictionary views) as the optimizer has changed between 11.1 and
>>>>>> 11.2 and what was once valid code may not be any more.
>>
>>>>>> David Fitzjarrell
>>
>>>>> I don't expect you to know what the problem is without further
>>>>> information - with, or without your crystal ball.
>>
>>>>> As I said in my original posting I am not an Oracle expert just
>>>>> someone using it so I was unsure what information to provide but hoped
>>>>> someone else might know what would be useful to start diagnosing the
>>>>> problem.
>>
>>>>> I cannot post all the package in question and in any case it is nearly
>>>>> 18000 lines long. I don't think it does much that complicated or
>>>>> elaborate but there is a lot of it. Here is a summary of it use:
>>
>>>>> 102 externally accessible + 82 internal procedures
>>>>> 33 externally accessible + 23 internal functions
>>>>> some functions return SYS_REFCURSOR types
>>>>> some reference cursors returned are generated from dynamic sql
>>>>> Some fns/procs take lobs
>>>>> A few constants
>>>>> A number of simple types declared where fields are types based on
>>>>> columns in tables we created
>>>>> A number of cursors declared in pkg body where they are used more than
>>>>> once (all on our tables, with some joins and sometimes for update)
>>
>>>>> A lot of calls to functions and procedures in other packages (all the
>>>>> other packages were compiled successfully prior to attempting to
>>>>> compile the package in question which hangs).
>>
>>>>> It uses some global views (on our tables) compiled before the package
>>>>> in question.
>>
>>>>> Quite a lot of views are used but they are all on tables we created. I
>>>>> cannot see any data dictionary stuff.
>>
>>>>> It would be very difficult to describe what the package does. It
>>>>> performs no manipulation of any system tables other than what may
>>>>> happen via using Oracle supplied packages. It works on tables I
>>>>> created, mostly inserting and updating and a few deletes. All tables
>>>>> have an insert/update trigger which at a minimum sets a timestamp and
>>>>> user column to the current time and user. Some triggers also call the
>>>>> dbms_alert package and a few raise application errors on certain types
>>>>> of activity. Some of the procedures in the package are called by the
>>>>> dbms_scheduler package. A couple of procedures lock a table for the
>>>>> duration of the procedure. The package is created with definer rights.
>>
>>>>> There is one cyclic dependency that occurs before the package in
>>>>> question (which cannot easily be avoided right now - basically a
>>>>> trigger uses a constant in a package and the package uses the table
>>>>> the trigger is on and the sql defining the table and trigger is in one
>>>>> file) but we issue DBMS_UTILITY.compile_schema after that package
>>>>> which corrects it.
>>
>>>>> Also, I left the compile running for over a day without timeout and
>>>>> examining the database via sql developer shows all the package fns/
>>>>> procs are visible but sqlplus has not returned and an oracle instance
>>>>> is very busy.
>>
>>>>> I've downgraded to 11.1 for now but if anyone has an ideas how to home
>>>>> in on the problem I will go back to 11.2 to investigate.
>>
>>>>> Thanks
>>
>>>> How exactly do you compile your package? Compile, compile body, tool?
>>>> What part is hanging: compile or compile body?
>>
>>>> Shakespeare
>>
>>> compile package spec with:
>>
>>> sqlplus -S user/p..._at_server.aaa.local:1522/test @pkg_spec.sql
>>
>>> sqlplus -S user/p..._at_server.aaa.local:1522/test @pkg_body.sql
>>
>>> The body hangs.
>>
>>> Martin
>>
>> In stead of running the script again, could you try 'alter package XXX
>> compile', 'alter package XXX compile body'?
>>
>> Shakespeare
Date: Wed, 17 Mar 2010 21:18:00 +0100
Message-ID: <4ba138fd$0$22916$e4fe514c_at_news.xs4all.nl>
Op 17-3-2010 18:57, Martin schreef:
> On Mar 17, 5:42 pm, Shakespeare<what..._at_xs4all.nl> wrote:
>> Op 17-3-2010 18:36, Martin schreef:
>>
>>
>>
>>> On Mar 17, 5:29 pm, Shakespeare<what..._at_xs4all.nl> wrote:
>>>> Op 17-3-2010 18:22, Martin schreef:
>>
>>>>> On Mar 17, 1:06 pm, ddf<orat..._at_msn.com> wrote:
>>>>>> On Mar 17, 4:48 am, Martin<martin.j.ev..._at_gmail.com> wrote:
>>
>>>>>>> On Mar 16, 5:47 pm, "Gerard H. Pille"<g..._at_skynet.be> wrote:
>>
>>>>>>>> Martin wrote:
>>>>>>>>> We have a large schema and packages that compile find on Oracle 11.1.
>>>>>>>>> When I try the same procedure to build our schema and packages on a
>>>>>>>>> new Oracle 11.2 on a new box it hangs during the compilation of a
>>>>>>>>> package:
>>
>>>>>>>>> sqlplus -S xxx/..._at_a.b.c:1521/sid @xxx_body.sql
>>
>>>>>>>>> SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
>>>>>>>>> id1, id2, lmode, request, type
>>>>>>>>> FROM V$LOCK
>>>>>>>>> WHERE (id1, id2, type) IN
>>>>>>>>> (SELECT id1, id2, type FROM V$LOCK WHERE request>0)
>>>>>>>>> ORDER BY id1, request
>>
>>>>>>>>> shows nothing and
>>
>>>>>>>>> select * from v$sql where users_executing> 0
>>
>>>>>>>>> shows:
>>
>>>>>>>>> BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END;
>>>>>>>>> select TIME_WAITED_MICRO from V$SYSTEM_EVENT where event = 'Shared IO
>>>>>>>>> Pool Memory'
>>>>>>>>> select 1 from obj$ where name='DBA_QUEUE_SCHEDULES'
>>
>>>>>>>>> I'm not an expert Oracle DBA. Any ideas?
>>
>>>>>>>>> Martin
>>
>>>>>>>> The package you are trying to compile is in use?
>>
>>>>>>> I don't see how. I have stopped and started the database and there is
>>>>>>> only me using the database and I'm not doing anything. Also, the
>>>>>>> package did not exist in the database before I started compiling it.
>>
>>>>>>> Martin- Hide quoted text -
>>
>>>>>>> - Show quoted text -
>>
>>>>>> No code posted so we can't see what Oracle is trying to access and yet
>>>>>> you expect us to know why your database is 'throwing fits' with this
>>>>>> package body ... the crystal balls are out of service, the Ouija
>>>>>> boards are of little use and parlor tricks won't answer that
>>>>>> question. Post more information if you really want an answer, and if
>>>>>> you can't post the actual code you could provide SOME information on
>>>>>> what this package does and which tables/views it accesses (especially
>>>>>> data dictionary views) as the optimizer has changed between 11.1 and
>>>>>> 11.2 and what was once valid code may not be any more.
>>
>>>>>> David Fitzjarrell
>>
>>>>> I don't expect you to know what the problem is without further
>>>>> information - with, or without your crystal ball.
>>
>>>>> As I said in my original posting I am not an Oracle expert just
>>>>> someone using it so I was unsure what information to provide but hoped
>>>>> someone else might know what would be useful to start diagnosing the
>>>>> problem.
>>
>>>>> I cannot post all the package in question and in any case it is nearly
>>>>> 18000 lines long. I don't think it does much that complicated or
>>>>> elaborate but there is a lot of it. Here is a summary of it use:
>>
>>>>> 102 externally accessible + 82 internal procedures
>>>>> 33 externally accessible + 23 internal functions
>>>>> some functions return SYS_REFCURSOR types
>>>>> some reference cursors returned are generated from dynamic sql
>>>>> Some fns/procs take lobs
>>>>> A few constants
>>>>> A number of simple types declared where fields are types based on
>>>>> columns in tables we created
>>>>> A number of cursors declared in pkg body where they are used more than
>>>>> once (all on our tables, with some joins and sometimes for update)
>>
>>>>> A lot of calls to functions and procedures in other packages (all the
>>>>> other packages were compiled successfully prior to attempting to
>>>>> compile the package in question which hangs).
>>
>>>>> It uses some global views (on our tables) compiled before the package
>>>>> in question.
>>
>>>>> Quite a lot of views are used but they are all on tables we created. I
>>>>> cannot see any data dictionary stuff.
>>
>>>>> It would be very difficult to describe what the package does. It
>>>>> performs no manipulation of any system tables other than what may
>>>>> happen via using Oracle supplied packages. It works on tables I
>>>>> created, mostly inserting and updating and a few deletes. All tables
>>>>> have an insert/update trigger which at a minimum sets a timestamp and
>>>>> user column to the current time and user. Some triggers also call the
>>>>> dbms_alert package and a few raise application errors on certain types
>>>>> of activity. Some of the procedures in the package are called by the
>>>>> dbms_scheduler package. A couple of procedures lock a table for the
>>>>> duration of the procedure. The package is created with definer rights.
>>
>>>>> There is one cyclic dependency that occurs before the package in
>>>>> question (which cannot easily be avoided right now - basically a
>>>>> trigger uses a constant in a package and the package uses the table
>>>>> the trigger is on and the sql defining the table and trigger is in one
>>>>> file) but we issue DBMS_UTILITY.compile_schema after that package
>>>>> which corrects it.
>>
>>>>> Also, I left the compile running for over a day without timeout and
>>>>> examining the database via sql developer shows all the package fns/
>>>>> procs are visible but sqlplus has not returned and an oracle instance
>>>>> is very busy.
>>
>>>>> I've downgraded to 11.1 for now but if anyone has an ideas how to home
>>>>> in on the problem I will go back to 11.2 to investigate.
>>
>>>>> Thanks
>>
>>>> How exactly do you compile your package? Compile, compile body, tool?
>>>> What part is hanging: compile or compile body?
>>
>>>> Shakespeare
>>
>>> compile package spec with:
>>
>>> sqlplus -S user/p..._at_server.aaa.local:1522/test @pkg_spec.sql
>>
>>> sqlplus -S user/p..._at_server.aaa.local:1522/test @pkg_body.sql
>>
>>> The body hangs.
>>
>>> Martin
>>
>> In stead of running the script again, could you try 'alter package XXX
>> compile', 'alter package XXX compile body'?
>>
>> Shakespeare
> > I don't see how I could do this since the very first time I try and > compile it, it hangs. This is not a recompilation of an existing > package but a brand new compilation of a package which previously did > not exist. Also the session which is compiling the package cannot be > killed - if you try it just says it is marked to be killed. After this > you are in real trouble as you cannot even shut the database down > unless you kill the oracle process manually. > > Martin
There's always 'shutdown abort'. I was wondering if the script leaves an uncompiled package body or nothing at all. I guess the script creates the package and compiles it. If it hangs, it may already have created the package, but be hanging in the compilation phase. That's why I asked to do a compile package body, for example after bouncing the database.
Shakespeare Received on Wed Mar 17 2010 - 15:18:00 CDT