Home » SQL & PL/SQL » SQL & PL/SQL » Nested cursor expressions (DB 12.1.0.2)
Nested cursor expressions [message #650321] Fri, 22 April 2016 08:46 Go to next message
John Watson
Messages: 8930
Registered: January 2010
Location: Global Village
Senior Member
I'm trying to sort out what is happening with some code that is projecting cursors in a select statement. There are several levels of nesting. This is a simple example of what I mean:
select empno,cursor(select dname from dept where dept.deptno=emp.deptno) from emp;

From the SQL Reference:
Quote:
A nested cursor is implicitly opened when the cursor expression is evaluated. For example, if the cursor expression appears in a select list, a nested cursor will be opened for each row fetched by the query. The nested cursor is closed only when:
The nested cursor is explicitly closed by the user
The parent cursor is reexecuted
The parent cursor is closed
The parent cursor is cancelled
An error arises during fetch on one of its parent cursors (it is closed as part of the clean-up)
In my query above, there are fourteen rows selected by the outer query. Does that mean that there will be fourteen cursors opened, concurrently, against dept?

I've tried to count the open cursors by using (guess what!) a cursor expression that queries v$mystat and I don't see that (though perhaops that could be because there is no read consistency against v$ views). In the problem database, I've had to raise the open_cursors parameter to 1000 just to get the query through, but I'm not getting repeatable results.

Thank you for any insight - I'm really not sure what is going on.

Report message to a moderator

Re: Nested cursor expressions [message #650323 is a reply to message #650321] Fri, 22 April 2016 09:32 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
That quote does strongly imply that in your case 14 cursors will be opened.
The real question is when are they closed - you'd like to hope it's as soon as the select finishes fetching data, and if so that probably explains why you're struggling to see the correct number of open cursors.

Report message to a moderator

Re: Nested cursor expressions [message #650324 is a reply to message #650323] Fri, 22 April 2016 09:45 Go to previous messageGo to next message
John Watson
Messages: 8930
Registered: January 2010
Location: Global Village
Senior Member
Thanks for replying. I'm now getting more confused. This code does not throw an error:
drop table t1;
drop table t2;
create table t1 as select rownum rn from dual connect by level < 100;
create table t2 as select rownum rn from dual connect by level < 100;

alter system set open_cursors=50;

select rn,cursor(select 'a' from t2 where t2.rn=t1.rn) from t1;
but the obvious interpretation of that doc quote is that it should. Then it gets worse: I can set open_cursors to 1, and still run the code. I must be missing something obvious.

The fact remains that when I raised open cursors in my problem DB, the queries started running ok.

Report message to a moderator

Re: Nested cursor expressions [message #650326 is a reply to message #650324] Fri, 22 April 2016 09:55 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
I always thought that it would run the select for each row in the select by reusing the same explain plan and cursor each time so it would open the internal select once and then reuse it for each row in the outer select.

Report message to a moderator

Re: Nested cursor expressions [message #650328 is a reply to message #650321] Fri, 22 April 2016 10:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
In my query above, there are fourteen rows selected by the outer query. Does that mean that there will be fourteen cursors opened, concurrently, against dept?


Yes:

SQL> @traceon

Session altered.

SQL> select empno,cursor(select dname from dept where dept.deptno=emp.deptno) from emp;
...

14 rows selected.

SQL> @traceoff

Session altered.


trace file


$ cat mikb2_ora_5432.trc | grep 'EXEC ' | wc -l
16

$ cat mikb2_ora_5432.trc | grep 'EXEC ' | cut -d':' -f1 | sort -u | wc -l
5


There are 16 executions (why not 15? I didn't analyze the file in details) of cursor but actually 5 different ones (one for the complete query, one per department in EMP (10,20,30) and one for "trace off", see Edit below). I think the figures may depends on the Oracle version (I used 11.2.0.4) and, most likely, on session_cached_cursors.


[Edit: silly me, the 16th EXEC is "alter session set events '10046 trace name context off'", of course]

[Updated on: Fri, 22 April 2016 10:08]

Report message to a moderator

Re: Nested cursor expressions [message #650329 is a reply to message #650326] Fri, 22 April 2016 10:09 Go to previous messageGo to next message
John Watson
Messages: 8930
Registered: January 2010
Location: Global Village
Senior Member
Well, yes, Bill - that is the way any sane person would do it - but the docs imply differently. I wish I could prove it one way or the other. There doesn't seem to be a metric for the maximum number of cursors opened concurrently by a session, which is what I'ld like to see.

Report message to a moderator

Re: Nested cursor expressions [message #650330 is a reply to message #650329] Fri, 22 April 2016 10:11 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
It has to be that way, otherwise a query on millions of rows would blow out the maximum cursors very fast (and I have done a query like tghat). I suspect that the documentation is wrong or misleading.

Report message to a moderator

Re: Nested cursor expressions [message #650331 is a reply to message #650328] Fri, 22 April 2016 10:12 Go to previous messageGo to next message
John Watson
Messages: 8930
Registered: January 2010
Location: Global Village
Senior Member
Thank you - I really appreciate the advice so far.

Michel, I see the number of executions: it seems to work like a correlated subquery, which the CBO can't optimize very well. But are the cursors for each execution held open until the callingquery finishes?

Report message to a moderator

Re: Nested cursor expressions [message #650333 is a reply to message #650330] Fri, 22 April 2016 10:17 Go to previous messageGo to next message
John Watson
Messages: 8930
Registered: January 2010
Location: Global Village
Senior Member
Yes, Bill, that's my problem - when the query retrieved "too many" rows it would fail. That's why I pushed up open_cursors to 1000.

This is in APEX, generating JSON through a webservice. I can't simulate it from SQL*Plus. If I could, I might come up with a solution that I would have confidence in for larger data sets.

Report message to a moderator

Re: Nested cursor expressions [message #650334 is a reply to message #650331] Fri, 22 April 2016 10:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In the trace file we see a child cursor is closed when the next row of the main cursor is fetched, sometimes immediately after, sometimes it waits the next one...

Quote:
it seems to work like a correlated subquery, which the CBO can't optimize very well.


Yes.

Report message to a moderator

Re: Nested cursor expressions [message #650335 is a reply to message #650333] Fri, 22 April 2016 10:19 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Can you see what the select that the APEX is running. What does it look like?

Report message to a moderator

Re: Nested cursor expressions [message #650336 is a reply to message #650333] Fri, 22 April 2016 10:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
This is in APEX, generating JSON through a webservice. I can't simulate it from SQL*Plus. If I could, I might come up with a solution that I would have confidence in for larger data sets.


You're right maybe the closing of cursors I see is the fact of SQL*Plus.

Thinking a little bit I'm pretty sure this is the case. I remember having written a Perl program which supports CURSOR and I had indeed to close the child cursor after fetching all of it for a row of the main cursor.

Report message to a moderator

Re: Nested cursor expressions [message #650337 is a reply to message #650335] Fri, 22 April 2016 10:27 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
One way around this would be to put the login into a procedure call and return a REF cursor. It works great and no problems with hopw other applications work.

Report message to a moderator

Re: Nested cursor expressions [message #650338 is a reply to message #650337] Fri, 22 April 2016 11:30 Go to previous messageGo to next message
John Watson
Messages: 8930
Registered: January 2010
Location: Global Village
Senior Member
This is a code snippet -
select
  'f1' as "fame",
  cursor(
     select
       cursor(
	   SELECT
		  p.id as "xx_id",
		  trim(upper(p.xx)) as "c_xx",
		  cursor(select x1.x_ID as "x_id",
					  NVL2(x1.xNAME||x1.xNAME,upper(x1.xNAME || ', ' || x1.xNAME || ' ' || x.xNAME),'') as "xname",
					  xx1.xx_ID  as "xx_id",

<snip>

		ORDER BY x.xx
       ) as "xxx"
     from dual
  ) as "xx"
from dual
I've hacked it to anonymize it but you get the idea.

Report message to a moderator

Re: Nested cursor expressions [message #650339 is a reply to message #650336] Fri, 22 April 2016 11:32 Go to previous messageGo to next message
John Watson
Messages: 8930
Registered: January 2010
Location: Global Village
Senior Member
Thankyou, Michel. If I understand you correctly, it maybe that APEX is holding the subquery cursors open, unlike SQL*Plus. There are several layers here (it is using Apache plus ORDS 3.0.2 dep[loyed to a Glassfish container) and the sessions are through a JDBC connection pool.

Report message to a moderator

Re: Nested cursor expressions [message #650340 is a reply to message #650337] Fri, 22 April 2016 11:33 Go to previous messageGo to next message
John Watson
Messages: 8930
Registered: January 2010
Location: Global Village
Senior Member
Bill B wrote on Fri, 22 April 2016 16:27
One way around this would be to put the login into a procedure call and return a REF cursor. It works great and no problems with hopw other applications work.



--update I messed that up trying again:

That is the heart of the problem. The code is using ORDS webservice to generate the JSON, and the webservice can't work with a ref cursor, only with the way I've described. I'm actually looking for a completely different method, but haven't found one. Yet.

[Updated on: Fri, 22 April 2016 11:36]

Report message to a moderator

Re: Nested cursor expressions [message #650341 is a reply to message #650339] Fri, 22 April 2016 11:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
John Watson wrote on Fri, 22 April 2016 18:32
Thankyou, Michel. If I understand you correctly, it maybe that APEX is holding the subquery cursors open, unlike SQL*Plus. There are several layers here (it is using Apache plus ORDS 3.0.2 dep[loyed to a Glassfish container) and the sessions are through a JDBC connection pool.


I don't see any other way to check it than to trace the session.

Report message to a moderator

Re: Nested cursor expressions [message #650343 is a reply to message #650341] Fri, 22 April 2016 19:11 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
I kind of lost the thread of this discussion. I think part of my confusion here is what are we counting? Number of cursors or number of concurrent cursors? Total number of cursors in

select empno,cursor(select dname from dept where dept.deptno=emp.deptno) from emp;


will be 16. One cursor for select from emp plus 15 cursors for select from dept. Number of concurrent cursors will be 2. I think there was a question why 15 if there are only 14 rows in emp table. Answer is quite simple. How many fetches there are when we select * from emp? There will be 15 fetches. Last one will result in cursor%notfound. However all preparatory work for that 15th fetch will be done and in case of having cursor in select list means curso for that 15th fetch will be prepared an open.

SY.

Report message to a moderator

Re: Nested cursor expressions [message #650344 is a reply to message #650343] Sat, 23 April 2016 00:19 Go to previous messageGo to next message
John Watson
Messages: 8930
Registered: January 2010
Location: Global Village
Senior Member
THank you for replying.

Are you certain of this? That the nested cursors will not be held open for the lifetime of the outer query? The docs imply differently, and my experience with this APEX application suggests differently.

The total number of cursors opened is easy to measure, the number concurrently opened is harder. There is the counter "opened cursors current" but it does not seem to give consistent results.

Report message to a moderator

Re: Nested cursor expressions [message #650349 is a reply to message #650343] Sat, 23 April 2016 01:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
How many fetches there are when we select * from emp? There will be 15 fetches. Last one will result in cursor%notfound However all preparatory work for that 15th fetch will be done and in case of having cursor in select list means curso for that 15th fetch will be prepared an open.


No, the last one returning NO_DATA_FOUND will not generate a child cursor, because there is nothing to generate.
In addition, my post counts the EXEC how could you execute a cursor that does not exist?
See the trace file I posted, it is clear the 16 cursor executions are 1 for the global query, 14 for each row of this later one and 1 for "trace off".

Report message to a moderator

Re: Nested cursor expressions [message #650363 is a reply to message #650344] Sat, 23 April 2016 06:44 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
Well, you're right it all depends on a tool. Parent cursor fetch always opens, parses & executes nested cursor but closing it is tool dependent. SQL*Plus, when executing SQL with nested cursor implicitly closes nested cursor. But if we write PL/SQL, then closing nested cursor is your responsibility.

SY.

Report message to a moderator

Re: Nested cursor expressions [message #650365 is a reply to message #650349] Sat, 23 April 2016 07:03 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
Michel Cadot wrote on Sat, 23 April 2016 02:32


No, the last one returning NO_DATA_FOUND will not generate a child cursor, because there is nothing to generate.
In addition, my post counts the EXEC how could you execute a cursor that does not exist?


Yes, your post counts EXECs and I was talking about open. My understanding is parent cursor fetch parses and opens nested cursor as part of pre-fetch activities. Lets forget about nested cursor for a moment. Assume we have:

FETCH some_cursor INTO some_variable_list

Oracle ties select list expressions to variables in some_variable_list as part of pre-fetch activities. Now assume one of select list expressions is nested cursor. Same way it needs to be tied to ref cursor in some_variable_list which means opening (re-opening) ref cursor + parsing (pre-parsing in our case since our nested cursor has bind variable). Then fetch occurs. And after %FOUND = TRUE fetch ref cursor bind variables are bound if needed (like in our case) and ref cursor is executed. But again, it is my understanding an, in fact, it is completely internal to Oracle and can change from release to release. And on top of it, there is advanced cursor sharing so each time we fetch we can end up with new child cursor which can affect cursor count.

SY.

Report message to a moderator

Re: Nested cursor expressions [message #650367 is a reply to message #650363] Sat, 23 April 2016 07:34 Go to previous messageGo to next message
John Watson
Messages: 8930
Registered: January 2010
Location: Global Village
Senior Member
Solomon Yakobson wrote on Sat, 23 April 2016 12:44
Well, you're right it all depends on a tool. Parent cursor fetch always opens, parses & executes nested cursor but closing it is tool dependent. SQL*Plus, when executing SQL with nested cursor implicitly closes nested cursor. But if we write PL/SQL, then closing nested cursor is your responsibility.

SY.
THank you again - this helps a lot. So to fix my problem (I've had to raise open_cursors to 3000 so far, which is clearly not a solution) I need to work out how to close the nested cursors. I have no idea how to do that, or even if it is possible in a SQL statement. Perhaps there is something I can do at the JDBC level?

Report message to a moderator

Re: Nested cursor expressions [message #650368 is a reply to message #650365] Sat, 23 April 2016 08:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Unfortunately we can't see any "open cursor" operation in the trace file. For me, at pre-fetch, there is just an allocation, in the PGA, of a reference to a possible slot in the cursor tables in the UGA that a subsequent OPEN will take if there is a row returned from the main query.
As, after the last row, this happens and is immediately deallocated as there is no more value, if this is what really happened, we can never see it...

[Updated on: Sat, 23 April 2016 08:35]

Report message to a moderator

Re: Nested cursor expressions [message #650369 is a reply to message #650367] Sat, 23 April 2016 08:40 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
Here is PL/SQL example:

set serveroutput on format wrapped
declare
    cursor v_cur
      is
        select  dname,
                cursor(
                       select  ename
                         from  emp c
                         where a.deptno = c.deptno
                      )
          from  dept a;
    v_dname      dept.dname%type;
    v_ename      emp.ename%type;
    v_nested_cur sys_refcursor;
begin
    open v_cur;
    loop
      fetch v_cur
        into v_dname,
             v_nested_cur;
      exit when v_cur%notfound;
      dbms_output.put_line (v_dname || ':');
      loop
        fetch v_nested_cur
          into v_ename;
        exit when v_nested_cur%notfound;
        dbms_output.put_line ('  ' || v_ename);
      end loop;
      close v_nested_cur;
    end loop;
    close v_cur;
end;
/
ACCOUNTING:
  CLARK
  KING
  MILLER
RESEARCH:
  SMITH
  JONES
  SCOTT
  ADAMS
  FORD
SALES:
  ALLEN
  WARD
  MARTIN
  BLAKE
  TURNER
  JAMES
OPERATIONS:

PL/SQL procedure successfully completed.

SQL>


And below is open cursors when not closing & closing nested cursor:

create or replace
  procedure p1(
               p_close_flag varchar2 := 'Y'
              )
  is
    v_refcur     sys_refcursor;
    v_dname      dept.dname%type;
    v_ename      emp.ename%type;
    v_nested_cur sys_refcursor;
begin
    open v_refcur
      for
        'select  dname,
                 cursor(
                        select  ename
                          from  emp c
                          where a.deptno = c.deptno
                       )
           from  dept a';
    loop
      fetch v_refcur
        into v_dname,
             v_nested_cur;
      exit when v_refcur%notfound;
      dbms_output.put_line (v_dname || ':');
      loop
        fetch v_nested_cur
          into v_ename;
        exit when v_nested_cur%notfound;
        dbms_output.put_line ('  ' || v_ename);
      end loop;
      if p_close_flag = 'Y'
        then
          close v_nested_cur;
      end if;
    end loop;
    close v_refcur;
end;
/

Procedure created.

SQL> -- I'll start with clean session
SQL> connect scott@pdb1sol12
Enter password: *****
Connected.
SQL> select  sql_text,
  2          cursor_type
  3    from  v$open_cursor
  4    where lower(sql_text) not like '%v$open\_cursor%' escape '\'
  5      and lower(sql_text) like '%ename%'
  6      and sid = sys_context('userenv','sid')
  7  /

no rows selected

SQL> -- closing nested cursor
SQL> exec p1

PL/SQL procedure successfully completed.

SQL> select  sql_text,
  2          cursor_type
  3    from  v$open_cursor
  4    where lower(sql_text) not like '%v$open\_cursor%' escape '\'
  5      and lower(sql_text) like '%ename%'
  6      and sid = sys_context('userenv','sid')
  7  /

SQL_TEXT                                                     CURSOR_TYPE
------------------------------------------------------------ ----------------------------------------
SELECT "C"."ENAME" "ENAME" FROM "EMP" "C" WHERE :CV1$="C"."D SESSION CURSOR CACHED

SQL> -- not closing nested cursor
SQL> exec p1('N')

PL/SQL procedure successfully completed.

SQL> select  sql_text,
  2          cursor_type
  3    from  v$open_cursor
  4    where lower(sql_text) not like '%v$open\_cursor%' escape '\'
  5      and lower(sql_text) like '%ename%'
  6      and sid = sys_context('userenv','sid')
  7  /

SQL_TEXT                                                     CURSOR_TYPE
------------------------------------------------------------ ----------------------------------------
SELECT "C"."ENAME" "ENAME" FROM "EMP" "C" WHERE :CV1$="C"."D DICTIONARY LOOKUP CURSOR CACHED
SELECT "C"."ENAME" "ENAME" FROM "EMP" "C" WHERE :CV1$="C"."D DICTIONARY LOOKUP CURSOR CACHED
SELECT "C"."ENAME" "ENAME" FROM "EMP" "C" WHERE :CV1$="C"."D DICTIONARY LOOKUP CURSOR CACHED
SELECT "C"."ENAME" "ENAME" FROM "EMP" "C" WHERE :CV1$="C"."D SESSION CURSOR CACHED

SQL>


SY.

Report message to a moderator

Re: Nested cursor expressions [message #650409 is a reply to message #650369] Sun, 24 April 2016 10:39 Go to previous message
John Watson
Messages: 8930
Registered: January 2010
Location: Global Village
Senior Member
Thank you for this example, SY. I'm investigating whether the technique can be used in our code that is generating the JSON docs. It is a very slim chance, but I need to look at every possibility.

Report message to a moderator

Previous Topic: with clause logic
Next Topic: Please help for regexp_replace in oracle 11g
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Apr 23 06:53:53 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.