Re: Function reloading in SGA
- From: David Fitzjarrell <oratune@xxxxxxxxx>
- To: hostetter.jay@xxxxxxxxx
- Date: Tue, 25 May 2010 13:43:30 -0700 (PDT)
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
>
Other related posts:
