Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Can I combine TEMP and RBS tablespace?

Re: Can I combine TEMP and RBS tablespace?

From: Howard J. Rogers <dba_at_hjrdba.com>
Date: Tue, 16 Jul 2002 06:17:18 +1000
Message-ID: <agvaj5$ljb$1@lust.ihug.co.nz>


"Edzard" <edzard_at_volcanomail.com> wrote in message news:5d75e934.0207151039.59ba72a8_at_posting.google.com...
> Hello,
>
> I'm working with a Oracle 7.3.4.5 database on Unix. The total database
> size is about 8G. There are 40 concurrent users on average. A
> relatively large part of the storage capacity is taken by the
> auxillary tablepaces TEMP and RBS. The temporary tablespace is 1G.
> This is only rarely used for a single report. The tablespace for
> rollback segments is also 1G, also rarely used, when populating a
> large index-organized table.
>
> To make thing easy, disk storage is allocated in parts of 512M (raw
> logical volumes). Now I run out of free raw logical volumes. I need
> one to extend the data tablespace. I might create a new one but there
> is a limitation of the backup capacity and I'd prefer to stick to the
> existing raw logical volumes.
>
> So it occured to me that a lot of space is wasted with the TEMP and
> RBS tablespaces and I wondered if I could not combine temporary and
> rollback segments in a single tablespace. I figure that the combined
> tablespace could do with three 512M data files, that is one less than
> the two seperate tablespaces do have now.
>
> Has anyone a opinion about this plan?
>

Yup. It's horrible.

Firstly, your TEMP tablespace, even in Oracle 7.3, should have been created with a 'create tablespace TEMP datafile 'xxxx' TEMPORARY' command. That last little word is important: it means that *only* temporary segments can be created within it, which rules out even trying to put permanent segments such as rollbacks in there.

You can of course use permanent tablespace as TEMP, but that's a performance disaster, and your tablespace will fragment like crazy, too.

Secondly, whilst there are no performance gains per se to be had by separating tables from their indexes, there are definitely performance woes to be accumulated if you combine TEMP and rollback segments -the two biggest I/O intensive parts of any Oracle database. The I/O clashes will be painful. Every transaction generates rollback, and most select statements will depend on it for read consistency reasons, too. Quite a few select statements will do sorts, either in memory or swapping down to TEMP if it can't fit. Combining them just doesn't bear thinking about.

If you've got a large sort_area_size so that TEMP is not being used much, make it smaller. Likewise, if you genuinely don't use 1Gb of rollback space, make it smaller. But whatever you do, don't combine them.

> The script to create the new combined rollback/temporary tablespace is
> below (not tested yet). To prevent fragmentation I chooses a uniform
> extent size for both types of segments.

Which is probably bad news. The extent sizes for temporary segments need to be a multiple of your sort_area_size plus one block. The extent sizes for your rollback segments probably ought to be something else - a common multiple of 5 blocks and your multiblock_read_count.

>My main worry is that the
> temporary segments behave different when created in a PERMANENT type
> of tablespace.

True enough: extents have to be allocated and de-allocated per sort, rather than allocated and re-used. That's a lot of old work for your data dictionary tables, and mean what sorts you do that hit TEMP will run slower. Why do you think Oracle invented proper temporary tablespace in the first place?

But that oughtn't to be your main concern. The main concern is the I/O competition that you will induce.

Then there's the small matter of a rollback segment ballooning in size because of a blocking transaction and preventing people running big reports because there's not enough space for the temporary segments.

It just doesn't bear thinking about.

