Hi All -
Is there a way to exclude a particular schema from gather stats? We have a bit of an odd situation where our ETL is causing issues with the database gather stats job. Long-story-short, we end up with a deadlock situation where multiple operations are trying to access the same table. This causes the ETL to fail. Database support confirmed it is some index rebuild tasks and the auto stats job "colliding". This doesn't always happen, only when the two operations coincide at the same time. It does happen often enough (once or twice a week) to cause us some headaches.
Interestingly enough, the application team confirmed for us that the ETL runs its own gather stats steps as part of its process. It does so after it makes inserts and/or updates on a particular table (as part of a table maintenance routine). So, we really don't need the database auto stats job at all? Or, do we? Because, I was thinking that, the ETL runs gather stats only on the data warehouse tables (all located in one schema). If we disabled the auto stats entirely, we may not get statistics on non-warehouse objects (e.g. there is a meta-data repository housed in its own schema; plus, I'm am not sure if other system objects still need statistics, etc.)? So, circling back ... is it possible to exclude the data warehouse schema from the gather stats job?
Other possible solutions:
1) Change the ETL logic to not collect statistics (thus, just letting it happen at the system level). No issues since only one gather stats would be happening. -- Thought about this. Possible, but a painful process to alter the ETL logic. Also, not 100% sure it is supported.
2) Change the time of the ETL runtime. -- We picked the current time so that it would run in between US & India work shifts. Sooner or later would impact one of the teams.
3) Change the auto stats job window. -- Possible. But, not sure ... Core DBA team would have the final say. Plus, it may be hard to find an alternate window
Also, would locking the statistics help? I saw that this could be done at the schema level, which would prevent auto stats. But, would this also prevent the ETL's gather stats job (DBMS_STATS.GATHER_TABLE_STATS)?
DB Version - 12.2.0.1
Regards,
Charles