flush buffer_cache 效果明显

大表count

SQL> select count(*) from tablea;

  COUNT(*)
----------
  68106892

Executed in 1.811 seconds

SQL>  alter system flush buffer_cache;

System altered

Executed in 8.335 seconds

SQL> select count(*) from tablea;

  COUNT(*)
----------
  68106892

Executed in 10.424 seconds

SQL> select count(*) from tablea;

  COUNT(*)
----------
  68106892

Executed in 2.248 seconds

SQL> 

大表enable check Constraint

19s到160s的 差异

SQL> alter table tablea enable constraint tablea_NN6;

Table altered

Executed in 19.904 seconds

SQL> alter system flush buffer_cache;

System altered

Executed in 16.42 seconds

SQL> alter table tablea enable constraint tablea_NN8;

Table altered

Executed in 129.324 seconds

SQL> alter table tablea enable constraint tablea_NN9;

Table altered

Executed in 17.395 seconds

SQL> alter system flush buffer_cache;

System altered

Executed in 6.743 seconds

SQL> alter table tablea enable constraint tablea_NN10;

Table altered

Executed in 164.962 seconds

SQL> alter table tablea enable constraint tablea_ATTR_2;

Table altered

Executed in 20.9 seconds

SQL> alter system flush buffer_cache;

System altered

Executed in 7.204 seconds

SQL>  alter table tablea enable constraint tablea_ATTR_3;

Executed in 164.714 seconds