-
Question to Imporove PL/SQL Performance
Hello Everyone, New to the forum. siginig in for the first time. Hoping to share and learn for gurus on the forum. Thanks. I am newbie to pl/sql programming and facing a hard time to imporve the performance. id column have indexes.
Here is the pl/sql procedure structure I wrote :
declare
cursor c1 select * from table1;
begin
for c1_rec in c1 loop
begin
select * from table2 where id = c1_rec.id ;
update table2 set where id = c1_rec.id ;
commit ;
exception
when no_data_found then
insert into table2 values ( ) ;
end ;
begin
select * from table3 where id = c1_rec.id ;
update table3 set where id = c1_rec.id ;
commit ;
exception
when no_data_found then
insert into table3 values ( ) ;
end ;
end loop;
exception
end ;
/
How should I increase the performance ?. It processes around 30 million rows in 10 hrs. Tables are quite big.
Thanks,
fossil
-
Yuo don't need a:
select * from table2 (or 3)
if you are not doing any computations based on c1_rec of table 1.
-
Hello and welcome .
I can't see the exact texts of your updates, but I think you should use SQL for this kind of task. PL/SQL processing row by row is slow.
Ales The whole difference between a little boy and an adult man is the price of toys
-
hi, try using BULK COLLECT in all ur Select stmts.
Cheers!
Cheers!
OraKid.
-
well, I dont think the SELECT here is used to fetch data, rather to test for data that's why the exception defined?
looks like you dont really need PL/SQL to do this as Ales mentioned
-
Thanks,
LKBrwn_DBA,
Ales,
Balajiyes, and Pando for your replies.
I apologize for not writing full code as its too long.
There is a lot of logical processing code before the "Update's".
The basic outline is :
If the record is found from "select * from table2", then it does the logical processing and then updates else it goes to the exception and insert's the record. The same thing is with table3. This procedure is repeated until the loop stops fetching from table1 (I refer table1 as transaction table)
Considering the above, which method is possible in the above situation to make it faster ?.
Thanks to all of you.
fossil
-
Re: Question to Imporove PL/SQL Performance
Originally posted by fossil
It processes around 30 million rows in 10 hrs.
If those are 30 mio updates/inserts then it could be a lot worse!
Have a look at the commits - reducing them could help. You could do it just before the END LOOP statement and only commit after every (say) 1'000 rows from table1 - set up a counter and
IF MOD(counter, 1000) = 0 THEN COMMIT; END IF;
- in this case you will need one outside the loop too.
The possible number of changes before the commit depends on the size of your roll-back segments.
You will probably also need to commit if you hit the final EXCEPTION.
(Your code as shown could miss commiting the last insert - I guess that was an error of editing rather than logic.)
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
Thanks DaPi, as a matter of fact, the code commits at every 1000 records. Couple of other very basic measures which I took are :
data and index are on separate tablespaces.
analyze is done on the table and indexes
explain plan shows that the select is using indexes on id fields
enough undo and temp tablespace
hit ratios are at very good.
I think as everyone has pointed out, I may not require to use pl/sql then what should I use ?, is my pl/sql approach wrong ?.
thanks,
fossil
-
I suspect that the fact that the code is so big that you did not post it means that simple SQL will not cope. If you have any IF statements then you will have to use your PL/SQL cursor.
It's time to use the ultimate tuning tool: stand in front of the disks and watch the lights flash. (I'm serious!).
Data and Indexes in different tablespaces is good - but are they on different physical units? WHERE ARE THE LOG FILES AND ARCHIVE-LOGS?
I've been spoilt, I had a monitoring tool since day one (Quest Spotlight - paid for itself in two weeks) so I don't know exactly which v$ views to look at. Perhaps someone can give us code for checking disk contention? ("8i Designing & Tuning for Performance" chaps 20 & 21 might be start - but it's the end of a long day . . . .).
Last edited by DaPi; 12-10-2002 at 11:12 AM.
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
(Brain failure . . . ) utl_stat is a place to start:
While the job is running:
@ORACLE_HOME/rdbms/admin/utlbstat.sql
. . wait 10 minutes . .
@ORACLE_HOME/rdbms/admin/utlestat.sql
the scripts need editing to include passwords.
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|