| Author |
What is more costly ?
|
|
| LineVoltageHalogen 2005-08-28, 5:49 pm |
| What is the order of costliness with regards to: a.) redo activity,
potential lock escalation, etc:
inserts, updates, or deletes?
Thanks, TFD
| |
| tina london 2005-08-28, 5:49 pm |
| in terms of what, oracle never escalates locks so forget that one.
if you think about it what is the redo for an insert - nothing until
committed,
for an update the changed columns, old values
deletes the whole of the row will be needed
regarda
tina
| |
| DA Morgan 2005-08-28, 5:50 pm |
| LineVoltageHalogen wrote:
> What is the order of costliness with regards to: a.) redo activity,
> potential lock escalation, etc:
>
> inserts, updates, or deletes?
>
> Thanks, TFD
Are you refering to Oracle? Lock escalation doesn't exist.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)
| |
| LineVoltageHalogen 2005-08-28, 8:48 pm |
| So if I have a process by which thousands of records/min are being
inserted into a table no other writers or readers would be blocked? I
am not talking about the rows being inserted and have not been
committed. I am talking about the rest of the table with its existing
data.
| |
| LineVoltageHalogen 2005-08-28, 8:48 pm |
| so when committed a new row insert is just as costly as the delete?
| |
| DA Morgan 2005-08-28, 8:48 pm |
| LineVoltageHalogen wrote:
> So if I have a process by which thousands of records/min are being
> inserted into a table no other writers or readers would be blocked? I
> am not talking about the rows being inserted and have not been
> committed. I am talking about the rest of the table with its existing
> data.
Correct. It is impossible for an uncommited insert in Oracle to block
anything.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)
| |
| DA Morgan 2005-08-28, 8:48 pm |
| LineVoltageHalogen wrote:
> so when committed a new row insert is just as costly as the delete?
From what do you derive this?
Inserts and deletes require different constraint checks.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)
| |
| IANAL_VISTA 2005-08-28, 8:48 pm |
| "LineVoltageHalogen" <tropicalfruitdrops@yahoo.com> wrote in
news:1125275244.207094.83320@g44g2000cwa.googlegroups.com:
> so when committed a new row insert is just as costly as the delete?
>
Which metric(s) are measured to comprise your "cost"?
As with many things involving Oracle, "It depends"
and can vary depending upon the actual details.
| |
| Andreas Sheriff 2005-08-29, 2:51 am |
|
"DA Morgan" <damorgan@psoug.org> wrote in message
news:1125252977.724921@yasure...
> LineVoltageHalogen wrote:
>
> Are you refering to Oracle? Lock escalation doesn't exist.
> --
> Daniel A. Morgan
> http://www.psoug.org
> damorgan@x.washington.edu
> (replace x with u to respond)
I think he's thinking SQL Server, which does escalate locks.
--
Andreas Sheriff
Oracle 9i Certified Professional
Oracle 10g Certified Professional
Oracle 9i Certified PL/SQL Developer
----
"If you don't eat your meat, you cannot have any pudding.
"How can you have any pudding, if you don't eat your meat?"
DO NOT REPLY TO THIS EMAIL
Reply only to the group.
| |
| Matthias Kleinicke 2005-08-29, 2:51 am |
| DA Morgan schrieb:
> LineVoltageHalogen wrote:
>
>
>
> Correct. It is impossible for an uncommited insert in Oracle to block
> anything.
there should be no real block, but in 8i an existing unique index could
spoil a lot. imho this is caused by the unique-check which is bound to
check even against uncommitted inserts. I have not checked in 9i or 10g
for this.
greetings
Matthihas
| |
| Mladen Gogala 2005-08-29, 7:49 am |
| On Sun, 28 Aug 2005 17:40:38 -0700, DA Morgan wrote:
>
> Correct. It is impossible for an uncommited insert in Oracle to block
> anything.
Not quite. It can cause "ORA-01555 Snapshot too old" which will break
(block?) concurrently running query. This is, of course, a pathological
case, but a possible one.
--
http://www.mgogala.com
| |
| Anton Dischner 2005-08-29, 7:49 am |
| Hi,
by my experience delete is the most expensive task, by far.
kind regards,
Toni
| |
| DA Morgan 2005-08-29, 5:58 pm |
| Andreas Sheriff wrote:
> "DA Morgan" <damorgan@psoug.org> wrote in message
> news:1125252977.724921@yasure...
>
>
>
> I think he's thinking SQL Server, which does escalate locks.
That was my suspicion.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)
| |
| Daniel Fink 2005-08-29, 5:58 pm |
| Not true. If there is a unique constraint, an uncommitted insert will
block an insert of the same unique value. I don't have a 'play
database' handy right now, but you can test this yourself.
Create a table with a unique constraint (say EMP.EMPNO). In session1
insert a record with a new EMPNO. In session2, insert the same record.
Session2 will wait until session1 has ended it's transaction (commit or
rollback). If session1 commits, session2 will get a constraint
violation at that point. If session1 rollsback, session2 will return
successfully and wait for the insert to be updated/deleted.
Will another session be blocked trying to insert a truly unique record?
No.
This will not block any readers (of course, they won't see the new EMP
record either).
| |
| Daniel Fink 2005-08-29, 5:58 pm |
| Redo is written before the actual data is committed. This could be
microseconds, or it could be hours. Any recovery would be seriously
compromised if the redo was not written as soon as a single change was
made to the data.
As each insert/update/delete makes a change to a row, a redo vector
for that change (and only that change) is written to the log buffer.
Even if that insert/update/delete is immediately rolledback, the
original entry is still in the redo log. Also remember that a redo
entry is written for the change to the undo/rollback segment. In terms
of redo activity, an insert generates an insert redo entry and a redo
entry for the undo 'delete' entry. For a delete, there is a delete redo
entry and a redo entry for the undo 'insert' entry.
| |
| DA Morgan 2005-08-29, 5:58 pm |
| Daniel Fink wrote:
> Not true. If there is a unique constraint, an uncommitted insert will
> block an insert of the same unique value. I don't have a 'play
> database' handy right now, but you can test this yourself.
>
> Create a table with a unique constraint (say EMP.EMPNO). In session1
> insert a record with a new EMPNO. In session2, insert the same record.
> Session2 will wait until session1 has ended it's transaction (commit or
> rollback). If session1 commits, session2 will get a constraint
> violation at that point. If session1 rollsback, session2 will return
> successfully and wait for the insert to be updated/deleted.
>
> Will another session be blocked trying to insert a truly unique record?
> No.
>
> This will not block any readers (of course, they won't see the new EMP
> record either).
You are absolutely correct. I knew it at some point in time but age or
brain cell death stripped it from my memory a few days ago.
Thanks for setting the record straight.
No scotch for me tonight.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)
|
|
|
|