DB system statistics gathering – recommendations
That‘s what happened: RAM was added to the system (over 1T now), SGA_TARGET set to 960G and 12.2.0.1 db has been restarted. As before in all cases when the hardware resources were added we executed system stats gathering, for 3 hours. It was Sunday, system was working normally but wasn’t heavily loaded.
On the Monday morning we found that many critical queries were running much slower then before and it eventually caused db to hang out with over 1500 active/waiting processes. DB had to be restarted, system stats were set to defaults and, most importantly, database load has been decreased by stopping some morning tasks. From this moment on db workload was gradually decreasing and in 1 hour db got stable. Eventually.