Re: Stats on Huge Partitioned Table and ORA-01555
Date: Tue, 26 May 2015 14:48:57 -0500 (CDT)
Message-ID: <fee383d9306f87fdd3a3e0d967ec5185.squirrel_at_society.servebeer.com>
Michael writes:
> Also, I'm still looking for better ways to get stats gathered on this table > - and perhaps others.
[snip]
>>> Table has 7.1 billion+ rows, 503 partitions, 3TB
>>>
>>> I am relatively new to partitioned tables and am getting ORA-01555 errors
>>> during execution of dbms_stats.gather_database_stats_job_proc.
The stats gathering is the default, so stats on the entire table is done at once. It seems like you'll want to consider custom stats collection.
In addition to using a non-default values for the DEGREE (parallel) parameter that was already mentioned, there's a PARTITION parameter as well. If your partitions are temporal, where "older" ones are not changing, you could gather stats on just the "new" ones. You might be able to gain some insight into this from the DBA_TAB_MODIFICATIONS view.
Also note that an increase in the DEGREE parameter may (will?) increase TEMP usage for stats gathering.
HTH! GL! Rich
-- http://www.freelists.org/webpage/oracle-lReceived on Tue May 26 2015 - 21:48:57 CEST