cancel
Showing results for 
Search instead for 
Did you mean: 

SAP Table index size is greater than the size of the actual table

Former Member
0 Kudos

Hello Experts,

We are resolving an issue related to database performance. The present database size is 9 Terabytes. The analysis of response times through ST03N shows that the db time is 50% of the total response time. We are planning to reorganize the most updated tables (found from DB02old tx).

Here we see that the size of the index for a table is greater than the actual size of the table. Is this possible, if yes then how can we reorganize the index as it does not allow us to reorganize the index using brspace command.

Hope to hear from you soon, and if any additional activities you can suggest to improve the performance of the database will be appreciated.

Thank you

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

To rebuild an index you can either use BR*Tools -> segment management, or call brspace directly:

brspace -f idrebuild -a rebuild -o <schemauser> -i "<INDEX>" -p <parallel degree>

Or you can do it in sqlplus:

SQL> alter index "<schemauser>"."<INDEX>" rebuild online parallel <parallel degree>;

Theoretically it is possible for an index to be bigger than a table, if the index goes over all table columns. But in general the index grows because of deletes on one side and inserts on the other (constantly changing key values). In that case a reorganisation can be beneficial. Please be aware that even when you specify "online", the index is locked against updates during the reorganisation.

Best regards, Michael

Former Member
0 Kudos

Hello Michael / Sachin,

Thank you for your response. I have another query regarding the reorganization of the 'TABLE'. I read on a website that if we reorganize the 'TABLE' the 'INDEX' is automatically rebuild.

But since we are doing an online reorganization of the 'TABLE' will this rebuild the "INDICES' of that table.

I tried the reorganization of a 'TABLE' but the output did not mention any rebuilding of 'INDEX'.

"

BR1124I Starting online reorganization of table SAPSR3.BALDAT ...

BR0280I BRSPACE time stamp: 2009-03-13 14.15.38

BR1105I Table SAPSR3.BALDAT reorganized successfully

BR0280I BRSPACE time stamp: 2009-03-13 14.15.38

BR1141I 1 of 1 table processed - 7795 of 7795 rows done

BR0204I Percentage done: 100.00%, estimated end time: 14:15

BR0001I **************************************************

BR0280I BRSPACE time stamp: 2009-03-13 14.15.38

BR1102I Number of tables reorganized successfully: 1

"

Former Member
0 Kudos

Yes the indexes will be rebuilt, when you rebuild the table with brspace. In fact a copy to the table will be created with new indexes and renamed at the end. You can check it with this statement:

SQL> select object_name, created from dba_objects where object_name like 'BALDAT%';

Regards, Michael

Former Member
0 Kudos

Hello Michael,

Thank you for your reply. This is very helpful indeed. Now my last question is

- is it suggested to perform an online reorganization or offline and why

- the size of the biggest table in my production system is 480GB and the corresponding index is 239GB

I would appreciate if you can suggest additional steps or methods for improving database performance, our database size is 9 Terabytes.

Thank you

lbreddemann
Active Contributor
0 Kudos

> - is it suggested to perform an online reorganization or offline and why

Online reorganization is, well, online. It let's you keep the system running during this housekeeping activity.

So it is of course preferable to avoid business interruption whenever possible.

Therefore: if you can reorganize online (e.g. the table does not contain columns with datatype 'long' and you have enough storage available to have your largest table plus the indexes twice in your system) then you should do it online.

Offline reorganization may be quicker, but you've to have a downtime for it.

Also, the offline reorganization bears a risk in it, since the export file cannot be checked for consistency, except by importing the data. If there is something going wrong then, you'll need a restore/recovery.

> - the size of the biggest table in my production system is 480GB and the corresponding index is 239GB

Doesn't matter - online reorganization is made exactly to handle large segments during uptime.

> I would appreciate if you can suggest additional steps or methods for improving database performance, our database size is 9 Terabytes.

Check for what the database spends most time when processing your business processes.

Start with your business transactions, get down to single statements and check where the processing time is spend.

Database reorganization is rather seldom the 'cure' for bigger performance issues.

Parameters are, wrong/missing indexes are, bad SQL is.

For Oracle there is a large list of SAP notes available providing tools and procedures on how to approach database performance issues. By reading these notes you'll be able to tackle with many issues on SAP systems.

regards,

Lars

Former Member
0 Kudos

Hello Lars,

Thank you for the response.

In transaction DB02old we generated the list of tables (Tables/Index history),

DB02 -> Goto -> Tables & Indices -> History of Tab/Ind

here we sorted the tables by column Size / K byte -> Chg /day.

As we understand the the tables/indexes having the maximum "Chg/day" should be reorganized/rebuild.

