Skip to Main Content

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

Question and Answer

Connor McDonald

Thanks for the question, nikoloz.

Asked: April 11, 2025 - 6:24 pm UTC

Last updated: April 14, 2025 - 7:05 am UTC

Version: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.14.0.0.0

Viewed 1000+ times

You Asked

hello I have a table FCM PARTITIONED ON (Q) Column BY RANGE INTERVAL( NUMTODSINTERVAL(7, 'DAY'))

i have an issue that "current/active" partition becomes stale several times a day
vast majority of DMLs on the table are INSERTS (basic inserts , with values , something like insert into table values :1 :2 )

while we do have a maintenance window , it is scheduled at night time , so during day we often have Stale statistics for current/active partition

here are the questions :
1.
is there any optimal way (something like trigger ) so that i can gather statistics on the partition AS SOON as it becomes stale ,

while it is possible to schedule a manual job to check for stale partitions , it still leaves the window between jobs , where partition can become stale , i dont really like the idea of a job running every 20 minutes , but as a last resort it can also be done .



and Connor said...

If you are running an engineered system, you could take a look at real time statistics on DML

https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/optimizer-statistics-concepts.html#GUID-769E609D-0312-43A7-9581-3F3EACF10BA9

eg

create table t1 (
  id           number,
  my_varchar2  varchar2(50)
);

insert /*+ append */ into t1 (id, my_varchar2)
select level, 'some data ' || level
from   dual
connect by level <= 5000;
commit;

select table_name,
       num_rows,
       notes
from   user_tab_statistics
where  table_name = 'T1';

TABLE_NAME   NUM_ROWS NOTES
---------- ---------- -------------------------
T1               5000

insert into t1
select level+5000,
       'more data ' || level || level
from   dual
connect by level <= 1000;
commit;

select table_name,
       num_rows,
       notes
from   user_tab_statistics
where  table_name = 'T1';

TABLE_NAME   NUM_ROWS NOTES
---------- ---------- -------------------------
T1               5000
T1               6000 STATS_ON_CONVENTIONAL_DML


but yes, a DDL trigger that fires when partitions are created is an enhancement request that's been in the queue for a number of years.

As it currently stands, you'd need to have that job setup to watch for it.




Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library