Exclude table from gather stats job in Oracle
Suppose we did not want to include any table or index from the gather schema stats job.Some table are very big in size or archive tables we did not used in daily activity.So, Oracle does not provide anything to pass in exclude list as parameter in DBMS_STATS.GATHER_SCHEMA_STATS Package.
Note: don’t use FORCE=TRUE option in parameter it forcefully analyzed the stats.
Solution
We have two ways:
1. Lock the table stats for excluded from list of gather stats job.
2. Change the PUBLISH parameter for table to FALSE value with SET_TABLE_PREFS
1. Method: Lock the Table Example
We need to lock the table manually before going to execute the gather stats package. So it skip the locked object.
Following are the steps of example:
1. We login to TEST schema with credentials in SQLPLUS and get the list of all object present in TEST schema.
SQL> SELECT table_name,to_char(last_analyzed,'DD-MON-YYYY HH24:MI:SS') "LASTANALYZED" from user_tables;
TABLE_NAME LASTANALYZED --------------- --------------- TESTPRIMARY TESTING TESTFOREIGN PRIMARY_TEST FOREIGN_TEST TEST1
2. Skip the first two table from gather schema stats job.
SQL> exec dbms_stats.lock_table_stats('TEST','TESTPRIMARY');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.lock_table_stats('TEST','TESTING');
PL/SQL procedure successfully completed.
3. Now run the gather schema stats jobs for TEST schema.
SQL> exec dbms_stats.gather_schema_stats( ownname => 'TEST');
PL/SQL procedure successfully completed.
4. Check the last analyzed tables.
SQL> SELECT table_name,to_char(last_analyzed,'DD-MON-YYYY HH24:MI:SS') "LASTANALYZED" from user_tables;
TABLE_NAME LASTANALYZED --------------- ----------------------------- TESTPRIMARY TESTING TESTFOREIGN 04-MAR-2020 11:09:11 PRIMARY_TEST 04-MAR-2020 11:09:11 FOREIGN_TEST 04-MAR-2020 11:09:11 TEST1 04-MAR-2020 11:09:11
5. Check table locked with following command.
--If ALL returen in STATTYPE colum then its locked , If NULL the unlocked
select table_name,stattype_locked from user_tab_statistics
Note: First of all unlock the table for second Method test.
SQL> exec dbms_stats.unlock_table_stats('TEST','TESTPRIMARY');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.unlock_table_stats('TEST','TESTING');
PL/SQL procedure successfully completed.
2. Method: SET_TABLE_PREFS Procedure to use PUBLISH parameter as FALSE
We can set the PUBLISH parameter as FALSE value will skip the gather stats job to PUBLISH latest gather stats in Dictionary.
1. Check the PUBLISH parameter for TABLE_NAME.
--DBMS_STATS.get_prefs('parameter','schema_name', 'table_name' )
SQL> SELECT DBMS_STATS.get_prefs('PUBLISH','TEST', 'TESTING' ) FROM dual;
DBMS_STATS.GET_PREFS('PUBLISH','TEST','TESTING')
-------------------------------------------------
TRUE
2. Change the PUBLISH to FALSE.
--EXEC dbms_stats.set_table_prefs('Schema_name', 'table_name', 'parameter', 'false');
SQL> exec dbms_stats.set_table_prefs('TEST', 'TESTING', 'PUBLISH', 'false');
PL/SQL procedure successfully completed.
3. Gather stats for testing.
SQL> exec dbms_stats.gather_schema_stats( ownname => 'TEST');
PL/SQL procedure successfully completed.
4. Check the stats for tables left.
SQL> SELECT table_name,to_char(last_analyzed,'DD-MON-YYYY HH24:MI:SS') "LASTANALYZED" from user_tables;
TABLE_NAME LASTANALYZED --------------- ----------------------------- TESTPRIMARY 04-MAR-2020 11:20:31 TESTING TESTFOREIGN 04-MAR-2020 11:20:30 PRIMARY_TEST 04-MAR-2020 11:20:30 FOREIGN_TEST 04-MAR-2020 11:20:30 TEST1 04-MAR-2020 11:20:30
