Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a stored procedure that includes (among many other things) a dynamic query with a LOOP and a FETCH. The fetch has to potential retrieve a large amount of data as one column contains spatial information.

The problem I am having is that if this procedure is run by itself, everything runs fine and performance is satisfactory, but if the procedure is asked to run twice (in parallel from different sessions, ultimately from front end web application) performance degrades enormously.

Using dbms_utility.get_time() I have been able to track the poor performance down to a single line of code:

fetch fcursor into l_id_val, l_feat_label, l_strip_desc, l_bar_desc, l_geom;

where the first four columns are plain text, the 5th is the geometry (lots of data).

open fcursor for sdo_stmt using minx, miny, maxx, maxy;
LOOP
  ss2:=dbms_utility.get_time();      
  fetch fcursor into l_id_val, l_feat_label, l_strip_desc, l_bar_desc, l_geom;
  dbms_output.put_line('fetch: ' || To_Char(dbms_utility.get_time()-ss2) || ' id: ' || l_id_val || ', Table: ' || currt_table);    

  EXIT when fcursor%NOTFOUND;
  ...
  ...
END LOOP;

Below is sample of the (filtered) output of the dbms_output.put_line. It shows a clean run (only executing once) and a run when the same procedure was running in another session. You can see that in the first run the fetch took between 300 and 600 (3 to 6 seconds). In the second run it starts out OK, but then blows out to 30 and even 50 seconds in one instance.

    Line 81: fetch: 308 id: , Table: BIO_AREA
Line 188: fetch: 305 id: , Table: BIO_AREA
Line 303: fetch: 306 id: , Table: BIO_AREA
Line 391: fetch: 310 id: , Table: BIO_AREA
Line 473: fetch: 311 id: , Table: BIO_AREA
Line 537: fetch: 312 id: , Table: BIO_AREA
Line 599: fetch: 602 id: 3078926, Table: BIO_AREA
Line 661: fetch: 601 id: 3078926, Table: BIO_AREA
Line 725: fetch: 599 id: 3078926, Table: BIO_AREA
Line 787: fetch: 302 id: , Table: BIO_AREA
Line 847: fetch: 603 id: 3078919, Table: BIO_AREA
Line 909: fetch: 310 id: , Table: BIO_AREA
Line 968: fetch: 312 id: , Table: BIO_AREA
Line 1029: fetch: 313 id: , Table: BIO_AREA

Line 81: fetch: 350 id: , Table: BIO_AREA
Line 188: fetch: 381 id: , Table: BIO_AREA
Line 303: fetch: 439 id: , Table: BIO_AREA
Line 391: fetch: 471 id: , Table: BIO_AREA
Line 473: fetch: 430 id: , Table: BIO_AREA
Line 537: fetch: 449 id: , Table: BIO_AREA
Line 599: fetch: 1016 id: 3078926, Table: BIO_AREA
Line 661: fetch: 2186 id: 3078926, Table: BIO_AREA
Line 725: fetch: 4963 id: 3078926, Table: BIO_AREA
Line 787: fetch: 3246 id: , Table: BIO_AREA
Line 847: fetch: 869 id: 3078919, Table: BIO_AREA
Line 909: fetch: 3304 id: , Table: BIO_AREA
Line 968: fetch: 3164 id: , Table: BIO_AREA
Line 1029: fetch: 3264 id: , Table: BIO_AREA

Unfortunately I am new to Oracle/PL SQL (4 weeks) and I am reading up on things like Explicit vs Implicit cursors etc, but so far I have been able to work out what is going on. It feels like some kind of contention or locking issue, but in reality I have no clue. Hopefully someone out there has seen something similar before.

As requested, here's the full code for the loop. I would like to understand how this might have a bearing on it, because the timings I have done are explicitly around the single FETCH line. Unfortunately I have inherited this so can't explain it in full.

open fcursor for sdo_stmt using minx, miny, maxx, maxy;
LOOP
  ss1:=dbms_utility.get_cpu_time();  
  ss2:=dbms_utility.get_time();      
  fetch fcursor into l_id_val, l_feat_label, l_strip_desc, l_bar_desc, l_geom;
  dbms_output.put_line('fetch: ' || To_Char(dbms_utility.get_time()-ss2) || ' id: ' || l_id_val || ', Table: ' || currt_table);    

  EXIT when fcursor%NOTFOUND;


  open int_cursor for select * from table(window_intercepts(minx, miny, maxx, maxy,l_geom)); -- there can be multiple intercepts for each feature
  LOOP 
    fetch int_cursor into l_start, l_end;
    EXIT when int_cursor%NOTFOUND;
    ewp_result_collection.extend(1);
    j:= j + 1;
    --dbms_output.put_line('strip desc:' || l_strip_desc || ', start:' || l_start || ', end:' || l_end);
    ewp_result_collection(j) := ewp_result_temp(ewp_feature_table(idx).segment_id,
        ewp_feature_table(idx).seq_id, 
        ewp_feature_table(idx).feature_id, 
        l_feat_label,
         l_strip_desc, 
        l_bar_desc,
        l_start, 
        l_end);
  END LOOP;
  close int_cursor;

  --
  -- get the collection of linked species for this feature type and store in the species results table
  --
  if upper(substr(ewp_feature_table(idx).fname,1,4)) = 'EWP_' and upper(substr(ewp_feature_table(idx).fname,1,7)) <> 'EWP_OBJ' and upper(substr(ewp_feature_table(idx).fname,1,9)) <> 'EWP_MINOR' and upper(substr(ewp_feature_table(idx).fname,1,6)) <> 'EWP_CU'  then
    spec_stmt := 'select species_id from ewp_species where ewp_id = :id' ;
  --dbms_output.put_line(spec_stmt);
    execute immediate spec_stmt bulk collect into species_collection using l_id_val;
    if species_collection.COUNT > 0 then
    for k in 1 .. species_collection.COUNT loop
      insert into ewp_species_result 
      (select cov_id, species_collection(k), style_in from dual where not exists (select null from ewp_species_result where coverage_id = cov_id and style_id = style_in and species_id = species_collection(k)));
    end loop;
    end if;
  end if;
END LOOP;
close fcursor;
share|improve this question
    
What else are you doing in the loop? If you're doing an update, delete or insert it will be relevant, so edit your question with exact details. –  Phil May 16 '13 at 6:20
    
use bulk fetch to avoid context switches between sql-pl/sql engines. –  haki May 16 '13 at 7:19
2  
Please post the results of an sql trace for the sessions including the wait information - this will help us see what's different. That said, I'm pretty sure you can get rid of most/all of the PL/SQL here which may solve the issue for you anyway. –  Chris Saxon May 16 '13 at 11:43
    
@ChrisSaxon: I'll have to find out how to do that. I have had my DBA looking at this too and the Oracle analysis has said: "Action Use bigger fetch arrays while fetching results from the SELECT statement with SQL_ID ..." for the query, but I haven't managed to found out how to change that, or even if it's actually applicable in this case. I did find a reference to SQL Plus though. –  Jenakai May 16 '13 at 22:45
1  
couple of things, the last insert statement seems strage - you query not exists and returning null from the inner query - isn't it always evaluates to true ? second , you said that theres performance impact only when running from two sessions - have you checked locks ? select * from v$session_event where sid in (<the two running session's sid> ). look for TM/TX events. –  haki May 17 '13 at 7:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.