TRACE并捕捉跟踪文件中的绑定变量),大家跟踪会话创制连串的进度

update
seq$ set
increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,

 

那么seq$那几个数量字典表是做什么用的呢?
其实这几个数额字典表是保存的是数据库下类别对象(SEQUENCE)的连锁音讯,而且它用来爱抚种类的转变。如下所示,大家通过实验来证实一下,我们启用拾0四陆事变,跟踪一下会话(level=4表示启用SQL_TRACE并捕捉跟踪文件中的绑定变量),我们跟踪会话创造种类的历程。上面测试环境为Oracle
1一g

其它三个题目便是,假设连串是NOCACHE,并发调用类别时,
那么也会爆发row lock contention,
所以给系列设置二个适宜的CACHE值是有非常大好处的,既能减少redo
log的发出,也能幸免减弱row lock
contention(并发更新seq$同1行记录)。不过体系设置了CACHE后,也有不小或者境遇跳号难题。那么这一个就要求基于实际情况酌定思考处理了。

 

 

 

SQL> alter sequence my_sequence_test cache 10;

 

Sequence altered.

 

SQL> set autotrace on;

SQL> select  my_sequence_test.nextval from dual;

 

   NEXTVAL

----------

     50017

 

 

Execution Plan

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

Plan hash value: 1070122491

 

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

| Id  | Operation        | Name             | Rows  | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT |                  |     1 |     2   (0)| 00:00:01 |

|   1 |  SEQUENCE        | MY_SEQUENCE_TEST |       |            |          |

|   2 |   FAST DUAL      |                  |     1 |     2   (0)| 00:00:01 |

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

 

Statistics

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

         30  recursive calls

          3  db block gets

          3  consistent gets

          0  physical reads

        908  redo size

        527  bytes sent via SQL*Net to client

        523  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

SQL> select  my_sequence_test.nextval from dual;

 

   NEXTVAL

----------

     50018

 

 

Execution Plan

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

Plan hash value: 1070122491

 

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

| Id  | Operation        | Name             | Rows  | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT |                  |     1 |     2   (0)| 00:00:01 |

|   1 |  SEQUENCE        | MY_SEQUENCE_TEST |       |            |          |

|   2 |   FAST DUAL      |                  |     1 |     2   (0)| 00:00:01 |

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

 

Statistics

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

          0  recursive calls

          0  db block gets

          0  consistent gets

          0  physical reads

          0  redo size

        527  bytes sent via SQL*Net to client

        523  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

SQL> select  my_sequence_test.nextval from dual;

 

   NEXTVAL

----------

     50019

 

 

Execution Plan

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

Plan hash value: 1070122491

 

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

| Id  | Operation        | Name             | Rows  | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT |                  |     1 |     2   (0)| 00:00:01 |

|   1 |  SEQUENCE        | MY_SEQUENCE_TEST |       |            |          |

|   2 |   FAST DUAL      |                  |     1 |     2   (0)| 00:00:01 |

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

 

Statistics

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

          0  recursive calls

          0  db block gets

          0  consistent gets

          0  physical reads

          0  redo size

        527  bytes sent via SQL*Net to client

        523  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

update
seq$ set increment$=:2, minvalue=:3, maxvalue=:4, cycle#=:5, order$=:6,
cache=:7, highwater=:8, audit$=:9, flags=:10 where obj#=:1

 

 

那么大家接下去,大家修改种类CACHE属性的值,然后再一次上边操作,如下所示,在跟踪文件之中,你会合到只更新了seq$贰遍,其实革新seq$的更新次数是跟CACHE的值有关系的。所以适用的运用CACHE,是足以减掉更新seq$数据字典表的次数。

 obj#=:1

利用上边脚本,你就会发现那些都是对应连串对象的局地消息(系列对象的OBJECT_ID、MINVALUE、MAXVALUE、CACHE等等)

 

除此以外3个题材即便,假诺体系是NOCACHE,并发调用连串时,
那么也会发出row lock contention,
所以给系列设置2个适当的CACHE值是有相当的大好处的,既能减弱redo
log的发生,也能防止裁减row lock
contention(并发更新seq$同1行记录)。然而种类设置了CACHE后,也有十分的大大概遇见跳号难点。那么那个就供给依据实情酌定思量处理了。

