oracle产生redo最多的sql,一种select产生redo的情况

理论上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>

打赏

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