RE: multiple extents are OK, dagnabbit!
Sorry Jeremiah, I don't have a clue... got the same error (after the same 2 hours) after purging the shared pool; there was no activity at all on the database, so I thought about increasing the size of the shared pool (~10.5MB) but had a need, and the option, of simply replacing the database with a backup. SQL alter tablespace USR2 coalesce * ERROR at line 1: ORA-04031: unable to allocate 4180 bytes of shared memory (shared pool,unknown object,sga heap,state objects) -Original Message- Sent: Wednesday, January 23, 2002 11:56 AM To: Multiple recipients of list ORACLE-L On Tue, 22 Jan 2002, Wiegand, Kurt wrote: sort of on the subject.I once had a table with ~88000 extents (most 1 block!) it took 8 hours to delete and a subsequent coalesce ran for 2 hours before failing as it ran out of shared memory(8.1.5). Kurt, What component of the SGA becomes exhausted by a long-running coalesce? -- Jeremiah Wilton http://www.speakeasy.net/~jwilton -Original Message- Sent: Tuesday, January 22, 2002 11:55 AM To: Multiple recipients of list ORACLE-L The problem arose in the catalog upgrade script. It would never return. My diary says we let one attempt run for 36 hours. The process showed CPU usage and I/O but nothing happened. Some of the Oracle guys figured the problem was with the $fet (or whatever tables hold the extent info, I never bother with the internals of the data dictionary) having problems while being restructured. Once the tables were changed from 40K to 500M extents the upgrade took less than 2 hours. One of the suggestions I did not use was to edit sql.bsq to provide much larger extents for the table holding the extent info. Even though I do this for the SOURCE$ table I am a big fan of the KISS principle and rebuilding the tables needed to be done anyways. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton 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: Wiegand, Kurt 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: multiple extents are OK, dagnabbit!
Kurt, If you're on 8.1.6.3, 8.1.7.0.0 or 8.1.7.1.0 this sounds suspiciouslly like either bug 1640583 or bug 1397603, both of which are fixed in 8.1.7.2+ The workaround for bug 1397603 is to set _db_handles_cached = 0 in the init.ora. HTH, -- Anita --- Wiegand, Kurt [EMAIL PROTECTED] wrote: Sorry Jeremiah, I don't have a clue... got the same error (after the same 2 hours) after purging the shared pool; there was no activity at all on the database, so I thought about increasing the size of the shared pool (~10.5MB) but had a need, and the option, of simply replacing the database with a backup. SQL alter tablespace USR2 coalesce * ERROR at line 1: ORA-04031: unable to allocate 4180 bytes of shared memory (shared pool,unknown object,sga heap,state objects) -Original Message- Sent: Wednesday, January 23, 2002 11:56 AM To: Multiple recipients of list ORACLE-L On Tue, 22 Jan 2002, Wiegand, Kurt wrote: sort of on the subject.I once had a table with ~88000 extents (most 1 block!) it took 8 hours to delete and a subsequent coalesce ran for 2 hours before failing as it ran out of shared memory(8.1.5). Kurt, What component of the SGA becomes exhausted by a long-running coalesce? -- Jeremiah Wilton http://www.speakeasy.net/~jwilton -Original Message- Sent: Tuesday, January 22, 2002 11:55 AM To: Multiple recipients of list ORACLE-L The problem arose in the catalog upgrade script. It would never return. My diary says we let one attempt run for 36 hours. The process showed CPU usage and I/O but nothing happened. Some of the Oracle guys figured the problem was with the $fet (or whatever tables hold the extent info, I never bother with the internals of the data dictionary) having problems while being restructured. Once the tables were changed from 40K to 500M extents the upgrade took less than 2 hours. One of the suggestions I did not use was to edit sql.bsq to provide much larger extents for the table holding the extent info. Even though I do this for the SOURCE$ table I am a big fan of the KISS principle and rebuilding the tables needed to be done anyways. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton 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: Wiegand, Kurt 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!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: A. Bardeen 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: multiple extents are OK, dagnabbit!
On the topic, I once had a tablespace with 300,000+ free extents and 0 used extents. We executed a drop tablespace command, and looking at fet$ and the rate at which it was dropping extents from the table, we estimated it would take us 64 hours. This was on a 7.3.4 db, and we thought it better to trash the database, and recreate. That was much much faster ;-) I once remember reading an artice at Jonathan Lewis site, which basically talked of stopping smon from coalescing, deleting all the rows from fet$ and adding one row for all the free extents. Of course, this was not supported by Oracle. Did anyone from this list ever really try that? Just curious. Raj Wiegand, Kurt [EMAIL PROTECTED]@fatcity.com on 01/24/2002 06:45:17 AM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Sorry Jeremiah, I don't have a clue... got the same error (after the same 2 hours) after purging the shared pool; there was no activity at all on the database, so I thought about increasing the size of the shared pool (~10.5MB) but had a need, and the option, of simply replacing the database with a backup. SQL alter tablespace USR2 coalesce * ERROR at line 1: ORA-04031: unable to allocate 4180 bytes of shared memory (shared pool,unknown object,sga heap,state objects) -Original Message- Sent: Wednesday, January 23, 2002 11:56 AM To: Multiple recipients of list ORACLE-L On Tue, 22 Jan 2002, Wiegand, Kurt wrote: sort of on the subject.I once had a table with ~88000 extents (most 1 block!) it took 8 hours to delete and a subsequent coalesce ran for 2 hours before failing as it ran out of shared memory(8.1.5). Kurt, What component of the SGA becomes exhausted by a long-running coalesce? -- Jeremiah Wilton http://www.speakeasy.net/~jwilton -Original Message- Sent: Tuesday, January 22, 2002 11:55 AM To: Multiple recipients of list ORACLE-L The problem arose in the catalog upgrade script. It would never return. My diary says we let one attempt run for 36 hours. The process showed CPU usage and I/O but nothing happened. Some of the Oracle guys figured the problem was with the $fet (or whatever tables hold the extent info, I never bother with the internals of the data dictionary) having problems while being restructured. Once the tables were changed from 40K to 500M extents the upgrade took less than 2 hours. One of the suggestions I did not use was to edit sql.bsq to provide much larger extents for the table holding the extent info. Even though I do this for the SOURCE$ table I am a big fan of the KISS principle and rebuilding the tables needed to be done anyways. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton 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: Wiegand, Kurt 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: 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: multiple extents are OK, dagnabbit!
I hit 1397603 (think that was the one) and with it you completely lose service to the database. By the way, my understanding is that purging the shared pool when you hit 4031 errors is not always going to solve the problem because if there was SQL available to age out Oracle would do it. I run this query which will return the age of the SQL in the shared pool. If you see 30-50% of the statements getting aged out within 10-30 minutes and you have a high parse rate it might just be your shared pool is too small to handle the load. If you were stating that your shared pool was going to be increased to 10.5 MB, that is very small. By the time your data dictionary et al. gets loaded there is not much room for SQL. Check V$SGASTAT for space used by SQL. You can modify the SQL to round to a more precise time period if you like. - Ethan select sql_statements, hours_in_pool, round(sql_statements/total_statements*100,0) percent_of_total from ( select count(*) sql_statements, round((sysdate-(to_date(first_load_time, '-mm-dd/hh24:mi:ss')))* 1440 / 60,0) hours_in_pool, total_statements from v$sqlarea a, (select count(*) total_statements from v$sqlarea) b group by round((sysdate-(to_date(first_load_time, '-mm-dd/hh24:mi:ss')))* 1440 / 60,0), total_statements) where round(sql_statements/total_statements*100,0) 0; -Original Message- Sent: Thursday, January 24, 2002 7:45 AM To: Multiple recipients of list ORACLE-L Kurt, If you're on 8.1.6.3, 8.1.7.0.0 or 8.1.7.1.0 this sounds suspiciouslly like either bug 1640583 or bug 1397603, both of which are fixed in 8.1.7.2+ The workaround for bug 1397603 is to set _db_handles_cached = 0 in the init.ora. HTH, -- Anita --- Wiegand, Kurt [EMAIL PROTECTED] wrote: Sorry Jeremiah, I don't have a clue... got the same error (after the same 2 hours) after purging the shared pool; there was no activity at all on the database, so I thought about increasing the size of the shared pool (~10.5MB) but had a need, and the option, of simply replacing the database with a backup. SQL alter tablespace USR2 coalesce * ERROR at line 1: ORA-04031: unable to allocate 4180 bytes of shared memory (shared pool,unknown object,sga heap,state objects) -Original Message- Sent: Wednesday, January 23, 2002 11:56 AM To: Multiple recipients of list ORACLE-L On Tue, 22 Jan 2002, Wiegand, Kurt wrote: sort of on the subject.I once had a table with ~88000 extents (most 1 block!) it took 8 hours to delete and a subsequent coalesce ran for 2 hours before failing as it ran out of shared memory(8.1.5). Kurt, What component of the SGA becomes exhausted by a long-running coalesce? -- Jeremiah Wilton http://www.speakeasy.net/~jwilton -Original Message- Sent: Tuesday, January 22, 2002 11:55 AM To: Multiple recipients of list ORACLE-L The problem arose in the catalog upgrade script. It would never return. My diary says we let one attempt run for 36 hours. The process showed CPU usage and I/O but nothing happened. Some of the Oracle guys figured the problem was with the $fet (or whatever tables hold the extent info, I never bother with the internals of the data dictionary) having problems while being restructured. Once the tables were changed from 40K to 500M extents the upgrade took less than 2 hours. One of the suggestions I did not use was to edit sql.bsq to provide much larger extents for the table holding the extent info. Even though I do this for the SOURCE$ table I am a big fan of the KISS principle and rebuilding the tables needed to be done anyways. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton 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: Wiegand, Kurt 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
Re: multiple extents are OK, dagnabbit!
I wonder why the upgrade scripts were rebuilding the FET$, UET$ tables. When you mention sql.bsq -- that applies only when you CREATE the database. Did you CREATE and IMPORT to do the upgrade ? IMPORT would certainly be active on FET$ and UET$. Hemant K Chitale Principal DBA Chartered Semiconductor Manufacturing Ltd Dave Morgan [EMAIL PROTECTED] 23/01/2002 12:55 AM Sent by: [EMAIL PROTECTED] Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: (bcc: CHITALE Hemant Krishnarao/Prin DBA/CSM/ST Group) Subject: Re: multiple extents are OK, dagnabbit! Hi Jeremiah, The problem arose in the catalog upgrade script. It would never return. My diary says we let one attempt run for 36 hours. The process showed CPU usage and I/O but nothing happened. Some of the Oracle guys figured the problem was with the $fet (or whatever tables hold the extent info, I never bother with the internals of the data dictionary) having problems while being restructured. Once the tables were changed from 40K to 500M extents the upgrade took less than 2 hours. One of the suggestions I did not use was to edit sql.bsq to provide much larger extents for the table holding the extent info. Even though I do this for the SOURCE$ table I am a big fan of the KISS principle and rebuilding the tables needed to be done anyways. HTH Dave Can you elaborate on exactly what happened? 8.1.5 to 8.1.6 is just a catalog script and a binary change. What error did you encounter, and at which step in the upgrade? Extents should not matter in an upgrade. -- Dave Morgan DBA, Cybersurf Office: 403 777 2000 ext 284 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dave Morgan 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: 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: multiple extents are OK, dagnabbit!
Hi Jeremiah, The problem arose in the catalog upgrade script. It would never return. My diary says we let one attempt run for 36 hours. The process showed CPU usage and I/O but nothing happened. Some of the Oracle guys figured the problem was with the $fet (or whatever tables hold the extent info, I never bother with the internals of the data dictionary) having problems while being restructured. Once the tables were changed from 40K to 500M extents the upgrade took less than 2 hours. One of the suggestions I did not use was to edit sql.bsq to provide much larger extents for the table holding the extent info. Even though I do this for the SOURCE$ table I am a big fan of the KISS principle and rebuilding the tables needed to be done anyways. HTH Dave Can you elaborate on exactly what happened? 8.1.5 to 8.1.6 is just a catalog script and a binary change. What error did you encounter, and at which step in the upgrade? Extents should not matter in an upgrade. -- Dave Morgan DBA, Cybersurf Office: 403 777 2000 ext 284 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dave Morgan 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: multiple extents are OK, dagnabbit!
sort of on the subject.I once had a table with ~88000 extents (most 1 block!) it took 8 hours to delete and a subsequent coalesce ran for 2 hours before failing as it ran out of shared memory(8.1.5). -Original Message- Sent: Tuesday, January 22, 2002 11:55 AM To: Multiple recipients of list ORACLE-L Hi Jeremiah, The problem arose in the catalog upgrade script. It would never return. My diary says we let one attempt run for 36 hours. The process showed CPU usage and I/O but nothing happened. Some of the Oracle guys figured the problem was with the $fet (or whatever tables hold the extent info, I never bother with the internals of the data dictionary) having problems while being restructured. Once the tables were changed from 40K to 500M extents the upgrade took less than 2 hours. One of the suggestions I did not use was to edit sql.bsq to provide much larger extents for the table holding the extent info. Even though I do this for the SOURCE$ table I am a big fan of the KISS principle and rebuilding the tables needed to be done anyways. HTH Dave Can you elaborate on exactly what happened? 8.1.5 to 8.1.6 is just a catalog script and a binary change. What error did you encounter, and at which step in the upgrade? Extents should not matter in an upgrade. -- Dave Morgan DBA, Cybersurf Office: 403 777 2000 ext 284 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dave Morgan 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: Wiegand, Kurt 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: multiple extents are OK, dagnabbit!
Hi All, Actually, in extreme cases ( 87000 in my case, and I had 12 tables like that) it can cause problems with upgrading. Not sure what, but we had to do CTAS into new tables with much larger extents to do the upgrade from 8.1.5 to 8.1.6 here. Had Oracle support and consultants baffled also I still like to keep the number of extents below 500, but I'm paranoid :) Dave Rachel wrote: Snip There really is NO reason to worry about large numbers of extents these days. I mean, I wouldn't want to really test the unlimited ability but other than that, there is no problem. -- Dave Morgan DBA, Cybersurf Office: 403 777 2000 ext 284 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dave Morgan 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: multiple extents are OK, dagnabbit!
And I was worried about 20 to 60 extents. :) But I do have one question, if a table has multiple extents, 20 extents at 1Mb each, and they are contiguous, is that equal to 1 extent of 20Mb big ?? Does oracle have to work harder to get those 20 extents ? (okay two questions) Darren -Original Message- Sent: January 21, 2002 8:51 AM To: Multiple recipients of list ORACLE-L Hi All, Actually, in extreme cases ( 87000 in my case, and I had 12 tables like that) it can cause problems with upgrading. Not sure what, but we had to do CTAS into new tables with much larger extents to do the upgrade from 8.1.5 to 8.1.6 here. Had Oracle support and consultants baffled also I still like to keep the number of extents below 500, but I'm paranoid :) Dave Rachel wrote: Snip There really is NO reason to worry about large numbers of extents these days. I mean, I wouldn't want to really test the unlimited ability but other than that, there is no problem. -- Dave Morgan DBA, Cybersurf Office: 403 777 2000 ext 284 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dave Morgan 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: Browett, Darren 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: multiple extents are OK, dagnabbit!
Can you elaborate on exactly what happened? 8.1.5 to 8.1.6 is just a catalog script and a binary change. What error did you encounter, and at which step in the upgrade? Extents should not matter in an upgrade. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Mon, 21 Jan 2002, Dave Morgan wrote: Actually, in extreme cases ( 87000 in my case, and I had 12 tables like that) it can cause problems with upgrading. Not sure what, but we had to do CTAS into new tables with much larger extents to do the upgrade from 8.1.5 to 8.1.6 here. Had Oracle support and consultants baffled also I still like to keep the number of extents below 500, but I'm paranoid :) Rachel wrote: Snip There really is NO reason to worry about large numbers of extents these days. I mean, I wouldn't want to really test the unlimited ability but other than that, there is no problem. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton 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: multiple extents are OK, dagnabbit!
Hey Jeremiah, add in something to the last paragraph about how using LMTs will obviate the problem in truncating tables with lots of extents :) There really is NO reason to worry about large numbers of extents these days. I mean, I wouldn't want to really test the unlimited ability but other than that, there is no problem. --- Jeremiah Wilton [EMAIL PROTECTED] wrote: Here's my swing at it: http://www.speakeasy.org/~jwilton/oracle/lots-of-extents.html -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Thu, 17 Jan 2002, Cunningham, Gerald wrote: I'm trying to convince a client that multiple extents for a table will not hurt their performance. It's a PeopleSoft app, and PeopleSoft is telling them that they need to reorg any object with greater than 10 extents (even indexes). This Oracle 8.1.6. I've referenced the How to Stop Defragmenting and Start Living: The Definitive Word on Fragmentation white paper by Bhaskar Himatsingka and Juan Loaiza of Oracle. That didn't convince them. I tried to explain that Oracle reads BUFFERS and not extents, etc., but that didn't work. I'm about to open a vein. Does anybody have any references that they can point me to? (Something from PeopleSoft would be ideal, though I would be suprised if it existed.) I read a rant on somebody's web site a while back that was really good, but alas I cannot remember his name or URL. (I blame my kids for my failing memory). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton 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!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ -- 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).
RE: multiple extents are OK, dagnabbit!
Jerry, Tell the client that you will be HAPPY to reorg the tables and indexes over 10 extents. It will cost X dollars and take Y hours of downtime/slowdown. Insert inappropriately huge numbers into X and Y. It's amazing how quickly people will change their minds when you talk hours and dollars. Some people don't see the light until they are on fire. Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Cunningham, Gerald [SMTP:[EMAIL PROTECTED]] Hi there - I'm trying to convince a client that multiple extents for a table will not hurt their performance. It's a PeopleSoft app, and PeopleSoft is telling them that they need to reorg any object with greater than 10 extents (even indexes). This Oracle 8.1.6. I've referenced the How to Stop Defragmenting and Start Living: The Definitive Word on Fragmentation white paper by Bhaskar Himatsingka and Juan Loaiza of Oracle. That didn't convince them. I tried to explain that Oracle reads BUFFERS and not extents, etc., but that didn't work. I'm about to open a vein. Does anybody have any references that they can point me to? (Something from PeopleSoft would be ideal, though I would be suprised if it existed.) I read a rant on somebody's web site a while back that was really good, but alas I cannot remember his name or URL. (I blame my kids for my failing memory). Thanks! - Jerry -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Whittle Jerome Contr NCI 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: multiple extents are OK, dagnabbit!
Jerry - You could approach the issue a little more subtly. Here is an Oracle paper where Oracle recommends locally managed tablespaces and uniform extents. If you can point out to them that you are a modern DBA that is keeping up with new Oracle features, I think that would be persuasive. http://www.oracle.com/collateral/o8i_high_avail_enhance_fo.pdf http://www.oracle.com/collateral/o8i_high_avail_enhance_fo.pdf Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, January 17, 2002 7:40 PM To: '[EMAIL PROTECTED]' Jerry - Maybe I'm missing something here. Since you refer to them as a client, you must have a consulting relationship with them - right? So if you rebuild the tables, you get more money - right? So you rebuild the tables, the client is happy, and you are a little wealthier - right? Or maybe you are too wealthy as it is, with more work than you can handle. Then you solicit help from others on this list to be your trusted assistant that will rebuild the tables, explaining to the client that you are overqualified for such a mundane task. I'm teasing you, but the older I get, the more I see that sometimes we computer folk are our own worst enemy. There is such a thing as being technically right but losing the client anyway. By the way, I totally agree with you on the multiple extents issue, but since Oracle was nice enough to post the paper Stop Defragmenting . . on their web site, that seems to have convinced the manager that wanted to hear something from the vendor before he would believe it. In my case I'm an employee, so it would just cut into my weekends. Good luck, but don't forget the softer skills. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, January 17, 2002 3:46 PM To: Multiple recipients of list ORACLE-L Hi there - I'm trying to convince a client that multiple extents for a table will not hurt their performance. It's a PeopleSoft app, and PeopleSoft is telling them that they need to reorg any object with greater than 10 extents (even indexes). This Oracle 8.1.6. I've referenced the How to Stop Defragmenting and Start Living: The Definitive Word on Fragmentation white paper by Bhaskar Himatsingka and Juan Loaiza of Oracle. That didn't convince them. I tried to explain that Oracle reads BUFFERS and not extents, etc., but that didn't work. I'm about to open a vein. Does anybody have any references that they can point me to? (Something from PeopleSoft would be ideal, though I would be suprised if it existed.) I read a rant on somebody's web site a while back that was really good, but alas I cannot remember his name or URL. (I blame my kids for my failing memory). Thanks! - Jerry -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS 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: multiple extents are OK, dagnabbit!
Hello, My 2 cents: It does make a difference to reorg, esp. when done thoughtfully, with a specific goal in mind. For example, if you have a order_log table that started with first extent 1MB and next extent 1MB, and this table has grown in size to say, 10 million rows (business is good), you would have hundreds of extents, and each of those new extents took some time to extend that would have been avoided if you had started with 100MB first and 25MB next. Indexes take a far worse performance hit. You also expose yourself to other issues (fragmentation, full table scans (yuck) run slower, table drops run slower, more extent overhead, recovery time runs slower, risk of failure increases). Thank you, Paul Sherman DBA voice - 781-501-4143 (office) fax- 781-278-8341 (office) email - [EMAIL PROTECTED] -Original Message- Sent: Friday, January 18, 2002 8:41 AM To: Multiple recipients of list ORACLE-L Jerry, Tell the client that you will be HAPPY to reorg the tables and indexes over 10 extents. It will cost X dollars and take Y hours of downtime/slowdown. Insert inappropriately huge numbers into X and Y. It's amazing how quickly people will change their minds when you talk hours and dollars. Some people don't see the light until they are on fire. Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Cunningham, Gerald [SMTP:[EMAIL PROTECTED]] Hi there - I'm trying to convince a client that multiple extents for a table will not hurt their performance. It's a PeopleSoft app, and PeopleSoft is telling them that they need to reorg any object with greater than 10 extents (even indexes). This Oracle 8.1.6. I've referenced the How to Stop Defragmenting and Start Living: The Definitive Word on Fragmentation white paper by Bhaskar Himatsingka and Juan Loaiza of Oracle. That didn't convince them. I tried to explain that Oracle reads BUFFERS and not extents, etc., but that didn't work. I'm about to open a vein. Does anybody have any references that they can point me to? (Something from PeopleSoft would be ideal, though I would be suprised if it existed.) I read a rant on somebody's web site a while back that was really good, but alas I cannot remember his name or URL. (I blame my kids for my failing memory). Thanks! - Jerry -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Whittle Jerome Contr NCI 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: Sherman, Paul R. 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: multiple extents are OK, dagnabbit!
Paul, With LMT's. uniform extents sizes and properly place objects I think you avoid most of the situations you described. Cary's paper at hotsos.com shows that in a system with a lot of activity your disk head is never going to fulfill the request for a full tablescan in a single operation anyway because of all of the competing requests. Also it shows that the probability of in another request in fact being beneficial to your processes disk read goes up. 9 out of 10 DBA's agree, LMT's, uniform extents sizes and objects with 1000 extents are just fine for most databases. These are always general rules and everyone's situation is different. Perhaps you want to keep everything under 100 extents for a DSS DB with few users, I would say that would be fine but to still stay with uniform LMT's as they are easier to manage. - Ethan -Original Message- Sent: Friday, January 18, 2002 10:41 AM To: Multiple recipients of list ORACLE-L Hello, My 2 cents: It does make a difference to reorg, esp. when done thoughtfully, with a specific goal in mind. For example, if you have a order_log table that started with first extent 1MB and next extent 1MB, and this table has grown in size to say, 10 million rows (business is good), you would have hundreds of extents, and each of those new extents took some time to extend that would have been avoided if you had started with 100MB first and 25MB next. Indexes take a far worse performance hit. You also expose yourself to other issues (fragmentation, full table scans (yuck) run slower, table drops run slower, more extent overhead, recovery time runs slower, risk of failure increases). Thank you, Paul Sherman DBA voice - 781-501-4143 (office) fax- 781-278-8341 (office) email - [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan 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: multiple extents are OK, dagnabbit!
Title: Message Search Tom Kytes asktom.oracle.com and there is also paper athotsos.com. Also check out http://www.speakeasy.org/~jwilton/oracle/lots-of-extents.html. - Ethan -Original Message-From: Cunningham, Gerald [mailto:[EMAIL PROTECTED]]Sent: Thursday, January 17, 2002 3:46 PMTo: Multiple recipients of list ORACLE-LSubject: multiple extents are OK, dagnabbit! Hi there - I'm trying to convince a client that multiple extents for a table will not hurt their performance. It's a PeopleSoft app, and PeopleSoft is telling them that they need to reorg any object with greater than 10 extents (even indexes). This Oracle 8.1.6. I've referenced the "How to Stop Defragmenting and Start Living: The Definitive Word on Fragmentation" white paper by Bhaskar Himatsingka and Juan Loaiza of Oracle. That didn't convince them. I tried to explain that Oracle reads BUFFERS and not extents, etc., but that didn't work. I'm about to open a vein. Does anybody have any references that they can point me to? (Something from PeopleSoft would be ideal, though I would be suprised if it existed.) I read a rant on somebody's web site a while back that was really good, but alas I cannot remember his name or URL. (I blame my kids for my failing memory). Thanks! - Jerry
Re: multiple extents are OK, dagnabbit!
Here's my swing at it: http://www.speakeasy.org/~jwilton/oracle/lots-of-extents.html -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Thu, 17 Jan 2002, Cunningham, Gerald wrote: I'm trying to convince a client that multiple extents for a table will not hurt their performance. It's a PeopleSoft app, and PeopleSoft is telling them that they need to reorg any object with greater than 10 extents (even indexes). This Oracle 8.1.6. I've referenced the How to Stop Defragmenting and Start Living: The Definitive Word on Fragmentation white paper by Bhaskar Himatsingka and Juan Loaiza of Oracle. That didn't convince them. I tried to explain that Oracle reads BUFFERS and not extents, etc., but that didn't work. I'm about to open a vein. Does anybody have any references that they can point me to? (Something from PeopleSoft would be ideal, though I would be suprised if it existed.) I read a rant on somebody's web site a while back that was really good, but alas I cannot remember his name or URL. (I blame my kids for my failing memory). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton 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: multiple extents are OK, dagnabbit!
Title: Message Jerry, If they want to pay you to reduce their extents, then let 'em! ;-) "A fool and his money are soon parted." If they employ youand want you to work weekends on this, then it's worth the effort to educate them. I'm surprised an official Oracle white paper didn't convince them. You may just be out of luck - adamant, entrenched misinformation is sometimes difficult to dislodge. If my anecdotal situation could be of any help, here it is. We just moved our production 8.1.6.0.0 database to 8.1.7.2.5 on a new, but almost identical server. Old server's OS was Windows 2000 Server with Service Pack 2 - new server, the same. Old server had dual 550MHz Xeon CPUs - new server, the same. Old server had 2GB RAM - new server has 4GB RAM (of which Oracle can only use 2GB anyway). Old server had eighteen 36GB drives - new server has twenty 36GB drives. In both cases configured as JBOD (Just a Bunch Of Drives - no RAID, no mirroring, no striping of any kind). Our 6 documents tableseach had (andhas) its own drive andeachhad (and has) about 2 million rows. The out-of-line CLOB documents take up about 20-30GB for each table. Each of those segments had between 20,000 and 30,000 1MB extents. For the year we operated that way, we never had a problem with performance, even with a full interMedia Text index on the CLOB column. When we moved theDBto 8.1.7.2.5, I pre-created those tables with 100MB extents for the CLOB segments before I imported the documents. So, now we're down to a few hundred extents per segment, instead of tens of thousands. It hasn't made any noticeable difference on performance. If numbers of extents really mattered, a 100 to 1 reduction would have made an impact - it didn't. Whatdid make a difference was spreading the main token table (DR$...$I) of the interMedia Text index across 3 drives, instead of one. Distributing I/O has significant impact. Number of extents per segment has close to zero impact. The Oracle white paper is dead-on accurate. Hope my experience helps convince your boneheaded clients. ;-) Jack Jack C. ApplewhiteDatabase Administrator/DeveloperOCP Oracle8 DBAiNetProfit, Inc.Austin, Texaswww.iNetProfit.com[EMAIL PROTECTED](512)327-9068 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Cunningham, GeraldSent: Thursday, January 17, 2002 3:46 PMTo: Multiple recipients of list ORACLE-LSubject: multiple extents are OK, dagnabbit! Hi there - I'm trying to convince a client that multiple extents for a table will not hurt their performance. It's a PeopleSoft app, and PeopleSoft is telling them that they need to reorg any object with greater than 10 extents (even indexes). This Oracle 8.1.6. I've referenced the "How to Stop Defragmenting and Start Living: The Definitive Word on Fragmentation" white paper by Bhaskar Himatsingka and Juan Loaiza of Oracle. That didn't convince them. I tried to explain that Oracle reads BUFFERS and not extents, etc., but that didn't work. I'm about to open a vein. Does anybody have any references that they can point me to? (Something from PeopleSoft would be ideal, though I would be suprised if it existed.) I read a rant on somebody's web site a while back that was really good, but alas I cannot remember his name or URL. (I blame my kids for my failing memory). Thanks! - Jerry
RE: multiple extents are OK, dagnabbit!
Jerry - Maybe I'm missing something here. Since you refer to them as a client, you must have a consulting relationship with them - right? So if you rebuild the tables, you get more money - right? So you rebuild the tables, the client is happy, and you are a little wealthier - right? Or maybe you are too wealthy as it is, with more work than you can handle. Then you solicit help from others on this list to be your trusted assistant that will rebuild the tables, explaining to the client that you are overqualified for such a mundane task. I'm teasing you, but the older I get, the more I see that sometimes we computer folk are our own worst enemy. There is such a thing as being technically right but losing the client anyway. By the way, I totally agree with you on the multiple extents issue, but since Oracle was nice enough to post the paper Stop Defragmenting . . on their web site, that seems to have convinced the manager that wanted to hear something from the vendor before he would believe it. In my case I'm an employee, so it would just cut into my weekends. Good luck, but don't forget the softer skills. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, January 17, 2002 3:46 PM To: Multiple recipients of list ORACLE-L Hi there - I'm trying to convince a client that multiple extents for a table will not hurt their performance. It's a PeopleSoft app, and PeopleSoft is telling them that they need to reorg any object with greater than 10 extents (even indexes). This Oracle 8.1.6. I've referenced the How to Stop Defragmenting and Start Living: The Definitive Word on Fragmentation white paper by Bhaskar Himatsingka and Juan Loaiza of Oracle. That didn't convince them. I tried to explain that Oracle reads BUFFERS and not extents, etc., but that didn't work. I'm about to open a vein. Does anybody have any references that they can point me to? (Something from PeopleSoft would be ideal, though I would be suprised if it existed.) I read a rant on somebody's web site a while back that was really good, but alas I cannot remember his name or URL. (I blame my kids for my failing memory). Thanks! - Jerry -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS 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).
