一个select sql 产生很多redo.

Leo

10.2.0.4.0   单机        NOARCHIVELOG模式

一个sql 执行，产生大量的redo，是什么原因呢？

Leo

1. SQL>  select '1' logType,
   
2.   2             (select count(*) totaLoginNum
   
3.   3                from oss.t_user_login_log t
   
4.   4               where t.login_time > trunc(SYSDATE, 'dd') - 1
   
5.   5                 and t.log_type = 1) totalUserNum,
   
6.   6             sysdate - 1,
   
7.   7             '1' statType
   
8.   8        from dual;
   
9. 
   
10. 已用时间:  00: 00: 07.63
    
11. 
    
12. 执行计划
    
13. ----------------------------------------------------------
    
14. Plan hash value: 1824551839
    
15. 
    
16. ----------------------------------------------------------------------------------------------------
    
17. | Id  | Operation                    | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
    
18. ----------------------------------------------------------------------------------------------------
    
19. |   0 | SELECT STATEMENT             |                              |     1 |       |     2   (0)| 00:00:01 |
    
20. |   1 |  SORT AGGREGATE              |                              |     1 |    11 |            |          |
    
21. |*  2 |   TABLE ACCESS BY INDEX ROWID| T_USER_LOGIN_LOG             |     1 |    11 |     5   (0)| 00:00:01 |
    
22. |*  3 |    INDEX RANGE SCAN          | T_USER_LOG_IINDEX_LOGIN_TIME |    10 |       |     3   (0)| 00:00:01 |
    
23. |   4 |  FAST DUAL                   |                              |     1 |       |     2   (0)| 00:00:01 |
    
24. ----------------------------------------------------------------------------------------------------
    
25. 
    
26. Predicate Information (identified by operation id):
    
27. ---------------------------------------------------
    
28. 
    
29.    2 - filter("T"."LOG_TYPE"=1)
    
30.    3 - access("T"."LOGIN_TIME">TRUNC(SYSDATE@!,'fmdd')-1)
    
31. 
    
32. 
    
33. 统计信息
    
34. ----------------------------------------------------------
    
35.         177  recursive calls
    
36.           0  db block gets
    
37.      183826  consistent gets
    
38.        4526  physical reads
    
39.        7344  redo size
    
40.         514  bytes sent via SQL*Net to client
    
41.         350  bytes received via SQL*Net from client
    
42.           2  SQL*Net roundtrips to/from client
    
43.           5  sorts (memory)
    
44.           0  sorts (disk)
    
45.           1  rows processed

复制代码

截图好像不清晰，还是copy出来 贴上来.

背向天堂

每次都有redo产生吗？ 这个应该和延迟块清理有关。

ShineCQY

Thomas Kyte《Expert one-on-one Oracle》
Block Cleanout
If you recall earlier, Chapter 3 on Locking and Concurrency, when we talked about datalocks and how they were managed, I described how they are actually attributes of the data,stored on the block header. A side effect of this is that the next time that block is accessed,we may have to ʹclean it outʹ, in other words, remove the transaction information. Thisaction generates redo and causes the block to become ʹdirtyʹ if it wasnʹt already. What thismeans is that a simple SELECT may generate redo, and may cause lots of blocks to bewritten to disk with the next checkpoint.
请参考：http://www.itpub.net/thread-1300346-1-1.html

Maclean Liu(刘相兵

select  产生redo很正常

详见 delay block cleanout