stats, reorg, index rebuild

Index rebuild commands
APPS.sql
REM select 'alter index '||owner||'.'||INDEX_NAME||' rebuild online ;'
REM from dba_indexes where owner in ('APPS','APPLSYS') order by owner;
set time on;
set serverout on ;
spool APPS.lst
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS AM') FROM dual;
alter index APPLSYS.AD_APPLIED_PATCHES_N1 rebuild online ;
alter index APPLSYS.AD_APPLIED_PATCHES_N2 rebuild online ;


HR.sql
REM select 'alter index '||owner||'.'||INDEX_NAME||' rebuild online ;'
REM from dba_indexes where owner in 'HR' order by owner;
set time on;
set serverout on ;
spool HR.lst
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS AM') FROM dual;
alter index HR.AME_ACTIONS_FK1 rebuild online ;
alter index HR.AME_ACTIONS_PK rebuild online ;
alter index HR.SYS_IOT_TOP_2009617 rebuild online ;
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS AM') FROM dual;
spool off;
REM No of Rows selected 2941
alter index HR.SYS_IOT_TOP_2009617 rebuild online ;
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS AM') FROM dual;
spool off;
REM No of Rows selected 2941


CUSTOM.sql
REM select 'alter index '||owner||'.'||INDEX_NAME||' rebuild online ;'
REM from dba_indexes where
REM owner in ('MIGRATE','FOGLIGHT','NOETIX_SYS','RX_AM_AGENT',
REM 'IWATCH','USIDBA','MONITOR','CUST','SPOTLIGHT_APPS','XXCONV','TTEC_AUS')
REM order by owner;
set time on;
set serverout on;
spool CUSTOM.lst
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS AM') FROM dual;
alter index CUST.TTEC_H_ES_CONTRACT_N1 rebuild online ;
alter index CUST.TTEC_H_ES_COST rebuild online ;
alter index XXCONV.XXTTEC_CONV_ERRORS_U1 rebuild online ;

SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS AM') FROM dual;
spool off;

OTHER.sql

REM select 'alter index '||owner||'.'||INDEX_NAME||' rebuild online ;'
REM from dba_indexes where owner not in
REM ('MIGRATE','FOGLIGHT','NOETIX_SYS','RX_AM_AGENT',
REM 'IWATCH','USIDBA','MONITOR','CUST','SPOTLIGHT_APPS',
REM 'XXCONV','APPS','HR','TTEC_AUS','SYS','SYSTEM','CTXSYS','APPLSYS')
REM order by owner;
set time on;
set serverout on;
spool OTHER.lst
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS AM') FROM dual;
alter index ABM.ABM_CALC_PARAMS_LOG_N8 rebuild online ;
alter index ABM.ABM_CALC_PARAMS_LOG_N9 rebuild online ;
alter index ABM.ABM_CO_DAT_N1 rebuild online ;
alter index XXEIS.EIS_BALANCE_ASG_ID_IDX rebuild online ;
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS AM') FROM dual;
spool off;
REM Total No Of 36002 rows selected.

one eg command to compute statistics at the time of creation of index
create index applsys.WF_ITEMS_N3 on applsys.WF_ITEMS (ITEM_KEY, ITEM_TYPE, END_DATE)
tablespace APPS_TS_TX_IDX
compute statistics;


Gather statistics for SYS schema and dictionary tables
Use SQL*Plus to connect to the database as sysdba and gather the statistics for the SYS schema using the following commands:

SQL> execute dbms_stats.gather_schema_stats
('SYS',cascade=>TRUE,degree=>20);

"As of Oracle Database 10g Release 10.1, Oracle recommends that you use the
$ sqlplus '/as sysdba'
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;



For Oracle8i and Oracle9i, use the DBMS_STATS.GATHER_SCHEMA_STATS procedure to gather statistics.
Backup the existing statistics as follows:

$ sqlplus '/as sysdba'
SQL>spool sdict

SQL>grant analyze any to sys;

SQL>exec dbms_stats.create_stat_table('SYS','dictstattab');

SQL>exec dbms_stats.export_schema_stats('WMSYS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('MDSYS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('CTXSYS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('XDB','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('WKSYS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('LBACSYS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('OLAPSYS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('DMSYS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('ODM','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('ORDSYS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('ORDPLUGINS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('SI_INFORMTN_SCHEMA','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('OUTLN','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('DBSNMP','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('SYSTEM','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('SYS','dictstattab',statown => 'SYS');

SQL>spool off
This data is useful if you want to revert back the statistics

For example, the following PL/SQL subprograms import the statistics for the SYS schema after deleting the existing statistics:

exec dbms_stats.delete_schema_stats('SYS');
exec dbms_stats.import_schema_stats('SYS','dictstattab');

To gather statistics run this script, connect to the database AS SYSDBA using SQL*Plus.
$ sqlplus '/as sysdba'

SQL>spool gdict

SQL>grant analyze any to sys;

SQL>exec dbms_stats.gather_schema_stats('WMSYS',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('MDSYS',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('CTXSYS',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('XDB',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('WKSYS',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('LBACSYS',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('OLAPSYS',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('DMSYS',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('ODM',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('ORDSYS',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('ORDPLUGINS',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('SI_INFORMTN_SCHEMA',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('OUTLN',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('DBSNMP',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('SYSTEM',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('SYS',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

SQL>spool off