What is UTL_CON?

UTL_CON is a package that DPA installs into the SYS schema in an Oracle database when it is registered. It is used for a variety of things including DPA upgrades, getting live plans (the DPA monitoring user is not privileged), DPA version information, etc.

Contents of the UTL_CON package

PROCEDURE EXPLAIN PLAN - This procedure is used when a DPA GUI user requests a live explain plan from the SQL page. The DPA user is not privileged, so it does not have permissions to see or read every table that a user would request a plan against. Thus, this procedure will run as the SYS user and get the plan for the SQL text passed in.

PROCEDURE GP - GP stands for Grant Permission and is used during DPA and monitored instance upgrades and to support DPA features requiring higher privileges than the monitoring user has. An example of this requirement is if a new feature is added that needs access to a new table. For example, one version of DPA added a live plan collection feature that retrieved data from V$SQL_PLAN. The previous version did not require that privilege, so during an upgrade this procedure is called to grant read access to this table.

PROCEDURE MPROC - Used to get hot block information. This procedure is deprecated because the feature is no longer present, but remains here for future use.

FUNCTION VERSION RETURNS NUMBER - Used for DPA to determine if an upgrade is required when the monitor is started.

Note: Each of the above procedures/functions requires a security key as one of the parameters. Only the DPA software knows this key. No one else can use this package without the security key.

Can I drop the UTL_CON package?

For various reasons, such as change control, security, etc., some companies want to remove this package. It can be dropped, but the features that would be missed or altered are as follows:

• Live Plan - A simple live plan would probably not work, but on the dialog in the GUI the user can supply a DBA account username and password and still get live plans. The DBA username and password would be required for each request. It is not stored by DPA.
• Automatic Upgrades - If access to a new object is required in a future DPA version, the DPA administrator would be required to manually grant the required permissions.
• Kill Session and Trace On/Off - Those actions on the Real Time Sessions page require  ALTER SYSTEM permission, which the DPA monitoring user does not have. Therefore, the GP procedure is used to execute those commands.
• Recreate monitoring objects after monitored instance has been upgraded - Sometimes monitoring objects required by DPA are dropped during an Oracle instance upgrade. DPA can recreate those objects using UTL_CON. If you want to avoid DPA creating monitoring objects on your Oracle instance, see the following article: Register an Oracle database instance for DPA monitoring with reduced permissions.

If these limitations are not an issue, the UTL_CON package can be dropped.