Re: Automatic Segment Space Management
It's interesting you should mention the
'select for update' in this context.
I'm still working on a puzzle where
I do:
create table t1 (n1 number);
insert into t1 values (0);
insert into t1 values(1);
commit;
select rowid from t1 where n1 = 0;
for i in 1..1000 loop
update t1 set n1 = n1 + 1
where n1 = i;
end loop;-- updates the '1' row 1,000 times.
/
Now, without committing - start another session
that does:
select n1 from t1
where rowid = '{value seen above for n1 = 0}
for update;
Repeat the experiment, but the second time do:
update t1
set n1 = 99
where rowid = '{value seen above for n1 = 0};
Why does one of these statements to 1000
CR gets, whilst the other does none ? How
different are they - they both put an ITL entry
on the block, and change the row content -
they both need to be able to lock the row.
I think this may have some bearing on your
'large number of CR reads' - I too have seen
sites where the numbers got very large (in
part because the CR limit doesn't seem to
be considered if there are free blocks (state = 0)
around to be used). But if the code does
'select for update, update' - then it takes a
long time to make a CR copy in a busy
enviornment, so if concurrency is high on
that block, then I guess the evolving (or
is that devolving) CR block is pinned for
a long time - allowing lots more CR blocks
to be created.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Coming soon one-day tutorials:
Cost Based Optimisation
Trouble-shooting and Tuning
Indexing Strategies
(see http://www.jlcomp.demon.co.uk/tutorial.html )
UK___March 19th
USA_(FL)_May 2nd
Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )
USA_(CA, TX)_August
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
-Original Message-
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: 20 February 2003 20:58
Stephan just passed this on to me...
Cary,
I really must subscribe to this mail list, but until I do, maybe you
can
pass this on.
You are correct, the _db_block_max_cr_dba parameter is just a guide.
I
believe that when needing to create a new CR copy and this limit has
been reached Oracle tries to place any older CR buffers (not sure if
it
does all of them or oldest found) to the cold end of the LRU ready to
leave the cache at the next possible opportunity. If the buffer has
any
users or waiters (can be seen in x$bh), then the CR buffer will
remain
in cache until next time. When a new CR buffer is created, and an
older
CR buffer no longer has users or waiters, it should be aged out of
the
cache as soon as possible.
I hope this helps,
Stephan
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- RMOUG Training Days 2003, Mar 5-6 Denver
- Hotsos Clinic 101, Mar 25-27 London
-Original Message-
Millsap
Sent: Thursday, February 20, 2003 1:41 PM
To: Multiple recipients of list ORACLE-L
Anjo personally saved my bacon when I was at a site in Dallas with
this problem. This particular problem was a vendor application ported
from Sybase and thus used select from blah_id for update; update
blah;
commit; instead of Oracle sequences. These guys had 1,200+ CR copies
of
each little 1-row-1-column id table in their system. In the end, the
vendor repaired its app to use sequence numbers (within the week,
actually!), and the problem which had caused daily shutdown/restarts
ended instantly. The 42 patch, as it was called at the time, would
have helped reduce the severity of the problem, but it wouldn't have
solved it.
I was pretty proud of myself when the engagement was done, but a
monkey
could have probably executed my part in the project if the monkey had
known how to call Anjo.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- RMOUG Training Days 2003, Mar 5-6 Denver
- Hotsos Clinic 101, Mar 25-27 London
-Original Message-
Sent: Thursday, February 20, 2003 1:04 PM
To: Multiple recipients of list ORACLE-L
The _db_block_max_cr_dba parameter was put in to fix this problem
with
massive
number of CR copies (segment header blocks mostly). I remember seeing
a
test
case that had 1500+ CR copies of the segment header block. So the fix
was to
limit the number of CR copies. The parameter _db_block_max_cr_dba had
initially a default value of 42 (really). And worked perfectly, but
it
was
brok in Parallel Server, the reason for it not working was very
funny.
While scanning the hash chain for the right (tsn, rdba) the CR code
may
already stop if it finds the best fit and never scan all the buffers
and
there it can't enforce the limit of 6. Another reason could be that
the
buffers are pinned (in use), but they should be flushed out later if
the
same
buffer hash chain is scanned again for the (tsn, rdba).
In version8 I have seen a particular test case with over 60+ CR
copies
of a
index root block (running many processes doing
RE: Automatic Segment Space Management
later to build up on my upcoming article on ITL Waits. Regards, Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, February 19, 2003 2:45 PM Arup, Just picking up the thread on the BBWs. (Btw, I asked this question in this list - never got an answer!) The following undocumented parameter limits the numbe of CR copies in the Block buffers. Name Value - -- Description -- -- --- _db_block_max_cr_dba 6 Maximum Allowed Number of CR buffers per dba What if there are more than 6 concurrent update requests for the same block. Would that not result in BBW? John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Disappointment is inevitable, but Discouragement is optional! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Arup Nanda [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 19, 2003 8:24 AM To: Multiple recipients of list ORACLE-L Subject: Re: Automatic Segment Space Management Jay, I have been using ASSM for last five months in our Datawarehouse environment. Haven't had a chance to play with the OLTP side, yet. Inserts are way faster as compared to system managed extent allocation. I read Don's article on DBAZINE. However, I would like to add one caveat here: ASSM does not *eliminate* buffer busy waits as the article claims; it *reduces* them. BBW occur due to concurrent access to a buffer by more than one session. This will be the case regardless of number of freelists. While ASSM eliminates the freelist contention - thereby reducing BBW in inserts - it does not reduce the likelihood that more than one sessions will try to get the same block to the buffer cache simulataneously. Table drops appear a little slower in ASSM; but that could be wrong - I never timed dropping a table in the system managed mode. HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, February 19, 2003 9:33 AM I'm continuing to introduce myself to 9i. I've been reading about Automatic Segment Space Management, and I just wondered if anybody had any positive/negative experiences with it. I got some good info at: http://www.dbazine.com/burleson11.html Thank you, Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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: Arup Nanda 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: 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
RE: Automatic Segment Space Management
Kirti: I have not complely following this thread. But I am sure it is worth mentioning even if someone already mentioned also. In ASSM there would be two highwatermarks called low high water mark and high high water mark. The high high water mark is the actual high water mark (like in the Freelist Managed Segments) and the low high water mark is the new one which is introduced in ASSM. i.e till low HWM all blocks are completely used and from LHWM to HWHM there could be some blocks unused. During sequential scan it has to read till HHWM (i.e it should scan empty block also). But I think by scanning the L2 bitmaps the process can find the unused blocks and skip that during sequential scanning, though I have not tested it thoroughly. Best Regards, K Gopalakrishnan Bangalore, INDIA (Now in Austria) --- Deshpande, Kirti [EMAIL PROTECTED] wrote: In a very limited tests that I performed with ASSM (quite some time ago), I found that it tends to use a bit more space than non-ASSM. Something to keep in mind when FTS is used to access tables. Not sure if this changed in 9.2.0.2.x. - Kirti = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan 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: Automatic Segment Space Management
Cary: I guess the parameter just puts a softlimit of number of CR Blocks per DBA. But I have seen more number of CR copies (10+ CR copies per DBA) many times. But then I was using Relative File# in the X$BH while querying (though the chances of same block# in the different files becoming hot is very rare), which I realized later. Any thoughts Cary? KG --- Cary Millsap [EMAIL PROTECTED] wrote: I've received additional insight from Stephan Haisley of Oracle. Bottom-line, it sounds like the real trade-off is that if a larger number of CR versions could be left on a chain, then the number of undo operations required to fulfill a given query might be reduced, but at the expense of longer chain searches. From Stephan: Cary, I think you are forgetting the fact that updates can only occur to a CURRENT buffer. There can only be ONE current buffer of any block in the buffer cache. All row updates will occur to the same current buffer. Therefore, it is not related to the max. number of CR buffers permitted per datablock. A CR block can not be used for row updates. Sure the block is updated during application of undo to make it consistent of a particular SCN, but this is not the same as a DML row update that must be applied to the CURRENT version of the buffer. Clone buffers (as Cary mentioned them) are mainly created in one of two cases. When a block is required for CR purposes (closest buffer to required SCN is found, cloned and then undo is applied). The second common occasion is when you want to update (DML) a buffer, and there is a current buffer already in cache. If all users or waiters are for CR purposes only (NO DMLs) according to the users state objects, the buffer will be cloned, switching the clone to the CURRENT buffer, and leaving the existing buffer as a CR buffer. Someone has stated already on this thread that, if there are multiple updates in the same block to different rows, the number ITL entries will be the limiting concurrency factor. And could also cause some BBW during the actual block update causing an incompatible mode BBW even between the processes actually applying their changes. Additions and corrections are welcome. I hope this helps, Stephan Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - RMOUG Training Days 2003, Mar 5-6 Denver - Hotsos Clinic 101, Mar 25-27 London -Original Message- Millsap Sent: Thursday, February 20, 2003 12:24 AM To: Multiple recipients of list ORACLE-L Jonathan or Steve or Stephan will likely provide a better answer to this than I will, but I'll add this food for thought: I think that if (1) six CR versions of one data block address already reside on a given cache buffers chain, and (2) at least one of them is not pinned, and (3) a request for a 7th distinct CR version of the block were to come along (i.e., same block but different SCN), then I think that _db_block_max_cr_dba=6 simply means that one of the existing (unpinned) CR versions will get expelled before the new CR construction takes place. The resulting desired behavior is thus that the construction of the new clone will not increase the original length of the cache buffers chain. I believe the trade-off is this: If, after this occurred, some query called were to Q request the CR version that was expelled (that is, if the expelled CR version's SCN suited the query's SCN), then the Oracle kernel would have to execute all the instructions required to reconstruct that CR version again (reading undo blocks using the ITL as a guide), instead of simply finding the right version's buffer header already on the chain (had the setting been 7, then the requested CR version wouldn't have been expelled). This whole operation would of course cause the expulsion of some other unpinned CR version of the same block (keeping the number of CR versions of the block to 6), so that the chain length would not increase. Had the parameter setting been 7, then Q's new CR request could have been fulfilled more cheaply, but at the expense of incurring longer average cache buffers chain lengths, which would cause incrementally longer cache buffers chain scan times, which would cause incrementally worse contention for the cache buffers chains latch on the relevant cache buffers chain. I think producing 7+ concurrent updates of a block will test, as Arup notes, only the kernel's ITL management prowess, not the case you're interested in. I'll step back timidly now, in anticipation of what dog trainers call a firm correction. :) Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - RMOUG Training Days 2003, Mar 5-6 Denver - Hotsos Clinic 101, Mar 25-27 London -Original Message- Sent: Wednesday, February 19, 2003 4:03 PM To: Multiple recipients of list ORACLE-L Gee, John, I was not aware of this
RE: Automatic Segment Space Management
Kirti: There are some (or more) errors in my previous post. The process can not find the partly filled/free blocks until it scans the L1 bitmaps (I said L2 bitmap in the previous post as I was in half-sleep while composting the message) and the L2 bitmaps just point the scanning process to the respective L1 bitmaps. And the other overhead in ASSM is, there are chances you will waste around 1-5% (depending on the block size,etc) blocks for just keeping the meta data and this could be a overhead along with the space waster in between the Low HWN and high HWM. Best Regards K Gopalakrishnan --- K Gopalakrishnan [EMAIL PROTECTED] wrote: Kirti: I have not complely following this thread. But I am sure it is worth mentioning even if someone already mentioned also. In ASSM there would be two highwatermarks called low high water mark and high high water mark. The high high water mark is the actual high water mark (like in the Freelist Managed Segments) and the low high water mark is the new one which is introduced in ASSM. i.e till low HWM all blocks are completely used and from LHWM to HWHM there could be some blocks unused. During sequential scan it has to read till HHWM (i.e it should scan empty block also). But I think by scanning the L2 bitmaps the process can find the unused blocks and skip that during sequential scanning, though I have not tested it thoroughly. Best Regards, K Gopalakrishnan Bangalore, INDIA (Now in Austria) --- Deshpande, Kirti [EMAIL PROTECTED] wrote: In a very limited tests that I performed with ASSM (quite some time ago), I found that it tends to use a bit more space than non-ASSM. Something to keep in mind when FTS is used to access tables. Not sure if this changed in 9.2.0.2.x. - Kirti = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan 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). = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan 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: Automatic Segment Space Management
In an attempt to get a handle on the overhead of the LHWM / HHWM thing, it's worth noting that you seem to get bitmap blocks per extent, and that the bitmap block caters for formatting 16 blocks at a time within that extent. The Low HWM is the point up to which all the blocks are formatted - and above which there may be unformatted blocks; the High HWM is the highest point in the last extent below which blocks are formatted. This means that once a 'normal' tablescan reaches the Low HWM, Oracle has to start checking the (level 1) bitmap blocks to guide it to the 16-block chunks (which can be read with multiblock reads) which are formatted above the LHWM. In theory, there won't be very many such chunks, so the overhead caused by the non-contiguity shouldn't be terribly significant. Of course, you are scanning more blocks than you need, and this looks particularly bad if you compare it to the vanilla scenario where you have a maximum of about 5 blocks formatted but unused on the master free list. But if you compare ASSM with the effects of having multiple freelists or freelist groups (which is the main problem that ASSM seeks to address), where each free list has had N blocks allocated, the difference is less dramatic - especially if you consider the case of old OPS - where you could allocate an extent to an instance - and find that an entire extent had to be formatted because there was only one high-water mark. A couple of open questions I have still: What's the largest number of extents with unformatted blocks in the middle that I can generate without cheating ? (My bust_bits script is a sort of cheat). When does the low high water mark get adjusted - I've got some results in multi- extent segments where the 'correct' place for the LHWM is right up at the HHWM, but the actual stored LHWM is still right at the start of the segment. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 20 February 2003 12:16 Kirti: There are some (or more) errors in my previous post. The process can not find the partly filled/free blocks until it scans the L1 bitmaps (I said L2 bitmap in the previous post as I was in half-sleep while composting the message) and the L2 bitmaps just point the scanning process to the respective L1 bitmaps. And the other overhead in ASSM is, there are chances you will waste around 1-5% (depending on the block size,etc) blocks for just keeping the meta data and this could be a overhead along with the space waster in between the Low HWN and high HWM. Best Regards K Gopalakrishnan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Automatic Segment Space Management
My guess would be it's the pinning issue. A pinned block will *never* be expelled, and the Oracle kernel is very unlikely to come back later to do an operation that's not possible to accomplish right now. However, having said that, it's likely something else too. I say this because I haven't even attempted to study the issue since about 1995, and virtually every time I've ever seen Steve Adams or Jonathan Lewis or Stephan Haisley's (etc.) mouth move, I become more aware that what I *thought* I knew in about 1995 is not really knowledge, it's more Knowledge Light. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - RMOUG Training Days 2003, Mar 5-6 Denver - Hotsos Clinic 101, Mar 25-27 London -Original Message- Gopalakrishnan Sent: Thursday, February 20, 2003 5:14 AM To: Multiple recipients of list ORACLE-L Cary: I guess the parameter just puts a softlimit of number of CR Blocks per DBA. But I have seen more number of CR copies (10+ CR copies per DBA) many times. But then I was using Relative File# in the X$BH while querying (though the chances of same block# in the different files becoming hot is very rare), which I realized later. Any thoughts Cary? KG --- Cary Millsap [EMAIL PROTECTED] wrote: I've received additional insight from Stephan Haisley of Oracle. Bottom-line, it sounds like the real trade-off is that if a larger number of CR versions could be left on a chain, then the number of undo operations required to fulfill a given query might be reduced, but at the expense of longer chain searches. From Stephan: Cary, I think you are forgetting the fact that updates can only occur to a CURRENT buffer. There can only be ONE current buffer of any block in the buffer cache. All row updates will occur to the same current buffer. Therefore, it is not related to the max. number of CR buffers permitted per datablock. A CR block can not be used for row updates. Sure the block is updated during application of undo to make it consistent of a particular SCN, but this is not the same as a DML row update that must be applied to the CURRENT version of the buffer. Clone buffers (as Cary mentioned them) are mainly created in one of two cases. When a block is required for CR purposes (closest buffer to required SCN is found, cloned and then undo is applied). The second common occasion is when you want to update (DML) a buffer, and there is a current buffer already in cache. If all users or waiters are for CR purposes only (NO DMLs) according to the users state objects, the buffer will be cloned, switching the clone to the CURRENT buffer, and leaving the existing buffer as a CR buffer. Someone has stated already on this thread that, if there are multiple updates in the same block to different rows, the number ITL entries will be the limiting concurrency factor. And could also cause some BBW during the actual block update causing an incompatible mode BBW even between the processes actually applying their changes. Additions and corrections are welcome. I hope this helps, Stephan Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - RMOUG Training Days 2003, Mar 5-6 Denver - Hotsos Clinic 101, Mar 25-27 London -Original Message- Millsap Sent: Thursday, February 20, 2003 12:24 AM To: Multiple recipients of list ORACLE-L Jonathan or Steve or Stephan will likely provide a better answer to this than I will, but I'll add this food for thought: I think that if (1) six CR versions of one data block address already reside on a given cache buffers chain, and (2) at least one of them is not pinned, and (3) a request for a 7th distinct CR version of the block were to come along (i.e., same block but different SCN), then I think that _db_block_max_cr_dba=6 simply means that one of the existing (unpinned) CR versions will get expelled before the new CR construction takes place. The resulting desired behavior is thus that the construction of the new clone will not increase the original length of the cache buffers chain. I believe the trade-off is this: If, after this occurred, some query called were to Q request the CR version that was expelled (that is, if the expelled CR version's SCN suited the query's SCN), then the Oracle kernel would have to execute all the instructions required to reconstruct that CR version again (reading undo blocks using the ITL as a guide), instead of simply finding the right version's buffer header already on the chain (had the setting been 7, then the requested CR version wouldn't have been expelled). This whole operation would of course cause the expulsion of some other unpinned CR version of the same block (keeping the number of CR versions of the block to 6), so that the chain length would not increase. Had the parameter setting been 7, then Q's new CR request could have been fulfilled more
RE: Automatic Segment Space Management
Gopal, Thanks for the info. As I said earlier, I have not played a lot with ASSM. - Kirti -Original Message- Sent: Thursday, February 20, 2003 5:10 AM To: Multiple recipients of list ORACLE-L Kirti: I have not complely following this thread. But I am sure it is worth mentioning even if someone already mentioned also. In ASSM there would be two highwatermarks called low high water mark and high high water mark. The high high water mark is the actual high water mark (like in the Freelist Managed Segments) and the low high water mark is the new one which is introduced in ASSM. i.e till low HWM all blocks are completely used and from LHWM to HWHM there could be some blocks unused. During sequential scan it has to read till HHWM (i.e it should scan empty block also). But I think by scanning the L2 bitmaps the process can find the unused blocks and skip that during sequential scanning, though I have not tested it thoroughly. Best Regards, K Gopalakrishnan Bangalore, INDIA (Now in Austria) --- Deshpande, Kirti [EMAIL PROTECTED] wrote: In a very limited tests that I performed with ASSM (quite some time ago), I found that it tends to use a bit more space than non-ASSM. Something to keep in mind when FTS is used to access tables. Not sure if this changed in 9.2.0.2.x. - Kirti = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti 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: Automatic Segment Space Management
The _db_block_max_cr_dba parameter was put in to fix this problem with massive number of CR copies (segment header blocks mostly). I remember seeing a test case that had 1500+ CR copies of the segment header block. So the fix was to limit the number of CR copies. The parameter _db_block_max_cr_dba had initially a default value of 42 (really). And worked perfectly, but it was brok in Parallel Server, the reason for it not working was very funny. While scanning the hash chain for the right (tsn, rdba) the CR code may already stop if it finds the best fit and never scan all the buffers and there it can't enforce the limit of 6. Another reason could be that the buffers are pinned (in use), but they should be flushed out later if the same buffer hash chain is scanned again for the (tsn, rdba). In version8 I have seen a particular test case with over 60+ CR copies of a index root block (running many processes doing NL and inserts into that index didn't help ofcourse). It is alway hard to tell why the limit is not enforced (may be we need a stat on this? ;-)). It could be a bug or buffer pinned (have seen both in production situations). Anjo. On Thursday 20 February 2003 07:18, Cary Millsap wrote: My guess would be it's the pinning issue. A pinned block will *never* be expelled, and the Oracle kernel is very unlikely to come back later to do an operation that's not possible to accomplish right now. However, having said that, it's likely something else too. I say this because I haven't even attempted to study the issue since about 1995, and virtually every time I've ever seen Steve Adams or Jonathan Lewis or Stephan Haisley's (etc.) mouth move, I become more aware that what I *thought* I knew in about 1995 is not really knowledge, it's more Knowledge Light. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - RMOUG Training Days 2003, Mar 5-6 Denver - Hotsos Clinic 101, Mar 25-27 London -Original Message- Gopalakrishnan Sent: Thursday, February 20, 2003 5:14 AM To: Multiple recipients of list ORACLE-L Cary: I guess the parameter just puts a softlimit of number of CR Blocks per DBA. But I have seen more number of CR copies (10+ CR copies per DBA) many times. But then I was using Relative File# in the X$BH while querying (though the chances of same block# in the different files becoming hot is very rare), which I realized later. Any thoughts Cary? KG --- Cary Millsap [EMAIL PROTECTED] wrote: I've received additional insight from Stephan Haisley of Oracle. Bottom-line, it sounds like the real trade-off is that if a larger number of CR versions could be left on a chain, then the number of undo operations required to fulfill a given query might be reduced, but at the expense of longer chain searches. From Stephan: Cary, I think you are forgetting the fact that updates can only occur to a CURRENT buffer. There can only be ONE current buffer of any block in the buffer cache. All row updates will occur to the same current buffer. Therefore, it is not related to the max. number of CR buffers permitted per datablock. A CR block can not be used for row updates. Sure the block is updated during application of undo to make it consistent of a particular SCN, but this is not the same as a DML row update that must be applied to the CURRENT version of the buffer. Clone buffers (as Cary mentioned them) are mainly created in one of two cases. When a block is required for CR purposes (closest buffer to required SCN is found, cloned and then undo is applied). The second common occasion is when you want to update (DML) a buffer, and there is a current buffer already in cache. If all users or waiters are for CR purposes only (NO DMLs) according to the users state objects, the buffer will be cloned, switching the clone to the CURRENT buffer, and leaving the existing buffer as a CR buffer. Someone has stated already on this thread that, if there are multiple updates in the same block to different rows, the number ITL entries will be the limiting concurrency factor. And could also cause some BBW during the actual block update causing an incompatible mode BBW even between the processes actually applying their changes. Additions and corrections are welcome. I hope this helps, Stephan Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - RMOUG Training Days 2003, Mar 5-6 Denver - Hotsos Clinic 101, Mar 25-27 London -Original Message- Millsap Sent: Thursday, February 20, 2003 12:24 AM To: Multiple recipients of list ORACLE-L Jonathan or Steve or Stephan will likely provide a better answer to this than I will, but I'll add this food for thought: I think that if (1) six CR versions of one data block address already reside on a given cache buffers chain, and (2) at least one
RE: Automatic Segment Space Management
Anjo personally saved my bacon when I was at a site in Dallas with this problem. This particular problem was a vendor application ported from Sybase and thus used select from blah_id for update; update blah; commit; instead of Oracle sequences. These guys had 1,200+ CR copies of each little 1-row-1-column id table in their system. In the end, the vendor repaired its app to use sequence numbers (within the week, actually!), and the problem which had caused daily shutdown/restarts ended instantly. The 42 patch, as it was called at the time, would have helped reduce the severity of the problem, but it wouldn't have solved it. I was pretty proud of myself when the engagement was done, but a monkey could have probably executed my part in the project if the monkey had known how to call Anjo. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - RMOUG Training Days 2003, Mar 5-6 Denver - Hotsos Clinic 101, Mar 25-27 London -Original Message- Sent: Thursday, February 20, 2003 1:04 PM To: Multiple recipients of list ORACLE-L The _db_block_max_cr_dba parameter was put in to fix this problem with massive number of CR copies (segment header blocks mostly). I remember seeing a test case that had 1500+ CR copies of the segment header block. So the fix was to limit the number of CR copies. The parameter _db_block_max_cr_dba had initially a default value of 42 (really). And worked perfectly, but it was brok in Parallel Server, the reason for it not working was very funny. While scanning the hash chain for the right (tsn, rdba) the CR code may already stop if it finds the best fit and never scan all the buffers and there it can't enforce the limit of 6. Another reason could be that the buffers are pinned (in use), but they should be flushed out later if the same buffer hash chain is scanned again for the (tsn, rdba). In version8 I have seen a particular test case with over 60+ CR copies of a index root block (running many processes doing NL and inserts into that index didn't help ofcourse). It is alway hard to tell why the limit is not enforced (may be we need a stat on this? ;-)). It could be a bug or buffer pinned (have seen both in production situations). Anjo. On Thursday 20 February 2003 07:18, Cary Millsap wrote: My guess would be it's the pinning issue. A pinned block will *never* be expelled, and the Oracle kernel is very unlikely to come back later to do an operation that's not possible to accomplish right now. However, having said that, it's likely something else too. I say this because I haven't even attempted to study the issue since about 1995, and virtually every time I've ever seen Steve Adams or Jonathan Lewis or Stephan Haisley's (etc.) mouth move, I become more aware that what I *thought* I knew in about 1995 is not really knowledge, it's more Knowledge Light. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - RMOUG Training Days 2003, Mar 5-6 Denver - Hotsos Clinic 101, Mar 25-27 London -Original Message- Gopalakrishnan Sent: Thursday, February 20, 2003 5:14 AM To: Multiple recipients of list ORACLE-L Cary: I guess the parameter just puts a softlimit of number of CR Blocks per DBA. But I have seen more number of CR copies (10+ CR copies per DBA) many times. But then I was using Relative File# in the X$BH while querying (though the chances of same block# in the different files becoming hot is very rare), which I realized later. Any thoughts Cary? KG --- Cary Millsap [EMAIL PROTECTED] wrote: I've received additional insight from Stephan Haisley of Oracle. Bottom-line, it sounds like the real trade-off is that if a larger number of CR versions could be left on a chain, then the number of undo operations required to fulfill a given query might be reduced, but at the expense of longer chain searches. From Stephan: Cary, I think you are forgetting the fact that updates can only occur to a CURRENT buffer. There can only be ONE current buffer of any block in the buffer cache. All row updates will occur to the same current buffer. Therefore, it is not related to the max. number of CR buffers permitted per datablock. A CR block can not be used for row updates. Sure the block is updated during application of undo to make it consistent of a particular SCN, but this is not the same as a DML row update that must be applied to the CURRENT version of the buffer. Clone buffers (as Cary mentioned them) are mainly created in one of two cases. When a block is required for CR purposes (closest buffer to required SCN is found, cloned and then undo is applied). The second common occasion is when you want to update (DML) a buffer, and there is a current buffer already in cache. If all users or waiters are for CR purposes only (NO DMLs) according to the users state objects, the buffer will be cloned, switching the clone
RE: Automatic Segment Space Management
Stephan just passed this on to me... Cary, I really must subscribe to this mail list, but until I do, maybe you can pass this on. You are correct, the _db_block_max_cr_dba parameter is just a guide. I believe that when needing to create a new CR copy and this limit has been reached Oracle tries to place any older CR buffers (not sure if it does all of them or oldest found) to the cold end of the LRU ready to leave the cache at the next possible opportunity. If the buffer has any users or waiters (can be seen in x$bh), then the CR buffer will remain in cache until next time. When a new CR buffer is created, and an older CR buffer no longer has users or waiters, it should be aged out of the cache as soon as possible. I hope this helps, Stephan Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - RMOUG Training Days 2003, Mar 5-6 Denver - Hotsos Clinic 101, Mar 25-27 London -Original Message- Millsap Sent: Thursday, February 20, 2003 1:41 PM To: Multiple recipients of list ORACLE-L Anjo personally saved my bacon when I was at a site in Dallas with this problem. This particular problem was a vendor application ported from Sybase and thus used select from blah_id for update; update blah; commit; instead of Oracle sequences. These guys had 1,200+ CR copies of each little 1-row-1-column id table in their system. In the end, the vendor repaired its app to use sequence numbers (within the week, actually!), and the problem which had caused daily shutdown/restarts ended instantly. The 42 patch, as it was called at the time, would have helped reduce the severity of the problem, but it wouldn't have solved it. I was pretty proud of myself when the engagement was done, but a monkey could have probably executed my part in the project if the monkey had known how to call Anjo. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - RMOUG Training Days 2003, Mar 5-6 Denver - Hotsos Clinic 101, Mar 25-27 London -Original Message- Sent: Thursday, February 20, 2003 1:04 PM To: Multiple recipients of list ORACLE-L The _db_block_max_cr_dba parameter was put in to fix this problem with massive number of CR copies (segment header blocks mostly). I remember seeing a test case that had 1500+ CR copies of the segment header block. So the fix was to limit the number of CR copies. The parameter _db_block_max_cr_dba had initially a default value of 42 (really). And worked perfectly, but it was brok in Parallel Server, the reason for it not working was very funny. While scanning the hash chain for the right (tsn, rdba) the CR code may already stop if it finds the best fit and never scan all the buffers and there it can't enforce the limit of 6. Another reason could be that the buffers are pinned (in use), but they should be flushed out later if the same buffer hash chain is scanned again for the (tsn, rdba). In version8 I have seen a particular test case with over 60+ CR copies of a index root block (running many processes doing NL and inserts into that index didn't help ofcourse). It is alway hard to tell why the limit is not enforced (may be we need a stat on this? ;-)). It could be a bug or buffer pinned (have seen both in production situations). Anjo. On Thursday 20 February 2003 07:18, Cary Millsap wrote: My guess would be it's the pinning issue. A pinned block will *never* be expelled, and the Oracle kernel is very unlikely to come back later to do an operation that's not possible to accomplish right now. However, having said that, it's likely something else too. I say this because I haven't even attempted to study the issue since about 1995, and virtually every time I've ever seen Steve Adams or Jonathan Lewis or Stephan Haisley's (etc.) mouth move, I become more aware that what I *thought* I knew in about 1995 is not really knowledge, it's more Knowledge Light. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - RMOUG Training Days 2003, Mar 5-6 Denver - Hotsos Clinic 101, Mar 25-27 London -Original Message- Gopalakrishnan Sent: Thursday, February 20, 2003 5:14 AM To: Multiple recipients of list ORACLE-L Cary: I guess the parameter just puts a softlimit of number of CR Blocks per DBA. But I have seen more number of CR copies (10+ CR copies per DBA) many times. But then I was using Relative File# in the X$BH while querying (though the chances of same block# in the different files becoming hot is very rare), which I realized later. Any thoughts Cary? KG --- Cary Millsap [EMAIL PROTECTED] wrote: I've received additional insight from Stephan Haisley of Oracle. Bottom-line, it sounds like the real trade-off is that if a larger number of CR versions could be left on a chain, then the number of undo operations required to fulfill a given query might be reduced, but at the expense of longer chain searches. From Stephan: Cary, I
RE: Automatic Segment Space Management
Cary I've done some pretty foolish things in my time, but I don't think I'd ever be silly enough to call you a monkey! Now Anjo on the other hand ... ;) Pete Controlling developers is like herding cats. Kevin Loney, Oracle DBA Handbook Oh no, it's not. It's much harder than that! Bruce Pihlamae, long-term Oracle DBA -Original Message- Millsap Sent: Thursday, February 20, 2003 11:41 AM To: Multiple recipients of list ORACLE-L Anjo personally saved my bacon when I was at a site in Dallas with this problem. This particular problem was a vendor application ported from Sybase and thus used select from blah_id for update; update blah; commit; instead of Oracle sequences. These guys had 1,200+ CR copies of each little 1-row-1-column id table in their system. In the end, the vendor repaired its app to use sequence numbers (within the week, actually!), and the problem which had caused daily shutdown/restarts ended instantly. The 42 patch, as it was called at the time, would have helped reduce the severity of the problem, but it wouldn't have solved it. I was pretty proud of myself when the engagement was done, but a monkey could have probably executed my part in the project if the monkey had known how to call Anjo. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - RMOUG Training Days 2003, Mar 5-6 Denver - Hotsos Clinic 101, Mar 25-27 London -Original Message- Sent: Thursday, February 20, 2003 1:04 PM To: Multiple recipients of list ORACLE-L The _db_block_max_cr_dba parameter was put in to fix this problem with massive number of CR copies (segment header blocks mostly). I remember seeing a test case that had 1500+ CR copies of the segment header block. So the fix was to limit the number of CR copies. The parameter _db_block_max_cr_dba had initially a default value of 42 (really). And worked perfectly, but it was brok in Parallel Server, the reason for it not working was very funny. While scanning the hash chain for the right (tsn, rdba) the CR code may already stop if it finds the best fit and never scan all the buffers and there it can't enforce the limit of 6. Another reason could be that the buffers are pinned (in use), but they should be flushed out later if the same buffer hash chain is scanned again for the (tsn, rdba). In version8 I have seen a particular test case with over 60+ CR copies of a index root block (running many processes doing NL and inserts into that index didn't help ofcourse). It is alway hard to tell why the limit is not enforced (may be we need a stat on this? ;-)). It could be a bug or buffer pinned (have seen both in production situations). Anjo. On Thursday 20 February 2003 07:18, Cary Millsap wrote: My guess would be it's the pinning issue. A pinned block will *never* be expelled, and the Oracle kernel is very unlikely to come back later to do an operation that's not possible to accomplish right now. However, having said that, it's likely something else too. I say this because I haven't even attempted to study the issue since about 1995, and virtually every time I've ever seen Steve Adams or Jonathan Lewis or Stephan Haisley's (etc.) mouth move, I become more aware that what I *thought* I knew in about 1995 is not really knowledge, it's more Knowledge Light. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - RMOUG Training Days 2003, Mar 5-6 Denver - Hotsos Clinic 101, Mar 25-27 London -Original Message- Gopalakrishnan Sent: Thursday, February 20, 2003 5:14 AM To: Multiple recipients of list ORACLE-L Cary: I guess the parameter just puts a softlimit of number of CR Blocks per DBA. But I have seen more number of CR copies (10+ CR copies per DBA) many times. But then I was using Relative File# in the X$BH while querying (though the chances of same block# in the different files becoming hot is very rare), which I realized later. Any thoughts Cary? KG --- Cary Millsap [EMAIL PROTECTED] wrote: I've received additional insight from Stephan Haisley of Oracle. Bottom-line, it sounds like the real trade-off is that if a larger number of CR versions could be left on a chain, then the number of undo operations required to fulfill a given query might be reduced, but at the expense of longer chain searches. From Stephan: Cary, I think you are forgetting the fact that updates can only occur to a CURRENT buffer. There can only be ONE current buffer of any block in the buffer cache. All row updates will occur to the same current buffer. Therefore, it is not related to the max. number of CR buffers permitted per datablock. A CR block can not be used for row updates. Sure the block is updated during application of undo to make it consistent of a particular SCN, but this is not the same as a DML row update that must be applied to the CURRENT version of the buffer. Clone buffers
RE: Automatic Segment Space Management
Title: RE: Automatic Segment Space Management In a very isolated test scenario with RAC; it removed a tremendous amount of free list contention(as well as header block..). In addition, we removed latch contention for a couple of specific latches as well.. But keep in mind; this was a very isolated test scenario.. Not sure how it would react with an ERP type of environment.. greg -Original Message- From: Jay Hostetter [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 19, 2003 9:34 AM To: Multiple recipients of list ORACLE-L Subject: Automatic Segment Space Management I'm continuing to introduce myself to 9i. I've been reading about Automatic Segment Space Management, and I just wondered if anybody had any positive/negative experiences with it. I got some good info at: http://www.dbazine.com/burleson11.html Thank you, Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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: Automatic Segment Space Management
Jay, I have been using ASSM for last five months in our Datawarehouse environment. Haven't had a chance to play with the OLTP side, yet. Inserts are way faster as compared to system managed extent allocation. I read Don's article on DBAZINE. However, I would like to add one caveat here: ASSM does not *eliminate* buffer busy waits as the article claims; it *reduces* them. BBW occur due to concurrent access to a buffer by more than one session. This will be the case regardless of number of freelists. While ASSM eliminates the freelist contention - thereby reducing BBW in inserts - it does not reduce the likelihood that more than one sessions will try to get the same block to the buffer cache simulataneously. Table drops appear a little slower in ASSM; but that could be wrong - I never timed dropping a table in the system managed mode. HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, February 19, 2003 9:33 AM I'm continuing to introduce myself to 9i. I've been reading about Automatic Segment Space Management, and I just wondered if anybody had any positive/negative experiences with it. I got some good info at: http://www.dbazine.com/burleson11.html Thank you, Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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: Arup Nanda 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: Automatic Segment Space Management
Jay - My impression from the Oracle9i New Features class is that it is most useful when you are doing intense inserts into a table. Based on that, I said to myself that I should remember this when I need to tune heavy inserts. Nothing past that. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 19, 2003 8:34 AM To: Multiple recipients of list ORACLE-L I'm continuing to introduce myself to 9i. I've been reading about Automatic Segment Space Management, and I just wondered if anybody had any positive/negative experiences with it. I got some good info at: http://www.dbazine.com/burleson11.html Thank you, Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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: Automatic Segment Space Management
See Jonathan Lewis' paper on ASS Management: http://www.jlcomp.demon.co.uk/bustbits.html HTH, A~ Jay Hostetter wrote: I'm continuing to introduce myself to 9i. I've been reading about Automatic Segment Space Management, and I just wondered if anybody had any positive/negative experiences with it. I got some good info at: http://www.dbazine.com/burleson11.html Thank you, Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Andrea LaBass 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: Automatic Segment Space Management
In a very limited tests that I performed with ASSM (quite some time ago), I found that it tends to use a bit more space than non-ASSM. Something to keep in mind when FTS is used to access tables. Not sure if this changed in 9.2.0.2.x. - Kirti -Original Message- Sent: Wednesday, February 19, 2003 10:24 AM To: Multiple recipients of list ORACLE-L Jay, I have been using ASSM for last five months in our Datawarehouse environment. Haven't had a chance to play with the OLTP side, yet. Inserts are way faster as compared to system managed extent allocation. I read Don's article on DBAZINE. However, I would like to add one caveat here: ASSM does not *eliminate* buffer busy waits as the article claims; it *reduces* them. BBW occur due to concurrent access to a buffer by more than one session. This will be the case regardless of number of freelists. While ASSM eliminates the freelist contention - thereby reducing BBW in inserts - it does not reduce the likelihood that more than one sessions will try to get the same block to the buffer cache simulataneously. Table drops appear a little slower in ASSM; but that could be wrong - I never timed dropping a table in the system managed mode. HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, February 19, 2003 9:33 AM I'm continuing to introduce myself to 9i. I've been reading about Automatic Segment Space Management, and I just wondered if anybody had any positive/negative experiences with it. I got some good info at: http://www.dbazine.com/burleson11.html Thank you, Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti 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: Automatic Segment Space Management
Arup, Just picking up the thread on the BBWs. (Btw, I asked this question in this list - never got an answer!) The following undocumented parameter limits the numbe of CR copies in the Block buffers. Name Value - -- Description --- _db_block_max_cr_dba 6 Maximum Allowed Number of CR buffers per dba What if there are more than 6 concurrent update requests for the same block. Would that not result in BBW? John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Disappointment is inevitable, but Discouragement is optional! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Arup Nanda [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 19, 2003 8:24 AM To: Multiple recipients of list ORACLE-L Subject: Re: Automatic Segment Space Management Jay, I have been using ASSM for last five months in our Datawarehouse environment. Haven't had a chance to play with the OLTP side, yet. Inserts are way faster as compared to system managed extent allocation. I read Don's article on DBAZINE. However, I would like to add one caveat here: ASSM does not *eliminate* buffer busy waits as the article claims; it *reduces* them. BBW occur due to concurrent access to a buffer by more than one session. This will be the case regardless of number of freelists. While ASSM eliminates the freelist contention - thereby reducing BBW in inserts - it does not reduce the likelihood that more than one sessions will try to get the same block to the buffer cache simulataneously. Table drops appear a little slower in ASSM; but that could be wrong - I never timed dropping a table in the system managed mode. HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, February 19, 2003 9:33 AM I'm continuing to introduce myself to 9i. I've been reading about Automatic Segment Space Management, and I just wondered if anybody had any positive/negative experiences with it. I got some good info at: http://www.dbazine.com/burleson11.html Thank you, Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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: Arup Nanda 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: 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
Re: Automatic Segment Space Management
It only takes two truly concurrent updates on a block to produce a BBW - only one actual current block change can take place at a time. However, the sequence of events would probably be something like: Session 1:pin block exclusively Sessions 2 - 6: join waiter list - into BBW Session 1:update block, release block pin Session 2:out of BBW - pin block exclusively Sessions 3 - 6: still waiting in BBW and so on. It might be quite hard to prove this though, as the high precision concurrency might be hard to achieve. (Perhaps you could fake it through discrete transactions). In practice, you might find that you got very rapid serialisation of updates most of the time, which could allow the pattern to be more like: Session 1:pin block, update and release Session 2:clone block to CU, change prior copy to CR pin clone, update and release Session 3:repeat. which would leave a chain of CR copies of the same block. The limit on CR blocks is a pretty soft limit - I believe its purpose is to keep to a minimum the time that the cache buffers chains latch for a given block is held as the chain is searched. It is still possible, however, for processes that NEED a CR block simply to grab the most appropriate one (i.e. legal and furthest back in time) and clone it to a new one. (You've reminded me that I still have to write a reply to Gerald Cunningham about his 160+ CR copies of numerous blocks in the buffer - sorry about the delay, Gerald). I believe that there are points in time that Oracle will 'new' (aka 'free') a buffer if it finds that there are too many copies - but I haven't proved that this is true, or figured out when it would do it. Of course, it would arguably make sense to do it, as 'free' blocks are used preferentially as the target for physical reads - so wiping the Nth CR copy of a block probably makes more sense than dumping a block with only a few recent clones. BTW - if there were Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 19 February 2003 20:07 Arup, Just picking up the thread on the BBWs. (Btw, I asked this question in this list - never got an answer!) The following undocumented parameter limits the numbe of CR copies in the Block buffers. Name Value - --- --- Description - --- --- _db_block_max_cr_dba 6 Maximum Allowed Number of CR buffers per dba What if there are more than 6 concurrent update requests for the same block. Would that not result in BBW? John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Automatic Segment Space Management
I was going to mention the paper - but don't take it as an extreme condemnation of ASSM. It's just an example of one detail of the implementation that still hasn't been corrected - and the example is doing something which you shouldn't do anyway. It does hint, however, that there may be other traps in the implementation waiting to catch the unwary. As Greg says, there have been cases where using ASSM with RAC has resulted in an easy and comfortable reduction in contention on freelist blocks - but the price you pay is that instead of having a very small number of freelist blocks per segment, you end up with a couple of bitmap space management blocks per segment - and that could turn into a significant fraction of the blocks from your total db_cache_size. More importantly, at the Miracle Masterclass in Copenhagen this year, Steve Adams listed a few outstanding (9.2) bugs with the way in which the bitmap space management blocks are maintained. In particular, it is very easy to 'leak' space and end up with bitmaps declaring that a data block is FULL when it isn't. As an easy example try this: create table in ASS managed tablespace insert 10,000 large rows into table rollback; dump all relevant bitmap blocks. You will find that every single block that was used id marked as FULL. Rolling back does not reset the bitmaps correctly. There are other, slightly more subtle, issues which might be a little more realistic in other cases - largely they revolve around rolling back or mixtures of deletes and inserts. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 19 February 2003 20:12 See Jonathan Lewis' paper on ASS Management: http://www.jlcomp.demon.co.uk/bustbits.html HTH, -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Automatic Segment Space Management
Gee, John, I was not aware of this underscore parameter. In my 9.2 database it's 6, just as yours. I did my test using upto three concurrent tranactions; guess I'll need to test with 7 or more. However, even if 7 concurrent transactions update the block's rows, and the limit is 6, then the waits should be based in ITL (Interested Trasnaction List) Waits, not BBW. since this is not due to a session not being able to get a particular buffer to the SGA, rather the lack of resources to get the CR copy of a buffer. Has anyone done this test? I'll certainly take it up later to build up on my upcoming article on ITL Waits. Regards, Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, February 19, 2003 2:45 PM Arup, Just picking up the thread on the BBWs. (Btw, I asked this question in this list - never got an answer!) The following undocumented parameter limits the numbe of CR copies in the Block buffers. Name Value - -- Description -- -- --- _db_block_max_cr_dba 6 Maximum Allowed Number of CR buffers per dba What if there are more than 6 concurrent update requests for the same block. Would that not result in BBW? John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Disappointment is inevitable, but Discouragement is optional! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Arup Nanda [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 19, 2003 8:24 AM To: Multiple recipients of list ORACLE-L Subject: Re: Automatic Segment Space Management Jay, I have been using ASSM for last five months in our Datawarehouse environment. Haven't had a chance to play with the OLTP side, yet. Inserts are way faster as compared to system managed extent allocation. I read Don's article on DBAZINE. However, I would like to add one caveat here: ASSM does not *eliminate* buffer busy waits as the article claims; it *reduces* them. BBW occur due to concurrent access to a buffer by more than one session. This will be the case regardless of number of freelists. While ASSM eliminates the freelist contention - thereby reducing BBW in inserts - it does not reduce the likelihood that more than one sessions will try to get the same block to the buffer cache simulataneously. Table drops appear a little slower in ASSM; but that could be wrong - I never timed dropping a table in the system managed mode. HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, February 19, 2003 9:33 AM I'm continuing to introduce myself to 9i. I've been reading about Automatic Segment Space Management, and I just wondered if anybody had any positive/negative experiences with it. I got some good info at: http://www.dbazine.com/burleson11.html Thank you, Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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: Arup Nanda 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
RE: Automatic Segment Space Management
Jonathan or Steve or Stephan will likely provide a better answer to this than I will, but I'll add this food for thought: I think that if (1) six CR versions of one data block address already reside on a given cache buffers chain, and (2) at least one of them is not pinned, and (3) a request for a 7th distinct CR version of the block were to come along (i.e., same block but different SCN), then I think that _db_block_max_cr_dba=6 simply means that one of the existing (unpinned) CR versions will get expelled before the new CR construction takes place. The resulting desired behavior is thus that the construction of the new clone will not increase the original length of the cache buffers chain. I believe the trade-off is this: If, after this occurred, some query called were to Q request the CR version that was expelled (that is, if the expelled CR version's SCN suited the query's SCN), then the Oracle kernel would have to execute all the instructions required to reconstruct that CR version again (reading undo blocks using the ITL as a guide), instead of simply finding the right version's buffer header already on the chain (had the setting been 7, then the requested CR version wouldn't have been expelled). This whole operation would of course cause the expulsion of some other unpinned CR version of the same block (keeping the number of CR versions of the block to 6), so that the chain length would not increase. Had the parameter setting been 7, then Q's new CR request could have been fulfilled more cheaply, but at the expense of incurring longer average cache buffers chain lengths, which would cause incrementally longer cache buffers chain scan times, which would cause incrementally worse contention for the cache buffers chains latch on the relevant cache buffers chain. I think producing 7+ concurrent updates of a block will test, as Arup notes, only the kernel's ITL management prowess, not the case you're interested in. I'll step back timidly now, in anticipation of what dog trainers call a firm correction. :) Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - RMOUG Training Days 2003, Mar 5-6 Denver - Hotsos Clinic 101, Mar 25-27 London -Original Message- Sent: Wednesday, February 19, 2003 4:03 PM To: Multiple recipients of list ORACLE-L Gee, John, I was not aware of this underscore parameter. In my 9.2 database it's 6, just as yours. I did my test using upto three concurrent tranactions; guess I'll need to test with 7 or more. However, even if 7 concurrent transactions update the block's rows, and the limit is 6, then the waits should be based in ITL (Interested Trasnaction List) Waits, not BBW. since this is not due to a session not being able to get a particular buffer to the SGA, rather the lack of resources to get the CR copy of a buffer. Has anyone done this test? I'll certainly take it up later to build up on my upcoming article on ITL Waits. Regards, Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, February 19, 2003 2:45 PM Arup, Just picking up the thread on the BBWs. (Btw, I asked this question in this list - never got an answer!) The following undocumented parameter limits the numbe of CR copies in the Block buffers. Name Value - -- Description -- -- --- _db_block_max_cr_dba 6 Maximum Allowed Number of CR buffers per dba What if there are more than 6 concurrent update requests for the same block. Would that not result in BBW? John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Disappointment is inevitable, but Discouragement is optional! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Arup Nanda [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 19, 2003 8:24 AM To: Multiple recipients of list ORACLE-L Subject: Re: Automatic Segment Space Management Jay, I have been using ASSM for last five months in our Datawarehouse environment. Haven't had a chance to play with the OLTP side, yet. Inserts are way faster as compared to system managed extent allocation. I read Don's article on DBAZINE. However, I would like to add one caveat here: ASSM does not *eliminate* buffer busy waits as the article claims; it *reduces* them. BBW occur due to concurrent access to a buffer by more than one session. This will be the case regardless of number of freelists. While ASSM eliminates the freelist contention - thereby reducing BBW in inserts - it does not reduce the likelihood that more than one sessions will try to get the same block to the buffer cache simulataneously. Table drops appear a little slower in ASSM
RE: automatic segment space management
You need to benchmark ASSM carefully because it may have impact especially on your smaller tables. To avoid the concurrency issues, you can end up with blocks sprayed as rows are created. For example, you might add a single row to an (empty) table and end up with the table being 10 blocks instead of 1 because ASSM tries to spread blocks around in this fashion. If those tables are targets of joins ( to large tables) as they often tend to be in DW, you might be trawling through a much larger amount of blocks then you need to be... hth connor --- Rachel Carmichael [EMAIL PROTECTED] wrote: Thanks Ian, that was indeed the question, although the other information is useful as well. if I understand what you are saying correctly, it works but it won't really buy me anything and I might do better controlling the space myself. And while you have had no problems, you've heard negative things about it. I can't afford to have this database not be available so I'll manage them on my own Rachel --- MacGregor, Ian A. [EMAIL PROTECTED] wrote: The question posed was not whether extent management local should be used, but whether automatic segment space management should be used. As this is a data warehouse, I would not expect you to have transactions trying to change the same block. Assuming you are loading; that is, inserting data and not doing updates, wouldn't you try to cram as much data as possible into a block? Seems this could be done more easily by controlling these parameters yourself I've got one system using automatic segment space management without any problems, however when I posed the same question on using it a few months ago, the respone which trickled in way highly negative concerning its usage. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 03, 2002 8:49 AM To: Multiple recipients of list ORACLE-L time for me to ask the experts again. My data warehouse will be 9.2, with all locally managed tablespaces. We will be following what I have taken to calling the Goldilocks principle -- that of small, medium and large tablespace extent sizes, with variations in that we will separate indexes and data, and will have even more separation for our fact tables into partitioned tables and tablespaces. However, now comes the time for me to work out storage clauses. And a quick read through the docs leaves me wondering if I should just turn on automatic segment-space management and not worry about setting PCTFREE, PCTUSED and FREELIST parameters. I can't find any real information or bugs on MetaLink either. Does anyone have any experience, good OR bad, with using this feature? If you are doing data warehouse work, what are good values for the parameters if I DO use them? One fact table is likely to be highly updated (customer info) as we collect more and more specific information from customers. The rest will be, as you would expect from a DW, mostly inserts. Help? Thanks! Rachel __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network
Re: automatic segment space management
yes I did notice that in the docs (oh my goodness, the docs were CLEAR? G) I think, based on what Ian has said, that I will manage the space myself. I know that updates will be rare, under 5% of the time. I know that deletes will not happen, unless I am pruning partitions, so that will not be affected by PCTFREE/PCTUSED values. I know that the data load will be once daily, a single process (for the time being), with no other users on the system. So I should be able to figure this out :) Rachel --- Tim Gorman [EMAIL PROTECTED] wrote: One note: 9i automatic segment space management does not automate PCTFREE; that still functions as before. It does cause PCTUSED, FREELISTS, and FREELIST GROUPS to be ignored, however... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, September 03, 2002 9:48 AM time for me to ask the experts again. My data warehouse will be 9.2, with all locally managed tablespaces. We will be following what I have taken to calling the Goldilocks principle -- that of small, medium and large tablespace extent sizes, with variations in that we will separate indexes and data, and will have even more separation for our fact tables into partitioned tables and tablespaces. However, now comes the time for me to work out storage clauses. And a quick read through the docs leaves me wondering if I should just turn on automatic segment-space management and not worry about setting PCTFREE, PCTUSED and FREELIST parameters. I can't find any real information or bugs on MetaLink either. Does anyone have any experience, good OR bad, with using this feature? If you are doing data warehouse work, what are good values for the parameters if I DO use them? One fact table is likely to be highly updated (customer info) as we collect more and more specific information from customers. The rest will be, as you would expect from a DW, mostly inserts. Help? Thanks! Rachel __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: automatic segment space management
okay, if I hadn't already been convinced not to use it, this would clinch it... we will have joins of 16+ tables to one of the fact tables and I can't impede performance. This is a very visible system and needs to be as good as it can be --- Connor McDonald [EMAIL PROTECTED] wrote: You need to benchmark ASSM carefully because it may have impact especially on your smaller tables. To avoid the concurrency issues, you can end up with blocks sprayed as rows are created. For example, you might add a single row to an (empty) table and end up with the table being 10 blocks instead of 1 because ASSM tries to spread blocks around in this fashion. If those tables are targets of joins ( to large tables) as they often tend to be in DW, you might be trawling through a much larger amount of blocks then you need to be... hth connor --- Rachel Carmichael [EMAIL PROTECTED] wrote: Thanks Ian, that was indeed the question, although the other information is useful as well. if I understand what you are saying correctly, it works but it won't really buy me anything and I might do better controlling the space myself. And while you have had no problems, you've heard negative things about it. I can't afford to have this database not be available so I'll manage them on my own Rachel --- MacGregor, Ian A. [EMAIL PROTECTED] wrote: The question posed was not whether extent management local should be used, but whether automatic segment space management should be used. As this is a data warehouse, I would not expect you to have transactions trying to change the same block. Assuming you are loading; that is, inserting data and not doing updates, wouldn't you try to cram as much data as possible into a block? Seems this could be done more easily by controlling these parameters yourself I've got one system using automatic segment space management without any problems, however when I posed the same question on using it a few months ago, the respone which trickled in way highly negative concerning its usage. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 03, 2002 8:49 AM To: Multiple recipients of list ORACLE-L time for me to ask the experts again. My data warehouse will be 9.2, with all locally managed tablespaces. We will be following what I have taken to calling the Goldilocks principle -- that of small, medium and large tablespace extent sizes, with variations in that we will separate indexes and data, and will have even more separation for our fact tables into partitioned tables and tablespaces. However, now comes the time for me to work out storage clauses. And a quick read through the docs leaves me wondering if I should just turn on automatic segment-space management and not worry about setting PCTFREE, PCTUSED and FREELIST parameters. I can't find any real information or bugs on MetaLink either. Does anyone have any experience, good OR bad, with using this feature? If you are doing data warehouse work, what are good values for the parameters if I DO use them? One fact table is likely to be highly updated (customer info) as we collect more and more specific information from customers. The rest will be, as you would expect from a DW, mostly inserts. Help? Thanks! Rachel __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB
RE: automatic segment space management
We didn't implement a Large tablespace on any of our OLTP databases for that very reason. The tables that didn't fit into Small or Medium got their own tablespaces. There are only a few big tables on the OLTP databases and on the Data Warehouse we've moved toward giving the really big partitioned tables one tablespace/partition. It's made storage management a bit easier. -Original Message- Sent: Tuesday, September 03, 2002 6:39 PM To: Multiple recipients of list ORACLE-L So, proper LMT means no LBE? ;) Great analogy! All the head-spinning and the green projectile vomiting and such... BTW, yes that is a good paper. I've read it and am trying to deal with the extent sizes as it applies to our DB, as only about two dozen of the 800+ tables are larger than 128MB and none are larger than 4GB. So, either I consider making the Large LMTs smaller, or maybe our li'l 25GB DB doesn't need a Papa Bear. Or maybe I just need to get used to the idea of having more than a couple hundred extents... :) Thanks! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 03, 2002 3:04 PM To: Multiple recipients of list ORACLE-L Subject: RE: automatic segment space management Rich - Good point. Yes, I create all the tables here, at least in production, and I probably wouldn't use autoextend if the situation were otherwise. The other thing to consider is if you are using uniform extents, by definition you have bought into the philosophy that you can have many extents and your database will not do a Linda Blair Exorcist imitation on you. If we use the guideline that the number of extents should be not many more than 1,000, then the 128K extent will get you 128M, which is good for most tables. While we are on the subject, anyone considering switching to LMTs should carefully read How to Stop Defragmenting and Start Living by Juan Loaiza, Rosanne Toohey, Bhaskar Himatsingka. You can find a copy on http://www.hotsos.com. Trying to implement a philosophy without fully understanding it is a recipe for failure. Dennis Williams -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: automatic segment space management
Rachel - I have been using LMTs with uniform extents (Oracle-recommended variation) for a couple of years now with Oracle 8.1.6, and now 9.2. The Oracle-recommended extent sizes are 128k, 4m, and 128m. As far as PCTFREE, et. al., these are at the table level, so my opinion would be that the guidelines for these are unchanged from the pre-LMT days. The key issue with the highly updated customer table would be whether the size of the row is changing. If you can keep the row size constant, then you won't wind up with chained rows. The biggest issue facing you is whether you turn AUTOEXTEND on. I did that and have encountered relatively few problems. Well, one problem. I had tables set with large NEXT extents to minimize extents, and when one extended boy did my sys admin get excited. I changed that. A bigger issue in building your data warehouse is whether you can use the partitioning option. Most of our queries were taking more than 2 minutes and I was able to partition and bring that down below 10 seconds. The users were pretty excited. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 03, 2002 10:49 AM To: Multiple recipients of list ORACLE-L time for me to ask the experts again. My data warehouse will be 9.2, with all locally managed tablespaces. We will be following what I have taken to calling the Goldilocks principle -- that of small, medium and large tablespace extent sizes, with variations in that we will separate indexes and data, and will have even more separation for our fact tables into partitioned tables and tablespaces. However, now comes the time for me to work out storage clauses. And a quick read through the docs leaves me wondering if I should just turn on automatic segment-space management and not worry about setting PCTFREE, PCTUSED and FREELIST parameters. I can't find any real information or bugs on MetaLink either. Does anyone have any experience, good OR bad, with using this feature? If you are doing data warehouse work, what are good values for the parameters if I DO use them? One fact table is likely to be highly updated (customer info) as we collect more and more specific information from customers. The rest will be, as you would expect from a DW, mostly inserts. Help? Thanks! Rachel __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: automatic segment space management
We used this method (I wish I had come up with the Goldilocks code name...) in 8.0.4 on a couple of data warehouses. The bottom line is that we had 1 space related failure (application code filled up the error log) in over 9 months of operation. Which so impressed management that they decided to cut the support budget (not enough outages/calls to help desk). There were over 150 tablespaces per database so we could perform I/O balancing, object segregation, etc. We did not follow the SAFE (3 and only 3 extent sizes in the database). Rather we used 3 extent sizes per tablespace with medium a multiple of small and large a multiple of medium. PCTFREE - set to 10 (no updates, but provided a little space just in case this changed) PCTUSED - set to 80 (same reason as above) FREELIST - set to 5 (originally designed so only 2 processes would ever concurrently insert data) Sounds like you are on the right track. -Original Message- Sent: Tuesday, September 03, 2002 9:49 AM To: Multiple recipients of list ORACLE-L time for me to ask the experts again. My data warehouse will be 9.2, with all locally managed tablespaces. We will be following what I have taken to calling the Goldilocks principle -- that of small, medium and large tablespace extent sizes, with variations in that we will separate indexes and data, and will have even more separation for our fact tables into partitioned tables and tablespaces. However, now comes the time for me to work out storage clauses. And a quick read through the docs leaves me wondering if I should just turn on automatic segment-space management and not worry about setting PCTFREE, PCTUSED and FREELIST parameters. I can't find any real information or bugs on MetaLink either. Does anyone have any experience, good OR bad, with using this feature? If you are doing data warehouse work, what are good values for the parameters if I DO use them? One fact table is likely to be highly updated (customer info) as we collect more and more specific information from customers. The rest will be, as you would expect from a DW, mostly inserts. Help? Thanks! Rachel __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fink, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: automatic segment space management
Dennis, who creates tables in your DB? If devs do, don't you worry that one could accidentally create a 512MB table in your 128K TS, instead of a 512KB one? I really would like to implement LMTs here, and am doing so in certain restricted instances where I'm the only one who creates the objects for that TS, but I'm a little leery of letting it go to the developers, even though I don't use AUTOEXTEND. Thx! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 03, 2002 12:42 PM To: Multiple recipients of list ORACLE-L Subject: RE: automatic segment space management Rachel - I have been using LMTs with uniform extents (Oracle-recommended variation) for a couple of years now with Oracle 8.1.6, and now 9.2. The Oracle-recommended extent sizes are 128k, 4m, and 128m. As far as PCTFREE, et. al., these are at the table level, so my opinion would be that the guidelines for these are unchanged from the pre-LMT days. The key issue with the highly updated customer table would be whether the size of the row is changing. If you can keep the row size constant, then you won't wind up with chained rows. The biggest issue facing you is whether you turn AUTOEXTEND on. I did that and have encountered relatively few problems. Well, one problem. I had tables set with large NEXT extents to minimize extents, and when one extended boy did my sys admin get excited. I changed that. A bigger issue in building your data warehouse is whether you can use the partitioning option. Most of our queries were taking more than 2 minutes and I was able to partition and bring that down below 10 seconds. The users were pretty excited. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: automatic segment space management
Rachel, You did not say if you would be using the UNIFORM option for the LMT's. If you allow the system to choose the initial sizing there can be a lot of wasted space as the table size grows. The system will choose sizing options that you most likely would not choose. I can't find my reference to the sizes that are chosen but 16K 64K 1M and 4M sound correct. The extends will increase as the total size of the table increases. I find it easier to manage the sizes of the tables in the tablespace with uniform extent sizes that are manageable and mist likely to be filled in a reasonable time frame. For small static tables I used a multiple of the block size and tried to group similarly used table in the same tablespace. The tables that are continually growing daily I partitioned by date range and allowed the tablespace to autoextend. There is very little wasted space and the tablespaces are usually 100 % full until the next extent is needed. I accepted the default PCTFREE, PCTUSED and FREELIST parameters and I haven't seen and problems yet. I hope this helps, Ron ROR mª¿ªm [EMAIL PROTECTED] 09/03/02 11:48AM time for me to ask the experts again. My data warehouse will be 9.2, with all locally managed tablespaces. We will be following what I have taken to calling the Goldilocks principle -- that of small, medium and large tablespace extent sizes, with variations in that we will separate indexes and data, and will have even more separation for our fact tables into partitioned tables and tablespaces. However, now comes the time for me to work out storage clauses. And a quick read through the docs leaves me wondering if I should just turn on automatic segment-space management and not worry about setting PCTFREE, PCTUSED and FREELIST parameters. I can't find any real information or bugs on MetaLink either. Does anyone have any experience, good OR bad, with using this feature? If you are doing data warehouse work, what are good values for the parameters if I DO use them? One fact table is likely to be highly updated (customer info) as we collect more and more specific information from customers. The rest will be, as you would expect from a DW, mostly inserts. Help? Thanks! Rachel __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: automatic segment space management
We may end up reworking the extent sizes, right now they are smaller than those but we are still in stage one, haven't gone live yet (and I don't even want to think about what a pain it will be to change things when we do go live). Data load test coming up soon, so I'll have a better idea of what I need to change things to, if I need to change them. There is no way the customer row will remain the same size unless I change all the varchar fields to char. We have VERY sparse data as yet and expect to be able to entice customers to fill in the blanks which will cause rows to grow. We are allocating WAY more space than we need at the moment and will be closely monitoring growth (once a day data loads) and I can always turn on autoextend if I need it. But then again, I am not the DBA who will be responsible for the production site, at least not for any space issues on the production DW. Rachel --- DENNIS WILLIAMS [EMAIL PROTECTED] wrote: Rachel - I have been using LMTs with uniform extents (Oracle-recommended variation) for a couple of years now with Oracle 8.1.6, and now 9.2. The Oracle-recommended extent sizes are 128k, 4m, and 128m. As far as PCTFREE, et. al., these are at the table level, so my opinion would be that the guidelines for these are unchanged from the pre-LMT days. The key issue with the highly updated customer table would be whether the size of the row is changing. If you can keep the row size constant, then you won't wind up with chained rows. The biggest issue facing you is whether you turn AUTOEXTEND on. I did that and have encountered relatively few problems. Well, one problem. I had tables set with large NEXT extents to minimize extents, and when one extended boy did my sys admin get excited. I changed that. A bigger issue in building your data warehouse is whether you can use the partitioning option. Most of our queries were taking more than 2 minutes and I was able to partition and bring that down below 10 seconds. The users were pretty excited. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 03, 2002 10:49 AM To: Multiple recipients of list ORACLE-L time for me to ask the experts again. My data warehouse will be 9.2, with all locally managed tablespaces. We will be following what I have taken to calling the Goldilocks principle -- that of small, medium and large tablespace extent sizes, with variations in that we will separate indexes and data, and will have even more separation for our fact tables into partitioned tables and tablespaces. However, now comes the time for me to work out storage clauses. And a quick read through the docs leaves me wondering if I should just turn on automatic segment-space management and not worry about setting PCTFREE, PCTUSED and FREELIST parameters. I can't find any real information or bugs on MetaLink either. Does anyone have any experience, good OR bad, with using this feature? If you are doing data warehouse work, what are good values for the parameters if I DO use them? One fact table is likely to be highly updated (customer info) as we collect more and more specific information from customers. The rest will be, as you would expect from a DW, mostly inserts. Help? Thanks! Rachel __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official
RE: automatic segment space management
Dan, Why 3 extent sizes in each tablespace? I can see how you prevented the dreaded fragmentation problem by making them multiples of one another but I don't understand why you did that. If I'm using LMTs can I still do that? I haven't really used them, I'd thought to not specify the initial and next extent sizes and just let the LMT deal with it. As for the Goldilocks name, you are more than welcome to steal, uh, borrow, it :) Rachel --- Fink, Dan [EMAIL PROTECTED] wrote: We used this method (I wish I had come up with the Goldilocks code name...) in 8.0.4 on a couple of data warehouses. The bottom line is that we had 1 space related failure (application code filled up the error log) in over 9 months of operation. Which so impressed management that they decided to cut the support budget (not enough outages/calls to help desk). There were over 150 tablespaces per database so we could perform I/O balancing, object segregation, etc. We did not follow the SAFE (3 and only 3 extent sizes in the database). Rather we used 3 extent sizes per tablespace with medium a multiple of small and large a multiple of medium. PCTFREE - set to 10 (no updates, but provided a little space just in case this changed) PCTUSED - set to 80 (same reason as above) FREELIST - set to 5 (originally designed so only 2 processes would ever concurrently insert data) Sounds like you are on the right track. -Original Message- Sent: Tuesday, September 03, 2002 9:49 AM To: Multiple recipients of list ORACLE-L time for me to ask the experts again. My data warehouse will be 9.2, with all locally managed tablespaces. We will be following what I have taken to calling the Goldilocks principle -- that of small, medium and large tablespace extent sizes, with variations in that we will separate indexes and data, and will have even more separation for our fact tables into partitioned tables and tablespaces. However, now comes the time for me to work out storage clauses. And a quick read through the docs leaves me wondering if I should just turn on automatic segment-space management and not worry about setting PCTFREE, PCTUSED and FREELIST parameters. I can't find any real information or bugs on MetaLink either. Does anyone have any experience, good OR bad, with using this feature? If you are doing data warehouse work, what are good values for the parameters if I DO use them? One fact table is likely to be highly updated (customer info) as we collect more and more specific information from customers. The rest will be, as you would expect from a DW, mostly inserts. Help? Thanks! Rachel __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fink, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: automatic segment space management
Ron, yes it helps, thanks! I forgot to mention that I will be using UNIFORM sizes for the LMTs, I don't really see the advantage to autoallocate I can probably get away with the defaults for the parameters (if I don't use automatic segment space allocation) but it will waste some space in some of the tables. We will be partitioning most of the fact tables by date (the date dimension key is going to be the numeric representation of the date that row has info on) but likely the customer fact table will be hash partitioned as we don't intend to ever prune that table. Rachel --- Ron Rogers [EMAIL PROTECTED] wrote: Rachel, You did not say if you would be using the UNIFORM option for the LMT's. If you allow the system to choose the initial sizing there can be a lot of wasted space as the table size grows. The system will choose sizing options that you most likely would not choose. I can't find my reference to the sizes that are chosen but 16K 64K 1M and 4M sound correct. The extends will increase as the total size of the table increases. I find it easier to manage the sizes of the tables in the tablespace with uniform extent sizes that are manageable and mist likely to be filled in a reasonable time frame. For small static tables I used a multiple of the block size and tried to group similarly used table in the same tablespace. The tables that are continually growing daily I partitioned by date range and allowed the tablespace to autoextend. There is very little wasted space and the tablespaces are usually 100 % full until the next extent is needed. I accepted the default PCTFREE, PCTUSED and FREELIST parameters and I haven't seen and problems yet. I hope this helps, Ron ROR mª¿ªm [EMAIL PROTECTED] 09/03/02 11:48AM time for me to ask the experts again. My data warehouse will be 9.2, with all locally managed tablespaces. We will be following what I have taken to calling the Goldilocks principle -- that of small, medium and large tablespace extent sizes, with variations in that we will separate indexes and data, and will have even more separation for our fact tables into partitioned tables and tablespaces. However, now comes the time for me to work out storage clauses. And a quick read through the docs leaves me wondering if I should just turn on automatic segment-space management and not worry about setting PCTFREE, PCTUSED and FREELIST parameters. I can't find any real information or bugs on MetaLink either. Does anyone have any experience, good OR bad, with using this feature? If you are doing data warehouse work, what are good values for the parameters if I DO use them? One fact table is likely to be highly updated (customer info) as we collect more and more specific information from customers. The rest will be, as you would expect from a DW, mostly inserts. Help? Thanks! Rachel __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY,
RE: automatic segment space management
I recall that these were the days before LMT and at the start of moving away from fine-grained object sizing. In hindsight, 1 size per tablespace makes more sense. There could be several exceptions. For example, all of the reference tables/indexes were in a single tablespace. Since these were fairly small and very static, we had a more classic approach to sizing (still only 3 extent sizes). LMTs are of 2 varieties Uniform size and Autoallocate. Uniform means 1 and only 1 extent size. Autoallocate has 4 extent sizes (Oracle documents only 3). I have used autoallocate when the rough size was not known or we were mixing several general sizes. Uniform would be good for known partitions, small reference tables, etc. So, if this is the goldilocks approach...who are the 3 bears? -Original Message- Sent: Tuesday, September 03, 2002 12:49 PM To: Multiple recipients of list ORACLE-L Dan, Why 3 extent sizes in each tablespace? I can see how you prevented the dreaded fragmentation problem by making them multiples of one another but I don't understand why you did that. If I'm using LMTs can I still do that? I haven't really used them, I'd thought to not specify the initial and next extent sizes and just let the LMT deal with it. As for the Goldilocks name, you are more than welcome to steal, uh, borrow, it :) Rachel --- Fink, Dan [EMAIL PROTECTED] wrote: We used this method (I wish I had come up with the Goldilocks code name...) in 8.0.4 on a couple of data warehouses. The bottom line is that we had 1 space related failure (application code filled up the error log) in over 9 months of operation. Which so impressed management that they decided to cut the support budget (not enough outages/calls to help desk). There were over 150 tablespaces per database so we could perform I/O balancing, object segregation, etc. We did not follow the SAFE (3 and only 3 extent sizes in the database). Rather we used 3 extent sizes per tablespace with medium a multiple of small and large a multiple of medium. PCTFREE - set to 10 (no updates, but provided a little space just in case this changed) PCTUSED - set to 80 (same reason as above) FREELIST - set to 5 (originally designed so only 2 processes would ever concurrently insert data) Sounds like you are on the right track. -Original Message- Sent: Tuesday, September 03, 2002 9:49 AM To: Multiple recipients of list ORACLE-L time for me to ask the experts again. My data warehouse will be 9.2, with all locally managed tablespaces. We will be following what I have taken to calling the Goldilocks principle -- that of small, medium and large tablespace extent sizes, with variations in that we will separate indexes and data, and will have even more separation for our fact tables into partitioned tables and tablespaces. However, now comes the time for me to work out storage clauses. And a quick read through the docs leaves me wondering if I should just turn on automatic segment-space management and not worry about setting PCTFREE, PCTUSED and FREELIST parameters. I can't find any real information or bugs on MetaLink either. Does anyone have any experience, good OR bad, with using this feature? If you are doing data warehouse work, what are good values for the parameters if I DO use them? One fact table is likely to be highly updated (customer info) as we collect more and more specific information from customers. The rest will be, as you would expect from a DW, mostly inserts. Help? Thanks! Rachel __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fink, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the
RE: automatic segment space management
On Tablespace Creation In sys.dba_tablespaces , Field ALLOCATION_TYPE Defaults to
SYSTEM
. Hence NEXT_EXTENT of Created Tables is NOT Taken from the User-specified Value but
internally by Oracle itself . This leads to Excessively Large Number of EXTENTs (Small
in Size)
To Allow Table Creation with NEXT_EXTENT taken from User Specified Value , the Value
ALLOCATION_TYPE can be Changed to USER From the Original SYSTEM Value as follows
SQL exec sys.dbms_space_admin.tablespace_migrate_from_local('GAM_PT1_TBLSPC');
SQL exec sys.dbms_space_admin.tablespace_migrate_to_local('GAM_PT1_TBLSPC');
This does the needful Allows Object's NEXT_EXTENT Size to be User-Specified
We have used Such Tablespaces in Benchmarking Activities BOTH with Oracle 8.1.7 9.0
The Performance has been just fine
HTH
P.S. Feel free to mention any Disadvantages with this approach
-Original Message-
Sent: Tuesday, September 03, 2002 11:49 PM
To: Multiple recipients of list ORACLE-L
Rachel,
You did not say if you would be using the UNIFORM option for the
LMT's. If you allow the system to choose the initial sizing there can be
a lot of wasted space as the table size grows. The system will choose
sizing options that you most likely would not choose. I can't find my
reference to the sizes that are chosen but 16K 64K 1M and 4M sound
correct. The extends will increase as the total size of the table
increases.
I find it easier to manage the sizes of the tables in the tablespace
with uniform extent sizes that are manageable and mist likely to be
filled in a reasonable time frame. For small static tables I used a
multiple of the block size and tried to group similarly used table in
the same tablespace. The tables that are continually growing daily I
partitioned by date range and allowed the tablespace to autoextend.
There is very little wasted space and the tablespaces are usually 100 %
full until the next extent is needed.
I accepted the default PCTFREE, PCTUSED and FREELIST parameters and I
haven't seen and problems yet.
I hope this helps,
Ron
ROR mª¿ªm
[EMAIL PROTECTED] 09/03/02 11:48AM
time for me to ask the experts again.
My data warehouse will be 9.2, with all locally managed tablespaces.
We
will be following what I have taken to calling the Goldilocks
principle -- that of small, medium and large tablespace extent sizes,
with variations in that we will separate indexes and data, and will
have even more separation for our fact tables into partitioned tables
and tablespaces.
However, now comes the time for me to work out storage clauses. And a
quick read through the docs leaves me wondering if I should just turn
on automatic segment-space management and not worry about setting
PCTFREE, PCTUSED and FREELIST parameters. I can't find any real
information or bugs on MetaLink either.
Does anyone have any experience, good OR bad, with using this feature?
If you are doing data warehouse work, what are good values for the
parameters if I DO use them? One fact table is likely to be highly
updated (customer info) as we collect more and more specific
information from customers. The rest will be, as you would expect from
a DW, mostly inserts.
Help?
Thanks!
Rachel
__
Do You Yahoo!?
Yahoo! Finance - Get real-time stock quotes
http://finance.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rachel Carmichael
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ron Rogers
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: VIVEK_SHARMA
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from
RE: automatic segment space management
Rachel The varchar business on your fact table worries me. I'm not trying to be critical, but to bring up some issues you may want to consider before you begin the big load. Unfortunately too many DWs end up with a flawed data model (ours included) that limit its usability. Sometimes the fortunate sites are the ones that get it so wrong it must be blasted away and recreated. Guess who gets to bear the brunt of that hasty learning exercise?? Yep, that's right. Here are my thoughts for what they are worth. 1. Normally the fact table is very large, so it is critical that it be designed as nearly right as possible. Especially with what is called the granularity, the lowest level of data that is stored. You can always aggregate up, never down. Daily data can be summed to weekly, but we can't take weekly data and figure out the daily amounts. 2. When you say the customers fill in the blanks, that worries me. That doesn't sound so much like a DW as an OLTP. Where is the history dimension? 3. The fact table usually is so large that the information only makes sense in aggregate. You aren't looking for the particular blue-eyed 23-year old female from Des Moines, but trying to find HOW MANY blue-eyed, etc. This means that you won't be scratching around with VARCHAR2 fields with query operators such as LIKE. Performance would be really BAD. 4. I don't know your application, just the minor details you've mentioned in passing, but consider something like this. The FACT table logs each new information that a user provides. VARCHAR2 fields. Never UPDATEd, just add a new record along with the date that record was added. From that we create an aggregate table CURRFACT. One row per customer. Weekly we scan the new records added to FACT, pulling new facts, updated facts into CURRFACT. Most of the fields in CURRFACT are single character flag fields. Bit map index the heck (sorry, but we have a fierce naughty word scanner) out of CURRFACT. Performance is awesome. Queries return before the users hit enter (just kidding). From to time marketing recognizes some relevant fact that isn't in CURRFACT. You add a new column to CURRFACT and start a really big query on FACT when you leave for the weekend that will populate the new column. There may be a few fields like address that you populate in CURRFACT just for convenience. But you don't search them. Also, if you ever need the history of how your customers have moved around, you have that data. And remember, a DW is all about history, never about current information. The modeling issues have a lot more about the performance and usability of the DW than the choices we have as DBAs such as LMT. Okay, I'll quit prattling on here. You probably didn't even get a say in the data model. They never ask the DBA. But if you raise the issues beforehand it'll amuse you more when they come back and ask you to redo everything. Hey, I just noticed that you won't be the production DBA on this! No worries! Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 03, 2002 1:44 PM To: Multiple recipients of list ORACLE-L We may end up reworking the extent sizes, right now they are smaller than those but we are still in stage one, haven't gone live yet (and I don't even want to think about what a pain it will be to change things when we do go live). Data load test coming up soon, so I'll have a better idea of what I need to change things to, if I need to change them. There is no way the customer row will remain the same size unless I change all the varchar fields to char. We have VERY sparse data as yet and expect to be able to entice customers to fill in the blanks which will cause rows to grow. We are allocating WAY more space than we need at the moment and will be closely monitoring growth (once a day data loads) and I can always turn on autoextend if I need it. But then again, I am not the DBA who will be responsible for the production site, at least not for any space issues on the production DW. Rachel --- DENNIS WILLIAMS [EMAIL PROTECTED] wrote: Rachel - I have been using LMTs with uniform extents (Oracle-recommended variation) for a couple of years now with Oracle 8.1.6, and now 9.2. The Oracle-recommended extent sizes are 128k, 4m, and 128m. As far as PCTFREE, et. al., these are at the table level, so my opinion would be that the guidelines for these are unchanged from the pre-LMT days. The key issue with the highly updated customer table would be whether the size of the row is changing. If you can keep the row size constant, then you won't wind up with chained rows. The biggest issue facing you is whether you turn AUTOEXTEND on. I did that and have encountered relatively few problems. Well, one problem. I had tables set with large NEXT extents to minimize extents, and when one extended boy did my sys admin get excited. I changed that. A bigger issue in building your data warehouse is whether you
RE: automatic segment space management
So, if this is the goldilocks approach...who are the 3 bears? duh. small tablespace (Baby Bear), medium tablespace (Mama Bear) and large tablespace (Papa Bear) and if a table is Goldilocks then one and only one tablespace will be just right Rachel --- Fink, Dan [EMAIL PROTECTED] wrote: I recall that these were the days before LMT and at the start of moving away from fine-grained object sizing. In hindsight, 1 size per tablespace makes more sense. There could be several exceptions. For example, all of the reference tables/indexes were in a single tablespace. Since these were fairly small and very static, we had a more classic approach to sizing (still only 3 extent sizes). LMTs are of 2 varieties Uniform size and Autoallocate. Uniform means 1 and only 1 extent size. Autoallocate has 4 extent sizes (Oracle documents only 3). I have used autoallocate when the rough size was not known or we were mixing several general sizes. Uniform would be good for known partitions, small reference tables, etc. So, if this is the goldilocks approach...who are the 3 bears? -Original Message- Sent: Tuesday, September 03, 2002 12:49 PM To: Multiple recipients of list ORACLE-L Dan, Why 3 extent sizes in each tablespace? I can see how you prevented the dreaded fragmentation problem by making them multiples of one another but I don't understand why you did that. If I'm using LMTs can I still do that? I haven't really used them, I'd thought to not specify the initial and next extent sizes and just let the LMT deal with it. As for the Goldilocks name, you are more than welcome to steal, uh, borrow, it :) Rachel --- Fink, Dan [EMAIL PROTECTED] wrote: We used this method (I wish I had come up with the Goldilocks code name...) in 8.0.4 on a couple of data warehouses. The bottom line is that we had 1 space related failure (application code filled up the error log) in over 9 months of operation. Which so impressed management that they decided to cut the support budget (not enough outages/calls to help desk). There were over 150 tablespaces per database so we could perform I/O balancing, object segregation, etc. We did not follow the SAFE (3 and only 3 extent sizes in the database). Rather we used 3 extent sizes per tablespace with medium a multiple of small and large a multiple of medium. PCTFREE - set to 10 (no updates, but provided a little space just in case this changed) PCTUSED - set to 80 (same reason as above) FREELIST - set to 5 (originally designed so only 2 processes would ever concurrently insert data) Sounds like you are on the right track. -Original Message- Sent: Tuesday, September 03, 2002 9:49 AM To: Multiple recipients of list ORACLE-L time for me to ask the experts again. My data warehouse will be 9.2, with all locally managed tablespaces. We will be following what I have taken to calling the Goldilocks principle -- that of small, medium and large tablespace extent sizes, with variations in that we will separate indexes and data, and will have even more separation for our fact tables into partitioned tables and tablespaces. However, now comes the time for me to work out storage clauses. And a quick read through the docs leaves me wondering if I should just turn on automatic segment-space management and not worry about setting PCTFREE, PCTUSED and FREELIST parameters. I can't find any real information or bugs on MetaLink either. Does anyone have any experience, good OR bad, with using this feature? If you are doing data warehouse work, what are good values for the parameters if I DO use them? One fact table is likely to be highly updated (customer info) as we collect more and more specific information from customers. The rest will be, as you would expect from a DW, mostly inserts. Help? Thanks! Rachel __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fink, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX:
RE: automatic segment space management
Rich - Good point. Yes, I create all the tables here, at least in production, and I probably wouldn't use autoextend if the situation were otherwise. The other thing to consider is if you are using uniform extents, by definition you have bought into the philosophy that you can have many extents and your database will not do a Linda Blair Exorcist imitation on you. If we use the guideline that the number of extents should be not many more than 1,000, then the 128K extent will get you 128M, which is good for most tables. While we are on the subject, anyone considering switching to LMTs should carefully read How to Stop Defragmenting and Start Living by Juan Loaiza, Rosanne Toohey, Bhaskar Himatsingka. You can find a copy on http://www.hotsos.com. Trying to implement a philosophy without fully understanding it is a recipe for failure. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 03, 2002 12:58 PM To: Multiple recipients of list ORACLE-L Dennis, who creates tables in your DB? If devs do, don't you worry that one could accidentally create a 512MB table in your 128K TS, instead of a 512KB one? I really would like to implement LMTs here, and am doing so in certain restricted instances where I'm the only one who creates the objects for that TS, but I'm a little leery of letting it go to the developers, even though I don't use AUTOEXTEND. Thx! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 03, 2002 12:42 PM To: Multiple recipients of list ORACLE-L Subject: RE: automatic segment space management Rachel - I have been using LMTs with uniform extents (Oracle-recommended variation) for a couple of years now with Oracle 8.1.6, and now 9.2. The Oracle-recommended extent sizes are 128k, 4m, and 128m. As far as PCTFREE, et. al., these are at the table level, so my opinion would be that the guidelines for these are unchanged from the pre-LMT days. The key issue with the highly updated customer table would be whether the size of the row is changing. If you can keep the row size constant, then you won't wind up with chained rows. The biggest issue facing you is whether you turn AUTOEXTEND on. I did that and have encountered relatively few problems. Well, one problem. I had tables set with large NEXT extents to minimize extents, and when one extended boy did my sys admin get excited. I changed that. A bigger issue in building your data warehouse is whether you can use the partitioning option. Most of our queries were taking more than 2 minutes and I was able to partition and bring that down below 10 seconds. The users were pretty excited. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: automatic segment space management
The question posed was not whether extent management local should be used, but whether automatic segment space management should be used. As this is a data warehouse, I would not expect you to have transactions trying to change the same block. Assuming you are loading; that is, inserting data and not doing updates, wouldn't you try to cram as much data as possible into a block? Seems this could be done more easily by controlling these parameters yourself I've got one system using automatic segment space management without any problems, however when I posed the same question on using it a few months ago, the respone which trickled in way highly negative concerning its usage. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 03, 2002 8:49 AM To: Multiple recipients of list ORACLE-L time for me to ask the experts again. My data warehouse will be 9.2, with all locally managed tablespaces. We will be following what I have taken to calling the Goldilocks principle -- that of small, medium and large tablespace extent sizes, with variations in that we will separate indexes and data, and will have even more separation for our fact tables into partitioned tables and tablespaces. However, now comes the time for me to work out storage clauses. And a quick read through the docs leaves me wondering if I should just turn on automatic segment-space management and not worry about setting PCTFREE, PCTUSED and FREELIST parameters. I can't find any real information or bugs on MetaLink either. Does anyone have any experience, good OR bad, with using this feature? If you are doing data warehouse work, what are good values for the parameters if I DO use them? One fact table is likely to be highly updated (customer info) as we collect more and more specific information from customers. The rest will be, as you would expect from a DW, mostly inserts. Help? Thanks! Rachel __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: automatic segment space management
my fault, the customer table is a dimension table, not a fact table. Doh! --- DENNIS WILLIAMS [EMAIL PROTECTED] wrote: Rachel The varchar business on your fact table worries me. I'm not trying to be critical, but to bring up some issues you may want to consider before you begin the big load. Unfortunately too many DWs end up with a flawed data model (ours included) that limit its usability. Sometimes the fortunate sites are the ones that get it so wrong it must be blasted away and recreated. Guess who gets to bear the brunt of that hasty learning exercise?? Yep, that's right. Here are my thoughts for what they are worth. 1. Normally the fact table is very large, so it is critical that it be designed as nearly right as possible. Especially with what is called the granularity, the lowest level of data that is stored. You can always aggregate up, never down. Daily data can be summed to weekly, but we can't take weekly data and figure out the daily amounts. 2. When you say the customers fill in the blanks, that worries me. That doesn't sound so much like a DW as an OLTP. Where is the history dimension? 3. The fact table usually is so large that the information only makes sense in aggregate. You aren't looking for the particular blue-eyed 23-year old female from Des Moines, but trying to find HOW MANY blue-eyed, etc. This means that you won't be scratching around with VARCHAR2 fields with query operators such as LIKE. Performance would be really BAD. 4. I don't know your application, just the minor details you've mentioned in passing, but consider something like this. The FACT table logs each new information that a user provides. VARCHAR2 fields. Never UPDATEd, just add a new record along with the date that record was added. From that we create an aggregate table CURRFACT. One row per customer. Weekly we scan the new records added to FACT, pulling new facts, updated facts into CURRFACT. Most of the fields in CURRFACT are single character flag fields. Bit map index the heck (sorry, but we have a fierce naughty word scanner) out of CURRFACT. Performance is awesome. Queries return before the users hit enter (just kidding). From to time marketing recognizes some relevant fact that isn't in CURRFACT. You add a new column to CURRFACT and start a really big query on FACT when you leave for the weekend that will populate the new column. There may be a few fields like address that you populate in CURRFACT just for convenience. But you don't search them. Also, if you ever need the history of how your customers have moved around, you have that data. And remember, a DW is all about history, never about current information. The modeling issues have a lot more about the performance and usability of the DW than the choices we have as DBAs such as LMT. Okay, I'll quit prattling on here. You probably didn't even get a say in the data model. They never ask the DBA. But if you raise the issues beforehand it'll amuse you more when they come back and ask you to redo everything. Hey, I just noticed that you won't be the production DBA on this! No worries! Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 03, 2002 1:44 PM To: Multiple recipients of list ORACLE-L We may end up reworking the extent sizes, right now they are smaller than those but we are still in stage one, haven't gone live yet (and I don't even want to think about what a pain it will be to change things when we do go live). Data load test coming up soon, so I'll have a better idea of what I need to change things to, if I need to change them. There is no way the customer row will remain the same size unless I change all the varchar fields to char. We have VERY sparse data as yet and expect to be able to entice customers to fill in the blanks which will cause rows to grow. We are allocating WAY more space than we need at the moment and will be closely monitoring growth (once a day data loads) and I can always turn on autoextend if I need it. But then again, I am not the DBA who will be responsible for the production site, at least not for any space issues on the production DW. Rachel --- DENNIS WILLIAMS [EMAIL PROTECTED] wrote: Rachel - I have been using LMTs with uniform extents (Oracle-recommended variation) for a couple of years now with Oracle 8.1.6, and now 9.2. The Oracle-recommended extent sizes are 128k, 4m, and 128m. As far as PCTFREE, et. al., these are at the table level, so my opinion would be that the guidelines for these are unchanged from the pre-LMT days. The key issue with the highly updated customer table would be whether the size of the row is changing. If you can keep the row size constant, then you won't wind up with chained rows. The biggest issue facing you is whether you turn AUTOEXTEND on. I did that and have
RE: automatic segment space management
Thanks Ian, that was indeed the question, although the other information is useful as well. if I understand what you are saying correctly, it works but it won't really buy me anything and I might do better controlling the space myself. And while you have had no problems, you've heard negative things about it. I can't afford to have this database not be available so I'll manage them on my own Rachel --- MacGregor, Ian A. [EMAIL PROTECTED] wrote: The question posed was not whether extent management local should be used, but whether automatic segment space management should be used. As this is a data warehouse, I would not expect you to have transactions trying to change the same block. Assuming you are loading; that is, inserting data and not doing updates, wouldn't you try to cram as much data as possible into a block? Seems this could be done more easily by controlling these parameters yourself I've got one system using automatic segment space management without any problems, however when I posed the same question on using it a few months ago, the respone which trickled in way highly negative concerning its usage. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 03, 2002 8:49 AM To: Multiple recipients of list ORACLE-L time for me to ask the experts again. My data warehouse will be 9.2, with all locally managed tablespaces. We will be following what I have taken to calling the Goldilocks principle -- that of small, medium and large tablespace extent sizes, with variations in that we will separate indexes and data, and will have even more separation for our fact tables into partitioned tables and tablespaces. However, now comes the time for me to work out storage clauses. And a quick read through the docs leaves me wondering if I should just turn on automatic segment-space management and not worry about setting PCTFREE, PCTUSED and FREELIST parameters. I can't find any real information or bugs on MetaLink either. Does anyone have any experience, good OR bad, with using this feature? If you are doing data warehouse work, what are good values for the parameters if I DO use them? One fact table is likely to be highly updated (customer info) as we collect more and more specific information from customers. The rest will be, as you would expect from a DW, mostly inserts. Help? Thanks! Rachel __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: automatic segment space management
So, proper LMT means no LBE? ;) Great analogy! All the head-spinning and the green projectile vomiting and such... BTW, yes that is a good paper. I've read it and am trying to deal with the extent sizes as it applies to our DB, as only about two dozen of the 800+ tables are larger than 128MB and none are larger than 4GB. So, either I consider making the Large LMTs smaller, or maybe our li'l 25GB DB doesn't need a Papa Bear. Or maybe I just need to get used to the idea of having more than a couple hundred extents... :) Thanks! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 03, 2002 3:04 PM To: Multiple recipients of list ORACLE-L Subject: RE: automatic segment space management Rich - Good point. Yes, I create all the tables here, at least in production, and I probably wouldn't use autoextend if the situation were otherwise. The other thing to consider is if you are using uniform extents, by definition you have bought into the philosophy that you can have many extents and your database will not do a Linda Blair Exorcist imitation on you. If we use the guideline that the number of extents should be not many more than 1,000, then the 128K extent will get you 128M, which is good for most tables. While we are on the subject, anyone considering switching to LMTs should carefully read How to Stop Defragmenting and Start Living by Juan Loaiza, Rosanne Toohey, Bhaskar Himatsingka. You can find a copy on http://www.hotsos.com. Trying to implement a philosophy without fully understanding it is a recipe for failure. Dennis Williams -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: automatic segment space management
Exactly. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 03, 2002 2:23 PM To: Multiple recipients of list ORACLE-L Thanks Ian, that was indeed the question, although the other information is useful as well. if I understand what you are saying correctly, it works but it won't really buy me anything and I might do better controlling the space myself. And while you have had no problems, you've heard negative things about it. I can't afford to have this database not be available so I'll manage them on my own Rachel --- MacGregor, Ian A. [EMAIL PROTECTED] wrote: The question posed was not whether extent management local should be used, but whether automatic segment space management should be used. As this is a data warehouse, I would not expect you to have transactions trying to change the same block. Assuming you are loading; that is, inserting data and not doing updates, wouldn't you try to cram as much data as possible into a block? Seems this could be done more easily by controlling these parameters yourself I've got one system using automatic segment space management without any problems, however when I posed the same question on using it a few months ago, the respone which trickled in way highly negative concerning its usage. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 03, 2002 8:49 AM To: Multiple recipients of list ORACLE-L time for me to ask the experts again. My data warehouse will be 9.2, with all locally managed tablespaces. We will be following what I have taken to calling the Goldilocks principle -- that of small, medium and large tablespace extent sizes, with variations in that we will separate indexes and data, and will have even more separation for our fact tables into partitioned tables and tablespaces. However, now comes the time for me to work out storage clauses. And a quick read through the docs leaves me wondering if I should just turn on automatic segment-space management and not worry about setting PCTFREE, PCTUSED and FREELIST parameters. I can't find any real information or bugs on MetaLink either. Does anyone have any experience, good OR bad, with using this feature? If you are doing data warehouse work, what are good values for the parameters if I DO use them? One fact table is likely to be highly updated (customer info) as we collect more and more specific information from customers. The rest will be, as you would expect from a DW, mostly inserts. Help? Thanks! Rachel __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ:
RE: automatic segment space management
While Oracle says you can have unlimited extents, practically it doesn't hand more than at MOST between 1000 and 4000 extents in a table. And you can just adjust your baby bear, mama bear and papa bear extent sizes in all your tablespaces, no one says the extent sizes are fixed! Geez, years from now, when someone talks about the Goldilocks method of extent management I'll be able to say I started it all. then I'll start running for my life. :) Rachel --- Jesse, Rich [EMAIL PROTECTED] wrote: So, proper LMT means no LBE? ;) Great analogy! All the head-spinning and the green projectile vomiting and such... BTW, yes that is a good paper. I've read it and am trying to deal with the extent sizes as it applies to our DB, as only about two dozen of the 800+ tables are larger than 128MB and none are larger than 4GB. So, either I consider making the Large LMTs smaller, or maybe our li'l 25GB DB doesn't need a Papa Bear. Or maybe I just need to get used to the idea of having more than a couple hundred extents... :) Thanks! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 03, 2002 3:04 PM To: Multiple recipients of list ORACLE-L Subject: RE: automatic segment space management Rich - Good point. Yes, I create all the tables here, at least in production, and I probably wouldn't use autoextend if the situation were otherwise. The other thing to consider is if you are using uniform extents, by definition you have bought into the philosophy that you can have many extents and your database will not do a Linda Blair Exorcist imitation on you. If we use the guideline that the number of extents should be not many more than 1,000, then the 128K extent will get you 128M, which is good for most tables. While we are on the subject, anyone considering switching to LMTs should carefully read How to Stop Defragmenting and Start Living by Juan Loaiza, Rosanne Toohey, Bhaskar Himatsingka. You can find a copy on http://www.hotsos.com. Trying to implement a philosophy without fully understanding it is a recipe for failure. Dennis Williams -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: automatic segment space management
One note: 9i automatic segment space management does not automate PCTFREE; that still functions as before. It does cause PCTUSED, FREELISTS, and FREELIST GROUPS to be ignored, however... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, September 03, 2002 9:48 AM time for me to ask the experts again. My data warehouse will be 9.2, with all locally managed tablespaces. We will be following what I have taken to calling the Goldilocks principle -- that of small, medium and large tablespace extent sizes, with variations in that we will separate indexes and data, and will have even more separation for our fact tables into partitioned tables and tablespaces. However, now comes the time for me to work out storage clauses. And a quick read through the docs leaves me wondering if I should just turn on automatic segment-space management and not worry about setting PCTFREE, PCTUSED and FREELIST parameters. I can't find any real information or bugs on MetaLink either. Does anyone have any experience, good OR bad, with using this feature? If you are doing data warehouse work, what are good values for the parameters if I DO use them? One fact table is likely to be highly updated (customer info) as we collect more and more specific information from customers. The rest will be, as you would expect from a DW, mostly inserts. Help? Thanks! Rachel __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
