Contents of procedure - Just two direct insert statements which are inserted through IN parameters. The procedure will be hit every second when a row has to be inserted. It's kind of a recharge. If you recharge, the details will be sent by some other team to us thru java (callable statement). Normally when CPU is low, for a second we can see 10-15 sessions for that procedure (in v$session) and data will be inserted perfectly. When CPU is a bit high, we are able to see 80-90 sessions at once, where every session becomes invalid after 2,3 seconds. it takes time to insert and gets timed out eventually in java in other system.
I hope I made clear on this. Is there any other way to rectify this issue. Other than thru a procedure. I tried with RECORD concept in pl/sql, but nothing happened really.
My procedure just looks like this.
create procedure procedure_name (co1 IN varchar , col2 IN vvarchar.... col15 IN varchar)
x varchar2,
y varchar2,
z varchar2,
.
.
.
begin
x:=col1;
y:=col2;
.
.
.
insert into table1 (column1, column2 ,...) values (x,y,z..); commit;
if (x=15) then
insert into table2(column1,column2,..) values (x,y,z); commit;
exception
when others
..
end;
