My Oracle Support Banner

Increased Sys.dbms_pickler.get_type_shape() Calls in Database (Doc ID 2634011.1)

Last updated on MAY 01, 2020

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information in this document applies to any platform.

Goal

AWR report, increased DBMS_PICKLER Package Calls are seen with two recursive SQLs.

Customer is noticing Pickler fetch sql's running frequently in the database from SYS user.

This SQL's are running on the database almost every day and run time also get increased significantly day by day with more executions:

begin :1 := sys.dbms_pickler.get_type_shape(:2, :3, :4, :5, :6, :7, :8, :9, :10); end;

18r4hmky0h0un

SELECT /*+ NOPARALLEL */ 1, NULL, 1, DECODE(O.TYPE#, 9, (SELECT TYP_NAME || (DECODE(BITAND(T.PROPERTIES, 134217728), 134217728, '%ROWTYPE', NULL)) FROM TYPE$ T WHERE T.TOID=C.ELEM_TOID), 2, O.NAME||'%ROWTYPE', 4, O.NAME||'%ROWTYPE', 13, (SELECT DECODE(T.TYPECODE, 9, DECODE(C.CHARSETFORM, 2, 'NVARCHAR2', O.NAME), 96, DECODE(C.CHARSETFORM, 2, 'NCHAR', O.NAME), 112, DECODE(C.CHARSETFORM, 2, 'NCLOB', O.NAME), O.NAME) FROM TYPE$ T WHERE T.TOID = C.ELEM_TOID), O.NAME), DECODE(O.TYPE#, 13, (SELECT (DECODE(BITAND(T.PROPERTIES, 64), 64, NULL, U.NAME)) FROM TYPE$ T WHERE T.TOID=C.ELEM_TOID), U.NAME), DECODE(O.TYPE#, 9, O.NAME, NULL), C.ELEM_TOID, NULL, NULL, NULL FROM OID$ ID, USER$ U, SYS. "_CURRENT_EDITION_OBJ" O, COLLECTION$ C WHERE C.TOID = :B1 AND ID.OID$ = C.ELEM_TOID AND O.OBJ# = ID.OBJ# AND U.USER# = O.OWNER#

f3pj95uc9tqsz

SELECT /*+ NOPARALLEL */ 1, A.NAME, A.ATTRIBUTE#, DECODE(AT.TYPECODE, 9, DECODE(A.CHARSETFORM, 2, 'NVARCHAR2', ATO.NAME), 96, DECODE(A.CHARSETFORM, 2, 'NCHAR', ATO.NAME), 112, DECODE(A.CHARSETFORM, 2, 'NCLOB', ATO.NAME), ATO.NAME), DECODE(BITAND(AT.PROPERTIES, 64), 64, NULL, ATU.NAME), NULL, A.ATTR_TOID, DECODE(BITAND(T.PROPERTIES, 65536), 65536, 'NO', 'YES'), NULL, NULL FROM SYS.ATTRIBUTE$ A, SYS.TYPE$ T, SYS.TYPE$ AT, SYS. "_CURRENT_EDITION_OBJ" ATO, SYS.USER$ ATU WHERE T.TOID = :B1 AND A.TOID = T.TOID AND AT.TOID = A.ATTR_TOID AND A.ATTR_TOID = ATO.OID

 

Solution

To view full details, sign in with your My Oracle Support account.

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


In this Document
Goal
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.