DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 30

Thread: Question to Imporove PL/SQL Performance

  1. #1
    Join Date
    Dec 2002
    Posts
    36

    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

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492
    Yuo don't need a:

    select * from table2 (or 3)

    if you are not doing any computations based on c1_rec of table 1.

  3. #3
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    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

  4. #4
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    hi, try using BULK COLLECT in all ur Select stmts.
    Cheers!
    Cheers!
    OraKid.

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  6. #6
    Join Date
    Dec 2002
    Posts
    36
    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

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

    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

  8. #8
    Join Date
    Dec 2002
    Posts
    36
    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

  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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

  10. #10
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    (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
  •  


Click Here to Expand Forum to Full Width