February 16, 2015 1 Comment
Prior to 18.104.22.168, the optimizer history tables are unpartitioned and DBMS_STATS.PURGE_STATS has little choice but to do do a slow delete of stats before the parameterised input timestamp.
Why might you be purging? Here’s one such illustration:
This delete can be slow if these tables are large and there are a number of reasons why they might be so, notably if MMON cannot complete the purge within its permitted timeframe.
But note that if you’re happy to purge all history, there is a special TRUNCATE option if you make the call with a magic timestamp:
but Oracle Support emphasises that:
This option is planned to be used as a workaround on urgent cases and under the advice of Support…
Ah… the old magic value pattern / antipattern!
PURGE_ALL CONSTANT TIMESTAMP WITH TIME ZONE := TO_TIMESTAMP_TZ('1001-01-0101:00:00-00:00','YYYY-MM-DDHH:MI:SSTZH:TZM');
As part of the upgrade to 22.214.171.124, one gotcha is that these history tables become partitioned.
I don’t have a copy of 126.96.36.199 to hand but I do have 188.8.131.52 and the tables here are daily interval partitioned so I presume this is the same.
One plus side of this newly partitioned table is that the PURGE_STATS can now drop old partitions which is quicker than delete but a minor downside is that the tables have global indexes so the recursive/internal operations have to be done with UPDATE GLOBAL INDEXES
One curiosity in the trace file from this operation was this statement:
delete /*+ dynamic_sampling(4) */ from sys.wri$_optstat_histhead_history where savtime_date < to_date('01-01-1900', 'dd-mm-yyyy') and savtime not in (select timestamp '0000-01-01 00:00:00 -0:0' + sid + serial#/86400 from gv$session where status = 'ACTIVE' and con_id in (0, sys_context('userenv', 'con_id'))) and rownum <= NVL(:1, rownum)
This is deleting from the P_PERMANENT default partition but why is this necessary and what is that subquery all about, particularly the timestamp ‘0000-01-01 00:00:00 -0:0’ + sid + serial#/86400 bit?