| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> OT RE: Not all rows deleted
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C087D0.91323D30
Content-Type: text/plain;
charset="iso-8859-1"
I love that second sentence! LoL!
You want "some kind of unreasonable, uncomprehensible explanation for this" ? Here's one:
Your code is running on Sun. The problem is in the 2.5.2.1.2 kernel. Now, on that kernel, there is a problem with the SHMMegma subsystem, resulting in a nanosecond race condition to set the GetSomeMore latch in the code. (My experimentation indicates this is in the Ring 0 code protecting the scheduler subsystem of the priority matrix, but don't hold me to that.)
Now, you might think: "Ha!" I am a tough guy, I can code in five languages, two of which haven't even been written yet, I will just write an assembler detour hack around the code sequence!" Well, you'd be wrong, and here's why:
The oracle kernel (you can tell from running ORADEBUG and generating
a "SPA FON WA" header dump, level 5, section III, double foam latte
and disassembling hex-wise from the cache chain filter latch list)
is *very* sensitive to changes in sub-nanosecond runtime race conditions.
When I say "sensitive", think of a adolescent male watching continuous loop
of Baywatch, and you'll realize things
could just pop off at any time. So, you don't want to do that.
So ( let's take a deep breath here, get up, stretch a bit....good! ) what we need to do is a bit esoteric, but it has worked for me a few times.
What you do is $OP*U#@_ A restriction in the system prevented delivery of the message.
The MTS-ID of the original message is: c=US;a=
;l=VOYAGER-010126180934Z-24725
MSEXCH:MSExchangeMTA:STARS-SMI:VOYAGER
-----Original Message-----
From: bonnergj_at_songs.sce.com [mailto:bonnergj_at_songs.sce.com]
Sent: Friday, January 26, 2001 10:36 AM
To: Multiple recipients of list ORACLE-L
Subject: Not all rows deleted
Hi everyone,
Sent this to the list two days ago but got no response. Come on, someone
has to have some kind of unreasonable, uncomprehensible explanation
for this.
I setup a job in cron that runs an oracle procedure every night at 3am. The
procedure deletes all the
rows of a table(less than 500 rows) then does an "insert select from a
remote database" to reload the
table. We delete rather than truncate because if the job fails it does a
rollback on the table and although
the data is 24 hours old it's preferred over "no data" until the problem is
corrected and reloaded.
The job has started failing 2 out of 5 times for the past several weeks.
When the "insert select" executes
it's failing with a unique constraint violation. The table has a unique
index on one column, same as the
remote table it's selecting from. The table being loaded only has 3
columns, same as the table it's
selecting from.
Just on a chance, I inserted some code between the delete and insert to
count how many records were
on the table after the delete. I was sure the count would be 0.
DELETE FROM bcc.func_desc_table;
SELECT COUNT(*) INTO v_count FROM bcc.func_desc_table;
IF v_count > 0 THEN
RAISE DELETE_ERROR;
END IF;
INSERT INTO BCC.FUNC_DESC_TABLE
(SELECT FUNWO , FUNCDESC , ACTIVITY
FROM BCC.TABLE20_at_BPCC.SONGS.SCE.COM);
I'm hitting the Raise Delete_Error because the count comes back with either
3 or 4 records still in the
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: bonnergj_at_songs.sce.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ------_=_NextPart_001_01C087D0.91323D30 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = charset=3Diso-8859-1"> <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2653.12"> <TITLE>OT RE: Not all rows deleted</TITLE> </HEAD> <BODY> <P><FONT SIZE=3D2>I love that second sentence! LoL! </FONT> </P> <P><FONT SIZE=3D2>You want "some kind of unreasonable, = uncomprehensible explanation</FONT> <BR><FONT SIZE=3D2>for this" ? Here's one:</FONT> </P> <P><FONT SIZE=3D2>Your code is running on Sun. The problem is in the = 2.5.2.1.2 </FONT> <BR><FONT SIZE=3D2>kernel. Now, on that kernel, there is a problem with = the</FONT> <BR><FONT SIZE=3D2>SHMMegma subsystem, resulting in a nanosecond race = condition</FONT> <BR><FONT SIZE=3D2>to set the GetSomeMore latch in the code. (My = experimentation</FONT> <BR><FONT SIZE=3D2>indicates this is in the Ring 0 code protecting the = scheduler</FONT> <BR><FONT SIZE=3D2>subsystem of the priority matrix, but don't hold me = to that.)</FONT> </P> <P><FONT SIZE=3D2>Now, you might think: "Ha!" I am a tough = guy, I can code in five</FONT> <BR><FONT SIZE=3D2>languages, two of which haven't even been written = yet, I will </FONT> <BR><FONT SIZE=3D2>just write an assembler detour hack around the code = sequence!"</FONT> <BR><FONT SIZE=3D2>Well, you'd be wrong, and here's why:</FONT> </P> <P><FONT SIZE=3D2>The oracle kernel (you can tell from running ORADEBUG = and generating</FONT> <BR><FONT SIZE=3D2>a "SPA FON WA" header dump, level 5, = section III, double foam latte </FONT> <BR><FONT SIZE=3D2>and disassembling hex-wise from the cache chain = filter latch list)</FONT> <BR><FONT SIZE=3D2>is *very* sensitive to changes in sub-nanosecond = runtime race conditions. When I say "sensitive", think of a = adolescent male watching continuous loop of Baywatch, and you'll = realize things</FONT></P> <P><FONT SIZE=3D2>could just pop off at any time. So, you don't want to = do that.</FONT> </P> <P><FONT SIZE=3D2>So ( let's take a deep breath here, get up, stretch a = bit....good! ) </FONT> <BR><FONT SIZE=3D2>what we need to do is a bit esoteric, but it has = worked for me a few</FONT> <BR><FONT SIZE=3D2>times. </FONT> </P> <P><FONT SIZE=3D2>What you do is = $OP*U#@_ A = restriction in the system prevented delivery of the message.</FONT> <BR> <FONT SIZE=3D2>The = MTS-ID of the original message is: c=3DUS;a=3D =Received on Fri Jan 26 2001 - 13:45:42 CST
;l=3DVOYAGER-010126180934Z-24725</FONT>
<BR><FONT = SIZE=3D2> &nb= sp; MSEXCH:MSExchangeMTA:STARS-SMI:VOYAGER</FONT> </P> <P><FONT SIZE=3D2>-----Original Message-----</FONT> <BR><FONT SIZE=3D2>From: bonnergj_at_songs.sce.com [<A = HREF=3D"mailto:bonnergj_at_songs.sce.com">mailto:bonnergj_at_songs.sce.com</A>= ]</FONT> <BR><FONT SIZE=3D2>Sent: Friday, January 26, 2001 10:36 AM</FONT> <BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT> <BR><FONT SIZE=3D2>Subject: Not all rows deleted</FONT> </P> <BR> <P><FONT SIZE=3D2>Hi everyone,</FONT> <BR><FONT SIZE=3D2>Sent this to the list two days ago but got no = response. Come on, someone</FONT> <BR><FONT SIZE=3D2>has to have some kind of unreasonable, = uncomprehensible explanation</FONT> <BR><FONT SIZE=3D2>for this.</FONT> <BR><FONT SIZE=3D2>I setup a job in cron that runs an oracle procedure = every night at 3am. The</FONT> <BR><FONT SIZE=3D2>procedure deletes all the</FONT> <BR><FONT SIZE=3D2>rows of a table(less than 500 rows) then does an = "insert select from a</FONT> <BR><FONT SIZE=3D2>remote database" to reload the</FONT> <BR><FONT SIZE=3D2>table. We delete rather than truncate because if the = job fails it does a</FONT> <BR><FONT SIZE=3D2>rollback on the table and although</FONT> <BR><FONT SIZE=3D2>the data is 24 hours old it's preferred over = "no data" until the problem is</FONT> <BR><FONT SIZE=3D2>corrected and reloaded.</FONT> <BR><FONT SIZE=3D2>The job has started failing 2 out of 5 times for the = past several weeks.</FONT> <BR><FONT SIZE=3D2>When the "insert select" executes</FONT> <BR><FONT SIZE=3D2>it's failing with a unique constraint violation. The = table has a unique</FONT> <BR><FONT SIZE=3D2>index on one column, same as the</FONT> <BR><FONT SIZE=3D2>remote table it's selecting from. The table being = loaded only has 3</FONT> <BR><FONT SIZE=3D2>columns, same as the table it's</FONT> <BR><FONT SIZE=3D2>selecting from.</FONT> <BR><FONT SIZE=3D2>Just on a chance, I inserted some code between the = delete and insert to</FONT> <BR><FONT SIZE=3D2>count how many records were</FONT> <BR><FONT SIZE=3D2>on the table after the delete. I was sure the count = would be 0.</FONT> <BR><FONT SIZE=3D2> DELETE FROM = bcc.func_desc_table;</FONT> <BR><FONT SIZE=3D2> SELECT COUNT(*) INTO = v_count FROM bcc.func_desc_table;</FONT> <BR><FONT SIZE=3D2> IF v_count > 0 = THEN</FONT> <BR><FONT = SIZE=3D2> = RAISE DELETE_ERROR;</FONT> <BR><FONT SIZE=3D2> END IF;</FONT> <BR><FONT SIZE=3D2> INSERT INTO = BCC.FUNC_DESC_TABLE</FONT> <BR><FONT SIZE=3D2> = (SELECT FUNWO , FUNCDESC , ACTIVITY</FONT> <BR><FONT = SIZE=3D2> &nb= sp; FROM BCC.TABLE20_at_BPCC.SONGS.SCE.COM);</FONT> <BR><FONT SIZE=3D2>I'm hitting the Raise Delete_Error because the count = comes back with either</FONT> <BR><FONT SIZE=3D2>3 or 4 records still in the</FONT> <BR><FONT SIZE=3D2>table. This explains the unique constraint violation = when the table is</FONT> <BR><FONT SIZE=3D2>reloaded but why are there records</FONT> <BR><FONT SIZE=3D2>remaining on the table after the Delete?</FONT> <BR><FONT SIZE=3D2>Next step, I added "LOCK TABLE = bcc.func_desc_table IN EXCLUSIVE MODE</FONT> <BR><FONT SIZE=3D2>NOWAIT" before the delete.</FONT> <BR><FONT SIZE=3D2>If the table couldn't be locked I would have gone to = my Exception handler</FONT> <BR><FONT SIZE=3D2>with a "-0054 resource busy" .</FONT> <BR><FONT SIZE=3D2>Even with the table locked it's still hitting the = Raise Delete_Error with 3</FONT> <BR><FONT SIZE=3D2>records remaining on the table.</FONT> <BR><FONT SIZE=3D2>There are no synonyms and neither table has primary = or foreign keys.</FONT> <BR><FONT SIZE=3D2>When I come in at 7 or 8am and manually run = the job there is never a</FONT> <BR><FONT SIZE=3D2>problem.</FONT> <BR><FONT SIZE=3D2>Any ideas, suggestions, theories??</FONT> </P> <P><FONT SIZE=3D2>-- </FONT> <BR><FONT SIZE=3D2>Please see the official ORACLE-L FAQ: <A = HREF=3D"http://www.orafaq.com" = TARGET=3D"_blank">http://www.orafaq.com</A></FONT> <BR><FONT SIZE=3D2>-- </FONT> <BR><FONT SIZE=3D2>Author: </FONT> <BR><FONT SIZE=3D2> INET: bonnergj_at_songs.sce.com</FONT> </P> <P><FONT SIZE=3D2>Fat City Network Services -- (858) = 538-5051 FAX: (858) 538-5051</FONT> <BR><FONT SIZE=3D2>San Diego, = California -- Public Internet = access / Mailing Lists</FONT> <BR><FONT = SIZE=3D2>---------------------------------------------------------------= -----</FONT> <BR><FONT SIZE=3D2>To REMOVE yourself from this mailing list, send an = E-Mail message</FONT> <BR><FONT SIZE=3D2>to: ListGuru_at_fatcity.com (note EXACT spelling of = 'ListGuru') and in</FONT> <BR><FONT SIZE=3D2>the message BODY, include a line containing: UNSUB =
 |
 |