Performance Tips for the Programmatic Interface of the DBMS_METADATA API

This section describes how to enhance performance when using the programmatic interface of the DBMS_METADATA API.

  1. Fetch all of one type of object before fetching the next. For example, if you are retrieving the definitions of all objects in your schema, first fetch all tables, then all indexes, then all triggers, and so on. This will be much faster than nesting OPEN contexts; that is, fetch one table then all of its indexes, grants, and triggers, then the next table and all of its indexes, grants, and triggers, and so on. "Example Usage of the DBMS_METADATA API" reflects this second, less efficient means, but its purpose is to demonstrate most of the programmatic calls, which are best shown by this method.

  2. Use the SET_COUNT procedure to retrieve more than one object at a time. This minimizes server round trips and eliminates many redundant function calls.

  3. When writing a PL/SQL package that calls the DBMS_METADATA API, declare LOB variables and objects that contain LOBs (such as SYS.KU$_DDLS) at package scope rather than within individual functions. This eliminates the creation and deletion of LOB duration structures upon function entrance and exit, which are very expensive operations.