Overtime, the Oracle DB will become sluggish with the insert/update/deletes. So we need to optimize the DB so that the queries are faster.

To optimize a single table, use the following command:

exec DBMS_STATS.GATHER_TABLE_STATS (ownname => ‘&schema’ , tabname => ‘&tabname’, cascade => true);

To optimize a single schema and its indexes, use the following command:

EXEC dbms_stats.gather_schema_stats(‘&schema’, cascade=>TRUE);

— replace &schema with your schema name and &tabname with table name.


To optimize all the DB, use the following command:


Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.