Re: Function reloading in SGA
- From: David Fitzjarrell <oratune@xxxxxxxxx>
- To: hostetter.jay@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Tue, 25 May 2010 11:59:12 -0700 (PDT)
Can you provide the function code so we can see what it is doing?
David Fitzjarrell
________________________________
From: Jay Hostetter <hostetter.jay@xxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
Sent: Tue, May 25, 2010 12:06:35 PM
Subject: Function reloading in SGA
I have a 9.2.0.4 database running on Windows that frequently runs into ORA-4031
errors (about once a month). The application has a known problem with literal
values in the SQL. I have been monitoring objects in the SGA to see if
anything else is causing fragmentation.
I noticed that one function is loaded repeatedly, even though it is pinned. It
has been reloaded several times today (LOADS column value keeps increasing in
v$db_object_cache). Now I know this happens when the object is invalidated,
however as far as I can tell it is not becoming invalidated. None of it's
depended objects are becoming invalidated either. Or perhaps I should say -
how do I know they are not becoming invalidated? There is no DDL occuring on
the function or its dependent objects. The dependent objects are shown
below. What else could be causing this function to be reloaded?
Thank you,
Jay
SQL> run
1 select * from dba_dependencies
2* where name = 'GET_NEXT_SEQUENCE'
OWNER NAME TYPE REFERENCED_O
REFERENCED_NAME REFERENCED_TYPE REFERENCED DEPE
---------- ------------------ ----------------- ------------
-------------------- ----------------- ---------- ----
MIS GET_NEXT_SEQUENCE FUNCTION SYS
DUAL TABLE HARD
MIS GET_NEXT_SEQUENCE FUNCTION PUBLIC
DUAL SYNONYM HARD
MIS GET_NEXT_SEQUENCE FUNCTION SYS
STANDARD PACKAGE HARD
MIS GET_NEXT_SEQUENCE FUNCTION MIS
INTERFACE_LOG TABLE HARD
MIS GET_NEXT_SEQUENCE FUNCTION MIS
NEXT_SEQUENCE_NUMBER TABLE HARD
MIS GET_NEXT_SEQUENCE FUNCTION MIS
DUAL NON-EXISTENT HARD
Other related posts:
