1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to monitor dml's in a table

Discussion in 'SQL PL/SQL' started by Shanmugapriya, Oct 13, 2016.

  1. Shanmugapriya

    Shanmugapriya Active Member

    Messages:
    32
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Bangalore
    Hi,
    I have around 15+ tables on which the DML's need to be tracked. I have a trigger on them. However, since the performance is bad, I have been asked to look for a different approach other than trigger.

    Could anyone advise on the same.

    Thank you,
    Shanmuga Priya D
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    What task at you: audit of change of fields?
    you use conditions for fields on a kotorm the trigger works?
    Describe your task.
    Look that such FGA
    and
    Compound TRG
     
  3. Shanmugapriya

    Shanmugapriya Active Member

    Messages:
    32
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Bangalore
    Hi Sergey,
    I have about 15 tables. Any DML's that is applied on them makes a subsequent entry into a different table 'X'. At the moment, I have trigger, so when the trigger is fired, I have a insert statement in to table 'X'. Since the performance is bad. I am looking for different approach.
     
  4. Shanmugapriya

    Shanmugapriya Active Member

    Messages:
    32
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Bangalore
    Yes, DML's on any columns of the 15 tables
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Post your trigger code so we can see what it's doing; there may be ways around this ''performance problem" you report.
     
  6. Shanmugapriya

    Shanmugapriya Active Member

    Messages:
    32
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Bangalore
    My requirement is not to use Trigger. Kindly advise if there is any other functionality to track the DML's.
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You can use DBMS_LOGMNR to query the SQL_REDO for insert, update and delete statements. Other than that and a trigger there is no other way. Why is your trigger so slow? Y0u won't post the code so it can be fixed so there's not much help anyone can give you.

    As an example I implemented transaction history for a client using triggers and tables over 20 years go and it's still in use today; here is an example of how I did it:

    Code (SQL):
    SQL>
    SQL> CREATE TABLE part (
      2  part_ser_no NUMBER NOT NULL,
      3  part_no NUMBER NOT NULL,
      4  part_desc varchar2(40) NOT NULL,
      5  part_cond varchar2(10) NOT NULL,
      6  part_hours NUMBER NOT NULL,
      7  part_cycles NUMBER NOT NULL,
      8  CONSTRAINT part_pk_ser PRIMARY KEY(part_ser_no, part_no));

    TABLE created.

    SQL>
    SQL> CREATE UNIQUE INDEX part_ser_uniq
      2  ON part(part_ser_no);

    INDEX created.

    SQL>
    SQL> CREATE TABLE engine(
      2  eng_ser_no NUMBER NOT NULL,
      3  eng_hours    NUMBER NOT NULL,
      4  eng_cycles NUMBER NOT NULL,
      5  eng_part_no NUMBER NOT NULL,
      6  eng_part_ser NUMBER NOT NULL,
      7  eng_last_maint_dt DATE NOT NULL,
      8  eng_last_mechanic NUMBER NOT NULL,
      9  CONSTRAINT eng_pk_ser PRIMARY KEY(eng_ser_no),
    10  CONSTRAINT eng_part_fk FOREIGN KEY(eng_part_ser, eng_part_no) REFERENCES part);

    TABLE created.

    SQL>
    SQL> CREATE TABLE part_hist (
      2  part_ser_no NUMBER NOT NULL,
      3  part_no NUMBER NOT NULL,
      4  part_desc varchar2(40) NOT NULL,
      5  part_cond varchar2(10) NOT NULL,
      6  part_hours NUMBER NOT NULL,
      7  part_cycles NUMBER NOT NULL,
      8  username varchar2(30),
      9  mod_dt  DATE,
    10  action varchar2(6),
    11  sql_text varchar2(4000));

    TABLE created.

    SQL>
    SQL> CREATE TABLE engine_hist (
      2  eng_ser_no NUMBER NOT NULL,
      3  eng_hours    NUMBER NOT NULL,
      4  eng_cycles NUMBER NOT NULL,
      5  eng_part_no NUMBER NOT NULL,
      6  eng_part_ser NUMBER NOT NULL,
      7  eng_last_maint_dt DATE NOT NULL,
      8  eng_last_mechanic NUMBER NOT NULL,
      9  username varchar2(30),
    10  mod_dt  DATE,
    11  action varchar2(6),
    12  sql_text varchar2(4000));

    TABLE created.

    SQL>
    SQL> CREATE OR REPLACE TRIGGER part_hist_trg
      2  after INSERT OR UPDATE OR DELETE
      3  ON part
      4  FOR each ROW
      5  BEGIN
      6           IF inserting THEN
      7               INSERT INTO part_hist
      8               (
      9                   part_ser_no ,
    10                   part_no ,
    11                   part_desc ,
    12                   part_cond ,
    13                   part_hours ,
    14                   part_cycles ,
    15                   username ,
    16                   mod_dt ,
    17                   action,
    18                   sql_text
    19               )
    20               VALUES
    21               (
    22                   :NEW.part_ser_no ,
    23                   :NEW.part_no    ,
    24                   :NEW.part_desc ,
    25                   :NEW.part_cond ,
    26                   :NEW.part_hours ,
    27                   :NEW.part_cycles ,
    28                   USER ,
    29                   sysdate ,
    30                   'INSERT',
    31                  (SELECT sql_text
    32               FROM v$sql
    33           WHERE (sql_id IN (SELECT sql_id FROM gv$session WHERE sid = sys_context('user','sid'))
    34           OR sql_id IN (SELECT prev_sql_id FROM gv$session WHERE sid = sys_context('user','sid')))
    35  AND sql_text NOT LIKE 'select sql_text%')
    36               );
    37           elsif updating THEN
    38               INSERT INTO part_hist
    39               (
    40                   part_ser_no ,
    41                   part_no ,
    42                   part_desc ,
    43                   part_cond ,
    44                   part_hours ,
    45                   part_cycles ,
    46                   username ,
    47                   mod_dt ,
    48                   action ,
    49                   sql_text
    50               )
    51               VALUES
    52               (
    53                   :NEW.part_ser_no ,
    54                   :NEW.part_no    ,
    55                   :NEW.part_desc ,
    56                   :NEW.part_cond ,
    57                   :NEW.part_hours ,
    58                   :NEW.part_cycles ,
    59                   USER ,
    60                   sysdate ,
    61                   'UPDATE' ,
    62                   (SELECT sql_text
    63                       FROM v$sql
    64                       WHERE (sql_id IN (SELECT sql_id FROM gv$session WHERE sid = sys_context('user','sid'))
    65                           OR sql_id IN (SELECT prev_sql_id FROM gv$session WHERE sid = sys_context('user','sid')))
    66                       AND sql_text NOT LIKE 'select sql_text%')
    67               );
    68           elsif deleting THEN
    69               INSERT INTO part_hist
    70               (
    71                   part_ser_no ,
    72                   part_no ,
    73                   part_desc ,
    74                   part_cond ,
    75                   part_hours ,
    76                   part_cycles ,
    77                   username ,
    78                   mod_dt ,
    79                   action,
    80                   sql_text
    81               )
    82               VALUES
    83               (
    84                   :OLD.part_ser_no ,
    85                   :OLD.part_no    ,
    86                   :OLD.part_desc ,
    87                   :OLD.part_cond ,
    88                   :OLD.part_hours ,
    89                   :OLD.part_cycles ,
    90                   USER ,
    91                   sysdate ,
    92                   'DELETE' ,
    93                   (SELECT sql_text
    94                       FROM v$sql
    95                       WHERE (sql_id IN (SELECT sql_id FROM gv$session WHERE sid = sys_context('user','sid'))
    96                           OR sql_id IN (SELECT prev_sql_id FROM gv$session WHERE sid = sys_context('user','sid')))
    97                       AND sql_text NOT LIKE 'select sql_text%')
    98               );
    99           END IF;
    100  END;
    101  /

    TRIGGER created.

    SQL>
    SQL> SHOW errors
    No errors.
    SQL>
    SQL> CREATE OR REPLACE TRIGGER engine_hist_trg
      2  after INSERT OR UPDATE OR DELETE
      3  ON engine
      4  FOR each ROW
      5  BEGIN
      6           IF inserting THEN
      7               INSERT INTO engine_hist
      8               (
      9                   eng_ser_no ,
    10                   eng_hours    ,
    11                   eng_cycles ,
    12                   eng_part_no ,
    13                   eng_part_ser ,
    14                   eng_last_maint_dt ,
    15                   eng_last_mechanic ,
    16                   username ,
    17                   mod_dt  ,
    18                   action,
    19                   sql_text
    20               )
    21               VALUES
    22               (
    23                   :NEW.eng_ser_no ,
    24                   :NEW.eng_hours  ,
    25                   :NEW.eng_cycles ,
    26                   :NEW.eng_part_no ,
    27                   :NEW.eng_part_ser ,
    28                   :NEW.eng_last_maint_dt ,
    29                   :NEW.eng_last_mechanic ,
    30                   USER ,
    31                   sysdate ,
    32                   'INSERT' ,
    33                   (SELECT sql_text
    34                       FROM v$sql
    35                       WHERE (sql_id IN (SELECT sql_id FROM gv$session WHERE sid = sys_context('user','sid'))
    36                           OR sql_id IN (SELECT prev_sql_id FROM gv$session WHERE sid = sys_context('user','sid')))
    37                       AND sql_text NOT LIKE 'select sql_text%')
    38               );
    39           elsif updating THEN
    40               INSERT INTO engine_hist
    41               (
    42                   eng_ser_no ,
    43                   eng_hours    ,
    44                   eng_cycles ,
    45                   eng_part_no ,
    46                   eng_part_ser ,
    47                   eng_last_maint_dt ,
    48                   eng_last_mechanic ,
    49                   username ,
    50                   mod_dt  ,
    51                   action,
    52                   sql_text
    53               )
    54               VALUES
    55               (
    56                   :NEW.eng_ser_no ,
    57                   :NEW.eng_hours  ,
    58                   :NEW.eng_cycles ,
    59                   :NEW.eng_part_no ,
    60                   :NEW.eng_part_ser ,
    61                   :NEW.eng_last_maint_dt ,
    62                   :NEW.eng_last_mechanic ,
    63                   USER ,
    64                   sysdate ,
    65                   'UPDATE' ,
    66                   (SELECT sql_text
    67                       FROM v$sql
    68                       WHERE (sql_id IN (SELECT sql_id FROM gv$session WHERE sid = sys_context('user','sid'))
    69                           OR sql_id IN (SELECT prev_sql_id FROM gv$session WHERE sid = sys_context('user','sid')))
    70                       AND sql_text NOT LIKE 'select sql_text%')
    71               );
    72           elsif deleting THEN
    73               INSERT INTO engine_hist
    74               (
    75                   eng_ser_no ,
    76                   eng_hours    ,
    77                   eng_cycles ,
    78                   eng_part_no ,
    79                   eng_part_ser ,
    80                   eng_last_maint_dt ,
    81                   eng_last_mechanic ,
    82                   username ,
    83                   mod_dt  ,
    84                   action,
    85                   sql_text
    86               )
    87               VALUES
    88               (
    89                   :OLD.eng_ser_no ,
    90                   :OLD.eng_hours  ,
    91                   :OLD.eng_cycles ,
    92                   :OLD.eng_part_no ,
    93                   :OLD.eng_part_ser ,
    94                   :OLD.eng_last_maint_dt ,
    95                   :OLD.eng_last_mechanic ,
    96                   USER ,
    97                   sysdate ,
    98                   'DELETE' ,
    99                   (SELECT sql_text
    100                       FROM v$sql
    101                       WHERE (sql_id IN (SELECT sql_id FROM gv$session WHERE sid = sys_context('user','sid'))
    102                           OR sql_id IN (SELECT prev_sql_id FROM gv$session WHERE sid = sys_context('user','sid')))
    103                       AND sql_text NOT LIKE 'select sql_text%')
    104               );
    105           END IF;
    106  END;
    107  /

    TRIGGER created.

    SQL>
    SQL> SHOW errors
    No errors.
    SQL>
    SQL> SET timing ON
    SQL>
    SQL> INSERT ALL
      2  INTO part
      3  (part_ser_no,
      4  part_no,
      5  part_desc,
      6  part_cond,
      7  part_hours,
      8  part_cycles)
      9  VALUES
    10  (1,
    11   432,
    12   'Flarp flap',
    13   'Decent',
    14   12,
    15   8)
    16  INTO part
    17  (part_ser_no,
    18  part_no,
    19  part_desc,
    20  part_cond,
    21  part_hours,
    22  part_cycles)
    23  VALUES
    24  (2,
    25   433,
    26   'Flarp flap flipper',
    27   'Decent',
    28   12,
    29   8)
    30  SELECT * FROM dual;

    2 ROWS created.

    Elapsed: 00:00:00.04
    SQL>
    SQL> INSERT INTO engine
      2  (eng_ser_no,
      3  eng_hours,
      4  eng_cycles,
      5  eng_part_no,
      6  eng_part_ser,
      7  eng_last_maint_dt,
      8  eng_last_mechanic)
      9  VALUES
    10  (9999999,
    11   44,
    12   32,
    13   432,
    14   1,
    15   sysdate,
    16   56);

    1 ROW created.

    Elapsed: 00:00:00.03
    SQL>
    SQL> DELETE FROM part
      2  WHERE part_no = 433;

    1 ROW deleted.

    Elapsed: 00:00:00.02
    SQL>
    SQL> SET timing off
    SQL>
    SQL> commit;

    Commit complete.

    SQL>
    SQL> -- set linesize 132
    SQL>
    SQL> SELECT * FROM part;

    PART_SER_NO    PART_NO PART_DESC                                PART_COND  PART_HOURS PART_CYCLES                                                                                                      
    ----------- ---------- ---------------------------------------- ---------- ---------- -----------                                                                                                      
              1        432 Flarp flap                               Decent             12           8                                                                                                      

    SQL> SELECT * FROM engine;

    ENG_SER_NO  ENG_HOURS ENG_CYCLES ENG_PART_NO ENG_PART_SER ENG_LAST_ ENG_LAST_MECHANIC                                                                                                                  
    ---------- ---------- ---------- ----------- ------------ --------- -----------------                                                                                                                  
       9999999         44         32         432            1 15-SEP-16                56                                                                                                                  

    SQL> SELECT * FROM part_hist;

    PART_SER_NO    PART_NO PART_DESC                                PART_COND  PART_HOURS PART_CYCLES USERNAME                       MOD_DT    ACTION                                                      
    ----------- ---------- ---------------------------------------- ---------- ---------- ----------- ------------------------------ --------- ------                                                      
    SQL_TEXT                                                                                                                                                                                              
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
              1        432 Flarp flap                               Decent             12           8 BING                           15-SEP-16 INSERT                                                      
                                                                                                                                                                                                           
                                                                                                                                                                                                           
              2        433 Flarp flap flipper                       Decent             12           8 BING                           15-SEP-16 INSERT                                                      
                                                                                                                                                                                                           
                                                                                                                                                                                                           
              2        433 Flarp flap flipper                       Decent             12           8 BING                           15-SEP-16 DELETE                                                      
                                                                                                                                                                                                           
                                                                                                                                                                                                           

    SQL> SELECT * FROM engine_hist;

    ENG_SER_NO  ENG_HOURS ENG_CYCLES ENG_PART_NO ENG_PART_SER ENG_LAST_ ENG_LAST_MECHANIC USERNAME                       MOD_DT    ACTION                                                                  
    ---------- ---------- ---------- ----------- ------------ --------- ----------------- ------------------------------ --------- ------                                                                  
    SQL_TEXT                                                                                                                                                                                              
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       9999999         44         32         432            1 15-SEP-16                56 BING                           15-SEP-16 INSERT                                                                  
                                                                                                                                                                                                           
                                                                                                                                                                                                           

    SQL>
     
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
  9. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation