SQL IN XDB.DBMS_XDBZ0 LINE 637 IS MAJOR CONSUMER OF CPU (Doc ID 972671.1)

Last updated on APRIL 15, 2014

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.4 - Release: 10.2 to 10.2
Information in this document applies to any platform.

Symptoms

The following SQL statement seen in statspack and AWR report from XDB.DBMS_XDBZ0 package line 637 is executed more than 10.000 times and consumes huge amount of CPU:

SELECT /*+ ALL_ROWS */ COUNT(*) FROM ALL_POLICIES V
WHERE V.OBJECT_OWNER = :B3 AND V.OBJECT_NAME = :B2
AND (POLICY_NAME LIKE '%xdbrls%' OR POLICY_NAME LIKE '%$xd_%')
AND V.FUNCTION = :B1
...


The following SQL is also a major CPU consumer

BEGIN
BEGIN
IF (xdb.DBMS_XDBZ0.is_hierarchy_enabled_internal(
sys.dictionary_obj_owner, sys.dictionary_obj_name, sys.dictionary_obj_owner))
THEN xdb.XDB_PITRIG_PKG.pitrig_truncate(sys.dictionary_obj_owner, sys.dictionary_obj_name);
END IF;
EXCEPTION WHEN OTHERS THEN null;
END;
...

Trigger source:

BEFORE DROP OR TRUNCATE on DATABASE
BEGIN
BEGIN
IF (xdb.DBMS_XDBZ0.is_hierarchy_enabled_internal(sys.dictionary_obj_owner, sys.dictionary_obj_name, sys.dictionary_obj_owner)) THEN
xdb.XDB_PITRIG_PKG.pitrig_truncate(sys.dictionary_obj_owner, sys.dictionary_obj_name);
END IF;
EXCEPTION
WHEN OTHERS THEN
null;
END;
BEGIN
IF (xdb.DBMS_XDBZ0.is_hierarchy_enabled_internal(sys.dictionary_obj_owner, sys.dictionary_obj_name, sys.dictionary_obj_owner, xdb.DBMS_XDBZ.IS_ENABLED_RESMETADATA)) THEN
xdb.XDB_PITRIG_PKG.pitrig_dropmetadata(sys.dictionary_obj_owner, sys.dictionary_obj_name);
END IF;
EXCEPTION
WHEN OTHERS THEN
null;
END;
END;

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms