0

My application hits database to load different sections in UI homepage. All sections are dependent on one input. So I created a proc that returns all data in different cursors. Now count of cursor return is around 25.

My question is: What is drawback of returning multiple cursors, though each do not have data more than a hundred rows (some also has one or two rows), from oracle pl/sql procedure? Cost of fetching for each cursor is very low, so they are quite fast.

Will it be better if I split proc into two and make 2 separate db hits to fetch data?

Ollie
  • 17,058
  • 7
  • 48
  • 59
Nitesh
  • 490
  • 3
  • 6
  • 15
  • 2
    I'd expect that any procedure that had 25 parameters would be rather poorly designed. Pieces of code should be small enough to do one thing well. I'd generally prefer a package with 25 separate functions each returning a cursor (assuming all you are doing is opening the cursor). But I'd also be a bit concerned about why one page needed 25 cursors opened. I'd expect that would produce a rather unfriendly UI. – Justin Cave Sep 25 '15 at 08:13
  • The advantage to your current system, is you have already written it, and you are happy with performance. But the advantage to using implicit cursors, is generally speaking, they are faster. This is an interesting post on the subject: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1173215100346519786 – Robert Dupuy Sep 25 '15 at 08:25
  • @RobertDupuy Link doesn't answer or is relevant here. I want to check how multiple output cursors result in performance degradation, if any. and these are not implicit cursors. – Nitesh Sep 28 '15 at 08:09
  • @JustinCave. I agree with you Justin for the poor design. Can you throw some light on performance part please – Nitesh Sep 28 '15 at 08:10
  • Nitesh, that's a style of programming, to use many explicit cursors, and not one that is necessarily the best design or the best performing. I'm sorry you didn't find that link of benefit. Not having seen the code, I took the approach to discuss things on a rather general level. Best of luck to you. – Robert Dupuy Sep 28 '15 at 08:32
  • Thanks @RobertDupuy. As of now I am going ahead with splitting proc into two procedures grouping relevant cursors. I would expect (I expect because I found no relevant answers as of now on any forums) it to perform better when multiple users use application simultaneously. – Nitesh Sep 29 '15 at 10:41

0 Answers0