select生成redo主要有几个原因,常见的主要是修改表记录太多,在commit后,由于记录已经不在数据缓存,在下次select时,再修改相关信息,称为快速提交.
做一个测试:
1.快速提交产生的:
2.select的中的字段带有sequence号,顺序号使用完要更新sys.seq$,会产生redo信心.
--可以发现没有redo。
做一个测试:
1.快速提交产生的:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> show sga
Total System Global Area 1068937216 bytes
Fixed Size 2220200 bytes
Variable Size 834670424 bytes
Database Buffers 226492416 bytes
Redo Buffers 5554176 bytes
SQL> select .25* 226492416 /8192 from dual ;
.25*226492416/8192
------------------
6912
SQL> create table t2 as select rownum id ,'a' name from dual connect by level
Table created.
SQL> ALTER TABLE t2 MINIMIZE RECORDS_PER_BLOCK;
Table altered.
--这样每个数据块的记录仅仅2条.
SQL> insert into t2 select rownum id ,'a' name from dual connect by level
14000 rows created.
SQL> commit ;
Commit complete.
SQL> set autot traceonly ;
SQL> select count(*) from t2 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 3321871023
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1913 (1)| 00:00:23 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T2 | 14774 | 1913 (1)| 00:00:23 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
29 recursive calls
1 db block gets
11564 consistent gets
0 physical reads
311216 redo size
528 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--可以发现产生大量redo.第2次执行,redo变为0
SQL> select count(*) from t2 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 3321871023
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1913 (1)| 00:00:23 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T2 | 14774 | 1913 (1)| 00:00:23 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7058 consistent gets
0 physical reads
0 redo size
528 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
2.select的中的字段带有sequence号,顺序号使用完要更新sys.seq$,会产生redo信心.
SQL> create sequence test_seq nocache;
SQL> set autot traceonly ;
SQL> select t2.* from t2 where rownum
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1154646200
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 160 | 3 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS FULL| T2 | 14774 | 230K| 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
732 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
--没有redo生成!
SQL> select test_seq.nextval,t2.* from t2 where rownum
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2371786635
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 160 | 3 (0)| 00:00:01 |
| 1 | SEQUENCE | TEST_SEQ | | | | |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | TABLE ACCESS FULL| T2 | 14774 | 230K| 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
162 recursive calls
30 db block gets
175 consistent gets
0 physical reads
6524 redo size
807 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
--由于sequence没有cache,导致每次都更新sys.seq$,导致日志产生。
3.select如果要建立临时表空间，会产生redo。
SQL> set autot traceonly ;
SQL> with a as ( select /*+ materialize */ * from emp ) select * from a;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2922916991
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 1305 | 5 (0)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D660C_8B1198 | | | | |
| 3 | TABLE ACCESS FULL | EMP | 15 | 540 | 3 (0)| 00:00:01 |
| 4 | VIEW | | 15 | 1305 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660C_8B1198 | 15 | 540 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
2 recursive calls
8 db block gets
13 consistent gets
1 physical reads
600 redo size
1571 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
SQL> set autot off
SQL> with a as ( select /*+ materialize */ * from emp ) select * from a;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
14 rows selected.
SQL> @dpc
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 3f5gc86jaqhct, child number 0
-------------------------------------
with a as ( select /*+ materialize */ * from emp ) select * from a
Plan hash value: 236646685
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)| OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 5 (100)| | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | |
| 2 | LOAD AS SELECT | | | | 269K| 269K| 269K (0)|
| 3 | TABLE ACCESS FULL | EMP | 15 | 3 (0)| | | |
| 4 | VIEW | | 15 | 2 (0)| | | |
| 5 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660A_8B1198 | 15 | 2 (0)| | | |
-----------------------------------------------------------------------------------------------------------------
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
23 rows selected.
--删除materialize提示：
SQL> set autot traceonly ;
SQL> with a as ( select /*+ 111materialize */ * from emp ) select * from a;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 540 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 15 | 540 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
1630 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
--可以发现没有redo。
