Home » SQL & PL/SQL » SQL & PL/SQL » forall for performance
forall for performance [message #248968] Tue, 03 July 2007 00:11 Go to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
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;

How do i change this to forall loop?i_PartitionList is a list of an object.

Ravi

Re: forall for performance [message #248969 is a reply to message #248968] Tue, 03 July 2007 00:21 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Forall is not something you can use without insert or update. Forall collects multiple rows before sending them to the database, so it does DML in batches instead of row by row.
It does not work on pure pl/sql loops.
Re: forall for performance [message #248973 is a reply to message #248968] Tue, 03 July 2007 00:29 Go to previous messageGo to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member

(i_addSession in out servSession,

i_PartitionList in out chPartitionList)


create or replace type servSession as object
(
cpCode varchar2(Cool,
sCode varchar2(Cool,
opDate date,
sessionNr integer,
recurSessID integer,
descr varchar2(200),
ipAddrSuffStart integer,
portStart integer,
ProtectionprotocolEnum varchar2(Cool,
--ipSecSeed varchar2(32),--changes for 3.1 release
ipSecAuthSeed varchar2(32),
ipSecEncryptionSeed varchar2(32),
--SRTPEncryptorCode varchar2(16),--changes for 3.1 release
ipeMgmtStatus varchar2(Cool,
ipeMgmtIds varchar2(500),
NOfIPERetries number,-- 3.1 release
sessionProfileCode number,
castingTime integer,
castingDuration integer,
bitRate integer,
totalPrice number(16,6),
schValStatus varchar2(Cool,
schValInfo varchar2(200),
nofRecvPackMin integer,
nofRecvPackAvg integer,
nofRecvPackMax integer,
nofSentPackMin integer,
nofSentPackAvg integer,
nofSentPackMax integer,
ERRORCORRECTIONPERCENTAGE NUMBER(2),
PriorityNumber NUMBER(2),
--for 3.2.2 end -----
isDeleted char(1),
modifDate date,
modifUser varchar2(20)
)


create or replace type chPartition as object
(
uniqueID integer,
ppStartDate date,
dpCode varchar2(Cool,
nwAreaCode varchar2(Cool,
subChannelID integer,
chPartitionNr integer,
partitionExists integer,
partitionUseful integer,
PID integer
)


create or replace type chPartitionList as varray(1000) of chPartition;

Re: forall for performance [message #248974 is a reply to message #248968] Tue, 03 July 2007 00:30 Go to previous messageGo to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
My package has all for loops like this which is causing degrade in performance. How do i increase performance?
Re: forall for performance [message #248977 is a reply to message #248968] Tue, 03 July 2007 00:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Syntax of FORALL statement is:
FORALL index_name IN bounds_clause sql_statement [SAVE EXCEPTIONS];

The only possibility after the "bounds_clause" is "sql_statement" which is (from doc FORALL Statement):
Quote:
A static, such as UPDATE or DELETE, or dynamic (EXECUTE IMMEDIATE) DML statement that references collection elements in the VALUES or WHERE clauses.

INSERT is missing there but is listed below:
Quote:
The INSERT, UPDATE, or DELETE statement must reference at least one collection. For example, a FORALL statement that inserts a set of constant values in a loop raises an exception.


Regards
Michel
Re: forall for performance [message #248978 is a reply to message #248973] Tue, 03 July 2007 00:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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).

Regards
Michel
Re: forall for performance [message #248981 is a reply to message #248968] Tue, 03 July 2007 00:37 Go to previous messageGo to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
Please suggest a way to increase the performance
Re: forall for performance [message #248986 is a reply to message #248981] Tue, 03 July 2007 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please follow the guidelines.

Regards
Michel
Re: forall for performance [message #248992 is a reply to message #248968] Tue, 03 July 2007 01:05 Go to previous messageGo to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
what guidelines? Do you have the answer first?
Re: forall for performance [message #248994 is a reply to message #248992] Tue, 03 July 2007 01:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The links I posted (including format your post)
Answer will come AFTER.
FIRST you follow the guidelines
THEN we help you.

Regards
Michel
Re: forall for performance [message #249000 is a reply to message #248968] Tue, 03 July 2007 01:19 Go to previous messageGo to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
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;
Re: forall for performance [message #249001 is a reply to message #248968] Tue, 03 July 2007 01:20 Go to previous messageGo to next message
freethinking
Messages: 10
Registered: November 2006
Junior Member
I think your script has no problem , it must run faster ,
you can test it and eliminate source you don't show here
Re: forall for performance [message #249007 is a reply to message #248968] Tue, 03 July 2007 01:26 Go to previous messageGo to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
Hi Michel,

This is the loop

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;

Ravi
Re: forall for performance [message #249008 is a reply to message #248968] Tue, 03 July 2007 01:27 Go to previous messageGo to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
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;
Re: forall for performance [message #249009 is a reply to message #248968] Tue, 03 July 2007 01:28 Go to previous messageGo to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
I know it doesnt have any error. I want improvement in performance
Re: forall for performance [message #249041 is a reply to message #248968] Tue, 03 July 2007 02:31 Go to previous messageGo to next message
freethinking
Messages: 10
Registered: November 2006
Junior Member
oh , maybe <<BSM_3_2_9_ipdc_schupd.validateSessionPid
(i_addSession,i_PartitionList)=0>>
is bottleneck,
I do not think you can use forall in this situation,but I am not a expert Smile
maybe you can rewrite BSM_3_2_9_ipdc_schupd.validateSessionPid and store your collection into a globle temporery table , so you can use SQL to check
Re: forall for performance [message #249097 is a reply to message #248968] Tue, 03 July 2007 06:40 Go to previous message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Why do you refuse to use Code tags?

If you want performance help, post the explain plan and TKPROF results.
Previous Topic: Optimizing help appreciated
Next Topic: subinventories that have no locators
Goto Forum:
  


Current Time: Sun Dec 04 00:51:37 CST 2016

Total time taken to generate the page: 0.04246 seconds