Error ORA-4021 And Slow Performance When Importing Packages Or Functions Into 11.2.0.3 Due To High "library cache pin" Waits

(Doc ID 1457574.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 to 11.2.0.3 [Release 11.2]
Information in this document applies to any platform.
***Checked for relevance on 12-Dec-2013***

Symptoms

While DataPump import (impdp) is moving very slow, DW trace file shows the following:

====================
PARSE ERROR #47401633293008:len=64 dep=1 uid=200 oct=17 lid=200 tim=1330357032402581 err=4021 <<<<<<<<<<< ORA-04021
grant execute on SYS_PLSQ ...
CLOSE #47401633293008:c=0,e=6,dep=1,type=0,tim=1330357032402827
XCTEND rlbk=0, rd_only=1, tim=1330357032403637

*** 2012-02-27 10:52:30.760
=====================
PARSE ERROR #47401633241200:len=64 dep=2 uid=200 oct=17 lid=200 tim=1330357950760050 err=4021 <<<<<<<<<<< ORA-04021
grant execute on SYS_PLSQ ...
CLOSE #47401633241200:c=0,e=9,dep=2,type=0,tim=1330357950760336

*** 2012-02-27 11:07:30.922
=====================
PARSE ERROR #47401633247912:len=64 dep=1 uid=200 oct=17 lid=200 tim=1330358850922453 err=4021 <<<<<<<<<<< ORA-04021
grant execute on SYS_PLSQ ...
CLOSE #47401633247912:c=0,e=8,dep=1,type=0,tim=1330358850922689

Tkprof output shows the following:

CREATE PACKAGE BODY Cs_Boundary
IS
-- get last spec point before this location
-- return null if no applicable spec point exists
FUNCTION get_last_spec_before_location(i_spec IN topoReference)
...

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.24     927.47          5         93          3           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.24     927.47          5         93          3           0 >>>>>>>>>>>>>>>>> 15 minutes for create one package

SQL ID: bfdg6zhw6x959 Plan Hash: 0

ALTER PACKAGE "CS_BOUNDARY" COMPILE REUSE SETTINGS TIMESTAMP  '2012-02-15:02:09:02'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.28     927.31          0          0          3           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.28     927.31          0          0          3           0 >>>>>>>>>>>>>>>>> 15 minutes for compile one package

********************************************************************************

CREATE FUNCTION Linearfilter (c_input_rows IN sys_refcursor)
  RETURN tdms_objects.linear_table pipelined
IS
 v_in_row tdms_objects.linear_row;
...

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1      0.05     900.08          2         59        206           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.05     900.08          2         59        206           0 >>>>>>>>>>>>>>>>> 15 minutes for create one function

********************************************************************************

SQL ID: f5zxgbg9m5pzw Plan Hash: 0

ALTER FUNCTION "FILTERMPRANGE" COMPILE REUSE SETTINGS TIMESTAMP  '2011-10-05:14:28:56'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.05     900.07          0          0          1           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.05     900.07          0          0          1           0 >>>>>>>>>>>>>>>>> 15 minutes for compile one function

Elapsed times include waiting on following events:
 Event waited on                             Times   Max. Wait  Total Waited
 ----------------------------------------   Waited  ----------  ------------
 library cache pin                               5      900.01       4500.03 >>>>>>>>>>>>>>>>>
 db file sequential read                        15        0.01          0.13
 log file sync                                1192        0.10          2.52
 SQL*Net message to client                    2549        0.00          0.00
 SQL*Net message from client                  2549        1.81          2.58
 SQL*Net more data from client                 231        0.00          0.00
 library cache lock                              6        9.11         54.13
 db file scattered read                         27        0.04          0.40

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