Skip to Main Content

before insert trigger to populate a sequence

889367Sep 12 2013 — edited Sep 13 2013

10.2.0.2 & 11.2.0.2 both on RHEL 5.5

I know how to use a trigger to assign a sequence value for a table on insert and I've seen Tom Kyte's comments on not doing that because of performance implications for bulk loading and scalability. But I'm trying to decide when and if I should use this. I wouldn't consider it but we've got Informatica developers that insist on writing dynamic sql functions to pull values for sequences to use in inserts because they can't reference the nextval in the their workflows. My thought is that doing a trigger/sequence combo is a lesser of two evils and we should do that where the tables in question are not going to be undergoing large bulk loads.

Also, i've seen that with 11g you can directly assign a nextval to :new.col, but i've yet to see what the reasoning on doing so is. Does this method perform better or is it just less coding?

Here's a sample test i've ran to see how differently they perform, and it's not a huge difference between to two.

SQL> DROP FUNCTION DBABEN.TEST_SEQ_FUNC;

DROP FUNCTION DBABEN.TEST_SEQ_FUNC

*

ERROR at line 1:

ORA-04043: object TEST_SEQ_FUNC does not exist

Elapsed: 00:00:00.02

SQL> DROP SEQUENCE DBABEN.SEQ_A;

DROP SEQUENCE DBABEN.SEQ_A

                     *

ERROR at line 1:

ORA-02289: sequence does not exist

Elapsed: 00:00:00.01

SQL> DROP SEQUENCE DBABEN.SEQ_B;

DROP SEQUENCE DBABEN.SEQ_B

                     *

ERROR at line 1:

ORA-02289: sequence does not exist

Elapsed: 00:00:00.01

SQL> DROP TABLE DBABEN.TEST1;

DROP TABLE DBABEN.TEST1

                  *

ERROR at line 1:

ORA-00942: table or view does not exist

Elapsed: 00:00:00.14

SQL> DROP TABLE DBABEN.TEST2;

DROP TABLE DBABEN.TEST2

                  *

ERROR at line 1:

ORA-00942: table or view does not exist

Elapsed: 00:00:00.02

SQL> DROP TRIGGER DBABEN.TEST1_SEQ_BI;

DROP TRIGGER DBABEN.TEST1_SEQ_BI

*

ERROR at line 1:

ORA-04080: trigger 'TEST1_SEQ_BI' does not exist

Elapsed: 00:00:00.01

SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE

SQL>

SQL> CREATE OR REPLACE FUNCTION DBABEN.TEST_SEQ_FUNC (IN_SEQ_NAME IN VARCHAR2)

  2  RETURN NUMBER

  3  IS

  4  RET_VAL NUMBER;

  5  BEGIN

  6  EXECUTE IMMEDIATE 'select DBABEN.'||IN_SEQ_NAME||'.nextval from dual' INTO RET_VAL;

  7  RETURN RET_VAL;

  8  END;

  9  /

Function created.

Elapsed: 00:00:00.02

SQL>

SQL> CREATE SEQUENCE DBABEN.SEQ_A

  2       START WITH 1

  3       INCREMENT BY 1

  4       NOMINVALUE

  5       MAXVALUE 9999999999999999999999999999

  6       NOCYCLE

  7       NOCACHE

  8       NOORDER

  9  ;

Sequence created.

Elapsed: 00:00:00.01

SQL>

SQL> CREATE SEQUENCE DBABEN.SEQ_B

  2       START WITH 1

  3       INCREMENT BY 1

  4       NOMINVALUE

  5       MAXVALUE 9999999999999999999999999999

  6       NOCYCLE

  7       NOCACHE

  8       NOORDER

  9  ;

Sequence created.

Elapsed: 00:00:00.01

SQL>

SQL> CREATE TABLE DBABEN.TEST1 (COL1 NUMBER, COL2 VARCHAR2(50));

Table created.

Elapsed: 00:00:00.01

SQL> CREATE UNIQUE INDEX DBABEN.TEST1_PK ON DBABEN.TEST1(COL1);

Index created.

Elapsed: 00:00:00.01

SQL> ALTER TABLE DBABEN.TEST1 ADD CONSTRAINT PK_TEST1 PRIMARY KEY (COL1) USING INDEX DBABEN.TEST1_PK;

Table altered.

Elapsed: 00:00:00.01

SQL>

SQL> CREATE TABLE DBABEN.TEST2 (COL1 NUMBER, COL2 VARCHAR2(50));

Table created.

Elapsed: 00:00:00.00

SQL> CREATE UNIQUE INDEX DBABEN.TEST2_PK ON DBABEN.TEST2(COL1);

Index created.

Elapsed: 00:00:00.01

SQL> ALTER TABLE DBABEN.TEST2 ADD CONSTRAINT PK_TEST2 PRIMARY KEY (COL1) USING INDEX DBABEN.TEST2_PK;

