This forum is currently locked. You can't register or post questions at this time. (read more)

Partition with tablespace standard

All posts relating to Oracle database administration.

Moderator: Tim...

shrinika
Advisor
Posts: 260
Joined: Fri Jun 13, 2008 3:18 pm

Partition with tablespace standard

Postby shrinika » Fri Sep 27, 2013 2:58 pm

Tim,

I am using oracle11g. One of my table has couple billion records and index tablespace size 1TB and data tablespace size 300GB.
The table is monthly partition. The plan is to purge(drop partition for older than 16 months) data for each quarter.

There are two ways we can keep the tablespace.


We can keep the one tablespace all the time and drop the older partition. Once it is dropped, it will free up the space. so, newer records
will use the free space.


We can keep the one tablespace for each quarter and drop the older partition and drop corresponding tablespace.

Which one is best option? I am thinking of going to for option 1. Do you think, any performance concern on option 1?
Once we drop the partition, tablespace will free up the space and those space will be used for recent data. Is there any performance issue on this?
Because, every insert, it has to look the free space and any fragmentation issue here?

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Partition with tablespace standard

Postby Tim... » Fri Sep 27, 2013 3:10 pm

Hi.

Here are a few thoughts on this:

1) I think you are a little confused about the whole looking for free space issue. What you describe is how Oracle looks for free space in existing blocks in a table/partition. This happens when blocks are partially empty. If you drop a partition, all the blocks are gone. Creating a new partition will reuse the space in the file, but it won't be refilling the old blocks, they will be wiped and everything starts again. In this sense, using a new tablespace or the old one is the same.

2) If you are worried about gaps in your datafiles, use uniform allocation, so all extents in the datafiles are of the same size. That way, if you drop a partition, the new partition you create will be able to use all the space. The problem with unused gaps in the datafiles comes when you have different extent sizes in the tablespace.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

shrinika
Advisor
Posts: 260
Joined: Fri Jun 13, 2008 3:18 pm

Re: Partition with tablespace standard

Postby shrinika » Fri Sep 27, 2013 3:28 pm

Tim, Thanks for quick and clear explanation.

My tablespace allocation type is SYSTEM. My db is very high transaction database. I don't want to go and change for now. However, i am not worried about gaps in the data file for now.

I was worried about what is the performance concern SINGLE TABLESPACE VERSUS MULTIPLE TABLESPACE in partition table.

Now you clarified that using new/old tablespace are same. Because, when we drop the partition, all the blocks are gone. Again when we create the new partition, it allocate the space from
the datafile.

This clear out my confusion.

Thanks again for your support!

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Partition with tablespace standard

Postby Tim... » Fri Sep 27, 2013 3:36 pm

Hi.

If all your tablespaces are being put in the same disks/arrays, then there is little difference due to tablespace arrangement from a performance perspective. If they are spread over different disks/arrays, then splitting into multiple tablespaces may give benefit if done right.

The bigger factor his is an administrative one. One giant tablespace is harder to manage than several smaller ones. Imagine a situation where you need to do a tablespace recovery... :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

shrinika
Advisor
Posts: 260
Joined: Fri Jun 13, 2008 3:18 pm

Re: Partition with tablespace standard

Postby shrinika » Fri Sep 27, 2013 5:56 pm

Thanks Tim!

All the tablespaces data files are different LUNS. It is lunix OS and each LUN size is between 150G to 250G.

Thanks

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Partition with tablespace standard

Postby Tim... » Sat Sep 28, 2013 7:31 am

OK.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 1 guest

cron