Confused about using PURGE_ALL to remove optimizer statistics from the SYSAUX ?
Posted by FatDBA on November 7, 2021
Hi All,
Recently while working in one for the project where SYSAUX was growing at an alarming rate, customer operations team decided to purge old stats from the SYSAUX as that was occupying around 50% of the total occupancies. Though MMON purges these stats regularly from the OPTSTAT tables, but due to a known bug (14373728) it failed to that. I mean ideally it should purge these statistics according to the default retention, which is 30 days in this case, but it seem it can not due to that bug. MMON performs these purge activities automatically, but it has a limit of 5 minutes to perform these activities. So if the purging takes more than 5 mins, then the activities are aborted and as a result the stats are not purged.
Due to some reasons they don’t want to apply the patch, but agreed to purge all stats from the OPTSTAT related tables within SYSAUX. But, the team was very perplexed to perform the PURGE_ALL as a big-bang (not using sysdate-x) approach, as they weren’t sure if PURGE_ALL will remove all the stats including current statistics on objects (Tables, Indexes etc.) or it only touches OPTSTAT tables and truncates them.
About optimizer stats tables (OPTSTAT in short), they contain backup information about stats collection and involves tables like WRI$_OPTSTAT_OPR, WRI$_OPTSTAT_AUX_HISTORY, WRI$_OPTSTAT_TAB_HISTORY, WRI$_OPTSTAT_IND_HISTORY, WRI$_OPTSTAT_HISTGRM_HISTORY, WRI$_OPTSTAT_HISTHEAD_HISTORY.
Even the comments from Oracle customer support has raised the confusion, as their explanation was too hazy and with lot of muddiness about using EXEC DBMS_STATS.PURGE_STATS(DBMS_STATS.PURGE_ALL). So, I decided to do a quick demo to clear all the doubts around this subject.
All below mentioned tests were performed on Oracle 21c database.
-- Here I first created a sample table and generated 10000 random rows.
[oracle@fatdba ~]$ sqlplus / as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Wed Nov 3 23:53:14 2021
Version 21.3.0.0.0
SQL> CREATE TABLE tab1 AS
SELECT level AS id,
CASE
WHEN MOD(level, 2) = 0 THEN 0
ELSE TRUNC(DBMS_RANDOM.value(1,10))
END AS record_type,
'Description for ' || level AS description
FROM dual
CONNECT BY level <= 10000;
Table created.
SQL> select count(*) from tab1;
COUNT(*)
----------
10000
SQL> ALTER TABLE tab1 ADD CONSTRAINT tab1_pk PRIMARY KEY (id);
Table altered.
SQL> CREATE INDEX tab1_record_type_idx ON tab1(record_type);
Index created.
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'TAB1');
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_STATS.gather_index_stats(USER, 'TAB1_RECORD_TYPE_IDX');
PL/SQL procedure successfully completed.
Okay, so we have a table ready with 10000 random rows with a primary key constraint and have created one index on record_type column and have gathered stats on the table and the index. Next, I will purge all stats from OPTSTAT tables using PURGE_ALL function
23:13:05 SQL> EXEC DBMS_STATS.PURGE_STATS(DBMS_STATS.PURGE_ALL);
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.13
23:13:09 SQL>
-- Now the same can be confirmed by querying OPTSTAT tables WRI$_OPTSTAT_IND_HISTORY and WRI$_OPTSTAT_TAB_HISTORY for Tables and Indexes,
-- And they all are gone, no more data is there in these tables after flush.
23:15:55 SQL> select OBJ#, flags, rowcnt, ANALYZETIME from WRI$_OPTSTAT_IND_HISTORY where obj#=77267;
no rows selected
23:16:01 SQL> select FLAGS,ROWCNT, OBJ#,ANALYZETIME from WRI$_OPTSTAT_TAB_HISTORY where obj#=77256;
no rows selected
Now, lets check if tables and index has the stats collection date coming or not, and yes they are there! means still the table has their current stats and didn’t got purged after we executed PURGE_ALL on OPTSTAT tables.
23:13:37 SQL> select index_name, index_type, last_analyzed from dba_indexes where index_name='TAB1_RECORD_TYPE_IDX';
INDEX_NAME INDEX_TYPE LAST_ANALYZED
------------------------------ --------------------------- --------------------
TAB1_RECORD_TYPE_IDX NORMAL 03-NOV-2021 23:12:07
Elapsed: 00:00:00.00
23:13:50 SQL> select table_name, last_analyzed from dba_tables where table_name='TAB1';
TABLE_NAME LAST_ANALYZED
-------------------- --------------------
TAB1 03-NOV-2021 23:08:32
Elapsed: 00:00:00.00
I am now going to recollect stats on both of them, Tables and its Index, and with that, the last_analyzed date/time is refreshed with the latest timestamp.
repeating same steps for TABLE. And one more time, I see that the TABLE HISTORY related OPTSTAT table has only previous/historical values not the latest timestamp from stats collection.
I mean it should should have ’03-NOV-2021 23:26:07′ as a latest entry in the table, but has only got ’03-NOV-2021 23:17:28′ when last stats were collected.
23:25:54 SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'TAB1');
PL/SQL procedure successfully completed.
23:26:15 SQL> select table_name, last_analyzed from dba_tables where table_name='TAB1';
TABLE_NAME LAST_ANALYZED
-------------------- --------------------
TAB1 03-NOV-2021 23:26:07
23:26:08 SQL> select FLAGS,ROWCNT, OBJ#,ANALYZETIME from WRI$_OPTSTAT_TAB_HISTORY where obj#=77256;
FLAGS ROWCNT OBJ# ANALYZETIME
---------- ---------- ---------- --------------------
10 10000 77256 03-NOV-2021 23:08:32
10 10000 77256 03-NOV-2021 23:17:28 ----> previous value, not the latest timestamp of 03-NOV-2021 23:26:07
-- One more time, for Table ....
23:29:05 SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'TAB1');
PL/SQL procedure successfully completed.
23:29:11 SQL> 23:29:11 SQL> select table_name, last_analyzed from dba_tables where table_name='TAB1';
TABLE_NAME LAST_ANALYZED
-------------------- --------------------
TAB1 03-NOV-2021 23:29:07
23:29:07 SQL> select FLAGS,ROWCNT, OBJ#,ANALYZETIME from WRI$_OPTSTAT_TAB_HISTORY where obj#=77256;
FLAGS ROWCNT OBJ# ANALYZETIME
---------- ---------- ---------- --------------------
10 10000 77256 03-NOV-2021 23:08:32
10 10000 77256 03-NOV-2021 23:17:28
10 10000 77256 03-NOV-2021 23:26:07 ----> previous value, not the latest timestamp of 03-NOV-2021 23:29:0
Let’s do this on Indexes!
23:32:26 SQL> EXEC DBMS_STATS.gather_index_stats(USER, 'TAB1_RECORD_TYPE_IDX');
PL/SQL procedure successfully completed.
23:32:34 SQL> select index_name, index_type, last_analyzed from dba_indexes where index_name='TAB1_RECORD_TYPE_IDX';
INDEX_NAME INDEX_TYPE LAST_ANALYZED
------------------------------ --------------------------- --------------------
TAB1_RECORD_TYPE_IDX NORMAL 03-NOV-2021 23:32:27
23:32:33 SQL> select OBJ#, flags, rowcnt, ANALYZETIME from WRI$_OPTSTAT_IND_HISTORY where obj#=77267;
OBJ# FLAGS ROWCNT ANALYZETIME
---------- ---------- ---------- --------------------
77267 10 10000 03-NOV-2021 23:12:07
77267 10 10000 03-NOV-2021 23:31:37 ---> Has only got '03-NOV-2021 23:31:37' which is a previous timestamp/history, not the latest of 03-NOV-2021 23:32:27
So, from above tests it’s clear that the PURGE_ALL only touches and truncates WRI$_OPTSTAT_XXXX tables and as they always have the previous stats collection details, your present stats on objects will always be there, untouched and safe. So, this command is very safe to run and is best when you are thinking of doing an iterative purge using SYSDATE-X as it’s always very slow as compared to PURGE_ALL which truncates the table and doesn’t delete records from it, and the reason of its agility.
Hope It Helped!
Prashant Dixit
Tales From A Lazy Fat DBA 
Leave a Reply