Home » SQL & PL/SQL » SQL & PL/SQL » What the Open cursor action?.(3 merged) (oracle 10g 10.2.03)
What the Open cursor action?.(3 merged) [message #560634] Mon, 16 July 2012 02:57 Go to next message
zengmuansha
Messages: 24
Registered: April 2012
Junior Member
create or replace procedure test_cur is
  cursor cur_t is
    select Course, avg(score)
      from exam e
     inner join (select student_id
                   from class
                  where class_name like '%Computer Sciencer%') c
        on e.student_id = c.student_id
     where e.exam_time between trunc(sysdate - 122) and trunc(sysdate)
     group by course, avg;

  type t_arry_course is table of varchar2 %type index by binary_integer;
  type t_arry_score is table of number %type index by binary_integer;
  l_arry_course t_arry_course;
  l_arry_score  t_arry_score;
begin
  open cur_t;
  loop
    fetch cur_t bulk collent
      into l_arry_course, l_arry_score limit 10000;
    exit when cur_t notfound or cur_t notfound is null;
  end loop;
end;


Q: Open cursor is parse plan and rember SCN, first fetch then execute the plan,
bulk is reduce sql/plsql switch cost ,is Ture?

test it procedur then read v$sqlstats column execute fetch and parse times,
that excute eque pase 3 times and fecth is 300 times.

I think that open cursor action is parse , excute plan and stored result to template tablespace,
and fetch action is fetch result from template.
what switch plsql/sql that fetch action excute the plan again?

That I think is lost sql/plsql switch


[EDITED by LF: after topics have being merged several times, I have removed duplicates and cleaned up the mess]

[Updated on: Tue, 17 July 2012 05:30] by Moderator

Report message to a moderator

Re: What the Open cursor action? [message #560767 is a reply to message #560634] Mon, 16 July 2012 21:12 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
I know the sql plan to get invalidated in the shared pool and re-parsed if the query is executed again:
1) The shared_pool is manually flushed by an administrator (sometimes done to free up memory).
2) New stats are run on the objects in the query (could be done manually or automatically if the rowcounts change too much).
3) The sql gets flushed out of the shared_pool for inactivity.
4) The objects in the query are altered or re-created which would force a re-parse to get the new object_ids and column_ids.
Re: What the Open cursor action? formate [message #560823 is a reply to message #560634] Tue, 17 July 2012 05:18 Go to previous messageGo to next message
Michel Cadot
Messages: 58523
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Reposting the question again and again will not give more answer, it is the opposite, regulars will be upset by these repetitions and moderators by the work they have to do to merge your questions.

Regards
Michel
Re: What the Open cursor action?.(3 merged) [message #560834 is a reply to message #560634] Tue, 17 July 2012 06:31 Go to previous messageGo to next message
John Watson
Messages: 4375
Registered: January 2010
Location: Global Village
Senior Member
OK, I'll try to answer this, and get rid of it. But it is difficult, because the question is confused.
I'm going to assume that when you say "template" you mean "temporary", and that "execute plan" means "execute the statement". So you are suggesting that when you open a cursor, Oracle will run the statement, and store the result set. Wrong! A cursor is just a pointer to information about the statement. The result set isn't stored at all (unless you use a Result Cache). Usually, there is no need to execute the entire statement before returning the first row.
Re: What the Open cursor action?.(3 merged) [message #560940 is a reply to message #560834] Tue, 17 July 2012 22:05 Go to previous messageGo to next message
zengmuansha
Messages: 24
Registered: April 2012
Junior Member
John Watson wrote on Tue, 17 July 2012 06:31
OK, I'll try to answer this, and get rid of it. But it is difficult, because the question is confused.
I'm going to assume that when you say "template" you mean "temporary", and that "execute plan" means "execute the statement". So you are suggesting that when you open a cursor, Oracle will run the statement, and store the result set. Wrong! A cursor is just a pointer to information about the statement. The result set isn't stored at all (unless you use a Result Cache). Usually, there is no need to execute the entire statement before returning the first row.


"before returning the first row" that means is first "fecth cursor " statement ?

Where you say is true that "first returning the first row" sotre of result?



Thank you !
Re: What the Open cursor action? formate [message #560942 is a reply to message #560823] Tue, 17 July 2012 22:16 Go to previous messageGo to next message
zengmuansha
Messages: 24
Registered: April 2012
Junior Member
Michel Cadot wrote on Tue, 17 July 2012 05:18
Reposting the question again and again will not give more answer, it is the opposite, regulars will be upset by these repetitions and moderators by the work they have to do to merge your questions.

Regards
Michel


Reposting the question again because the pl/sql code have not formating.


Thank you merge it
Re: What the Open cursor action? [message #560943 is a reply to message #560767] Tue, 17 July 2012 22:19 Go to previous message
zengmuansha
Messages: 24
Registered: April 2012
Junior Member
alan.kendall@nfl.com wrote on Mon, 16 July 2012 21:12
I know the sql plan to get invalidated in the shared pool and re-parsed if the query is executed again:
1) The shared_pool is manually flushed by an administrator (sometimes done to free up memory).
2) New stats are run on the objects in the query (could be done manually or automatically if the rowcounts change too much).
3) The sql gets flushed out of the shared_pool for inactivity.
4) The objects in the query are altered or re-created which would force a re-parse to get the new object_ids and column_ids.


Thank you answer,but it direction is error!
Previous Topic: Issue with Pipeline table function
Next Topic: Need help with SQL query
Goto Forum:
  


Current Time: Thu Jul 24 03:33:32 CDT 2014

Total time taken to generate the page: 0.10609 seconds