Any tool to give the statistics of table DML?

[zxmgh]

Hi, this is a following up of the post: "Tricky Question"

Is there any tool that can give the statistics of DML of a specific table, i.e., how many insert, delete, update transactions during the last xxx seconds on table xxx.

I know that /rdbms/admin/catio.sql can create a sample_io procedure can give IO statistics, but no DML information.

What about other tools like STATSPACK, tkprof, OEM Perf Manager or third party software?

If not, how to create a stored procedure to achieve this?

Thanks

[zxmgh]

And maybe Log Miner, SET_SQL_TRACE_IN_SESSION, Oradebug, Autotrace?

[pando]

alter table xxx monitoring and look dba_modifications

it is NOT real time

if you want real time create your triggers

[zxmgh]

pando:

non-realtime statistics is useless to us.
I have no problem to create a trigger like this:

CREATE OR REPLACE TRIGGER {trigger name}
AFTER|BEFORE INSERT OR DELETE OR UPDATE ON {table name}

DECLARE
/*my code*/
BEGIN
END {trigger name};

but the problem is that how can i store the number of DML transactions somewhere? Since I can't issue COMMIT in the trigger body.

[pando]

you can if you use autonomous transaction

[zxmgh]

pando:

You mean I can do like this:

CREATE OR REPLACE TRIGGER {trigger name}
AFTER INSERT OR DELETE OR UPDATE ON {table name}

DECLARE
pragma AUTONOMOUS_TRANSACTION;
dmlnumber number;
BEGIN
SELECT col1 INTO dmlnumber FROM stat_dml WHERE col2 = {table name};
dmlnumber = dmlnumber + 1;
UPDATE stat_dml SET col1 = :dmlnumber WHERE col2 = {table name};
COMMIT;
END {trigger name};

[pando]

should be able