hanging package compile
Martin
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/x...@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
Martin
> 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:
>
>
> 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
Some additional info:
select * from v$session_wait where sid = 33
33 228 db file sequential read file# 4 0000000000000004 block# 978
00000000000003D2 blocks 1 0000000000000001 1740759767 8 User I/O 1
2399 WAITED KNOWN TIME 5170 (null) 2399084207
Martin
Gerard H. Pille
The package you are trying to compile is in use?
Martin
> 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:
>
>
> > 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
ddf
>
> - 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
Martin
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
Shakespeare
How exactly do you compile your package? Compile, compile body, tool?
What part is hanging: compile or compile body?
Shakespeare
Gerard H. Pille
>
> 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:
>
Split it up.
Martin
compile package spec with:
sqlplus -S user/pa...@server.aaa.local:1522/test @pkg_spec.sql
sqlplus -S user/pa...@server.aaa.local:1522/test @pkg_body.sql
The body hangs.
Martin
Shakespeare
In stead of running the script again, could you try 'alter package XXX
compile', 'alter package XXX compile body'?
Shakespeare
Martin
>
> > sqlplus -S user/p...@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
ddf
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
Shakespeare
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
sandeep pande
> 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:
>
>
> 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