Oracle Scratchpad

April 7, 2013

DML Tracking

Filed under: Oracle,Troubleshooting — Jonathan Lewis @ 10:04 pm BST Apr 7,2013

You’ve probably seen questions on the internet occasionally about finding out how frequently an object has been modified. The question is a little ambiguous – does it mean how much change has occurred, or how many DML statements have been executed; either may be an interesting measure. Of course, Oracle gave us a method of answering the first question a long time ago: v$segstat (or v$segment_statistics if you don’t mind suffering the join) and the resulting content in the AWR or Statspack reports:

Segments by DB Blocks Changes   DB/Inst: XXXXXXXX/XXXXXXXX  Snaps: 85563-85564
-> % of Capture shows % of DB Block Changes for each top segment compared
-> with total DB Block Changes for all segments captured by the Snapshot

           Tablespace                      Subobject  Obj.      DB Block    % of
Owner         Name    Object Name            Name     Type       Changes Capture
---------- ---------- -------------------- ---------- ----- ------------ -------
XXXXXXXXX  DATA       MLOG$_xxxxxxxxxxxxxx            TABLE    1,144,112   22.16
XXXXXXXXX  DATA       PK_xxxxxxxxxxxxxxxxx            INDEX      614,256   11.90
XXXXXXXXX  DATA       xxxxxxxxxxxxxxx                 TABLE      490,080    9.49
XXXXXXXXX  IDX        IDX_xxxxxxxxxxxxxxxx            INDEX      353,392    6.84
XXXXXXXXX  IDX        PK_yyyyyyyyyyyyyyy              INDEX      273,664    5.30

There’s still a little ambiguity in this report (in the right circumstances you can change multiple rows in a block while only reporting a single block change), but it’s a very good indicator of the volume of change to an object; but is the million or so block changes due to one SQL statement or 1,000,000 SQL statements ?

With there is an (undocumented) option for finding the answer to that question. Since it’s undocumented I wouldn’t use it on production unless I was really desperate, and even then I’d do the standard “check with Oracle support” first; however I might use it on a test system if I were do something like running tests of a new version of an overnight suite of batch jobs.

You can enable “DML frequency tracking” which seems to count the number of DML statements that modify an object or, to be more accurate, a table. It doesn’t do anything to identify indexes, although it will report index organized tables (using the table name); it won’t identify partition-level DML individually for partitioned tables, it will simply record the DML as being relevant to the table.

To enable the feature you modify hidden parameter _dml_frequency_tracking and then query dynamic performance view v$object_dml_frequencies.

alter system set "_dml_frequency_tracking"=true;

	object, working, slot0, slot1, slot2, slot3, slot4
order by
	working + slot0 + slot1 + slot2 + slot3

The view has 25 columns for numbered “slots”, and a “working” column. Each slot represents a 15 minute interval (set by parameter _dml_frequency_tracking_slot_time) and only 4 slots are maintained by default (set by parameter _dml_frequency_tracking_slots). The values roll from slot to slot every 15 minutes, and the “working” column keeps track of objects that have suffered DML since the last roll-over. Unfortunately there doesn’t seem to be any obvious way of tracking when a roll-over takes place, so you can’t tell how long the “working” column has been active for.

If you have the default 4 slots set, then “working” and slots 0 to 2 are populated with 15 minutes worth of information each, and slot3 seems to be the accumulated history of all accesses since you enabled the feature.

The view sits on top of x$ksxm_dft, but there are no interesting extra columns in the x$ that aren’t displayed in the v$ and gv$. (It’s an interesting little quirk that the v$ doesn’t following the usual pattern of being a view of the gv$ restricted to the current instance.) You can see the information in the shared pool (by querying v$sgastat) in an area named “dml frequency” – although the figure reported the last time I checked was wrong by a factor of roughly 3 – show about 8KB when I was expecting roughly 24KB.

I haven’t spent a lot of time investigating the feature yet, so there are probably a few questions and boundary conditions to test before using it in anger; but there don’t appear to be any special latches associated with the counters (so possibly the results would be under-count in a highly concurrent system, but maybe the results are collected under a mutex).

Related parameters

_dml_frequency_tracking (false) Control DML frequency tracking — can be set at system level on demand
_dml_frequency_tracking_advance (true) Control automatic advance and broadcast of DML frequencies — ?
_dml_frequency_tracking_slot_time (15) Time length of each slot for DML frequency tracking — needs restart
_dml_frequency_tracking_slots ( 4) Number of slots to use for DML frequency tracking — needs restart


  1. How does this differ from tracking changes against a table using dba_tab_modifications?

    Comment by Jon — April 8, 2013 @ 12:43 pm BST Apr 8,2013 | Reply

    • Jon,

      A couple of key differences:

      It keeps track of the number of DML statements executed while dba_tab_modifications keeps track of the number of rows inserted, updated or deleted; the difference in viewpoints can be very helpful.

      It keeps track of multiple 15 minute windows, while dba_tab_modifications keeps track of the total change since the last time you collected stats on the table (or flushed the monitoring information explicitly with a call to dbms_stats.flush_database_monitoring_info).

      Comment by Jonathan Lewis — April 8, 2013 @ 4:49 pm BST Apr 8,2013 | Reply

  2. I think you can get this yourself. from v$sql_plan by using hourly snapshots and then just looking for new plan_hash_values
    to find update,deletes:

    selecta a.sql_id,a.child_number,object_name,object_owner,operation,parsing_schema_name,executions
    from v$sql_plan a, v$sql b
    where id = 1
    and operation in (‘UPDATE’,’DELETE’)

    — I add parsing_schema_Name because I think that will tell me which user is performing the DML.

    inserts are trickier. You have to parse them out of v$sql.sql_text. I dont think you need to scan through sql_fulltext because I think inserts should be at the beginning of the statement. If the insert is in pl/sql, then that insert statement will spawn a seperate sql_id.

    Do you know if there is a way to get the table_name you insert to out of the the data dictionary without parsing? For ‘insert into table_name values’ this gets a plan_hash_value = 0 so it does not go to v$sql_plan. I am pretty sure I can put together a regular expression to parse out the table_name (need to account for hints and extra spaces), but if there is an easier way, I’d rather not do the extra work.

    You can build this by taking hourly snapshots of v$sql_plan then ‘diff’ (use a minus) the current v$sql_plan with your last snapshots, to find new plan_hash_value, then parse out the updates,deletes by table. So you can do your own calculations. This way you dont have to use an undocumented feature. I want to extend this to inserts as well. Then I want to see if there is a way to figure out how much redo I am generating per statement and store that. I am not sure if this last option is possible in which is the version that I am on.

    Comment by Ryan — July 10, 2013 @ 6:09 pm BST Jul 10,2013 | Reply

    • Ryan,

      Nice idea, but there are always problems with looking at v$sql and v$sql_plan; cursors can close, get flushed, get cloned etc, and some of the statistics can get duplicated or lost (depending on version and bugs). the diff

      I don’t know of any published information that would help for inserts, although 12c does seem to have v$sql_plan information for insert values(), and you can track from V$sql to v$object_dependency for insert statements. There are still odd gaps, of course – including the absence of an object_name in the insert plan, and problem of dealing with “insert into synonym” or “insert into view”.

      Comment by Jonathan Lewis — July 10, 2013 @ 9:33 pm BST Jul 10,2013 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by