(21) Object Dependencies


  • If you alter the definition of a referenced object, dependent objects might not continue to function without error, depending on the type of alteration. For example, if you drop a table, no view based on the dropped table is usable.

[ALL_ | DBA_ | USER_]DEPENDENCIES: describe dependencies among db objects

SQL> create or replace view highsal as
 select * from employees
 where salary >=100000;
 
SQL> create or replace view commissioned as
  select first_name, last_name, commission_pct
  from employees
  where commission_pct > 0.00;

SQL> select object_name, status 
from user_objects
where object_type = 'VIEW'
order by object_name;

OBJECT_NAME      STATUS
---------------- -------
COMMISSIONED     VALID
HIGHSAL          VALID

SQL> alter table employees modify email varchar2(100);

SQL> select object_name, status 
from user_objects
where object_type = 'VIEW'
order by object_name;


OBJECT_NAME      STATUS
---------------- -------
COMMISSIONED     VALID
HIGHSAL          INVALID


SQL> select name, type, referenced_name, referenced_type, dependency_type
from user_dependencies
order by name;

NAME            TYPE          REFERENCED_NAME  REFERENCED_TYPE  DEPENDENCY_TYPE
-----------     ------------  ---------------  --------------   -------------
CIRCLE_AREA     PROCEDURE     DBMS_OUTPUT      SYNONYM          HARD
CIRCLE_AREA     PROCEDURE     MY_PKG           PACKAGE          HARD
...
COMMISSIONED    VIEW          EMPLOYEES        TABLE            HARD
HIGHSAL         VIEW          EMPLOYEES        TABLE            HARD
...
MY_PKG          PACKAGE BODY  MY_PKG           PACKAGE          HAD
MY_PKG          PACKAGE BODY  DBMS_DB_VERSION  PACKAGE          HARD
...
MY_PKG          PACKAGE       DBMS_DB_VERSION  PACKAGE          HARD




  • Cascading invalidation: Indirect dependents can be invalidated by changes to the reference object that do not affect them.
    • If a change to C invalidates B, it invalidates A (and all other direct and indirect dependents of B).
  • Coarse-grained invalidation: DDL statement that changes a referenced object invalidates all of its dependents.

On 11g R1: Fine-grained invalidation
  • Fine-grained invalidation: DDL statement that changes a referenced object invalidates only dependents for which:
    1. Dependent relies on the attribute of the referenced object that the DDL statement changed.
    2. The compiled metadata of the dependent is no longer correct for the changed referenced object.
    3. For example, if view v selects columns c1 and c2 from table t, a DDL statement that changes only column c3 of t does not invalidate v.


Minimize object invalidation

(a) Add Items to End of Package
This preserves the entry point numbers of existing top-level package items, preventing their invalidation.

CREATE OR REPLACE PACKAGE pkg1 IS
  FUNCTION get_var RETURN VARCHAR2;
END;

-- Adding an item to the end of pkg1, as follows, 
-- does not invalidate dependents that reference the get_var function:
CREATE OR REPLACE PACKAGE pkg1 IS
  FUNCTION get_var RETURN VARCHAR2;
  PROCEDURE set_var (v VARCHAR2);
END;

-- Inserting an item between the get_var function and the set_var procedure
-- invalidates dependents that reference the set_var function:
CREATE OR REPLACE PACKAGE pkg1 IS
  FUNCTION get_var RETURN VARCHAR2;
  PROCEDURE assert_var (v VARCHAR2);
  PROCEDURE set_var (v VARCHAR2);
END;

(b) Reference Each Table Through a View
Reference tables indirectly, using views, enabling you to:
  • Add columns to the table without invalidating dependent views or dependent PL/SQL objects.
  • Modify or delete columns not referenced by the view without invalidating dependent objects
  • The statement CREATE OR REPLACE VIEW does not invalidate an existing view or its dependents if the new ROWTYPE matches the old ROWTYPE.


Revalidation

To recompile a schema object using the appropriate SQL statement with the COMPILE clause.
  • ALTER PACKAGE pkg1 COMPILE REUSE SETTINGS;

Requisites to use UTL_RECOMP package:

  • This package must be run using SQL*PLUS.
  • You must be connected AS SYSDBA to run this script.
  • The following packages should have VALID status:
    • STANDARD (standard.sql)
    • DBMS_STANDARD (dbmsstdx.sql)
    • DBMS_JOB (dbmsjob.sql)
    • DBMS_RANDOM (dbmsrand.sql)
  • No other DDL must be running on the database, or deadlock may result.
  • This package uses the job queue for parallel recompilation.




You can use UTL_RECOMP package to recompile one or more invalid objects:

  • This is useful after an application upgrade or patch, when is good practice to revalidate invalid objects to avoid application latencies that result from on-demand object revalidation.



-- Recompile all objects sequentially:
EXECUTE UTL_RECOMP.RECOMP_SERIAL();

-- Recompile objects in schema SCOTT sequentially:
EXECUTE UTL_RECOMP.RECOMP_SERIAL('SCOTT');

-- Recompile all objects using 4 parallel threads:
EXECUTE UTL_RECOMP.RECOMP_PARALLEL(4);

-- Recompile objects in schema JOE using the number of threads specified in the parameter JOB_QUEUE_PROCESSES:
EXECUTE UTL_RECOMP.RECOMP_PARALLEL(NULL, 'JOE');


Revalidate individual invalid objects using DBMS_UTILITY.VALIDATE.
-- Revalidate the procedure UPDATE_SALARY in schema HR:
begin
dbms_utility.validate('HR', 'UPDATE_SALARY', namespace=>1);
end;

-- Revalidate the package body HR.ACCT_MGMT:
begin
dbms_utility.validate('HR', 'ACCT_MGMT', namespace=>2);
end;





No comments:

Post a Comment