Optimize Oracle Database

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:

exec DBMS_STATS.GATHER_DATABASE_STATS;

Leave a Comment