Developer:
I am running this query:
DELETE FROM tab;
It's been running since 2PM yesterday. Can you make it go faster?
DBA:
You shouldn't delete 80M rows in a single transaction. You should have used TRUNCATE instead of DELETE. I have killed your session and it will be a while before it completes.
Developer (5 minutes later):
I tried TRUNCATE but it says my table is locked.
DBA:
I know, the table will be locked up for a while while the transaction rolls back.
Developer:
Can you make it go faster?
DBA:
I have turned on _transmogrify_delete_queries on so that is the best I can do.
Developer:
Thanks!! That should definitely help!!!
Subscribe to:
Post Comments (Atom)
5 comments:
I need to take note of that parameter. :)
Or should you have?
word: biltr
In this case, yes he should have used truncate, but point taken.
OK, I'm sitting here scratching my head.
I have a simple table with a few thousand rows, no index. Every half hour with a sqlplus script, I truncate it, reload it, update the rows a couple of times (straight update statements, takes about a minute), then delete the identical table's rows over a link to XE and insert into it. This has been working fine for a couple of years now, it was a quick-and-dirty for some development that still hasn't been completed. Last week, I updated the XE to 11g production, recreating the table there.
This morning I came in, to see dbconsole perf screen all brown with concurrency, something I never see on this system. Investigation shows every process since 2AM has blown the truncate with ORA-54. Try killing the processes with dbconsole, they sit there marked for kill. Kill the processes at OS (hp-ux Itanium) with kill, they go away as far as ps is concerned, still marked for kill in Oracle. They are all still showing on the locks screen, TX Exclusive and TM Row Exclusive. Do an alter system flush shared pool, of course that does nothing to that truncate sql.
I note that the table was analyzed 6AM yesterday (Default 10.2.0.4 job). So I'm wondering if that left a lock? But then why would I not see the processes hung until starting 2AM today?
select * from v$lock where request!=0; gives no rows, no ORA- in the alert log.
I have a feeling I've seen an answer to this somewhere (probably Bug 5907779), but at this point I'm about to bounce the instance, up since the beginning of June with no problems. Drop the object also gets ora-54, of course.
Word: exokees
Ah yes, remote died, processes kept dogpiling. After killing every session and process I could find related to this, eventually could finally truncate. Then I saw why it looked so familiar.
Thanks for letting me think out loud.
word: solicult
Post a Comment