Home » SQL & PL/SQL » SQL & PL/SQL » pl/sql tuning
pl/sql tuning [message #247942] Wed, 27 June 2007 10:43 Go to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
Hi All,

When i run this procedure from front end to create sessions with more than 500 records the entire server is struck.

This procedure works fine for <400 records . I tried to collect
dbms_profiler statistics by running from frontend.
But that could not record any statistics.


Can anyone analyse the code and suggest some improvements for
faster performance.

Ravi









CREATE OR REPLACE PACKAGE BODY BSM_3_2_9_ipdc_schrec AS
errMsg varchar2(200);
-------------------------------
-- Procedures
-------------------------------
--******************************************************************
-- createRecurrence
-- note that i_PartitionList doesn't necessarily list the partitions
-- to be used in all the Sessions because if the recurrence extends
-- across PlanningPeriods, then they will be different.
-- i_inclWeekDayList is expected in increasing order of weekdaynumber
-- from Java.
--******************************************************************
procedure createRecurrence
(
i_addSession in out servSession,
i_ipSecSeeds in ipSecSeedList,
i_PartitionList in out chPartitionList,
i_forceCreation in integer,
i_recurSession in recurSession,
i_inclWeekDayList in out inclWeekDayList,
o_partitionExists out integer,
o_partitionUseful out integer,
o_nwAreaNotAvailable out nwAreaList,
o_nwAreaNoBW out nwAreaList,
o_sessionList out servSessionList,
o_sessStatusList out sessStatusList,
o_sessPartitions out sessPartitionList,
i_sessionSource in out Sessionsourceaddresslist,
i_enddate in date,
i_ipSecAuthSeeds in ipSecSeedList,--for 3.1.1
i_isWholeDaySess in number
) as
v_fwSwitchDayProfile DayProfile.dayProfileCode%TYPE := null;
v_bwSwitchDayProfile DayProfile.dayProfileCode%TYPE := null;
v_dcserver DatacastServer.datacastServerCode%TYPE := null;
v_dpCode DayProfile.dayProfileCode%TYPE := null;

v_temp integer := 0;
v_nofSessions integer := 0;
v_nextWDNr integer := 0;
v_nextOpDate date := null;
v_sessOverlap integer := 0;
v_firstStartTime integer := 0;
v_lastOpDay date := null;
v_currentOpDate date := null;
v_StartTime integer := 0;
v_index integer := 0;
v_sessionCount integer := 0;
v_weekIndex integer := 0;
v_weekdayIndex integer := 0;
v_firstWeekDay integer := 0;
-- changes for version 3.0.3
--v_annStartDateOffset integer := 0;
v_session servSession := null;
v_opDatePartComp integer := 0;
v_PartitionList chPartitionList := null;
v_sessPartition sessPartition := null;
v_i integer := 0;
v_sessPartitions sessPartitionList := null;
v_sessPartCount integer := 0;
v_recurSession recurSession := null;
v_inclWeekDayList inclWeekDayList := null;
v_recurSessID integer := 0;
v_count integer := 0;
v_partitionExists integer := -1;
v_partitionUseful integer := -1;
v_nwAreaNotAvailable nwAreaList := null;
v_nwAreaNoBW nwAreaList := null;
sessPartitions sessPartitionList := null;
v_IDSessIndex integer := 0;
v_nofIntraDaySess integer := 0;
v_intraDaySessions servSessionList := null;
v_recurDay date := null;
v_arrPartExists intList := null;
v_arrPartUseful intList := null;
v_temppartitionExists integer := -1;
v_temppartitionUseful integer := -1;
v_tempdoItemsFit integer := -1;
v_tempdoProgramsFit integer := -1;
v_tempnwAreaNotAvailable nwAreaList := null;
v_tempnwAreaNoBW nwAreaList := null;
v_partIndex integer := 0;
v_tempsessStatList sessStatusList := null;
v_firstSession servSession := null;
v_dirtySessionFound integer := 0;
v_tempDirtySess integer := 0;
v_firstProgOffset integer := 0;
v_ipSecSeedIndex integer := 0;
v_validOpDate integer := 0;
v_tempSessPartIndex integer := 0;
v_validProvider integer := 0;
v_ipPort sessIPAddrPort := null;
v_ipAddrNotAvailable integer := 0;
v_portNotAvailable integer := 0;
v_ipPortList sessIPAddrPortList := null;
v_sysParamIPStart integer := 0;
v_sysParamIPEnd integer := 0;
v_sysParamPortStart integer := 0;
v_sysParamPortEnd integer := 0;
v_tempIPAddrNotAvailable integer := 0;
v_tempPortNotAvailable integer := 0;
v_dcservers dcsStatusList := null;
v_dcsIndex integer := 0;
v_fwSwitchStartTime integer := 0;
v_bwSwitchStartTime integer := 0;
v_dlSwitchAmount integer := 0;
v_newStartTime integer := 0;
v_duration integer := 0;
v_opDayEnd integer := 0;
v_dayEnd integer := 0;
v_PartitionListForOverlap chPartitionList := null;
v_firstOpDate date := null;
v_firstSessNotCreated integer := 0;
v_firstIntendedSess integer := 0;
v_invSessItems integer := 0;
v_temppartExists integer := -1;
v_temppartUseful integer := -1;
v_tempnwAreaList nwAreaList := null;
v_sessCount integer := 0;
v_tempPartList chPartitionList := null;
v_tempValue integer := 0;
v_recSessionList servSessionList := null;
v_tempRecSessionList servSessionList := null;
v_finalRecList servSessionList := null;
v_tempCount integer := 1;
v_isNonOverLapSess integer := 0;
v_maxBurstInterval number := null;
v_def_accesstype number;
i_overlap_flag number;
e_overlap_exception exception;
----TD CR 422,423,424,425
v_basePartList chpartitionlist:=null;
v_currduration number;
v_currcasting number;
v_dayprofile DayProfile.dayProfileCode%TYPE := null;
v_firstDPCode DayProfile.dayProfileCode%TYPE := null;

e_invalid_pid_ex exception;
pragma exception_init(e_invalid_pid_ex, -20761);

fw_flag boolean:=false;
v_pid integer:=0;
v_bwpid integer:=0;

begin





---------------- for debug--------------------------------------------------


log_de_bug(11,'i_addSession.cpcode is '||i_addSession.cpCode);
log_de_bug(11,'i_addSession.sCode is '||i_addSession.sCode);
log_de_bug(11,'i_addSession.opDate is '||i_addSession.opDate);
log_de_bug(11,'i_addSession.sessionNr is '||i_addSession.sessionNr);
log_de_bug(11,'i_addSession.descr is '||i_addSession.descr);
log_de_bug(11,'i_addSession.ipAddrSuffStart is '||i_addSession.ipAddrSuffStart);
log_de_bug(11,'i_addSession.portStart is '||i_addSession.portStart);
log_de_bug(11,'i_addSession.ProtectionprotocolEnum is '||i_addSession.ProtectionprotocolEnum);
log_de_bug(11,'i_addSession.ipSecAuthSeed is '||i_addSession.ipSecAuthSeed);
log_de_bug(11,'i_addSession.ipSecEncryptionSeed is '||i_addSession.ipSecEncryptionSeed);
log_de_bug(11,'i_addSession.ipeMgmtStatus is '||i_addSession.ipeMgmtStatus);
log_de_bug(11,'i_addSession.ipeMgmtIds is '||i_addSession.ipeMgmtIds);
log_de_bug(11,'i_addSession.NOfIPERetries is '||i_addSession.NOfIPERetries);
log_de_bug(11,'i_addSession.sessionProfileCode is '||i_addSession.sessionProfileCode);
log_de_bug(11,'i_addSession.castingTime is '||i_addSession.castingTime);
log_de_bug(11,'i_addSession.castingDuration is '||i_addSession.castingDuration);
log_de_bug(11,'i_addSession.bitRate is '||i_addSession.bitRate);
log_de_bug(11,'i_addSession.totalPrice is '||i_addSession.totalPrice);
log_de_bug(11,'i_addSession.schValStatus is '||i_addSession.schValStatus);
log_de_bug(11,'i_addSession.schValInfo is '||i_addSession.schValInfo);
log_de_bug(11,'i_addSession.nofRecvPackMin is '||i_addSession.nofRecvPackMin);
log_de_bug(11,'i_addSession.nofRecvPackAvg is '||i_addSession.nofRecvPackAvg);
log_de_bug(11,'i_addSession.nofRecvPackMax is '||i_addSession.nofRecvPackMax);
log_de_bug(11,'i_addSession.nofSentPackMin is '||i_addSession.nofSentPackMin);
log_de_bug(11,'i_addSession.nofSentPackAvg is '||i_addSession.nofSentPackAvg);
log_de_bug(11,'i_addSession.nofSentPackMax is '||i_addSession.nofRecvPackMax);
log_de_bug(11,'i_addSession.nofSentPackMin is '||i_addSession.nofSentPackMin);
log_de_bug(11,'i_addSession.ERRORCORRECTIONPERCENTAGE is '||i_addSession.ERRORCORRECTIONPERCENTAGE);
log_de_bug(11,'i_addSession.PriorityNumber is '||i_addSession.PriorityNumber);
log_de_bug(11,'i_addSession.isDeleted is '||i_addSession.isDeleted);
log_de_bug(11,'i_addSession.modifDate is '||i_addSession.modifDate);
log_de_bug(11,'i_addSession.modifUser is '||i_addSession.modifUser);

if i_ipSecSeeds.exists(1) then

for i in 1..i_ipSecSeeds.count loop

log_de_bug(11,'i_ipSecSeeds is '||i_ipSecSeeds(i));

end loop;

end if;

if i_PartitionList.exists(1) then

for i in 1..i_PartitionList.count loop

log_de_bug(11,'i_PartitionList.uniqueID is '||i_PartitionList(i).uniqueID);
log_de_bug(11,'i_PartitionList.ppStartDate is '||i_PartitionList(i).ppStartDate);
log_de_bug(11,'i_PartitionList.dpCode is '||i_PartitionList(i).dpCode);
log_de_bug(11,'i_PartitionList.nwAreaCode is '||i_PartitionList(i).nwAreaCode);
log_de_bug(11,'i_PartitionList.subChannelID is '||i_PartitionList(i).subChannelID);
log_de_bug(11,'i_PartitionList.chPartitionNr is '||i_PartitionList(i).chPartitionNr);
log_de_bug(11,'i_PartitionList.partitionExists is '||i_PartitionList(i).partitionExists);
log_de_bug(11,'i_PartitionList.partitionUseful is '||i_PartitionList(i).partitionUseful);
log_de_bug(11,'i_PartitionList.PID is '||i_PartitionList(i).PID);

end loop;

end if;


log_de_bug(11,'i_forceCreationis '||i_forceCreation);

log_de_bug(11,'i_recurSession.recurSessID is '||i_recurSession.recurSessID);
log_de_bug(11,'i_recurSession.firstStartTime is '||i_recurSession.firstStartTime);
log_de_bug(11,'i_recurSession.interval is '||i_recurSession.interval);
log_de_bug(11,'i_recurSession.nofRepetitions is '||i_recurSession.nofRepetitions);
log_de_bug(11,'i_recurSession.firstOpDate is '||i_recurSession.firstOpDate);
log_de_bug(11,'i_recurSession.nofWeeks is '||i_recurSession.nofWeeks);
log_de_bug(11,'i_recurSession.isDeleted is '||i_recurSession.isDeleted );
log_de_bug(11,'i_recurSession.modifDate is '||i_recurSession.modifDate);
log_de_bug(11,'i_recurSession.modifUser is '||i_recurSession.modifUser);


if i_inclWeekDayList.exists(1) then

for i in 1..i_inclWeekDayList.count loop

log_de_bug(11,'i_inclWeekDayList.recurSessID is '||i_inclWeekDayList(i).recurSessID);
log_de_bug(11,'i_inclWeekDayList.weekDayNr is '||i_inclWeekDayList(i).weekDayNr);
log_de_bug(11,'i_inclWeekDayList.isDeleted is '||i_inclWeekDayList(i).isDeleted );
log_de_bug(11,'i_inclWeekDayList.modifDate is '||i_inclWeekDayList(i).modifDate);
log_de_bug(11,'i_inclWeekDayList.modifUser is '||i_inclWeekDayList(i).modifUser);

end loop;

end if;

if i_sessionSource.exists(1) then
for i in 1..i_sessionSource.count loop

log_de_bug(11,'i_sessionSource.CONTENTPROVIDERCODE is '||i_sessionSource(i).CONTENTPROVIDERCODE);
log_de_bug(11,'i_sessionSource.SERVICECODE is '||i_sessionSource(i).SERVICECODE);
log_de_bug(11,'i_sessionSource.OPERATIONALDATE is '||i_sessionSource(i).OPERATIONALDATE);
log_de_bug(11,'i_sessionSource.SESSIONNUMBER is '||i_sessionSource(i).SESSIONNUMBER);
log_de_bug(11,'i_sessionSource.ADDRESSNUMBER is '||i_sessionSource(i).ADDRESSNUMBER);
log_de_bug(11,'i_sessionSource.SOURCEIPADDRESS is '||i_sessionSource(i).SOURCEIPADDRESS);
log_de_bug(11,'i_sessionSource.ISDELETED is '||i_sessionSource(i).ISDELETED);
log_de_bug(11,'i_sessionSource.MODIFDATE is '||i_sessionSource(i).MODIFDATE);
log_de_bug(11,'i_sessionSource.MODIFUSER is '||i_sessionSource(i).MODIFUSER);

end loop;
end if;


if i_ipSecAuthSeeds.exists(1) then

for i in 1..i_ipSecAuthSeeds.count loop

log_de_bug(11,'i_ipSecAuthSeeds is '||i_ipSecAuthSeeds(i));

end loop;

end if;



--------------for debug--------------

if i_PartitionList.Exists(1) then
for v_index in 1..i_PartitionList.Count loop
if i_PartitionList(v_index).pid is not null then
if BSM_3_2_9_ipdc_schupd.validateSessionPid(i_addSession,i_PartitionList)=0 then
raise e_invalid_pid_ex;
end if;
end if;
end loop;
end if;


v_tempnwAreaList := nwAreaList();
v_recSessionList := servSessionList();
--check whether the firstoperationaldate falls atleast in one of the
--selected weekdays.
--for 3.1.1

if(i_addSession.protectionprotocolenum is not null) then
if(lower(i_addSession.protectionprotocolenum) = 'ipsecaut' and (i_ipSecSeeds is null or
i_ipSecAuthSeeds is null)) then
raise invalid_keylist;
end if;

if(lower(i_addSession.protectionprotocolenum) = 'ipsec' and (i_ipSecSeeds is null)) then
raise invalid_keylist;
end if;
end if;



BSM_3_2_9_ipdc_schutil.verifyOperationalDate(i_addSession.opDate,v_validOpDate);
if(v_validOpDate = 0) then
raise invalid_operationaldate;
end if;

BSM_3_2_9_ipdc_schutil.verifyService(i_addSession);
BSM_3_2_9_ipdc_schinsget.isProviderValid(i_addSession,v_validProvider);
if(v_validProvider = 0) then
raise invalid_provider;
end if;

--verify IPAddr and Port range required with System Parameter range
v_sysParamIPStart := BSM_3_2_9_ipdc_common.SERVICE_IPADDRESS_SUFFIX_START;--FOR 3.2.3--to_number(BSM_3_2_9_ipdc_deployment.getSystemParameter('Service.IPAddressSuffixStart'));
v_sysParamIPEnd := BSM_3_2_9_ipdc_common.SERVICE_IPADDRESS_SUFFIX_END;--FOR 3.2.3--to_number(BSM_3_2_9_ipdc_deployment.getSystemParameter('Service.IPAddressSuffixEnd'));


v_PartitionList := chPartitionList();
v_sessPartitions := sessPartitionList();
v_inclWeekDayList := inclWeekDayList();
o_sessPartitions := sessPartitionList();
o_sessionList := servSessionList();
o_nwAreaNotAvailable := nwAreaList();
o_nwAreaNoBW := nwAreaList();
o_sessStatusList := sessStatusList();
v_nwAreaNotAvailable := nwAreaList();
v_nwAreaNoBW := nwAreaList();
sessPartitions := sessPartitionList();
v_tempnwAreaNotAvailable := nwAreaList();
v_tempnwAreaNoBW := nwAreaList();
v_tempsessStatList := sessStatusList();
v_intraDaySessions := servSessionList();
v_nofSessions := -1;
v_ipPortList := sessIPAddrPortList();
v_basePartList := chpartitionlist();----TD CR 422,423,424,425


BSM_3_2_9_ipdc_schutil.getTotalRecurSessions(i_recurSession,i_inclWeekDayList,v_nofSessions);
o_partitionExists := BSM_3_2_9_ipdc_common.INVALID_INTEGER;
o_partitionUseful := BSM_3_2_9_ipdc_common.INVALID_INTEGER;
v_temp := BSM_3_2_9_ipdc_common.INVALID_INTEGER;


if(i_inclWeekDayList is not null) then
v_firstWeekDay := BSM_3_2_9_ipdc_schutil.checkWeekDayNumber(i_addSession.opDate, i_inclWeekDayList);

if(v_firstWeekDay = -1) then
raise opday_not_in_weekdaylist;
end if;


--check whether recurrence extends beyond the defined operationaldays
--obtain the ordered list of included weekdays
if i_enddate is null then
v_temp := BSM_3_2_9_ipdc_schutil.checkOperationalDays(i_recurSession, i_inclWeekDayList,v_lastOpDay);
else
select count(*) into v_temp from operationalday
where operationaldate=i_enddate
and isdeleted=0;

end if;

if(v_temp = 0) then
raise invalid_operationaldate;
end if;
else
v_lastOpDay := i_addSession.opDate;

end if;

/* Tuning sessoverlap is not required as overlaping is not allowed */
--v_sessOverlap := BSM_3_2_9_ipdc_schutil.checkSessionOverlap(i_addSession, i_recurSession);
v_sessionCount := 0;
v_currentOpDate := i_addSession.opDate;

--normally annStartDate is before CastingDate, if it is the reverse,
--v_annStartDateOffset is -ve
-- changes for version 3.0.3
--v_annStartDateOffset := i_addSession.opDate - i_addSession.annStartDate;
v_fwSwitchDayProfile := BSM_3_2_9_ipdc_deployment.getSystemParameter('Calendar.ForwardSwitchDayProfileCode');
v_bwSwitchDayProfile := BSM_3_2_9_ipdc_deployment.getSystemParameter('Calendar.BackwardSwitchDayProfileCode');
v_fwSwitchStartTime := BSM_3_2_9_ipdc_deployment.getSystemParameter('Calendar.ForwardSwitchStart');
v_bwSwitchStartTime := BSM_3_2_9_ipdc_deployment.getSystemParameter('Calendar.BackwardSwitchStart');
v_dlSwitchAmount := BSM_3_2_9_ipdc_deployment.getSystemParameter('Calendar.DaylightSwitchAmount');
v_dayEnd := BSM_3_2_9_ipdc_deployment.getSystemParameter('Calendar.OperationalDayEnd');

--important to do delete because in case of errors, the table might have old entries since
--commit is not done in such situations
delete from recurSessInstance;
v_firstIntendedSess := 1;
select dayProfileCode into v_firstDPCode
from OperationalDay
where operationaldate=i_addSession.opDate
and isDeleted=0;
--nofWeeks is zero for 'only intraday recurrence'

if(i_recurSession.nofWeeks != 0) then

for v_weekIndex in 1..i_recurSession.nofWeeks loop
for v_weekdayIndex in 1..i_inclWeekDayList.count loop

if i_enddate is not null then
if v_currentOpDate>i_enddate then
exit;
end if;
end if;
select dayProfileCode into v_dpCode from OperationalDay where operationaldate=v_currentOpDate and isDeleted=0;
--nofWeeks is zero for 'only interday recurrence'
if (i_recurSession.nofRepetitions != 0) then
v_nofIntraDaySess := i_recurSession.nofRepetitions + 1;
for v_index in 1..v_nofIntraDaySess loop
--creating Sessions within a single day.

v_StartTime := i_recurSession.firstStartTime +
(v_index - 1)*i_recurSession.interval;

v_duration := i_addSession.castingDuration;

--recurrence pattern doesnt give correct timings for daylight switching days. the
--corrections have to be applied by application
v_opDayEnd := v_dayEnd;
if(v_dpCode = v_fwSwitchDayProfile or v_dpCode = v_bwSwitchDayProfile) then

BSM_3_2_9_ipdc_schutil.getCorrectedStartTime(v_StartTime, v_duration, v_dpCode, v_fwSwitchDayProfile, v_bwSwitchDayProfile,
v_fwSwitchStartTime, v_bwSwitchStartTime, v_dlSwitchAmount);

end if;
--v_sessionCount is not the actual SessionNumber to be used
--in ServiceSession table, it is only used to distinguish between
--the sessions of this operation.
--apply dlSaving rules:
--(i) donot consider session if it has invalid timing for fwswitch day.
if(v_StartTime = BSM_3_2_9_ipdc_common.INVALID_INTEGER) then
if(v_firstIntendedSess = 1) then
v_firstSessNotCreated := 1;
v_firstIntendedSess := 0;
end if;
else
v_sessionCount := v_sessionCount + 1;
if(v_dpCode = v_fwSwitchDayProfile) then
v_opDayEnd := v_opDayEnd- v_dlSwitchAmount;
elsif(v_dpCode = v_bwSwitchDayProfile) then
v_opDayEnd := v_opDayEnd + v_dlSwitchAmount;

end if;
if(v_StartTime+v_duration > v_opDayEnd and i_isWholeDaySess = 0) then
raise sess_beyond_opday;
end if;


insert into recurSessInstance
(cpcode,
scode,
opdate,
sessnr,
sessionProfileEnum,
ipaddrsuffixstart,
portrangestart,
castingtime,
castingduration,
annstartdate,
svstatus)
values (i_addSession.cpCode,
i_addSession.sCode,
v_currentOpDate,
v_sessionCount,
i_addSession.sessionProfileCode,
0,
0,
v_StartTime,
v_duration,
v_currentOpDate,
'dirty');
end if;
--addSession details, esp (date,castingtime) into the temporary table.
end loop;
else


v_StartTime := i_addSession.castingTime;

v_duration := i_addSession.castingDuration;

--for recurrence forward switching day fault
--for 3.2.5 TD 1278 changes by imtiyaaz-----
if fw_flag = true and v_starttime = 14400 and v_duration =82800 then

v_starttime := i_addSession.castingTime-3600;
v_duration := i_addSession.castingDuration+3600;
end if;
--for 3.2.5 TD 1278 ----
--bug fix/* when rec sessions created starting with fwswitch day */
if (v_firstDPCode=v_bwSwitchDayProfile and v_dpCode<>v_bwSwitchDayProfile ) then

--for 3.2.5 TD 1285 changes by imtiyaaz-------
v_duration:=i_addSession.castingDuration;--- v_dlSwitchAmount;---v_dlSwitchAmount;
--commented for TD 1285 bw swithch day
--for 3.2.5 TD end 1285 changes by imtiyaaz-------
end if;

--interday recurrence alone
v_opDayEnd := v_dayEnd;

if(v_dpCode = v_fwSwitchDayProfile or v_dpCode = v_bwSwitchDayProfile) then

BSM_3_2_9_ipdc_schutil.getCorrectedStartTime(v_StartTime, v_duration, v_dpCode, v_fwSwitchDayProfile, v_bwSwitchDayProfile,
v_fwSwitchStartTime, v_bwSwitchStartTime, v_dlSwitchAmount);

--for 3.2.5 TD 1278 changes by imtiyaaz-----
if v_dpCode = v_fwSwitchDayProfile then
fw_flag:=true;
end if;
--for 3.2.5 TD end 1278 changes by imtiyaaz-------
end if;
--(i) donot consider session if it has invalid timing for fwswitch day.
if(v_StartTime = BSM_3_2_9_ipdc_common.INVALID_INTEGER) then
if(v_firstIntendedSess = 1) then
v_firstSessNotCreated := 1;
v_firstIntendedSess := 0;
end if;
else
v_sessionCount := v_sessionCount + 1;
if(v_dpCode = v_fwSwitchDayProfile) then

v_opDayEnd := v_opDayEnd - v_dlSwitchAmount;

elsif(v_dpCode = v_bwSwitchDayProfile) then

v_opDayEnd := v_opDayEnd + v_dlSwitchAmount;

end if;

if(v_StartTime+v_duration > v_opDayEnd and i_isWholeDaySess = 0) then

raise sess_beyond_opday;
end if;


insert into recurSessInstance (cpcode,
scode,
opdate,
sessnr,
sessionProfileEnum,
ipaddrsuffixstart,
portrangestart,
castingtime,
castingduration,
annstartdate,svstatus)
values (
i_addSession.cpCode,
i_addSession.sCode,
v_currentOpDate,
v_sessionCount,
i_addSession.sessionProfileCode,
0, 0,v_StartTime,
v_duration,
v_currentOpDate,
'dirty'
);

end if;
end if;
--v_nextWDNr :=
if(v_weekdayIndex != i_inclWeekDayList.count) then
v_currentOpDate := next_day(v_currentOpDate,
BSM_3_2_9_ipdc_auxiliary.getWeekDay(i_inclWeekDayList(v_weekdayIndex+1).weekDayNr).descr);
else
--if the end of weekdays has been reached, then loop back
--to the first weekday before exiting v_weekdayIndex loop
v_currentOpDate := next_day(v_currentOpDate,
BSM_3_2_9_ipdc_auxiliary.getWeekDay(i_inclWeekDayList(1).weekDayNr).descr);
end if;

end loop; --for v_weekdayIndex in 1..i_inclWeekDayList.count loop
end loop; --for v_weekIndex in 1..i_recurSession.nofWeeks loop
else
--this 'else' part is when only intraday recurrence is configured.
--hence, not necessary to check the following condition because
--either one or both of inter and intraday recurrence should be
--configured by user
select dayProfileCode into v_dpCode from OperationalDay where operationaldate=v_currentOpDate and isDeleted=0;

if (i_recurSession.nofRepetitions != 0) then

v_nofIntraDaySess := i_recurSession.nofRepetitions + 1;

for v_index in 1..v_nofIntraDaySess loop
--creating Sessions within a single day.
v_StartTime := i_recurSession.firstStartTime +
(v_index - 1)*i_recurSession.interval;

v_duration := i_addSession.castingDuration;

v_opDayEnd := v_dayEnd;


if(v_dpCode = v_fwSwitchDayProfile /*or v_dpCode = v_bwSwitchDayProfile*/)then-- --commented or condition for td1285 imtiyaz

BSM_3_2_9_ipdc_schutil.getCorrectedStartTime(v_StartTime, v_duration, v_dpCode, v_fwSwitchDayProfile, v_bwSwitchDayProfile,
v_fwSwitchStartTime, v_bwSwitchStartTime, v_dlSwitchAmount);

end if;

--(i) donot consider session if it has invalid timing for fwswitch day.
if(v_StartTime = BSM_3_2_9_ipdc_common.INVALID_INTEGER) then
if(v_firstIntendedSess = 1) then
v_firstSessNotCreated := 1;
v_firstIntendedSess := 0;
end if;
else

v_sessionCount := v_sessionCount + 1;
--addSession details, esp (date,castingtime) into the temporary table.
--i_addSession.sCode || ', ' || v_currentOpDate || ', ' ||
--v_sessionCount || ', ' /* || i_addSession.nofIPAddr || ', ' */ ||
--'0' || ', ' || /* i_addSession.nofPort || ', ' ||*/ '0' || ', ' ||
--v_StartTime || ', ' || v_duration || ',' || v_currentOpDate ||
--', ' || 'dirty');
if(v_dpCode = v_fwSwitchDayProfile) then
v_opDayEnd := v_opDayEnd - v_dlSwitchAmount;
elsif(v_dpCode = v_bwSwitchDayProfile) then
v_opDayEnd := v_opDayEnd + v_dlSwitchAmount;


