 |
|
PL/SQL Build_SUID_Matrix Package Tips
Oracle Tips by Burleson Consulting |
Managing Standardized PL/SQL Code
Oracle stores the source code for PL/SQL objects inside the ALL_SOURCE
view of the DBA_SOURCE table in the SYS schema. This allows you to
query the most recent source code for a procedure or function from the database.
If the coding standards are followed fairly closely, it’s possible to write
scripts that work with your source code to assist you in your documentation
efforts. Included on the CD-ROM is a script that takes full advantage of the
coding standards to collect information about and document source code.
The PL/SQL Build_SUID_Matrix Package
Oracle stores dependencies in the ALL_DEPENDENCIES view, but this
information exists only at the object-to-object and object-to-table levels. The
view cannot, for instance, state precisely which objects perform INSERT
statements on a specific table.
The Build_SUID_Matrix package was designed to locate all references to
tables within a specified PL/SQL object. The procedures and functions in the
package locate table references and sort the references by type (SELECT,
INSERT, UPDATE, DELETE, and %TYPE or %ROWTYPE).
The package populates the SUID_MATRIX table with this information.
Once the SUID_MATRIX table is fully populated, a query can be run to
see precisely which objects access a specified table or which tables a specified
object references. This is particularly useful when:
• Examining the impact of
creating a new index.
• Examining the impact of
altering a table’s structure.
• Determining which
objects perform a particular type of operation against a table.
Improving The Build_SUID_Matrix Package
There are a number of potential improvements that can be made to the
Build_SUID_Matrix package:
• Improving the level of
detail determined to the column level. Knowing which objects modify data in a
table is very useful, but if 20 routines update a table and only a few routines
update a particular column that has a suspect value, it’s even quicker to find
the routines that modify the value in that column.
• Recognizing
objects within packages. The current implementation of the package only
recognizes objects to the package level and doesn’t differentiate between
procedures and functions within the package.
This is an excerpt from "High Performance Oracle Database Automation", by
Jonathan Ingram and Donald K. Burleson, Series Editor.
 |
If you like Oracle tuning, see the book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
