Any tool to give the statistics of table DML?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Any tool to give the statistics of table DML?

  1. #1
    Join Date
    Apr 2001
    Posts
    125

    Question

    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

  2. #2
    Join Date
    Apr 2001
    Posts
    125
    And maybe Log Miner, SET_SQL_TRACE_IN_SESSION, Oradebug, Autotrace?
    Oracle 8, 8i, 9i OCP DBA
    Oracle 6/6i OCP DEV
    Sun Solaris8 SCSA
    MCDBA 2000

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    alter table xxx monitoring and look dba_modifications

    it is NOT real time

    if you want real time create your triggers

  4. #4
    Join Date
    Apr 2001
    Posts
    125
    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.


  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you can if you use autonomous transaction

  6. #6
    Join Date
    Apr 2001
    Posts
    125
    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};

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    should be able

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width