how cost based opti. handle OLTP objects
In OLTP database, the objects (tables / indexes) will get updated frequently and the data change percent will cross 10 or 15 % within few minutes after the operation starts. I checked the percentage change using the following sql,
SELECT dtm.table_owner, dtm.table_name, dtm.partition_name,
ROUND ( (dtm.inserts + dtm.updates + dtm.deletes)
/ dt.num_rows,
2
)
* 100 "CHANGE_FACTOR",
dt.partitioned
FROM sys.dba_tab_modifications dtm, dba_tables dt
WHERE dtm.table_owner = dt.owner and dt.num_rows > 0
AND dtm.table_name = dt.table_name
order by change_factor;
I found the change_factor (ie, percentage of change) for few tables has crossed 10 lakhs (1 million) percent and many tables have the percentage change in 1000s. But so far we have not faced any major performance issue (sudden slowdown) in operation hours.