As I said "if I remember correctly" and what I wrote from memory needs a bit of modification, so let me do that now: When a session first calls a function coded with PRAGMA AUTONOMOUS_TRANSACTION it loads that function whether or not it is pinned in the shared pool. All calls from that same session to that same function won't cause a reload. So for each session that calls the function you should see the loads increase; the executions will likely be higher. David Fitzjarrell ________________________________ From: Jay Hostetter <hostetter.jay@xxxxxxxxx> To: David Fitzjarrell <oratune@xxxxxxxxx> Cc: oracle-l@xxxxxxxxxxxxx Sent: Tue, May 25, 2010 4:00:43 PM Subject: Re: Function reloading in SGA If that were the case, wouldn't the number of loads to be very close to the number of executions? It is not a recently pinned object. Jay SQL> run 1 select owner,name,type,loads,executions,kept from v$db_object_cache 2* where name='GET_NEXT_SEQUENCE' OWNER NAME TYPE LOADS EXECUTIONS KEP ---------- -------------------- ---------------- ---------- ---------- --- FIRM GET_NEXT_SEQUENCE NON-EXISTENT 35 0 NO MIS GET_NEXT_SEQUENCE FUNCTION 385 1396646 YES PUBLIC GET_NEXT_SEQUENCE SYNONYM 35 0 NO On Tue, May 25, 2010 at 3:13 PM, David Fitzjarrell <oratune@xxxxxxxxx> wrote: If I remember correctly PRAGMA AUTONOMOUS_TRANSACTION will cause a reload of the function every time it is called since each call is an independent operation. > >David Fitzjarrell > > > > > > ________________________________ From: Jay Hostetter <hostetter.jay@xxxxxxxxx> >To: David Fitzjarrell <oratune@xxxxxxxxx>; oracle-l@xxxxxxxxxxxxx >Sent: Tue, May 25, 2010 3:08:12 PM >Subject: Re: Function reloading in SGA > > >Here it is: > >create or replace function get_next_sequence >( arg_facility IN varchar2, > arg_seq_name IN varchar2, > arg_This_Id IN number, > arg_julianday IN number ) >RETURN NUMBER IS > PRAGMA AUTONOMOUS_TRANSACTION; > ret_next_seq NUMBER; > v_last_id NUMBER; > v_julian NUMBER; > V_REPDATE DATE; > >BEGIN >