ANBOB™

理论上select是不会产生redo的，不果有一种现象叫做 延迟块清除，下面这个例子看看怎么理解

sql>conn system/oracle

sql>set autot on;

SQL> select count(*) from zwz.testredo;

COUNT(*)
———-
52840

Execution Plan
———————————————————-
Plan hash value: 1457698251

———————————————————————–
| Id  | Operation          | Name     | Rows  | Cost (%CPU)| Time     |
———————————————————————–
|   0 | SELECT STATEMENT   |          |     1 |   158   (1)| 00:00:02 |
|   1 |  SORT AGGREGATE    |          |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TESTREDO | 68430 |   158   (1)| 00:00:02 |
———————————————————————–

Note
—–
– dynamic sampling used for this statement

Statistics
———————————————————-
0  recursive calls
0  db block gets
12787  consistent gets
0  physical reads
4780 redo size
517  bytes sent via SQL*Net to client
492  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed

看到生成了redo,而且多次查询还是一样生成redo,为什么呢，呵呵往下看

SQL> select addr,status,ses_addr from v$transaction;

ADDR             STATUS           SES_ADDR
—————- —————- —————-
00000000D9D48850 ACTIVE           00000000DB450120

SQL> set linesize 200;

SQL> col machine for a10
SQL> run
1* select sid,prev_sql_addr,taddr,username,status,machine from v$session where taddr=’00000000D9D48850′

SID PREV_SQL_ADDR    TADDR            USERNAME                       STATUS   MACHINE
———- —————- —————- —————————— ——– ———-
1094 00000000D16CF780 00000000D9D48850 ZWZ                            INACTIVE aix

SQL> col sql_text for a100;
SQL> run
1* select sql_text,address from v$sql where address=’00000000D16CF780′

SQL_TEXT                                                                                             ADDRESS
—————————————————————————————————- —————-
update testredo set subobject_name=’test_’||subobject_name where rownum<6000                         00000000D16CF780

————呵呵，关键就在这里，我是在另一个session里对这个表做了更新，而没有提交

回到update 的session,rollback;

再查询一下

SQL> select count(*) from zwz.testredo;

COUNT(*)
———-
52840

Execution Plan
———————————————————-
Plan hash value: 1457698251

———————————————————————–
| Id  | Operation          | Name     | Rows  | Cost (%CPU)| Time     |
———————————————————————–
|   0 | SELECT STATEMENT   |          |     1 |   158   (1)| 00:00:02 |
|   1 |  SORT AGGREGATE    |          |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TESTREDO | 52840 |   158   (1)| 00:00:02 |
———————————————————————–

Statistics
———————————————————-
0  recursive calls
0  db block gets
715  consistent gets
0  physical reads
0  redo size
517  bytes sent via SQL*Net to client
492  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed

SQL>

打赏

微信扫一扫，打赏作者吧～