473,320 Members | 1,910 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Tablespace Design

Which one is better in terms of producing optimum performance:

a) Single tablespace that contain multiple containers, say 5 containers of
100MB each container
or
b)Single tablespace that that contain single container, say 1 container of
500MB - same tablespace size as in (a).

Is there any trade-off?

Your help will be appreciated.

Thanks.
maricel


Nov 12 '05 #1
2 2064
"maricel" <ma*****@xtra.co.nz> wrote in message
news:SL********************@news.xtra.co.nz...
Which one is better in terms of producing optimum performance:

a) Single tablespace that contain multiple containers, say 5 containers of
100MB each container
or
b)Single tablespace that that contain single container, say 1 container of
500MB - same tablespace size as in (a).

Is there any trade-off?

Your help will be appreciated.

Thanks.
maricel

To make the most of using multiple containers, put them on different
physical hard drives (not just partitions on the same hard drive). So if you
have 2 hard drives, use two containers per tablespace. This assumes that
your drives are not already striped with RAID.

If your database is mostly OLTP or queries that access a small number of
rows via indexes, then it will not make much difference. But if you have a
IW database with a lot of tablespace scans or index scans (with prefetch
activity), optimum configuration will probably be noticeable.

Also, having large buffer pools reduces the effect that tablespace
configuration has on performance.
Nov 12 '05 #2
With such small tablespaces, it's probably unimportant. If you have a lot of
drive arrays, partitioning the table over the drives for load balancing is
good for performance. But if you only hae a small number of drive arrays,
jsut separating tables and/or logs is normally adequate. With 500MB tables,
and 36GB / 72GB size drives being the smallest modern RAID drive sizes,
you're unlikely to have the hardware for these sorts of considerations.

"maricel" <ma*****@xtra.co.nz> wrote in message
news:SL********************@news.xtra.co.nz...
Which one is better in terms of producing optimum performance:

a) Single tablespace that contain multiple containers, say 5 containers of
100MB each container
or
b)Single tablespace that that contain single container, say 1 container of
500MB - same tablespace size as in (a).

Is there any trade-off?

Your help will be appreciated.

Thanks.
maricel

Nov 12 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: sandeep G | last post by:
I've a table which has a number & a blob column, both of which are NOT NULL type. This table is composite partitioned using range & hash on the same column. Each partition is sub partitioned into...
1
by: Jens H. Hamann | last post by:
Hi, I am having a strange problem with the storage parameters in Oracle 9.2.0.1 database. I create a tablespace with some default storage parameters but when i am adding a table to the tablespace...
6
by: robert | last post by:
just got out of a "class" on EXPLAIN, from a 390/v6 maven. was told that ACCESSTYPE = 'R' in the plan_table meant a TableSpace scan. hmmmm. next step up (or down, depending on your outlook)...
4
by: duoduo | last post by:
I want to know one thing, what is best design for tablespace containers, of course, it is DB2 EE 7.2 on AIX 4.3.3. For example, I have RAID 0+1 storage (8 disks) on EMC Symmetrix. Now I need to...
2
by: Kums | last post by:
What is the maximum permissible size of a database? Is there any limitation. What is the maximum # of tablespace's allowed in a database? Thanks for your response.
4
by: Joachim Klassen | last post by:
DB2 V8.2 FP10 on Windows I tested the following HADR scenario: - a new tablespace on a new filesytem is created on the primary System - the replay on standby fails because of lacking permissions...
2
by: Raghava | last post by:
hi all, i was working on DB2 and came across an issue. i.e. can a tablespace be rollforwarded to POINT-IN-TIME? the scenario is as follows: i created a data base and a tablespace in it. i have...
2
by: vj_dba | last post by:
Hi Group, I have a problem in restoring my tablespace, my database is running in ARCHIVAL logging, I created a tablespace, did some transaction, then took the ONLINE BACKUP of the tablespace....
1
by: Michel Esber | last post by:
Hi all, DB2 LUW v8 FP15 Is there any performance overhead if a table is created on data and index tablespaces with different page sizes ? What kind of penalty is expected ? Sample Layout:
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.