oracle字符乱码 老熊,【转载】Hint的常见错误使用方式

老熊 Reply:

一月 10th, 2012 at 10:22 下午

的确是个很奇怪的现象。下面是我测试的结果，第1次带有nologging，redosize为0，但是第2次insert仍然带有nologging，redosize就变成了1100，第3次insert不带有nologging，redosize仍然是1100。

SQL> set autot on

SQL> insert into t2 select * from t1 nologging;

已创建10行。

执行计划

———————————————————-

Plan hash value: 3617692013

————————————————————————–

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

————————————————————————–

| 0 | INSERT STATEMENT | | 11402 | 946K| 40 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| T1 | 11402 | 946K| 40 (0)| 00:00:01 |

————————————————————————–

统计信息

———————————————————-

266 recursive calls

8 db block gets

40 consistent gets

0 physical reads

0 redo size

1767 bytes sent via SQL*Net to client

1149 bytes received via SQL*Net from client

6 SQL*Net roundtrips to/from client

2 sorts (memory)

0 sorts (disk)

10 rows processed

SQL> insert into t2 select * from t1 nologging;

已创建10行。

执行计划

———————————————————-

Plan hash value: 3617692013

————————————————————————–

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

————————————————————————–

| 0 | INSERT STATEMENT | | 11402 | 946K| 40 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| T1 | 11402 | 946K| 40 (0)| 00:00:01 |

————————————————————————–

统计信息

———————————————————-

0 recursive calls

1 db block gets

7 consistent gets

0 physical reads

1100 redo size

908 bytes sent via SQL*Net to client

950 bytes received via SQL*Net from client

4 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

10 rows processed

SQL> insert into t2 select * from t1 ;

已创建10行。

执行计划

———————————————————-

Plan hash value: 3617692013

————————————————————————–

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

————————————————————————–

| 0 | INSERT STATEMENT | | 11402 | 946K| 40 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| T1 | 11402 | 946K| 40 (0)| 00:00:01 |

————————————————————————–

统计信息

———————————————————-

1 recursive calls

1 db block gets

7 consistent gets

0 physical reads

1100 redo size

908 bytes sent via SQL*Net to client

941 bytes received via SQL*Net from client

4 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

10 rows processed

我们再做另一个测试，第1次插入不带nologging，而第2次插入带nologging，这一次测试不带nologging的redosize反而是0了。

SQL> truncate table t2;

表被截断。

SQL> set autot on

SQL> insert into t2 select * from t1;

已创建10行。

执行计划

———————————————————-

Plan hash value: 3617692013

————————————————————————–

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

————————————————————————–

| 0 | INSERT STATEMENT | | 11402 | 946K| 40 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| T1 | 11402 | 946K| 40 (0)| 00:00:01 |

————————————————————————–

统计信息

———————————————————-

1 recursive calls

8 db block gets

13 consistent gets

0 physical reads

0 redo size

1769 bytes sent via SQL*Net to client

1143 bytes received via SQL*Net from client

6 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

10 rows processed

SQL> insert into t2 select * from t1 nologging;

已创建10行。

执行计划

———————————————————-

Plan hash value: 3617692013

————————————————————————–

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

————————————————————————–

| 0 | INSERT STATEMENT | | 11402 | 946K| 40 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| T1 | 11402 | 946K| 40 (0)| 00:00:01 |

————————————————————————–

统计信息

———————————————————-

1 recursive calls

1 db block gets

7 consistent gets

0 physical reads

1100 redo size

909 bytes sent via SQL*Net to client

954 bytes received via SQL*Net from client

4 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

10 rows processed

看起来这应该是autotrace的问题，准确的结果应该是看v$sesstat里面的redosize统计值。