Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Kathrin.

Asked: November 22, 2005 - 11:09 am UTC

Last updated: May 30, 2016 - 5:15 am UTC

Version: 9.2

Viewed 1000+ times

You Asked

Hi Tom,
we're planning for a Datawarehouse. Someone came up with a design which will use a really large number of tablespaces, but most of them will be read-only.
We're not final about sizing and datamodel, but this design will go for about 40,000 tablespaces. I'm a bit concerned what impact this design will have. I can imagine growing needs, so the number will almost definitely go up. I learned that since Oracle 9 max_data_files is no drawback anymore.
(datafiles will be round about 1:1 to tablespaces).

So where did this design come from?
partitioning by date, keeping 400 days.
Tables are loaded independently, so the design is, to place each table in distinct tablespaces. So 100 tables each with 400 partitions = 40,000 tablespaces.
Everytime a day-slice of a table is loaded successfully, the tablespace will get turned read-only and go to backup.

I can think of alternatives, but this design will be really easy to maintain (from logical point-of-view).
But what about the technical view?
The DBWRs won't be in trouble tracking SCNs in the file-headers, because most are read-only? What will happen to the controlfiles? What about backup and restore?

I will appreciate any input,
thanks,
Kathrin


and Tom said...

I'm not sure this is a good idea.

Yesterday, I set about setting this up - to see what would happen.

First issue I hit was:

ops$tkyte@ORA10GR2> show parameter db_files;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_files integer 31100
ops$tkyte@ORA10GR2> alter system set db_files = 40000 scope=spfile;

System altered.

ops$tkyte@ORA10GR2> connect / as sysdba
Connected.
sys@ORA10GR2> startup force;
ORA-00064: object is too large to allocate on this O/S (1,5120000)


I could not on my platform even get to 40k. You might be able to.


However, I found 31,100 was OK (31,150 was not, so my limit was somewhere in there).....

So, I started the test:

....
create tablespace t_00001 datafile '/home/ora10gr2/oradata/ora10gr2/test/d002/t_00001.dbf' size 1m
Thu Nov 24 10:06:18 2005
Completed: create tablespace t_00001 datafile '/home/ora10gr2/oradata/ora10gr2/test/d002/t_00001.dbf' size 1m
Thu Nov 24 10:06:18 2005
alter tablespace t_00001 read only
Converting block 0 to version 10 format
Completed: alter tablespace t_00001 read only
Thu Nov 24 10:06:18 2005
create tablespace t_00002 datafile '/home/ora10gr2/oradata/ora10gr2/test/d003/t_00002.dbf' size 1m
Completed: create tablespace t_00002 datafile '/home/ora10gr2/oradata/ora10gr2/test/d003/t_00002.dbf' size 1m
Thu Nov 24 10:06:18 2005
alter tablespace t_00002 read only
Converting block 0 to version 10 format
Completed: alter tablespace t_00002 read only
......
create tablespace t_31093 datafile '/home/ora10gr2/oradata/ora10gr2/test/d094/t_31093.dbf' size 1m
Completed: create tablespace t_31093 datafile '/home/ora10gr2/oradata/ora10gr2/test/d094/t_31093.dbf' size 1m
Fri Nov 25 12:20:19 2005
alter tablespace t_31093 read only
Fri Nov 25 12:20:22 2005
Converting block 0 to version 10 format
Completed: alter tablespace t_31093 read only
Fri Nov 25 12:20:22 2005
create tablespace t_31094 datafile '/home/ora10gr2/oradata/ora10gr2/test/d095/t_31094.dbf' size 1m
Completed: create tablespace t_31094 datafile '/home/ora10gr2/oradata/ora10gr2/test/d095/t_31094.dbf' size 1m
Fri Nov 25 12:20:27 2005
alter tablespace t_31094 read only
Converting block 0 to version 10 format
Completed: alter tablespace t_31094 read only
Fri Nov 25 12:20:29 2005



Now, I spread the files over 500 subdirectories (d001 .. d500) so as to not jam one directory with thousands of files. so, about 310 files/directory.


Note that it took well over 24 hours to do this, at the end - it was taking 4-5 seconds to create a new tablespace and 2-3 seconds to make it read only. In the beginning - this was under a second (about .5/.75 seconds)...

Opening the database after this took forever - until I set read_only_open_delayed=true (skip checking the thousands of files). That made it workable.

