Home » SQL & PL/SQL » SQL & PL/SQL » Estimate invalidation
Estimate invalidation [message #243251] Wed, 06 June 2007 10:53 Go to next message
gourmet
Messages: 5
Registered: June 2007
Junior Member
Hi,

I wonder if there is any way to estimate how many objects will oracle invalidate due to the compilation or modification in a unique object. i.e: I have a package specification named "searchanddestroy" Wink and prior to compilating it in the database I want to have an estimation of how many objects will be invalidated by Oracle due to this.

I am using this SQL sentence to obtain the references but I don't know if it is accurate enough so as to satisfy my premise

SELECT O.NAME NAME
  FROM SYS.OBJ$        O,
       SYS.OBJ$        PO,
       SYS.DEPENDENCY$ D,
       SYS.USER$       U,
       SYS.USER$       PU
 WHERE O.OBJ# = D.D_OBJ#
   AND O.OWNER# = U.USER#
   AND PO.OBJ# = D.P_OBJ#
   AND PO.OWNER# = PU.USER#
   AND U.NAME = 'EVILOWNER'
   AND PO.NAME = 'SEARCHANDDESTROY'


Someone have a tip about this?. I need this information to predicts the "destruction" of a database before an implementator apply a developer's object.

Thanks in advance.
Re: Estimate invalidation [message #243253 is a reply to message #243251] Wed, 06 June 2007 10:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Where do you find this query?
Just use dba_dependencies and query it recursively.

Btw, I don't understand your concern. Either you have to recompile and so no choice you have to whatever can be the invalidations.
Either you don't have to and so why recompile?

Regards
Michel
Re: Estimate invalidation [message #243261 is a reply to message #243251] Wed, 06 June 2007 11:21 Go to previous messageGo to next message
gourmet
Messages: 5
Registered: June 2007
Junior Member
Thanks a lot Michael,

I need to know how many objects will be invalidated before applying a specific object in a production database. This data (number of objects) can inform the implementator how risky will it be to apply the object in a live production environment, or in other words, what the percentage of database invalidation will be.

ie: If a developer requests to implement an object compilation that will invalidate 3 objects that a user doesn't use regulary this is a low risk operation; on the other hand, if the same developer's object invalidates 300 objects, then this is a high risk

Is this clear enough?

Cheers!



Re: Estimate invalidation [message #243264 is a reply to message #243261] Wed, 06 June 2007 11:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I still don't understand.
If you have a new procedure this is because there is a business need. You don't change something in the production just for your pleasure.
Moreover, a new procedure or a procedure change is an application upgrade this must not be done during production hours.
In the end, there is almost no risk from invalidation, Oracle recompiles the invalidated objects on the fly. You don't even see it, just a small slow in performances. No problem should happen in production as all must have been detected before in test environment.

Forgot, you can never know all the dependent cursors you will invalidate in production unless you have a tiny application.

Regards
Michel
Re: Estimate invalidation [message #243291 is a reply to message #243251] Wed, 06 June 2007 15:27 Go to previous messageGo to next message
gourmet
Messages: 5
Registered: June 2007
Junior Member
Michael,

In my work the system is alive 24 hrs. at day (when not crashes occurs Wink), but the people that implements objects in it not. In some situations the deploy of a Oracle objects must be done at normal labors hours.

Anyway, I have written a little package that perhaps helps someone else at the forum, this returns an estimate of objects to invalidate in case of compiling or alter of the input object. Here is the magic: Embarassed

-- The specification
CREATE OR REPLACE PACKAGE test AS

FUNCTION f_num_dependencias ( p_nom_objeto VARCHAR2,
                              p_tipo       VARCHAR2 ) RETURN NUMBER;
PROCEDURE p_inicia;
            
PROCEDURE p_objetos;

END test     ;

-- The BODY 
CREATE OR REPLACE PACKAGE BODY test AS

TYPE tabla IS TABLE OF NUMBER
INDEX BY VARCHAR2(255);

objetos tabla; 

PROCEDURE p_objetos IS
l_actual  VARCHAR2(255) := objetos.FIRST;
BEGIN
  WHILE l_actual IS NOT NULL
  LOOP
     dbms_output.put_line(l_actual);
     l_actual := objetos.NEXT(l_actual);
  END LOOP;
END p_objetos;

PROCEDURE p_inicia IS
BEGIN
  objetos.DELETE;
END p_inicia;

FUNCTION f_num_dependencias ( p_nom_objeto VARCHAR2,
                              p_tipo       VARCHAR2 ) RETURN NUMBER IS

CURSOR c1 ( c_nom_objeto VARCHAR2,
            c_tipo       VARCHAR2 ) IS 
SELECT NAME, TYPE 
  FROM DBA_DEPENDENCIES
 WHERE referenced_owner = 'TRON2000'      
   AND referenced_name = c_nom_objeto
   AND referenced_type = c_tipo
   AND (NAME != c_nom_objeto
   OR TYPE != c_tipo);
 
reg c1%ROWTYPE;

cantidad NUMBER;

BEGIN 
 OPEN c1 (upper(p_nom_objeto ), upper(p_tipo));
 FETCH c1 INTO reg;
 WHILE c1%FOUND 
 LOOP           
     
     IF NOT objetos.EXISTS(reg.NAME||'-'||reg.TYPE)
     THEN
         objetos(reg.NAME||'-'||reg.TYPE) := 1;
         cantidad := f_num_dependencias(reg.NAME,reg.TYPE);
     END IF;
     
     FETCH c1 INTO reg;
         
 END LOOP;
 
 RETURN objetos.COUNT;

END f_num_dependencias;

END test;


You must execute the "f_num_dependencias" function and provides a object name and it's object type

Is just a test so no take it very seriously. If someone find it helpful or some bug to fix (or enhancement) just post it to this thread in the forum.

edit: BTW, I am a "just migrated" user of Oracle 9.2.8, before we work with Oracle 7.3.1 on AIX. So, there are big chances that my problem was solved in this or prior version of Oracle.






[Updated on: Wed, 06 June 2007 15:34]

Report message to a moderator

Re: Estimate invalidation [message #243292 is a reply to message #243291] Wed, 06 June 2007 15:31 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Just a tiny little question:
If your system doesn't plan for outage for upgrades, what happens to the bug in the package that invalidates 300 others?
Re: Estimate invalidation [message #243297 is a reply to message #243251] Wed, 06 June 2007 15:48 Go to previous message
gourmet
Messages: 5
Registered: June 2007
Junior Member
Hi Frank,

The system has scheduled outages for upgrades or fixes (once at the end of month, without regular users in the system) but in the day to day (not all days, obviously) there are "hot fixes", this hot fixes are the objects that I wanna control before apply them in the database.

Alway will be invalidate objects, I just want to know if a object to be implemented will "destroy" a tiny portion of the database or if I must kill a developer Smile

Cheers!
Previous Topic: why error generating
Next Topic: Runtime Foreign Keys (for a generic event log)
Goto Forum:
  


Current Time: Sun Dec 11 04:19:09 CST 2016

Total time taken to generate the page: 0.16441 seconds