1. How do we find out the indexes that are to be rebuild?

You can use sapdba --> reorganization --> check extents and fragmentation.

2. Is there any tcode available in SAP to rebuild indexes or should we do it only at oracle level?

You can also do it at oracle level, but using sapdba is the best option to have sync between oracle and sap.

3. What are the precautionary steps before rebuilding the indexes?

a. Update statistics to latest.
b. Enough space in Tablespace or filesystem depends on the reorg method
c. Stop SAP and start the database in restricted mode so that no process is having any locks on that index nor any process will try use the index when performing the reorg.

I wouldn't rebuild indexes using SAP, unless you have to. It will likely lock tables, so you would need system offline.

At Oracle 8.1.7 you can (usually but not always, depending upon the nature of the index)

alter index <indexname> rebuild online ;

zero (well, as close as you'll get... ) impact on the running system.

You can also parallelise it like:

alter index <indexname> rebuild online parallel 10 ;

Don't think its quite the same as a proper drop index plus create index, like sapdba does, mind you. But most of the benefits, none of the drawbacks.
 

I rebuild all my SAP indices and then run stats afterwards once a month using cron'ed jobs.

Because there are so many SAP indices and the outputsize is limited, I have to break up the job into 3 ranges, A-J, K-R, and R up. I use this script:

rem ----------------Rebuild Script Start---------------------------------
rem -----------------------------------------------------------------------
rem Filename: idxrebld.sql
rem Purpose: Re-build all user indexes on-line
rem Notes: - Run this script from sqlplus as a DBA user.
rem -----------------------------------------------------------------------

set serveroutput on size 1000000
set line 1024 feed off trimspool on echo off
spool index_rebuild_runAJ.sql

begin
for c1 in (
select i.owner, i.index_name, s.tablespace_name,
i.initial_extent, i.next_extent, i.min_extents, i.max_extents,
i.pct_increase, s.bytes
from sys.dba_segments s, sys.dba_indexes i
where s.segment_type = 'INDEX'
and i.index_name = s.segment_name
and i.index_name between 'A%' and 'J%'
and i.owner = 'SAPR3' order by i.index_name )
loop

dbms_output.put_line('ALTER INDEX "'||c1.owner||'"."'||c1.index_name||
'" REBUILD;');

end loop;
end;
/

spool off

-- Run the generated script
set echo on time on timing on feed on
spool index_rebuildAJ
@@index_rebuild_runAJ
spool off

rm index_rebuild_runAJ.sql

exit
rem ----------------Rebuild Script End---------------------------------

Read also:
Names of  SAP Standard Admin Jobs

SAP Basis Reference Books:
SAP Basis Components, System Administration, Security, ALE and iDoc Books

Back to Basis Menu:
SAP BC (Basis Components) Hints and Tips

Return to :-
SAP ABAP/4 Programming, Basis Administration, Configuration Hints and Tips