Performance issue in gathering Stats
We are in the process of upgrading our database from 11.2.0.4 to 12.2.0.1.
We have few batch jobs which uses a table contains 32 million records. At the last step it gathers stats using below sql .
GATHER_TABLE_STATS(ownname => '"TEST_SCHEMA"', tabname => '"TEST_TABLE"', cascade => TRUE, no_invalidate => FALSE, force => FALSE);
Degree for this table is set to 1. When this steps runs its running 72 process in each node and can see total of 144 process.
In EXADATA 11.2.0.4(APR2020CPU) 2 Node RAC ==> It takes about 6 minutes consistently to gather the steps.
In EXADATA 12.2.0.1(April 2020 CPU) Single Node==> It takes about 6 minutes consistently to gather the stats.
1