| What the Open cursor action?.(3 merged) [message #560634] |
Mon, 16 July 2012 02:57  |
 |
zengmuansha
Messages: 26
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?.(3 merged) [message #560834 is a reply to message #560634] |
Tue, 17 July 2012 06:31  |
John Watson
Messages: 8989
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? [message #560943 is a reply to message #560767] |
Tue, 17 July 2012 22:19 |
|
zengmuansha
Messages: 26
Registered: April 2012
|
Junior Member |
|
|
alan.kendall@nfl.com wrote on Mon, 16 July 2012 21:12I 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!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
