Re: hanging package compile
From: ddf <oratune_at_msn.com>
Date: Wed, 17 Mar 2010 11:07:11 -0700 (PDT)
Message-ID: <687581fc-1cc6-416b-859d-6f344d4921c0_at_v34g2000prm.googlegroups.com>
On Mar 17, 1:57 pm, Martin <martin.j.ev..._at_gmail.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
Date: Wed, 17 Mar 2010 11:07:11 -0700 (PDT)
Message-ID: <687581fc-1cc6-416b-859d-6f344d4921c0_at_v34g2000prm.googlegroups.com>
On Mar 17, 1:57 pm, Martin <martin.j.ev..._at_gmail.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
Please post the full Oracle version where this compile has issues (meaning 11.2.0.1,;11.1.0.6,; etc.) Also your 10,000 foot view of the package body really doesn't help anyone troubleshoot this problem. Is your DBA aware of this issue? Has he or she run an oradebug hanganalyze while this package body is compiling?
David Fitzjarrell Received on Wed Mar 17 2010 - 13:07:11 CDT