end if;
if(v_StartTime+v_duration > v_opDayEnd and i_isWholeDaySess = 0) then
raise sess_beyond_opday;
end if;
insert into recurSessInstance (cpcode,scode,opdate,sessnr, sessionProfileEnum,
/* nofipaddr,*/ ipaddrsuffixstart,portrangestart,/* nofports,*/ castingtime,
castingduration,annstartdate,svstatus)
values (i_addSession.cpCode, i_addSession.sCode, v_currentOpDate,v_sessionCount,i_addSession.sessionProfileCode,
/* i_addSession.nofIPAddr,*/ 0, /* i_addSession.nofPort,*/ 0,v_StartTime,
v_duration,
-- changes for version 3.0.3
--(v_currentOpDate - v_annStartDateOffset),
v_currentOpDate,
'dirty');
end if;
end loop;
end if;

end if;

--> CR 464
if i_isWholeDaySess = 1 then
for i in (select * from recurSessInstance) loop
BSM_3_2_9_ipdc_schutil. getcurrdaycastingtime(v_currcasting);
if i.opdate = (case when v_currcasting>86400 then trunc(sysdate)-1 else trunc(sysdate) end) then
--v_currcasting := BSM_3_2_9_ipdc_schutil.getcurrdaycastingtime(v_currduration);
BSM_3_2_9_ipdc_schutil.getcurrdaycastingtime(v_currcasting);
BSM_3_2_9_ipdc_schutil.getcurrdayduration(v_currcasting,v_currduration);