Table altered.

Elapsed: 00:00:00.01

SQL>

SQL> CREATE TRIGGER DBABEN.TEST1_SEQ_BI BEFORE INSERT ON DBABEN.TEST1 FOR EACH ROW

  2  BEGIN

  3   SELECT DBABEN.SEQ_A.NEXTVAL INTO :NEW.COL1 FROM DUAL;

  4  END;

  5  /

Trigger created.

Elapsed: 00:00:00.01

SQL>

SQL> set serveroutput on

SQL> execute runstats_pkg.rs_start;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.20

SQL> declare

  2   val1 varchar2(50) := 'A';

  3  begin

  4    for i in 1..1000000 loop

  5           insert into dbaben.test1 ( col2 ) values ( val1 );

  6    end loop;

  7    commit;

  8  end;

  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:02:40.92

SQL> execute runstats_pkg.rs_middle;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.12

SQL> declare

  2   val1 varchar2(50) := 'A';

  3   val2 number;

  4  begin

  5    for i in 1..1000000 loop

  6       select DBABEN.TEST_SEQ_FUNC('SEQ_B') into val2 from dual;

  7           insert into dbaben.test2 values (val2, val1);

  8    end loop;

  9    commit;

10  end;

11  /

PL/SQL procedure successfully completed.

Elapsed: 00:03:04.00

SQL> execute runstats_pkg.rs_stop(p_difference_threshold=>9);

Run1 ran in 16099 hsecs                                                        

Run2 ran in 18399 hsecs                                                        

run 1 ran in 87.5% of the time                                                 

                                                                                  

Name                                            Run1        Run2        Diff   

LATCH.session timer                               53          63          10   

LATCH.library cache load lock                     20          30          10   

LATCH.transaction branch allocation              116         126          10   

LATCH.channel anchor                              64          74          10   

STAT...cluster key scan block gets               420         430          10   

STAT...immediate (CURRENT) block cleanou       3,779       3,769         -10   

LATCH.object stats modification                   19           8         -11   

STAT...workarea memory allocated                 -29         -17          12   

STAT...write clones created in foregroun           5          17          12   

STAT...sorts (rows)                           38,266      38,253         -13   

LATCH.ASM db client latch                        162         147         -15   

LATCH.dictionary lookup                           23           8         -15   

STAT...parse time elapsed                        318         300         -18   

STAT...dirty buffers inspected                     0          23          23   

STAT...parse time cpu                            369         340         -29   

STAT...buffer is pinned count                    113          81         -32   

LATCH.message pool operations parent lat          57          24         -33   

STAT...pinned buffers inspected                   39           4         -35   

LATCH.object queue header heap                    93          56         -37   

LATCH.shared pool simulator                      199         149         -50   

LATCH.resmgr group change latch                   52         110          58   

STAT...index crx upgrade (positioned)            187         249          62   

STAT...shared hash latch upgrades - no w         191         253          62   

STAT...session cursor cache hits           1,000,322   1,000,384          62   

LATCH.post/wait queue                            347         410          63   

LATCH.compile environment latch                   44         109          65   

LATCH.event group latch                           44         109          65   

LATCH.resmgr:actses change group                  43         109          66   

LATCH.global KZLD latch for mem in SGA            43         109          66   

LATCH.FOB s.o list latch                         182         115         -67   

STAT...index scans kdiixs1                       334         407          73   

LATCH.resmgr:resource group CPU method            38         112          74   

LATCH.PL/SQL warning settings                     61         136          75   

STAT...opened cursors cumulative           1,000,472   1,000,548          76   

STAT...parse count (total)                 1,000,549   1,000,628          79   

LATCH.dml lock allocation                  2,001,420   2,001,339         -81   

STAT...bytes received via SQL*Net from c       1,448       1,545          97   

STAT...db block gets from cache            6,707,836   6,707,732        -104   

STAT...db block gets                       6,707,836   6,707,732        -104   

STAT...table fetch by rowid                      305         410         105   

LATCH.channel operations parent latch          1,260       1,374         114   

LATCH.threshold alerts latch                     126           4        -122   

STAT...enqueue requests                    2,003,345   2,003,475         130   

STAT...enqueue releases                    2,003,345   2,003,475         130   

LATCH.dummy allocation                            83         221         138   

LATCH.process group creation                      83         221         138   

LATCH.OS process: request allocation              83         221         138   

LATCH.process allocation                          83         221         138   

LATCH.parameter table management                  85         223         138   

LATCH.resmgr:free threads list                    81         221         140   

LATCH.channel handle pool latch                   91         231         140   

LATCH.resmgr:active threads                       87         227         140   

LATCH.OS process allocation                      141         282         141   

LATCH.Consistent RBA                           1,871       1,726        -145   

