Oracle DB extent based striping

[Sudip1]

Dear All,

We have Oracle 10g (ECC 6.0 - unicode) systems installed on HP UX. The usage of the sapdata folders are as follows:

/oracle/RD1/sapdata4 80%

/oracle/RD1/sapdata3 48%

/oracle/RD1/sapdata2 83%

/oracle/RD1/sapdata1 45%

Our data centre team (storage) is telling to migrate this database and implement "DB Extent based Striping" under only sapdata1 & sapdata2.

As per them, if we implement this and if any tablespace has got more than one data file (usally happens for SAP installation), Oracle will automatically allocate extents to all the data files simultaneously whenever it writes and that will ensure the equal utilization of storage space under all sapdata mount points.

I am very much unclear about this.

Any idea how to implement the same? Is there any documentation available?

Please advice.

Thanks & Regards

Sudip

[Former Member]

Hi Sudip,

Let me make it very clear that datafiles auto extend is not recommended for Production environment at all.It is true sapdata1 ......n contains more than one datfiles.

It is very much depends what actually you want to achieve for e.g performance: I/O bottleneck at one particular datafile.

Let me know what exactily is your requirement is ? So it would be easier to understand and help you out.

Regards,

King V.

[Sudip1]

Hi King,

Thanks for the reply.

The Storage team is trying to achieve two things:

01) Even usage of sapdata folders.

02) Avoid I/O contention in one datafile (as per them, one extent will be automatically extended to all data files so to achieve read parallelization and increase database throughput).

As per then the requirement is to migrate all tablespaces (export and import) so that they become "Locally Managed" (which is the default case) and "Segment space management" becomes "Auto".

Please advise

[lbreddemann]

Hi Sudip,

it's correct hat whenever a tablespace has more than one datafile Oracle will allocate extents in a round-robin fashion.

You get that for free - nothing to change here.

However, this will not equalize I/O traffic concerning the sapdataX-folders.

The idea to migrate to LMTS and ASSM is also quite a good one, but these are technologies that make storage easier to handle.

The storage is not going to be necessarily faster by that.

(Ok - LMTS brings performance enhancements during (de-)allocation of extents - but this is not the major concern here).

So for the goals of your storage people:

>01) Even usage of sapdata folders.

>02) Avoid I/O contention in one datafile (as per them, one extent will be automatically extended to all data files so to achieve read parallelization and increase database throughput.

I propose to move the datafiles so, that the I/O traffic is evenly distributed over the I/O Channels/LUNs ... whatever you have there...

This is done by just moving the files around.

Concerning the comment to 02... this is plain wrong. One extent will always just be stored into one datafile. Always.

BTW: Changing these things is usually quite time intensive and can rarely be done online. Have you got any measurements that actually indicate that your I/O troughput is bad due to I/O contention?

KR Lars

[Sudip1]

Hi,

Thanks for the reply and sorry for this delay in response.

I have had a detailed discussion with our storage team and ultimately it boils down to the following:

01) The Storage team wants to reduce the number of mount points... instead of SAPDATA1...4 they want to reduce it to SAPDATA1 & 2.

02) They want to recreate the SAPDATA tablespaces (PSAPSR3 / PSAPSR3DB / PSAPSR3USR / PSAPSR3640) with "EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M" and move data from the existing tablespaces to the newly created ones....using "alter table" command...online.

My confusion here is.....as per note 214995, tablespaces should be created as "EXTENT MANAGEMENT LOCAL AUTOALLOCATE" with the exception of PSAPTEMP.

Will there be any problem in case I follow as mentioned in point #2 above?

Please advise.

Thanks & Regards

Sudip

[lbreddemann]

> I have had a detailed discussion with our storage team and ultimately it boils down to the following:

>

> 01) The Storage team wants to reduce the number of mount points... instead of SAPDATA1...4 they want to reduce it to SAPDATA1 & 2.

I don't see the improvement here - but hey, it's your system. Do what you want to!

> 02) They want to recreate the SAPDATA tablespaces (PSAPSR3 / PSAPSR3DB / PSAPSR3USR / PSAPSR3640) with "EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M" and move data from the existing tablespaces to the newly created ones....using "alter table" command...online.

> My confusion here is.....as per note 214995, tablespaces should be created as "EXTENT MANAGEMENT LOCAL AUTOALLOCATE" with the exception of PSAPTEMP.

>

> Will there be any problem in case I follow as mentioned in point #2 above?

You mean except the fact that the UNIFORM SIZE 1M will lead to space wastage for smaller tables and indexes?

See, I don't want to sound harsh - but do yourself a favor and read the oracle documentation, specifically the concepts guide [here|http://www.oracle.com/pls/db102/to_toc?pathname=server.102%2Fb14220%2Ftoc.htm&remark=portal%28GettingStarted%29]

The whole extent management topic is not that difficult to comprehend - but you have to read it first.

Using UNIFORM sized extents for something else then the TEMP tablespace is usually only a good idea for very specific usages - like BW tablespace where you load lots of data. For these tablespaces you may then even choose bigger extent sizes.

Maybe also this blog entry may be useful to you: [Fragmentation in Oracle LMTS tablespace!|https://www.sdn.sap.com/irj/sdn/weblogs?blog=/pub/wlg/8013] [original link is broken] [original link is broken] [original link is broken];

KR Lars

[Sudip1]

Hi KR,

Thanks for the reply and for being polite to me...

I am not new to Oracle ....working almost since 1997 but this term "DB Extent based striping" was new to me and thats why I opened this thread, in case I come accross any one who has also heard of the same term and help me to understand.

By point #2 I wanted to know if I create the new tablespace (PSAPSR3) as UNIFORM SIZE 1M instead of AUTOALLOCATE .. , will that contradict the SAP working in any way(?) as because I am not following the recommendations as described in the section "LMTS - Uniform Size or Autoallocate?" in note 214995.

Please note, I am new to SAP.

Any suggesions?

Thanks & Regards

Sudip

[lbreddemann]

Hi Sudip,

actually SAP together with Oracle recommend to use LMTS (autoallocate) + ASSM.

Using LMTS (uniform) would not render the SAP unusable or affect your support - but you will run into problems here.

See - in the usual SAP ABAP schema there are (depending on the SAP release) 50000 to 80000 tables. Each of these tables has one or more indexes.

Sums up to around 120000 segments in a SAP ABAP schema.

Of these tables there are many that will just not be used in your system.

With LMTS (autoallocate), these unused (aka empty) segments will take 8 blocks (64k) each. With your proposed LMTS (uniform 1M) approach, each of them will take ... 1M.

So, for one empty table with say 3 indexes you're going to allocate... 3M compared to 192k!

If you want to bloat your database by allocating in 1M sizes - you're the boss, you buy the storage

Bottomline: please don't do this. Really.

If you think that things will go that much better with your setup - test it.

Do the reorganization on your testmachine.

Check how long it takes and how much time/resources you safe by it.

KR Lars

[Former Member]

Hi Sudip,

"01) The Storage team wants to reduce the number of mount points... instead of SAPDATA1...4 they want to reduce it to SAPDATA1 & 2."

A quick fix for this is to create softlinks without having to rename individual datafiles. This way, from storage team perspective, database is using two mount points without changes to dictionary.

Cheers,

Nisch