wrong setting of sort_area_size
we had a customer of the sort_area_size=1000 in their database, the temp table space is 256MB, the db_block_size is 8k. They have some performance problem, I think it's probably related to the setting of sort_area_size(we have only one user). So what's the benefits/disadvantage of too big sort_area_size? Thanks, Chris __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.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).
RE: wrong setting of sort_area_size
Advantage:increasing sort area size decreases the I/O, hence quicker sorting process. Disadvantage: increasing sort area causes sort process to allocate more memory, which effects the OS memory allocation resulting in paging and swapping. large sort area sizes can used if you are using with a minimal sort_area_retained_size parameter. Sunil Nookala Dell Corp Austin, TX -Original Message- Sent: Thursday, May 03, 2001 3:06 PM To: Multiple recipients of list ORACLE-L we had a customer of the sort_area_size=1000 in their database, the temp table space is 256MB, the db_block_size is 8k. They have some performance problem, I think it's probably related to the setting of sort_area_size(we have only one user). So what's the benefits/disadvantage of too big sort_area_size? Thanks, Chris __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.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: wrong setting of sort_area_size
Hi, Setting the sort_area_size to something so big it sounds silly may actually improve some querries dramatically. I don't see why it should degrade performance if you have the physical memory to spare. We had a batch job that took about 60 hours but had to be tuned so it fitted in the 4 hours maintenance window. Apart from some funky compute statistics we increased the sort_area_size to 100Mb after which it did a hash join i.s.o nested loops and finished well within half hour Jack CC Harvest oo.com> cc: Sent by: Subject: wrong setting of sort_area_size [EMAIL PROTECTED] om 03-05-2001 22:06 Please respond to ORACLE-L we had a customer of the sort_area_size=1000 in their database, the temp table space is 256MB, the db_block_size is 8k. They have some performance problem, I think it's probably related to the setting of sort_area_size(we have only one user). So what's the benefits/disadvantage of too big sort_area_size? Thanks, Chris __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.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). = De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst & Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst & Young. Ernst & Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst & Young does not guarantee that the integrit
Re: wrong setting of sort_area_size
Mininmal disadvantages besides overextending the machine's memory...eg if 100 people are all doing a large sort at the SAME time, you could have 100 x 10m of memory or more getting munch up...But the memory is only used when they want to do a sort ... hth connor --- CC Harvest <[EMAIL PROTECTED]> wrote: > we had a customer of the sort_area_size=1000 > in their database, the temp table space is 256MB, > the > db_block_size is 8k. They have some performance > problem, I think it's probably related to the > setting > of sort_area_size(we have only one user). So what's > the benefits/disadvantage of too big sort_area_size? > > Thanks, > > Chris > > __ > Do You Yahoo!? > Yahoo! Auctions - buy the things you want at great > prices > http://auctions.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). = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) "Some days you're the pigeon, some days you're the statue" Do You Yahoo!? Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk or your free @yahoo.ie address at http://mail.yahoo.ie -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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: wrong setting of sort_area_size
Usually such large sort_area_size is used for batch processes, so there is no problem with many such jobs running at the same time. It can be done befor e running the job and then back to old value after the job ended. Also if hash_area_size parameter default value is sort_area_size. This is why the job which took 60 hours finished in 30 min. in previous message and not because sorting became faster. Alex Hillman -Original Message- Sent: Friday, May 04, 2001 11:50 AM To: Multiple recipients of list ORACLE-L Mininmal disadvantages besides overextending the machine's memory...eg if 100 people are all doing a large sort at the SAME time, you could have 100 x 10m of memory or more getting munch up...But the memory is only used when they want to do a sort ... hth connor --- CC Harvest <[EMAIL PROTECTED]> wrote: > we had a customer of the sort_area_size=1000 > in their database, the temp table space is 256MB, > the > db_block_size is 8k. They have some performance > problem, I think it's probably related to the > setting > of sort_area_size(we have only one user). So what's > the benefits/disadvantage of too big sort_area_size? > > Thanks, > > Chris > > __ > Do You Yahoo!? > Yahoo! Auctions - buy the things you want at great > prices > http://auctions.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). = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) "Some days you're the pigeon, some days you're the statue" Do You Yahoo!? Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk or your free @yahoo.ie address at http://mail.yahoo.ie -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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).
