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;