update recursessinstance
set recursessinstance.castingtime = v_currcasting,
recursessinstance.castingduration = v_currduration
where scode = i.scode
and cpcode = i.cpcode
and sessnr = i.sessnr
and opdate = i.opdate;
else
select dayprofilecode
into v_dayprofile
from operationalday
where operationaldate=i.opdate;

if v_dayprofile not in (v_fwSwitchDayProfile,v_bwSwitchDayProfile) then
update recursessinstance
set recursessinstance.castingtime = BSM_3_2_9_ipdc_deployment.getSystemParameter('Calendar.OperationalDayStart'),
recursessinstance.castingduration = to_number(BSM_3_2_9_ipdc_deployment.getSystemParameter('Calendar.OperationalDayEnd')) -
to_number(BSM_3_2_9_ipdc_deployment.getSystemParameter('Calendar.OperationalDayStart'))
where scode = i.scode
and cpcode = i.cpcode
and sessnr = i.sessnr
and opdate = i.opdate;
elsif v_dayprofile = v_fwSwitchDayProfile then
update recursessinstance
set recursessinstance.castingtime = BSM_3_2_9_ipdc_deployment.getSystemParameter('Calendar.OperationalDayStart'),
recursessinstance.castingduration = to_number(BSM_3_2_9_ipdc_deployment.getSystemParameter('Calendar.OperationalDayEnd')) -
(to_number(BSM_3_2_9_ipdc_deployment.getSystemParameter('Calendar.OperationalDayStart')) +
v_dlSwitchAmount)
where scode = i.scode
and cpcode = i.cpcode
and sessnr = i.sessnr
and opdate = i.opdate;
elsif v_dayprofile = v_bwSwitchDayProfile then


