Re: hanging package compile
From: Martin <martin.j.evans_at_gmail.com>
Date: Wed, 17 Mar 2010 10:36:39 -0700 (PDT)
Message-ID: <d9a16186-f30c-4457-be82-c9930d5dc134_at_d27g2000yqf.googlegroups.com>
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
Date: Wed, 17 Mar 2010 10:36:39 -0700 (PDT)
Message-ID: <d9a16186-f30c-4457-be82-c9930d5dc134_at_d27g2000yqf.googlegroups.com>
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/pass_at_server.aaa.local:1522/test @pkg_spec.sql
sqlplus -S user/pass_at_server.aaa.local:1522/test @pkg_body.sql
The body hangs.
Martin Received on Wed Mar 17 2010 - 12:36:39 CDT