Increased Sys.dbms_pickler.get_type_shape() Calls in Database
(Doc ID 2634011.1)
Last updated on JULY 20, 2024
Applies to:
Oracle Database - Enterprise Edition - Version 12.1.0.2 and laterInformation 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 |