今天在优化一个select发现一个问题,不知道为什么每次都会产生redo,而且产生的日志还很大,块清除也会产生redo,不可能多次执行一样的sql还会产生redo?移动到测试系统发现,sql语句中要执行seq1.nextval的顺序号我没有建立,检查这个seq1马上发现,这个seq1的建立属性为NOCACHE,这样导致每次select的时候都要修改系统表sys.seq$。修改为cache属性,加大cache=1000后,再执行,问题消失。
一般通过seqence很难保证序号不跳号的,看来问题主要是开发人员不了解seq的特性。
测试例子:
CREATE SEQUENCE SCOTT.AA
START WITH 1
MAXVALUE 100
MINVALUE 1
CYCLE
NOCACHE
NOORDER;
=> set autotrace traceonly ;
=> select aa.nextval from dual ;
Execution Plan
----------------------------------------------------------
Plan hash value: 4244615631
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | SEQUENCE | AA | | | |
| 2 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------
Statistics
----------------------------------------------------------
15 recursive calls
3 db block gets
1 consistent gets
0 physical reads
700 redo size
417 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
=> select aa.nextval,dept.* from dept ;
Execution Plan
------------------------------select aa.nextval from dual ;----------------------------
Plan hash value: 4032135561
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 91 | 3 (0)| 00:00:01 |
| 1 | SEQUENCE | AA | | | | |
| 2 | TABLE ACCESS FULL| DEPT | 7 | 91 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Statistics
----------------------------------------------------------
98 recursive calls
22 db block gets
15 consistent gets
0 physical reads
4636 redo size
751 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
7 rows processed
可以发现访问的记录越多,redo越大.
修改ALTER SEQUENCE SCOTT.AA INCREMENT BY 1 MINVALUE 1 MAXVALUE 100 CACHE 20 CYCLE NOORDER;后,
再执行:
select aa.nextval from dual ;
就没有redo产生了。
一般通过seqence很难保证序号不跳号的,看来问题主要是开发人员不了解seq的特性。
测试例子:
CREATE SEQUENCE SCOTT.AA
START WITH 1
MAXVALUE 100
MINVALUE 1
CYCLE
NOCACHE
NOORDER;
=> set autotrace traceonly ;
=> select aa.nextval from dual ;
Execution Plan
----------------------------------------------------------
Plan hash value: 4244615631
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | SEQUENCE | AA | | | |
| 2 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------
Statistics
----------------------------------------------------------
15 recursive calls
3 db block gets
1 consistent gets
0 physical reads
700 redo size
417 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
=> select aa.nextval,dept.* from dept ;
Execution Plan
------------------------------select aa.nextval from dual ;----------------------------
Plan hash value: 4032135561
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 91 | 3 (0)| 00:00:01 |
| 1 | SEQUENCE | AA | | | | |
| 2 | TABLE ACCESS FULL| DEPT | 7 | 91 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Statistics
----------------------------------------------------------
98 recursive calls
22 db block gets
15 consistent gets
0 physical reads
4636 redo size
751 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
7 rows processed
可以发现访问的记录越多,redo越大.
修改ALTER SEQUENCE SCOTT.AA INCREMENT BY 1 MINVALUE 1 MAXVALUE 100 CACHE 20 CYCLE NOORDER;后,
再执行:
select aa.nextval from dual ;
就没有redo产生了。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-524627/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-524627/