New Index on 550 Gb table - suggstions please

[ashish_vikas]

Hello friends,

i have to create a new Index in Oracle 11g db for a table of size around 550GB. please give me your expert suggestions.

I have checked SAP Note 334224 and plan to do with SQl

Create the index using SQLPLUS with the following command:

create index <index name> on <table name> ( <field1, field2, ...> )

nologging tablespace <tablespace name>

parallel (degree <number>)

..........online;

questions:

1. How much Temp tablespace is required for this.

2. Any suggestions for parallel (degree <number>) parameter.. we have 4 CPU.

3. Should i use parameter nologging or complete nologging tablespace <tablespace name> ? does it have any problems ? Database is in archivelog mode.. and is it required for complete tablespace ?

thanks

ashish

[Former Member · Accepted solution]

Hi,

At the first step, I recommend you to execute "dbms_space.create_index_cost" to calculate estimate index size.

Now let's talk about to your questions;

>> 1. How much Temp tablespace is required for this.

While creating an index on the Oracle, the system uses PGA first. If it suffers, then the system uses PSAPTEMP tablespace. At this stage, before you start the operation, I recommend you create a new temporary tablespace for this operation. At the end of it, drop this temporary tablespace and continue to use old one. So, I suggest factor 2x temporary tablespace for this operation.

>> 2. Any suggestions for parallel (degree <number>) parameter.. we have 4 CPU.

The formula is "CPU_COUNT * PARALLEL_THREADS_PER_CPU" as indicated in the note 651060 - FAQ: Oracle Parallel Execution. At this case, in order to avoid performance problems, the value should be 4*1=4. Please take into account that the system should be able to find idle CPU resource for the other operations. If you are planning to perform this operation while SAP is running, I recommend you to set this value as "2"

>> 3. Should i use parameter nologging or complete nologging tablespace <tablespace name> ? does it have any problems ? Database is in archivelog mode.. and is it required for complete tablespace ?

If you use nologging, the system will not write index blocks to the online redologs. So, if you face with a problem before a backup, you need to re-create the index, from the scratch, after the restore. Find the advantages and disadvantages in the note 547464 - Nologging Option when creating indexes.

I hope that I clarified the issue,

Best regards,

Orkun Gedik

[ashish_vikas · Accepted solution]

Hi Orkun,

thanks for reply.

While creating an index on the Oracle, the system uses PGA first. If it suffers, then the system uses PSAPTEMP tablespace. At this stage, before you start the operation, I recommend you create a new temporary tablespace for this operation. At the end of it, drop this temporary tablespace and continue to use old one. So, I suggest factor 2x temporary tablespace for this operation.

> i had checked similar recommendation by Oracle.

http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/indexes003.htm#i1006643

It says Use the TEMPORARY TABLESPACE option of the ALTER USER statement to make this your new temporary tablespace..

for SAP what will be this User?

Also, can you please tell me, how much space i need to allocate for this tablespace.. 2X of ? if table then it will be 1100 GB??

2nd point Ok.

3 point > reviewing note 547464.

And a new question.. is there any way we can predict time it will take.. any idea.

thanks

ashish

[Former Member · Accepted solution]

Hi,

>> It says Use the TEMPORARY TABLESPACE option of the ALTER USER statement to make this your new temporary tablespace.. for SAP what will be this User?

There are two ways to perform this operation. First one is; create a new oracle user with the same rights as SAPSR3, for example. Then, create a new temporary tablespace and start to create the index by using this user. At the end of the respective operation, you can change the owner of the index and drop the temporary tablespace. I applied this solution once and it worked.

Second one is; increasing default temporary tablespace which is easiest way to do it, but users may face with a performance problem during the operations because of the intensive I/O on PSAPTEMP.

You should choose your own strategy among of above items.

>> Also, can you please tell me, how much space i need to allocate for this tablespace.. 2X of ? if table then it will be 1100 GB??

In order to be at the safe side, I suggested factor 2x. This is maximum value. Minimum value is; it should be as large as the index size.

In short, I suggest you create this index when the system is empty. It will be more efficient, at this case.

Best regards,

Orkun Gedik

[stefan_koehler · Accepted solution]

Hello Orkun,

At the end of the respective operation, you can change the owner of the index and drop the temporary tablespace. I applied this solution once and it worked.

Oh, really? How did you do that without "hacking" some oracle DDIC tables?

but users may face with a performance problem during the operations because of the intensive I/O on PSAPTEMP.

Hmm ... sorry but i can not follow your argumentation. How does an additional temporary tablespace help by I/O load which can cause some performance problems? If the second temporary one is stored on an different LUN on different disks - maybe - but most of the SAP systems are running on a central storage sub system.

Regards

Stefan

[Former Member · Accepted solution]

Hi Stefan,

>> Oh, really? How did you do that without "hacking" some oracle DDIC tables?

You need to execute some steps to use temporary table for a specific user. To find out simple information, check the document, below;

http://www.orafaq.com/node/2

As I told that once I did this operation without any problem.

>> Hmm ... sorry but i can not follow your argumentation. How does an additional temporary tablespace help by I/O load which can cause some performance problems? If the second temporary one is stored on an different LUN on different disks - maybe - but most of the SAP systems are running on a central storage sub system.

Please read my statement very carefully. I said "but users "MAY" face with a performance problem during the operations because of the intensive I/O on PSAPTEMP". You never figure out the hardware from remote. Sorry, but I am not an oracle

Best regards,

Orkun Gedik

