cancel
Showing results for 
Search instead for 
Did you mean: 

LTMS - can they become fragmented ?

former_member185954
Active Contributor
0 Kudos

Hello All,

I have a database with LTMS, I have the following tablespaces:

SQL> select tablespace_name, contents, extent_management,

allocation_type, next_extent

from dba_tablespaces;

2 3

TABLESPACE_NAME CONTENTS EXTENT_MAN ALLOCATIO NEXT_EXTENT

-


-


-


-


-


SYSTEM PERMANENT LOCAL SYSTEM

PSAPUNDO UNDO LOCAL SYSTEM

PSAPTEMP TEMPORARY LOCAL UNIFORM 1048576

PSAPDCM PERMANENT LOCAL SYSTEM

PSAPDCM620 PERMANENT LOCAL SYSTEM

PSAPDCMUSR PERMANENT LOCAL SYSTEM

PSAPDCC PERMANENT LOCAL SYSTEM

PSAPDCC620 PERMANENT LOCAL SYSTEM

PSAPDCCUSR PERMANENT LOCAL SYSTEM

PSAPDBW PERMANENT LOCAL SYSTEM

PSAPDBW640 PERMANENT LOCAL SYSTEM

TABLESPACE_NAME CONTENTS EXTENT_MAN ALLOCATIO NEXT_EXTENT

-


-


-


-


-


PSAPDBWUSR PERMANENT LOCAL SYSTEM

PSAPDBWDB PERMANENT LOCAL SYSTEM

PSAPDCCDB PERMANENT LOCAL SYSTEM

PSAPJ2EDB PERMANENT LOCAL SYSTEM

PSAPJJJDB PERMANENT LOCAL SYSTEM

16 rows selected.

SQL>

Just want to know whether fragmentation can occur in Locally managed tablespaces ?

I have read note: Note 214995 - Oracle locally-managed tablespaces in the SAP environment however the note only talks about benefits.

I want to know if anyone has faced performance problems of any sort using LTMS?

PS: I should have included this vital info before:

I have a SAP R/3 Enterprise (6.20) running on Oracle 9.2 patchset 9.2.0.7.0 we have a MCOD config wherein a SAP CRM and a SAP BW also runs on the same database (please don't ask me why )

Regards,

Siddhesh

Edited by: Siddhesh Ghag on Feb 12, 2009 12:44 PM

Accepted Solutions (1)

Accepted Solutions (1)

0 Kudos

Hi,

What do you define by fragmentation?

Why should it be "bad"?

very "old" oracle versions had some limitations (number of extents they could manage and so on)

those limitations had been overcome, but there are still people who think they are current.

I recommend you to take a look at the note 821687 FAQ: Space utilization and fragmentation in Oracle

former_member185954
Active Contributor
0 Kudos

Hello Fidel,

Thanks for the note, i had already gone through this long back.

I do understand fragmentation, however i have noticed that our workflow tables are growing at a constant rate.

Even after we do archiving of 'workitem' object every month.

We observed that if we miss one month's archiving , theoratically there should be an increase in the data growth of these tables for that month.

However that doesn't happen, the growth rate is almost uniform (even with the archiving).

That's why I wondered whether any tables would be affected by fragmentation even when LTMS is active.

Regards,

Siddhesh

0 Kudos

Hi,

  • After archiving, do you reorganize the table?

  • In this case you would be talking about "table fragmentation" not about "tablespace fragmentation". Two different things. In this case, you have to take into consideration PCTFREE and PCTUSED

former_member185954
Active Contributor
0 Kudos

Yes we do not reorg tables, I was assuming LMTS should help w.r.t tables too.

Would that be a cause ?

0 Kudos

Hi,

Those are two different issues.

How the table allocates the extents is handled by the LMTS, but the internal utilization of the table "freespace" depends on the PCTFREE and PCTUSED.

The following link points to the oracle concepts guide, where [PCTFREE, PCTUSED, and Row Chaining|http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/logical.htm#sthref344] are explained. Resume

1) the rows are inserted in blocks that are filled less than PCTFREE.

2) when the block reach this value is taken out of the "list of free blocks"

3) The block will not come back to that list (no more inserts will happen) until the "free" space inside reachs PCTUSED.

What (probably) is happening in your table is the following aproximation:

1) data is being inserted, blocks are taken out of the "freelist"

2) you delete (archive) data, but each individual block is not freed below the PCTUSED, therefore, space is not reused.

3) you insert more data, this will be inserted on "new" blocks => the table grows

what can you do?

