RE: Tablespace Design
Hi Pawan, Having smaller files - easier to move a db file to diffrent drives if required - quicker to recover a single file if necessary Having larger files - easier to manage due to smaller no. of files My personal choice: I am comforatble with file size around 5GB. Long -Original Message- Sent: Wednesday, 28 August 2002 6:13 PM To: Multiple recipients of list ORACLE-L Hi DBA gurus, One of the application is going live .At presenr data is 15GB and will grow more by 10 GB in year time. What is the right approach of tablespace design having more files of smaller size(10 files of 2 GB ) or less file of bigger size (3 files of 8 gb ). No tables in this application is partitioned all the files will be on a single disk I am Orcale 8.1.7 on Win 2K. 2) Following is output of my v$waitstat . Kinly give som suggestions CLASS COUNT TIME -- -- -- data block 46251 0 sort block 0 0 save undo block 0 0 segment header 15 0 save undo header0 0 free list 0 0 extent map 0 0 bitmap block0 0 bitmap index block 0 0 unused 0 0 system undo header 0 0 system undo block 0 0 undo header 1225 0 undo block 55 0 Regards Pawan Dalmia -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pawan Dalmia 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: Tablespace Design
Design tablespaces with more files of smaller size (2Gb is a nice size). Use this rule unless the number of datafiles in the database will get unmanageable. You describe a 15Gb database that will grow to 25Gb in a year -- this is not a large database, stick with 2Gb or smaller datafiles. The advantage of smaller datafiles comes with backup/recovery. In the event that a single datafile needs recovery, a smaller datafile can be recovered more quickly than a larger datafile. Kevin Kennedy First Point Energy Corporation If you take RAC out of Oracle you get OLE! What can this mean? -Original Message- Sent: Wednesday, August 28, 2002 1:13 AM To: Multiple recipients of list ORACLE-L Hi DBA gurus, One of the application is going live .At presenr data is 15GB and will grow more by 10 GB in year time. What is the right approach of tablespace design having more files of smaller size(10 files of 2 GB ) or less file of bigger size (3 files of 8 gb ). No tables in this application is partitioned all the files will be on a single disk I am Orcale 8.1.7 on Win 2K. 2) Following is output of my v$waitstat . Kinly give som suggestions CLASS COUNT TIME -- -- -- data block 46251 0 sort block 0 0 save undo block 0 0 segment header 15 0 save undo header0 0 free list 0 0 extent map 0 0 bitmap block0 0 bitmap index block 0 0 unused 0 0 system undo header 0 0 system undo block 0 0 undo header 1225 0 undo block 55 0 Regards Pawan Dalmia -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pawan Dalmia 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: kkennedy 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: Tablespace Design
Pawan - The gurus are sleeping in this morning, so you're stuck with the "aspiring guru". According to Oracle's documentation, v$waitstat breaks buffer waits down by block class. You may be better off starting with v$system_event and v$session_event and if this shows a lot of buffer busy waits, then key in on v$waitstat. You might get a copy of Gaja's book "Oracle Performance Tuning 101". Then you'll really impress him. If you have everything on a single drive, tablespace design probably isn't going to buy you much in terms of performance. Myself, I would tend to tune the memory buffer cache first in this situation. You may also want to consider adding more drives, depending on how you plan to use this database. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, August 28, 2002 3:13 AM To: Multiple recipients of list ORACLE-L Hi DBA gurus, One of the application is going live .At presenr data is 15GB and will grow more by 10 GB in year time. What is the right approach of tablespace design having more files of smaller size(10 files of 2 GB ) or less file of bigger size (3 files of 8 gb ). No tables in this application is partitioned all the files will be on a single disk I am Orcale 8.1.7 on Win 2K. 2) Following is output of my v$waitstat . Kinly give som suggestions CLASS COUNT TIME -- -- -- data block 46251 0 sort block 0 0 save undo block 0 0 segment header 15 0 save undo header0 0 free list 0 0 extent map 0 0 bitmap block0 0 bitmap index block 0 0 unused 0 0 system undo header 0 0 system undo block 0 0 undo header 1225 0 undo block 55 0 Regards Pawan Dalmia -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pawan Dalmia 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: 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).
Tablespace Design
Hi DBA gurus, One of the application is going live .At presenr data is 15GB and will grow more by 10 GB in year time. What is the right approach of tablespace design having more files of smaller size(10 files of 2 GB ) or less file of bigger size (3 files of 8 gb ). No tables in this application is partitioned all the files will be on a single disk I am Orcale 8.1.7 on Win 2K. 2) Following is output of my v$waitstat . Kinly give som suggestions CLASS COUNT TIME -- -- -- data block 46251 0 sort block 0 0 save undo block 0 0 segment header 15 0 save undo header0 0 free list 0 0 extent map 0 0 bitmap block0 0 bitmap index block 0 0 unused 0 0 system undo header 0 0 system undo block 0 0 undo header 1225 0 undo block 55 0 Regards Pawan Dalmia -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pawan Dalmia 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: Temporary Tablespace Design
Temporary tablespace also used after sorting in memory finished (for sorts small enough to not need intermediate sorts and merging) and sort_area_retained_size less then sort_area_size. Alex Hillman -Original Message- Sent: Wednesday, April 18, 2001 12:26 PM To: Multiple recipients of list ORACLE-L Well, not quite. SORT_AREA_SIZE specifies the maximum amount, in bytes, of memory to use for a sort. After the sort is complete and all that remains to do is to return the rows, the memory is released down to the size specified by SORT_AREA_RETAINED_SIZE. After the last row is returned, all memory is freed. Temporary disk is used for the intermediate sort runs only if the SORT_AREA_SIZE isn't large enough to handle the sort in memory. The SORT_AREA_RETAINED_SIZE controls the size of the read buffer which is used to maintain a portion of the sort in memory. Myself, I use 2048000 for SORT_AREA_SIZE, and 512000 for SORT_AREA_RETAINED_SIZE. >>> [EMAIL PROTECTED] 04/18/01 11:36AM >>> I think you are not correct. First - Oracle allocates memory for sorting as needed by 8K chunks up to sort_area_size. Second if your sort_area_size is large enough to do sort in memory and your sort_area_retained_size < sort_area_size oracle will dump sorted data into temporary tablespace and then read from this tablespace. So it is a tradeoff between late memory release and temporary tablespace I/O. Alex Hillman -Original Message- Sent: Wednesday, April 18, 2001 10:40 AM To: Multiple recipients of list ORACLE-L Hi John, As you mentioned, it depends on a number of factors. I believe I replied to a question about improving performance of index building after an import. Anyway, here is the reasoning behind why I said that When Oracle begins sorting, it starts with the memory equal to sort_area_retained_size value and may eventually acquire memory equal to sort_area_size. This involves at OS level, malloc (and may be some other) memory mgmt routine(s). After the sort phase is complete, Oracle will trigger memory de-allocation and the OS will go at work again. If there are servile sessions performing sorts, there can be an increased OS level memory mgmt activity (while attaining sort_area_size and releasing it back to sort_area_retained_size) . But if the server has *enough* memory keeping sort_area_retained_size equal to sort_area_size may actually help. I also assume that these days most installations have ample memory. And that the advice of using 50% (which I had heard of) of sort area size for sort area retained size should always be viewed in light of one's requirements and resource availability. But I can't stop wondering... how come it was 50% and not 10% or 20%.. so it's all relative. I have been using what I suggested, for a number of years now with no problems or complaints. Cheers ! - Kirti Deshpande Verizon Information Services http://www.superpages.com > -Original Message- > From: Hallas, John [SMTP:[EMAIL PROTECTED]] > Sent: Wednesday, April 18, 2001 6:21 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: Temporary Tablespace Design > > FOR YOUR INFORMATION > > ESIS and EPFAL are now part of Logica. The Internet email addresses of the > staff has changed to the following - [EMAIL PROTECTED] eg > [EMAIL PROTECTED] Emails using the old format will continue to be > delivered until 30th June 2001. > > > Kirti Deshpande writes > > "Also, keep SORT_AREA_RETAINED_SIZE the same as SORT_AREA_SIZE. As the > former will be used while fetching data after sort phase is completed." > > I thought best advice was to make the retained size smaller (50%?) of > sort_area_size to allow initial sort memory to be released after the first > part of the sort is managed and only the merge phase of a disk sort is > left > to do. > > I appreciate that a lot depends on the amount of memory available and the > number of concurrent processes that may be performing sorts but surely the > aim is to free memory up as soon as possible down to the > sort_area_retained_size > > Thanks > > John > > (PS I do apologise for the rubbish at the top of this mail but it is > inserted after I have sent the mail into our mail gateway) > > > > > > This e-mail and any files transmitted with it, are confidential to Logica > and are intended solely for the use of the individual or entity to whom > they are addressed. > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Hallas, John > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourse
RE: Temporary Tablespace Design
Hi Alex, Yes, Thanks for catching it. I got mixed up in explaining the memory allocation properly and left out some things. Sorry. What you say, is what happens in the very first sort as far as allocating memory in chunks (until sort_area_size is reached) is concerned. When the very first sort operation completes, 'sort area' is left in PGA (UGA for MTS) with the size equal to sort_area_retained_size (after deallocating additional memory, whenever the free() call is executed). Subsequent sort operation will start with 'sort area' of the size equal to sort_area_retained_size and start allocating memory when needed till it reaches the max allowed (sort_area_size). Keeping these two areas of the same value avoids this memory allocation/deallocation process, provided there is enough memory available to do so. Also, the sort_area_retained_size is used in the 'fetch' phase of a sort opeation. So, if the sort had to use disk, then it will help reduce the number of I/Os. This is my understanding of how sort is handled. Anyone knowing more 'internal' workings of sort, please let me (and us) know so I can get this straight. Thanks. - Kirti Deshpande Verizon Information Services http://www.superpages.com > -Original Message- > From: Hillman, Alex [SMTP:[EMAIL PROTECTED]] > Sent: Wednesday, April 18, 2001 10:37 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: Temporary Tablespace Design > > I think you are not correct. First - Oracle allocates memory for sorting > as > needed by 8K chunks up to sort_area_size. Second if your sort_area_size is > large enough to do sort in memory and your sort_area_retained_size < > sort_area_size oracle will dump sorted data into temporary tablespace and > then read from this tablespace. So it is a tradeoff between late memory > release and temporary tablespace I/O. > > Alex Hillman > > -Original Message- > Sent: Wednesday, April 18, 2001 10:40 AM > To: Multiple recipients of list ORACLE-L > > > Hi John, > As you mentioned, it depends on a number of factors. I believe I replied > to > a question about improving performance of index building after an import. > Anyway, here is the reasoning behind why I said that When Oracle > begins > sorting, it starts with the memory equal to sort_area_retained_size value > and may eventually acquire memory equal to sort_area_size. This involves > at > OS level, malloc (and may be some other) memory mgmt routine(s). After the > sort phase is complete, Oracle will trigger memory de-allocation and the > OS > will go at work again. If there are servile sessions performing sorts, > there > can be an increased OS level memory mgmt activity (while attaining > sort_area_size and releasing it back to sort_area_retained_size) . But if > the server has *enough* memory keeping sort_area_retained_size equal to > sort_area_size may actually help. I also assume that these days most > installations have ample memory. And that the advice of using 50% (which I > had heard of) of sort area size for sort area retained size should always > be > viewed in light of one's requirements and resource availability. But I > can't > stop wondering... how come it was 50% and not 10% or 20%.. so it's all > relative. I have been using what I suggested, for a number of years now > with no problems or complaints. > > Cheers ! > > - Kirti Deshpande > Verizon Information Services >http://www.superpages.com > > > -Original Message- > > From: Hallas, John [SMTP:[EMAIL PROTECTED]] > > Sent: Wednesday, April 18, 2001 6:21 AM > > To: Multiple recipients of list ORACLE-L > > Subject:RE: Temporary Tablespace Design > > > > FOR YOUR INFORMATION > > > > ESIS and EPFAL are now part of Logica. The Internet email addresses of > the > > staff has changed to the following - [EMAIL PROTECTED] eg > > [EMAIL PROTECTED] Emails using the old format will continue to be > > delivered until 30th June 2001. > > > > > > Kirti Deshpande writes > > > > "Also, keep SORT_AREA_RETAINED_SIZE the same as SORT_AREA_SIZE. As the > > former will be used while fetching data after sort phase is completed." > > > > I thought best advice was to make the retained size smaller (50%?) of > > sort_area_size to allow initial sort memory to be released after the > first > > part of the sort is managed and only the merge phase of a disk sort is > > left > > to do. > > > > I appreciate that a lot depends on the amount of memory available and > the > > number of concurrent processes that may be performing sorts but surely > th
RE: Temporary Tablespace Design
FOR YOUR INFORMATION ESIS and EPFAL are now part of Logica. The Internet email addresses of the staff has changed to the following - [EMAIL PROTECTED] eg [EMAIL PROTECTED] Emails using the old format will continue to be delivered until 30th June 2001. Tim, You are confirming my original understanding. However Kirti stated that based on your figures if a sort was used then malloc would be called 4 times to get from 512000 to 2048000. I do not know the answer for certain but Kirti's view seems logical because sort_area_size is the MAX size and it is very likely that only the retained is allocated initially and then increased in increments up to sort_area_retained_size. Any bigger and the sort is done to the temporary tablespace. John -Original Message- From: Tim Sawmiller [mailto:[EMAIL PROTECTED]] Sent: 18 April 2001 17:26 To: Multiple recipients of list ORACLE-L Subject:RE: Temporary Tablespace Design Well, not quite. SORT_AREA_SIZE specifies the maximum amount, in bytes, of memory to use for a sort. After the sort is complete and all that remains to do is to return the rows, the memory is released down to the size specified by SORT_AREA_RETAINED_SIZE. After the last row is returned, all memory is freed. Temporary disk is used for the intermediate sort runs only if the SORT_AREA_SIZE isn't large enough to handle the sort in memory. The SORT_AREA_RETAINED_SIZE controls the size of the read buffer which is used to maintain a portion of the sort in memory. Myself, I use 2048000 for SORT_AREA_SIZE, and 512000 for SORT_AREA_RETAINED_SIZE. >>> [EMAIL PROTECTED] 04/18/01 11:36AM >>> I think you are not correct. First - Oracle allocates memory for sorting as needed by 8K chunks up to sort_area_size. Second if your sort_area_size is large enough to do sort in memory and your sort_area_retained_size < sort_area_size oracle will dump sorted data into temporary tablespace and then read from this tablespace. So it is a tradeoff between late memory release and temporary tablespace I/O. Alex Hillman -Original Message- Sent: Wednesday, April 18, 2001 10:40 AM To: Multiple recipients of list ORACLE-L Hi John, As you mentioned, it depends on a number of factors. I believe I replied to a question about improving performance of index building after an import. Anyway, here is the reasoning behind why I said that When Oracle begins sorting, it starts with the memory equal to sort_area_retained_size value and may eventually acquire memory equal to sort_area_size. This involves at OS level, malloc (and may be some other) memory mgmt routine(s). After the sort phase is complete, Oracle will trigger memory de-allocation and the OS will go at work again. If there are servile sessions performing sorts, there can be an increased OS level memory mgmt activity (while attaining sort_area_size and releasing it back to sort_area_retained_size) . But if the server has *enough* memory keeping sort_area_retained_size equal to sort_area_size may actually help. I also assume that these days most installations have ample memory. And that the advice of using 50% (which I had heard of) of sort area size for sort area retained size should always be viewed in light of one's requirements and resource availability. But I can't stop wondering... how come it was 50% and not 10% or 20%.. so it's all relative. I have been using what I suggested, for a number of years now with no problems or complaints. Cheers ! - Kirti Deshpande Verizon Information Services http://www.superpages.com > -Original Message- > From: Hallas, John [SMTP:[EMAIL PROTECTED]] > Sent: Wednesday, April 18, 2001 6:21 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: Temporary Tablespace Design > > > Kirti Deshpande writes > > "Also, keep SORT_AREA_RETAINED_SIZE the same as SORT_AREA_SIZE. As the > former will be used while fetching data after sort phase is completed." > > I thought best advice was to make the retained size smaller (50%?) of >
RE: Temporary Tablespace Design
Jared, Thanks for pointing that out. Yes, the free() is not immediate. However, I was generalizing all the OS actions before & after the sort. Unfortunately I missed Steve's post on this. I will search his Web site to see if there is anything related to this. - Kirti > -Original Message- > From: Jared Still [SMTP:[EMAIL PROTECTED]] > Sent: Wednesday, April 18, 2001 10:25 AM > To: Multiple recipients of list ORACLE-L > Subject: Re: Temporary Tablespace Design > > On Wednesday 18 April 2001 07:40, you wrote: > > Hi John, > > After the sort phase is complete, Oracle will trigger memory > > de-allocation and the OS will go at work again. > > Kirti, > > I seem to recall a post from Steve Adams not too long ago about > this. As I recall, free() is not called immediately, but may be delayed. > > Know anything about that? > > Jared > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Jared Still > 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: Deshpande, Kirti 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: Temporary Tablespace Design
Well, not quite. SORT_AREA_SIZE specifies the maximum amount, in bytes, of memory to use for a sort. After the sort is complete and all that remains to do is to return the rows, the memory is released down to the size specified by SORT_AREA_RETAINED_SIZE. After the last row is returned, all memory is freed. Temporary disk is used for the intermediate sort runs only if the SORT_AREA_SIZE isn't large enough to handle the sort in memory. The SORT_AREA_RETAINED_SIZE controls the size of the read buffer which is used to maintain a portion of the sort in memory. Myself, I use 2048000 for SORT_AREA_SIZE, and 512000 for SORT_AREA_RETAINED_SIZE. >>> [EMAIL PROTECTED] 04/18/01 11:36AM >>> I think you are not correct. First - Oracle allocates memory for sorting as needed by 8K chunks up to sort_area_size. Second if your sort_area_size is large enough to do sort in memory and your sort_area_retained_size < sort_area_size oracle will dump sorted data into temporary tablespace and then read from this tablespace. So it is a tradeoff between late memory release and temporary tablespace I/O. Alex Hillman -Original Message- Sent: Wednesday, April 18, 2001 10:40 AM To: Multiple recipients of list ORACLE-L Hi John, As you mentioned, it depends on a number of factors. I believe I replied to a question about improving performance of index building after an import. Anyway, here is the reasoning behind why I said that When Oracle begins sorting, it starts with the memory equal to sort_area_retained_size value and may eventually acquire memory equal to sort_area_size. This involves at OS level, malloc (and may be some other) memory mgmt routine(s). After the sort phase is complete, Oracle will trigger memory de-allocation and the OS will go at work again. If there are servile sessions performing sorts, there can be an increased OS level memory mgmt activity (while attaining sort_area_size and releasing it back to sort_area_retained_size) . But if the server has *enough* memory keeping sort_area_retained_size equal to sort_area_size may actually help. I also assume that these days most installations have ample memory. And that the advice of using 50% (which I had heard of) of sort area size for sort area retained size should always be viewed in light of one's requirements and resource availability. But I can't stop wondering... how come it was 50% and not 10% or 20%.. so it's all relative. I have been using what I suggested, for a number of years now with no problems or complaints. Cheers ! - Kirti Deshpande Verizon Information Services http://www.superpages.com > -Original Message- > From: Hallas, John [SMTP:[EMAIL PROTECTED]] > Sent: Wednesday, April 18, 2001 6:21 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: Temporary Tablespace Design > > FOR YOUR INFORMATION > > ESIS and EPFAL are now part of Logica. The Internet email addresses of the > staff has changed to the following - [EMAIL PROTECTED] eg > [EMAIL PROTECTED] Emails using the old format will continue to be > delivered until 30th June 2001. > > > Kirti Deshpande writes > > "Also, keep SORT_AREA_RETAINED_SIZE the same as SORT_AREA_SIZE. As the > former will be used while fetching data after sort phase is completed." > > I thought best advice was to make the retained size smaller (50%?) of > sort_area_size to allow initial sort memory to be released after the first > part of the sort is managed and only the merge phase of a disk sort is > left > to do. > > I appreciate that a lot depends on the amount of memory available and the > number of concurrent processes that may be performing sorts but surely the > aim is to free memory up as soon as possible down to the > sort_area_retained_size > > Thanks > > John > > (PS I do apologise for the rubbish at the top of this mail but it is > inserted after I have sent the mail into our mail gateway) > > > > > > This e-mail and any files transmitted with it, are confidential to Logica > and are intended solely for the use of the individual or entity to whom > they are addressed. > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Hallas, John > 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 ot
Re: Temporary Tablespace Design
On Wednesday 18 April 2001 07:40, you wrote: > Hi John, > After the sort phase is complete, Oracle will trigger memory > de-allocation and the OS will go at work again. Kirti, I seem to recall a post from Steve Adams not too long ago about this. As I recall, free() is not called immediately, but may be delayed. Know anything about that? Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still 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: Temporary Tablespace Design
I think you are not correct. First - Oracle allocates memory for sorting as needed by 8K chunks up to sort_area_size. Second if your sort_area_size is large enough to do sort in memory and your sort_area_retained_size < sort_area_size oracle will dump sorted data into temporary tablespace and then read from this tablespace. So it is a tradeoff between late memory release and temporary tablespace I/O. Alex Hillman -Original Message- Sent: Wednesday, April 18, 2001 10:40 AM To: Multiple recipients of list ORACLE-L Hi John, As you mentioned, it depends on a number of factors. I believe I replied to a question about improving performance of index building after an import. Anyway, here is the reasoning behind why I said that When Oracle begins sorting, it starts with the memory equal to sort_area_retained_size value and may eventually acquire memory equal to sort_area_size. This involves at OS level, malloc (and may be some other) memory mgmt routine(s). After the sort phase is complete, Oracle will trigger memory de-allocation and the OS will go at work again. If there are servile sessions performing sorts, there can be an increased OS level memory mgmt activity (while attaining sort_area_size and releasing it back to sort_area_retained_size) . But if the server has *enough* memory keeping sort_area_retained_size equal to sort_area_size may actually help. I also assume that these days most installations have ample memory. And that the advice of using 50% (which I had heard of) of sort area size for sort area retained size should always be viewed in light of one's requirements and resource availability. But I can't stop wondering... how come it was 50% and not 10% or 20%.. so it's all relative. I have been using what I suggested, for a number of years now with no problems or complaints. Cheers ! - Kirti Deshpande Verizon Information Services http://www.superpages.com > -Original Message- > From: Hallas, John [SMTP:[EMAIL PROTECTED]] > Sent: Wednesday, April 18, 2001 6:21 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: Temporary Tablespace Design > > FOR YOUR INFORMATION > > ESIS and EPFAL are now part of Logica. The Internet email addresses of the > staff has changed to the following - [EMAIL PROTECTED] eg > [EMAIL PROTECTED] Emails using the old format will continue to be > delivered until 30th June 2001. > > > Kirti Deshpande writes > > "Also, keep SORT_AREA_RETAINED_SIZE the same as SORT_AREA_SIZE. As the > former will be used while fetching data after sort phase is completed." > > I thought best advice was to make the retained size smaller (50%?) of > sort_area_size to allow initial sort memory to be released after the first > part of the sort is managed and only the merge phase of a disk sort is > left > to do. > > I appreciate that a lot depends on the amount of memory available and the > number of concurrent processes that may be performing sorts but surely the > aim is to free memory up as soon as possible down to the > sort_area_retained_size > > Thanks > > John > > (PS I do apologise for the rubbish at the top of this mail but it is > inserted after I have sent the mail into our mail gateway) > > > > > > This e-mail and any files transmitted with it, are confidential to Logica > and are intended solely for the use of the individual or entity to whom > they are addressed. > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Hallas, John > 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: Deshpande, Kirti 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
RE: Temporary Tablespace Design
Hi John, As you mentioned, it depends on a number of factors. I believe I replied to a question about improving performance of index building after an import. Anyway, here is the reasoning behind why I said that When Oracle begins sorting, it starts with the memory equal to sort_area_retained_size value and may eventually acquire memory equal to sort_area_size. This involves at OS level, malloc (and may be some other) memory mgmt routine(s). After the sort phase is complete, Oracle will trigger memory de-allocation and the OS will go at work again. If there are servile sessions performing sorts, there can be an increased OS level memory mgmt activity (while attaining sort_area_size and releasing it back to sort_area_retained_size) . But if the server has *enough* memory keeping sort_area_retained_size equal to sort_area_size may actually help. I also assume that these days most installations have ample memory. And that the advice of using 50% (which I had heard of) of sort area size for sort area retained size should always be viewed in light of one's requirements and resource availability. But I can't stop wondering... how come it was 50% and not 10% or 20%.. so it's all relative. I have been using what I suggested, for a number of years now with no problems or complaints. Cheers ! - Kirti Deshpande Verizon Information Services http://www.superpages.com > -Original Message- > From: Hallas, John [SMTP:[EMAIL PROTECTED]] > Sent: Wednesday, April 18, 2001 6:21 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: Temporary Tablespace Design > > FOR YOUR INFORMATION > > ESIS and EPFAL are now part of Logica. The Internet email addresses of the > staff has changed to the following - [EMAIL PROTECTED] eg > [EMAIL PROTECTED] Emails using the old format will continue to be > delivered until 30th June 2001. > > > Kirti Deshpande writes > > "Also, keep SORT_AREA_RETAINED_SIZE the same as SORT_AREA_SIZE. As the > former will be used while fetching data after sort phase is completed." > > I thought best advice was to make the retained size smaller (50%?) of > sort_area_size to allow initial sort memory to be released after the first > part of the sort is managed and only the merge phase of a disk sort is > left > to do. > > I appreciate that a lot depends on the amount of memory available and the > number of concurrent processes that may be performing sorts but surely the > aim is to free memory up as soon as possible down to the > sort_area_retained_size > > Thanks > > John > > (PS I do apologise for the rubbish at the top of this mail but it is > inserted after I have sent the mail into our mail gateway) > > > > > > This e-mail and any files transmitted with it, are confidential to Logica > and are intended solely for the use of the individual or entity to whom > they are addressed. > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Hallas, John > 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: Deshpande, Kirti 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: Temporary Tablespace Design
FOR YOUR INFORMATION ESIS and EPFAL are now part of Logica. The Internet email addresses of the staff has changed to the following - [EMAIL PROTECTED] eg [EMAIL PROTECTED] Emails using the old format will continue to be delivered until 30th June 2001. Kirti Deshpande writes "Also, keep SORT_AREA_RETAINED_SIZE the same as SORT_AREA_SIZE. As the former will be used while fetching data after sort phase is completed." I thought best advice was to make the retained size smaller (50%?) of sort_area_size to allow initial sort memory to be released after the first part of the sort is managed and only the merge phase of a disk sort is left to do. I appreciate that a lot depends on the amount of memory available and the number of concurrent processes that may be performing sorts but surely the aim is to free memory up as soon as possible down to the sort_area_retained_size Thanks John (PS I do apologise for the rubbish at the top of this mail but it is inserted after I have sent the mail into our mail gateway) This e-mail and any files transmitted with it, are confidential to Logica and are intended solely for the use of the individual or entity to whom they are addressed. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hallas, John 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: Temporary Tablespace Design
Do not increase the sort_area_size at the database level but at the session level. When I have big batch jobs running alone at night I increased the hash_area_size and the sort_area_size of the session running the job. --- CC Harvest <[EMAIL PROTECTED]> a écrit : > Kirti: >The server is solely used for the Oracle Database > and it has 2GB Ram. I didn't increase sort_area_size > too much because I thought the table is so big, and > probablay I could not run them in the memory. That's > why I just try the temp tablespace. > > Thanks, > > Chris > > --- "Deshpande, Kirti" <[EMAIL PROTECTED]> > wrote: > > I would suggest that you increase (as much as > > possible) sort_area_size and > > sort_area_retained_size for your session when > > building indexes to minimize > > temporary tablespace use. Making temporary > > tablepspace of type temporary and > > adjusting default initial & next extent size can > > also help. > > HTH.. > > - Kirti Deshpande > > Verizon Information Services > >http://www.superpages.com > > > > > -Original Message- > > > From: CC Harvest [SMTP:[EMAIL PROTECTED]] > > > Sent: Thursday, April 12, 2001 11:23 AM > > > To: Multiple recipients of list ORACLE-L > > > Subject: Re:Temporary Tablespace Design > > > > > > Thanks Dick and Lisa for answering my question. > I > > > think I am going to either let the file > > auto-extend, > > > or will try a smaller file as a start. I found > my > > temp > > > tablespace is too small(1GB) because it seems > like > > > takes forever to rebuild an index with > nologging. > > I > > > have 11 indexes on this tables, and it took me > > tons > > > hours to do the index rebuilding. My application > > is > > > a mixed system with 10% batch processing, and > 90% > > > OLAP. > > > But we need the 10% batch processing part should > > be > > > really fast. > > > > > > Thanks, > > > > > > Chris > > > > > > --- [EMAIL PROTECTED] wrote: > > > > Chris, > > > > > > > > First let me say that I have a TON of > > respect > > > > for Mike and count him as a > > > > friend. That said, I also take exception to > > many of > > > > his pronouncements from a > > > > practical, not theoretical, point of view. > > Given > > > > infinite resources, like disk > > > > space and memory and CPU, he does have it > > absolutely > > > > right. But in the real > > > > world there is infinite nothing. > > > > > > > > The first item on my list here it to look > at > > > > what temp space is used for. > > > > It's mainly used for sorting, grouping, and > > distinct > > > > operations. These are the > > > > normal things that involve temp segments, and > in > > a > > > > day to day operation that > > > > will consume an amount of space. The other > item > > > > their used for is index > > > > building, which is not a normal day to day > > > > operation. Therefore the need for an > > > > extremely large temp tablespace is a sporadic > > and > > > > plan able event. Second, > > > > comes the question of the purpose of the > > database. > > > > If your building an OLTP > > > > system then temp usage is going to be even > less > > > > since the majority of actions > > > > will affect few rows at one time. If it's a > > data > > > > warehouse on the other hand > > > > then data mining operations tend to make > extreme > > use > > > > of temp for group and sort > > > > operations, but even so the amount of data > being > > > > processed will not hit the > > > > extremes and when it does it's most likely > bogus > > in > > > > the first place. My > > > > favorite in this vein is our CIO who let loose > a > > > > Cartesian product query just > > > > because he forgot to join the fact table to > the > > > > other tables. In this case the > > > > lack of temp space brought the query to a halt > > > > quickly and mercifully. > > > > > > > > OK, so where should you go? Well, I'll > get > > i
RE: Temporary Tablespace Design
I think you missed analyze operation. It is for this command is 4x may be required. Also space for temporary tablespaces can be on very cheap disks. Alex Hillman -Original Message- Sent: Thursday, April 12, 2001 10:57 AM To: Multiple recipients of list ORACLE-L Chris, First let me say that I have a TON of respect for Mike and count him as a friend. That said, I also take exception to many of his pronouncements from a practical, not theoretical, point of view. Given infinite resources, like disk space and memory and CPU, he does have it absolutely right. But in the real world there is infinite nothing. The first item on my list here it to look at what temp space is used for. It's mainly used for sorting, grouping, and distinct operations. These are the normal things that involve temp segments, and in a day to day operation that will consume an amount of space. The other item their used for is index building, which is not a normal day to day operation. Therefore the need for an extremely large temp tablespace is a sporadic and plan able event. Second, comes the question of the purpose of the database. If your building an OLTP system then temp usage is going to be even less since the majority of actions will affect few rows at one time. If it's a data warehouse on the other hand then data mining operations tend to make extreme use of temp for group and sort operations, but even so the amount of data being processed will not hit the extremes and when it does it's most likely bogus in the first place. My favorite in this vein is our CIO who let loose a Cartesian product query just because he forgot to join the fact table to the other tables. In this case the lack of temp space brought the query to a halt quickly and mercifully. OK, so where should you go? Well, I'll get into our DB's which range from our 150GB data warehouse to our 200GB operational data store. The former has 1GB of temp storage for normal operations. The latter gets along very well on 400MB of temp space. Both have a 14GB disk area that they share as required for those monster index rebuilds. Where you go from here is a lot of personal decision. I recommend starting small & working your way up as necessary. The easiest way to do that is to enable auto-extend. Dick Goulet Reply Separator Author: CC Harvest <[EMAIL PROTECTED]> Date: 4/12/2001 12:05 AM What's your experience about the temporary table design? I read Michael Ault's Orcale8 Administartion and Management , it says "For Cost-based optimization, it should be 4 times of the largest table". I have a table of 60 Million records, and it costs 16GB, should I have a 64GB temp tablespace(I don't think so, though it's a 100GB database, and I have a 300GB of diskspace). Thanks for your advice. Chris __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hillman, Alex 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: Temporary Tablespace Design
Hi Chris, The entire sort may not take place in the memory, but having more memory for sorting minimizes disk usage, which can lead to disk I/O, space mgmt issues and may hamper performance. If there is enough free memory available when building indexes, it is okay to use it, as much as possible, to speed up the sorting. Another thing to consider is properly sizing the initial, next extents for temp segments. Initial = next and ideally they should be some multiples of the SORT_AREA_SIZE and + 1 db block size (some argue about this 1 extra db block size as unnecessary if the block overhead is built into sizing the data file itself). Oracle writes one sort area size worth of information to disk when needed. Proper sizing of the extents minimizes space mgmt tasks. Also, keep SORT_AREA_RETAINED_SIZE the same as SORT_AREA_SIZE. As the former will be used while fetching data after sort phase is completed. HTH.. - Kirti Deshpande Verizon Information Services http://www.superpages.com > -Original Message- > From: CC Harvest [SMTP:[EMAIL PROTECTED]] > Sent: Thursday, April 12, 2001 11:30 AM > To: [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED] > Subject: RE: Temporary Tablespace Design > > Kirti: >The server is solely used for the Oracle Database > and it has 2GB Ram. I didn't increase sort_area_size > too much because I thought the table is so big, and > probablay I could not run them in the memory. That's > why I just try the temp tablespace. > > Thanks, > > Chris > > --- "Deshpande, Kirti" <[EMAIL PROTECTED]> > wrote: > > I would suggest that you increase (as much as > > possible) sort_area_size and > > sort_area_retained_size for your session when > > building indexes to minimize > > temporary tablespace use. Making temporary > > tablepspace of type temporary and > > adjusting default initial & next extent size can > > also help. > > HTH.. > > - Kirti Deshpande > > Verizon Information Services > >http://www.superpages.com > > > > > -Original Message- > > > From: CC Harvest [SMTP:[EMAIL PROTECTED]] > > > Sent: Thursday, April 12, 2001 11:23 AM > > > To: Multiple recipients of list ORACLE-L > > > Subject: Re:Temporary Tablespace Design > > > > > > Thanks Dick and Lisa for answering my question. I > > > think I am going to either let the file > > auto-extend, > > > or will try a smaller file as a start. I found my > > temp > > > tablespace is too small(1GB) because it seems like > > > takes forever to rebuild an index with nologging. > > I > > > have 11 indexes on this tables, and it took me > > tons > > > hours to do the index rebuilding. My application > > is > > > a mixed system with 10% batch processing, and 90% > > > OLAP. > > > But we need the 10% batch processing part should > > be > > > really fast. > > > > > > Thanks, > > > > > > Chris > > > > > > --- [EMAIL PROTECTED] wrote: > > > > Chris, > > > > > > > > First let me say that I have a TON of > > respect > > > > for Mike and count him as a > > > > friend. That said, I also take exception to > > many of > > > > his pronouncements from a > > > > practical, not theoretical, point of view. > > Given > > > > infinite resources, like disk > > > > space and memory and CPU, he does have it > > absolutely > > > > right. But in the real > > > > world there is infinite nothing. > > > > > > > > The first item on my list here it to look at > > > > what temp space is used for. > > > > It's mainly used for sorting, grouping, and > > distinct > > > > operations. These are the > > > > normal things that involve temp segments, and in > > a > > > > day to day operation that > > > > will consume an amount of space. The other item > > > > their used for is index > > > > building, which is not a normal day to day > > > > operation. Therefore the need for an > > > > extremely large temp tablespace is a sporadic > > and > > > > plan able event. Second, > > > > comes the question of the purpose of the > > database. > > > > If your building an OLTP > > > > system then temp usage is going to be even less > > > > since the majority of actions > > > > will affect few rows at one time. If it&
RE: Temporary Tablespace Design
Kirti: The server is solely used for the Oracle Database and it has 2GB Ram. I didn't increase sort_area_size too much because I thought the table is so big, and probablay I could not run them in the memory. That's why I just try the temp tablespace. Thanks, Chris --- "Deshpande, Kirti" <[EMAIL PROTECTED]> wrote: > I would suggest that you increase (as much as > possible) sort_area_size and > sort_area_retained_size for your session when > building indexes to minimize > temporary tablespace use. Making temporary > tablepspace of type temporary and > adjusting default initial & next extent size can > also help. > HTH.. > - Kirti Deshpande > Verizon Information Services >http://www.superpages.com > > > -Original Message- > > From: CC Harvest [SMTP:[EMAIL PROTECTED]] > > Sent: Thursday, April 12, 2001 11:23 AM > > To: Multiple recipients of list ORACLE-L > > Subject:Re:Temporary Tablespace Design > > > > Thanks Dick and Lisa for answering my question. I > > think I am going to either let the file > auto-extend, > > or will try a smaller file as a start. I found my > temp > > tablespace is too small(1GB) because it seems like > > takes forever to rebuild an index with nologging. > I > > have 11 indexes on this tables, and it took me > tons > > hours to do the index rebuilding. My application > is > > a mixed system with 10% batch processing, and 90% > > OLAP. > > But we need the 10% batch processing part should > be > > really fast. > > > > Thanks, > > > > Chris > > > > --- [EMAIL PROTECTED] wrote: > > > Chris, > > > > > > First let me say that I have a TON of > respect > > > for Mike and count him as a > > > friend. That said, I also take exception to > many of > > > his pronouncements from a > > > practical, not theoretical, point of view. > Given > > > infinite resources, like disk > > > space and memory and CPU, he does have it > absolutely > > > right. But in the real > > > world there is infinite nothing. > > > > > > The first item on my list here it to look at > > > what temp space is used for. > > > It's mainly used for sorting, grouping, and > distinct > > > operations. These are the > > > normal things that involve temp segments, and in > a > > > day to day operation that > > > will consume an amount of space. The other item > > > their used for is index > > > building, which is not a normal day to day > > > operation. Therefore the need for an > > > extremely large temp tablespace is a sporadic > and > > > plan able event. Second, > > > comes the question of the purpose of the > database. > > > If your building an OLTP > > > system then temp usage is going to be even less > > > since the majority of actions > > > will affect few rows at one time. If it's a > data > > > warehouse on the other hand > > > then data mining operations tend to make extreme > use > > > of temp for group and sort > > > operations, but even so the amount of data being > > > processed will not hit the > > > extremes and when it does it's most likely bogus > in > > > the first place. My > > > favorite in this vein is our CIO who let loose a > > > Cartesian product query just > > > because he forgot to join the fact table to the > > > other tables. In this case the > > > lack of temp space brought the query to a halt > > > quickly and mercifully. > > > > > > OK, so where should you go? Well, I'll get > into > > > our DB's which range from > > > our 150GB data warehouse to our 200GB > operational > > > data store. The former has > > > 1GB of temp storage for normal operations. The > > > latter gets along very well on > > > 400MB of temp space. Both have a 14GB disk area > > > that they share as required for > > > those monster index rebuilds. > > > > > > Where you go from here is a lot of personal > > > decision. I recommend starting > > > small & working your way up as necessary. The > > > easiest way to do that is to > > > enable auto-extend. > > > > > > Dick Goulet > > > > > > Reply > > > Separator > > > Author: CC Harvest <[EMAIL PROTECTED]>
Re[2]:Temporary Tablespace Design
Chris, Some of what your saying would lead me to look at the wait_io in a the sar output. You may want to talk with your SA as it sounds like you maybe IO bound. That being the case it does not matter how much temp space you have, your just sitting around waiting on the drive(s). Kirti's suggestion maybe of more help, unless your short on memory. One item to stay away from at all costs is having stuff in memory paged out to disk. That is a REAL performance problem. Dick Goulet Reply Separator Author: CC Harvest <[EMAIL PROTECTED]> Date: 4/12/2001 8:23 AM Thanks Dick and Lisa for answering my question. I think I am going to either let the file auto-extend, or will try a smaller file as a start. I found my temp tablespace is too small(1GB) because it seems like takes forever to rebuild an index with nologging. I have 11 indexes on this tables, and it took me tons hours to do the index rebuilding. My application is a mixed system with 10% batch processing, and 90% OLAP. But we need the 10% batch processing part should be really fast. Thanks, Chris --- [EMAIL PROTECTED] wrote: > Chris, > > First let me say that I have a TON of respect > for Mike and count him as a > friend. That said, I also take exception to many of > his pronouncements from a > practical, not theoretical, point of view. Given > infinite resources, like disk > space and memory and CPU, he does have it absolutely > right. But in the real > world there is infinite nothing. > > The first item on my list here it to look at > what temp space is used for. > It's mainly used for sorting, grouping, and distinct > operations. These are the > normal things that involve temp segments, and in a > day to day operation that > will consume an amount of space. The other item > their used for is index > building, which is not a normal day to day > operation. Therefore the need for an > extremely large temp tablespace is a sporadic and > plan able event. Second, > comes the question of the purpose of the database. > If your building an OLTP > system then temp usage is going to be even less > since the majority of actions > will affect few rows at one time. If it's a data > warehouse on the other hand > then data mining operations tend to make extreme use > of temp for group and sort > operations, but even so the amount of data being > processed will not hit the > extremes and when it does it's most likely bogus in > the first place. My > favorite in this vein is our CIO who let loose a > Cartesian product query just > because he forgot to join the fact table to the > other tables. In this case the > lack of temp space brought the query to a halt > quickly and mercifully. > > OK, so where should you go? Well, I'll get into > our DB's which range from > our 150GB data warehouse to our 200GB operational > data store. The former has > 1GB of temp storage for normal operations. The > latter gets along very well on > 400MB of temp space. Both have a 14GB disk area > that they share as required for > those monster index rebuilds. > > Where you go from here is a lot of personal > decision. I recommend starting > small & working your way up as necessary. The > easiest way to do that is to > enable auto-extend. > > Dick Goulet > > Reply > Separator > Author: CC Harvest <[EMAIL PROTECTED]> > Date: 4/12/2001 12:05 AM > > What's your experience about the temporary table > design? I read Michael Ault's Orcale8 Administartion > and Management , it says "For Cost-based > optimization, > it should be 4 times of the largest table". I have a > table of 60 Million records, and it costs 16GB, > should > I have a 64GB temp tablespace(I don't think so, > though > it's a 100GB database, and I have a 300GB of > diskspace). > > Thanks for your advice. > > Chris > > __ > Do You Yahoo!? > Get email at your own domain with Yahoo! Mail. > http://personal.mail.yahoo.com/ > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: CC Harvest > 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
RE: Temporary Tablespace Design
I would suggest that you increase (as much as possible) sort_area_size and sort_area_retained_size for your session when building indexes to minimize temporary tablespace use. Making temporary tablepspace of type temporary and adjusting default initial & next extent size can also help. HTH.. - Kirti Deshpande Verizon Information Services http://www.superpages.com > -Original Message- > From: CC Harvest [SMTP:[EMAIL PROTECTED]] > Sent: Thursday, April 12, 2001 11:23 AM > To: Multiple recipients of list ORACLE-L > Subject: Re:Temporary Tablespace Design > > Thanks Dick and Lisa for answering my question. I > think I am going to either let the file auto-extend, > or will try a smaller file as a start. I found my temp > tablespace is too small(1GB) because it seems like > takes forever to rebuild an index with nologging. I > have 11 indexes on this tables, and it took me tons > hours to do the index rebuilding. My application is > a mixed system with 10% batch processing, and 90% > OLAP. > But we need the 10% batch processing part should be > really fast. > > Thanks, > > Chris > > --- [EMAIL PROTECTED] wrote: > > Chris, > > > > First let me say that I have a TON of respect > > for Mike and count him as a > > friend. That said, I also take exception to many of > > his pronouncements from a > > practical, not theoretical, point of view. Given > > infinite resources, like disk > > space and memory and CPU, he does have it absolutely > > right. But in the real > > world there is infinite nothing. > > > > The first item on my list here it to look at > > what temp space is used for. > > It's mainly used for sorting, grouping, and distinct > > operations. These are the > > normal things that involve temp segments, and in a > > day to day operation that > > will consume an amount of space. The other item > > their used for is index > > building, which is not a normal day to day > > operation. Therefore the need for an > > extremely large temp tablespace is a sporadic and > > plan able event. Second, > > comes the question of the purpose of the database. > > If your building an OLTP > > system then temp usage is going to be even less > > since the majority of actions > > will affect few rows at one time. If it's a data > > warehouse on the other hand > > then data mining operations tend to make extreme use > > of temp for group and sort > > operations, but even so the amount of data being > > processed will not hit the > > extremes and when it does it's most likely bogus in > > the first place. My > > favorite in this vein is our CIO who let loose a > > Cartesian product query just > > because he forgot to join the fact table to the > > other tables. In this case the > > lack of temp space brought the query to a halt > > quickly and mercifully. > > > > OK, so where should you go? Well, I'll get into > > our DB's which range from > > our 150GB data warehouse to our 200GB operational > > data store. The former has > > 1GB of temp storage for normal operations. The > > latter gets along very well on > > 400MB of temp space. Both have a 14GB disk area > > that they share as required for > > those monster index rebuilds. > > > > Where you go from here is a lot of personal > > decision. I recommend starting > > small & working your way up as necessary. The > > easiest way to do that is to > > enable auto-extend. > > > > Dick Goulet > > > > Reply > > Separator > > Author: CC Harvest <[EMAIL PROTECTED]> > > Date: 4/12/2001 12:05 AM > > > > What's your experience about the temporary table > > design? I read Michael Ault's Orcale8 Administartion > > and Management , it says "For Cost-based > > optimization, > > it should be 4 times of the largest table". I have a > > table of 60 Million records, and it costs 16GB, > > should > > I have a 64GB temp tablespace(I don't think so, > > though > > it's a 100GB database, and I have a 300GB of > > diskspace). > > > > Thanks for your advice. > > > > Chris > > > > __ > > Do You Yahoo!? > > Get email at your own domain with Yahoo! Mail. > > http://personal.mail.yahoo.com/ > > -- > > Please see the official ORACLE-L FAQ: > > http://www.orafaq.com > > -- >
Re:RE: Temporary Tablespace Design
Steve, Thanks for reminding me of that one other operation that does also take up temp. Again, I think that depends on how often you calculate statistics & if your using partitioning. We do statistics once a week, and do it partition by partition so it does not take up that much. Dick Goulet Reply Separator Author: "Steve Adams" <[EMAIL PROTECTED]> Date: 4/13/2001 12:50 AM Hi Dick, I guess that Mike was thinking of ANALYZE operations against that large table. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Friday, 13 April 2001 0:57 To: Multiple recipients of list ORACLE-L Chris, First let me say that I have a TON of respect for Mike and count him as a friend. That said, I also take exception to many of his pronouncements from a practical, not theoretical, point of view. Given infinite resources, like disk space and memory and CPU, he does have it absolutely right. But in the real world there is infinite nothing. The first item on my list here it to look at what temp space is used for. It's mainly used for sorting, grouping, and distinct operations. These are the normal things that involve temp segments, and in a day to day operation that will consume an amount of space. The other item their used for is index building, which is not a normal day to day operation. Therefore the need for an extremely large temp tablespace is a sporadic and plan able event. Second, comes the question of the purpose of the database. If your building an OLTP system then temp usage is going to be even less since the majority of actions will affect few rows at one time. If it's a data warehouse on the other hand then data mining operations tend to make extreme use of temp for group and sort operations, but even so the amount of data being processed will not hit the extremes and when it does it's most likely bogus in the first place. My favorite in this vein is our CIO who let loose a Cartesian product query just because he forgot to join the fact table to the other tables. In this case the lack of temp space brought the query to a halt quickly and mercifully. OK, so where should you go? Well, I'll get into our DB's which range from our 150GB data warehouse to our 200GB operational data store. The former has 1GB of temp storage for normal operations. The latter gets along very well on 400MB of temp space. Both have a 14GB disk area that they share as required for those monster index rebuilds. Where you go from here is a lot of personal decision. I recommend starting small & working your way up as necessary. The easiest way to do that is to enable auto-extend. Dick Goulet Reply Separator Author: CC Harvest <[EMAIL PROTECTED]> Date: 4/12/2001 12:05 AM What's your experience about the temporary table design? I read Michael Ault's Orcale8 Administartion and Management , it says "For Cost-based optimization, it should be 4 times of the largest table". I have a table of 60 Million records, and it costs 16GB, should I have a 64GB temp tablespace(I don't think so, though it's a 100GB database, and I have a 300GB of diskspace). Thanks for your advice. Chris __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest 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). -- 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 ---
Re:Temporary Tablespace Design
Thanks Dick and Lisa for answering my question. I think I am going to either let the file auto-extend, or will try a smaller file as a start. I found my temp tablespace is too small(1GB) because it seems like takes forever to rebuild an index with nologging. I have 11 indexes on this tables, and it took me tons hours to do the index rebuilding. My application is a mixed system with 10% batch processing, and 90% OLAP. But we need the 10% batch processing part should be really fast. Thanks, Chris --- [EMAIL PROTECTED] wrote: > Chris, > > First let me say that I have a TON of respect > for Mike and count him as a > friend. That said, I also take exception to many of > his pronouncements from a > practical, not theoretical, point of view. Given > infinite resources, like disk > space and memory and CPU, he does have it absolutely > right. But in the real > world there is infinite nothing. > > The first item on my list here it to look at > what temp space is used for. > It's mainly used for sorting, grouping, and distinct > operations. These are the > normal things that involve temp segments, and in a > day to day operation that > will consume an amount of space. The other item > their used for is index > building, which is not a normal day to day > operation. Therefore the need for an > extremely large temp tablespace is a sporadic and > plan able event. Second, > comes the question of the purpose of the database. > If your building an OLTP > system then temp usage is going to be even less > since the majority of actions > will affect few rows at one time. If it's a data > warehouse on the other hand > then data mining operations tend to make extreme use > of temp for group and sort > operations, but even so the amount of data being > processed will not hit the > extremes and when it does it's most likely bogus in > the first place. My > favorite in this vein is our CIO who let loose a > Cartesian product query just > because he forgot to join the fact table to the > other tables. In this case the > lack of temp space brought the query to a halt > quickly and mercifully. > > OK, so where should you go? Well, I'll get into > our DB's which range from > our 150GB data warehouse to our 200GB operational > data store. The former has > 1GB of temp storage for normal operations. The > latter gets along very well on > 400MB of temp space. Both have a 14GB disk area > that they share as required for > those monster index rebuilds. > > Where you go from here is a lot of personal > decision. I recommend starting > small & working your way up as necessary. The > easiest way to do that is to > enable auto-extend. > > Dick Goulet > > Reply > Separator > Author: CC Harvest <[EMAIL PROTECTED]> > Date: 4/12/2001 12:05 AM > > What's your experience about the temporary table > design? I read Michael Ault's Orcale8 Administartion > and Management , it says "For Cost-based > optimization, > it should be 4 times of the largest table". I have a > table of 60 Million records, and it costs 16GB, > should > I have a 64GB temp tablespace(I don't think so, > though > it's a 100GB database, and I have a 300GB of > diskspace). > > Thanks for your advice. > > Chris > > __ > Do You Yahoo!? > Get email at your own domain with Yahoo! Mail. > http://personal.mail.yahoo.com/ > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: CC Harvest > 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). __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORAC
Re:Temporary Tablespace Design
Chris, First let me say that I have a TON of respect for Mike and count him as a friend. That said, I also take exception to many of his pronouncements from a practical, not theoretical, point of view. Given infinite resources, like disk space and memory and CPU, he does have it absolutely right. But in the real world there is infinite nothing. The first item on my list here it to look at what temp space is used for. It's mainly used for sorting, grouping, and distinct operations. These are the normal things that involve temp segments, and in a day to day operation that will consume an amount of space. The other item their used for is index building, which is not a normal day to day operation. Therefore the need for an extremely large temp tablespace is a sporadic and plan able event. Second, comes the question of the purpose of the database. If your building an OLTP system then temp usage is going to be even less since the majority of actions will affect few rows at one time. If it's a data warehouse on the other hand then data mining operations tend to make extreme use of temp for group and sort operations, but even so the amount of data being processed will not hit the extremes and when it does it's most likely bogus in the first place. My favorite in this vein is our CIO who let loose a Cartesian product query just because he forgot to join the fact table to the other tables. In this case the lack of temp space brought the query to a halt quickly and mercifully. OK, so where should you go? Well, I'll get into our DB's which range from our 150GB data warehouse to our 200GB operational data store. The former has 1GB of temp storage for normal operations. The latter gets along very well on 400MB of temp space. Both have a 14GB disk area that they share as required for those monster index rebuilds. Where you go from here is a lot of personal decision. I recommend starting small & working your way up as necessary. The easiest way to do that is to enable auto-extend. Dick Goulet Reply Separator Author: CC Harvest <[EMAIL PROTECTED]> Date: 4/12/2001 12:05 AM What's your experience about the temporary table design? I read Michael Ault's Orcale8 Administartion and Management , it says "For Cost-based optimization, it should be 4 times of the largest table". I have a table of 60 Million records, and it costs 16GB, should I have a 64GB temp tablespace(I don't think so, though it's a 100GB database, and I have a 300GB of diskspace). Thanks for your advice. Chris __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest 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: Temporary Tablespace Design
I'd be tempted to make it 4 times your largest expected working set, but how you arrive at that figure is beyond me... >>> [EMAIL PROTECTED] 04/12/01 04:05AM >>> What's your experience about the temporary table design? I read Michael Ault's Orcale8 Administartion and Management , it says "For Cost-based optimization, it should be 4 times of the largest table". I have a table of 60 Million records, and it costs 16GB, should I have a 64GB temp tablespace(I don't think so, though it's a 100GB database, and I have a 300GB of diskspace). Thanks for your advice. Chris __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest 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: Tim Sawmiller 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: Temporary Tablespace Design
Title: RE: Temporary Tablespace Design Hi Chris, Yes there may be 'rules' like this, but realistically who has 64GB to spend on disk space? I have one huge table similar to what you describe. My temp tablespace is 20GB only because I have the disk. Maybe you can take the route of determining how large your largest index would be on this table and size it from that? Of course that doesn't help with any long running processes that cause large sorts, but it's a place to start. HTH Lisa Rutland Koivu Oracle Database Administrator [EMAIL PROTECTED] NeoMedia 2201 Second St., Suite 600 Fort Myers, FL 33901, USA Phone: 941-337-3434 Fax: 941-337-3668 www.neom.com <http://www.neom.com> www.paperclick.com <http://www.paperclick.com> www.qode.com <http://www.qode.com> P a p e r C l i c k . c o m <http://www.paperclick.com/home.htm> Enter Your PaperClick Code Here! -Original Message- From: CC Harvest [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 12, 2001 4:05 AM To: Multiple recipients of list ORACLE-L Subject: Temporary Tablespace Design What's your experience about the temporary table design? I read Michael Ault's Orcale8 Administartion and Management , it says "For Cost-based optimization, it should be 4 times of the largest table". I have a table of 60 Million records, and it costs 16GB, should I have a 64GB temp tablespace(I don't think so, though it's a 100GB database, and I have a 300GB of diskspace). Thanks for your advice. Chris __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest 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).
Temporary Tablespace Design
What's your experience about the temporary table design? I read Michael Ault's Orcale8 Administartion and Management , it says "For Cost-based optimization, it should be 4 times of the largest table". I have a table of 60 Million records, and it costs 16GB, should I have a 64GB temp tablespace(I don't think so, though it's a 100GB database, and I have a 300GB of diskspace). Thanks for your advice. Chris __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest 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).