动用上边脚本,你就会发觉那个都是对应类别对象的一些音信(种类对象的OBJECT_ID、MINVALUE、MAXVALUE、CACHE等等)

 

 

 

 

 

 

 

betvictor1946 1

SQL> show user;

USER is "TEST"

SQL> alter session set events '10046 trace name context forever, level 4';

 

Session altered.

 

SQL> create sequence my_sequence_test

  2  start with 1

  3  increment by 1

  4  maxvalue 999999999

  5  nocache;

 

Sequence created.

 

SQL> alter session set events '10046 trace name context off';

 

Session altered.

 

SQL> SELECT    a.VALUE

  2         || b.symbol

  3         || LOWER(c.instance_name)

  4         || '_ora_'

  5         || d.spid

  6         || '.trc' trace_file

  7    FROM (SELECT VALUE

  8            FROM v$parameter

  9           WHERE NAME = 'user_dump_dest') a,

 10         (SELECT SUBSTR (VALUE, -6, 1) symbol

 11            FROM v$parameter

 12           WHERE NAME = 'user_dump_dest') b,

 13         (SELECT instance_name

 14            FROM v$instance) c,

 15         (SELECT spid

 16            FROM v$session s, v$process p, v$mystat m

 17           WHERE s.paddr = p.addr AND s.SID = m.SID AND m.statistic# = 0) d

 18  /

 

TRACE_FILE

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

/u01/app/oracle/diag/rdbms/gsp/gsp/trace/gsp_ora_28201.trc

 

 

 

update
seq$ set increment$=:2, minvalue=:3, maxvalue=:4, cycle#=:5, order$=:6,
cache=:7, highwater=:8, audit$=:9, flags=:10 where obj#=:1

 

betvictor1946 2

 

 

SQL> alter sequence my_sequence_test nocache;

 

Sequence altered.

 

SQL> set autotrace on;

SQL> select  my_sequence_test.nextval from dual; 

 

   NEXTVAL

----------

     50015

 

 

Execution Plan

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

Plan hash value: 1070122491

 

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

| Id  | Operation        | Name             | Rows  | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT |                  |     1 |     2   (0)| 00:00:01 |

|   1 |  SEQUENCE        | MY_SEQUENCE_TEST |       |            |          |

|   2 |   FAST DUAL      |                  |     1 |     2   (0)| 00:00:01 |

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

 

Statistics

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

         30  recursive calls

          3  db block gets

          3  consistent gets

          0  physical reads

        908  redo size

        527  bytes sent via SQL*Net to client

        523  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

SQL> select  my_sequence_test.nextval from dual;

 

   NEXTVAL

----------

     50016

 

 

Execution Plan

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

Plan hash value: 1070122491

 

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

| Id  | Operation        | Name             | Rows  | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT |                  |     1 |     2   (0)| 00:00:01 |

|   1 |  SEQUENCE        | MY_SEQUENCE_TEST |       |            |          |

|   2 |   FAST DUAL      |                  |     1 |     2   (0)| 00:00:01 |

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

 

Statistics

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

         14  recursive calls

          4  db block gets

          1  consistent gets

          0  physical reads

        908  redo size

        527  bytes sent via SQL*Net to client

        523  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

SQL> 
SQL> alter sequence my_sequence_test cache 10;

 

Sequence altered.

 

SQL> alter session set sql_trace=true;

 

Session altered.

 

SQL> select my_sequence_test.currval, my_sequence_test.nextval from dual;

 

   CURRVAL    NEXTVAL

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

         5          5

 

SQL> select my_sequence_test.currval, my_sequence_test.nextval from dual;

 

   CURRVAL    NEXTVAL

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

         6          6

 

SQL> select my_sequence_test.currval, my_sequence_test.nextval from dual;

 

   CURRVAL    NEXTVAL

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

         7          7

 

SQL> alter session set sql_trace=false;

 

Session altered.

 

SQL> 

 

 

 

 

update
seq$ set
increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,

 

https://asktom.oracle.com/pls/asktom/f?p=100:11:451611870226342::::P11_QUESTION_ID:2985886242221

betvictor1946 3

 

 obj#=:1

 

betvictor1946 4

 

SQL> alter sequence my_sequence_test cache 10;

 

Sequence altered.

 

SQL> set autotrace on;

SQL> select  my_sequence_test.nextval from dual;

 

   NEXTVAL

----------

     50017

 

 

Execution Plan

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

Plan hash value: 1070122491

 

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

| Id  | Operation        | Name             | Rows  | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT |                  |     1 |     2   (0)| 00:00:01 |

|   1 |  SEQUENCE        | MY_SEQUENCE_TEST |       |            |          |

|   2 |   FAST DUAL      |                  |     1 |     2   (0)| 00:00:01 |

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

 

Statistics

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

         30  recursive calls

          3  db block gets

          3  consistent gets

          0  physical reads

        908  redo size

        527  bytes sent via SQL*Net to client

        523  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

SQL> select  my_sequence_test.nextval from dual;

 

   NEXTVAL

----------

     50018

 

 

Execution Plan

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

Plan hash value: 1070122491

 

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

| Id  | Operation        | Name             | Rows  | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT |                  |     1 |     2   (0)| 00:00:01 |

|   1 |  SEQUENCE        | MY_SEQUENCE_TEST |       |            |          |

|   2 |   FAST DUAL      |                  |     1 |     2   (0)| 00:00:01 |

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

 

Statistics

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

          0  recursive calls

          0  db block gets

          0  consistent gets

          0  physical reads

          0  redo size

        527  bytes sent via SQL*Net to client

        523  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

SQL> select  my_sequence_test.nextval from dual;

 

   NEXTVAL

----------

     50019

 

 

Execution Plan

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

Plan hash value: 1070122491

 

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

| Id  | Operation        | Name             | Rows  | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT |                  |     1 |     2   (0)| 00:00:01 |

|   1 |  SEQUENCE        | MY_SEQUENCE_TEST |       |            |          |

|   2 |   FAST DUAL      |                  |     1 |     2   (0)| 00:00:01 |

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

 

Statistics

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

          0  recursive calls

          0  db block gets

          0  consistent gets

          0  physical reads

          0  redo size

        527  bytes sent via SQL*Net to client

        523  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

tkprof格式化后的出口文件之中,未有绑定变量,在原始跟踪文件gsp_ora_2820一.trc中,你可以见到相应绑定变量的值

SQL> create table test(id  number);

 

Table created.

 

 

begin

        

        for row_num in 1 .. 50000

        loop

          insert into test

            select  my_sequence_test.nextval from dual;

            

            commit;

        end loop;

end;

/

参考资料:

 

 

 

如下所示,你看到INSEHummerH二T语句执行了陆仟0次,而创新seq$执行了五千次,因为地点测试将连串的CACHE设置为10了,假如未有安装CACHE,那么连串被调用六千0次,更新seq$对象也将立异50000次。

 

 

 

动用tkprof将跟踪文件转换来可读格式的公文后,你会小心到:在开立系列时,会往数据字典表seq$中插入一条记下(其实成立系列的原形正是在seq$和obj$中插入了一条记下),如下截图所示:

那么,大家接下去使用SQL
TRACE看看使用SEQUENCE时,会对seq$表有吗操作。如下所示,大家在启用SQL_TRACE后,执行3次该SQL语句

 

tkprof格式化后的出口文件之中,未有绑定变量,在原来跟踪文件gsp_ora_2820一.trc中,你能够看看相应绑定变量的值

 

 

 

betvictor1946 5

where

 

在跟踪文件中(具体经过跟上边查看跟踪文件类似,在此忽略具体进度),你会看出也对seq$做了一遍立异,更新HIGHWATEEnclave的值。

betvictor1946, 

 

 

SQL> create table test(id  number);

 

Table created.

 

 

begin

        

        for row_num in 1 .. 50000

        loop

          insert into test

            select  my_sequence_test.nextval from dual;

            

            commit;

        end loop;

end;

/

 

 
cache=:7,highwater=:8,audit$=:9,flags=:10

 

 

 

SQL> alter sequence my_sequence_test cache 10;

 

Sequence altered.

 

SQL> alter session set sql_trace=true;

 

Session altered.

 

SQL> select my_sequence_test.currval, my_sequence_test.nextval from dual;

 

   CURRVAL    NEXTVAL

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

         5          5

 

SQL> select my_sequence_test.currval, my_sequence_test.nextval from dual;

 

   CURRVAL    NEXTVAL

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

         6          6

 

SQL> select my_sequence_test.currval, my_sequence_test.nextval from dual;

 

   CURRVAL    NEXTVAL

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

         7          7

 

SQL> alter session set sql_trace=false;

 

Session altered.

 

SQL> 

利用tkprof将跟踪文件转换到可读格式的公文后,你会专注到:在开创连串时,会往数据字典表seq$中插入一条记下(其实创设体系的本色就是在seq$和obj$中插入了一条记下),如下截图所示:

SQL> show user;

USER is "TEST"

SQL> alter session set events '10046 trace name context forever, level 4';

 

Session altered.

 

SQL> create sequence my_sequence_test

  2  start with 1

  3  increment by 1

  4  maxvalue 999999999

  5  nocache;

 

Sequence created.

 

SQL> alter session set events '10046 trace name context off';

 

Session altered.

 

SQL> SELECT    a.VALUE

  2         || b.symbol

  3         || LOWER(c.instance_name)

  4         || '_ora_'

  5         || d.spid

  6         || '.trc' trace_file

  7    FROM (SELECT VALUE

  8            FROM v$parameter

  9           WHERE NAME = 'user_dump_dest') a,

 10         (SELECT SUBSTR (VALUE, -6, 1) symbol

 11            FROM v$parameter

 12           WHERE NAME = 'user_dump_dest') b,

 13         (SELECT instance_name

 14            FROM v$instance) c,

 15         (SELECT spid

 16            FROM v$session s, v$process p, v$mystat m

 17           WHERE s.paddr = p.addr AND s.SID = m.SID AND m.statistic# = 0) d

 18  /

 

TRACE_FILE

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

/u01/app/oracle/diag/rdbms/gsp/gsp/trace/gsp_ora_28201.trc

 

 

 

betvictor1946 6

假诺利用CACHE的sequence对象而言,redo
size生成的频率肯定是低得多。如下所示,测试一回,唯有首先次生成了redo
log,
当然这几个是跟系列的CACHE值有关,当缓存的体系值使用完了,生成新的行列值缓存时,也会时有发生redo
log。

那么,大家接下去使用SQL
TRACE看看使用SEQUENCE时,会对seq$表有甚操作。如下所示,大家在启用SQL_TRACE后,执行3次该SQL语句

https://asktom.oracle.com/pls/asktom/f?p=100:11:451611870226342::::P11_QUESTION_ID:2985886242221

 

 
cache=:7,highwater=:8,audit$=:9,flags=:10

 

 

 

[oracle@DB-Server trace]$ tkprof gsp_ora_28201.trc  anay_out_28201.txt aggreage=yes;

LRM-00101: unknown parameter name 'aggreage'

error during command line parsing, cannot continue.

[oracle@DB-Server trace]$ tkprof gsp_ora_28201.trc  anay_out_28201.txt aggregate=yes;

 

TKPROF: Release 11.2.0.1.0 - Development on Tue Aug 29 22:52:08 2017

 

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

 

 

 

 

betvictor1946 7

那正是说大家接下去成立二个表,然后循环递归调用系列,然后生成对应时间段的AW库罗德报告,大家来再次出现一下生育环蒙受到的题材:

 

 

[oracle@DB-Server trace]$ tkprof gsp_ora_28201.trc  anay_out_28201.txt aggreage=yes;

LRM-00101: unknown parameter name 'aggreage'

error during command line parsing, cannot continue.

[oracle@DB-Server trace]$ tkprof gsp_ora_28201.trc  anay_out_28201.txt aggregate=yes;

 

TKPROF: Release 11.2.0.1.0 - Development on Tue Aug 29 22:52:08 2017

 

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

 

 

参考资料:

 

 

 

在跟踪文件中(具体进度跟下边查看跟踪文件类似,在此忽略具体经过),你会看出也对seq$做了三遍革新,更新HIGHWATE奥迪Q5的值。

别的,调用种类也会有一些redo
log费用,如下测试所示,我们先将体系设置为NOCACHE,然后测试进度意识,每一回执行都有900多大小的redo
log生成。

 

betvictor1946 8

betvictor1946 9

 

 

SQL> show user;

USER is "SYS"

SQL> select obj#,increment$,minvalue,maxvalue,cycle#,cache,highwater

  2  from seq$

  3  where obj#=97570;

 

      OBJ# INCREMENT$   MINVALUE   MAXVALUE     CYCLE#      CACHE  HIGHWATER

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

     97570          1          1  999999999          0          0          1

 

SQL> select object_type,object_name from dba_objects

  2  where object_id=97570;

 

OBJECT_TYPE         OBJECT_NAME

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

SEQUENCE            MY_SEQUENCE_TEST

 

SQL> select * from dba_sequences where sequence_name='MY_SEQUENCE_TEST';

 

SEQUENCE_OWNER SEQUENCE_NAME     MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER

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

TEST           MY_SEQUENCE_TEST          1  999999999            1 N N          0           1

 

SQL> 
SQL> show user;

USER is "TEST"

SQL> select my_sequence_test.currval, my_sequence_test.nextval from dual;

 

   CURRVAL    NEXTVAL

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

         1          1

 

SQL> alter session set sql_trace=true;

 

Session altered.

 

SQL> select my_sequence_test.currval, my_sequence_test.nextval from dual;

 

   CURRVAL    NEXTVAL

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

         2          2

 

SQL> select my_sequence_test.currval, my_sequence_test.nextval from dual;

 

   CURRVAL    NEXTVAL

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

         3          3

 

SQL> select my_sequence_test.currval, my_sequence_test.nextval from dual;

 

   CURRVAL    NEXTVAL

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

         4          4

 

SQL> alter session set sql_trace=false;

 

Session altered.

 

SQL> 

 

 

那么我们接下去成立1个表,然后循环递归调用连串,然后生成对应时间段的AWHummerH二报告,大家来再次出现一下生产条件遭受的标题:

 

 

 

SQL> show user;

USER is "TEST"

SQL> select my_sequence_test.currval, my_sequence_test.nextval from dual;

 

   CURRVAL    NEXTVAL

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

         1          1

 

SQL> alter session set sql_trace=true;

 

Session altered.

 

SQL> select my_sequence_test.currval, my_sequence_test.nextval from dual;

 

   CURRVAL    NEXTVAL

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

         2          2

 

SQL> select my_sequence_test.currval, my_sequence_test.nextval from dual;

 

   CURRVAL    NEXTVAL

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

         3          3

 

SQL> select my_sequence_test.currval, my_sequence_test.nextval from dual;

 

   CURRVAL    NEXTVAL

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

         4          4

 

SQL> alter session set sql_trace=false;

 

Session altered.

 

SQL> 

 

betvictor1946 10

SQL> alter sequence my_sequence_test nocache;

 

Sequence altered.

 

SQL> set autotrace on;

SQL> select  my_sequence_test.nextval from dual; 

 

   NEXTVAL

----------

     50015

 

 

Execution Plan

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

Plan hash value: 1070122491

 

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

| Id  | Operation        | Name             | Rows  | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT |                  |     1 |     2   (0)| 00:00:01 |

|   1 |  SEQUENCE        | MY_SEQUENCE_TEST |       |            |          |

|   2 |   FAST DUAL      |                  |     1 |     2   (0)| 00:00:01 |

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

 

Statistics

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

         30  recursive calls

          3  db block gets

          3  consistent gets

          0  physical reads

        908  redo size

        527  bytes sent via SQL*Net to client

        523  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

SQL> select  my_sequence_test.nextval from dual;

 

   NEXTVAL

----------

     50016

 

 

Execution Plan

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

Plan hash value: 1070122491

 

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

| Id  | Operation        | Name             | Rows  | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT |                  |     1 |     2   (0)| 00:00:01 |

|   1 |  SEQUENCE        | MY_SEQUENCE_TEST |       |            |          |

|   2 |   FAST DUAL      |                  |     1 |     2   (0)| 00:00:01 |

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

 

Statistics

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

         14  recursive calls

          4  db block gets

          1  consistent gets

          0  physical reads

        908  redo size

        527  bytes sent via SQL*Net to client

        523  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

SQL> 

 

 

 

 

 

betvictor1946 11

 

where

http://www.xifenfei.com/forum/performance/%E5%85%B3%E4%BA%8Eoracle-sequence%E4%B8%80%E4%BA%9B%E5%B0%8F%E6%B5%8B%E8%AF%95

 

假如使用CACHE的sequence对象而言,redo
size生成的功效肯定是低得多。如下所示,测试一回,唯有首先次生成了redo
log,
当然那几个是跟系列的CACHE值有关,当缓存的体系值使用完了,生成新的队列值缓存时,也会爆发redo
log。

 

 

SQL> show user;

USER is "SYS"

SQL> select obj#,increment$,minvalue,maxvalue,cycle#,cache,highwater

  2  from seq$

  3  where obj#=97570;

 

      OBJ# INCREMENT$   MINVALUE   MAXVALUE     CYCLE#      CACHE  HIGHWATER

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

     97570          1          1  999999999          0          0          1

 

SQL> select object_type,object_name from dba_objects

  2  where object_id=97570;

 

OBJECT_TYPE         OBJECT_NAME

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

SEQUENCE            MY_SEQUENCE_TEST

 

SQL> select * from dba_sequences where sequence_name='MY_SEQUENCE_TEST';

 

SEQUENCE_OWNER SEQUENCE_NAME     MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER

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

TEST           MY_SEQUENCE_TEST          1  999999999            1 N N          0           1

 

SQL> 

 

1般来说所示,你见到INSE安德拉T语句执行了四千0次,而立异seq$执行了五千次,因为地点测试将连串的CACHE设置为拾了,假使未有设置CACHE,那么种类被调用40000次,更新seq$对象也将履新陆仟0次。

 

在条分缕析ORACLE的AW本田UR-V报告时,发现SQL
ordered by Executions(记录了如约SQL的实施次数排序的TOP
SQL。该排序能够见见监察和控制范围内的SQL执行次数)下有一个SQL语句执行分外频仍,一个小时实施了上万次:

betvictor1946 12

 

在条分缕析ORACLE的AWOdyssey报告时,发现SQL
ordered by Executions(记录了如约SQL的实行次数排序的TOP
SQL。该排序可以见到监察和控制范围内的SQL执行次数)下有3个SQL语句执行非凡频仍,一个钟头实施了上万次:

http://www.xifenfei.com/forum/performance/%E5%85%B3%E4%BA%8Eoracle-sequence%E4%B8%80%E4%BA%9B%E5%B0%8F%E6%B5%8B%E8%AF%95

那正是说大家接下去,大家修改体系CACHE属性的值,然后再次上边操作,如下所示,在跟踪文件之中,你会看出只更新了seq$二次,其实立异seq$的翻新次数是跟CACHE的值有关联的。所以适当的行使CACHE,是足以削减更新seq$数据字典表的次数。

 

那正是说seq$这些数目字典表是做什么用的吧?
其实那么些数额字典表是保存的是数据库下系列对象(SEQUENCE)的相关音讯,而且它用来珍贵体系的更动。如下所示,我们经超过实际验来证实一下,大家启用十0四六风云,跟踪一下会话(level=肆表示启用SQL_TRACE并捕捉跟踪文件中的绑定变量),大家跟踪会话创设体系的长河。上面测试环境为Oracle
1一g

 

 

 

 

别的,调用种类也会有一对redo
log费用,如下测试所示,大家先将类别设置为NOCACHE,然后测试进度意识,每一遍执行都有900多大小的redo
log生成。

 

 

 

相关文章