Database Administration (MOSC)

MOSC Banner

DBMS Stats parameter Question

edited Aug 10, 2020 2:38PM in Database Administration (MOSC) 18 commentsAnswered ✓

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.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center