DBMS_STATS.GATHER_STATS for tables schemas indexes columns

 

Action	Full Command	Description
Update statistics for a specific table	EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');	Updates statistics for the EMPLOYEES table in the HR schema.
Update statistics for a table and its indexes	EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES', cascade => TRUE);	Updates statistics for the EMPLOYEES table and all its indexes.
Update statistics for a specific index	EXEC DBMS_STATS.GATHER_INDEX_STATS('HR', 'EMP_SALARY_IDX');	Updates statistics for the EMP_SALARY_IDX index in the HR schema.
Update statistics for all tables in a schema	EXEC DBMS_STATS.GATHER_SCHEMA_STATS('HR');	Updates statistics for all tables and indexes in the HR schema.
Update statistics for the entire database	EXEC DBMS_STATS.GATHER_DATABASE_STATS;	Updates statistics for all schemas and tables in the database. (Time-consuming on large DBs)
Check last statistics update for a table	SELECT table_name, last_analyzed FROM user_tables WHERE table_name = 'EMPLOYEES';	Shows the last time statistics were gathered for the EMPLOYEES table.
Check last statistics update for an index	SELECT index_name, last_analyzed FROM user_indexes WHERE table_name = 'EMPLOYEES';	Shows the last time statistics were gathered for indexes of EMPLOYEES.
Update statistics in parallel for faster execution	EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES', degree => 4);	Runs statistics gathering in parallel for faster performance (using 4 CPU threads).
Update statistics for specific columns	EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES', columns => ('SALARY'));	Updates statistics only for the SALARY column.

---

Best Practices:

✔ Use cascade => TRUE to update both table and indexes.
✔ Use degree => 4 for faster execution on large tables.
✔ Schedule statistics updates during off-peak hours.

Would you like a PL/SQL script to automate this for multiple tables? 😊