I may be late to the party on this one, but it certainly surprised me during a database migration from Oracle 11G to Oracle 19.26 recently (yes, there’s a surprising amount of databases still on 11G – released in 12 years ago, and 10G, and even 9i, 8i, 8.0 and even 7.2).
We were experiencing some performance issues. First question was, as usual, is this a result of anything that we have changed as part of the migration. We certainly made changes, as the original Oracle 11.2 database had a few underscore parameters set (always remove them when changing versions), as well as some SQL Plan Management (again, always remove/disable when upgrading too)
Anyway, after some analysis we discovered that the global optimizer statistics for a particular table were being set to 0 (i.e. the PARTITION statistics were untouched but the global rolled-up statistics for num_rows and blocks were 0) However, nobody was running dbms_stats jobs (confirmed using the unified_audit_trail) but the partitions for these “transient” tables were being truncated before the start of a batch run. And that is where the issue was taking place.
In Oracle 11, if you issue ALTER TABLE <table> TRUNCATE PARTITION <partition>, the partition is truncated and the statistics remain the same.
From Oracle 12 onwards (confirmed that 18/19/23ai are all affected), if you truncate a partition the GLOBAL stats (associated with the “parent” table) are modified to reflect the fact that you have less data.
How much are the stats changed?
The global figure for BLOCKS is reduced by the amount of BLOCKS (from the partition stats – dba_tab_statistics.blocks where partition_name=’…’)
The global figure for NUM_ROWS is reduced by a factor of: BLOCKS (for the partition) divided by the total blocks (from the “parent” table – dba_tab_statistics.blocks where partition_name IS NULL)
(note: this is BLOCKS value in the statistics, not the actual number of block allocated)
LAST_ANALYZED is updated to reflect this change. Stats may also be marked STALE if the percentage change exceeds the threshold (10% by default). Stats for the truncated partition are marked stale of course.
Partition level stats are otherwise unaffected.
Lets see that in action
create & populate a partitioned table:
> -- create table to get an uneven distribution of data in small extents
> CREATE TABLE test (owner VARCHAR2(128),object_name VARCHAR2(128), object_type VARCHAR2(23))
2 PARTITION BY RANGE (object_type)
3 (PARTITION test_part_e VALUES LESS THAN ('E') STORAGE(INITIAL 8K NEXT 8K),
4 PARTITION test_part_ev VALUES LESS THAN ('EV') STORAGE(INITIAL 8K NEXT 8K),
5 PARTITION test_part_m VALUES LESS THAN ('M') STORAGE(INITIAL 8K NEXT 8K),
6 PARTITION test_part_z VALUES LESS THAN ('Z') STORAGE(INITIAL 8K NEXT 8K)
7 )
8 /
Table created.
>
> -- populate with data
> INSERT INTO test SELECT owner,object_name,object_type FROM dba_objects;
73887 rows created.
>
> COMMIT;
Commit complete.
>
> -- gather stats
> exec dbms_stats.gather_table_stats(user,'TEST');
PL/SQL procedure successfully completed.
>
> -- wait so we can detect and change in last_analyzed
> host sleep 5
> -- stats after gather
> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.
>
> SELECT table_name,partition_name,num_rows,blocks,sample_size,last_analyzed,stattype_locked as locked, stale_stats as stale
2 FROM dba_tab_statistics
3 WHERE table_name = 'TEST'
4 ORDER BY owner,table_name,partition_name NULLS FIRST;
TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS SAMPLE_SIZE LAST_ANALYZED LOCKED STALE
---------- -------------------- ---------- ---------- ----------- ------------------- ------- -------
TEST 73887 750 73887 2025-08-04 14:19:55 NO
TEST TEST_PART_E 70 5 70 2025-08-04 14:19:55 NO
TEST TEST_PART_EV 1 5 1 2025-08-04 14:19:55 NO
TEST TEST_PART_M 45354 496 45354 2025-08-04 14:19:55 NO
TEST TEST_PART_Z 28462 244 28462 2025-08-04 14:19:55 NO
Now, what happens to the statistics if we truncate a partition
> -- truncate TEST_PART_EV
> alter table test truncate partition TEST_PART_EV;
Table truncated.
>
> -- after truncate, flush stats and check again
> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.
> SELECT table_name,partition_name,num_rows,blocks,sample_size,last_analyzed,stattype_locked as locked, stale_stats as stale
2 FROM dba_tab_statistics
3 WHERE table_name = 'TEST'
4 ORDER BY owner,table_name,partition_name NULLS FIRST;
TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS SAMPLE_SIZE LAST_ANALYZED LOCKED STALE
---------- -------------------- ---------- ---------- ----------- ------------------- ------- -------
TEST 73394 745 73887 2025-08-04 14:20:00 NO
TEST TEST_PART_E 70 5 70 2025-08-04 14:19:55 NO
TEST TEST_PART_EV 1 5 1 2025-08-04 14:19:55 YES
TEST TEST_PART_M 45354 496 45354 2025-08-04 14:19:55 NO
TEST TEST_PART_Z 28462 244 28462 2025-08-04 14:19:55 NO
We can see that the NUM_ROWS for the table GLOBAL_STATS has reduced from 73887 to 73394
Blocks: 750 – 5 = 745
Num Rows: 73887 – (73887 * (5/750)) = 73391.44
(so pretty close to 73394 – there’s a bit of rounding going on somewhere 🙂 )
and truncate the rest of the partitions
> -- truncate TEST_PART_Z
> alter table test truncate partition TEST_PART_Z;
Table truncated.
>
> -- and check stats
> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.
> SELECT table_name,partition_name,num_rows,blocks,sample_size,last_analyzed,stattype_locked as locked, stale_stats as stale
2 FROM dba_tab_statistics
3 WHERE table_name = 'TEST'
4 ORDER BY owner,table_name,partition_name NULLS FIRST;
TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS SAMPLE_SIZE LAST_ANALYZED LOCKED STALE
---------- -------------------- ---------- ---------- ----------- ------------------- ------- -------
TEST 49356 501 73887 2025-08-04 14:20:01 YES
TEST TEST_PART_E 70 5 70 2025-08-04 14:19:55 NO
TEST TEST_PART_EV 1 5 1 2025-08-04 14:19:55 YES
TEST TEST_PART_M 45354 496 45354 2025-08-04 14:19:55 NO
TEST TEST_PART_Z 28462 244 28462 2025-08-04 14:19:55 YES
>
> -- truncate
> alter table test truncate partition TEST_PART_M;
Table truncated.
>
> -- and check stats
> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.
> SELECT table_name,partition_name,num_rows,blocks,sample_size,last_analyzed,stattype_locked as locked, stale_stats as stale
2 FROM dba_tab_statistics
3 WHERE table_name = 'TEST'
4 ORDER BY owner,table_name,partition_name NULLS FIRST;
TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS SAMPLE_SIZE LAST_ANALYZED LOCKED STALE
---------- -------------------- ---------- ---------- ----------- ------------------- ------- -------
TEST 492 5 73887 2025-08-04 14:20:01 YES
TEST TEST_PART_E 70 5 70 2025-08-04 14:19:55 NO
TEST TEST_PART_EV 1 5 1 2025-08-04 14:19:55 YES
TEST TEST_PART_M 45354 496 45354 2025-08-04 14:19:55 YES
TEST TEST_PART_Z 28462 244 28462 2025-08-04 14:19:55 YES
>
> -- truncate
> alter table test truncate partition TEST_PART_E;
Table truncated.
>
> -- after truncates
> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.
> SELECT table_name,partition_name,num_rows,blocks,sample_size,last_analyzed,stattype_locked as locked, stale_stats as stale
2 FROM dba_tab_statistics
3 WHERE table_name = 'TEST'
4 ORDER BY owner,table_name,partition_name NULLS FIRST;
TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS SAMPLE_SIZE LAST_ANALYZED LOCKED STALE
---------- -------------------- ---------- ---------- ----------- ------------------- ------- -------
TEST 0 0 73887 2025-08-04 14:20:01 YES
TEST TEST_PART_E 70 5 70 2025-08-04 14:19:55 YES
TEST TEST_PART_EV 1 5 1 2025-08-04 14:19:55 YES
TEST TEST_PART_M 45354 496 45354 2025-08-04 14:19:55 YES
TEST TEST_PART_Z 28462 244 28462 2025-08-04 14:19:55 YES
trunc TEST_PART_Z: 733994 – (73394 * (244/745)) = 49356 (49356 – matched)
trunc TEXT_PART_M: 49356 – (49356 * (496/501)) = 493 (492 – close)
trunc TEXT_PART_E: Blocks is now 0 so 0.
So at the end we can see we have global stats reducing and eventually reflecting 0 rows and 0 blocks, with all partition stats intact.
What if I don’t want this? What if I only want the stats to change when I gather them? What if I have a table that I want stats on because it gets truncated every day and then filled and processed? What if I need it to work like in Oracle 11G!
Fortunately, you can switch it off with a fix control
alter session set "_fix_control"='28135957:0';
This will allow your session to test what’s going on.
For a permanent global fix:
alter system set "_fix_control"='28135957:0' comment='retain global stats like 11G' scope=both sid='*';
Happy Truncating!
Whilst I have not shown them, the statistics for the indexes on the test run above were not changed; neither global not local index stats were modified.
And to swing back to the start, removing the underscore parameters and ALL of the SQL Plan Management for this 11G to 19C upgrade has been fine. None of it is needed to allow this database to perform; just a couple of new indexes and some nice timely statistics.
Many thanks to Nigel Bayliss – the Oracle Optimizer PM – again, informing me of the fix control, and also for pointing out to me that he has, in fact, mentioned this change in behaviour between 11G and 19C at conferences. Something I have forgotten (and I could not locate this feature in the manuals either). Go to conferences – you meet so many good and helpful people, and occasionally learn something too.
UPDATE 2025-08-11
Based upon Piotr’s comment and further discussions, Oracle seems to be trying to keep the global stats as up to date as possible when performing partition-wise actions. This is generally going to be a good thing. I understand the reasoning behind using blocks rather than num_rows, and whilst I think the solution could be more elegant it makes a lot of sense when you factor in more variables, such as Autonomous Databases. Indeed, all of it make a lot of sense but I think I’d like a control (global/table stat preference?) to be able to switch oracle from trying to maintain global stats during partition-wise actions and have the option of just leaving the stats alone. This would also automatically mean these actions would become documented as part of the stats preference (my main object to the current scenario is the lack of transparency) and I could enable it at a granular level. There are some tables which will definitely benefit from this, and some where it causes a lot of problems.
(Photo by Our Life in Pixels on Unsplash)
Neil Chandler's DB Blog 
Leave a comment