Re: rbs' maxextents in LMT
Hi, Arup: I created lmt rbs this way: CREATE TABLESPACE RBS DATAFILE '/oracle/u02/oradata/YPD/rbs01.dbf' SIZE 2048M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M; and then SQL create rollback segment RBSTest1 storage(initial 2048K next 2048K) tablespace rbs; Rollback segment created. SQL create rollback segment RBSTest2 storage(initial 2048K next 2048K MAXEXTENTS 300) tablespace rbs; Rollback segment created. SQL select SEGMENT_NAME,INITIAL_EXTENT,NEXT_EXTENT, 2 MIN_EXTENTS,MAX_EXTENTS 3 from dba_rollback_segs 4 where SEGMENT_NAME like '%TEST%'; SEGMENT_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS -- -- --- --- MAX_EXTENTS --- RBSTEST1 4194304 2097152 1 32765 RBSTEST2 4194304 2097152 1 32765 It shows that you can not set MAXEXTENTS of a rollback segment when it is created in LMT. What I mean the run away transaction is a transaction that keep using rollback segment until it uses up all it's extents. In DMT case, we can set the MAXEXTENTS of all the rollback segments so that there is no transaction that can use the whole tablespace. But in LMT, it seems a run away transaction can eat up the whole rbs tablespace because there is no MAXEXTENTS ( ie, MAXEXTENTS = unlimited). Does anyone know there is somewhere in Oracle Doc that I can find the answer of my question? Thanks. Guang Date: Mon, 06 Jan 2003 18:10:08 -0500 Guang, You should use LMTs with UNFORM extent allocation of some size So create the tablespaces and the rollback segments but not the INITIAL or NEXT. I am not sure what you meamn by runaway processes. If a transaction needs rollback segment space, it will need to extend it. You can still specify MAXEXTENTS to limit the number of extents. HTH Arup _ Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei 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: rbs' maxextents in LMT
Guang, Which version are you using? I tested this on a 8.1.7.4 system and it works fine. The only difference is, I didn't supply the INITIAL and NEXT; they are unnecessary anyway. create rollback segment arup2 storage(maxextents 4); select max_extents from dba_rollback_segs where segment_name = 'ARUP2' returns 4, as expected! Anyway, the other issue is about your decision to limit extension of rollback segments to contain what you term as runaway transaction. A transaction does not own an rbs, rather an rbs contains several transactions. When a txn changes data, it places the pre-image in the rbs and if there is no space, then the rbs grows. Now, a long transaction may be killed since a rbs space was not found, but it can also happen to a small, legitimate txn that needs to store the pre-image, simply because the long txn has grown the rbs to the maxextents. So, how did it help? It stopped a desired txn. Another problem is the read consistency. Not just transactions, but even selects also need to read data from RBS. If a rollback segment cannot grow, oracle determines if there is a way it can get the RBS to be used again. If there is no active transaction, then the old space is reused; but if a long running query needs that old data, i.e. pre-image, it doesn't find it and you get the dreaded ORA-1555 Snapshot too old error. The likelihood increases if your RBS can't grow. So, that was my concern for artificially limiting the RBS extension. If you need to hal t abnormlly long transactions, use resource managaers, but not using MAXEXTENTS. Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 07, 2003 2:08 PM Hi, Arup: I created lmt rbs this way: CREATE TABLESPACE RBS DATAFILE '/oracle/u02/oradata/YPD/rbs01.dbf' SIZE 2048M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M; and then SQL create rollback segment RBSTest1 storage(initial 2048K next 2048K) tablespace rbs; Rollback segment created. SQL create rollback segment RBSTest2 storage(initial 2048K next 2048K MAXEXTENTS 300) tablespace rbs; Rollback segment created. SQL select SEGMENT_NAME,INITIAL_EXTENT,NEXT_EXTENT, 2 MIN_EXTENTS,MAX_EXTENTS 3 from dba_rollback_segs 4 where SEGMENT_NAME like '%TEST%'; SEGMENT_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS -- -- --- --- MAX_EXTENTS --- RBSTEST1 4194304 2097152 1 32765 RBSTEST2 4194304 2097152 1 32765 It shows that you can not set MAXEXTENTS of a rollback segment when it is created in LMT. What I mean the run away transaction is a transaction that keep using rollback segment until it uses up all it's extents. In DMT case, we can set the MAXEXTENTS of all the rollback segments so that there is no transaction that can use the whole tablespace. But in LMT, it seems a run away transaction can eat up the whole rbs tablespace because there is no MAXEXTENTS ( ie, MAXEXTENTS = unlimited). Does anyone know there is somewhere in Oracle Doc that I can find the answer of my question? Thanks. Guang Date: Mon, 06 Jan 2003 18:10:08 -0500 Guang, You should use LMTs with UNFORM extent allocation of some size So create the tablespaces and the rollback segments but not the INITIAL or NEXT. I am not sure what you meamn by runaway processes. If a transaction needs rollback segment space, it will need to extend it. You can still specify MAXEXTENTS to limit the number of extents. HTH Arup _ Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei 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
Re: rbs' maxextents in LMT
Guang, It can eat up only the segment it is assigned to, not the whole rbs tablespace, you will still have other segments. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 07, 2003 2:08 PM Hi, Arup: I created lmt rbs this way: CREATE TABLESPACE RBS DATAFILE '/oracle/u02/oradata/YPD/rbs01.dbf' SIZE 2048M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M; and then SQL create rollback segment RBSTest1 storage(initial 2048K next 2048K) tablespace rbs; Rollback segment created. SQL create rollback segment RBSTest2 storage(initial 2048K next 2048K MAXEXTENTS 300) tablespace rbs; Rollback segment created. SQL select SEGMENT_NAME,INITIAL_EXTENT,NEXT_EXTENT, 2 MIN_EXTENTS,MAX_EXTENTS 3 from dba_rollback_segs 4 where SEGMENT_NAME like '%TEST%'; SEGMENT_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS -- -- --- --- MAX_EXTENTS --- RBSTEST1 4194304 2097152 1 32765 RBSTEST2 4194304 2097152 1 32765 It shows that you can not set MAXEXTENTS of a rollback segment when it is created in LMT. What I mean the run away transaction is a transaction that keep using rollback segment until it uses up all it's extents. In DMT case, we can set the MAXEXTENTS of all the rollback segments so that there is no transaction that can use the whole tablespace. But in LMT, it seems a run away transaction can eat up the whole rbs tablespace because there is no MAXEXTENTS ( ie, MAXEXTENTS = unlimited). Does anyone know there is somewhere in Oracle Doc that I can find the answer of my question? Thanks. Guang Date: Mon, 06 Jan 2003 18:10:08 -0500 Guang, You should use LMTs with UNFORM extent allocation of some size So create the tablespaces and the rollback segments but not the INITIAL or NEXT. I am not sure what you meamn by runaway processes. If a transaction needs rollback segment space, it will need to extend it. You can still specify MAXEXTENTS to limit the number of extents. HTH Arup _ Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei 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: Igor Neyman 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: rbs' maxextents in LMT
Hi, the trick is to use package dbms_space_admin (Cf Oracle Doc) to convert from DMT to LMT. With this method, you obtain a USER allocation type as seen in dba_tablespaces view that allow you to use maxextents clause. N.B. to ease the conversion, it's better to have DMT tablespaces already using minimum_extent clause Hope this helps At 13:33 06/01/2003 -0800, you wrote: Hi: Oracle 8173 on Sun Solaris 2.8. I am in the process of converting rollback segments from DMT to LMT (to see how much performance we can get, if any). The tablespace size is 8G and I used to create rbs like: create rollback segment RBS00X storage(initial 2048K next 2048K minextents 20 maxextents 800) tablespace rbs; I set maxextents to avoid run away transaction. But now with rbs in LMT, there is no way to set maxextents, how can we avoid run away transaction in this situation? Thanks. Guang Mei _ Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei 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). Gilles Parc carpe diem !! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gilles PARC 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: rbs' maxextents in LMT
Guang, You should use LMTs with UNFORM extent allocation of some size So create the tablespaces and the rollback segments but not the INITIAL or NEXT. I am not sure what you meamn by runaway processes. If a transaction needs rollback segment space, it will need to extend it. You can still specify MAXEXTENTS to limit the number of extents. HTH Arup From: Guang Mei [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: rbs' maxextents in LMT Date: Mon, 06 Jan 2003 13:33:53 -0800 MIME-Version: 1.0 Received: from newsfeed.cts.com ([209.68.248.164]) by mc3-f32.law16.hotmail.com with Microsoft SMTPSVC(5.0.2195.5600); Mon, 6 Jan 2003 14:06:48 -0800 Received: from fatcity.UUCP (uucp@localhost)by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id OAA03860;Mon, 6 Jan 2003 14:05:48 -0800 (PST) Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 00527F5F; Mon, 06 Jan 2003 13:33:53 -0800 Message-ID: [EMAIL PROTECTED] X-Comment: Oracle RDBMS Community Forum X-Sender: Guang Mei [EMAIL PROTECTED] Sender: [EMAIL PROTECTED] Errors-To: [EMAIL PROTECTED] Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Return-Path: [EMAIL PROTECTED] X-OriginalArrivalTime: 06 Jan 2003 22:06:48.0984 (UTC) FILETIME=[E8E2F180:01C2B5CF] Hi: Oracle 8173 on Sun Solaris 2.8. I am in the process of converting rollback segments from DMT to LMT (to see how much performance we can get, if any). The tablespace size is 8G and I used to create rbs like: create rollback segment RBS00X storage(initial 2048K next 2048K minextents 20 maxextents 800) tablespace rbs; I set maxextents to avoid run away transaction. But now with rbs in LMT, there is no way to set maxextents, how can we avoid run away transaction in this situation? Thanks. Guang Mei _ Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei 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). _ Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail -- 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).