However, for the first time - I had to gather stats on the perfstat schema before I could run a statspack report (it was taking forever), and even then - the report was less than useful at that point. Which brings me to the point that many tools out there will choke big time with that many tablespaces (I doubt many people would test their database admin tool with 40k tablespaces). You'll find issues with some tools I am sure.

I would look for options to cut down on tablespaces in this case - I've never seen this many in use personally - one of the reasons I'm going to publish this one is to see what experiences others have had. I asked around and got nothing "positive" back (no one said - sure, sounds like a good idea, they were cautious at best - negative more often).







Rating

  (7 ratings)

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

Comments

Directories

mark, November 26, 2005 - 3:41 am UTC

"I spread the files over 500 subdirectories" Is there a benefit to this, other than administrative one? Especiall if you're on SAN...Where I work there used to be a standard to have /oradata01 through /oradata05 to hold the files....I changed it to just /oradata.

Tom Kyte
November 26, 2005 - 12:28 pm UTC

the limit is in the OS and the directory structure itself. Directories are themselves "files" that are read to find other files. Too many files in a directory can definitely affect file open and directory traversal operations.

Quadro, November 26, 2005 - 9:12 am UTC

Kathrin,
if you are trying to make last possible effort to minimize backup time -- consider using 10G and block change tracking feature for rman. With BCT tablespace size is virtually unrelevant (only amount of blocks changed).

Quadro, November 26, 2005 - 9:23 am UTC

Mark,
there is file system overhead issues then you have too many files in the same directory.

thanks so much

A reader, November 28, 2005 - 3:33 am UTC

Thanks so much,
for putting evidence into this. I had a bad feeling about this design from the start, but was not able to name the points exactly. With this test scenario I'll have a fair chance to get my colleagues to re-design, before we become completely doomed.
regards,
Kathrin

Number of tablespaces

SS, January 13, 2010 - 8:55 am UTC

Hi Tom,

There was a proposal on the table to move files like MS Word, excel etc out of the blobs where they are currently stored and into the OS file system. The reason was that it was getting really expensive to store GBs of data in the database.

I proposed that we consider moving the blobs into a separate tablespace and put the tablespace in a cheaper storage area. Do you think that is a valid idea?

Also, we have a multi-tenant database architecture using VPD. One of the requirements is to limit the "total size of files" per blob by customer. So, for example, consider a documents table with the structure:

(document_id number primary key,
document blob,
customer_id number foreign key references customer(customer_id))

Is there a good way to enforce a restriction that the document blob cannot exceed 20gbs / customer id?

Also, thanks for your talk in Pittsburgh yesterday. You have a very good way of explaining things.

Tom Kyte
January 18, 2010 - 4:25 pm UTC

.. The
reason was that it was getting really expensive to store GBs of data in the
database.
...

and storing them in the file system will solve this how? If you want to talk expensive - explain what happens when someone does an "rm" - you know, to free up some space. Sure you can restore from backups (maybe) but can you do so consistently? with respect to the database?

What about security - how are you going to give the level of protection in the file system you have in the database?

and more.

Especially given your multi-tenant approach - security and recoverability might be relevant - your customers might expect that.




For the 20gb/customer, I'd probably use a materialized view/summary table to hold the output of a summation by customer id. Maybe that would be in the background (so as to not affect the current application) and when they exceeded 20gb - you would have a flag set that would prevent future inserts.


You might (*MIGHT*) consider storing the file size as an attribute - not referencing dbms_lob.getlength() - since getlength would actually read the blob to figure that out. If you know the length when you add the document compute it once) maintaining the aggregate is a very "low impact" thing to do.

Thank you

SS, January 19, 2010 - 9:54 am UTC


expansion on direcory file counts

Mathew, May 29, 2016 - 5:13 pm UTC

As Tom stated, the directories are a file, however one thing to keep in mind that it's a special file that the kernel file system driver assumes would be able to fit cleanly into a very small portion of ram. Once a folder gets a given amount of files (in my environment it was 50,000 files) ls -l and other stat operations in the folder start to take exponentially longer (seconds, minutes, hours). In addition the directory is a flat file database, thus it doesn't "shrink" and keeps a high water mark of the total size until you either delete and recreate the folder or use a third party tool to reclaim the directory blocks. Each file system handles these differently, but the ramifications are the same across all of them.
Chris Saxon
May 30, 2016 - 5:15 am UTC

Good input.

A "common" problem people have in this area is if they dont clean up their standard audit files (*.aud). Once you get tens of thousands of these, any operation (eg connect) that needs to write an audit file tends to slow down badly.


More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.