this is oracle, you have a couple of dozen different options

1) no brain breaker -> Reorganize the table. This action will compact the data in the block, freeing space => Your table is smaller.

This is the typical recommendation after archiving.

2) more "think" is necessary. If you are "constantly" archiving data from this table, may be it is interesting to have a higher PCTUSED. That way, after archiving, the block is returned faster to the "freelist" and new inserts will happen in blocks that are already allocated.

Interesting note:

572060 Options in the Oracle database during archiving

It is a bit old, but will explain more in detail what I have already mentioned.

former_member185954
Active Contributor
0 Kudos

Excellent.

This is spot on info, I think you are right about the PCTFREE and PCTUSED.

I think we have a problem there definitely.

I checked the largest table in DB02

Blocksize.........byte 8,192

Pct_free ............% 10

Pct_used ............% 0

Pct_used = 0 , I am not sure if this is a way of telling oracle to automatically managed the PCTUSED or its a genuine problem.

cause if its a genuine problem, the blocks are not being added to the freelist untill they are completely empty.

Please correct me if my understanding is incorrect.

Regards,

Siddhesh

former_member185954
Active Contributor
0 Kudos

Further,

Is there any standard from SAP's point of view which should have been followed when setting the PCTUSED and PCTFREE side.

I am wondering why the PCTFREE was set to 0.

Regards,

Siddhesh

0 Kudos

Hi,

Easy.

As commented before, we are talking about oracle, with its millions of options

The table is located in a ASSM tablespace.

Oracle handle automatically the PCTUSED.

you can check it with this:

SELECT TABLESPACE_NAME, EXTENT_MANAGEMENT, ALLOCATION_TYPE, SEGMENT_SPACE_MANAGEMENT FROM DBA_TABLESPACES;

If the SEGMENT_SPACE_MANAGEMENT is AUTO, then it is an ASSM tablespace.

you have more information/description on the SAP note 620803.

Good things

1) you can "shrink" the tables

2) you can use[dbms_space.space_usage|http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_space.htm#sthref6026] to find out how mich free space "has" the table

former_member185954
Active Contributor
0 Kudos

Hello,

All my tablespaces except PSAPTEMP and PSAPUNDO are AUTO managed.

I am yet to check at table level, is there a table which provides me the average free space within a table ?

I can see in the DB02-Detailed Analysis , this is probably being calculated.

In that i can use this information to home in on tables which have more than 30% average freespace as candidate for table reorgs.

Regards,

Siddhesh

0 Kudos

Hi,

> I am yet to check at table level, is there a table which provides me the average free space within a table ?

No, as I mentioned before you can get that information with the package:

dbms_space.space_usage

Follow the link form my previous post.

Do you say that you get the free space for a table in DB02?

Can you be more specific, I do not recall it? (maybe we are talking about different things)

former_member185954
Active Contributor
0 Kudos

Hello Fidel,

I saw the stored procedure, that should help my cause, however I was wondering whether the database storing the value of average freespace pertaining to each table in some kind of Data dictionary table?

In db02, select detailed analysis button provide table name and other relevant details and in the next screen you would see the table name, tablespace name with the size in KB,Blocks,Extents,MaxExtents,Next (K).

On this screen select the table you are interested in and then click the detailed analysis button on the top.

Now it will show you another screen which has various sections detailing various attributes of the table you selected.

In the space section there is a attribute *Free in used blocks.% and also *Total free space....% these I think provide an indicator on whether the selected table will need REORG or not.

Regards,

Siddhesh

former_member185954
Active Contributor
0 Kudos

Hello Fidel,

DBSTATTORA is the table from where DB02 seems to be picking up the Average freespace.

and this table should be filled up by the compute statistics job I think.

Regards,

Siddhesh

Answers (2)

Answers (2)

former_member185954
Active Contributor
0 Kudos

Closing question.

former_member204746
Active Contributor
0 Kudos

yes, fragmentation is still possible, if you delete rows, some block might not be fully freed. but frammentation is less likely in LMTS compared to DMTS becasue extents size vary a lot. in LMTS, extents of power of 2.

tables TST01 and TST03 are tables that are usually fragmented a lot. there are others.

in my case, LMTS has improved performance after a full database reorg.

former_member185954
Active Contributor
0 Kudos

Yes i agree.

Even those tables are growing inspite of the fact that we have standard spool reorg jobs removing old spool requests.

I am currently reviewing the following guide:

https://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/25c1f5d1-0901-0010-d495-e96d02a0...

Regards,

Siddhesh