DBMS Stats parameter Question
Hi Experts - We are in Oracle EXADATA 12.2.0.1 (Apr cpu2020).
could you please help me to understand why there is a major difference in timings when we gather the statistics. the table has 28 million rows with 150+ columns.
SQL> EXEC DBMS_STATS.DELETE_TABLE_STATS(OWNNAME=>'TEST', TABNAME=>'TEST_TABLE');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.09
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname => '"TEST"', tabname => '"TEST_TABLE"', cascade => TRUE, no_invalidate => FALSE, force => FALSE); <<<< Took 3 Hour 53 minutes
PL/SQL procedure successfully completed.
Elapsed: 03:53:01.92
SQL> EXEC DBMS_STATS.DELETE_TABLE_STATS(OWNNAME=>'TEST', TABNAME=>'TEST_TABLE');
PL/SQL procedure successfully completed.