ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20120417]select生产redo.txt

[20120417]select生产redo.txt

原创 Linux操作系统 作者:lfree 时间:2012-04-17 15:05:49 0 删除 编辑
select生成redo主要有几个原因,常见的主要是修改表记录太多,在commit后,由于记录已经不在数据缓存,在下次select时,再修改相关信息,称为快速提交.

做一个测试:

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<=2;
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;
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;
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<=10)

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;
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<=10)
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。






来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-721428/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2285
  • 访问量
    6024980