RE: SORT_AREA_SIZE question
You didn't specify which version of Oracle you are using, but if it's 9i then you should investigate the use of pga_aggregate_target instead of the older sort_area parameters. I've been using pga_aggregate_target in a hybrid OLTP/reporting database for about 8 months with great results. Mine is set to 100 MB. You'll get the benifit of the memory advice feature of 9i as well. Have a look at OEM if you're using it, otherwise you can look directly in the dictionary views to determine proper sizing of PGA, shared pool, and buffer cache. Best regards, David B. Wagoner Database Administrator Arsenal Digital Solutions -Original Message-From: Bart Kersteter [mailto:[EMAIL PROTECTED]Sent: Wednesday, June 25, 2003 10:39 AMTo: Multiple recipients of list ORACLE-LSubject: SORT_AREA_SIZE question Hello, I joined this list last week, so I apologize in advance if I'm asking a question that has previously been answered. I am responsible for a reporting database/data mart that is approximately 175 GB. Our main fact table ranges from 1-14 GB depending upon how far along we are into our financial year. I have large reports that run full table scans on this table daily. In an effort to keep as much of the sorting in memory as possible I have specified SORT_AREA_SIZE to be 100MB. Some of the tuning books I am reading now are making me second-guess myself and I am wondering if this is overkill. Can anyone provide some advice on how large they are setting their SORT_AREA_SIZE values for their DSS systems? Thanks in advance, Bart Please NoteThe information in this E-mail message is legally privilegedand confidential information intended only for the use of theindividual(s) named above. If you, the reader of this message,are not the intended recipient, you are hereby notified that you should not further disseminate, distribute, or forward thisE-mail message. If you have received this E-mail in error,please notify the sender. Thank you
RE: SORT_AREA_SIZE question
Title: Message Bart Each user which connects to the DB will consume that amount of RAM on top of any memory used by the application. A lot depends on how much memory your server has i.e. 10 users will use at least 1Gb of RAM. It could induce swapping which is a killer. Our DSS systems use 20Mb for sort area mind you we have 18Gb temp tablespace Regards Nigel BishopSnr. Oracle DBAiokoTel DDI: +44 (0) 1904 435 458Mobile: +44 (0) 7881 624 386Fax: +44 (0) 1904 435 450Email: [EMAIL PROTECTED] www.ioko.com -Original Message-From: Bart Kersteter [mailto:[EMAIL PROTECTED] Sent: 25 June 2003 15:39To: Multiple recipients of list ORACLE-LSubject: SORT_AREA_SIZE question Hello, I joined this list last week, so I apologize in advance if I'm asking a question that has previously been answered. I am responsible for a reporting database/data mart that is approximately 175 GB. Our main fact table ranges from 1-14 GB depending upon how far along we are into our financial year. I have large reports that run full table scans on this table daily. In an effort to keep as much of the sorting in memory as possible I have specified SORT_AREA_SIZE to be 100MB. Some of the tuning books I am reading now are making me second-guess myself and I am wondering if this is overkill. Can anyone provide some advice on how large they are setting their SORT_AREA_SIZE values for their DSS systems? Thanks in advance, Bart Please NoteThe information in this E-mail message is legally privilegedand confidential information intended only for the use of theindividual(s) named above. If you, the reader of this message,are not the intended recipient, you are hereby notified that you should not further disseminate, distribute, or forward thisE-mail message. If you have received this E-mail in error,please notify the sender. Thank you
RE: SORT_AREA_SIZE question
Bart - No it hasn't been recently asked. 1. SORT_AREA_SIZE is per-process. So be a little wary. Keep in mind that in-memory sorts are much faster than disk sorts. But you knew that. 2. Ask your system administrator to monitor whether your system is getting paging/swapping. 3. Find out how many disk sorts are occurring. I prefer STATSPACK. Try to get a handle on how may disk vs. memory sorts are occurring. Try to increase SORT_AREA_SIZE until you have few disk sorts, but not so large you cause paging/swapping. Also look at your temp space settings to make sure when disk is used, it is used most efficiently. With a data mart, you may have some really large sorts that will always be too large for memory sorts. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, June 25, 2003 9:39 AM To: Multiple recipients of list ORACLE-L Hello, I joined this list last week, so I apologize in advance if I'm asking a question that has previously been answered. I am responsible for a reporting database/data mart that is approximately 175 GB. Our main fact table ranges from 1-14 GB depending upon how far along we are into our financial year. I have large reports that run full table scans on this table daily. In an effort to keep as much of the sorting in memory as possible I have specified SORT_AREA_SIZE to be 100MB. Some of the tuning books I am reading now are making me second-guess myself and I am wondering if this is overkill. Can anyone provide some advice on how large they are setting their SORT_AREA_SIZE values for their DSS systems? Thanks in advance, Bart _ Please Note The information in this E-mail message is legally privileged and confidential information intended only for the use of the individual(s) named above. If you, the reader of this message, are not the intended recipient, you are hereby notified that you should not further disseminate, distribute, or forward this E-mail message. If you have received this E-mail in error, please notify the sender. Thank you _ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: SORT_AREA_SIZE question
You want a sorts to disk ratio of 1 percent or less. To find this run this; dispatch column name format a30dispatch select name, value 2 from v$sysstat 3 where name like 'sort%'; NAME VALUE-- -sorts (memory) 283420sorts (disk) 64sorts (rows) 29507963 the ratio of sorts = round((64/283420)*100,3) ratio of sorts = 0.023% Learned this from the Oracle DBA 101 book, one of the best buys I made. Dave -Original Message-From: Bart Kersteter [mailto:[EMAIL PROTECTED]Sent: Wednesday, June 25, 2003 9:39 AMTo: Multiple recipients of list ORACLE-LSubject: SORT_AREA_SIZE question Hello, I joined this list last week, so I apologize in advance if I'm asking a question that has previously been answered. I am responsible for a reporting database/data mart that is approximately 175 GB. Our main fact table ranges from 1-14 GB depending upon how far along we are into our financial year. I have large reports that run full table scans on this table daily. In an effort to keep as much of the sorting in memory as possible I have specified SORT_AREA_SIZE to be 100MB. Some of the tuning books I am reading now are making me second-guess myself and I am wondering if this is overkill. Can anyone provide some advice on how large they are setting their SORT_AREA_SIZE values for their DSS systems? Thanks in advance, Bart Please NoteThe information in this E-mail message is legally privilegedand confidential information intended only for the use of theindividual(s) named above. If you, the reader of this message,are not the intended recipient, you are hereby notified that you should not further disseminate, distribute, or forward thisE-mail message. If you have received this E-mail in error,please notify the sender. Thank you
Re: SORT_AREA_SIZE question
Hey Bart, You'll probably get a lot of responses from this, but my little piece of input starts with a question... Is this database a 9i version? If so, do some research on pga_aggregate_target. It lets Oracle do some of that *_area_size guess work for you. [EMAIL PROTECTED] 06/25/03 09:39AM Hello, I joined this list last week, so I apologize in advance if I'm asking a question that has previously been answered. I am responsible for a reporting database/data mart that is approximately 175 GB. Our main fact table ranges from 1-14 GB depending upon how far along we are into our financial year. I have large reports that run full table scans on this table daily. In an effort to keep as much of the sorting in memory as possible I have specified SORT_AREA_SIZE to be 100MB. Some of the tuning books I am reading now are making me second-guess myself and I am wondering if this is overkill. Can anyone provide some advice on how large they are setting their SORT_AREA_SIZE values for their DSS systems? Thanks in advance, Bart Please Note The information in this E-mail message is legally privileged and confidential information intended only for the use of the individual(s) named above. If you, the reader of this message, are not the intended recipient, you are hereby notified that you should not further disseminate, distribute, or forward this E-mail message. If you have received this E-mail in error, please notify the sender. Thank you * NOTICE BY ZALE CORPORATION This message, as well as any attached document, contains information from Zale Corporation that is proprietary, confidential and/or privileged. The information is intended only for the use of the addressee named above. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or the taking of any action in reliance on the contents of this message or its attachments is strictly prohibited, and may be unlawful. If you have received this message in error, please delete all electronic copies of this message and its attachments, if any, destroy any hard copies you may have created, without disclosing the contents, and notify the sender immediately. Unintended transmission does not constitute waiver of any privilege. Unless expressly stated otherwise, nothing contained in this message should be construed as a digital or electronic signature, nor is it intended to reflect an intention to make an agreement by electronic means. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: SORT_AREA_SIZE question
Some consideration too: db_block_size 8K (this has to be set when the database was created) You need to have this setup to: db_file_multiblock_read_count (as I high as Possible) bitmap_merge_area_size create_bitmap_area_size hash_area_size sort_area_size Your temporary tablespace: initial = next = multiple of sort_area_size (i.e: 2* 100MB if your sort_area_size is 100 MB) Regis -Original Message-From: Bart Kersteter [mailto:[EMAIL PROTECTED]Sent: Wednesday, June 25, 2003 3:39 PMTo: Multiple recipients of list ORACLE-LSubject: SORT_AREA_SIZE question Hello, I joined this list last week, so I apologize in advance if I'm asking a question that has previously been answered. I am responsible for a reporting database/data mart that is approximately 175 GB. Our main fact table ranges from 1-14 GB depending upon how far along we are into our financial year. I have large reports that run full table scans on this table daily. In an effort to keep as much of the sorting in memory as possible I have specified SORT_AREA_SIZE to be 100MB. Some of the tuning books I am reading now are making me second-guess myself and I am wondering if this is overkill. Can anyone provide some advice on how large they are setting their SORT_AREA_SIZE values for their DSS systems? Thanks in advance, Bart Please NoteThe information in this E-mail message is legally privilegedand confidential information intended only for the use of theindividual(s) named above. If you, the reader of this message,are not the intended recipient, you are hereby notified that you should not further disseminate, distribute, or forward thisE-mail message. If you have received this E-mail in error,please notify the sender. Thank you * This electronic transmission is strictly confidential and intended solely for the addressee. It may contain information which is covered by legal, professional or other privilege. If you are not the intended addressee, you must not disclose, copy or take any action in reliance of this transmission. If you have received this transmission in error, please notify the sender as soon as possible. This footnote also confirms that this message has been swept for computer viruses. **
RE: SORT_AREA_SIZE question
Since we are talking of Sort area sizes here, I tried to collect some statistics on the amount of memory used by each dedicated Oracle connection. As I understand it, the PGA is allocated in chunks upto the SORT_AREA_SIZE. Initially, I just started a session, and noted down the memory used using pmap. Then, I did a small sort, and then, a huge sort, noting the memory utilization each time. I was even monitoring the PGA statistics in v$sysstat. I was expecting the sizes to increase, and then decrease when the sorting was done with. (retained_size was 0). I dont recall my observations rite now, but it was definitely not what I expected. Has anyone done something similar? Are my expectations rite? Thanks Raj DENNIS WILLIAMS [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] TOUCH.COMcc: Sent by: Subject: RE: SORT_AREA_SIZE question [EMAIL PROTECTED] ty.com 06/25/2003 12:30 PM Please respond to ORACLE-L Bart - No it hasn't been recently asked. 1. SORT_AREA_SIZE is per-process. So be a little wary. Keep in mind that in-memory sorts are much faster than disk sorts. But you knew that. 2. Ask your system administrator to monitor whether your system is getting paging/swapping. 3. Find out how many disk sorts are occurring. I prefer STATSPACK. Try to get a handle on how may disk vs. memory sorts are occurring. Try to increase SORT_AREA_SIZE until you have few disk sorts, but not so large you cause paging/swapping. Also look at your temp space settings to make sure when disk is used, it is used most efficiently. With a data mart, you may have some really large sorts that will always be too large for memory sorts. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, June 25, 2003 9:39 AM To: Multiple recipients of list ORACLE-L Hello, I joined this list last week, so I apologize in advance if I'm asking a question that has previously been answered. I am responsible for a reporting database/data mart that is approximately 175 GB. Our main fact table ranges from 1-14 GB depending upon how far along we are into our financial year. I have large reports that run full table scans on this table daily. In an effort to keep as much of the sorting in memory as possible I have specified SORT_AREA_SIZE to be 100MB. Some of the tuning books I am reading now are making me second-guess myself and I am wondering if this is overkill. Can anyone provide some advice on how large they are setting their SORT_AREA_SIZE values for their DSS systems? Thanks in advance, Bart -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: SORT_AREA_SIZE question
Title: Message Hi! At the least the wording is incorrect, a user who *connects* to instance, doesn't allocate any sort memory unless there is a need for sort. And if there's need for 100k, only that much will be allocated. And when the sort is over (and rows returned), the sort memory is freed. On the other hand, if all of the 10 users do huge sorts with sort_area_size 100M, they could have 1GB allocated altogether, even more than that, check my other mail. So yeah, this could introduce paging if having not enough memory. Btw, in mixed environments you could set sort_area_size to small value and create logon trigger which sets s_a_s big for reporting users.. just a thought about swapping issues... Tanel. - Original Message - From: Nigel Bishop To: Multiple recipients of list ORACLE-L Sent: Wednesday, June 25, 2003 6:39 PM Subject: RE: SORT_AREA_SIZE question Bart Each user which connects to the DB will consume that amount of RAM on top of any memory used by the application. A lot depends on how much memory your server has i.e. 10 users will use at least 1Gb of RAM. It could induce swapping which is a killer. Our DSS systems use 20Mb for sort area mind you we have 18Gb temp tablespace Regards Nigel BishopSnr. Oracle DBAiokoTel DDI: +44 (0) 1904 435 458Mobile: +44 (0) 7881 624 386Fax: +44 (0) 1904 435 450Email: [EMAIL PROTECTED] www.ioko.com -Original Message-From: Bart Kersteter [mailto:[EMAIL PROTECTED] Sent: 25 June 2003 15:39To: Multiple recipients of list ORACLE-LSubject: SORT_AREA_SIZE question Hello, I joined this list last week, so I apologize in advance if I'm asking a question that has previously been answered. I am responsible for a reporting database/data mart that is approximately 175 GB. Our main fact table ranges from 1-14 GB depending upon how far along we are into our financial year. I have large reports that run full table scans on this table daily. In an effort to keep as much of the sorting in memory as possible I have specified SORT_AREA_SIZE to be 100MB. Some of the tuning books I am reading now are making me second-guess myself and I am wondering if this is overkill. Can anyone provide some advice on how large they are setting their SORT_AREA_SIZE values for their DSS systems? Thanks in advance, Bart Please NoteThe information in this E-mail message is legally privilegedand confidential information intended only for the use of theindividual(s) named above. If you, the reader of this message,are not the intended recipient, you are hereby notified that you should not further disseminate, distribute, or forward thisE-mail message. If you have received this E-mail in error,please notify the sender. Thank you
Re: SORT_AREA_SIZE question
Hi! Not entirely the truth. In huge reporting/OLAP environments you just can't have that much memory that all sorts are running entirely in memory. What you should look out for are multipass sorts, the ones which require reading and writing the same to disk multiple times. So keep the merge phases 0, but if not got enough memory, keep the merge phases as low as possible (1). You can somewhat monitor the merge passes with following: SQL select name, value from v$sysstat where name like '%workarea%'; -- or use v$sesstat NAME VALUE --workarea memory allocated 0workarea executions - optimal 1755workarea executions - onepass 0workarea executions - multipass 0 Also, if you use parallel queries for your reporting, it might be so that some parallel slaves do have two sort areas in use, one for sortin the queried data, other for merging the results with other slaves. Read more from http://www.ixora.com.au/newsletter/2000_11.htm#sort_area Also you could set event 10032 and play with sort_area_size (reduce it), to see when you start seeing "intermediate runs" in addition to "initial runs". But more - if you got enough memory, then keep the size at 100M and don't worry about it - Oracle only allocates that much of memory for sort that it needs, no memory is wasted. Tanel. You want a sorts to disk ratio of 1 percent or less. To find this run this; dispatch column name format a30dispatch select name, value 2 from v$sysstat 3 where name like 'sort%'; NAME VALUE-- -sorts (memory) 283420sorts (disk) 64sorts (rows) 29507963 the ratio of sorts = round((64/283420)*100,3) ratio of sorts = 0.023% Learned this from the Oracle DBA 101 book, one of the best buys I made. Dave -Original Message-From: Bart Kersteter [mailto:[EMAIL PROTECTED]Sent: Wednesday, June 25, 2003 9:39 AMTo: Multiple recipients of list ORACLE-LSubject: SORT_AREA_SIZE question Hello, I joined this list last week, so I apologize in advance if I'm asking a question that has previously been answered. I am responsible for a reporting database/data mart that is approximately 175 GB. Our main fact table ranges from 1-14 GB depending upon how far along we are into our financial year. I have large reports that run full table scans on this table daily. In an effort to keep as much of the sorting in memory as possible I have specified SORT_AREA_SIZE to be 100MB. Some of the tuning books I am reading now are making me second-guess myself and I am wondering if this is overkill. Can anyone provide some advice on how large they are setting their SORT_AREA_SIZE values for their DSS systems? Thanks in advance, Bart Please NoteThe information in this E-mail message is legally privilegedand confidential information intended only for the use of theindividual(s) named above. If you, the reader of this message,are not the intended recipient, you are hereby notified that you should not further disseminate, distribute, or forward thisE-mail message. If you have received this E-mail in error,please notify the sender. Thank you
RE: SORT_AREA_SIZE question
Sorry... I'm using 9.0.1.4 I will look into PGA_AGGREGATE_TARGET. Thanks, Bart [EMAIL PROTECTED] 06/25/03 10:24AM You didn't specify which version of Oracle you are using, but if it's 9i then you should investigate the use of pga_aggregate_target instead of the older sort_area parameters. I've been using pga_aggregate_target in a hybrid OLTP/reporting database for about 8 months with great results. Mine is set to 100 MB. You'll get the benifit of the memory advice feature of 9i as well. Have a look at OEM if you're using it, otherwise you can look directly in the dictionary views to determine proper sizing of PGA, shared pool, and buffer cache. Best regards, David B. Wagoner Database Administrator Arsenal Digital Solutions -Original Message-From: Bart Kersteter [mailto:[EMAIL PROTECTED]Sent: Wednesday, June 25, 2003 10:39 AMTo: Multiple recipients of list ORACLE-LSubject: SORT_AREA_SIZE question Hello, I joined this list last week, so I apologize in advance if I'm asking a question that has previously been answered. I am responsible for a reporting database/data mart that is approximately 175 GB. Our main fact table ranges from 1-14 GB depending upon how far along we are into our financial year. I have large reports that run full table scans on this table daily. In an effort to keep as much of the sorting in memory as possible I have specified SORT_AREA_SIZE to be 100MB. Some of the tuning books I am reading now are making me second-guess myself and I am wondering if this is overkill. Can anyone provide some advice on how large they are setting their SORT_AREA_SIZE values for their DSS systems? Thanks in advance, Bart Please NoteThe information in this E-mail message is legally privilegedand confidential information intended only for the use of theindividual(s) named above. If you, the reader of this message,are not the intended recipient, you are hereby notified that you should not further disseminate, distribute, or forward thisE-mail message. If you have received this E-mail in error,please notify the sender. Thank you Please NoteThe information in this E-mail message is legally privilegedand confidential information intended only for the use of theindividual(s) named above. If you, the reader of this message,are not the intended recipient, you are hereby notified that you should not further disseminate, distribute, or forward thisE-mail message. If you have received this E-mail in error,please notify the sender. Thank you
RE: SORT_AREA_SIZE question
Raj From our perspective as users of the operating system we expect when we release memory that the O.S. will immediately release it to other users. From the O.S. perspective, that is treated more like a suggestion. The O.S. has many tasks to accomplish and reusing released memory is pretty low on the list of priorities. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, June 25, 2003 1:31 PM To: Multiple recipients of list ORACLE-L Since we are talking of Sort area sizes here, I tried to collect some statistics on the amount of memory used by each dedicated Oracle connection. As I understand it, the PGA is allocated in chunks upto the SORT_AREA_SIZE. Initially, I just started a session, and noted down the memory used using pmap. Then, I did a small sort, and then, a huge sort, noting the memory utilization each time. I was even monitoring the PGA statistics in v$sysstat. I was expecting the sizes to increase, and then decrease when the sorting was done with. (retained_size was 0). I dont recall my observations rite now, but it was definitely not what I expected. Has anyone done something similar? Are my expectations rite? Thanks Raj DENNIS WILLIAMS [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] TOUCH.COMcc: Sent by: Subject: RE: SORT_AREA_SIZE question [EMAIL PROTECTED] ty.com 06/25/2003 12:30 PM Please respond to ORACLE-L Bart - No it hasn't been recently asked. 1. SORT_AREA_SIZE is per-process. So be a little wary. Keep in mind that in-memory sorts are much faster than disk sorts. But you knew that. 2. Ask your system administrator to monitor whether your system is getting paging/swapping. 3. Find out how many disk sorts are occurring. I prefer STATSPACK. Try to get a handle on how may disk vs. memory sorts are occurring. Try to increase SORT_AREA_SIZE until you have few disk sorts, but not so large you cause paging/swapping. Also look at your temp space settings to make sure when disk is used, it is used most efficiently. With a data mart, you may have some really large sorts that will always be too large for memory sorts. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, June 25, 2003 9:39 AM To: Multiple recipients of list ORACLE-L Hello, I joined this list last week, so I apologize in advance if I'm asking a question that has previously been answered. I am responsible for a reporting database/data mart that is approximately 175 GB. Our main fact table ranges from 1-14 GB depending upon how far along we are into our financial year. I have large reports that run full table scans on this table daily. In an effort to keep as much of the sorting in memory as possible I have specified SORT_AREA_SIZE to be 100MB. Some of the tuning books I am reading now are making me second-guess myself and I am wondering if this is overkill. Can anyone provide some advice on how large they are setting their SORT_AREA_SIZE values for their DSS systems? Thanks in advance, Bart -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: SORT_AREA_SIZE question
Bart, Another consideration is the effect of S_A_S on the CBO. Setting this (and letting other dependent parameters such as HASH_AREA_SIZE which is 2 x S_A_S unless set) may adversely affect the decisions that the CBO would take for otherwise 'sane' plans. As for the original problem, an ON LOGON system trigger should allow specific connections to set a larger SAS. And no one has mentioned SORT_AREA_RETAINED_SIZE yet... I wonder why! John Kanagaraj Oracle Applications DBA DB Soft Inc Work : (408) 970 7002 Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- Sent: Wednesday, June 25, 2003 11:55 AM To: Multiple recipients of list ORACLE-L Hi! At the least the wording is incorrect, a user who *connects* to instance, doesn't allocate any sort memory unless there is a need for sort. And if there's need for 100k, only that much will be allocated. And when the sort is over (and rows returned), the sort memory is freed. On the other hand, if all of the 10 users do huge sorts with sort_area_size 100M, they could have 1GB allocated altogether, even more than that, check my other mail. So yeah, this could introduce paging if having not enough memory. Btw, in mixed environments you could set sort_area_size to small value and create logon trigger which sets s_a_s big for reporting users.. just a thought about swapping issues... Tanel. - Original Message - To: Multiple recipients of list ORACLE-L Sent: Wednesday, June 25, 2003 6:39 PM Bart Each user which connects to the DB will consume that amount of RAM on top of any memory used by the application. A lot depends on how much memory your server has i.e. 10 users will use at least 1Gb of RAM. It could induce swapping which is a killer. Our DSS systems use 20Mb for sort area mind you we have 18Gb temp tablespace Regards Nigel Bishop Snr. Oracle DBA ioko Tel DDI: +44 (0) 1904 435 458 Mobile: +44 (0) 7881 624 386 Fax: +44 (0) 1904 435 450 Email:[EMAIL PROTECTED] www.ioko.com -Original Message- Sent: 25 June 2003 15:39 To: Multiple recipients of list ORACLE-L Hello, I joined this list last week, so I apologize in advance if I'm asking a question that has previously been answered. I am responsible for a reporting database/data mart that is approximately 175 GB. Our main fact table ranges from 1-14 GB depending upon how far along we are into our financial year. I have large reports that run full table scans on this table daily. In an effort to keep as much of the sorting in memory as possible I have specified SORT_AREA_SIZE to be 100MB. Some of the tuning books I am reading now are making me second-guess myself and I am wondering if this is overkill. Can anyone provide some advice on how large they are setting their SORT_AREA_SIZE values for their DSS systems? Thanks in advance, Bart Please Note The information in this E-mail message is legally privileged and confidential information intended only for the use of the individual(s) named above. If you, the reader of this message, are not the intended recipient, you are hereby notified that you should not further disseminate, distribute, or forward this E-mail message. If you have received this E-mail in error, please notify the sender. Thank you -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: SORT_AREA_SIZE question
Thanks Dennis. My question was not about the memory release, but more of the way memory is allocated to the PGA. Given that Oracle allocates memory in small chunks to the PGA until it approaches the sort area size, if I run a huge sort, should I expect the PGA memory to increase, until it reaches sort_area_size? 1. Initial dedicated connection established. The PGA used should be very minimal. 2. Say, I invoke a small sort on DBA_USERS. The PGA (or UGA) should increase by a very small value. 3. I invoke a sort on a huge table. I expect the PGA to keep increasing, and flatten out once it reaches the SORT_AREA_SIZE. I tried to monitor the memory utilization by the session using pmap, and v$sysstat, during these 3 steps, and did not see the expected results. It was a rudimentary test on a high volume database, so I guess I could have been wrong. I remember I tried this coz I read a post from Jonathan Lewis, suggesting that the initial memory for sorting is allocated in the UGA upto sort_area_retained_size, after which it is allocated from the PGA upto sort_area_size. Thanks Raj DENNIS WILLIAMS [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] TOUCH.COMcc: Sent by: Subject: RE: SORT_AREA_SIZE question [EMAIL PROTECTED] ty.com 06/25/2003 04:27 PM Please respond to ORACLE-L Raj From our perspective as users of the operating system we expect when we release memory that the O.S. will immediately release it to other users. From the O.S. perspective, that is treated more like a suggestion. The O.S. has many tasks to accomplish and reusing released memory is pretty low on the list of priorities. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, June 25, 2003 1:31 PM To: Multiple recipients of list ORACLE-L Since we are talking of Sort area sizes here, I tried to collect some statistics on the amount of memory used by each dedicated Oracle connection. As I understand it, the PGA is allocated in chunks upto the SORT_AREA_SIZE. Initially, I just started a session, and noted down the memory used using pmap. Then, I did a small sort, and then, a huge sort, noting the memory utilization each time. I was even monitoring the PGA statistics in v$sysstat. I was expecting the sizes to increase, and then decrease when the sorting was done with. (retained_size was 0). I dont recall my observations rite now, but it was definitely not what I expected. Has anyone done something similar? Are my expectations rite? Thanks Raj DENNIS WILLIAMS [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] TOUCH.COMcc: Sent by: Subject: RE: SORT_AREA_SIZE question [EMAIL PROTECTED] ty.com 06/25/2003 12:30 PM Please respond to ORACLE-L Bart - No it hasn't been recently asked. 1. SORT_AREA_SIZE is per-process. So be a little wary. Keep in mind that in-memory sorts are much faster than disk sorts. But you knew that. 2. Ask your system administrator to monitor whether your system is getting paging/swapping. 3. Find out how many disk sorts are occurring. I prefer STATSPACK. Try to get a handle on how may disk vs. memory sorts
Re: SORT_AREA_SIZE question
Hi! You are getting some things a bit wrong here. With pmap you see the whole process, which is much more than just sort area which resides in CGA which resides in PGA which resides in memory allocated to the process. There is much more memory needed for a process, such text, data, and uninitialized data segments. Text segment is read-only, thus shared and can be counted only once when calculating memory requirements (correct if I'm wrong). Others are writable, thus separately allocated for each process. Also there's stack, memory for cursor, probably lot's more of stuff there. Why you don't see OS memory decreasing for a process when sort is over is because free() call doesn't return memory allocated with malloc() to OS immediately (actually it'll wait for OS to collect the memory... er.. I believe). If never used again, the unused pages are paged out, thus not harming anyone, but hey, in Oracle we expect continous operations, so when next query is executed, malloc() can get exactly same pages back, no memory is wasted. Btw, you can check the size of executable using (believe the uninitialized data segment isn't allocated during executable load, others are) bash-2.03$ /usr/ccs/bin/size $ORACLE_HOME/bin/oracle 27230877 + 267348 + 21108 = 27519333 --Above: Text + data + uninitialized sizes for oracle executable (9.2 on solaris) Lot's of what I've written, comes from Steve Adams site, so you really ought to check http://www.ixora.com.au/newsletter/2000_11.htm for example. Cheers, Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, June 26, 2003 1:04 AM Thanks Dennis. My question was not about the memory release, but more of the way memory is allocated to the PGA. Given that Oracle allocates memory in small chunks to the PGA until it approaches the sort area size, if I run a huge sort, should I expect the PGA memory to increase, until it reaches sort_area_size? 1. Initial dedicated connection established. The PGA used should be very minimal. 2. Say, I invoke a small sort on DBA_USERS. The PGA (or UGA) should increase by a very small value. 3. I invoke a sort on a huge table. I expect the PGA to keep increasing, and flatten out once it reaches the SORT_AREA_SIZE. I tried to monitor the memory utilization by the session using pmap, and v$sysstat, during these 3 steps, and did not see the expected results. It was a rudimentary test on a high volume database, so I guess I could have been wrong. I remember I tried this coz I read a post from Jonathan Lewis, suggesting that the initial memory for sorting is allocated in the UGA upto sort_area_retained_size, after which it is allocated from the PGA upto sort_area_size. Thanks Raj DENNIS WILLIAMS [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] TOUCH.COMcc: Sent by: Subject: RE: SORT_AREA_SIZE question [EMAIL PROTECTED] ty.com 06/25/2003 04:27 PM Please respond to ORACLE-L Raj From our perspective as users of the operating system we expect when we release memory that the O.S. will immediately release it to other users. From the O.S. perspective, that is treated more like a suggestion. The O.S. has many tasks to accomplish and reusing released memory is pretty low on the list of priorities. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, June 25, 2003 1:31 PM To: Multiple recipients of list ORACLE-L Since we are talking of Sort area sizes here, I tried to collect some statistics on the amount of memory used by each dedicated Oracle connection. As I understand it, the PGA is allocated in chunks upto the SORT_AREA_SIZE. Initially, I just started a session, and noted down the memory used using pmap. Then, I did a small sort, and then, a huge sort, noting the memory utilization each time. I was even monitoring the PGA statistics in v$sysstat. I was expecting the sizes to increase, and then decrease when the sorting was done with. (retained_size was 0). I dont recall my observations rite now, but it was definitely not what I expected. Has anyone done something similar? Are my expectations rite? Thanks Raj DENNIS WILLIAMS [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] TOUCH.COMcc: Sent by: Subject: RE: SORT_AREA_SIZE question [EMAIL PROTECTED] ty.com 06/25/2003 12:30 PM Please respond to ORACLE-L
Re: SORT_AREA_SIZE question
The original question does not appear to have been answered. The question on 'reporting database/data mart' with lots of FTS does not suggest S_A_S will provide any significant benefits regardless of how you tweak the parameters. What is more likely to provide benefits is to look at how the DSS type of environment is designed and constructed, especially partitioning, and for 'datamarts', you have already constructed the schemas such that the queries are simple and the only sorting you need to do are to ensure the results are returned in order. As for pmap showing memory utilisation, it is not that straightforward to interpret - there is a Metalink doc showing how to do that correctly. 'top' gives a pretty good idea once you have worked out what the SGA component of the shared-memory should be. As for S_A_S, I suspect it will buy you very little. Look at all the other DW types of features you can make use of - like range-partitioning, hash-partitioning, or some combination thereoff. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, June 26, 2003 10:04 AM Thanks Dennis. My question was not about the memory release, but more of the way memory is allocated to the PGA. Given that Oracle allocates memory in small chunks to the PGA until it approaches the sort area size, if I run a huge sort, should I expect the PGA memory to increase, until it reaches sort_area_size? 1. Initial dedicated connection established. The PGA used should be very minimal. 2. Say, I invoke a small sort on DBA_USERS. The PGA (or UGA) should increase by a very small value. 3. I invoke a sort on a huge table. I expect the PGA to keep increasing, and flatten out once it reaches the SORT_AREA_SIZE. I tried to monitor the memory utilization by the session using pmap, and v$sysstat, during these 3 steps, and did not see the expected results. It was a rudimentary test on a high volume database, so I guess I could have been wrong. I remember I tried this coz I read a post from Jonathan Lewis, suggesting that the initial memory for sorting is allocated in the UGA upto sort_area_retained_size, after which it is allocated from the PGA upto sort_area_size. Thanks Raj DENNIS WILLIAMS [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] TOUCH.COMcc: Sent by: Subject: RE: SORT_AREA_SIZE question [EMAIL PROTECTED] ty.com 06/25/2003 04:27 PM Please respond to ORACLE-L Raj From our perspective as users of the operating system we expect when we release memory that the O.S. will immediately release it to other users. From the O.S. perspective, that is treated more like a suggestion. The O.S. has many tasks to accomplish and reusing released memory is pretty low on the list of priorities. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, June 25, 2003 1:31 PM To: Multiple recipients of list ORACLE-L Since we are talking of Sort area sizes here, I tried to collect some statistics on the amount of memory used by each dedicated Oracle connection. As I understand it, the PGA is allocated in chunks upto the SORT_AREA_SIZE. Initially, I just started a session, and noted down the memory used using pmap. Then, I did a small sort, and then, a huge sort, noting the memory utilization each time. I was even monitoring the PGA statistics in v$sysstat. I was expecting the sizes to increase, and then decrease when the sorting was done with. (retained_size was 0). I dont recall my observations rite now, but it was definitely not what I expected. Has anyone done something similar? Are my expectations rite? Thanks Raj DENNIS WILLIAMS [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] TOUCH.COMcc: Sent by: Subject: RE: SORT_AREA_SIZE question [EMAIL PROTECTED] ty.com 06/25/2003 12:30 PM Please respond to ORACLE-L Bart - No it hasn't been recently asked. 1. SORT_AREA_SIZE is per-process. So be a little wary. Keep in mind that in-memory sorts are much faster than disk sorts. But you knew that. 2. Ask your system administrator to monitor whether your system is getting paging/swapping. 3. Find out how many disk sorts are occurring. I prefer STATSPACK. Try to get a handle on how may disk vs. memory sorts are occurring. Try to increase SORT_AREA_SIZE until you have few disk sorts, but not so
Re: SORT_AREA_SIZE question
Thanks for the reply. I did use the Metalink Note to calculate pmap, minus the shared memory, et all. The problem with pmap is how do I make out what portion of this memory is UGA, and PGA? So, I look at v$sysstat. Anyway, hope to pursue this test in a much more detailed manner, when I have the time on hand. Of Course, I take everything that Steve Adams says to be the holy gospel, and have his site listed under favourites. Thanks Once again. Raj Tanel Poder tanel.poder.00 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] [EMAIL PROTECTED]cc: Sent by: Subject: Re: SORT_AREA_SIZE question [EMAIL PROTECTED] ty.com 06/25/2003 07:51 PM Please respond to ORACLE-L Hi! You are getting some things a bit wrong here. With pmap you see the whole process, which is much more than just sort area which resides in CGA which resides in PGA which resides in memory allocated to the process. There is much more memory needed for a process, such text, data, and uninitialized data segments. Text segment is read-only, thus shared and can be counted only once when calculating memory requirements (correct if I'm wrong). Others are writable, thus separately allocated for each process. Also there's stack, memory for cursor, probably lot's more of stuff there. Why you don't see OS memory decreasing for a process when sort is over is because free() call doesn't return memory allocated with malloc() to OS immediately (actually it'll wait for OS to collect the memory... er.. I believe). If never used again, the unused pages are paged out, thus not harming anyone, but hey, in Oracle we expect continous operations, so when next query is executed, malloc() can get exactly same pages back, no memory is wasted. Btw, you can check the size of executable using (believe the uninitialized data segment isn't allocated during executable load, others are) bash-2.03$ /usr/ccs/bin/size $ORACLE_HOME/bin/oracle 27230877 + 267348 + 21108 = 27519333 --Above: Text + data + uninitialized sizes for oracle executable (9.2 on solaris) Lot's of what I've written, comes from Steve Adams site, so you really ought to check http://www.ixora.com.au/newsletter/2000_11.htm for example. Cheers, Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, June 26, 2003 1:04 AM Thanks Dennis. My question was not about the memory release, but more of the way memory is allocated to the PGA. Given that Oracle allocates memory in small chunks to the PGA until it approaches the sort area size, if I run a huge sort, should I expect the PGA memory to increase, until it reaches sort_area_size? 1. Initial dedicated connection established. The PGA used should be very minimal. 2. Say, I invoke a small sort on DBA_USERS. The PGA (or UGA) should increase by a very small value. 3. I invoke a sort on a huge table. I expect the PGA to keep increasing, and flatten out once it reaches the SORT_AREA_SIZE. I tried to monitor the memory utilization by the session using pmap, and v$sysstat, during these 3 steps, and did not see the expected results. It was a rudimentary test on a high volume database, so I guess I could have been wrong. I remember I tried this coz I read a post from Jonathan Lewis, suggesting that the initial memory for sorting is allocated in the UGA upto sort_area_retained_size, after which it is allocated from the PGA upto sort_area_size. Thanks Raj
