on 02-12-2009 11:39 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
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
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
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
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)
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
Closing question.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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:
Regards,
Siddhesh
User | Count |
---|---|
84 | |
24 | |
11 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.