Infection pattern analysis in Oracle

Quite recently some customers have asked me a somewhat similar question. They all have applications residing in an Oracle database. The application is a standard application build by a third party. During the past years they have been building additions to those applications. Now they come to a moment in time that the third party vendor is providing upgrades to the software. The update will involve a whole set of newly developed or already existing however changed database objects.

The past couple of years they have been building extensions which have caused a version lock-in. Question that is now popping up is how intertwined is are the custom build extensions which the standard code and with the parts of the code that will change. To give a clear answer on this you will have to check the dependencies that are within the application (including extensions).

When unraveling such a puzzle you will have to look at infection path analysis. For example we have the below application which includes an extension. A, B, C, D & E are the standard components of the application. 1, 2, 3 & 4 are custom objects build as an extension. You can see the dependencies towards standard components visualized with the lines coming from the custom objects towards the standard objects.

In the above example all the red objects will change during an upgrade. This means that the customized objects you should look and, based upon this view, are object 1 (depending on changing object A) and object 3 (depending on changing object A & D). 

This is a first generation infection path which only shows you the direct relations between custom objects and changing objects. You should take this a step deeper. In the below example we have gone a dependency level deeper and you can see that 4 is depending on 3 and 1 is depending on 2.

As we potentially have to change 3 to cope with the changes in A & D we also have to look at the potential code change of object 3. And if 3 is changed this might affect object 4.

Object 1 is depending on object 2 and in this level of the infection 2 is not changed so this not changing anything on the list of objects to check.

With every level you go deeper into an infection pattern you will see more objects are potentially “infected” by the change and should have a proper look at by a developer. You can also create “resistant islands “which are in no way affected by change. Potentially you can have your entire database analyzed with a proper infection pattern algorithm. If this is wise is for debate because it can cloud the usability and the correctness of your outcome. In general I do tend to think a 3 level of 4 level deep infection pattern analysis is proper to be used within Oracle databases.

When you are trying to develop your own infection pattern algorithm for a database it is good to have a look at a couple of things.

Within the database dependencies are stored in sys.dependency$ and more information about the objects are stored in dba_objects. Combining the 2 in a query will give you a headstart in building your algorithm. As a simple example if I wanted to know something about object 123321 I could fire of the query;

select 
      objects2.owner as dep_owner,
      objects2.object_name as dep_object_name,
      objects2.object_type as dep_object_type
  from 
      sys.dependency$ depen,
      dba_objects objects2
where 
      depen.p_obj# = 123321
      and objects2.owner not like 'SYS'
      and objects2.object_type not in ('SYNONYM')
      and objects2.object_id = depen.d_obj#
order by 1, 2, 3;

If you build this into a more profound PL/SQL script and you would add a list of changing components to start with you could create a dependency list. The easiest way is to output it to a flat file and provide it to your developers and consultants as a reference of things to look into as they are most likely to be hit by the upgrade.

However, as we are humans and humans are somewhat visually and like to see things in pictures a great way to do this is not to output it to a flat text file however build as a secondary output a file that you can parse via DOT. DOT is a markup language used to plot relation diagrams as the ones above used in the examples. As DOT is a free and opensource way of doing this and it saves you hours and hours of building diagrams in MS Visio I do think it is worth looking into the DOT Language documentation.