LATCH.mostly latch-free SCN                    1,897       1,747        -150   

LATCH.lgwr LWN SCN                             1,874       1,724        -150   

LATCH.JS queue state obj latch                   960       1,110         150   

LATCH.session state list latch                   165         345         180   

STAT...redo entries                        4,025,720   4,025,901         181   

LATCH.sequence cache                       2,000,144   2,000,345         201   

LATCH.OS process                                 123         336         213   

STAT...db block changes                    8,064,475   8,064,703         228   

LATCH.file cache latch                           617         375        -242   

STAT...no work - consistent read gets          1,015       1,284         269   

LATCH.user lock                                  162         442         280   

LATCH.library cache lock allocation              218         503         285   

LATCH.In memory undo latch                     1,036         688        -348   

LATCH.session switching                          197         563         366   

LATCH.client/application info                    286         659         373   

STAT...buffer is not pinned count              1,153       1,527         374   

LATCH.active service list                        503         880         377   

LATCH.library cache pin                    4,001,694   4,002,121         427   

STAT...calls to kcmgas                     1,053,347   1,052,745        -602   

STAT...redo ordering marks                    51,228      50,614        -614   

STAT...redo subscn max counts                 54,129      53,475        -654   

LATCH.SQL memory manager workarea list l       3,809       4,567         758   

LATCH.list of block allocation                 2,486       1,604        -882   

LATCH.redo allocation                          7,613       6,603      -1,010   

LATCH.active checkpoint queue latch            1,565         552      -1,013   

LATCH.enqueues                             1,008,739   1,007,721      -1,018   

LATCH.SGA IO buffer pool latch                   320       1,436       1,116   

LATCH.session allocation                   4,001,537   4,002,826       1,289   

STAT...consistent gets - examination       2,004,160   2,006,054       1,894   

LATCH.redo writing                             8,139       6,206      -1,933   

STAT...recursive cpu usage                    15,481      17,417       1,936   

STAT...CPU used by this session               15,782      17,921       2,139   

STAT...CPU used when call started             15,782      17,921       2,139   

STAT...session logical reads               8,715,273   8,717,455       2,182   

STAT...consistent gets from cache          2,007,437   2,009,723       2,286   

STAT...consistent gets                     2,007,437   2,009,723       2,286   

STAT...Elapsed Time                           16,110      18,412       2,302   

STAT...DB time                                16,097      18,400       2,303   

LATCH.enqueue hash chains                  4,017,742   4,015,275      -2,467   

LATCH.messages                                11,280       8,207      -3,073   

LATCH.shared pool                              4,142       7,535       3,393   

STAT...IMU Redo allocation size               34,156      37,552       3,396   

STAT...hot buffers moved to head of LRU       14,983      20,026       5,043   

LATCH.row cache objects                    7,015,405   7,021,192       5,787   

LATCH.undo global data                     3,035,853   3,026,139      -9,714   

LATCH.cache buffer handles                    22,068      11,810     -10,258   

STAT...free buffer requested                  50,816      33,487     -17,329   

LATCH.transaction allocation                  81,547     102,013      20,466   

STAT...undo change vector size           384,659,248 384,680,304      21,056   

LATCH.library cache                        7,007,411   7,030,401      22,990   

STAT...free buffer inspected                   5,828      29,489      23,661   

STAT...IMU undo allocation size              101,208      68,064     -33,144   

LATCH.checkpoint queue latch                  38,976      83,349      44,373   

LATCH.object queue header operation          220,296     266,592      46,296   

LATCH.simulator lru latch                     25,498      75,501      50,003   

LATCH.cache buffers lru chain                 52,658     151,989      99,331   

STAT...session uga memory                    130,976           0    -130,976   

STAT...redo size                        ########################     142,776   

LATCH.session idle bit                     1,205,082   1,047,276    -157,806   

STAT...session uga memory max                314,392     130,976    -183,416   

STAT...session pga memory max                262,144           0    -262,144   

STAT...session pga memory                   -262,144           0     262,144   

STAT...execute count                       3,000,546   4,000,623   1,000,077   

STAT...calls to get snapshot scn: kcmgss   3,002,663   4,002,815   1,000,152   

STAT...recursive calls                    16,005,433  17,006,257   1,000,824   

LATCH.simulator hash latch                 2,304,730     664,790  -1,639,940   

LATCH.library cache lock                   8,195,779  10,021,069   1,825,290   

LATCH.cache buffers chains                36,152,269  40,842,410   4,690,141   

                                                                                  

Run1 latches total versus runs -- difference and pct                           

Run1        Run2        Diff       Pct                                         

82,442,582  87,428,038   4,985,456     94.30%                                  

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.28

SQL> exit

Thanks

Comments
Post Details
Added on Sep 12 2013
7 comments
593 views