topic Re: Locally Managed Tablespaces in Operating System - HP-UX

Locally Managed Tablespaces

ericfjchen — Tue, 23 Nov 2004 20:34:32 GMT

1. When using locally managed tablespaces, the default allocation type is system. Can we know the extent size?

2. What benifit does all extents have the same size in a locally managed tablespace?

Thanks

Eric

Re: Locally Managed Tablespaces

twang — Tue, 23 Nov 2004 21:07:05 GMT

1.
select TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, MIN_EXTLEN, EXTENT_MANAGEMENT, ALLOCATION_TYPE, PLUGGED_IN from dba_tablespaces where tablespace_name like 'LOCAL%';
2. Better space management due to:
- uniform extent sizes;
- reduced data dictionary access.
Reduced tablespace fragmentation.
Better management of temporary space.

Re: Locally Managed Tablespaces

ericfjchen — Tue, 23 Nov 2004 21:33:13 GMT

select TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, MIN_EXTLEN, EXTENT_MANAGEMENT, ALLOCATION_TYPE, PLUGGED_IN
from dba_tablespaces
where EXTENT_MANAGEMENT ='LOCAL'
and ALLOCATION_TYPE='SYSTEM'

===============
The cloumn "NEXT_EXTENT" is NULL.....

Re: Locally Managed Tablespaces

Michael Schulte zur Sur — Tue, 23 Nov 2004 22:11:50 GMT

Hi,

NEXT_EXTENT would be the same as INITIAL_EXTENT.

greetings,

Michael

Re: Locally Managed Tablespaces

ericfjchen — Tue, 23 Nov 2004 22:28:11 GMT

Can we define the initial extent size when creating a locally managed tablespace?

Re: Locally Managed Tablespaces

Jean-Luc Oudart — Wed, 24 Nov 2004 04:14:02 GMT

Hi Eric

we use Uniform extent for our tablespaces.
We have different tablespaces for DATA and Indexes and also group by average size of objects (SMALL,MEDIUM,LARGE).

As mentioned you save cpu and IO vs dictionary managed tablespaces and remove fragmentation.

example :
create tablespace SMALL_DATA datafile ''
SIZE 100M
extent management local uniform size 128K;

Regards
Jean-Luc

Re: Locally Managed Tablespaces

Michael Schulte zur Sur — Wed, 24 Nov 2004 08:07:10 GMT

Eric,

initial extents are used when creating a table. You can specify the extent size for the tablespace and there is no initial and next there. See attachment.

greetings,

Michael

Re: Locally Managed Tablespaces

Volker Borowski — Wed, 24 Nov 2004 15:15:03 GMT

Hi,

the idea is to reduce space management.
With a uniform extentsize, it does not matter at all, if your tablespace is extent-fragmented, because all newly extents to be allocated will fit into any hole that a dropped object has left.

Volker

Re: Locally Managed Tablespaces

ericfjchen — Wed, 24 Nov 2004 20:03:02 GMT

1. If you choose locally managed tablespaces,
which one (auotallocate & uniform) do you prefer? Why?
2. If you choose locally managed tablespaces & uniform, how to determine a better extent size?

Thanks

Eric

Re: Locally Managed Tablespaces

twang — Wed, 24 Nov 2004 22:25:40 GMT

UNIFORM size should be the Initial_Extent of DBA_TABLESPACES.
In my view it should be 5, 10, or 20 times the block size and not 8 times the block size(oracle recommend).

Re: Locally Managed Tablespaces

Yogeeraj_1 — Thu, 25 Nov 2004 01:05:32 GMT

hi,

question 1.
I prefer pctincrease = 0, initial=next. A locally managed tablespace with uniform extents makes that the "de-facto" rule, you cannot subvert it as you can with a dictionary managed tables. It totally avoids fragmentation -- it is impossible to have fragmentation in these tablespaces.

Also note that, Locally managed tablespaces are much faster at allocating and de-allocating extents - many order of magnitudes faster.

Remember that, Locally managed tablespaces decrease contention on the data dictionary.

question 2.
I put them in to "small", "medium" and "large" tablespaces based on my guess as to how big or small they will end up.

I use the following:
------------------------------------------------------------
Uniform Size Autoextend Striped
Extent Maxsize over disks
------------------------------------------------------------
tbs_lmt_small 64K 10M 50M all disks
tbs_lmt_medium 1024K 100M 1024M all disks
tbs_lmt_large 10M 1024M 2048M all disks

You may have many combinations of those!

hope this helps!

regards
Yogeeraj