Sequences

SEQUENCES Document History # MODIFIED (MM/DD/YY) # epost 05/1/06 – Created.



  



Every sequence name should match the column name it is primarily used for? o Why? Because it makes it very easy for us to determine which tables the sequence is associated with and to validate that the existing data is not in conflict with the sequence. This can easily happen when migrating data from one database to another. Sequences are checked into SourceSafe into the sequences folder. File names must start with “seq”. File names must be lower-case.

The file should contain a command to create a public synonym for the sequence. It is preferred that you use the procedure in PERADIGM_CONFIG package to do this. exec peradigm_config.create_public_synonym(‘HSD_MYOBJECT’, ’MYOBJECT’);



NEVER include a DROP SEQUENCE statement in your file.





It has never been the practice to use triggers to assign sequence values when data is being inserted into tables. Do not begin this practice without an email to the configuration management team which includes a reference to this document. New sequence related columns should not limit the size of the number that may be inserted.

CACHE or NOCACHE When you create a sequence you have the option to specify the option to set the caching option. You should cache values on sequences that will be called frequently. When the database is restarted values in cache will be lost but this should not be a concern because you SHOULD NEVER use sequences when your application requires that no value be lost. If you do not cache values there is a significant performance hit.

You have two sequences.

and…

Write two procedures, to test each of the sequences…

Test by calling each procedure 3 times and showing stats. Run 1 below, shows the values when cache is used, run 2 when no cache is used. You can see that run2 used significantly more latches than run1. When cached procedure averaged 1.5 seconds.

When no cache was used procedure took 10 seconds!

NAME RUN1 RUN2 DIFF --------------------------------------------- ---------- ---------- ---------LATCH.Consistent RBA 6 898 892 LATCH.SQL memory manager workarea list latch 268 804 536 LATCH.active checkpoint queue latch 5 45 40 LATCH.cache buffer handles 2 4 2 LATCH.cache buffers chains 181070 576755 395685 LATCH.cache buffers lru chain 24 1705 1681 LATCH.channel operations parent latch 8 24 16 LATCH.checkpoint queue latch 275 3667 3392 LATCH.child cursor hash table 7 7 0 LATCH.dml lock allocation 124 60237 60113 LATCH.enqueue hash chains 216 120458 120242 LATCH.enqueues 26 30090 30064 LATCH.job_queue_processes parameter latch 0 1 1 LATCH.kwqit: protect wakeup time 1 2 1 LATCH.lgwr LWN SCN 6 899 893 LATCH.library cache 150655 511016 360361 LATCH.library cache pin 120317 360527 240210 LATCH.library cache pin allocation 232 120380 120148 LATCH.messages 118 58693 58575 LATCH.mostly latch-free SCN 6 899 893 LATCH.ncodef allocation latch 0 1 1 LATCH.post/wait queue 1 21 20 LATCH.redo allocation 124 62060 61936 LATCH.redo writing 31 2764 2733 LATCH.row cache enqueue latch 70 60178 60108 LATCH.row cache objects 82 180186 180104 LATCH.sequence cache 90006 60000 -30006 LATCH.session allocation 80 60273 60193 LATCH.session idle bit 34 30032 29998 LATCH.session switching 0 1 1 LATCH.session timer 4 13 9 LATCH.shared pool 60618 210743 150125 LATCH.simulator hash latch 0 217 217 LATCH.simulator lru latch 0 45 45 LATCH.sort extent pool 0 1 1 LATCH.spilled msgs queues list latch 0 1 1 LATCH.transaction branch allocation 0 1 1 LATCH.undo global data 120 90295 90175 STAT...CPU used by this session 4 4 0 STAT...CPU used when call started 4 4 0 STAT...SQL*Net roundtrips to/from client 3 3 0 STAT...active txn count during cleanout 0 1 1 STAT...buffer is not pinned count 12 12 0

STAT...bytes received via SQL*Net from client STAT...bytes sent via SQL*Net to client STAT...calls to get snapshot scn: kcmgss STAT...calls to kcmgas STAT...calls to kcmgcs STAT...cleanout - number of ktugct calls STAT...commit cleanouts STAT...commit cleanouts successfully complete STAT...consistent gets STAT...consistent gets - examination STAT...db block changes STAT...db block gets STAT...deferred (CURRENT) block cleanout appl STAT...enqueue releases STAT...enqueue requests STAT...execute count STAT...free buffer requested STAT...messages sent STAT...no work - consistent read gets STAT...opened cursors cumulative STAT...parse count (hard) STAT...parse count (total) STAT...parse time cpu STAT...parse time elapsed STAT...recursive calls STAT...redo entries STAT...redo size STAT...redo synch time STAT...redo synch writes STAT...session logical reads STAT...session pga memory max STAT...sorts (memory) STAT...sorts (rows) STAT...table fetch by rowid STAT...user calls STAT...user commits STAT...workarea executions - optimal 80 rows selected.

1085 563 6 1 3 0 4 4 10 0 36 31 0 6 6 6 4 1 6 6 1 6 1 1 22 22 28044 3 1 41 131072 4 2471 6 5 1 8

1085 563 6 1 3 1 4 4 11 1 32 25 1 4 4 6 4 1 6 6 1 6 1 1 22 19 28332 4 1 36 0 4 2471 6 5 1 8

0 0 0 0 0 1 0 0 1 1 -4 -6 1 -2 -2 0 0 0 0 0 0 0 0 0 0 -3 288 1 0 -5 -131072 0 0 0 0 0 0