-
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
-
And maybe Log Miner, SET_SQL_TRACE_IN_SESSION, Oradebug, Autotrace?
-
alter table xxx monitoring and look dba_modifications
it is NOT real time
if you want real time create your triggers
-
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.
-
you can if you use autonomous transaction
-
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};
-