Home > SQL-PL/SQL > How to analyze Object Dependency?

How to analyze Object Dependency?

In this article, I will talk about object dependency analysis for oracle database object deployments (package, procedure, function, table, index).

Without dependency analyzing, deployments can cause serious problems in the database. Also it can cause service outage and damage the corporate vision. May be too much dependence of object which we want to deploy on the database. And depended objects may be invalidated. Invalidated object does not constitute any problem if you compile it before using that object by the end user. If invalidated object called by the end user frequently then it cause to take an error by the end user.

Performance as a service quality, is one of the most important factors for us.

So what should we be looking object deployments? How to analyze object dependency? Now let’s search the answer for these questions.

You can check object dependency with following query. As a result of the following query, you must get at least one record (of the object itself). This situation shows the existence of the object. If there is no record in the database then there is no such an object.

SELECT name FROM dba_dependencies d  WHERE (d.referenced_name,d.referenced_type) in(
SELECT name,type FROM dba_dependencies d1 WHERE d1.referenced_name IN ('OBJECT_NAME')
)
UNION
SELECT name FROM dba_dependencies d2 WHERE d2.referenced_name IN ('OBJECT_NAME')
UNION
SELECT object_NAME FROM dba_objects WHERE object_NAME IN ('OBJECT_NAME')

1- SP DEPLOYMENTS:
You must check existence of SPEC on package deployments.If there is SPEC then it will be invalidate all depended objects. In this situation you must check usage of this package. If usage of this package is too much then you musn’t deploy it. Because it causes concurrency waits on database. You can check usage an object with following query;

SELECT name, loads, executions, pins,c.*
FROM v$db_object_cache c
WHERE name in
(SELECT name FROM dba_dependencies d  WHERE (d.referenced_name,d.referenced_type) in(
SELECT name,type FROM dba_dependencies d1 WHERE d1.referenced_name IN ('OBJECT_NAME')
)
UNION
SELECT name FROM dba_dependencies d2 WHERE d2.referenced_name IN ('OBJECT_NAME')
UNION
SELECT object_NAME FROM dba_objects WHERE object_NAME IN ('OBJECT_NAME'))
AND pins >0;

2- ADD, DROP, MODIFY COLUMN  Commands:
Adding, Dropping, Modifying a column(ADD, DROP, MODIFY) and modifying constraint will be invalidate packages or stored procedures used by table.You should make dependency analysis for the table. And you can do planned deployment.

3- ADD, DROP CONSTRAINT Commands:
Adding, Dropping constraints  (ADD, DROP CONSTRAINT) will not invalidate objects. But it locks the table when the constraint type is PK/FK.

4- CREATE INDEX :
CREATE INDEX command locks the table when it creating index . Be sure that table is not used frequently.

5- CREATE TRIGGER :
Create trigger command doesn’t lock the table. You can create dml trigger on the table during day. When you create insert trigger on a table that insert statements are frequently on, it causes the short-term pin problem.

6- CREATE TABLE:
You must examine constaints in table create script. If there is PK/FK constraint, be sure! because you must consider 3th step.

Talip Hakan ÖZTÜRK

  1. TSR
    July 2, 2013 at 10:00 am

    Very nice post Talip…

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: