Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Roshan.

Asked: October 07, 2018 - 4:20 pm UTC

Last updated: October 13, 2018 - 4:32 am UTC

Version: 12.2.0

Viewed 1000+ times

You Asked

Hello Team,

my question is a bit similar to https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:228413960506 but I will need further clarification.
I am loading data on monthly partitioned tables from flat files using sqlloader everyday. We will keep the data on each table for 18 months. Below is the values of size of data loaded calculated using select sum(vsize(column name)+vsize(column name)+...) from tablename; on each day.

  
Table Date  Numberofrows size(MB)      size for 1 months    size for 18 months
A 29-Sep   16305639 272.357         11.13GB                 200.34 GB
 01-Oct   22233440 371.4  
 02-Oct   6761553  112.9  
 03-Oct   7710875  128.8  
 04-Oct   7989948 133.4  
 05-Oct   7572118  126.5


Kindly guide me how I should design the tablespace using sample data as shown above.

I would like to know how do I set the parameters in the storage clause and which blocksize to use.

<i>CREATE SMALLFILE TABLESPACE TEST 
datafile  '/home/test/data01.dbf' size 1024m 
MINIMUM EXTENT 50M 
ONLINE 
PERMANENT 
LOGGING 
BLOCKSIZE 8K 
DEFAULT STORAGE( 
INITIAL <size>
NEXT <size>
PCTINCREASE <size>
MINEXTENTS <size>
MAXEXTENTS <size>
MAXSIZE <size>
); </i>


I should be able to see the values set when I query dba_tablespaces. Normally when the storage clause is omitted, the default values in dba_tablespaces is used?

Grateful if you can guide me.

Thanks,

Roshan

and Connor said...

You'll be pleased to know that much of that stuff is either obsolete or handled for you nowadays.

So you can just do:

create tablespace XXX datafile '....' size NNN;

and the defaults will work just fine. You'll get "locally managed" tablespaces with automatic segment space management by default, and then will look after extent sizes etc for you.

So then it just comes down to

- how many tablespaces
- how many datafiles and how big they should be

Only you can really answer that - in terms of pick a tablespace schema that best suits your requirements. For example, you might have just 1 big tablespace for the 200G because you know thats all you'll ever have.

Or, you might choose a tablespace for each (say) 3 months, so you can archive off 3 monthly chunks to slower storage, or transport 3 monthly chunks to a development/testing database from time to time.

Or you might have monthly tablespaces so you can make older months read-only once they are complete to keep backup times short..etc

Hope this helps.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

extent size

Roshan Bisnath, October 09, 2018 - 9:51 am UTC

Hi,

I would like to know how to determine extent size for uniform extent sizing. Is it based on number of rows?

Normally objects are classified in categories below

1)small objects
2)slightly larger than small objects
3)medium objects
4)truely large objects

How do I determine under which category a table belongs to? Is it according to number of rows? size of table (mb, GB, T)?

Thanks,

Roshan
Connor McDonald
October 10, 2018 - 1:43 am UTC

I wouldn't worry too much about it, since the auto-extent size algorithm is fine for the vast majority of applications, ie, we give you 64k to start with, then as the number of extents grows, we move to 1mb, then 8mb and so forth. (Things are different for partitioned table etc, but you get the idea)

But you can see from the algorithm, that its the number of extents that is our driver here - we aim to keep down to a *reasonable* number (100s or 1000s not millions).

So if you wanted to go with your own uniform system, then picking a number that keeps you in a similar extent number range would be a sensible option.

number of extents

A reader, October 10, 2018 - 2:34 am UTC

Thanks for the reply. I would like to know how to set the number of extents when creating a tablespace? or should I set it at table level? Is it calculated using the table size(for 18 months)/size of extent? 

create table BIMOBSMS(
EVTTIMESTAMP DATE,
MSISDN VARCHAR2(32),
"TYPE" VARCHAR2(18 BYTE),
POSTPRE VARCHAR2(3),
WAY VARCHAR2(7 BYTE),
NE VARCHAR2(10 BYTE),
MSISDN_MS VARCHAR2(32 BYTE)
)
STORAGE(
<b>MINEXTENTS 2
MAXEXTENTS 4096</b>
)  tablespace DW_MOBILE PARTITION BY RANGE ("STARTDATE") INTERVAL (NUMTOYMINTERVAL('1','MONTH'))   
(PARTITION "P1"  VALUES LESS THAN (TO_DATE('2000-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')));


Can you please advise on uniform extent and autoallocate when creating tablespace?

https://it.toolbox.com/blogs/dyahalom/oracle-extent-allocation-autoallocate-vs-uniform-032707
Connor McDonald
October 10, 2018 - 3:19 am UTC

In a word: Don't :-)

Here's how I would create that table:

create table BIMOBSMS(
EVTTIMESTAMP DATE,
MSISDN VARCHAR2(32),
"TYPE" VARCHAR2(18 BYTE),
POSTPRE VARCHAR2(3),
WAY VARCHAR2(7 BYTE),
NE VARCHAR2(10 BYTE),
MSISDN_MS VARCHAR2(32 BYTE)
)
tablespace DW_MOBILE 
PARTITION BY RANGE ("STARTDATE") INTERVAL (NUMTOYMINTERVAL('1','MONTH'))   
(PARTITION "P1"  VALUES LESS THAN (TO_DATE('2000-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')));


Probably the only other things I'd be looking at setting would be:

- pctfree (based on if I'm going to update rows)
- compress (if its direct load operations to add data)

A reader, October 10, 2018 - 5:17 am UTC

Hi,

I would like to know how to set the pctfree on the create table clause? based on what should I set it?

Is the dbms_space.space_usage procedure ok?

Results for a test table shown below. 
SQL> SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23  Total number of blocks that are unformatted: 512
Number of blocks that has at least 0 to 25% free space: 0
Number of blocks that has at least 25 to 50% free space: 0
Number of blocks that has at least 50 to 75% free space: 0
Number of blocks that has at least 75 to 100% free space: 178
Total number of blocks that are full in the segment: 31948

PL/SQL procedure successfully completed.


Connor McDonald
October 13, 2018 - 4:32 am UTC

If you only ever INSERT or DELETE, you can consider a pctfree lower than the default of 10.

If you also do UPDATES, but the rows never grow in size, you can consider a pctfree lower than the default of 10.

If UPDATE's do make rows grow, but only rarely, you can consider a pctfree lower than the default of 10.

If you have incredibly high concurrency on rows, then a higher pctfree spreads that load over a greater number of blocks.

But for warehouse loads, it is not uncommon to see pctfree of 1 or 2 rather than 10.

If you compress data, Oracle will automatically make the pctfree to zero, on the assumption that we will not be revisiting that data to update it.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database