on 03-13-2009 7:26 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
"
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
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
> - 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
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
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
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.