Table still fragmented after REORG !!

[sap_administrateurs]

Hello

I need help !!! After have execute a script provided by the sap note  "821687 - FAQ: Space utilization and fragmentation in Oracle"

I have observed several tables which are fragmented and in particular the table "PCL2"

TABLE_NAME                                                        NUM_ROWS     ROWLEN     BLOCKS     NET_MB   GROSS_MB  WASTED_MB

--------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ----------

PCL2                                                              38057847       1218   23886199      46393     171486     125094

HRP1001                                                            6659067        144    1257021        966       9025       8059

table PCL2 : WASTED_MB = 125Go  !!!!!

table HRP1001 : WASTED_MB = 8Go

So i have performed a REORG operation on PCL2, but nothing changed

First test :

Reorg, on one table PCL2 in the same tablespace. Then, update statistics at the end. But the result is not good. Nothing changed, the table is still fragmented. The Table is more big

brspace -u / -c force -f tbreorg -t sapsr3.PCL2 -e 4

brconnect -u / -c -f stats -t sapsr3.PCL2 -f collect

Second test :

Reorg, table PCL2 in new tablespace, but the result is not good too, the table is still fragmented.

brspace -u / -c force -f tbreorg -t sapsr3.pcl2 -n PSAPSR3PCL2 -e 4

brconnect -u / -c -f stats -t sapsr3.PCL2 -f collect

Number of segment before reorg : 3606

Number of segment after reorg : 3637

Why after a reorg my table is still fragmented. I dont understand.

When I executed the same action on table HRP1001, result is good, Fragmentation disappeared and i recovered 8Go space free.

Please can you help me ?

Thanks a lot

[ACE-SAP · Accepted solution]

Hi

Both table has a LOB field and the script provides wrong result in this case, it is clearly said in the note.

As your tablespaces must be in ASSM (segment_space_management=auto in dba_tablespaces) you should use DBMS_SPACE package => 1295200 - Oracle 10g or higher: Space statistics based on DBMS_SPACE

The script from note 821687 just estimate the NET_MB in a dirty way based on the number of lines and average size of a line !

Try script  Space_SegmentFragmentationAndChainedRows_DBMS_SPACE_CommandGenerator.txt from note 1438410 - SQL script collection for Oracle

Replace line

    'EABL' SEGMENT_NAME,          /* Name of segment or name pattern */

with

    'PCL2' SEGMENT_NAME,          /* Name of segment or name pattern */

Run the script it will generate an sql query you can then run to get information on fragmentation.

Here are the results provided by both scripts on a table with lob field.

Script from note 1295200

TABLE_NAME      NUM_ROWS ROWLEN     BLOCKS     NET_MB GROSS_MB  WASTED_MB

SOFFCONT1       1435467     178       27564     257        198        -59

Script from note 1438410

OWNER  SEGMENT_N P SEGME UNUSED_MB  QUALITY_% GROSS_MB    NET_MB      CHAIN_%

SAPSR3 SOFFCONT1 TABLE       10.47     95.68    242.00    231.53            0

The associated LOB segment for that table is ten time bigger that the table itself => 43694 vs  242

Best regards

821687 - FAQ: Space utilization and fragmentation in Oracle

Statistics created with DBMS_STATS did not take LONG, LONG RAW, and LOB columns into account correctly. For this reason, these tables can be displayed as highly fragmented. This is incorrect.

[sap_administrateurs · Accepted solution]

Hello Yves.

Thanks a lot for your reply

This is result of query

OWNER  SEGM P SEGME UNUSED_MB  QUALITY_% GROSS_MB

NET_MB

CHAIN_%

------ ---- - ----- ---------- --------- ----------- ----------- -------

SAPSR3 PCL2   TABLE

2118.08

98.86   186435.00   184316.92

0

"QUALITY" seems good and "UNUSED_MB" 2Go

So is it means PCL2 is finally not fragmented ?

[ACE-SAP · Accepted solution]

Yes indeed !

[fschoen · Accepted solution]

Hi

As another test you could also just copy the table and check the size afterwards . e.g.

-- in the database
select segment_name, bytes from user_segments where segment_name like 'PCL2%' 
    and segment_type ='TABLE' 
    order by segment_type;

-- copy table
create table PCL2_copy as select * from PCL2;

-- check size afterwards
select segment_name, bytes from user_segments where segment_name like 'PCL2%' 
    and segment_type ='TABLE' 
    order by segment_type;

-- drop the newly created table
drop table PCL2_copy....

It's only an additional option to check.

regards

fabian

[sap_administrateurs]

many thanks all for your help

Bye

[former_member185954]

Hello,

PLC2 is HR table which has LRAW fields, they need to be handled specially have a look at the thread below:

https://scn.sap.com/thread/1322054

Regards,

Siddhesh

[sap_administrateurs]

Thanks for your reply

Nothing fields with LRAW

SQL> desc sapsr3.pcl2

Name                                      Null?    Type

----------------------------------------- -------- ----------------------------

CLIENT                                    NOT NULL VARCHAR2(9)

RELID                                     NOT NULL VARCHAR2(6)

SRTFD                                     NOT NULL VARCHAR2(120)

SRTF2                                     NOT NULL NUMBER(10)

HISTO                                     NOT NULL VARCHAR2(3)

AEDTM                                     NOT NULL VARCHAR2(24)

UNAME                                     NOT NULL VARCHAR2(36)

PGMID                                     NOT NULL VARCHAR2(24)

VERSN                                     NOT NULL VARCHAR2(6)

CLUSTR                                    NOT NULL NUMBER(5)

CLUSTD                                             BLOB

[former_member185954]

Hello,

Maybe it is converted to a BLOB field, in my system it appears to be LRAW.

The presence of a LOB field requires special treatment as per the note you specified.

• LOB segment fragmentation

                    LOB columns are generally paged out into separate LOB segments (see Note 500340). Unfavorable space utilization in an LOB segment is often caused by the fact that a lot of undo information is stored in the segment itself. To limit the scope of retained undo information, make sure that PCTVERSION storage parameter is not set too high. Default value 10 is usually sufficient.

Check the note 500340 (point no 16).

Regards,

Siddhesh