Database Tuning (MOSC)

MOSC Banner

how cost based opti. handle OLTP objects

edited Jul 8, 2009 2:01AM in Database Tuning (MOSC) 4 commentsAnswered ✓
I have one question,

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.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center