Oracle Sequence Introduction and performance testing

I. Theoretical knowledge

Let's look at a statement that creates sequence:

sql> Create sequence Seq_tmp

2 Increment by 1

3 Start with 1

4 Nomaxvalue

5 nocycle

6;

The sequence has been created.

Related parameter Description:

INCREMENT by 1--add a few each time

Start with 1-counting starting from 1

Nomaxvalue--Do not set the maximum value

Nocycle--keep accumulating, not looping

CACHE 10; --Set cache caches sequence

Currval= returns the current value of the sequence

Nextval= increases the value of sequence and then returns the sequence value

For more information, refer to Oracle online documentation:

Cache Cache (Ceil (maxvalue-minvalue))/ABS (INCREMENT)

Cachenote:

Cachenocache nocachecachenocacheOrder to guarantee that sequence numbers is generated in order of req Uest. This clause was useful if you are using the sequence numbers as timestamps. Guaranteeing order is usually isn't important for sequences used to generate primary keys.

ORDERNoorder If you don't want to guarantee sequence numbers is generated in order of request. This is the default.

To view the structure of the User_sequences table:

sql> desc user_sequences;

is the name empty? Type

----------------------------------------- -------- ---------------

Sequence_name not NULL VARCHAR2 (30)

Min_value number

Max_value number

Increment_by not NULL number

Cycle_flag VARCHAR2 (1)

Order_flag VARCHAR2 (1)

Cache_size not NULL number

Last_number not NULL number

View the values of the sequence seq_tmp that you just created:

Sql> SELECT * from user_sequences where sequence_name= ' seq_tmp ';

Sequence_n min_value max_value increment_by C O cache_size last_number

---------- ---------- ---------- ------------ - - ---------- -----------

Seq_tmp 1 1.0000E+28 1 n n 20 21

Here's a cache_size value. When we created the sequence, we enabled the cache, but we didn't give it a value. So the cache_size here is the system's mode value. That's 20 of them.

To remove a sequence value:

Sql> select Seq_tmp.nextval from dual;

Nextval

----------

1

Sql> select Seq_tmp.nextval from dual;

Nextval

----------

2

To view the value of the current sequence:

Sql> select Seq_tmp.currval from dual;

Currval

----------

To modify the cache size:

If the cache is already specified, we can modify the cache size. The ALTER command can modify all parameters except start in sequence.

Such as:

Alter sequence emp_sequence cache 100;

SELECT * from User_sequences where Sequence_name=upper (' emp_sequence ');

Two. Experiments

Test results on a user's RAC system:

nocache:2100s

Cache =1000:55s

The difference is obvious.

Test One:

sql> Create sequence seq_1 nocache;

The sequence has been created.

Sql> set timing on;

Sql> Declare

2 x number;

3 begin

4 for I in 1.. 10000 loop

5 Select Seq_1.nextval to x from dual;

6 end Loop;

7 End;

8/

The PL/SQL process has completed successfully.

Time used: 00:00:02.26

Test Two:

Sql> Create sequence seq_2 cache 20;

The sequence has been created.

Time used: 00:00:00.01

Sql> Declare

2 x number;

3 begin

4 for I in 1.. 10000 loop

5 Select Seq_2.nextval to x from dual;

6 end Loop;

7 End;

8/

The PL/SQL process has completed successfully.

Time used: 00:00:00.46

Test Three:

Sql> Create sequence seq_3 cache 100;

The sequence has been created.

Time used: 00:00:00.05

Sql> Declare

2 x number;

3 begin

4 for I in 1.. 10000 loop

5 Select Seq_3.nextval to x from dual;

6 end Loop;

7 End;

8/

The PL/SQL process has completed successfully.

Time used: 00:00:00.37

Test four:

Sql> Create sequence seq_4 cache 1000;

The sequence has been created.

Time used: 00:00:00.04

Sql> Declare

2 x number;

3 begin

4 for I in 1.. 40000 loop

5 Select Seq_4.nextval to x from dual;

6 end Loop;

7 End;

8/

The PL/SQL process has completed successfully.

Time used: 00:00:01.31

Sql> Declare

2 x number;

3 begin

4 for I in 1.. 40000 loop

5 Select Seq_1.nextval to x from dual;

6 end Loop;

7 End;

8/

The PL/SQL process has completed successfully.

Time used: 00:00:09.33

Sql>

Summary:

Tested on your own laptop, Oracle 11gR2. Single Instance database single session loop 140,000 values are not interrupted.

nocache:2.26s 10000

CACHE:20 0.46s 10000

CACHE:100 0.37s 10000

cache:1000 1.31s 40000

nocache:9.33s 40000

Basically the cache is more than 20 when the performance is basically acceptable, nocache when the performance is really poor.

This article is quoted from: http://blog.csdn.net/tianlesoftware/article/details/5995051

Oracle Sequence Introduction and performance testing