Analyze Table & Indexes

CREATE OR REPLACE PROCEDURE ANALYZE_TABLE_INDEXES IS
CURSOR c_table_name
IS
SELECT table_name
FROM user_tables
WHERE TEMPORARY = 'N';

CURSOR c_index_name (table_name1 IN VARCHAR2)
IS
SELECT index_name
FROM user_indexes
WHERE table_name = table_name1 AND status = 'VALID';

owner_name VARCHAR2 (200);
BEGIN
SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER')
INTO owner_name
FROM DUAL;

FOR i IN c_table_name LOOP
DBMS_STATS.unlock_table_stats (ownname => owner_name,
tabname => i.table_name,
stattype => 'ALL'
);

BEGIN
DBMS_STATS.gather_table_stats
(ownname => owner_name,
tabname => i.table_name,
estimate_percent => DBMS_STATS.auto_sample_size,
CASCADE => DBMS_STATS.auto_cascade,
DEGREE => 2,
no_invalidate => DBMS_STATS.auto_invalidate,
granularity => 'AUTO',
method_opt => 'FOR ALL COLUMNS SIZE AUTO'
);

DBMS_OUTPUT.put_line ( 'Statistics gathered for table: '
|| i.table_name
);

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line
( 'Statistics can not gathered for table: '
|| i.table_name
);
END;

FOR j IN c_index_name (i.table_name) LOOP
BEGIN
DBMS_STATS.gather_index_stats
(ownname => owner_name,
indname => j.index_name,
estimate_percent => DBMS_STATS.auto_sample_size,
DEGREE => 2,
no_invalidate => DBMS_STATS.auto_invalidate,
granularity => 'AUTO'
);

DBMS_OUTPUT.put_line ( 'Statistics gathered for index: '
|| i.table_name
|| '.'
|| j.index_name
);

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line
( 'Statistics can not gathered for index: '
|| i.table_name
|| '.'
|| j.index_name
);
END;
END LOOP;

DBMS_STATS.lock_table_stats (ownname => owner_name,
tabname => i.table_name
);
END LOOP;
END;
/