Home » SQL & PL/SQL » SQL & PL/SQL » Code not waiting for REFCURSOR (Oracle 12c)
Code not waiting for REFCURSOR [message #685752] Thu, 17 March 2022 04:28 Go to next message
IanC
Messages: 4
Registered: March 2022
Junior Member
This is my first post on here so hello everybody! I'm fairly new to PL/SQL and come from an MS background so hopefully so hopefully this is a simple answer to something I'm struggling with.

I've setup a reporting structure so our Crystal reports can call stored procedures for the data and it's working quite well; each sproc effectively has three lines of codes, the first to create an entry in a logging table, the second to open a refcursor and the third to update the entry in the logging table with the finish time. The problem I'm getting is that although the query for the refcursor can sometimes take a while to run, the code seems to skip straight over the refcursor instantly and go straight onto the final update so according to my logs each report is only taking a split second to run.

I guess this is probably something to do with the refcursor as it probably just acts as a pointer to the data but I need the code to wait until the query has run before moving on to the next line of code; is it possible to do this?
Re: Code not waiting for REFCURSOR [message #685754 is a reply to message #685752] Thu, 17 March 2022 10:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68220
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As stated in the documentation:

Quote:
The OPEN FOR statement associates a cursor variable with a query, allocates database resources to process the query, identifies the result set, and positions the cursor before the first row of the result set.

So, generally, the biggest part of the time Oracle takes to open a cursor is to determine the first row which can be instantaneous or take long depending on the query text and the execution path the optimizer chooses.
If you want to be sure that all the result set is retrieved at this time (I think this what you meant with "the query has run") you have to add an ORDER BY clause which constrains Oracle to get all rows (and order them) to know which one is the first one.
Be careful that this means that Oracle has to copy all rows in your session sort area and may consume memory and/or temp disk space, and, in the later case, leads to I/Os.

In the end, the question is not correctly set in Oracle environment, there is no thing like "query run time": you open a cursor for a query, execute the query (identify the result set), fetch each row of the result set (or part of it), one by one or, better, by batch/array, and, in the end, close the cursor (release the resources). The time between open and close (which can be seen as the query run time) depends on what you do with each row after each fetch.

Re: Code not waiting for REFCURSOR [message #685755 is a reply to message #685754] Thu, 17 March 2022 11:09 Go to previous messageGo to next message
IanC
Messages: 4
Registered: March 2022
Junior Member
Thanks for your reply Michel, I suspect ordering the query as you suggest may work well for a lot of our reports so I will experiment with it shortly and see if it works. I will have to be mindful of affecting overall performance and watch out for I/O's, etc.

Some of our report's however are quite big with the sorting and grouping being done on the Crystal side after the data has been received and I suspect these could be trouble. Will have to see how I get on.

I'd welcome any other suggestions as I'm sure others will have come up against the same problem.
Re: Code not waiting for REFCURSOR [message #685766 is a reply to message #685752] Fri, 18 March 2022 11:22 Go to previous messageGo to next message
IanC
Messages: 4
Registered: March 2022
Junior Member
Unfortunately putting and ORDER BY clause in the query doesn't seem to make any difference apart from affecting performance slightly (on the query I was testing anyway).
Re: Code not waiting for REFCURSOR [message #685768 is a reply to message #685766] Fri, 18 March 2022 12:37 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3152
Registered: January 2010
Location: Connecticut, USA
Senior Member
When cursor is open all that is done is parsing SQL statement, generating execution plan and creating some memory structure that (for simplicity) that holds current state of cursor. Nothing is fetched at that point. Static cursor, dynamic cursor, cursor variable - makes no difference. It seems you misassumed that opening cursor gets all relevant rows and fetch just read it froom there. Actual work starts with first fetch. And if, for example, execution plan suggests to hash join several tables and then filter based on where clause to get relevant rows then that first join will take time to hash join, store results in temp tablespace and then dig through it finding first matching row. Then it will save (again for simplicity) where it left off in cursor structures. Consecutive fetches will check where previous one left off and continue to find next match.

SY.

[Updated on: Fri, 18 March 2022 12:42]

Report message to a moderator

Re: Code not waiting for REFCURSOR [message #685773 is a reply to message #685768] Tue, 22 March 2022 05:50 Go to previous message
IanC
Messages: 4
Registered: March 2022
Junior Member
I've solved it in the end by putting a sub-report in the report footer that calls my UPDATE sproc separately to the main report sproc, this ensures that the update is not made until the report has pulled through all of the data and my timestamp is now realistic. Thanks for your help in pointing out how the cursor works, it stopped me from going too far down that route and forced me to look for another way of doing it.
Previous Topic: Select Query suggestion
Next Topic: DB last date
Goto Forum:
  


Current Time: Sat Jul 02 21:42:40 CDT 2022