Re: using temp tables for staging databases?
What you have described is expected behaviour - if"the next insert would drop amount of free space less than PCTFREE", the block is unlinked. What Ihad notconsidered is what happenswhen the block is still below PCTUSED? As usual, Steve Adams' website explains this very nicely - it is not unlinked, just walked over, so the next smaller insert is likely to succeed. And as you pointed out, It would indeed take rows that are a large proportion of blocksize for this effect to have an impact. - Original Message - From: Tanel Poder To: Multiple recipients of list ORACLE-L Sent: Saturday, October 25, 2003 10:09 PM Subject: Re: using temp tables for staging databases? No, blocks won't fill up to PCTFREE in case a block is already above PCTUSED and the next insert would drop amount of free space less than PCTFREE. Blocks are just unlinked from freelist in this case. That means if you normally have 10byte inserts and occasionally have 4000 byte inserts in your table, then you might be wasting space due to premature unlink of blocks in freelist.
Re: using temp tables for staging databases?
Hi,
The walking in the freelist is just 5 blocks (or
the value of _walk_insert_threshold number of blocks) and I belive tanel
is talking
about (_release_insert_threshold) unlinking from
freelist, which also default to 5 blocks.
KG
- Original Message -
From:
Binley
Lim
To: Multiple recipients of list ORACLE-L
Sent: Monday, October 27, 2003 4:59
PM
Subject: Re: using temp tables for
staging databases?
What you have described is expected behaviour -
if"the next insert would drop amount of free space less than PCTFREE",
the block is unlinked.
What Ihad notconsidered is what
happenswhen the block is still below PCTUSED? As usual, Steve Adams'
website explains this very nicely - it is not unlinked, just walked over, so
the next smaller insert is likely to succeed. And as you pointed out, It would
indeed take rows that are a large proportion of blocksize for this effect to
have an impact.
- Original Message -
From:
Tanel
Poder
To: Multiple recipients of list ORACLE-L
Sent: Saturday, October 25, 2003 10:09
PM
Subject: Re: using temp tables for
staging databases?
No, blocks won't fill up to PCTFREE in case a
block is already above PCTUSED and the next insert would drop amount of free
space less than PCTFREE. Blocks are just unlinked from freelist in this
case. That means if you normally have 10byte inserts and occasionally have
4000 byte inserts in your table, then you might be wasting space due to
premature unlink of blocks in
freelist.
Re: using temp tables for staging databases?
No, blocks won't fill up to PCTFREE in case a block is already above PCTUSED and the next insert would drop amount of free space less than PCTFREE. Blocks are just unlinked from freelist in this case. That means if you normally have 10byte inserts and occasionally have 4000 byte inserts in your table, then you might be wasting space due to premature unlink of blocks in freelist. So, as Arup already said, PCTFREE and PCTUSED definitely affect block space utilization, even in insert-only environment. Tanel. - Original Message - From: Binley Lim To: Multiple recipients of list ORACLE-L Sent: Wednesday, October 22, 2003 6:39 AM Subject: Re: using temp tables for staging databases? Yes, I understand BBW, and how PCTFREE affects block density, and hence BBW. My point, which you appear to have missed, is PCTUSED does not affect block densitywhich is determined by PCTFREE. Lets take a simple case of 10% PCTFREE, and 40% PCTUSED. Blocks will fill up to 90% _no matter_ what you make the PCTUSED figure to be. Only if you havelots of updates/deletes that PCTUSED comes into play but then you have a 90% packed block to begin with, so its effect is marginal.PCTFREE alone will reduce block density if that is the aim. In your paper, you changed several parameters at the same time - including freelists which does not affect block density. From a statistical/testing point of view you cannot really draw any conclusions. Lower block density mayreduce BBWs, but you still have to process the same number of rows. And you have to process a higher number of blocks which incurs an additional cost. Question becomes - has the problem been simply shifted somewhere else? - Original Message - From: Arup Nanda To: Multiple recipients of list ORACLE-L Sent: Wednesday, October 22, 2003 4:19 AM Subject: Re: using temp tables for staging databases? Binley,The cause of Buffer Busy Waits (BBW) is not exclusively the setting of PCTUSED and PCTFREE; they just two of the causes. To understand the connection, let me explain a little bit on the cause of BBWs.When a session requests some data element from a table, the server process of the session gets the block from the disk to the cache (assume the block is not present in the cache). The event of the block coming from the disk to occupy a buffer in the caceh is pretty straight forward. Now, imagaine, at the exact same time another session selects a row from the same block. A *different* row but from the *same* block. That session will search the cache buffer chain and see that the buffer is not present and will attempt the same maneuevre, i.e. get the buffer from the disk. However, the first session is currently moving the buffer; the second session has to *wait* till the process is complete. This wait is known as buffer busy wait (BBW); but I guess you already knew that. The two sessions are not in conflict over the same row, but the same buffer; so it's not locking contention.How can we eliminate BBWs? Unfortunately we can't bring it to zero. There is always a probability that two sessions will try to get the same block. The only exception is when a block contains only one row. In that case the sessions will select different blocks for different rows. Again, this is not practical. We can reduce BBW by reducing the *possibility* that two sessions will not try to access the same block. This can be done using several ways:(1) reducing the block size(2) making a block less compact, so that each block holds less number of rows. The fewer the number of rows in a block, the lesser the probability that two sessions will access rows in the same block.The first option is not a very practical one in most cases. The second option is. It can be effected by allocating less space in a block, which can be done by using a large value of PCTFREE, e.g. 40 and/or small value of PCTUSED, such as 40, instead of 99. Other ways to achieve the same result is using a higher value of INITRANS, or anything that will cause less number of rows to fill up a block. Less rows = less chance of BBW occuring.I wrote a paper in Select Journal a few months ago explaining this very situation. Although the article is on Segment Level Statistics, it has an example which you can simulate to see the effect of PCTFREE/PCTUSED/INITRANS on Buffer Busy Waits. It can be downlaoded from my website at www.proligence.com/downloads.html and choose New Tool on the Block - Segment Level Statistics. Please feel free to give it a whirl.Further qualifying the case for higher PCTUSED and lower PCTFREE in datawarehouse environments, the chance that two sessions will access the row in same
Re: using temp tables for staging databases?
Hi Arup, Having a low PCTUSED will achieve nothing with regard to row density per block if there are no deletes or really really significant updates that reduce row lengths. Therefore, using it to reduce BBW is a doubtful method. That said, using say PCTFREE (for which a high value might be effective) to artificially reduce row density per block and hence possibly reduce BBW is also dangerous. Wasted space below the HWM and the extra LIOs associated with FTS can cause more issues than the BBWs you're trying to avoid. Especially if FTS are common and the BBWs are as a result of poor freelist management during inserts ... In my opinion, the reason for the default settings for PCTFREE and PCTUSED is that a row must be greater than 50% of the block size for the insertion to fail whist the block is currently *under* the PCTUSED value (for which a new block is required). This would be the worst case scenario. IMHO, a PCTUSED of 40 is dangerous when tables have sparse/random deletions as this could again result in wasted space below the HWM. FTS would just hate you for it. Ideally PCTFREE should be sized to accommodate average row growths, PCTUSED should be sized to efficiently reclaim deleted space without excessive freelist overheads. It's easier said than done ;) Cheers Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, October 21, 2003 10:29 PM Mark, While waiting for Tim, I can offer another situation - in datawarehouses, where the subsequent updates are not likely to occur. Also, space is a premium and packing the blocks as densly as populated might be necessary. I will also add to Tim's response of justifying a smaller PCTUSED. In addition to the freelist problem he mentioned, there is also a greater chance of buffer busy waits occuring when a block contains too many rows. In an OLTP database that is certainly likely to happen - another case for the default 40 setting for the parameter. In DW, however, the chances of BBW are low, hence a higher setting may be possible. HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, October 21, 2003 5:19 AM Tim, Can you sum up a few situations when the need *has* arisen to change these values? Cheers Mark -Original Message- Tim Gorman Sent: 21 October 2003 06:09 To: Multiple recipients of list ORACLE-L Unless you typo'd, there are some serious problems here... Setting PCTFREE to 99 is not likely to pack in the blocks. Rather the opposite; you are instead leaving blocks 99% empty. Quite a bit of wasted I/O in performing a FULL table scan here... :-) Anyway, it is not a good idea to have PCTFREE and PCTUSED sum to a value greater than 70 or 80 or so, just as a rule of thumb. Having them sum to a value near 100 ensures that each insert, delete, or even update will potentially cause the block to be removed or reinserted to one of the segment's free list. Think about it: the width of a single row crossing the boundary from off the free list to on the free list. Better to leave a bit of a no man's land between the two values. The default settings of PCTFREE=10 and PCTUSED=40 are one of the few default settings that need little manipulation for most situations. on 10/20/03 7:34 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: we drop and recreate the temp tables every night. We also use PCTFREE PCTUSED at 99 and 1 to pack in the blocks and we use very small extent sizes. then we analyze with an estimate size of 20 percent which is quite fast. All of them are used for full table scans and do not have indexes. Ive found that a 'create table as' is MUCH faster than inserting into global temporary tables when you do not have to worry about latch contention(ie 1-3 users logged in at a time). anyone else notice this? Seems to go against conventional wisdom which says never use them. So I want to make sure Im not missing something. From: Tim Gorman [EMAIL PROTECTED] Date: 2003/10/20 Mon AM 10:19:33 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: using temp tables for staging databases? All the time. Oracle Apps's open interfaces are built this way, for example. However, the guys here covered their bases by specifying smaller temporary tables, as if they could prevent them from becoming large. I suppose they might feel that they indemnify themselves if the tables should ever become large? As with OraApps open interface tables, it is when a large volume of data is pushed through that the trouble starts. The high-water marks on all the tables are pushed to a high level, thereafter causing full table scans on the interface/temporary tables to run slowly. The only way to bring the HWM back down
Re: using temp tables for staging databases?
:09 To: Multiple recipients of list ORACLE-L Unless you typo'd, there are some serious problems here... Setting PCTFREE to 99 is not likely to pack in the blocks. Rather the opposite; you are instead leaving blocks 99% empty. Quite a bit of wasted I/O in performing a FULL table scan here... :-) Anyway, it is not a good idea to have PCTFREE and PCTUSED sum to a value greater than 70 or 80 or so, just as a rule of thumb. Having them sum to a value near 100 ensures that each insert, delete, or even update will potentially cause the block to be removed or reinserted to one of the segment's free list. Think about it: the width of a single row crossing the boundary from off the free list to on the free list. Better to leave a bit of a no man's land between the two values. The default settings of PCTFREE=10 and PCTUSED=40 are one of the few default settings that need little manipulation for most situations. on 10/20/03 7:34 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: we drop and recreate the temp tables every night. We also use PCTFREE PCTUSED at 99 and 1 to pack in the blocks and we use very small extent sizes. then we analyze with an estimate size of 20 percent which is quite fast. All of them are used for full table scans and do not have indexes. Ive found that a 'create table as' is MUCH faster than inserting into global temporary tables when you do not have to worry about latch contention(ie 1-3 users logged in at a time). anyone else notice this? Seems to go against conventional wisdom which says never use them. So I want to make sure Im not missing something. From: Tim Gorman [EMAIL PROTECTED] Date: 2003/10/20 Mon AM 10:19:33 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: using temp tables for staging databases? All the time. Oracle Apps's open interfaces are built this way, for example. However, the guys here covered their bases by specifying smaller temporary tables, as if they could prevent them from becoming large. I suppose they might feel that they indemnify themselves if the tables should ever become large? As with OraApps open interface tables, it is when a large volume of data is pushed through that the trouble starts. The high-water marks on all the tables are pushed to a high level, thereafter causing full table scans on the interface/temporary tables to run slowly. The only way to bring the HWM back down is quiesce the interface/app and then truncate the tables. on 10/20/03 6:39 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: This is for non-transactional data load instances. The guys here sware that by using smaller temporary tables(not global temp tables) they can increase the speed of the data loads. Not worried about latch contention because its just for bulk loads. I know this bad in transactional instances. Has anyone used these in non-transactional data load instances? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman 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: Tim Gorman 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). --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.524 / Virus Database: 321 - Release Date: 06/10/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.524 / Virus Database: 321 - Release Date: 06/10/2003 -- Please see the official ORACLE-L FAQ
RE: using temp tables for staging databases?
Tim, Can you sum up a few situations when the need *has* arisen to change these values? Cheers Mark -Original Message- Tim Gorman Sent: 21 October 2003 06:09 To: Multiple recipients of list ORACLE-L Unless you typo'd, there are some serious problems here... Setting PCTFREE to 99 is not likely to pack in the blocks. Rather the opposite; you are instead leaving blocks 99% empty. Quite a bit of wasted I/O in performing a FULL table scan here... :-) Anyway, it is not a good idea to have PCTFREE and PCTUSED sum to a value greater than 70 or 80 or so, just as a rule of thumb. Having them sum to a value near 100 ensures that each insert, delete, or even update will potentially cause the block to be removed or reinserted to one of the segment's free list. Think about it: the width of a single row crossing the boundary from off the free list to on the free list. Better to leave a bit of a no man's land between the two values. The default settings of PCTFREE=10 and PCTUSED=40 are one of the few default settings that need little manipulation for most situations. on 10/20/03 7:34 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: we drop and recreate the temp tables every night. We also use PCTFREE PCTUSED at 99 and 1 to pack in the blocks and we use very small extent sizes. then we analyze with an estimate size of 20 percent which is quite fast. All of them are used for full table scans and do not have indexes. Ive found that a 'create table as' is MUCH faster than inserting into global temporary tables when you do not have to worry about latch contention(ie 1-3 users logged in at a time). anyone else notice this? Seems to go against conventional wisdom which says never use them. So I want to make sure Im not missing something. From: Tim Gorman [EMAIL PROTECTED] Date: 2003/10/20 Mon AM 10:19:33 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: using temp tables for staging databases? All the time. Oracle Apps's open interfaces are built this way, for example. However, the guys here covered their bases by specifying smaller temporary tables, as if they could prevent them from becoming large. I suppose they might feel that they indemnify themselves if the tables should ever become large? As with OraApps open interface tables, it is when a large volume of data is pushed through that the trouble starts. The high-water marks on all the tables are pushed to a high level, thereafter causing full table scans on the interface/temporary tables to run slowly. The only way to bring the HWM back down is quiesce the interface/app and then truncate the tables. on 10/20/03 6:39 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: This is for non-transactional data load instances. The guys here sware that by using smaller temporary tables(not global temp tables) they can increase the speed of the data loads. Not worried about latch contention because its just for bulk loads. I know this bad in transactional instances. Has anyone used these in non-transactional data load instances? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman 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: Tim Gorman 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). --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.524 / Virus Database: 321 - Release Date: 06/10/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.524 / Virus Database: 321 - Release Date: 06/10/2003 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web
Re: using temp tables for staging databases?
Mark, While waiting for Tim, I can offer another situation - in datawarehouses, where the subsequent updates are not likely to occur. Also, space is a premium and packing the blocks as densly as populated might be necessary. I will also add to Tim's response of justifying a smaller PCTUSED. In addition to the freelist problem he mentioned, there is also a greater chance of buffer busy waits occuring when a block contains too many rows. In an OLTP database that is certainly likely to happen - another case for the default 40 setting for the parameter. In DW, however, the chances of BBW are low, hence a higher setting may be possible. HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, October 21, 2003 5:19 AM Tim, Can you sum up a few situations when the need *has* arisen to change these values? Cheers Mark -Original Message- Tim Gorman Sent: 21 October 2003 06:09 To: Multiple recipients of list ORACLE-L Unless you typo'd, there are some serious problems here... Setting PCTFREE to 99 is not likely to pack in the blocks. Rather the opposite; you are instead leaving blocks 99% empty. Quite a bit of wasted I/O in performing a FULL table scan here... :-) Anyway, it is not a good idea to have PCTFREE and PCTUSED sum to a value greater than 70 or 80 or so, just as a rule of thumb. Having them sum to a value near 100 ensures that each insert, delete, or even update will potentially cause the block to be removed or reinserted to one of the segment's free list. Think about it: the width of a single row crossing the boundary from off the free list to on the free list. Better to leave a bit of a no man's land between the two values. The default settings of PCTFREE=10 and PCTUSED=40 are one of the few default settings that need little manipulation for most situations. on 10/20/03 7:34 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: we drop and recreate the temp tables every night. We also use PCTFREE PCTUSED at 99 and 1 to pack in the blocks and we use very small extent sizes. then we analyze with an estimate size of 20 percent which is quite fast. All of them are used for full table scans and do not have indexes. Ive found that a 'create table as' is MUCH faster than inserting into global temporary tables when you do not have to worry about latch contention(ie 1-3 users logged in at a time). anyone else notice this? Seems to go against conventional wisdom which says never use them. So I want to make sure Im not missing something. From: Tim Gorman [EMAIL PROTECTED] Date: 2003/10/20 Mon AM 10:19:33 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: using temp tables for staging databases? All the time. Oracle Apps's open interfaces are built this way, for example. However, the guys here covered their bases by specifying smaller temporary tables, as if they could prevent them from becoming large. I suppose they might feel that they indemnify themselves if the tables should ever become large? As with OraApps open interface tables, it is when a large volume of data is pushed through that the trouble starts. The high-water marks on all the tables are pushed to a high level, thereafter causing full table scans on the interface/temporary tables to run slowly. The only way to bring the HWM back down is quiesce the interface/app and then truncate the tables. on 10/20/03 6:39 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: This is for non-transactional data load instances. The guys here sware that by using smaller temporary tables(not global temp tables) they can increase the speed of the data loads. Not worried about latch contention because its just for bulk loads. I know this bad in transactional instances. Has anyone used these in non-transactional data load instances? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman 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: Tim Gorman 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
Re: using temp tables for staging databases?
For PCTFREE, setting it to less than the default of 10 is an option for tables that are INSERT-only where someone wants to pack rows into the blocks. The attendent risks are that UPDATEs causing row expansion may be forced to migrate to another block, thus hurting subsequent query performance. Setting PCTFREE greater than the default of 10 is a good idea if you have experienced row-migration in the past (or expect to experience it) and you want to leave more free space in each block to accommodate row expansion from UPDATE statements. For PCTUSED, I just can't imagine any practical real-world reasons to change it from the default of 40. I'm sure someone else can... on 10/21/03 2:19 AM, Mark Leith at [EMAIL PROTECTED] wrote: Tim, Can you sum up a few situations when the need *has* arisen to change these values? Cheers Mark -Original Message- Tim Gorman Sent: 21 October 2003 06:09 To: Multiple recipients of list ORACLE-L Unless you typo'd, there are some serious problems here... Setting PCTFREE to 99 is not likely to pack in the blocks. Rather the opposite; you are instead leaving blocks 99% empty. Quite a bit of wasted I/O in performing a FULL table scan here... :-) Anyway, it is not a good idea to have PCTFREE and PCTUSED sum to a value greater than 70 or 80 or so, just as a rule of thumb. Having them sum to a value near 100 ensures that each insert, delete, or even update will potentially cause the block to be removed or reinserted to one of the segment's free list. Think about it: the width of a single row crossing the boundary from off the free list to on the free list. Better to leave a bit of a no man's land between the two values. The default settings of PCTFREE=10 and PCTUSED=40 are one of the few default settings that need little manipulation for most situations. on 10/20/03 7:34 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: we drop and recreate the temp tables every night. We also use PCTFREE PCTUSED at 99 and 1 to pack in the blocks and we use very small extent sizes. then we analyze with an estimate size of 20 percent which is quite fast. All of them are used for full table scans and do not have indexes. Ive found that a 'create table as' is MUCH faster than inserting into global temporary tables when you do not have to worry about latch contention(ie 1-3 users logged in at a time). anyone else notice this? Seems to go against conventional wisdom which says never use them. So I want to make sure Im not missing something. From: Tim Gorman [EMAIL PROTECTED] Date: 2003/10/20 Mon AM 10:19:33 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: using temp tables for staging databases? All the time. Oracle Apps's open interfaces are built this way, for example. However, the guys here covered their bases by specifying smaller temporary tables, as if they could prevent them from becoming large. I suppose they might feel that they indemnify themselves if the tables should ever become large? As with OraApps open interface tables, it is when a large volume of data is pushed through that the trouble starts. The high-water marks on all the tables are pushed to a high level, thereafter causing full table scans on the interface/temporary tables to run slowly. The only way to bring the HWM back down is quiesce the interface/app and then truncate the tables. on 10/20/03 6:39 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: This is for non-transactional data load instances. The guys here sware that by using smaller temporary tables(not global temp tables) they can increase the speed of the data loads. Not worried about latch contention because its just for bulk loads. I know this bad in transactional instances. Has anyone used these in non-transactional data load instances? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman 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: Tim Gorman 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
Re: Re: using temp tables for staging databases?
We set our staging tables to 1 percent free and 99 percent used. We do this to keep the datafile as small as possible. This is because we have to tranport it to other servers when we publish. There by cutting down on the time it takes to copy the file and send it across the pipe. We have cut our copy times down by 2/3s doing this. Row migration? We have been doing this for about 5 months now with large nightly loads. I have about 4-5 tables to rebuild in the next few days. That isnt bad. We actually had one table get to 25% migrated rows and in a light transaction database there was no noticeable performance degradation. Ill still rebuild it. If you have large tables and you need to do alot of full table scans percent used 40% is way too low. It blows up the table and increases the number of physical I/Os. You have alot of transactions on your system andthen your PIOs blow up. There is a script called 'sparse_tables' or something like that on steve adams site that tells you whether you should consider changing your pctused. Im on my first project using an NAS storage system. We have about 8-10 servers attached to the same cluster right now and I/O is a definite issue, since it all goes across the same pipe. From: Tim Gorman [EMAIL PROTECTED] Date: 2003/10/21 Tue AM 09:24:25 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: using temp tables for staging databases? For PCTFREE, setting it to less than the default of 10 is an option for tables that are INSERT-only where someone wants to pack rows into the blocks. The attendent risks are that UPDATEs causing row expansion may be forced to migrate to another block, thus hurting subsequent query performance. Setting PCTFREE greater than the default of 10 is a good idea if you have experienced row-migration in the past (or expect to experience it) and you want to leave more free space in each block to accommodate row expansion from UPDATE statements. For PCTUSED, I just can't imagine any practical real-world reasons to change it from the default of 40. I'm sure someone else can... on 10/21/03 2:19 AM, Mark Leith at [EMAIL PROTECTED] wrote: Tim, Can you sum up a few situations when the need *has* arisen to change these values? Cheers Mark -Original Message- Tim Gorman Sent: 21 October 2003 06:09 To: Multiple recipients of list ORACLE-L Unless you typo'd, there are some serious problems here... Setting PCTFREE to 99 is not likely to pack in the blocks. Rather the opposite; you are instead leaving blocks 99% empty. Quite a bit of wasted I/O in performing a FULL table scan here... :-) Anyway, it is not a good idea to have PCTFREE and PCTUSED sum to a value greater than 70 or 80 or so, just as a rule of thumb. Having them sum to a value near 100 ensures that each insert, delete, or even update will potentially cause the block to be removed or reinserted to one of the segment's free list. Think about it: the width of a single row crossing the boundary from off the free list to on the free list. Better to leave a bit of a no man's land between the two values. The default settings of PCTFREE=10 and PCTUSED=40 are one of the few default settings that need little manipulation for most situations. on 10/20/03 7:34 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: we drop and recreate the temp tables every night. We also use PCTFREE PCTUSED at 99 and 1 to pack in the blocks and we use very small extent sizes. then we analyze with an estimate size of 20 percent which is quite fast. All of them are used for full table scans and do not have indexes. Ive found that a 'create table as' is MUCH faster than inserting into global temporary tables when you do not have to worry about latch contention(ie 1-3 users logged in at a time). anyone else notice this? Seems to go against conventional wisdom which says never use them. So I want to make sure Im not missing something. From: Tim Gorman [EMAIL PROTECTED] Date: 2003/10/20 Mon AM 10:19:33 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: using temp tables for staging databases? All the time. Oracle Apps's open interfaces are built this way, for example. However, the guys here covered their bases by specifying smaller temporary tables, as if they could prevent them from becoming large. I suppose they might feel that they indemnify themselves if the tables should ever become large? As with OraApps open interface tables, it is when a large volume of data is pushed through that the trouble starts. The high-water marks on all the tables are pushed to a high level, thereafter causing full table scans on the interface/temporary tables to run slowly. The only way to bring the HWM back down is quiesce the interface/app and then truncate the tables
RE: using temp tables for staging databases?
Hi, Mark. I'm not Tim, but I did encounter such a situation. This was not a temp table, but a permanent one. We have a db with a very strange block size of 4608 (actually Tim is painfully aware of this one). We have a very large table in this database. It was expanding at about 200 megs per week -- way out of control for a relataively small database. The database was not reusing blocks. Oracle recommends that (100% - (pctfree+pcused)) be greater than the maximum sie of a row. So we did an exact calculation of the blocksize less %free+%used 1% of a block is 46.08 80% of a block is 3686.4 4608 - (46.08 + 3686.4) = 875.52 our largest row length is 860 So we set pctfree at 1% and pctused at 80% One of the reasons we can get by with this is because the vendor designed the database with all char (not varchar2), so we pretty much know exactly what each row is going to consume. (It's a Cobol app) After this change, the database stopped it's wild expansion. Not a normal situation, but then nothing here is normal. (Kids -- don't try this at home!) Barb --- Mark Leith [EMAIL PROTECTED] wrote: Tim, Can you sum up a few situations when the need *has* arisen to change these values? Cheers Mark -Original Message- Tim Gorman Sent: 21 October 2003 06:09 To: Multiple recipients of list ORACLE-L Unless you typo'd, there are some serious problems here... Setting PCTFREE to 99 is not likely to pack in the blocks. Rather the opposite; you are instead leaving blocks 99% empty. Quite a bit of wasted I/O in performing a FULL table scan here... :-) Anyway, it is not a good idea to have PCTFREE and PCTUSED sum to a value greater than 70 or 80 or so, just as a rule of thumb. Having them sum to a value near 100 ensures that each insert, delete, or even update will potentially cause the block to be removed or reinserted to one of the segment's free list. Think about it: the width of a single row crossing the boundary from off the free list to on the free list. Better to leave a bit of a no man's land between the two values. The default settings of PCTFREE=10 and PCTUSED=40 are one of the few default settings that need little manipulation for most situations. on 10/20/03 7:34 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: we drop and recreate the temp tables every night. We also use PCTFREE PCTUSED at 99 and 1 to pack in the blocks and we use very small extent sizes. then we analyze with an estimate size of 20 percent which is quite fast. All of them are used for full table scans and do not have indexes. Ive found that a 'create table as' is MUCH faster than inserting into global temporary tables when you do not have to worry about latch contention(ie 1-3 users logged in at a time). anyone else notice this? Seems to go against conventional wisdom which says never use them. So I want to make sure Im not missing something. From: Tim Gorman [EMAIL PROTECTED] Date: 2003/10/20 Mon AM 10:19:33 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: using temp tables for staging databases? All the time. Oracle Apps's open interfaces are built this way, for example. However, the guys here covered their bases by specifying smaller temporary tables, as if they could prevent them from becoming large. I suppose they might feel that they indemnify themselves if the tables should ever become large? As with OraApps open interface tables, it is when a large volume of data is pushed through that the trouble starts. The high-water marks on all the tables are pushed to a high level, thereafter causing full table scans on the interface/temporary tables to run slowly. The only way to bring the HWM back down is quiesce the interface/app and then truncate the tables. on 10/20/03 6:39 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: This is for non-transactional data load instances. The guys here sware that by using smaller temporary tables(not global temp tables) they can increase the speed of the data loads. Not worried about latch contention because its just for bulk loads. I know this bad in transactional instances. Has anyone used these in non-transactional data load instances? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman 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
Re: using temp tables for staging databases?
Binley,The cause of Buffer Busy Waits (BBW) is not exclusively the setting of PCTUSED and PCTFREE; they just two of the causes. To understand the connection, let me explain a little bit on the cause of BBWs.When a session requests some data element from a table, the server process of the session gets the block from the disk to the cache (assume the block is not present in the cache). The event of the block coming from the disk to occupy a buffer in the caceh is pretty straight forward. Now, imagaine, at the exact same time another session selects a row from the same block. A *different* row but from the *same* block. That session will search the cache buffer chain and see that the buffer is not present and will attempt the same maneuevre, i.e. get the buffer from the disk. However, the first session is currently moving the buffer; the second session has to *wait* till the process is complete. This wait is known as buffer busy wait (BBW); but I guess you already knew that. The two sessions are not in conflict over the same row, but the same buffer; so it's not locking contention.How can we eliminate BBWs? Unfortunately we can't bring it to zero. There is always a probability that two sessions will try to get the same block. The only exception is when a block contains only one row. In that case the sessions will select different blocks for different rows. Again, this is not practical. We can reduce BBW by reducing the *possibility* that two sessions will not try to access the same block. This can be done using several ways:(1) reducing the block size(2) making a block less compact, so that each block holds less number of rows. The fewer the number of rows in a block, the lesser the probability that two sessions will access rows in the same block.The first option is not a very practical one in most cases. The second option is. It can be effected by allocating less space in a block, which can be done by using a large value of PCTFREE, e.g. 40 and/or small value of PCTUSED, such as 40, instead of 99. Other ways to achieve the same result is using a higher value of INITRANS, or anything that will cause less number of rows to fill up a block. Less rows = less chance of BBW occuring.I wrote a paper in Select Journal a few months ago explaining this very situation. Although the article is on Segment Level Statistics, it has an example which you can simulate to see the effect of PCTFREE/PCTUSED/INITRANS on Buffer Busy Waits. It can be downlaoded from my website at www.proligence.com/downloads.html and choose New Tool on the Block - Segment Level Statistics. Please feel free to give it a whirl.Further qualifying the case for higher PCTUSED and lower PCTFREE in datawarehouse environments, the chance that two sessions will access the row in same block is much less in DW than in OLTP. Hence the values can be different in DW.HTH.Arup Nanda- Original Message - From: "Binley Lim" [EMAIL PROTECTED]To: "Multiple recipients of list ORACLE-L" [EMAIL PROTECTED]Sent: Tuesday, October 21, 2003 10:24 AMSubject: Re: using temp tables for staging databases? I'm unclear how BBW is related to PCTUSED. PCTUSED is used to control when blocks are returned to the freelist due to deletions. Blocks already-off the freelist, and above PCTUSED, remain unavailable for inserts. PCTUSED does not prevent a "block contains too many rows" -since a low PCTFREE will pack the rows tightly anyway. If BBW wait is a problem, then there are other causes. PCTUSED is not one of them, or at least should not be an attempted solution. I will also add to Tim's response of justifying a smaller PCTUSED. In addition to the freelist problem he mentioned, there is also a greater chance of buffer busy waits occuring when a block contains too many rows. In an OLTP database that is certainly likely to happen - another case for the default 40 setting for the parameter. In DW, however, the chances of BBW are low, hence a higher setting may be possible. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Binley Lim 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: using temp tables for staging databases?
This is definitely one for the Hall of [F|Sh]ame! 4608 byte block size! But how did someone arrive at that number - Typo? Wheel of Fortune? DBMS_RANDOM? Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, October 21, 2003 11:19 AM Hi, Mark. I'm not Tim, but I did encounter such a situation. This was not a temp table, but a permanent one. We have a db with a very strange block size of 4608 (actually Tim is painfully aware of this one). We have a very large table in this database. It was expanding at about 200 megs per week -- way out of control for a relataively small database. The database was not reusing blocks. Oracle recommends that (100% - (pctfree+pcused)) be greater than the maximum sie of a row. So we did an exact calculation of the blocksize less %free+%used 1% of a block is 46.08 80% of a block is 3686.4 4608 - (46.08 + 3686.4) = 875.52 our largest row length is 860 So we set pctfree at 1% and pctused at 80% One of the reasons we can get by with this is because the vendor designed the database with all char (not varchar2), so we pretty much know exactly what each row is going to consume. (It's a Cobol app) After this change, the database stopped it's wild expansion. Not a normal situation, but then nothing here is normal. (Kids -- don't try this at home!) Barb --- Mark Leith [EMAIL PROTECTED] wrote: Tim, Can you sum up a few situations when the need *has* arisen to change these values? Cheers Mark -Original Message- Tim Gorman Sent: 21 October 2003 06:09 To: Multiple recipients of list ORACLE-L Unless you typo'd, there are some serious problems here... Setting PCTFREE to 99 is not likely to pack in the blocks. Rather the opposite; you are instead leaving blocks 99% empty. Quite a bit of wasted I/O in performing a FULL table scan here... :-) Anyway, it is not a good idea to have PCTFREE and PCTUSED sum to a value greater than 70 or 80 or so, just as a rule of thumb. Having them sum to a value near 100 ensures that each insert, delete, or even update will potentially cause the block to be removed or reinserted to one of the segment's free list. Think about it: the width of a single row crossing the boundary from off the free list to on the free list. Better to leave a bit of a no man's land between the two values. The default settings of PCTFREE=10 and PCTUSED=40 are one of the few default settings that need little manipulation for most situations. on 10/20/03 7:34 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: we drop and recreate the temp tables every night. We also use PCTFREE PCTUSED at 99 and 1 to pack in the blocks and we use very small extent sizes. then we analyze with an estimate size of 20 percent which is quite fast. All of them are used for full table scans and do not have indexes. Ive found that a 'create table as' is MUCH faster than inserting into global temporary tables when you do not have to worry about latch contention(ie 1-3 users logged in at a time). anyone else notice this? Seems to go against conventional wisdom which says never use them. So I want to make sure Im not missing something. From: Tim Gorman [EMAIL PROTECTED] Date: 2003/10/20 Mon AM 10:19:33 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: using temp tables for staging databases? All the time. Oracle Apps's open interfaces are built this way, for example. However, the guys here covered their bases by specifying smaller temporary tables, as if they could prevent them from becoming large. I suppose they might feel that they indemnify themselves if the tables should ever become large? As with OraApps open interface tables, it is when a large volume of data is pushed through that the trouble starts. The high-water marks on all the tables are pushed to a high level, thereafter causing full table scans on the interface/temporary tables to run slowly. The only way to bring the HWM back down is quiesce the interface/app and then truncate the tables. on 10/20/03 6:39 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: This is for non-transactional data load instances. The guys here sware that by using smaller temporary tables(not global temp tables) they can increase the speed of the data loads. Not worried about latch contention because its just for bulk loads. I know this bad in transactional instances. Has anyone used these in non-transactional data load instances? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com
Re: using temp tables for staging databases?
Too many drugs? Not enough?? Here's an exact quote from the vendor -- they placed this line in our init file. Sadly, they did not plan for any overhead . . . (the app was installed before we had an oracle db on board) ### # The db_block_size is set at 9 multiples of 512 bytes(OpenVMS block size) # This is to accomodate the WO table. The average row length of the WO table is # 900 bytes. A 4608 parameter allows 5 rows to be stored in a single Oracle bloc k # Do not change without consulting NWI! --- Arup Nanda [EMAIL PROTECTED] wrote: This is definitely one for the Hall of [F|Sh]ame! 4608 byte block size! But how did someone arrive at that number - Typo? Wheel of Fortune? DBMS_RANDOM? Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, October 21, 2003 11:19 AM Hi, Mark. I'm not Tim, but I did encounter such a situation. This was not a temp table, but a permanent one. We have a db with a very strange block size of 4608 (actually Tim is painfully aware of this one). We have a very large table in this database. It was expanding at about 200 megs per week -- way out of control for a relataively small database. The database was not reusing blocks. Oracle recommends that (100% - (pctfree+pcused)) be greater than the maximum sie of a row. So we did an exact calculation of the blocksize less %free+%used 1% of a block is 46.08 80% of a block is 3686.4 4608 - (46.08 + 3686.4) = 875.52 our largest row length is 860 So we set pctfree at 1% and pctused at 80% One of the reasons we can get by with this is because the vendor designed the database with all char (not varchar2), so we pretty much know exactly what each row is going to consume. (It's a Cobol app) After this change, the database stopped it's wild expansion. Not a normal situation, but then nothing here is normal. (Kids -- don't try this at home!) Barb --- Mark Leith [EMAIL PROTECTED] wrote: Tim, Can you sum up a few situations when the need *has* arisen to change these values? Cheers Mark -Original Message- Tim Gorman Sent: 21 October 2003 06:09 To: Multiple recipients of list ORACLE-L Unless you typo'd, there are some serious problems here... Setting PCTFREE to 99 is not likely to pack in the blocks. Rather the opposite; you are instead leaving blocks 99% empty. Quite a bit of wasted I/O in performing a FULL table scan here... :-) Anyway, it is not a good idea to have PCTFREE and PCTUSED sum to a value greater than 70 or 80 or so, just as a rule of thumb. Having them sum to a value near 100 ensures that each insert, delete, or even update will potentially cause the block to be removed or reinserted to one of the segment's free list. Think about it: the width of a single row crossing the boundary from off the free list to on the free list. Better to leave a bit of a no man's land between the two values. The default settings of PCTFREE=10 and PCTUSED=40 are one of the few default settings that need little manipulation for most situations. on 10/20/03 7:34 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: we drop and recreate the temp tables every night. We also use PCTFREE PCTUSED at 99 and 1 to pack in the blocks and we use very small extent sizes. then we analyze with an estimate size of 20 percent which is quite fast. All of them are used for full table scans and do not have indexes. Ive found that a 'create table as' is MUCH faster than inserting into global temporary tables when you do not have to worry about latch contention(ie 1-3 users logged in at a time). anyone else notice this? Seems to go against conventional wisdom which says never use them. So I want to make sure Im not missing something. From: Tim Gorman [EMAIL PROTECTED] Date: 2003/10/20 Mon AM 10:19:33 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: using temp tables for staging databases? All the time. Oracle Apps's open interfaces are built this way, for example. However, the guys here covered their bases by specifying smaller temporary tables, as if they could prevent them from becoming large. I suppose they might feel that they indemnify themselves if the tables should ever become large? As with OraApps open interface tables, it is when a large volume of data is pushed through that the trouble starts. The high-water marks on all the tables are pushed to a high level, thereafter causing full table scans on the interface/temporary tables
RE: using temp tables for staging databases?
I believe Oracle will round that block size off. What I would not be sure of is what Oracle did during database creation. I believe it should have gone with an 8K (8192 bytes) block size since the specified size of 4608 Bytes is above a 4K (4096 Bytes) block size. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Tuesday, October 21, 2003 11:55 AM To: Multiple recipients of list ORACLE-L Too many drugs? Not enough?? Here's an exact quote from the vendor -- they placed this line in our init file. Sadly, they did not plan for any overhead . . . (the app was installed before we had an oracle db on board) ### # The db_block_size is set at 9 multiples of 512 bytes(OpenVMS block size) # This is to accomodate the WO table. The average row length of the WO table is # 900 bytes. A 4608 parameter allows 5 rows to be stored in a single Oracle bloc k # Do not change without consulting NWI! --- Arup Nanda [EMAIL PROTECTED] wrote: This is definitely one for the Hall of [F|Sh]ame! 4608 byte block size! But how did someone arrive at that number - Typo? Wheel of Fortune? DBMS_RANDOM? Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, October 21, 2003 11:19 AM Hi, Mark. I'm not Tim, but I did encounter such a situation. This was not a temp table, but a permanent one. We have a db with a very strange block size of 4608 (actually Tim is painfully aware of this one). We have a very large table in this database. It was expanding at about 200 megs per week -- way out of control for a relataively small database. The database was not reusing blocks. Oracle recommends that (100% - (pctfree+pcused)) be greater than the maximum sie of a row. So we did an exact calculation of the blocksize less %free+%used 1% of a block is 46.08 80% of a block is 3686.4 4608 - (46.08 + 3686.4) = 875.52 our largest row length is 860 So we set pctfree at 1% and pctused at 80% One of the reasons we can get by with this is because the vendor designed the database with all char (not varchar2), so we pretty much know exactly what each row is going to consume. (It's a Cobol app) After this change, the database stopped it's wild expansion. Not a normal situation, but then nothing here is normal. (Kids -- don't try this at home!) Barb --- Mark Leith [EMAIL PROTECTED] wrote: Tim, Can you sum up a few situations when the need *has* arisen to change these values? Cheers Mark -Original Message- Tim Gorman Sent: 21 October 2003 06:09 To: Multiple recipients of list ORACLE-L Unless you typo'd, there are some serious problems here... Setting PCTFREE to 99 is not likely to pack in the blocks. Rather the opposite; you are instead leaving blocks 99% empty. Quite a bit of wasted I/O in performing a FULL table scan here... :-) Anyway, it is not a good idea to have PCTFREE and PCTUSED sum to a value greater than 70 or 80 or so, just as a rule of thumb. Having them sum to a value near 100 ensures that each insert, delete, or even update will potentially cause the block to be removed or reinserted to one of the segment's free list. Think about it: the width of a single row crossing the boundary from off the free list to on the free list. Better to leave a bit of a no man's land between the two values. The default settings of PCTFREE=10 and PCTUSED=40 are one of the few default settings that need little manipulation for most situations. on 10/20/03 7:34 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: we drop and recreate the temp tables every night. We also use PCTFREE PCTUSED at 99 and 1 to pack in the blocks and we use very small extent sizes. then we analyze with an estimate size of 20 percent which is quite fast. All of them are used for full table scans and do not have indexes. Ive found that a 'create table as' is MUCH faster than inserting into global temporary tables when you do not have to worry about latch contention(ie 1-3 users logged in at a time). anyone else notice this? Seems to go against conventional wisdom which says never use them. So I want to make sure Im not missing something. From: Tim Gorman [EMAIL PROTECTED] Date: 2003/10/20 Mon AM 10:19:33 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: using temp tables for staging databases? All the time. Oracle Apps's open interfaces are built this way, for example. However, the guys here covered their bases by specifying smaller temporary tables, as if they could
Re: using temp tables for staging databases?
All that is nice, but from my practice so far, by far the most frequent cause of the buffer busy waits id DBWR being unable to catch up. This can come as a consequence of several things: - Poorly written transaction that modifies thousands of blocks during peak time hours. Typical example is bill generation, which generates the table from which the bills are printed, and it'usually done during peak hours. It generally slows down everybody else, causes a lot of screaming and cannot be resolved by increasing the cache hit ratio. Moving bill generation to operational data store, combined with replication and spreading the load over a period of time can solve these. Alternative solution is not available ever since Richard Kuklinsky, the Ice Man, is off the market. - Slow peripherals and insufficient I/O bandwidth, usually caused by magazine reading PHB. DBA needs to develop a healthy cynical attitude and desperately try spreading the workload throughout the 24 hours and all 7 days in a week. Disproportionately high number of these sites are running windoze and are easily recognized when the IT manager tells you about the wonderful Matrox Millennium card that he has in the database server and quotes the number of OpenGL operations his new database server can do. - Very high transaction rates and inability of the CPUs to handle the load. In this case there are so many transactions that DBWR is unable to catch up. That happens when the system is in desperate need of a good upgrade. This usually happens in places where the system is stabilized and the business users say that they have what they need and that no major work should be done on the boxes. Candidates are sites which are running things like 7.3.4 and 8.0.6 today. Of course, when an upgrade actually is needed, panic spreads and hit and run consultants are brought in to make things worse. On 10/21/2003 11:19:32 AM, Arup Nanda wrote: Binley, The cause of Buffer Busy Waits (BBW) is not exclusively the setting of PCTUSED and PCTFREE; they just two of the causes. To understand the connection, let me explain a little bit on the cause of BBWs. When a session requests some data element from a table, the server process of the session gets the block from the disk to the cache (assume the block is not present in the cache). The event of the block coming from the disk to occupy a buffer in the caceh is pretty straight forward. Now, imagaine, at the exact same time another session selects a row from the same block. A *different* row but from the *same* block. That session will search the cache buffer chain and see that the buffer is not present and will attempt the same maneuevre, i.e. get the buffer from the disk. However, the first session is currently moving the buffer; the second session has to *wait* till the process is complete. This wait is known as buffer busy wait (BBW); but I guess you already knew that. The two sessions are not in conflict over the same row, but the same buffer; so it's not locking contention. How can we eliminate BBWs? Unfortunately we can't bring it to zero. There is always a probability that two sessions will try to get the same block. The only exception is when a block contains only one row. In that case the sessions will select different blocks for different rows. Again, this is not practical. We can reduce BBW by reducing the *possibility* that two sessions will not try to access the same block. This can be done using several ways: (1) reducing the block size (2) making a block less compact, so that each block holds less number of rows. The fewer the number of rows in a block, the lesser the probability that two sessions will access rows in the same block. The first option is not a very practical one in most cases. The second option is. It can be effected by allocating less space in a block, which can be done by using a large value of PCTFREE, e.g. 40 and/or small value of PCTUSED, such as 40, instead of 99. Other ways to achieve the same result is using a higher value of INITRANS, or anything that will cause less number of rows to fill up a block. Less rows = less chance of BBW occuring. I wrote a paper in Select Journal a few months ago explaining this very situation. Although the article is on Segment Level Statistics, it has an example which you can simulate to see the effect of PCTFREE/PCTUSED/INITRANS on Buffer Busy Waits. It can be downlaoded from my website at www.proligence.com/downloads.html and choose New Tool on the Block - Segment Level Statistics. Please feel free to give it a whirl. Further qualifying the case for higher PCTUSED and lower PCTFREE in datawarehouse environments, the chance that two sessions will access the row in same block is much less in DW than in OLTP. Hence the values can be different in DW. HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, October 21, 2003 10:24 AM I'm unclear how
Re: using temp tables for staging databases?
Yes, I understand BBW, and how PCTFREE affects block density, and hence BBW. My point, which you appear to have missed, is PCTUSED does not affect block densitywhich is determined by PCTFREE. Lets take a simple case of 10% PCTFREE, and 40% PCTUSED. Blocks will fill up to 90% _no matter_ what you make the PCTUSED figure to be. Only if you havelots of updates/deletes that PCTUSED comes into play but then you have a 90% packed block to begin with, so its effect is marginal.PCTFREE alone will reduce block density if that is the aim. In your paper, you changed several parameters at the same time - including freelists which does not affect block density. From a statistical/testing point of view you cannot really draw any conclusions. Lower block density mayreduce BBWs, but you still have to process the same number of rows. And you have to process a higher number of blocks which incurs an additional cost. Question becomes - has the problem been simply shifted somewhere else? - Original Message - From: Arup Nanda To: Multiple recipients of list ORACLE-L Sent: Wednesday, October 22, 2003 4:19 AM Subject: Re: using temp tables for staging databases? Binley,The cause of Buffer Busy Waits (BBW) is not exclusively the setting of PCTUSED and PCTFREE; they just two of the causes. To understand the connection, let me explain a little bit on the cause of BBWs.When a session requests some data element from a table, the server process of the session gets the block from the disk to the cache (assume the block is not present in the cache). The event of the block coming from the disk to occupy a buffer in the caceh is pretty straight forward. Now, imagaine, at the exact same time another session selects a row from the same block. A *different* row but from the *same* block. That session will search the cache buffer chain and see that the buffer is not present and will attempt the same maneuevre, i.e. get the buffer from the disk. However, the first session is currently moving the buffer; the second session has to *wait* till the process is complete. This wait is known as buffer busy wait (BBW); but I guess you already knew that. The two sessions are not in conflict over the same row, but the same buffer; so it's not locking contention.How can we eliminate BBWs? Unfortunately we can't bring it to zero. There is always a probability that two sessions will try to get the same block. The only exception is when a block contains only one row. In that case the sessions will select different blocks for different rows. Again, this is not practical. We can reduce BBW by reducing the *possibility* that two sessions will not try to access the same block. This can be done using several ways:(1) reducing the block size(2) making a block less compact, so that each block holds less number of rows. The fewer the number of rows in a block, the lesser the probability that two sessions will access rows in the same block.The first option is not a very practical one in most cases. The second option is. It can be effected by allocating less space in a block, which can be done by using a large value of PCTFREE, e.g. 40 and/or small value of PCTUSED, such as 40, instead of 99. Other ways to achieve the same result is using a higher value of INITRANS, or anything that will cause less number of rows to fill up a block. Less rows = less chance of BBW occuring.I wrote a paper in Select Journal a few months ago explaining this very situation. Although the article is on Segment Level Statistics, it has an example which you can simulate to see the effect of PCTFREE/PCTUSED/INITRANS on Buffer Busy Waits. It can be downlaoded from my website at www.proligence.com/downloads.html and choose New Tool on the Block - Segment Level Statistics. Please feel free to give it a whirl.Further qualifying the case for higher PCTUSED and lower PCTFREE in datawarehouse environments, the chance that two sessions will access the row in same block is much less in DW than in OLTP. Hence the values can be different in DW.HTH.Arup Nanda- Original Message - From: "Binley Lim" [EMAIL PROTECTED]To: "Multiple recipients of list ORACLE-L" [EMAIL PROTECTED]Sent: Tuesday, October 21, 2003 10:24 AMSubject: Re: using temp tables for staging databases? I'm unclear how BBW is related to PCTUSED. PCTUSED is used to control when blocks are returned to the freelist due to deletions. Blocks already-off the freelist, and above PCTUSED, remain unavailable for inserts. PCTUSED does not prevent a "block contains too many rows" -since a low PCTFREE will pack the rows tightly anyway. If BBW wait is a problem, then there are other causes. PCTUSED is not one of them, or at least should not be an attempted solution. I will also add to Tim's response of
using temp tables for staging databases?
This is for non-transactional data load instances. The guys here sware that by using smaller temporary tables(not global temp tables) they can increase the speed of the data loads. Not worried about latch contention because its just for bulk loads. I know this bad in transactional instances. Has anyone used these in non-transactional data load instances? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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: using temp tables for staging databases?
All the time. Oracle Apps's open interfaces are built this way, for example. However, the guys here covered their bases by specifying smaller temporary tables, as if they could prevent them from becoming large. I suppose they might feel that they indemnify themselves if the tables should ever become large? As with OraApps open interface tables, it is when a large volume of data is pushed through that the trouble starts. The high-water marks on all the tables are pushed to a high level, thereafter causing full table scans on the interface/temporary tables to run slowly. The only way to bring the HWM back down is quiesce the interface/app and then truncate the tables. on 10/20/03 6:39 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: This is for non-transactional data load instances. The guys here sware that by using smaller temporary tables(not global temp tables) they can increase the speed of the data loads. Not worried about latch contention because its just for bulk loads. I know this bad in transactional instances. Has anyone used these in non-transactional data load instances? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman 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: Re: using temp tables for staging databases?
we drop and recreate the temp tables every night. We also use PCTFREE PCTUSED at 99 and 1 to pack in the blocks and we use very small extent sizes. then we analyze with an estimate size of 20 percent which is quite fast. All of them are used for full table scans and do not have indexes. Ive found that a 'create table as' is MUCH faster than inserting into global temporary tables when you do not have to worry about latch contention(ie 1-3 users logged in at a time). anyone else notice this? Seems to go against conventional wisdom which says never use them. So I want to make sure Im not missing something. From: Tim Gorman [EMAIL PROTECTED] Date: 2003/10/20 Mon AM 10:19:33 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: using temp tables for staging databases? All the time. Oracle Apps's open interfaces are built this way, for example. However, the guys here covered their bases by specifying smaller temporary tables, as if they could prevent them from becoming large. I suppose they might feel that they indemnify themselves if the tables should ever become large? As with OraApps open interface tables, it is when a large volume of data is pushed through that the trouble starts. The high-water marks on all the tables are pushed to a high level, thereafter causing full table scans on the interface/temporary tables to run slowly. The only way to bring the HWM back down is quiesce the interface/app and then truncate the tables. on 10/20/03 6:39 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: This is for non-transactional data load instances. The guys here sware that by using smaller temporary tables(not global temp tables) they can increase the speed of the data loads. Not worried about latch contention because its just for bulk loads. I know this bad in transactional instances. Has anyone used these in non-transactional data load instances? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman 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: [EMAIL PROTECTED] 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: using temp tables for staging databases?
Unless you typo'd, there are some serious problems here... Setting PCTFREE to 99 is not likely to pack in the blocks. Rather the opposite; you are instead leaving blocks 99% empty. Quite a bit of wasted I/O in performing a FULL table scan here... :-) Anyway, it is not a good idea to have PCTFREE and PCTUSED sum to a value greater than 70 or 80 or so, just as a rule of thumb. Having them sum to a value near 100 ensures that each insert, delete, or even update will potentially cause the block to be removed or reinserted to one of the segment's free list. Think about it: the width of a single row crossing the boundary from off the free list to on the free list. Better to leave a bit of a no man's land between the two values. The default settings of PCTFREE=10 and PCTUSED=40 are one of the few default settings that need little manipulation for most situations. on 10/20/03 7:34 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: we drop and recreate the temp tables every night. We also use PCTFREE PCTUSED at 99 and 1 to pack in the blocks and we use very small extent sizes. then we analyze with an estimate size of 20 percent which is quite fast. All of them are used for full table scans and do not have indexes. Ive found that a 'create table as' is MUCH faster than inserting into global temporary tables when you do not have to worry about latch contention(ie 1-3 users logged in at a time). anyone else notice this? Seems to go against conventional wisdom which says never use them. So I want to make sure Im not missing something. From: Tim Gorman [EMAIL PROTECTED] Date: 2003/10/20 Mon AM 10:19:33 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: using temp tables for staging databases? All the time. Oracle Apps's open interfaces are built this way, for example. However, the guys here covered their bases by specifying smaller temporary tables, as if they could prevent them from becoming large. I suppose they might feel that they indemnify themselves if the tables should ever become large? As with OraApps open interface tables, it is when a large volume of data is pushed through that the trouble starts. The high-water marks on all the tables are pushed to a high level, thereafter causing full table scans on the interface/temporary tables to run slowly. The only way to bring the HWM back down is quiesce the interface/app and then truncate the tables. on 10/20/03 6:39 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: This is for non-transactional data load instances. The guys here sware that by using smaller temporary tables(not global temp tables) they can increase the speed of the data loads. Not worried about latch contention because its just for bulk loads. I know this bad in transactional instances. Has anyone used these in non-transactional data load instances? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman 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: Tim Gorman 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).
