Re: Effective Oracle by Design - p259 - 260 - confused,isthere a mistake?
- From: <t_adolph@xxxxxxxxxxx>
- To: "Norman Dunbar" <norman.dunbar@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 2 Nov 2005 13:26:05 +0100
Hi Norman and aothers,
Its clear now, see below for anyone interested in my mistake and the
outcome....
He does:
create table emp as select * from scott.emp;
variable a refcursor
variable b refcursor
variable c refcursor
alter session set sql_trace=true;
begin
open :a for select empno from emp q1 where ename = 'BLAKE';
open :b for select empno from emp q2 where ename = 'BLAKE';
open :c for select empno from emp q3 where ename = 'SMITH';
end;
/
print a
begin
for i in 1 .. 1000
loop
update emp
set sal = sal
where ename = 'BLAKE';
commit;
end loop;
delete from emp
where ename = 'SMITH';
commit;
end;
/
print b
print c
At the "print b" point tracing shows that the undo is used, i.e. query =
1000, but I didn't undestand why as the loop commited all of its changes.
The explanation from Tom:
"These extra I/Os where die to Oracle reading the undo infomation to, in
effect, rollback the block so we would get BLAKE's infomation as of the
point in time the query was oppened."
Here's where I got confused. When is the curse b "evaluated" is the point
people have been pointing me to, its when its fetched, i.e. after the loop.
My example, trying to simplify the example was no good as it didn't open a
cursor at a point-in-time *before* the looping.
I think my old teachers at school whould have said "Read the question Tony"
!!
Thanks for all the feedback folks,
Cheers
Tony
PS: yes, for those who know me, I do have a long memory
----- Original Message -----
From: "Norman Dunbar" <norman.dunbar@xxxxxxxxxxxxxxxxxxxxxxxxx>
To: <t_adolph@xxxxxxxxxxx>
Sent: Wednesday, November 02, 2005 12:29 PM
Subject: Re: Effective Oracle by Design - p259 - 260 - confused,isthere a
mistake?
>
> Hi Tony,
>
> >> Thanks for the feedback.
> Welcome.
>
> I'm afraid I'm a few miles away from my copy of the book so I'm unable
> to say 100% what is going on.
>
> However, if Tom commits in the loop (interesting thought because he
> correctly advises never committing in a loop) then all I can think of is
> that his other query was started before the data was updated. It's a
> shame he doesn't supply one of his 'time line' examples for this demo -
> he usually does and you can see what happened when and where quite
> easily in those.
>
> <SNIP>
>
> >> Maybe I've misunderstood, and its using the reference cursors he
> sets up
> >> earlier. But I can't figure that out as they before the loop "bit".
>
> AHA (maybe). When the reference cursor is opened, that is the time at
> which the data is required to be committed by in order to be seen
> 'directly'. Any updates since the open time will require the undo
> details to roll them back to the cursor open time.
>
> Maybe that's what has happened. Does he open the cursor (or call the
> procedure/function which returns the ref cursor) before he starts
> updating or after ?
>
> For example :
>
> Session B - call procedure to get a ref cursor.
>
> Session A - start updating with commit in loop.
>
> Session B - start USING data from ref cursor.
>
>
> Cheers,
> Norman.
>
>
>
> Norman Dunbar.
> Contract Oracle DBA.
> Rivers House, Leeds.
>
> Internal : 7 28 2051
> External : 0113 231 2051
>
>
> Information in this message may be confidential and may be legally
privileged. If you have received this message by mistake, please notify the
sender immediately, delete it and do not copy it to anyone else.
>
> We have checked this email and its attachments for viruses. But you should
still check any attachment before opening it.
>
> We may have to make this message and any reply to it public if asked to
under the Freedom of Information Act, Data Protection Act or for litigation.
Email messages and attachments sent to or from any Environment Agency
address may also be accessed by someone other than the sender or recipient,
for business purposes.
>
> If we have sent you information and you wish to use it please read our
terms and conditions which you can get by calling us on 08708 506 506. Find
out more about the Environment Agency at www.environment-agency.gov.uk
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » Re: Effective Oracle by Design - p259 - 260 - confused,isthere a mistake?
