Temporary Tablespace Sizing and SNOTE 164925

[former_member360410]

Hi All,

I am facing a dilemma when I look into the temporary tablespace setting.

I have gone thru the SNOTE 164925, for the sizing parameters of PSAPTEMP.

There is a calculation on the note for selecting initial extent next extent etc.....

My PSAPTEMP is locally managed and having default initial extent value of 1 MB but as per the calculation given on SNOTE 164925 solution section.

But the point is in the How Can I check the specified value section on the same note it says....

"As of Oracle 8i, SAP recommends using the assignment of locally managed

temporary tablespaces (see Notes 659946 and 662900). This means that when

problems with a 'dictionary managed' temporary tablespace occur, you should

change to a 'locally managed' temporary tablespace instead of optimizing

the settings of the 'dictionary managed' temporary tablespace."

Now my question is whther I should still go with the default value or with the new values as per the formula given in the same note.

I am using Oracle 10g behind SAPR3 4.6D

Regards,

Soumen

[former_member360410]

Dear Stefan,

Thanks for providing the answer and the link, esspecially the link is very useful to lot of basis people....

I have already gone thru the document you referred before posting this message....

But my question is on the confusion on the SNOTE 164925.

As per the calculation base provided by the note intial segment size should be 10 MB. Because my WORKAREA_SIZE_POLICY = AUTO, and PSAPTEMP size is 14Gigs

Although my PSAPTEMP is LMT but autoextend is off.

I am actually confused on the two contradictory statement in this note. And sometime I wonder whether I follow Oracle standard or SAP standard. Because in this not this is also specified

"Note: The Oracle Enterprise Manager (OEM) sets the above values differently

from the SAP defaults. Therefore, you should adjust these values

accordingly when you use the OEM."

Regards,

[stefan_koehler]

Hello Soumen,

> But my question is on the confusion on the SNOTE 164925.

> As per the calculation base provided by the note intial segment size should be 10 MB. Because my WORKAREA_SIZE_POLICY = AUTO, and PSAPTEMP size is 14Gigs

> Although my PSAPTEMP is LMT but autoextend is off.

I take a look at sapnote #164925 - this note is not "valid" for your temporary tablespace .. the note itselfs says:

> This note contains the storage parameters recommended by SAP for the temporary tablespeace of the Oracle database, provided the temporary tablespace was created as "dictionary managed".

> As per the calculation base provided by the note intial segment size should be 10 MB

If you have a locally managed temporary tablespace, you can not specify any initial size... the only value that you can specify is UNIFORM.. and so please choose a value between 1-5 MB, like i posted before.

Regards

Stefan

[stefan_koehler]

Hello Soumen,

to be honest - i don't really understand your question / problem.

Just use locally managed temporary tablespace and set an uniform size.

The values initial, next and pctincrease doesn't matter in this case. Set the uniform size to 1 - 5 MB.

As you told us you are using oracle 10g .. just check the documenation:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7003.htm#CJAIDDDB

Specify LOCAL if you want the tablespace to be locally managed. Locally managed tablespaces have some part of the tablespace set aside for a bitmap. This is the default for permanent tablespaces. Temporary tablespaces are always automatically created with locally managed extents.
AUTOALLOCATE specifies that the tablespace is system managed. Users cannot specify an extent size. You cannot specify AUTOALLOCATE for a temporary tablespace.
UNIFORM specifies that the tablespace is managed with uniform extents of SIZE bytes.The default SIZE is 1 megabyte. All extents of temporary tablespaces are of uniform size, so this keyword is optional for a temporary tablespace. However, you must specify UNIFORM in order to specify SIZE. You cannot specify UNIFORM for an undo tablespace.
Restriction on Dictionary-managed Tablespaces 
You cannot specify DICTIONARY if the SYSTEM tablespace of the database is locally managed or if you have specified the temporary_tablespace_clause.

Regards

Stefan