Have you ever met situation that for “select only” operation “redo size” has different value than 0 in autotrace output?
It’s possible that sometimes we may face a bug and setting event 70150 or 71830 in database could fix this issue. One example below:
Simple count:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
SQL> set autotrace on SQL> select count(*) from ORAVSP; COUNT(*) ---------- 67108864 Elapsed: 00:00:10.79 Execution Plan ---------------------------------------------------------- Plan hash value: 1321210629 --------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | --------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 598K (96)| 01:59:42 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| ORAVSP | 64G| 598K (96)| 01:59:42 | --------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 4 recursive calls 0 db block gets 263992 consistent gets 202814 physical reads 4345324 redo size <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 425 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 |
As you can see redo size in statistics part is 4345324
Let’s try to set some events:
|
1 2 3 4 5 6 7 |
SQL> alter session set events '71830'; Session altered. Elapsed: 00:00:00.02 SQL> alter session set events '70150'; Session altered. Elapsed: 00:00:00.00 |
And try to count again:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
SQL> select count(*) from ORAVSP; COUNT(*) ---------- 67108864 Elapsed: 00:00:01.85 Execution Plan ---------------------------------------------------------- Plan hash value: 1321210629 --------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | --------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 598K (96)| 01:59:42 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| ORAVSP | 64G| 598K (96)| 01:59:42 | --------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 143041 consistent gets 101897 physical reads 0 redo size 425 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 |
As you can see execution time is the same, however we didn’t use redo = “redo size” value is 0
Querying again – “redo size” still empty
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 |
SQL>select count(*) from ORAVSP; COUNT(*) ---------- 67108864 Elapsed: 00:00:01.88 Execution Plan ---------------------------------------------------------- Plan hash value: 1321210629 --------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | --------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 598K (96)| 01:59:42 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| ORAVSP | 64G| 598K (96)| 01:59:42 | --------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 143041 consistent gets 101897 physical reads 0 redo size 425 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 SQL>select count(*) from ORAVSP; COUNT(*) ---------- 67108864 Elapsed: 00:00:01.91 Execution Plan ---------------------------------------------------------- Plan hash value: 1321210629 --------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | --------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 598K (96)| 01:59:42 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| ORAVSP | 64G| 598K (96)| 01:59:42 | --------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 143041 consistent gets 101897 physical reads 0 redo size 425 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 |