[stefan_koehler · Accepted solution]

Hello Orkun,

You need to execute some steps to use temporary table for a specific user. To find out simple information, check the document, below;

Sorry, but this link does not contain any information howto change the owner of an index afterwards?

Could you please post the procedure - i am very interested into that.

Regards

Stefan

[Former Member · Accepted solution]

Stefan,

Find the information in the document. You can find documents about the issue, if you would like.

http://www.pythian.com/news/218/oracle-how-to-move-a-table-to-another-schema/

This needs to hack DDIC tables, but it worked at my case.

This information show how to change owner a table, but you can convert it to the index scenario. Applying this operation is ona a productive system is another issue. Because of this I suggested to increase PSAPTEMP, also.

I hope that I clarified your doubt.

Best regards,

Orkun Gedik

[ashish_vikas · Accepted solution]

I must tell you.. i did not had much option to test any thing in D & T systems for this.. developer performed this index creation with transport (for such a huge table) and it came to my notice when the import was running in Prod and index creation got terminated with small temp tablespce.

So as of now, we already have this index in ABAP of P and now it is required to create in db with SQLs..

I hope Index already available in ABAP with not create any further problems in creation of it in DB.

thanks

ashish

[Former Member · Accepted solution]

Hi,

Again, I would like to note that it would be great if you could execute this operation, when the system is idle. By doing so, you will be able to use all CPU resources for this operation.

>> I hope Index already available in ABAP with not create any further problems in creation of it in DB.

Good luck

Best regards,

Orkun Gedik

[volker_borowski2]

> 1. How much Temp tablespace is required for this.
> 2. Any suggestions for parallel (degree <number>) parameter.. we have 4 CPU.
> 3. Should i use parameter nologging or complete nologging tablespace <tablespace name> ? does it have any problems ? Database is in archivelog mode.. and is it required for complete tablespace ?

Hi,

1) I think, when using parallel DDL, it always uses direct path i/o and then it goes directly to temp.

Never saw anything different when doing this. Always had "direct path write temp" as wait event in ST04 during creation.

Check the index name on DB level in the development system. If the tablename is long, the activation

of the transport imported later might change the name to make it fit into the namespace and therefore

might shorten some names.

I e. ODS-Table name

/BIC/ABCD_GH_JKL

Index 010

leads to an index name

/BIC/ABCD_GH_JKL10

on DB level. It depends on the length of the table name. In this case /BIC/ABCD_GH_JKL~010 would be too long.

If you plan to import the corresponding transport afterwards, make sure, that the index name fit the one that

will be generated on DB level. Otherwise you might get an error that this fieldlist is already indexed.

Keep in mind that allthough you create online, it requires a short exclusive lock to start. Just had that last week.

My statement waited about 25 minutes in ST04 before it kicked off the PQ slaves.

2) If you are nearly alone, and your CPUs are speedy, I'd go parallel 8. This will create 16 PQ slaves for the statement,

8 beinig busy pumping data to temp, 8 waiting until these are ready and then doing reading temp and writing target.

If there is activity on the system, 2 or 4 might be a better choice.

3) NEVER set the TS nologging. But yes, you can use NOLOGGING in the create statement. Check the brrecover docu.

It is capable of handling NOLOGGING indexes after a restore, but in general it should be avoided,

esp. if the index is not re-created all the time (as i.e. in BW dataloads).

Volker

Oh, and as for the discussion of temp files:

I thought since oracle 10g you can simply drop empty datafiles and tempfiles.

So I'd extend PSAPTEMP as needed and simply drop the files back out afterwards.

Keep in mind these are SPARSE Files that may not use the shown space in the filesystem until utilized!

I mind I have read somewhere that change of a schema owner is a development request for version 12.

Edited by: Volker Borowski on Sep 27, 2011 8:23 PM

[ashish_vikas]

many thanks to all of you.. It is really going to be great help.

1. I think we will go with default temp tablespace extension.. as this is easier way for Prod system. I see another Index on this table and they are of size around 30-40 GB.. so i expect new Index to also take similar size. I plan to add 100-200 GB temp more..

2. again for CPU, i will go for 2-4..

However, is there any way we can just predict time it will take.. roughly. 6 hrs or 10hrs.something. I know, this is not correct question to ask but we need to provide some predicted time to customer .. (we were able to create a index on different 250Gb table in 2 hrs in same system with import of transport).

Also, after i started the SQL, is there something available with which we can monitor progress.. or any help in predicted time to finish.

thanks

ashish

[Former Member]

Hello ashish,

since you are asking about suggestions: You could check SAP note 1109743 "Use of Index Key Compression for Oracle Databases". Index compression is available even for Oracle 10g and doesn't cost additional license fees. It strongly depends on the indexed colums, so if you are lucky your newly created index won't be 90 GB but maybe only 20 GB in size.

Ideally you would perform this first step on a small system, e.g. development system:

Step 1: Compute the number of the leading columns of an index to deliver the highest compression rate

Once your index creation is running you can watch V$SESSION_LONGOPS. The column TIME_REMAINING gives an estimation about how long the running transaction will take. When creating indexes this information should be quite reliable.

Regards,

Mark

[volker_borowski2]

> However, is there any way we can just predict time it will take.. 

Hi,

when going parallel, you can do a

set timing on

select /*+ full(T) parallel(T,n) / count() from sapSR3.tablename T;

This will give you roughly the time which will be needed to read the table with PQ.

Give it additional 50% for writing to temp and copying back the result to the target when creating the index

Volker