OraFAQ Forum: SQL & PL/SQL » Question on Performance of a Stored Package.

Home » SQL & PL/SQL » SQL & PL/SQL » Question on Performance of a Stored Package.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Question on Performance of a Stored Package. [message #38394]

Tue, 16 April 2002 08:57

Vidyalakshmi Iyer
Messages: 13
Registered: April 2002

Junior Member

Hi,
We have a stored package which opens a cursor for update (about 60,000 - 75,000 records )
then a lot of processing is done on these records.
Each record is fetched inside a loop and then
They are rated taking the rates from various table. for which cursors are opened and closed inside the loop.
these tables from where these rates are taken are also pretty huge about 80,000-90,000 records.
at the end of the loop the table is updated with processed fields with the where current of clause.

Now I have a problem that this entire process of processing about 75000 records takes me about 6-7 hours.
This is surely not what we want.

We have only 2 processors.
Oracle 9i
Sun Solaris Unix - 5.8
What are the ways you suggest in which we could improve the speed.

Thanking you,
Regards,
Vidya.

Report message to a moderator

Re: Question on Performance of a Stored Package. [message #38395 is a reply to message #38394]

Tue, 16 April 2002 09:14

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

we have implemented certain batch jobs, which handle a few million records. based on that, i would recomend the following.
1. use pl/sql tables for lookup purpose in the procedure.
2. commit data in-frequently.
3.if possible move the concerned tablespaces to a different contention free disk.
4. Avoid using any like or % operators in the cursor definition.
5. make use of the unix pipes so that many copies of the same procedure can be run in parallel.
6. in case of 9i turn the logging options for the tablespace off (oracle does not recomend it,though it is faster for updates) so no logs are generated.
7. Analyze the disk RPM, available swap (min is 3 x ram) and other temporary memory management. ( if you use sorts,order by etc, increase the temp segment...etc).

[Updated on: Tue, 12 May 2009 11:02]

Report message to a moderator

Re: Question on Performance of a Stored Package. [message #38402 is a reply to message #38395]

Tue, 16 April 2002 17:40

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Whatever you do, do NOT commit more frequently - this will only slow down your transaction.

Report message to a moderator

Re: Question on Performance of a Stored Package. [message #38422 is a reply to message #38395]

Wed, 17 April 2002 06:52

Vidyalakshmi Iyer
Messages: 13
Registered: April 2002

Junior Member

Hi,
Thanks Mahesh For your suggestions.. I implemented the part which commits after 2000 records and it reduced the time from 7 hours to 4 hours ..
we are still working on it..have to get it to do in about 2 hours..or less..if possible..
i am still working on your other suggestions..
will keep you posted.

Thanks again,
Vidya

Report message to a moderator

Re: Question on Performance of a Stored Package. [message #38427 is a reply to message #38395]

Wed, 17 April 2002 09:50

Vidyalakshmi Iyer
Messages: 13
Registered: April 2002

Junior Member

Hi ,
We did not have any like or % operator in our cursor definition but we do have substr operator in our cursor definition which I beleive is slow.. but we cannot avoid Is there any other way to do same thing that substr does but faster..?
Regards,
Vidya

Report message to a moderator

Re: Question on Performance of a Stored Package. [message #38432 is a reply to message #38395]

Wed, 17 April 2002 10:55

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

hi.
as per your other posting, it seems that you are OPEN the cursor definition with substr many times.
try to avoid that. make use of pl/sql tables.
something like this.
------------------------------------
declare
define the cursor C1 with substr
define the table.
define the variables(indexes) for pl/sql tables.
begin
for record in C1 loop
load the values from the cursor(resultset) into the plsql tables.
....
....
do your business logic.
whenever u want to refer the resultSet refer it from the array..
end;
------------------------------------------------
PL/SQL tables are INDEXed BY BINARY_INTEGERs, therefore, the index ranges from -2147483647 to +2147483647. The theoretical maximum number of rows
is 4 Gigabytes. Remember, this all comes out of the PGA. and ur SGA is RELATIVELY FREE!!!.

-------------------------------------------------
and, did u anaylze the indexes? try rebuilding them.

Report message to a moderator

Re: Question on Performance of a Stored Package. [message #38437 is a reply to message #38395]

Wed, 17 April 2002 14:39

Vidyalakshmi Iyer
Messages: 13
Registered: April 2002

Junior Member

Hi Mahesh,
Thanks so much for your time.
but this is the problem I have...
the cursor definition goes like this..
Cursor v is
Select name from emp
where
id = Substr( :vid, 1, Length(id) )
order by id desc ;
and the value of vid changes changes in
every iteration of the loop
this cursor fetches many records but we need just the first one that it fetches..that is why the order by.
the table emp is huge ..about 50,000-60,000 records.
yes, we do a full table scan in this cursor.
so if i use a pl/sql table I will have to load the entire table emp (name & id fields only) in the pl/sql table and then for each value of vid go through the pl/sql table instead of emp table.. will that be faster?
if yes,I shall go ahead and do that.
Regards,
Vidya.

Report message to a moderator

Re: Question on Performance of a Stored Package. [message #38449 is a reply to message #38395]

Thu, 18 April 2002 05:40

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

scanning through the pl/sql table is definetly faster than a database table.
i am not very clear with your requirements.

as per your posting,
******************************************************
this cursor fetches many records but we need just the first one that it fetches..that is why the order by.
******************************************************
so why dont u use the rownum < 2 ??

something like
Cursor v is
Select name from emp
where
id = Substr( :vid, 1, Length(id) )
and rownum < 2;

Report message to a moderator

Re: Question on Performance of a Stored Package. [message #38454 is a reply to message #38395]

Thu, 18 April 2002 10:44

Lonnie C. Spears
Messages: 2
Registered: March 2002

Junior Member

Depending on your need, and the version of Oracle you are running. You might try a function based index on the id field using your substring function, parameterize your cursor or use an 'where exists' statement in the select statment.

Report message to a moderator

Re: Question on Performance of a Stored Package. [message #38459 is a reply to message #38395]

Thu, 18 April 2002 15:01

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

then,
how about writing a function that returns only one 'best matched record'??
and use that function in the cursor definition? that may avoid the frequent looping...inside the procedure.
*************************
SQL> select * from oo;

ID NAME
---------- ----------
43 aaa
432 bbb
4325 ccc

SQL> get f1
1 create or replace function f1
2 (vid number)
3 return varchar2 is
4 ret_val varchar2(10);
5 cursor c1 is Select name into ret_val from oo
6 where
7 id = Substr( vid,1,Length(id) )
8 order by id desc;
9 begin
10 for mag in c1 loop
11 dbms_output.put_line('names are '||mag.name);
12 ret_val:=mag.name;
13 exit;
14 end loop;
15 return ret_val;
16* end;
SQL> /

Function created.

SQL> select distinct(f1(43257)) from oo;

(F1(43257))
----------------------------------------------------------------------------------------------------
ccc

SQL>

Report message to a moderator

Re: Question on Performance of a Stored Package. [message #38496 is a reply to message #38395]

Mon, 22 April 2002 23:33

jack
Messages: 123
Registered: September 2000

Senior Member

I think I understand what you are trying to do. Here is an approach to think about.

Load the emp table into a index-by table, using the emp.id as the index. index-by tables are sparse so you will only use memory for indexes that are created.

To get a best match: start with the full vid and see if an entry in the index-by table exists at that vid (use the vid as in index). If it does not, remove the end character from the vid and see if that vid index exists. Repeat until the vid is 0 length or found.

Now, I *THINK* this should be more effective then the cursor approach. You'll need one full table scan to load the emp table into the index-by table. The lookups to get a vid match will be *very fast* since the vid is a direct index into the table, although there will be a few wasted lookups until a best match is found.

Report message to a moderator

Previous Topic:	compiling pl/sql procedures in sqlplus...
Next Topic:	Simple SQL Question

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 01:31:27 CDT 2024