hanging package compile

#11

On Mar 17, 5:42*pm, Shakespeare <what... (AT) xs4all (DOT) nl> wrote:

Quote:

Op 17-3-2010 18:36, Martin schreef:

On Mar 17, 5:29 pm, Shakespeare<what... (AT) xs4all (DOT) nl> *wrote:
Op 17-3-2010 18:22, Martin schreef:

On Mar 17, 1:06 pm, ddf<orat... (AT) msn (DOT) com> * *wrote:
On Mar 17, 4:48 am, Martin<martin.j.ev... (AT) gmail (DOT) com> * *wrote:

On Mar 16, 5:47 pm, "Gerard H. Pille"<g... (AT) skynet (DOT) 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 ona
new Oracle 11.2 on a new box it hangs during the compilation of a
package:

sqlplus -S xxx/...@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, andif
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 (DOT) aaa.local:1522/test @pkg_spec.sql

sqlplus -S user/p... (AT) server (DOT) 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

#12

On Mar 17, 1:57*pm, Martin <martin.j.ev... (AT) gmail (DOT) com> wrote:

Quote:

On Mar 17, 5:42*pm, Shakespeare <what... (AT) xs4all (DOT) nl> wrote:

Op 17-3-2010 18:36, Martin schreef:

On Mar 17, 5:29 pm, Shakespeare<what... (AT) xs4all (DOT) nl> *wrote:
Op 17-3-2010 18:22, Martin schreef:

On Mar 17, 1:06 pm, ddf<orat... (AT) msn (DOT) com> * *wrote:
On Mar 17, 4:48 am, Martin<martin.j.ev... (AT) gmail (DOT) com> * *wrote:

On Mar 16, 5:47 pm, "Gerard H. Pille"<g... (AT) skynet (DOT) be> * *wrote:

Martin wrote:
We have a large schema and packages that compile find on Oracle11.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 ofa
package:

sqlplus -S xxx/...@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 andyet
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 (DOT) aaa.local:1522/test @pkg_spec.sql

sqlplus -S user/p... (AT) server (DOT) 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

#13

Op 17-3-2010 18:57, Martin schreef:

Quote:

On Mar 17, 5:42 pm, Shakespeare<what... (AT) xs4all (DOT) nl> wrote:
Op 17-3-2010 18:36, Martin schreef:

On Mar 17, 5:29 pm, Shakespeare<what... (AT) xs4all (DOT) nl> wrote:
Op 17-3-2010 18:22, Martin schreef:

On Mar 17, 1:06 pm, ddf<orat... (AT) msn (DOT) com> wrote:
On Mar 17, 4:48 am, Martin<martin.j.ev... (AT) gmail (DOT) com> wrote:

On Mar 16, 5:47 pm, "Gerard H. Pille"<g... (AT) skynet (DOT) 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/...@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 (DOT) aaa.local:1522/test @pkg_spec.sql

sqlplus -S user/p... (AT) server (DOT) 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

#14

On Mar 16, 10:14*pm, Martin <martin.j.ev... (AT) gmail (DOT) com> wrote:

Quote:

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/...@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

Hi,

query v$sess_io, v$session_events and v$session_waits

Regards
Sandy

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

hanging package compile

comp.databases.oracle.misc comp.databases.oracle.misc

Re: hanging package compile - 03-17-2010 , 11:57 AM

Re: hanging package compile - 03-17-2010 , 12:07 PM

Re: hanging package compile - 03-17-2010 , 02:18 PM

Re: hanging package compile - 05-11-2010 , 05:00 AM