Re: Function reloading in SGA
- From: Jay Hostetter <hostetter.jay@xxxxxxxxx>
- To: David Fitzjarrell <oratune@xxxxxxxxx>, oracle-l@xxxxxxxxxxxxx
- Date: Tue, 25 May 2010 15:08:12 -0400
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
SELECT SYSDATE INTO V_REPDATE FROM DUAL;
ret_next_seq := 0;
begin
SELECT LAST_NUMBER+INCR_BY, last_id
INTO ret_next_seq, v_last_id
FROM NEXT_SEQUENCE_NUMBER
WHERE FACILITY = nvl(arg_facility,'<>')
AND SEQUENCE_NAME = arg_seq_name
for update;
exception
when no_data_found then
insert into next_sequence_number
( FACILITY, SEQUENCE_NAME, LAST_NUMBER, INCR_BY )
values (nvl(arg_facility,'<>'), arg_seq_name, 1, 1);
ret_next_seq := 1;
when others then
ret_next_seq := null;
end;
IF arg_this_id IS NOT NULL
THEN
IF arg_this_id = v_Last_Id and
arg_seq_name = 'PACKAGE'
THEN
begin
SELECT last_number
INTO v_julian
FROM next_sequence_number
WHERE facility = nvl(arg_facility,'<>')
AND sequence_Name = 'JULIANDAY'
AND last_id=1;
exception
when no_data_found then
insert into next_sequence_number
( FACILITY, SEQUENCE_NAME, LAST_NUMBER, INCR_BY, LAST_ID )
values (nvl(arg_facility,'<>'),'JULIANDAY',
TO_NUMBER(TO_CHAR(SYSDATE,'YY')||TO_CHAR(SYSDATE,'DDD')), 1, 1);
when others then
ret_next_seq := null;
end;
if V_Julian <> arg_julianday
then
ret_next_seq := 1;
end if;
ELSIF arg_this_id != v_Last_Id and
arg_seq_name = 'PACKAGE'
THEN
IF arg_This_Id = 1
THEN
ret_next_seq := 1;
END IF;
ELSIF arg_this_id != v_Last_Id and
arg_seq_name != 'PACKAGE'
THEN
ret_next_seq := 1;
END IF;
END IF;
if ret_next_seq is not null then
begin
UPDATE NEXT_SEQUENCE_NUMBER
SET LAST_NUMBER = ret_next_seq,
LAST_ID = arg_This_Id,
REP_TIMESTAMP = v_repdate
WHERE FACILITY = nvl(arg_facility,'<>')
AND SEQUENCE_NAME = arg_seq_name;
exception
when others then
ret_next_seq := null;
end;
else
Insert into interface_log
(facility, timestamp, status, search_ind, search_column,
table_name, user_comment)
Values
('<>',sysdate, 'F', 'EQ', arg_seq_name,
'NEXT_SEQUENCE_NUMBER', 'value is null');
end if;
commit;
return ret_next_seq;
end;
On 5/25/10, David Fitzjarrell <oratune@xxxxxxxxx> wrote:
> 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
>
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
