Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Posts
    16

    Unhappy Unanswered: stored procedure performance

    I have been running a stored procedure for about three consecutive days now. I am wondering why I am getting such bad performance from my stored procedure. Let me give you a breakdown of what it does:

    It is updating 2,658,452 rows (3 columns) in table_1. However it has to search 3 other tables to update the rows in table_1. There does exist a flag in table_1 that tells the programmer in which of the 3 other tables, the information exists, so with that, with every update of table_1, it still only has to search through one of the tables because it has a flag that tell it which table the information exists.

    The size of the 3 other tables are:
    Table_2: 1,062,277 rows
    Table_3: 704,584 rows
    Table_4: 891,591 rows

    Given the fact that in order to update all of the rows in table_1 it needs to search table_2, table_3, and table_4, is it realistic for my stored procedure to require 3 days of execution.

    If not, what are some self-tuning ideas I can use on my oracle 9i database to make the execution of my stored procedure faster?

    Thansk for any suggestions.
    Jason B. Simms

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Can the 3 other lookup tables be IOT tables ???
    Are the tables properly indexed ???
    Are you using 1 trx for the 2.6 mil updates ???
    Are you using a LARGE rollback segment for the updates???

    Can the updated table be set to nologging ???
    etc ...

    Gregg

  3. #3
    Join Date
    Jan 2004
    Posts
    16
    Originally posted by gbrabham
    Can the 3 other lookup tables be IOT tables ???
    Are the tables properly indexed ???
    Are you using 1 trx for the 2.6 mil updates ???
    Are you using a LARGE rollback segment for the updates???

    Can the updated table be set to nologging ???
    etc ...

    Gregg

    I have to admit that I am somewhat of a beginner with oracle. I do know that the tables are properly indexed for this procedure. I am not sure if the other tables can be IOT tables.

    I am not sure what "trx" means, not sure if it is using a large rollback segment fro the updates, and not sure if the updated table can be set to nologging.

    How would I be able to find out this information
    Jason B. Simms

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: stored procedure performance

    Can you show us the code? If it looks anything like this it is likely to be very slow:
    PHP Code:
    ...
    FOR 
    table1_row IN table1_cur LOOP
      
    IF table1_row.flag 2 THEN
        OPEN table2_cur
    ;
        ...
      
    ELSIF table1_row.flag 3 THEN
        OPEN table3_cur
    ;
        ...
      
    ELSIF table1_row.flag 4 THEN
        OPEN table4_cur
    ;
        ...
      
    END IF;
       ...
      
    UPDATE table1 ...;
    END LOOP
    Also, run TRACE/TKPROF to see what the slowest queries are.

  5. #5
    Join Date
    May 2003
    Posts
    369

    temp tables versus cursors

    Looks to appear you may be using cursors to do the processing of your tables in Oracle stored procedure. Instead, I would advocate that you use temp tables in your stored procedures for better performance. Also, what is your concurrency and locking status of your Oracle database like during execution of these stored procs? If you have too many things running at the same time then performance will take a hit. What about your operating system configuration? If your Oracle is running on UNIX you may need to look at how your storage layout is configured with these tablespaces that are linked to the stored procs. Hope this helps

    Scott

  6. #6
    Join Date
    Jan 2004
    Posts
    16
    AS

    CURSOR csr_master IS
    Select * from table_1 h where alt is null;
    counter NUMBER;
    v_org_rec csr_master%ROWTYPE;

    Begin


    counter := 0;
    open csr_master;

    LOOP
    FETCH csr_master INTO v_org_rec;
    EXIT WHEN csr_master%NOTFOUND;

    IF(v_org_rec.center = 'P') Then
    counter := 0;
    For i IN (Select fsc,niin,fy,fq,alt,plt,boqty from table_2 where
    niin = v_org_rec.niin and fsc = v_org_rec.fsc and fy = v_org_rec.fy
    and fq = v_org_rec.fq) LOOP
    IF(counter = 0) Then
    UPDATE table_1
    set alt = i.alt, plt = i.plt, boqty = i.boqty
    where niin = v_org_rec.niin and fsc = v_org_rec.fsc and fy = v_org_rec.fy and fq = v_org_rec.fq;
    END IF;
    counter := counter + 1;
    END LOOP;

    ELSIF(v_org_rec.center = 'C') Then
    counter := 0;
    For i IN (Select fsc,niin,fy,fq,alt,plt,boqty from table_3 where
    niin = v_org_rec.niin and fsc = v_org_rec.fsc and fy = v_org_rec.fy
    and fq = v_org_rec.fq) LOOP
    IF(counter = 0) Then
    UPDATE table_1
    set alt = i.alt, plt = i.plt, boqty = i.boqty
    where niin = v_org_rec.niin and fsc = v_org_rec.fsc and fy = v_org_rec.fy and fq = v_org_rec.fq;
    END IF;
    counter := counter + 1;
    END LOOP;

    ELSIF(v_org_rec.center = 'R') Then
    counter := 0;
    For i IN (Select fsc,niin,fy,fq,alt,plt,boqty from table_4 where
    niin = v_org_rec.niin and fsc = v_org_rec.fsc and fy = v_org_rec.fy
    and fq = v_org_rec.fq) LOOP
    IF(counter = 0) Then
    UPDATE table_1
    set alt = i.alt, plt = i.plt, boqty = i.boqty
    where niin = v_org_rec.niin and fsc = v_org_rec.fsc and fy = v_org_rec.fy and fq = v_org_rec.fq;
    END IF;
    counter := counter + 1;
    END LOOP;

    END IF;

    COMMIT;
    END LOOP;

    close csr_master;

    END;
    Jason B. Simms

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    As I predicted. Presumably those selects from tables 2,3 and 4 only find 1 row each time? Otherwise they are wasting even more time by continuing to fetch records and ignore them.

    It could be rewritten something like this:

    PHP Code:
    BEGIN
      UPDATE table_1 t1
      set alt
    pltboqty = (select t2.altt2.pltt2.i.boqty
                               from table2 t2
                              where t2
    .niin t1.niin 
                                
    and t2.fsc t1.fsc
                                
    and t2.fy t1.fy
                            
    )
      
    where alt is null
        
    and center 'P'
        
    and exists
            
    (select 1
               from table2 t2
              where t2
    .niin t1.niin 
                
    and t2.fsc t1.fsc
                
    and t2.fy t1.fy
            
    );
      
    UPDATE table_1 t1
      set alt
    pltboqty = (select t3.altt3.pltt3.i.boqty
                               from table3 t3
                              where t3
    .niin t1.niin 
                                
    and t3.fsc t1.fsc
                                
    and t3.fy t1.fy
                            
    )
      
    where alt is null
        
    and center 'C'
        
    and exists
            
    (select 1
               from table3 t3
              where t3
    .niin t1.niin 
                
    and t3.fsc t1.fsc
                
    and t3.fy t1.fy
            
    );
      
    UPDATE table_1 t1
      set alt
    pltboqty = (select t4.altt4.pltt4.i.boqty
                               from table4 t4
                              where t4
    .niin t1.niin 
                                
    and t4.fsc t1.fsc
                                
    and t4.fy t1.fy
                            
    )
      
    where alt is null
        
    and center 'R'
        
    and exists
            
    (select 1
               from table4 t4
              where t4
    .niin t1.niin 
                
    and t4.fsc t1.fsc
                
    and t4.fy t1.fy
            
    );
      
    commit;
    END
    Last edited by andrewst; 02-23-04 at 18:19.

  8. #8
    Join Date
    Jan 2004
    Posts
    16
    Thank you. I really appreciated your help on this one. It worked out just fine.
    Jason B. Simms

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •