This discussion is archived
1 Reply Latest reply: Aug 29, 2012 1:18 PM by Jason_(A_Non) RSS

After Insert Trigger, concat data into xml, store as CLOB bad performance

788407 Newbie
Currently Being Moderated
Hi people,

I have a problem with a trigger which fires on "after insert" into a table. The reason for the trigger is to keep history on events in that specific table.
WITH the trigger enabled insert of 10 rows as shown in the SQL statement below takes about 17 seconds.
When the trigger is disabled the same insert completes in 10ms.
The trigger is basically concatenating all column data in the insert in order to produce the "XML_DATA" string which is to be inserted in
the column "XML_DATA" with the datatype CLOB.
It seems like this is the part where time is consumed but I can't confirm this.

First some tech details:
Oracle Platform: Linux RedHat ES 4 update 6 (64-bit)
Oracle Version: 10.2.0.5 (Real Application Cluster) (64-bit)
Size of SGA (sga_target) 12G
Table and trigger code:
CREATE TABLE PRODUSR.TABLE1
(
  APAR_ONCE       NUMBER(15)                    DEFAULT 0                     NOT NULL,
  APAR_TYPE       VARCHAR2(1 BYTE)              DEFAULT ' '                   NOT NULL,
  BANK_ACCOUNT    VARCHAR2(35 BYTE)             DEFAULT ' '                   NOT NULL,
  BONUS_GR        VARCHAR2(4 BYTE)              DEFAULT ' '                   NOT NULL,
  CASH_DELAY      NUMBER(15)                    DEFAULT 0                     NOT NULL,
  CLEARING_CODE   VARCHAR2(13 BYTE)             DEFAULT ' '                   NOT NULL,
  COLLECT_FLAG    NUMBER(15)                    DEFAULT 0                     NOT NULL,
  COMP_REG_NO     VARCHAR2(25 BYTE)             DEFAULT ' '                   NOT NULL,
  CONTROL         VARCHAR2(1 BYTE)              DEFAULT ' '                   NOT NULL,
  CREDIT_LIMIT    NUMBER(30,3)                  DEFAULT 0                     NOT NULL,
  CURRENCY_SET    NUMBER(15)                    DEFAULT 0                     NOT NULL,
  DISC_CODE       VARCHAR2(25 BYTE)             DEFAULT ' '                   NOT NULL,
  EXPIRED_DATE    DATE                          DEFAULT to_date ('01-01-1900','DD-MM-YYYY') NOT NULL,
  FAC_SHORT_SET   NUMBER(15)                    DEFAULT 0                     NOT NULL,
  FOREIGN_ACC     VARCHAR2(35 BYTE)             DEFAULT ' '                   NOT NULL,
  LANGUAGE        VARCHAR2(2 BYTE)              DEFAULT ' '                   NOT NULL,
  LAST_UPDATE     DATE                          DEFAULT to_date ('01-01-1900','DD-MM-YYYY') NOT NULL,
  MESSAGE_TEXT    VARCHAR2(255 BYTE)            DEFAULT ' '                   NOT NULL,
  PAY_DELAY       NUMBER(15)                    DEFAULT 0                     NOT NULL,
  PAY_METHOD      VARCHAR2(2 BYTE)              DEFAULT ' '                   NOT NULL,
  PAY_METHOD_SET  NUMBER(15)                    DEFAULT 0                     NOT NULL,
  PAY_TEMP_ID     VARCHAR2(4 BYTE)              DEFAULT ' '                   NOT NULL,
  POSTAL_ACC      VARCHAR2(35 BYTE)             DEFAULT ' '                   NOT NULL,
  PRIORITY_NO     NUMBER(15)                    DEFAULT 0                     NOT NULL,
  SHORT_NAME      VARCHAR2(10 BYTE)             DEFAULT ' '                   NOT NULL,
  STATUS          VARCHAR2(1 BYTE)              DEFAULT ' '                   NOT NULL,
  SWIFT           VARCHAR2(11 BYTE)             DEFAULT ' '                   NOT NULL,
  TAX_SET         NUMBER(15)                    DEFAULT 0                     NOT NULL,
  TC_SET          NUMBER(15)                    DEFAULT 0                     NOT NULL,
  TERMS_SET       NUMBER(15)                    DEFAULT 0                     NOT NULL,
  TRANS_DATE      DATE                          DEFAULT to_date ('01-01-1900','DD-MM-YYYY') NOT NULL,
  VAT_REG_NO      VARCHAR2(25 BYTE)             DEFAULT ' '                   NOT NULL,
  APAR_GR_ID      VARCHAR2(25 BYTE)             DEFAULT ' '                   NOT NULL,
  APAR_ID         VARCHAR2(25 BYTE)             DEFAULT ' '                   NOT NULL,
  APAR_ID_REF     VARCHAR2(25 BYTE)             DEFAULT ' '                   NOT NULL,
  APAR_NAME       VARCHAR2(255 BYTE)            DEFAULT ' '                   NOT NULL,
  CLIENT          VARCHAR2(25 BYTE)             DEFAULT ' '                   NOT NULL,
  COUNTRY_CODE    VARCHAR2(25 BYTE)             DEFAULT ' '                   NOT NULL,
  CURRENCY        VARCHAR2(25 BYTE)             DEFAULT ' '                   NOT NULL,
  DESCRIPTION     VARCHAR2(255 BYTE)            DEFAULT ' '                   NOT NULL,
  EXT_APAR_REF    VARCHAR2(100 BYTE)            DEFAULT ' '                   NOT NULL,
  FACTOR_SHORT    VARCHAR2(25 BYTE)             DEFAULT ' '                   NOT NULL,
  INTRULE_ID      VARCHAR2(25 BYTE)             DEFAULT ' '                   NOT NULL,
  INVOICE_CODE    VARCHAR2(25 BYTE)             DEFAULT ' '                   NOT NULL,
  MAIN_APAR_ID    VARCHAR2(25 BYTE)             DEFAULT ' '                   NOT NULL,
  TAX_CODE        VARCHAR2(25 BYTE)             DEFAULT ' '                   NOT NULL,
  TAX_SYSTEM      VARCHAR2(25 BYTE)             DEFAULT ' '                   NOT NULL,
  TERMS_ID        VARCHAR2(25 BYTE)             DEFAULT ' '                   NOT NULL,
  USER_ID         VARCHAR2(25 BYTE)             DEFAULT ' '                   NOT NULL,
  WF_STATE        VARCHAR2(1 BYTE)              DEFAULT ' '                   NOT NULL,
  SUPPLEMENTAL LOG GROUP PRDTAB1_G1 (CLIENT,APAR_ID) ALWAYS,
  SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS,
  SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS,
  SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS
)
TABLESPACE DATA2
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          96M
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING
ENABLE ROW MOVEMENT;


CREATE UNIQUE INDEX PRODUSR.I1TABLE1 ON PRODUSR.TABLE1
(CLIENT, APAR_ID)
LOGGING
TABLESPACE INDEX2
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE INDEX PRODUSR.AITABLE12 ON PRODUSR.TABLE1
(CLIENT, APAR_NAME)
LOGGING
TABLESPACE INDEX2
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE INDEX PRODUSR.AITABLE13 ON PRODUSR.TABLE1
(CLIENT, SHORT_NAME)
LOGGING
TABLESPACE INDEX2
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE INDEX PRODUSR.AITABLE14 ON PRODUSR.TABLE1
(CLIENT, COMP_REG_NO)
LOGGING
TABLESPACE INDEX2
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE INDEX PRODUSR.IX2TABLE1 ON PRODUSR.TABLE1
(CLIENT, COMP_REG_NO, APAR_ID)
LOGGING
TABLESPACE INDEX2
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE INDEX PRODUSR.IX3TABLE1 ON PRODUSR.TABLE1
(CLIENT, APAR_ID, APAR_GR_ID)
LOGGING
TABLESPACE INDEX2
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE INDEX PRODUSR.IX4TABLE1 ON PRODUSR.TABLE1
(FOREIGN_ACC, POSTAL_ACC, BANK_ACCOUNT, PAY_METHOD, APAR_ONCE, 
CLIENT)
LOGGING
TABLESPACE INDEX2
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE TRIGGER PRODUSR.i_TABLE1
 AFTER INSERT ON PRODUSR.TABLE1
 FOR EACH ROW
DECLARE 
     cUserId varchar(25) := ' '; 
      cKey varchar(1000) := ' ';  
      cXml CLOB := ' '; 
 BEGIN 
     cXml := '<CHG client="'||'c;'||:new.client ||'" apar_id="'||'c;'||:new.apar_id||'">'; 
      cXml := cXml || '<COL NAM="apar_gr_id" TYP="c"><NEW>' || TO_CHAR(:new.apar_gr_id ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="apar_id" TYP="c"><NEW>' || TO_CHAR(:new.apar_id ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="apar_id_ref" TYP="c"><NEW>' || TO_CHAR(:new.apar_id_ref ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="apar_name" TYP="c"><NEW>' || TO_CHAR(:new.apar_name ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="apar_once" TYP="i"><NEW>' || TO_CHAR(:new.apar_once ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="apar_type" TYP="c"><NEW>' || TO_CHAR(:new.apar_type ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="bank_account" TYP="c"><NEW>' || TO_CHAR(:new.bank_account ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="bonus_gr" TYP="c"><NEW>' || TO_CHAR(:new.bonus_gr ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="cash_delay" TYP="i"><NEW>' || TO_CHAR(:new.cash_delay ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="clearing_code" TYP="c"><NEW>' || TO_CHAR(:new.clearing_code ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="client" TYP="c"><NEW>' || TO_CHAR(:new.client ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="collect_flag" TYP="i"><NEW>' || TO_CHAR(:new.collect_flag ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="comp_reg_no" TYP="c"><NEW>' || TO_CHAR(:new.comp_reg_no ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="control" TYP="c"><NEW>' || TO_CHAR(:new.control ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="country_code" TYP="c"><NEW>' || TO_CHAR(:new.country_code ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="credit_limit" TYP="f"><NEW>' || TO_CHAR(:new.credit_limit,'999999999999999999.99999') || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="currency" TYP="c"><NEW>' || TO_CHAR(:new.currency ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="currency_set" TYP="i"><NEW>' || TO_CHAR(:new.currency_set ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="description" TYP="c"><NEW>' || TO_CHAR(:new.description ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="disc_code" TYP="c"><NEW>' || TO_CHAR(:new.disc_code ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="expired_date" TYP="d"><NEW>' || TO_CHAR(:new.expired_date,'YYYY-MM-DD HH24:mi:ss') || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="ext_apar_ref" TYP="c"><NEW>' || TO_CHAR(:new.ext_apar_ref ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="fac_short_set" TYP="i"><NEW>' || TO_CHAR(:new.fac_short_set ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="factor_short" TYP="c"><NEW>' || TO_CHAR(:new.factor_short ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="foreign_acc" TYP="c"><NEW>' || TO_CHAR(:new.foreign_acc ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="intrule_id" TYP="c"><NEW>' || TO_CHAR(:new.intrule_id ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="invoice_code" TYP="c"><NEW>' || TO_CHAR(:new.invoice_code ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="language" TYP="c"><NEW>' || TO_CHAR(:new.language ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="last_update" TYP="d"><NEW>' || TO_CHAR(:new.last_update,'YYYY-MM-DD HH24:mi:ss') || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="main_apar_id" TYP="c"><NEW>' || TO_CHAR(:new.main_apar_id ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="message_text" TYP="c"><NEW>' || TO_CHAR(:new.message_text ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="pay_delay" TYP="i"><NEW>' || TO_CHAR(:new.pay_delay ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="pay_method" TYP="c"><NEW>' || TO_CHAR(:new.pay_method ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="pay_method_set" TYP="i"><NEW>' || TO_CHAR(:new.pay_method_set ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="pay_temp_id" TYP="c"><NEW>' || TO_CHAR(:new.pay_temp_id ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="postal_acc" TYP="c"><NEW>' || TO_CHAR(:new.postal_acc ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="priority_no" TYP="i"><NEW>' || TO_CHAR(:new.priority_no ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="short_name" TYP="c"><NEW>' || TO_CHAR(:new.short_name ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="status" TYP="c"><NEW>' || TO_CHAR(:new.status ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="swift" TYP="c"><NEW>' || TO_CHAR(:new.swift ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="tax_code" TYP="c"><NEW>' || TO_CHAR(:new.tax_code ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="tax_set" TYP="i"><NEW>' || TO_CHAR(:new.tax_set ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="tax_system" TYP="c"><NEW>' || TO_CHAR(:new.tax_system ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="tc_set" TYP="i"><NEW>' || TO_CHAR(:new.tc_set ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="terms_id" TYP="c"><NEW>' || TO_CHAR(:new.terms_id ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="terms_set" TYP="i"><NEW>' || TO_CHAR(:new.terms_set ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="trans_date" TYP="d"><NEW>' || TO_CHAR(:new.trans_date,'YYYY-MM-DD HH24:mi:ss') || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="user_id" TYP="c"><NEW>' || TO_CHAR(:new.user_id ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="vat_reg_no" TYP="c"><NEW>' || TO_CHAR(:new.vat_reg_no ) || '</NEW></COL>' ; 
      cXml := cXml || '<COL NAM="wf_state" TYP="c"><NEW>' || TO_CHAR(:new.wf_state ) || '</NEW></COL>' ; 
      cXml := cXml || ' </CHG> '; 
      INSERT INTO TABLE1HIST (table_name,exact_time, xml_data, object_type, composite_key, user_id)
          VALUES ('TABLE1', sysdate, cXml, 'I',  :new.client||';'||:new.apar_id, :new.user_id); 
 END;
/

CREATE TABLE PRODUSR.TABLE1HIST
(
  COMPOSITE_KEY  VARCHAR2(255 BYTE)             DEFAULT ' '                   NOT NULL,
  EXACT_TIME     DATE                           DEFAULT to_date('19000101 00:00:00', 'yyyymmdd hh24:mi:ss') NOT NULL,
  OBJECT_TYPE    VARCHAR2(1 BYTE)               DEFAULT ' '                   NOT NULL,
  TABLE_NAME     VARCHAR2(50 BYTE)              DEFAULT ' '                   NOT NULL,
  USER_ID        VARCHAR2(25 BYTE)              DEFAULT ' '                   NOT NULL,
  XML_DATA       CLOB                           DEFAULT ' '                   NOT NULL,
  STATUS         VARCHAR2(1 BYTE)               DEFAULT 'N'                   NOT NULL
)
LOB (XML_DATA) STORE AS (
  TABLESPACE  DATA2
  ENABLE      STORAGE IN ROW
  CHUNK       8192
  RETENTION
  NOCACHE
  LOGGING
      STORAGE    (
                  INITIAL          64K
                  NEXT             1M
                  MINEXTENTS       1
                  MAXEXTENTS       UNLIMITED
                  PCTINCREASE      0
                  BUFFER_POOL      DEFAULT
                 ))
TABLESPACE DATA2
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE INDEX PRODUSR.I1TABLE1HIST ON PRODUSR.TABLE1HIST
(TABLE_NAME, COMPOSITE_KEY, EXACT_TIME)
LOGGING
TABLESPACE INDEX2
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;
*10046 trace info*

A simple insert of 10 rows into the "TABLE1" table runs for about 17 seconds. If I disable the trigger it completes in about 10ms.
I ran a 10046, level 12 trace on the statement as shown here:
INSERT INTO table1 ( apar_gr_id , apar_id , apar_id_ref ,
       apar_name , apar_type , bank_account , bonus_gr ,
       cash_delay , clearing_code , client , collect_flag ,
       comp_reg_no , control , country_code , credit_limit ,
       currency , currency_set , description , disc_code ,
       expired_date , ext_apar_ref , factor_short , foreign_acc
       , intrule_id , invoice_code , language , last_update ,
       main_apar_id , message_text , pay_delay , pay_method ,
       postal_acc , priority_no , short_name , status , swift ,
       tax_set , tax_system , terms_id , terms_set , user_id ,
       vat_reg_no , pay_temp_id )
SELECT i.apar_gr_id , i.apar_id , i.apar_id_ref , i.apar_name
       , i.apar_type , i.bank_account , i.bonus_gr ,
       i.cash_delay , i.clearing_code , c.leg_act_cli ,
       i.collect_flag , i.comp_reg_no , i.control ,
       i.country_code , i.credit_limit , i.currency ,
       i.currency_set , i.reference_1 , i.disc_code ,
       i.expired_date , i.ext_apar_ref , i.factor_short ,
       i.foreign_acc , i.intrule_id , i.invoice_code ,
       i.language , i.last_update , i.main_apar_id ,
       i.message_text , i.pay_delay , i.pay_method ,
       i.postal_acc , i.priority_no , i.short_name , i.status ,
       i.swift , i.tax_set , i.tax_system , i.terms_id ,
       i.terms_set , i.user_id , i.vat_reg_no , i.pay_temp_id
 FROM table2 i , table3 c
WHERE i.full_record = :"SYS_B_0" AND i.client = c.client

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1     16.70      17.02         13       4533      33738          10
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     16.70      17.02         13       4533      33738          10

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 63

Rows     Row Source Operation
-------  ---------------------------------------------------
     10  HASH JOIN  (cr=10 pr=0 pw=0 time=1637 us)
     50   TABLE ACCESS FULL TABLE3 (cr=7 pr=0 pw=0 time=409 us)
     10   TABLE ACCESS FULL TABLE2 (cr=3 pr=0 pw=0 time=173 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  library cache lock                              5        0.00          0.00
  library cache pin                               4        0.00          0.00
  row cache lock                                 92        0.00          0.10
  gc cr disk read                                 1        0.00          0.00
  db file sequential read                        13        0.02          0.11
  rdbms ipc reply                                 1        0.00          0.00
  gc cr grant 2-way                               1        0.00          0.00
  latch: row cache objects                        1        0.00          0.00
  gc current block 2-way                          5        0.00          0.00
  gc current grant 2-way                          4        0.00          0.00
  gc current block 3-way                          2        0.00          0.00
  latch: cache buffers chains                     1        0.00          0.00
  latch: cache buffers lru chain                  1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00
Summary

Table2 and table3 have appr. 800 rows each.

I can't see where the time is spent... If anyone of you have had similar experience or some extra time to look at this I am truly grateful :-)

Best regards
Måns

Edited by: mans_axell on Aug 27, 2012 7:55 AM

Edited by: mans_axell on Aug 28, 2012 4:43 AM

Edited by: mans_axell on Aug 29, 2012 1:47 AM

Removed supplemental log data from all columns. This was used for streams replication earlier.
However, no change in performance.

Edited by: mans_axell on Aug 29, 2012 1:52 AM
CHANGE OF TABLESPACE
The lob segment has been moved into a non-ASSM tablespace without any improvements.
  • 1. Re: After Insert Trigger, concat data into xml, store as CLOB bad performance
    Jason_(A_Non) Expert
    Currently Being Moderated
    So what happens, i.e. how long does it take, if you insert 10 rows into TABLE1HIST directly instead of via the trigger? How about when you copy the trigger code into a procedure so that the CLOB building portion is done as well? You have apparently identified the slowdown as being the trigger but I do not see any breakdowns on your research into where in the trigger the issue is.

    Side note: I assume you are storing the XML data in a CLOB column due to streams replication issue with XMLType based columns.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points