Forum OpenACS Improvement Proposals (TIPs): TIP #65: (Approved) Modify APM to recompile busted Oracle PACKAGE and PACKAGE BODY objects after upgrade

Collapse
1: TIP #65: (Approved) Modify APM to recompile busted Oracle PACKAGE and PACKAGE BODY objects after upgrade
Posted by Don Baccus on 05/25/04 05:11 PM
Dependencies can cause Oracle PACKAGE and PACKAGE BODY objects to become invalid due to dependency problems after an upgrade script is run that does a CREATE OR REPLACE on a package.  Sloan ran into a very, very large number of these which Andrew first recompiled by hand.  I suggested he write some SQL to grab the bad PACKAGE [BODY] objects from the proper in Oracle internal table and to first recompile all broken PACKAGE (i.e. header) objects followed by all broken PACKAGE BODY objects.

This fixed all but one or two out of dozens or a couple of hundred broken objects automatically, a great improvement.

My proposal is that the APM, after running an Oracle upgrade scripts, use Andrew's SQL code to fix any broken PAKCAGE and PACKAGE BODY objects (in that order) and report any errors (just as it does now when running *-create.sql scripts).  This will greatly simplify the upgrade process for complex upgrades of many packages, especially for those who skip upgrades for some period of time then upgrade several versions at once.

Collapse
2: Re: TIP 65: (proposed) Modify APM to recompile busted Oracle PACKAGE and PACKAGE BODY objects after upgrade (response to 1)
Posted by Andrew Piskorski on 05/26/04 11:01 PM
You do not need to write any script to recompile broken PL/SQL, because one (or more) already exists, and has been discussed here many times over the years.
Collapse
3: Re: TIP 65: (proposed) Modify APM to recompile busted Oracle PACKAGE and PACKAGE BODY objects after upgrade (response to 1)
Posted by Dirk Gomez on 05/27/04 02:24 AM
Dosn't Oracle even try to recompile invalid objects automatically?

(It certainly does try it for views. IIRC it does try it for package bodies as well)

Collapse
4: Re: TIP 65: (proposed) Modify APM to recompile busted Oracle PACKAGE and PACKAGE BODY objects after upgrade (response to 1)
Posted by Dion Sweat on 05/27/04 03:42 AM
Oracle supplies a script to recompile invalid packages and procedures.  You'll usually want to run this after you do a manual database upgrade.  This is so you'll incur the cost of recompiling during the migration process rather than run time.

The sript is located in $ORACLE_HOME/rdbms/admin/utlrp.sql

Collapse
5: Re: TIP 65: (proposed) Modify APM to recompile busted Oracle PACKAGE and PACKAGE BODY objects after upgrade (response to 3)
Posted by Andrew Piskorski on 05/27/04 04:05 PM
Dirk, the short answer is, "no". You can read the old threads for more info, but basically, Oracle does attempt to recompile invalidated packages when they are used, but it does not do it in dependency order so "attempt" is the right word - and the attempt can easily fail.
Collapse
6: Re: TIP 65: (proposed) Modify APM to recompile busted Oracle PACKAGE and PACKAGE BODY objects after upgrade (response to 4)
Posted by Andrew Piskorski on 05/27/04 04:14 PM
Dion, intersting, I did not know that this utlrp.sql script even existed. Looking at the version shipped with Oracle 8.1.7.4, it sounds as if that script will do the job - so that makes 3 entirely different scripts all available to solve this same problem.

However, the comments in utlrp.sql do specifically say that it must be run as Oracle user SYS, which is a very serious limitation. Someone should try it and tell us if that is in fact the case. If it is, it could probably be hacked to fix that, but you could also just use either Trezzo's or Kyte's scripts instead, which don't have that problem.

Btw, of the 3 solutions which should work, the only one I've ever actually used is Trezzo's recompile_all_objects. I found it first, about 4 years ago now, it worked just fine for me, and thus I never tried any of these others.

Collapse
8: Re: TIP 65: (proposed) Modify APM to recompile busted Oracle PACKAGE and PACKAGE BODY objects after upgrade (response to 1)
Posted by Don Baccus on 05/28/04 06:04 PM
Dirk ... if the answer were "yes" Andrew wouldn't've run into the problem in the first place!

Essentially I've TIP'd this for Andrew, who's in Iceland at the moment on holiday.  I don't - and I can't imagine Andrew does - care which script or query is used (it's very simple anyway).

The main point of the TIP is to have the APM do it for you automatically ...

Collapse
10: Re: TIP 65: (proposed) Modify APM to recompile busted Oracle PACKAGE and PACKAGE BODY objects after upgrade (response to 1)
Posted by Dirk Gomez on 05/28/04 07:50 PM
I vaguely remembered something about automatic recompilation (and probable performance hits for the first access). See here:

Oracle automatically records dependencies among objects to alleviate the complex job of dependency management for the database administrator and users. For example, if you alter a table on which several stored procedures depend, Oracle automatically recompiles the dependent procedures the next time the procedures are referenced (executed or compiled against).

But nevermind, it probably says somewhere what the rules for recompilation on the fly are and how intricate it can get.

Collapse
12: Re: TIP #65: (Approved) Modify APM to recompile busted Oracle PACKAGE and PACKAGE BODY objects after upgrade (response to 1)
Posted by Andrew Grumet on 06/07/04 09:04 PM
I was away for most of this discussion but, yeah, let's use existing code.  Here's a couple more while we're at it

http://download-west.oracle.com/docs/cd/A87860_01/doc/appdev.817/a76936/dbms_ut2.htm#1002085
http://otn.oracle.com/oramag/oracle/04-may/o34tech_plsql.html

I'll try out utlrp.sql and the others and report back.

Collapse
13: Re: TIP #65: (Approved) Modify APM to recompile busted Oracle PACKAGE and PACKAGE BODY objects after upgrade (response to 1)
Posted by Andrew Grumet on 06/07/04 09:07 PM
As a side note, views containing "select * from table" do not recompile correctly under "alter view compile" when columns are added to the underlying table.  I'm fiddling around with some code that selects the source from user_views and re-runs it, but I bet code exists for this too.  Does it?
Collapse
14: Re: TIP #65: (Approved) Modify APM to recompile busted Oracle PACKAGE and PACKAGE BODY objects after upgrade (response to 1)
Posted by Andrew Grumet on 06/07/04 09:17 PM
utlrp.sql does not run as a normal user.

SQL> @/oracle/m00/app/oracle/product/8.1.7/rdbms/admin/utlrp
          from obj$ o, user$ u
                *
ERROR at line 25:
ORA-06550: line 25, column 17:
PLS-00201: identifier 'OBJ$' must be declared
[blah blah blah]