Re: Delete followed by Select Count(1) - SLOW
See my website: Index of topics - Miscellaneous - Block Cleanout Jonathan Lewis Seminars on getting the best out of Oracle Last few places available for Sept 10th/11th See http://www.jlcomp.demon.co.uk/seminar.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 17 August 2001 02:52 |Hi Jonathan, | |Can you tell us a little bit more about delayed block |cleanouts .. if you can | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Delete followed by Select Count(1) - SLOW
I remember in slightly older versions of Oracle, delayed_logging_block_cleanouts is by default true, so redo won't be written immediately at block cleanout. Correct me if I'm wrong. Yong Huang [EMAIL PROTECTED] Jonathan Lewis wrote: bear in mind that a scan after deleting 100,000 rows would probably be doing a lot of delayed block cleanout - resulting in plenty of redo log, and possibly a lot of dbwr activity. Jonathan Lewis __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: yong huang INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Delete followed by Select Count(1) - SLOW
delayed_logging_block_cleanout was introduced in newer (7.3+) versions of Oracle to smooth things out with OPS. But delayed block cleanout has been around much longer - the documents tended to hint that it no longer happened with the introduction of the fast commit and delayed_logging_block_cleanout, but reports were (like Mark Twain's death) exaggerated. Jonathan Lewis Seminars on getting the best out of Oracle Last few places available for Sept 10th/11th See http://www.jlcomp.demon.co.uk/seminar.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 17 August 2001 21:49 |I remember in slightly older versions of Oracle, |delayed_logging_block_cleanouts is by default true, so redo won't be written |immediately at block cleanout. Correct me if I'm wrong. | |Yong Huang |[EMAIL PROTECTED] | |Jonathan Lewis wrote: | |bear in mind that a scan after deleting 100,000 |rows would probably be doing a lot of |delayed block cleanout - resulting in plenty |of redo log, and possibly a lot of dbwr activity. | |Jonathan Lewis | |__ |Do You Yahoo!? |Make international calls for as low as $.04/minute with Yahoo! Messenger |http://phonecard.yahoo.com/ |-- |Please see the official ORACLE-L FAQ: http://www.orafaq.com |-- |Author: yong huang | INET: [EMAIL PROTECTED] | |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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Jonathan's Book again (WAS) Re: Delete followed by Select Count(1) - SLOW
On Fri, 17 Aug 2001, [EMAIL PROTECTED] wrote: delayed_logging_block_cleanout was introduced in newer (7.3+) versions of Oracle to smooth things out with OPS. I just read the first chapter and it already explained things that I have always been fuzzy about. I am looking forward to the rest of the book. In the first chapter, he talks about delayed_logging_block_cleanout and what's going on. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Galen Boyer INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Delete followed by Select Count(1) - SLOW
Hi Walter, The extents you refer to in ur thread is the allocated space. Oracle by default scans upto the high watermark. The reason truncate is fast is cause it resets the high watermark so oracle did not scan anything for u when it returned 0 rows .. however in case of delete, it still scanned upto the HWM Deepak --- Walter K [EMAIL PROTECTED] wrote: I have a user that deleted all of the rows in a table (i.e. 100,000), waited for it to complete, and then ran a SELECT COUNT(1) FROM table. It took a few minutes for '0 rows' to be returned to the prompt. The table has ~60 extents (128k ea.). Granted, the number of extents is excessive but it's a development instance and this table is an exception. Is Oracle scanning through all of the blocks, since the space wasn't released, and this is the cause of the latency? The curious thing is that I told this user to use TRUNCATE instead and we talked about using the drop/reuse storage clauses. He performed a TRUNCATE...REUSE STORAGE and the same select and it was night and day in terms of performance. If the allocated space isn't being released in this case also, why is there such a performance difference between the two? -w __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deepak Thapliyal INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Delete followed by Select Count(1) - SLOW
Yes, It has been known if you delete all records, use a truncate and not delete. Otherwise it will still behave like it is full as it has to touch all the extents. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Thursday, August 16, 2001 5:33 PM To: Multiple recipients of list ORACLE-L I have a user that deleted all of the rows in a table (i.e. 100,000), waited for it to complete, and then ran a SELECT COUNT(1) FROM table. It took a few minutes for '0 rows' to be returned to the prompt. The table has ~60 extents (128k ea.). Granted, the number of extents is excessive but it's a development instance and this table is an exception. Is Oracle scanning through all of the blocks, since the space wasn't released, and this is the cause of the latency? The curious thing is that I told this user to use TRUNCATE instead and we talked about using the drop/reuse storage clauses. He performed a TRUNCATE...REUSE STORAGE and the same select and it was night and day in terms of performance. If the allocated space isn't being released in this case also, why is there such a performance difference between the two? -w __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christopher Spence INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Delete followed by Select Count(1) - SLOW
Truncate basically resets the table's highwater mark to 0. Deletes don't do that. A 'select count(*)' does a full table scan, which much touch all the blocks up to the HWM, and that's what's taking the time. The REUSE STORAGE option keeps the extents allocated to the table, but the HWM is still reset to 0. At least, that's what I was taught! HTH, Mike --- === Michael P. Vergara Oracle DBA Guidant Corporation (909) 914-2304 -Original Message- Sent: Thursday, August 16, 2001 2:33 PM To: Multiple recipients of list ORACLE-L I have a user that deleted all of the rows in a table (i.e. 100,000), waited for it to complete, and then ran a SELECT COUNT(1) FROM table. It took a few minutes for '0 rows' to be returned to the prompt. The table has ~60 extents (128k ea.). Granted, the number of extents is excessive but it's a development instance and this table is an exception. Is Oracle scanning through all of the blocks, since the space wasn't released, and this is the cause of the latency? The curious thing is that I told this user to use TRUNCATE instead and we talked about using the drop/reuse storage clauses. He performed a TRUNCATE...REUSE STORAGE and the same select and it was night and day in terms of performance. If the allocated space isn't being released in this case also, why is there such a performance difference between the two? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vergara, Michael (TEM) INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Delete followed by Select Count(1) - SLOW
HWM, oracle will search up to the high water mark when doing a count, like you proposed, so if you're gonna delete all of the rows anyways, save time(now and later) by doing the truncate. joe [EMAIL PROTECTED] 08/16/01 05:32PM I have a user that deleted all of the rows in a table(i.e. 100,000), waited for it to complete, and thenran a SELECT COUNT(1) FROM table. It took a fewminutes for '0 rows' to be returned to the prompt. Thetable has ~60 extents (128k ea.). Granted, the numberof extents is excessive but it's a developmentinstance and this table is an exception.Is Oracle scanning through all of the blocks, sincethe space wasn't released, and this is the cause ofthe latency?The curious thing is that I told this user to useTRUNCATE instead and we talked about using thedrop/reuse storage clauses. He performed aTRUNCATE...REUSE STORAGE and the same select and itwas night and day in terms of performance. If theallocated space isn't being released in this casealso, why is there such a performance differencebetween the two?-w__Do You Yahoo!?Make international calls for as low as $.04/minute with Yahoo! Messengerhttp://phonecard.yahoo.com/-- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: Walter K INET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: Delete followed by Select Count(1) - SLOW
Hi Jonathan, Can you tell us a little bit more about delayed block cleanouts .. if you can Thx Deepak --- Jonathan Lewis [EMAIL PROTECTED] wrote: Apart from the comments other posters have made about truncate and the HWM, bear in mind that a scan after deleting 100,000 rows would probably be doing a lot of delayed block cleanout - resulting in plenty of redo log, and possibly a lot of dbwr activity. Jonathan Lewis Seminars on getting the best out of Oracle Last few places available for Sept 10th/11th See http://www.jlcomp.demon.co.uk/seminar.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 16 August 2001 21:46 |I have a user that deleted all of the rows in a table |(i.e. 100,000), waited for it to complete, and then |ran a SELECT COUNT(1) FROM table. It took a few |minutes for '0 rows' to be returned to the prompt. The |table has ~60 extents (128k ea.). Granted, the number |of extents is excessive but it's a development |instance and this table is an exception. | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deepak Thapliyal INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Delete followed by Select Count(1) - SLOW
truncate resets the highwater mark delete does not, at least not until you have done the count... at that point the highwater mark is reset From: Walter K [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Delete followed by Select Count(1) - SLOW Date: Thu, 16 Aug 2001 13:32:50 -0800 I have a user that deleted all of the rows in a table (i.e. 100,000), waited for it to complete, and then ran a SELECT COUNT(1) FROM table. It took a few minutes for '0 rows' to be returned to the prompt. The table has ~60 extents (128k ea.). Granted, the number of extents is excessive but it's a development instance and this table is an exception. Is Oracle scanning through all of the blocks, since the space wasn't released, and this is the cause of the latency? The curious thing is that I told this user to use TRUNCATE instead and we talked about using the drop/reuse storage clauses. He performed a TRUNCATE...REUSE STORAGE and the same select and it was night and day in terms of performance. If the allocated space isn't being released in this case also, why is there such a performance difference between the two? -w __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