Also I have a table "S033" which shows Size = 0 but the Chg/day = 2,984,007. I would like to understand how the table size being zero the changes to the table are almost 3 GB per day.

Thank you

Former Member
0 Kudos

Database reorganization is rather seldom the 'cure' for bigger performance issues.

Very true indeed, you can trust Lars on this one.

As we understand the the tables/indexes having the maximum "Chg/day" should be reorganized/rebuild.

Not necessarily, it could help, but a reorg could hurt as well. And especially if you do online reorgs, you put additional load on the server.

I have a table "S033" which shows Size = 0

Check if the data collection job was running lately, there is always a timestamp somewhere, showing the collection time. You can verify the size of an object in sqlplus as well:

SQL> select segment_name, bytes from dba_segments where segment_name = 'S033';

I suggest before starting "random" reorganisations, you do a serious analysis of your actual performance issues. If possible take the advice of a performance tuning specialist. After that decide on the tuning actions. You should also investigate if archiving / deletion of data is possible.

Best regards, Michael

Former Member
0 Kudos

Hi Zaheer,

online redef may help you (for a little while) , but also check WHY the index became fragmented.

Improper settings can bring the index fragmented again and you have reoccuring reorg needs.

i.e.

check if PCT_INCREASE >0 if you are using Dictionary Managed Tablespaces or locally managed tablespaces that uses a "User" allocation policy. Set it to 0 to generate uniform next extents in the online reorg.

select

            SEGMENT_NAME,
            SEGMENT_TYPE,
            round((NEXT_EXTENT*BLOCKS)/(EXTENTS*BYTES))*(BYTES/BLOCKS),
            PCT_INCREASE
        from
            DBA_SEGMENTS
        where
            OWNER='SAPR3'
        and
            SEGMENT_TYPE in ('INDEX',
                            'TABLE')
        and
            PCT_INCREASE > 0
        and segment_name in ('Yourtable','Yourindex')

In the following cases, it may be worthwhile to rebuild the index:

--> the percentage of the space used is bad - lower than 66%: PCT_USED

--> deleted leaf blocks represent more than 20% of total leaf blocks: DEL_LF_ROWS

--> the height of the tree is bigger than 3: HEIGHT or BLEVEL

select 
  name, 
  '----------------------------------------------------------'    headsep, 
  'height               '||to_char(height,     '999,999,990')     height, 
  'blocks               '||to_char(blocks,     '999,999,990')     blocks, 
  'del_lf_rows          '||to_char(del_lf_rows,'999,999,990')     del_lf_rows, 
  'del_lf_rows_len      '||to_char(del_lf_rows_len,'999,999,990') del_lf_rows_len, 
  'distinct_keys        '||to_char(distinct_keys,'999,999,990')   distinct_keys, 
  'most_repeated_key    '||to_char(most_repeated_key,'999,999,990') most_repeated_key, 
  'btree_space          '||to_char(btree_space,'999,999,990')       btree_space, 
  'used_space           '||to_char(used_space,'999,999,990')        used_space, 
  'pct_used                     '||to_char(pct_used,'990')          pct_used, 
  'rows_per_key         '||to_char(rows_per_key,'999,999,990')      rows_per_key, 
  'blks_gets_per_access '||to_char(blks_gets_per_access,'999,999,990') blks_gets_per_access, 
  'lf_rows      '||to_char(lf_rows,    '999,999,990')||'        '||+ 
  'br_rows      '||to_char(br_rows,    '999,999,990')                  br_rows, 
  'lf_blks      '||to_char(lf_blks,    '999,999,990')||'        '||+ 
  'br_blks      '||to_char(br_blks,    '999,999,990')                  br_blks, 
  'lf_rows_len  '||to_char(lf_rows_len,'999,999,990')||'        '||+ 
  'br_rows_len  '||to_char(br_rows_len,'999,999,990')                  br_rows_len, 
  'lf_blk_len   '||to_char(lf_blk_len, '999,999,990')||'        '||+ 
  'br_blk_len   '||to_char(br_blk_len, '999,999,990')                br_blk_len 
from 
  index_stats where index_name = 'yourindex'
/

bye

yk

Former Member
0 Kudos

Hello YK,

Thank you for the suggestion, I ran the SQL queries you mentioned but it returned with no rows selected, while running the query I replaced the 'Table' , 'Index' with the actual names of the large sized tables and corresponding index.

Thank you

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi,

What is the size of Index. Indexes can be reorganize online. If it is small then goto se38 and execute program RSANAORA. here you can reorg the Index.

However do this activity during offpeak hrs. otherwise you might face slight performance issue.

Regards,

Sachin Rane.