MSEG number of recordes increases

[former_member199632]

Hello,

In PRD, MSEG table contain more than 3,200,000 entires

Due to this end-users are complaining their transactions are very time consuming.

Using RSTABLESIZE i have checked the size of MSEG. Its around 10 GB

whats the best solution we can provide ?

Is it possible to archive data / remove old data

regards,

Zerandib

[former_member188883 · Accepted solution]

Hi Zerandib,

I have seen clients whose mseg table is around 120 GB and they operate well without doing archiving. Here you can do the following

1) Check whether your database statistics is up to date

2) You can re-build primary as well as secondary indexes on table MSEG

Above 2 can provide your immediate results in terms of performance.

Regards,

Deepak Kori

[former_member199632 · Accepted solution]

Hello deepakkor

what mean by "Check whether your database statistics is up to date";

Can you please provide more info. regarding this.

regards,

zerandib

[former_member188883 · Accepted solution]

Hi Zerandib,

I want you to check whether you run update database optimizer statistics regularly or not.

Regards,

Deepak Kori

[former_member199632 · Accepted solution]

Hi Deepak,

Yes, we have schedule that once in 2-days

So stats are up to date

regards,

Ushan

[former_member188883 · Accepted solution]

Hi Ushan,

Can you try re-building the existing indexes on table MSEG.

Use the following command to rebuild indexes online.

alter index <Schema>."MSEG~R" rebuild online tablespace PSAP<SID>;

Regards,

Deepak Kori

[former_member199632 · Accepted solution]

Hi Deepak,

Using dba_indexed , I have checked how many indexes are there for the MSEG table.

there are 4.

What will be the effect to the System, if i execute this;

Whats the process that will happen when rebuilding an index

alter index <Schema>."MSEG~R" rebuild online tablespace PSAP<SID>;

Is it possible to execute this, while users are working on PRD.

Kindly let me know.

regards,

zerandib

OWNER SAPSR3

INDEX_NAME MSEG~0

INDEX_TYPE NORMAL

TABLE_OWNER SAPSR3

TABLE_NAME MSEG

TABLE_TYPE TABLE

UNIQUENESS UNIQUE

COMPRESSION DISABLED

TABLESPACE_NAME PSAPSR3

INI_TRANS 2

MAX_TRANS 255

INITIAL_EXTENT 16384

MIN_EXTENTS 1

MAX_EXTENTS 2147483645

PCT_FREE 10

LOGGING YES

BLEVEL 2

LEAF_BLOCKS 30497

DISTINCT_KEYS 2355948

AVG_LEAF_BLOCKS_PER_KEY 1

AVG_DATA_BLOCKS_PER_KEY 1

CLUSTERING_FACTOR 1063592

STATUS VALID

NUM_ROWS 2355948

SAMPLE_SIZE 88743

LAST_ANALYZED 05-AUG-11

DEGREE 1

INSTANCES 1

PARTITIONED NO

TEMPORARY N

GENERATED N

SECONDARY N

BUFFER_POOL DEFAULT

USER_STATS NO

GLOBAL_STATS YES

JOIN_INDEX NO

IOT_REDUNDANT_PKEY_ELIM NO

DROPPED NO

-

---

OWNER SAPSR3

INDEX_NAME MSEG~M

INDEX_TYPE NORMAL

TABLE_OWNER SAPSR3

TABLE_NAME MSEG

TABLE_TYPE TABLE

UNIQUENESS NONUNIQUE

COMPRESSION DISABLED

TABLESPACE_NAME PSAPSR3

INI_TRANS 2

MAX_TRANS 255

INITIAL_EXTENT 16384

MIN_EXTENTS 1

MAX_EXTENTS 2147483645

PCT_FREE 10

LOGGING YES

BLEVEL 2

LEAF_BLOCKS 20297

DISTINCT_KEYS 112410

AVG_LEAF_BLOCKS_PER_KEY 1

AVG_DATA_BLOCKS_PER_KEY 19

CLUSTERING_FACTOR 2195150

STATUS VALID

NUM_ROWS 2450658

SAMPLE_SIZE 138700

LAST_ANALYZED 05-AUG-11

DEGREE 1

INSTANCES 1

PARTITIONED NO

TEMPORARY N

GENERATED N

SECONDARY N

BUFFER_POOL DEFAULT

USER_STATS NO

GLOBAL_STATS YES

JOIN_INDEX NO

IOT_REDUNDANT_PKEY_ELIM NO

DROPPED NO

-

---

OWNER SAPSR3

INDEX_NAME MSEG~R

INDEX_TYPE NORMAL

TABLE_OWNER SAPSR3

TABLE_NAME MSEG

TABLE_TYPE TABLE

UNIQUENESS NONUNIQUE

COMPRESSION DISABLED

TABLESPACE_NAME PSAPSR3

INI_TRANS 2

MAX_TRANS 255

INITIAL_EXTENT 16384

MIN_EXTENTS 1

MAX_EXTENTS 2147483645

PCT_FREE 10

LOGGING YES

BLEVEL 2

LEAF_BLOCKS 15197

DISTINCT_KEYS 35595

AVG_LEAF_BLOCKS_PER_KEY 1

AVG_DATA_BLOCKS_PER_KEY 11

CLUSTERING_FACTOR 411229

STATUS VALID

NUM_ROWS 2464302

SAMPLE_SIZE 186278

LAST_ANALYZED 05-AUG-11

DEGREE 1

INSTANCES 1

PARTITIONED NO

TEMPORARY N

GENERATED N

SECONDARY N

BUFFER_POOL DEFAULT

USER_STATS NO

GLOBAL_STATS YES

JOIN_INDEX NO

IOT_REDUNDANT_PKEY_ELIM NO

DROPPED NO

-

---

OWNER SAPSR3

INDEX_NAME MSEG~S

INDEX_TYPE NORMAL

TABLE_OWNER SAPSR3

TABLE_NAME MSEG

TABLE_TYPE TABLE

UNIQUENESS NONUNIQUE

COMPRESSION DISABLED

TABLESPACE_NAME PSAPSR3

INI_TRANS 2

MAX_TRANS 255

INITIAL_EXTENT 16384

MIN_EXTENTS 1

MAX_EXTENTS 2147483645

PCT_FREE 10

LOGGING YES

BLEVEL 2

LEAF_BLOCKS 12137

DISTINCT_KEYS 29710

AVG_LEAF_BLOCKS_PER_KEY 1

AVG_DATA_BLOCKS_PER_KEY 12

CLUSTERING_FACTOR 357332

STATUS VALID

NUM_ROWS 2413549

SAMPLE_SIZE 228439

LAST_ANALYZED 05-AUG-11

DEGREE 1

INSTANCES 1

PARTITIONED NO

TEMPORARY N

GENERATED N

SECONDARY N

BUFFER_POOL DEFAULT

USER_STATS NO

GLOBAL_STATS YES

JOIN_INDEX NO

IOT_REDUNDANT_PKEY_ELIM NO

DROPPED NO

[former_member188883 · Accepted solution]

Hi Ushan,

It is recommended to run these commands when the user load is pretty low. Mostly in the night time.

No downtime is required for the same.

Regards,

Deepak Kori