update recursessinstance
set recursessinstance.castingtime = BSM_3_2_9_ipdc_deployment.getSystemParameter('Calendar.OperationalDayStart'),
recursessinstance.castingduration = to_number(BSM_3_2_9_ipdc_deployment.getSystemParameter('Calendar.OperationalDayEnd')) -
(to_number(BSM_3_2_9_ipdc_deployment.getSystemParameter('Calendar.OperationalDayStart'))-v_dlSwitchAmount)

where scode = i.scode
and cpcode = i.cpcode
and sessnr = i.sessnr
and opdate = i.opdate;

end if;
end if;
end loop;
end if;

--> CR 93

if i_enddate is not null then
delete from recurSessInstance
where Opdate>i_enddate;
end if;


v_nofSessions := v_sessionCount; --overwrite the v_nofSessions obtained from getTotalRecurSessions()
--iterate through the sessions and do the following operations before calling
--insertServiceSession and others
--getIPAddrPort() for every session
--checkBWAvailabilityForRecurrence() - user maynot have given partitions
--for all sessions, so determine which can be used combination of
----checkBWAvailability() and obtainPartitionsForSession()

--loop through the list of recurrent sessions and find whether there will
--be a situation where a particular instance cannot be created (CP doesn't exist)
v_PartitionList := i_PartitionList;
v_ipAddrNotAvailable := -1;
v_portNotAvailable := -1;
if(v_sessOverlap != 1) then
declare
cursor sessCur is
select opDate, castingTime, castingduration, annStartDate
from recurSessInstance
order by opDate, castingTime; --TD CR 422,423,424,425 --this ordering is very important
begin
v_i := 1;
-- sudha
v_sessCount := 1;

v_tempCount:= 1;

/* Tuning
max burst interval is taken out of loop as it is assumed that in create mode
all values will be same for all instances
*/

v_maxBurstInterval := BSM_3_2_9_ipdc_schedule.getMaxBurstInterval(i_addSession);

for sessInstance in sessCur loop
v_sessPartitions := sessPartitionList();
v_session := servSession(i_addSession.cpCode,
i_addSession.sCode,
sessInstance.opDate,i_addSession.sessionNr,
-1,
i_addSession.descr,
i_addSession.ipAddrSuffStart,i_addSession.portStart,
i_addSession.protectionprotocolenum,--for release 3.1.1
i_addSession.ipSecauthSeed, --for release 3.1.1
i_addSession.ipSecencryptionSeed,--for release 3.1.1
i_addSession.ipeMgmtStatus,
i_addSession.ipemgmtids,
i_addSession.nofiperetries, --for release 3.1.1
i_addSession.sessionProfileCode,
sessInstance.castingTime,
--i_addSession.castingDuration,
sessInstance.castingduration,
i_addSession.bitRate,
i_addSession.totalPrice,i_addSession.schValStatus,
i_addSession.schValInfo,
0,
0,
0,
0,
0,
0,
i_addSession.ERRORCORRECTIONPERCENTAGE,
/* for 3.2 start by ravi madhamanchi */
i_addSession.PriorityNumber,
i_addSession.isDeleted,
i_addSession.modifDate,
i_addSession.modifUser);

--collect the non - overlapping sessions here for allocating IPAddress

v_recSessionList.extend;
v_recSessionList(v_tempCount) := v_session;
v_tempCount := v_tempCount + 1;
-- bug fix start -- ipdc_MAIN E055472 -- sudha
if(v_sessCount = 1 and v_PartitionList is not null and v_PartitionList.count != 0) then
--For the first session, check the bandwidth availabilty with the given partitions
--sessPartitions := sessPartitionList();
v_SessCount := v_SessCount + 1;
-- bug fix start -- ipdc_MAIN E054358 -- sudha
-- if the first Session is not created , dont do bandwidth checking
-- IPDC 3.0.1 MPE-FEC
--for tuning --v_maxBurstInterval := BSM_3_2_9_ipdc_schedule.getMaxBurstInterval(v_session);
if(v_firstSessNotCreated != 1) then

v_sessPartitions := BSM_3_2_9_ipdc_schpart2.checkBWAvailability(v_session.cpCode, v_session.sCode,
v_session.opDate,v_session.sessionNr,v_session.bitRate,v_maxBurstInterval, v_session.castingTime,v_session.castingDuration,
/* i_addSession.dcMethod,*/ v_PartitionList, o_nwAreaNotAvailable,v_temppartitionExists, v_temppartitionUseful,v_session.ERRORCORRECTIONPERCENTAGE,v_session.sessionProfileCode,v_session.ProtectionprotocolEnum);
end if;
-- bug fix end -- ipdc_MAIN E054358
else
--for the other sessions also, first check whether bandwidth is available with the
--given partitions
if(v_PartitionList is not null and v_partitionList.count != 0) then
-- IPDC 3.0.1 MPE-FEC

v_sessPartitions := BSM_3_2_9_ipdc_schpart2.checkBWAvailability(v_session.cpCode, v_session.sCode,
v_session.opDate,v_session.sessionNr,v_session.bitRate,v_maxBurstInterval, v_session.castingTime,v_session.castingDuration,
/* i_addSession.dcMethod,*/ v_PartitionList, o_nwAreaNotAvailable,v_temppartitionExists, v_temppartitionUseful,v_session.ERRORCORRECTIONPERCENTAGE,v_session.sessionProfileCode,v_session.ProtectionprotocolEnum);
-- if partitions are not useful, then obtain usable partitions
if(v_sessPartitions is null) then
v_sessPartitions := BSM_3_2_9_ipdc_schpart2.obtainPartitionsForSession(v_session, 0, v_partitionList,
v_tempnwAreaNotAvailable, v_tempnwAreaNoBW, v_temppartitionExists, v_temppartitionUseful);
end if;
else
-- if partitions are not given, obtain partitions
v_sessPartitions := BSM_3_2_9_ipdc_schpart2.obtainPartitionsForSession(v_session, 0, v_partitionList,
v_tempnwAreaNotAvailable, v_tempnwAreaNoBW, v_temppartitionExists, v_temppartitionUseful);
end if;
end if;
-- bug fix End -- ipdc_MAIN E055472 -- sudha

v_tempDirtySess := 0;
if(v_sessPartitions is not null and v_sessPartitions.count != 0) then
for v_index in 1..v_sessPartitions.count loop
if(v_sessPartitions(v_index).sessionStatus = 'dirty') then
v_dirtySessionFound := 1;
v_tempDirtySess := 1;
end if;
end loop;
else
null;
--dbms_output.put_line('createRecurrence: Partitions not available');
end if;

--collect the status of only first 100 records and collect the list of only bad sessions
if(v_i <= 100 and
(v_temppartitionExists = BSM_3_2_9_ipdc_common.PARTITION_DOESNOT_EXIST or v_temppartitionUseful = BSM_3_2_9_ipdc_common.PARTITION_NOT_USABLE or v_tempDirtySess =1 or
v_tempIPAddrNotAvailable = 1 or v_tempPortNotAvailable = 1)) then
v_tempsessStatList.extend;
v_tempsessStatList(v_i) := sessStatus(v_session.cpCode,v_session.sCode,v_session.opDate,
v_session.sessionNr,v_session.castingTime,v_session.castingDuration,v_session.bitRate,
v_temppartitionExists,v_temppartitionUseful,v_tempDirtySess,
v_tempIPAddrNotAvailable,v_tempPortNotAvailable);
v_i := v_i + 1;
end if;


--set the token values for error conditions
if(v_partitionExists = BSM_3_2_9_ipdc_common.INVALID_INTEGER and v_temppartitionExists = BSM_3_2_9_ipdc_common.PARTITION_DOESNOT_EXIST) then
v_partitionExists := BSM_3_2_9_ipdc_common.PARTITION_DOESNOT_EXIST;
end if;
if(v_partitionUseful = BSM_3_2_9_ipdc_common.INVALID_INTEGER and v_temppartitionUseful = BSM_3_2_9_ipdc_common.PARTITION_NOT_USABLE) then
v_partitionUseful := BSM_3_2_9_ipdc_common.PARTITION_NOT_USABLE;
end if;
if((v_nwAreaNotAvailable is not null and v_nwAreaNotAvailable.count = 0) and
(v_tempnwAreaNotAvailable is not null and v_tempnwAreaNotAvailable.count != 0)) then
v_nwAreaNotAvailable := v_tempnwAreaNotAvailable;
end if;
if((v_nwAreaNoBW is not null and v_nwAreaNoBW.count = 0) and
(v_tempnwAreaNoBW is not null and v_tempnwAreaNoBW.count != 0)) then
v_nwAreaNoBW := v_tempnwAreaNoBW;
end if;
if(sessPartitions is not null and v_sessPartitions is null) then
sessPartitions := null;
end if;
if(v_ipAddrNotAvailable = -1 and v_tempIPAddrNotAvailable = 1) then
v_ipAddrNotAvailable := 1;
end if;
if(v_portNotAvailable = -1 and v_tempPortNotAvailable = 1) then
v_portNotAvailable := 1;
end if;

-- bug fix start -- ipdc_MAIN E055472
-- Commented ( Sudha ) -- For all the Sessions, Check whether the partitions are useful
-- with the given partitions and only if they are not available, we obtain other partitions
--v_PartitionList := null; --in order to use the given partitions for only the first session
end loop;--for sessInstance in sessCur loop

v_tempIPAddrNotAvailable := 0;
v_tempPortNotAvailable := 0;


----TD CR 422,423,424,425 start
--BSM_3_2_9_ipdc_ipport1.getIPAddrPortForRec(v_recSessionList,0);
--creating channel partition list from the sesspartitionlist as channel partition list
--is not provided from the PRwebportal, CU and CPWSI.
if(v_sessPartitions is not null and v_sessPartitions.count != 0) then
v_basePartList := BSM_3_2_9_ipdc_schutil.getChannelPartitionList(v_sessPartitions);
else
null;
end if;

BSM_3_2_9_ipdc_ipport3.findIPReuseForRecSession(v_recSessionList, v_basePartList);
--TD CR 422,423,424,425 end
v_isNonOverLapSess := 1;
-- TODO check the count of sessions passed back to the GUI v_i 100
-- loop through the sessions and check whether ipaddr/port cannot be allocated
-- if we are not able to allocate, collect that session in v_tempsessStatList

v_tempCount := v_tempsessStatList.count;
for i in 1..v_recSessionList.count loop
-- Commented sudha .. the IPAddrPort
--v_tempIPAddrNotAvailable := v_recSessionList(i).ipAddrSuffStart;
--v_tempPortNotAvailable := v_recSessionList(i).portStart;

--if(v_tempIPAddrNotAvailable = -1 or v_tempPortNotAvailable = -1) then
if(v_recSessionList(i).ipAddrSuffStart = -1 or v_recSessionList(i).portStart = -1) then

if(v_recSessionList(i).ipAddrSuffStart = -1) then
v_tempIPAddrNotAvailable := 1;
end if;
if(v_recSessionList(i).portStart = -1) then
v_tempPortNotAvailable := 1;
end if;

v_i := v_tempsessStatList.count;
for j in 1..v_tempsessStatList.count loop
-- if the session is already in the sessStatus List, update it..or else, create it
if((v_recSessionList(i).cpCode = v_tempSessStatList(j).cpCode) and
(v_recSessionList(i).sCode = v_tempSessStatList(j).sCode) and
(v_recSessionList(i).opDate = v_tempSessStatList(j).opDate) and
(v_recSessionList(i).sessionNr = v_tempSessStatList(j).sessionNr)) then
v_tempSessStatList(j).noIP := v_tempIPAddrNotAvailable;
v_tempSessStatList(j).noPort := v_tempPortNotAvailable;
else
v_tempSessStatList.extend;
v_i:= v_i + 1;
v_tempSessStatList(v_i) := sessStatus(v_recSessionList(i).cpCode,v_recSessionList(i).sCode,
v_recSessionList(i).opDate, v_recSessionList(i).sessionNr,
v_recSessionList(i).castingTime, v_recSessionList(i).castingDuration,
v_recSessionList(i).bitRate, 1,1,1,
v_tempIPAddrNotAvailable,
v_tempPortNotAvailable);
if(v_i > 100) then
exit;
end if;
end if;

if(v_tempsessStatList.count > 100) then
exit;
end if;

end loop;
-- set the token values for error conditions
if(v_tempIPAddrNotAvailable = 1) then
v_ipAddrNotAvailable := 1;
end if;
if(v_tempPortNotAvailable = 1) then
v_portNotAvailable := 1;
end if;
/* this check should be done inside the loop ..
if(v_tempsessStatList.count > 100) then
exit;
end if; */
end if;

end loop;
end; --cursor sessCur is
else
--for overlapping sessions, collect sessions on per day basis and find bw availability
v_PartitionListForOverlap := v_PartitionList;
declare
cursor sessCur is
select opDate,castingTime,annStartDate from recurSessInstance order by opDate,castingtime;
begin
v_i := 1;
v_IDSessIndex := 1;
v_sessionCount := 0;
--v_firstOpDate := i_addSession.opDate;
--it is possible that there are no sessions for the first operationaldate because of say
--fwswitch timings. If there are no sessions at all in the recurrence, then v_firstOpDate will be null.
--Also, check whether the firstopdate is same as the session date passed, if not, then
--then dont use the given partitions at all
select MIN(opDate) into v_firstOpDate from recurSessInstance;
if(v_firstOpDate != i_addSession.opDate) then
v_PartitionListForOverlap := null;
end if;
for sessInstance in sessCur loop
if(v_recurDay is null) then
--for first session
v_intraDaySessions := servSessionList();
v_recurDay := sessInstance.opDate;

v_IDSessIndex := 1;

v_intraDaySessions.extend;


v_intraDaySessions(v_IDSessIndex) :=
servSession(i_addSession.cpCode,
i_addSession.sCode,
sessInstance.opDate,
v_IDSessIndex,
-1,
i_addsession.descr,

i_addSession.ipAddrSuffStart,
i_addSession.portStart,
i_addSession.protectionprotocolenum,--for release 3.1.1
i_addSession.ipSecauthSeed, --for release 3.1.1
i_addSession.ipSecencryptionSeed, --for release 3.1.1
i_addSession.ipeMgmtStatus,
i_addSession.ipeMgmtIds,
i_addSession.nofiperetries,--for release 3.1.1
i_addSession.sessionProfileCode,
sessInstance.castingTime,
i_addSession.castingDuration,
i_addSession.bitRate,
i_addSession.totalPrice,
i_addSession.schValStatus,
i_addSession.schValInfo,
0,
0,
0,
0,
0,
0,
i_addSession.ErrorCorrectionPercentage,
---for 3.2 start by ravi madhamanchi---
i_addSession.PriorityNumber,
---for 3.2 end-----
i_addSession.isDeleted,
i_addSession.modifDate,
i_addSession.modifUser
);

v_IDSessIndex := v_IDSessIndex + 1;
v_sessionCount := v_sessionCount + 1;

elsif(v_recurDay is not null and v_recurDay = sessInstance.opDate) then
--if the new session belongs to same day, add it to the list.
--add the session to existing list

v_intraDaySessions.extend;

v_intraDaySessions(v_IDSessIndex) :=
servSession(i_addSession.cpCode,
i_addSession.sCode,
sessInstance.opDate,
v_IDSessIndex,
-1,
i_addsession.descr,
i_addSession.ipAddrSuffStart,
i_addSession.portStart,
i_addSession.protectionprotocolenum,--for release 3.1.1
i_addSession.ipSecauthSeed, --for release 3.1.1
i_addSession.ipSecencryptionSeed, --for release 3.1.1
i_addSession.ipeMgmtStatus,
i_addSession.ipeMgmtIds,
i_addSession.nofiperetries,--for release 3.1.1
i_addSession.sessionProfileCode,
sessInstance.castingTime,
i_addSession.castingDuration,
i_addSession.bitRate,
i_addSession.totalPrice,
i_addSession.schValStatus,
i_addSession.schValInfo,
0,
0,
0,
0,
0,
0,
i_addSession.ErrorCorrectionPercentage,
--for 3.2 start by ravi madhamanchi----
i_addSession.PriorityNumber,
--for 3.2 end-----
i_addSession.isDeleted,
i_addSession.modifDate,
i_addSession.modifUser
);
v_IDSessIndex := v_IDSessIndex + 1;
v_sessionCount := v_sessionCount + 1;


--for the last day
if(v_sessionCount = v_nofSessions) then
if(v_recurDay != v_firstOpDate or v_firstSessNotCreated = 1) then
--use the given partitions only for the first Session. if the session is
--not being created (say because of bad fwswitch time), then the given partitions
--are never used
v_PartitionListForOverlap := null;
end if;
v_sessPartitions := BSM_3_2_9_ipdc_srecsup.getPartForRecurSess(v_intraDaySessions,v_PartitionListForOverlap,
v_tempnwAreaNotAvailable,v_tempnwAreaNoBW, v_arrPartExists, v_arrPartUseful);

--length of v_ipPortList is equal to nof intraday sessions
v_ipPortList := BSM_3_2_9_ipdc_ipport2.getIPPortForIntraDay(v_intraDaySessions,v_sysParamIPStart,v_sysParamIPEnd/*,
v_sysParamPortStart,v_sysParamPortEnd*/);
v_tempDirtySess := 0;
for v_IDIndex in 1..v_intraDaySessions.count loop
if(v_sessPartitions is not null and v_sessPartitions.count != 0) then
for v_tempSessPartIndex in 1..v_sessPartitions.count loop
if(v_sessPartitions(v_tempSessPartIndex).sessionNr = v_intraDaySessions(v_IDIndex).sessionNr and
v_sessPartitions(v_tempSessPartIndex).sessionStatus = 'dirty') then
v_dirtySessionFound := 1;
v_tempDirtySess := 1;
end if;
end loop;
end if;

v_tempIPAddrNotAvailable := 0;
v_tempPortNotAvailable := 0;
if(v_ipPortList(v_IDIndex).ipAddrSuffStart = -1) then
v_tempIPAddrNotAvailable := 1;
end if;
if(v_ipPortList(v_IDIndex).portStart = -1) then
v_tempPortNotAvailable := 1;
end if;

--collect the status of only first 100 records and collect the list of only bad sessions
if(v_i <= 100 and
(v_arrPartExists(v_IDIndex) = 0 or v_arrPartUseful(v_IDIndex) = 0 or v_tempDirtySess =1 or
v_tempIPAddrNotAvailable = 1 or v_tempPortNotAvailable = 1)) then
v_tempsessStatList.extend;
v_tempsessStatList(v_i) := sessStatus(v_intraDaySessions(v_IDIndex).cpCode,v_intraDaySessions(v_IDIndex).sCode,v_intraDaySessions(v_IDIndex).opDate,
v_intraDaySessions(v_IDIndex).sessionNr,v_intraDaySessions(v_IDIndex).castingTime,v_intraDaySessions(v_IDIndex).castingDuration,v_int raDaySessions(v_IDIndex).bitRate,
v_arrPartExists(v_IDIndex),v_arrPartUseful(v_IDIndex),v_tempDirtySess,
v_tempIPAddrNotAvailable,v_tempPortNotAvailable);
v_i := v_i + 1;
end if;


--set the token values for error conditions
if(v_partitionExists = BSM_3_2_9_ipdc_common.INVALID_INTEGER and v_arrPartExists(v_IDIndex) = BSM_3_2_9_ipdc_common.PARTITION_DOESNOT_EXIST) then
v_partitionExists := BSM_3_2_9_ipdc_common.PARTITION_DOESNOT_EXIST;
end if;
if(v_partitionUseful = BSM_3_2_9_ipdc_common.INVALID_INTEGER and v_arrPartUseful(v_IDIndex) = BSM_3_2_9_ipdc_common.PARTITION_NOT_USABLE) then
v_partitionUseful := BSM_3_2_9_ipdc_common.PARTITION_NOT_USABLE;
end if;
if(sessPartitions is not null and v_sessPartitions is null) then
sessPartitions := null;
end if;
if(v_ipAddrNotAvailable = BSM_3_2_9_ipdc_common.INVALID_INTEGER and v_tempIPAddrNotAvailable = BSM_3_2_9_ipdc_common.TRUE_VALUE) then
v_ipAddrNotAvailable := BSM_3_2_9_ipdc_common.TRUE_VALUE;
end if;
if(v_portNotAvailable = BSM_3_2_9_ipdc_common.INVALID_INTEGER and v_tempPortNotAvailable = BSM_3_2_9_ipdc_common.TRUE_VALUE) then
v_portNotAvailable := BSM_3_2_9_ipdc_common.TRUE_VALUE;
end if;
end loop; --for v_IDIndex in 1..v_intraDaySessions.count
end if; --if(v_sessionCount = v_nofSessions)

elsif(v_recurDay is not null and v_recurDay != sessInstance.opDate) then
--if the new session belongs to another (next session) day,
--check for BW availability in the collected Sessions first and then create new servsessionList.
if(v_recurDay != v_firstOpDate or v_firstSessNotCreated = 1) then
v_PartitionListForOverlap := null;
end if;
v_sessPartitions := BSM_3_2_9_ipdc_srecsup.getPartForRecurSess(v_intraDaySessions,v_PartitionListForOverlap,
v_tempnwAreaNotAvailable,v_tempnwAreaNoBW, v_arrPartExists, v_arrPartUseful);

--length of v_ipPortList is equal to nof intraday sessions
v_ipPortList := BSM_3_2_9_ipdc_ipport2.getIPPortForIntraDay(v_intraDaySessions,v_sysParamIPStart,v_sysParamIPEnd/*,
v_sysParamPortStart,v_sysParamPortEnd*/);--TD CR 422,423,424,425 --TODO this can be removed, to analyse
v_tempDirtySess := 0;
for v_IDIndex in 1..v_intraDaySessions.count loop
if(v_sessPartitions is not null and v_sessPartitions.count != 0) then
for v_tempSessPartIndex in 1..v_sessPartitions.count loop
if(v_sessPartitions(v_tempSessPartIndex).sessionNr = v_intraDaySessions(v_IDIndex).sessionNr and
v_sessPartitions(v_tempSessPartIndex).sessionStatus = 'dirty') then
v_dirtySessionFound := 1;
v_tempDirtySess := 1;
end if;
end loop;
end if;

v_tempIPAddrNotAvailable := 0;
v_tempPortNotAvailable := 0;
if(v_ipPortList(v_IDIndex).ipAddrSuffStart = -1) then
v_tempIPAddrNotAvailable := 1;
end if;
if(v_ipPortList(v_IDIndex).portStart = -1) then
v_tempPortNotAvailable := 1;
end if;
--collect the status of only first 100 records and collect the list of only bad sessions
if(v_i <= 100 and
(v_arrPartExists(v_IDIndex) = 0 or v_arrPartUseful(v_IDIndex) = 0 or v_tempDirtySess =1 or
v_tempIPAddrNotAvailable = 1 or v_tempPortNotAvailable = 1)) then
v_tempsessStatList.extend;
v_tempsessStatList(v_i) := sessStatus(v_intraDaySessions(v_IDIndex).cpCode,v_intraDaySessions(v_IDIndex).sCode,v_intraDaySessions(v_IDIndex).opDate,
v_intraDaySessions(v_IDIndex).sessionNr,v_intraDaySessions(v_IDIndex).castingTime,v_intraDaySessions(v_IDIndex).castingDuration,v_int raDaySessions(v_IDIndex).bitRate,
v_arrPartExists(v_IDIndex),v_arrPartUseful(v_IDIndex),v_tempDirtySess,
v_tempIPAddrNotAvailable,v_tempPortNotAvailable);
v_i := v_i + 1;
end if;


--set the token values for error conditions
if(v_partitionExists = BSM_3_2_9_ipdc_common.INVALID_INTEGER and v_arrPartExists(v_IDIndex) = BSM_3_2_9_ipdc_common.PARTITION_DOESNOT_EXIST) then
v_partitionExists := BSM_3_2_9_ipdc_common.PARTITION_DOESNOT_EXIST;
end if;
if(v_partitionUseful = BSM_3_2_9_ipdc_common.INVALID_INTEGER and v_arrPartUseful(v_IDIndex) = BSM_3_2_9_ipdc_common.PARTITION_NOT_USABLE) then
v_partitionUseful := BSM_3_2_9_ipdc_common.PARTITION_NOT_USABLE;
end if;
if(sessPartitions is not null and v_sessPartitions is null) then
sessPartitions := null;
end if;
if(v_ipAddrNotAvailable = BSM_3_2_9_ipdc_common.INVALID_INTEGER and v_tempIPAddrNotAvailable = BSM_3_2_9_ipdc_common.TRUE_VALUE) then
v_ipAddrNotAvailable := BSM_3_2_9_ipdc_common.TRUE_VALUE;
end if;
if(v_portNotAvailable = BSM_3_2_9_ipdc_common.INVALID_INTEGER and v_tempPortNotAvailable = BSM_3_2_9_ipdc_common.TRUE_VALUE) then
v_portNotAvailable := BSM_3_2_9_ipdc_common.TRUE_VALUE;
end if;
end loop; --for v_IDIndex in 1..v_intraDaySessions.count

v_intraDaySessions := servSessionList();
v_recurDay := sessInstance.opDate;

v_IDSessIndex := 1;


v_intraDaySessions.extend;

v_intraDaySessions(v_IDSessIndex) :=
servSession(i_addSession.cpCode,
i_addSession.sCode,
sessInstance.opDate, v_IDSessIndex,
-1,
i_addsession.descr,
i_addSession.ipAddrSuffStart,
i_addSession.portStart,
i_addSession.protectionprotocolenum,--for release 3.1.1
i_addSession.ipSecauthSeed, --for release 3.1.1
i_addSession.ipSecencryptionSeed, --for release 3.1.1
i_addSession.ipeMgmtStatus,
i_addSession.ipemgmtIds,
i_addSession.nofiperetries,--for release 3.1.1
i_addSession.sessionProfileCode,
sessInstance.castingTime,
i_addSession.castingDuration,
i_addSession.bitRate,
i_addSession.totalPrice,
i_addSession.schValStatus,
i_addSession.schValInfo,
0,
0,
0,
0,
0,
0,
i_addSession.ErrorCorrectionPercentage,
--for 3.2 by ravi madhamanchi---
i_addSession.PriorityNumber,
--for 3.2 end---
i_addSession.isDeleted,
i_addSession.modifDate,
i_addSession.modifUser
);

v_IDSessIndex := v_IDSessIndex + 1;
v_sessionCount := v_sessionCount + 1;
end if; --if((v_recurDay is not null and v_recurDay = sessInstance.opDate) ...

end loop; --for sessInstance in sessCur loop
end;
end if;--if(v_sessOverlap != 1) then


--if conditions are fine or force creation is required, then create sessions
if(sessPartitions is null or
(v_nwAreaNotAvailable is not null and v_nwAreaNotAvailable.count != 0) or
v_partitionExists = BSM_3_2_9_ipdc_common.PARTITION_DOESNOT_EXIST or v_partitionUseful = BSM_3_2_9_ipdc_common.PARTITION_NOT_USABLE) then
--never create session if partitions arent available
o_nwAreaNotAvailable := v_nwAreaNotAvailable;
o_nwAreaNoBW := v_nwAreaNoBW;
o_sessStatusList := v_tempsessStatList;
o_sessPartitions := sessPartitions;
else

if(((v_nwAreaNoBW is not null and v_nwAreaNoBW.count != 0) or
v_dirtySessionFound = 1
-- changes for version 3.0.2 Srinivas
--or o_doItemsFit = 0 or o_doProgramsFit = 0
or v_invSessItems = 1 or
v_ipAddrNotAvailable = 1 or v_portNotAvailable = 1) and
i_forceCreation = 0) then
--do not create the session if there isnt enough bw.
--note: ipaddr/port check is not performed here - should be included
o_nwAreaNotAvailable := v_nwAreaNotAvailable;
o_nwAreaNoBW := v_nwAreaNoBW;
o_sessStatusList := v_tempsessStatList;
o_sessPartitions := sessPartitions;
null;
else
--create the Recurring Session and includedWeekDays first
v_recurSession := recurSession(i_recurSession.recurSessId,i_recurSession.firstStartTime,i_recurSession.interval,
i_recurSession.nofRepetitions,i_recurSession.firstOpDate,i_recurSession.nofWeeks,
i_recurSession.isDeleted,i_recurSession.modifDate,i_recurSession.modifUser);



BSM_3_2_9_ipdc_schinsget.insertRecurringSession(v_recurSession);

v_recurSession := BSM_3_2_9_ipdc_schinsget.getRecurringSession(v_recurSession);

if(i_recurSession.nofWeeks != 0) then
v_inclWeekDayList := i_inclWeekDayList;
for v_index in 1..v_inclWeekDayList.count loop
v_inclWeekDayList(v_index).recurSessID := v_recurSession.recurSessID;
BSM_3_2_9_ipdc_schinsget.insertIncludedWeekDay(v_inclWeekDayList(v_index));
end loop;
end if;

v_sessPartCount := o_sessPartitions.count;
declare
cursor sessCur is
select cpCode,sCode,opDate,sessNr,/* nofIPAddr,*/ ipAddrSuffixStart,
portRangeStart/* ,nofPorts*/ ,castingTime,castingduration,annStartDate,svStatus
from recurSessInstance;

begin
v_i := 1;
--v_ipseckeyIndex := 1;
v_ipSecSeedIndex := 1;


--Tuning changes
v_maxBurstInterval := BSM_3_2_9_ipdc_schedule.getMaxBurstInterval(i_addSession);


for sessInstance in sessCur loop
--nof sessions could be more than the nof ipsecSeeds. Reuse the keys after the
--session count reaches 10000 (ipSecSeedList's size)
--this neednot be done in case of non-encrypted sessions
if(v_ipSecSeedIndex = 10000) then
v_ipSecSeedIndex := 1;
end if;

--- Sudha
v_sessPartitions := null;
-- Sudha

v_session := servSession(
i_addSession.cpCode,
i_addSession.sCode,
sessInstance.opDate,
i_addSession.sessionNr,
v_recurSession.recurSessID,
i_addsession.descr,
i_addSession.ipAddrSuffStart,
i_addSession.portStart,
i_addSession.protectionprotocolenum,
i_addSession.ipSecauthseed,
i_addSession.ipSecencryptionseed,
i_addSession.ipeMgmtStatus,
i_addSession.ipemgmtids,
i_addSession.nofiperetries,--for release 3.1.1
i_addSession.sessionProfileCode,
sessInstance.castingTime,
sessInstance.castingduration,
i_addSession.bitRate,
i_addSession.totalPrice,
i_addSession.schValStatus,
i_addSession.schValInfo,
0,
0,
0,
0,
0,
0,
i_addSession.ErrorCorrectionPercentage,
---for 3.2 start by ravi madhamanchi---
i_addSession.PriorityNumber,
--for 3.2 end----
i_addSession.isDeleted,
i_addSession.modifDate,
i_addSession.modifUser);
--use the given partition for only first session (if it is being created,i.e., no bad fwswitch time problem)
--(following the same policy as in checking for BW availability above)

-- bug fix start -- ipdc_MAIN E055472 -- sudha
if(i_PartitionList is not null and i_PartitionList.count != 0
--and v_firstSessNotCreated = 0) then
) then
--re-initialize v_PartitionList because it was set to null above

v_PartitionList := chPartitionList();

-- we take the partitions supplied by the user and check for bandwidth availabilty,
-- if the partitions are not useful, we obtain partitions
for v_partIndex in 1..i_PartitionList.count loop
v_PartitionList.extend;


v_PartitionList(v_partIndex) := chPartition(i_PartitionList(v_partIndex).uniqueID,i_PartitionList(v_partIndex).ppStartDate,
i_PartitionList(v_partIndex).dpCode,i_PartitionList(v_partIndex).nwAreaCode,i_PartitionList(v_partIndex).subChannelID,
i_PartitionList(v_partIndex).chPartitionNr,i_PartitionList(v_partIndex).partitionExists,i_PartitionList(v_partIndex).partitionUseful, i_PartitionList(v_partIndex).pid);--for 3.2.3 ravim
v_bwpid:=i_PartitionList(v_partIndex).pid;
end loop;
-- IPDC 3.0.1 MPE-FEC
/* Tuning -- burst interval calculated before entering into loop*/
--v_maxBurstInterval := BSM_3_2_9_ipdc_schedule.getMaxBurstInterval(i_addSession);

v_sessPartitions := BSM_3_2_9_ipdc_schpart2.checkBWAvailability(sessInstance.cpCode, sessInstance.sCode,
sessInstance.opDate,sessInstance.sessNr,i_addSession.bitRate,v_maxBurstInterval, sessInstance.castingTime,sessInstance.castingDuration,
/* i_addSession.dcMethod,*/ v_PartitionList, v_tempnwAreaList,v_temppartExists, v_temppartUseful,i_addSession.ERRORCORRECTIONPERCENTAGE,i_addSession.sessionProfileCode,i_addSession.ProtectionprotocolEnum);
-- If partitions are not useful, then set partition list to null, so that it will
--be collected during serviceSession creation
-- bug fix #E062198 --ramana

if(v_sessPartitions is null OR v_i > 1) then

v_sessPartitions := BSM_3_2_9_ipdc_schpart2.obtainPartitionsForSession(v_session, 0, v_partitionList,
v_tempnwAreaNotAvailable, v_tempnwAreaNoBW, v_temppartitionExists, v_temppartitionUseful);

if(v_sessPartitions is not null) then
v_PartitionList := chPartitionList();

for v_partIndex in 1..v_sessPartitions.count loop
v_PartitionList.extend;
v_pid := null;--for 3.2.5 ravi m---

for v_count in 1..i_PartitionList.count loop
if(i_PartitionList(v_count).ppStartDate = v_sessPartitions(v_partIndex).ppStartDate and
i_PartitionList(v_count).dpCode = v_sessPartitions(v_partIndex).dpCode and
i_PartitionList(v_count).nwAreaCode = v_sessPartitions(v_partIndex).nwAreaCode and
i_PartitionList(v_count).subChannelID = v_sessPartitions(v_partIndex).subChannelID and
i_PartitionList(v_count).chPartitionNr = v_sessPartitions(v_partIndex).chPartitionNr) then

v_pid := i_PartitionList(v_count).pid;

end if;
end loop;

v_PartitionList(v_partIndex) := chPartition(-1,v_sessPartitions(v_partIndex).ppStartDate,
v_sessPartitions(v_partIndex).dpCode,v_sessPartitions(v_partIndex).nwAreaCode,v_sessPartitions(v_partIndex).subChannelID,
v_sessPartitions(v_partIndex).chPartitionNr,v_tempValue,v_tempValue,v_bwpid);--v_pid);--for 3.2.3 ravim
end loop;
--v_partitionList := null;
end if;--v_sessPartitions is not null
end if;
else
v_partitionList := null;
--bug fix while tuning start ravikiran
v_sessPartitions := BSM_3_2_9_ipdc_schpart2.obtainPartitionsForSession(v_session, 0, v_partitionList,
v_tempnwAreaNotAvailable, v_tempnwAreaNoBW, v_temppartitionExists, v_temppartitionUseful);

if(v_sessPartitions is not null) then
v_PartitionList := chPartitionList();
for v_partIndex in 1..v_sessPartitions.count loop
v_PartitionList.extend;

v_PartitionList(v_partIndex) := chPartition(-1,v_sessPartitions(v_partIndex).ppStartDate,
v_sessPartitions(v_partIndex).dpCode,v_sessPartitions(v_partIndex).nwAreaCode,v_sessPartitions(v_partIndex).subChannelID,
v_sessPartitions(v_partIndex).chPartitionNr,v_tempValue,v_tempValue,v_pid);--for 3.2.3 ravim
end loop;
--v_partitionList := null;
end if;--v_sessPartitions is not null

--bug fix while tuning end ravikiran
end if;
-- bug fix end -- ipdc_MAIN E055472 -- sudha

v_session.recurSessID := v_recurSession.recurSessID;
if(v_session.protectionprotocolenum is not null and
lower(i_addSession.protectionprotocolenum) = 'ipsecaut') then
--IPDC Release 3.0.1 <IPSec Seed> Subir --start
--v_session.ipSecKey := i_ipsecKeys(v_ipseckeyIndex);
v_session.ipSecencryptionseed := i_ipSecSeeds(v_ipSecSeedIndex);
v_session.ipSecauthseed := i_ipSecAuthSeeds(v_ipSecSeedIndex);--for release 3.1.1
elsif(v_session.protectionprotocolenum is not null and
lower(i_addSession.protectionprotocolenum) = 'ipsec') then

v_session.ipSecencryptionseed := i_ipSecSeeds(v_ipSecSeedIndex);
else
v_session.ipSecencryptionseed := null;
--IPDC Release 3.0.1 <IPSec Seed> Subir --end
end if;

-- If the Session is non overlapping, take the IPAddress passed, and dont
-- allocate a fresh IP address
if(v_isNonOverLapSess = 1) then
/*
BSM_3_2_9_ipdc_schedule.validate_sess_overlap(v_session, i_overlap_flag);

if(i_overlap_flag = 1) then
raise e_overlap_exception;
end if;
*/

BSM_3_2_9_ipdc_schedule_rec.createServiceSession(v_session,v_PartitionList,
-- pass the IP address and port
v_recSessionList(1).ipAddrSuffStart,
v_recSessionList(1).portStart,
i_forceCreation,
o_partitionExists,
o_partitionUseful,o_nwAreaNotAvailable,
o_nwAreaNoBW,
v_sessPartitions,
i_sessionSource);
else
/*
BSM_3_2_9_ipdc_schedule.validate_sess_overlap(v_session, i_overlap_flag);

if(i_overlap_flag = 1) then
raise e_overlap_exception;
end if;
*/
BSM_3_2_9_ipdc_schedule_rec.createServiceSession(v_session,v_PartitionList,
-1,-1,
i_forceCreation,
o_partitionExists,
o_partitionUseful,
o_nwAreaNotAvailable,
o_nwAreaNoBW,
v_sessPartitions,
i_sessionSource);
end if;
if(v_i = 1) then
--collect only the first session because targets for first session alone
--will be returned to GUI
v_firstSession := v_session;
end if;

v_ipSecSeedIndex := v_ipSecSeedIndex + 1;
--IPDC Release 3.0.1 <IPSec Seed> Subir --end
v_i := v_i + 1;
end loop; --for sessInstance in sessCur loop

v_tempRecSessionList := BSM_3_2_9_ipdc_schinsget.getSessionsByRecur(v_recurSession);

--if sessions created, then set the error info fields to null
o_nwAreaNotAvailable := null;
o_nwAreaNoBW := null;
o_sessStatusList := null;
o_sessPartitions := null;
end; --cursor sessCur is

if(v_i > 1) then
i_addSession := BSM_3_2_9_ipdc_schinsget.getSession(v_firstSession);
i_PartitionList := BSM_3_2_9_ipdc_schinsget.getPartitionsBySession(v_firstSession.cpCode,v_firstSession.sCode,
v_firstSession.opDate,v_firstSession.sessionNr);
o_sessionList := BSM_3_2_9_ipdc_schinsget.getSessionsByRecur(v_recurSession);
end if;

end if; --if(((v_nwAreaNoBW is not null and v_nwAreaNoBW.count != 0) or
end if; --if(sessPartitions is null or

exception when opday_not_in_weekdaylist then
raise_application_error(-20501, 'First operational day is not in the included weekdays');
when invalid_operationaldate then
raise_application_error(-20502, 'invalid operational date');
when invalid_provider then
raise_application_error(-20527, 'createRecurrence: invalid provider');
when invalid_nofIPAddr then
raise_application_error(-20530, 'createRecurrence: nof IPAddr exceeds System range');
when invalid_nofPort then
raise_application_error(-20531, 'createRecurrence: nof Port exceeds System range');
when dcs_not_available then
raise_application_error(-20533, 'createRecurrence: datacast server not available');
when sess_beyond_opday then
raise_application_error(-20535, 'createReccurence: session spans beyond (operationalday,starttime,duration): (' ||
to_char(v_currentOpDate,'YYYY-MM-DD') || ',' || v_StartTime || ',' || v_duration || ')');
when dcproc_not_avail then
raise_application_error(-20538, 'createRecurrence: datacast server doesnot have process for session');
when invalid_keylist then
raise_application_error(-20539, 'updateServiceSession: IPSecSeeds not provided for creating sessions');
when invalid_access_type_rec then
raise_application_error(-20547,'Create Reccurence : Overlaping Recurrent Sessions cannot have Default Accesstype');
when invalid_access_type then
raise_application_error(-20541,'Create Reccurence : Reccurent Sessions overlaps with Existing cannot have default AccessType');
when e_overlap_exception then
raise_application_error(-20743,'Create Reccurence : Sessions cannot overlap');
when e_invalid_pid_ex then
raise_application_error(sqlcode,'createServiceSession : Invalid PID range given for the Broadcastchannel.');
when others then
errMsg := substr(sqlerrm,1,200);
BSM_3_2_9_ipdc_common.throwError(sqlcode, errMsg, 'createRecurrence');

end createRecurrence;

--**************************************
-- deleteRecurrance
--**************************************
procedure deleteRecurrance
(
i_recurringSessionID in integer,
i_deleteSessions in integer
)as

v_currentDate date := null;
v_hour integer := null;
v_min integer := null;
v_sec integer := null;
v_currentTime integer := null;
begin

Select sysDate into v_currentDate from dual;
Select to_char(v_currentDate,'hh24') into v_hour from dual;
Select to_char(v_currentDate,'mi') into v_min from dual;
Select to_char(v_currentDate,'ss') into v_sec from dual;
v_currentTime := (v_hour * 60 * 60 )+ (v_min * 60) + v_sec;

if(i_deleteSessions = 0) then
for sess in
( Select * from ServiceSession
where RecurringSessionID = i_recurringSessionId
and isDeleted = 0
) loop
if((sess.OperationalDate = trunc(v_currentDate)) and
(sess.CastingTime <= v_currentTime) and
((sess.CastingTIme + sess.CastingDuration) >= v_currentTime)) then
raise on_going_session;
end if;
end loop;
Update RecurringSession set isDeleted = 1
where RecurringSessionID = i_recurringSessionID
and isDeleted = 0;
else
Update RecurringSession set isDeleted = 1
where RecurringSessionID = i_recurringSessionID
and isDeleted = 0;
end if;
exception
when on_going_session then
raise_application_error(-20541, 'deleteRecurringSession: Session Instances are on-going');
when others then
errMsg := substr(sqlerrm,1,200);
BSM_3_2_9_ipdc_common.throwError(sqlcode, errMsg, 'deleteRecurrence');

end deleteRecurrance;

END BSM_3_2_9_ipdc_schrec;
Re: pl/sql tuning [message #247950 is a reply to message #247942] Wed, 27 June 2007 11:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And you expect someone will have a look at hundred of non formatted lines?

Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Break your lines to max 80-100 characters when you format.
Please always post your Oracle version (4 decimals).

Forgot: number the lines.

Regards
Michel

[Updated on: Wed, 27 June 2007 11:04]

Report message to a moderator

Re: pl/sql tuning [message #247953 is a reply to message #247942] Wed, 27 June 2007 11:15 Go to previous messageGo to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
Hey Michel,

Just look at the for loops and if conditions and suggest
what can be used to replace them and increase performance.

Iam fed up with the performance.Please be patient and look at the code.

Ravi

Re: pl/sql tuning [message #247957 is a reply to message #247953] Wed, 27 June 2007 11:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Look at the links I posted, read them and post your procedure formatted.
Extract what is important.
Comment in details.
Explain...

Regards
Michel
Re: pl/sql tuning [message #247963 is a reply to message #247942] Wed, 27 June 2007 11:39 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
If YOU can't be bothered to format your post so it is readable why should we be bothered to look at it ?

Use the appropriate tags and only include the code where the performance issues are occuring.

To try and isolate these you can call a procedure with an autonomous transactions to write the start/end times of where you suspect the performance issues might be.

Try running explain plans etc etc.

We have NO clue what your data looks like, which is tightly linked to performance.

Try to do some work on your own rather than just cut and paste a procedure into a forum and expect Magical answers.

[Updated on: Wed, 27 June 2007 11:44]

Report message to a moderator

Re: pl/sql tuning [message #247973 is a reply to message #247942] Wed, 27 June 2007 12:34 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
1627 lines of code you posted. Seriously, what do you expect? What would you do if someone gave that to you and said it's slow, make it faster.
Re: pl/sql tuning [message #248014 is a reply to message #247942] Wed, 27 June 2007 15:34 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Post TKPROF.

Michael
Re: pl/sql tuning [message #248054 is a reply to message #248014] Wed, 27 June 2007 21:57 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Read this article on tuning PL/SQL if you are processing high volumes of data.

Ross Leishman
Previous Topic: How to call SQLLDR (sql*loader)from oracle stored procedure?
Next Topic: CRITICAL SELECT STATEMENT
Goto Forum:
  


Current Time: Sun Dec 11 04:23:47 CST 2016

Total time taken to generate the page: 0.15833 seconds