Regards
HJR
> Thanks,
> Edzard Pasma
>
>
> CREATE TABLESPACE rbt
> DATAFILE '/u11/oracle/p1/oradata/rbt01.ln' SIZE 524272K REUSE
> DEFAULT STORAGE (INITIAL 4M NEXT 4M PCTINCREASE 0)
> MAXEXTENTS 256;
>
> ALTER TABLESPACE rbt
> ADD DATAFILE '/u11/oracle/p1/oradata/rbt02.ln' SIZE 524272K REUSE;
> ALTER TABLESPACE rbt
> ADD DATAFILE '/u11/oracle/p1/oradata/rbt03.ln' SIZE 524272K REUSE;
>
> CREATE PUBLIC ROLLBACK SEGMENT rbs001 TABLESPACE rbt STORAGE (OPTIMAL
> 12M );
> CREATE PUBLIC ROLLBACK SEGMENT rbs002 TABLESPACE rbt STORAGE (OPTIMAL
> 12M );
> CREATE PUBLIC ROLLBACK SEGMENT rbs003 TABLESPACE rbt STORAGE (OPTIMAL
> 12M );
> CREATE PUBLIC ROLLBACK SEGMENT rbs004 TABLESPACE rbt STORAGE (OPTIMAL
> 12M );
> CREATE PUBLIC ROLLBACK SEGMENT rbs005 TABLESPACE rbt STORAGE (OPTIMAL
> 12M );
> CREATE PUBLIC ROLLBACK SEGMENT rbs006 TABLESPACE rbt STORAGE (OPTIMAL
> 12M );
> CREATE PUBLIC ROLLBACK SEGMENT rbs007 TABLESPACE rbt STORAGE (OPTIMAL
> 12M );
> CREATE PUBLIC ROLLBACK SEGMENT rbs008 TABLESPACE rbt STORAGE (OPTIMAL
> 12M );
> CREATE PUBLIC ROLLBACK SEGMENT rbs009 TABLESPACE rbt STORAGE (OPTIMAL
> 12M );
> CREATE PUBLIC ROLLBACK SEGMENT rbs010 TABLESPACE rbt STORAGE (OPTIMAL
> 12M );
> CREATE PUBLIC ROLLBACK SEGMENT rbs011 TABLESPACE rbt STORAGE (OPTIMAL
> 12M );
> CREATE PUBLIC ROLLBACK SEGMENT rbs012 TABLESPACE rbt STORAGE (OPTIMAL
> 12M );
> CREATE PUBLIC ROLLBACK SEGMENT rbs013 TABLESPACE rbt STORAGE (OPTIMAL
> 12M );
> CREATE PUBLIC ROLLBACK SEGMENT rbs014 TABLESPACE rbt STORAGE (OPTIMAL
> 12M );
> CREATE PUBLIC ROLLBACK SEGMENT rbs015 TABLESPACE rbt STORAGE (OPTIMAL
> 12M );
> CREATE PUBLIC ROLLBACK SEGMENT rbs016 TABLESPACE rbt STORAGE (OPTIMAL
> 12M );
>
> ALTER ROLLBACK SEGMENT rbs01 ONLINE;
> ALTER ROLLBACK SEGMENT rbs02 ONLINE;
> ALTER ROLLBACK SEGMENT rbs03 ONLINE;
> ALTER ROLLBACK SEGMENT rbs04 ONLINE;
> ALTER ROLLBACK SEGMENT rbs05 ONLINE;
> ALTER ROLLBACK SEGMENT rbs06 ONLINE;
> ALTER ROLLBACK SEGMENT rbs07 ONLINE;
> ALTER ROLLBACK SEGMENT rbs08 ONLINE;
> ALTER ROLLBACK SEGMENT rbs09 ONLINE;
> ALTER ROLLBACK SEGMENT rbs10 ONLINE;
> ALTER ROLLBACK SEGMENT rbs11 ONLINE;
> ALTER ROLLBACK SEGMENT rbs12 ONLINE;
> ALTER ROLLBACK SEGMENT rbs13 ONLINE;
> ALTER ROLLBACK SEGMENT rbs14 ONLINE;
> ALTER ROLLBACK SEGMENT rbs15 ONLINE;
> ALTER ROLLBACK SEGMENT rbs16 ONLINE;
>
> ALTER USER sys TEMPORARY TABLESPACE rbt;
> ALTER USER system TEMPORARY TABLESPACE rbt;
> ALTER USER rapp TEMPORARY TABLESPACE rbt;
> ALTER USER raph TEMPORARY TABLESPACE rbt;
>
> SPOOL OFF
Received on Mon Jul 15 2002 - 15:17:18 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US