ITPub博客

首页 > Linux操作系统 > Linux操作系统 > nologging对delete以及update效果不大

nologging对delete以及update效果不大

原创 Linux操作系统 作者:安佰胜 时间:2011-01-24 17:54:41 0 删除 编辑
SQL> create table a (id int);

Table created.

SQL> begin for i in 1..10000 loop
  2  insert into a select i from dual;
  3  end loop;
  4  commit;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> insert into a select * from a;

10000 rows created.


SQL> desc a;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(38)


SQL> begin for i in 1..10000 loop
  2  insert into a select i from dual;
  3  end loop;
  4  commit;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select count(*) from a;

  COUNT(*)
----------
     10000

SQL>
SQL> set autotrace traceonly;
SQL>


----------
--insert

SQL>insert into a select * from a;

10000 rows created.


Execution Plan
----------------------------------------------------------
   0      INSERT STATEMENT ptimizer=ALL_ROWS (Cost=8 Card=20000 Bytes
          =260000)

   1    0   TABLE ACCESS (FULL) OF 'A' (TABLE) (Cost=8 Card=20000 Byte
          s=260000)

 

 

Statistics
----------------------------------------------------------
         58  recursive calls
      10238  db block gets
       3284  consistent gets
          0  physical reads
    2373796  redo size
        643  bytes sent via SQL*Net to client
        538  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      10000  rows processed

SQL>insert into a nologging select * from a;

10000 rows created.


Execution Plan
----------------------------------------------------------
   0      INSERT STATEMENT ptimizer=ALL_ROWS (Cost=8 Card=20000 Bytes
          =260000)

   1    0   TABLE ACCESS (FULL) OF 'A' (TABLE) (Cost=8 Card=20000 Byte
          s=260000)


Statistics
----------------------------------------------------------
          5  recursive calls
      10193  db block gets
      10101  consistent gets
          0  physical reads
    2370584  redo size
        643  bytes sent via SQL*Net to client
        548  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      10000  rows processed

SQL> rollback;

Rollback complete.

SQL>insert /*+ append */ into a nologging select * from a;

10000 rows created.


Execution Plan
----------------------------------------------------------
Statistics
----------------------------------------------------------
         93  recursive calls
         45  db block gets
         94  consistent gets
          0  physical reads
     136144  redo size
        627  bytes sent via SQL*Net to client
        562  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      10000  rows processed

SQL>
SQL>
SQL> rollback;

---------------
--delete


SQL>delete from a;

10000 rows deleted.


Execution Plan
----------------------------------------------------------
   0      DELETE STATEMENT ptimizer=ALL_ROWS (Cost=1 Card=1 Bytes=3)
   1    0   DELETE OF 'A'
   2    1     INDEX (FULL SCAN) OF 'SYS_C005064' (INDEX (UNIQUE)) (Cos
          t=1 Card=1 Bytes=3)

 

 

Statistics
----------------------------------------------------------
          9  recursive calls
      10450  db block gets
         24  consistent gets
          0  physical reads
   2540712  redo size
        642  bytes sent via SQL*Net to client
        522  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      10000  rows processed

SQL> rollback;

Rollback complete.

 

SQL>delete from a nologging;

10000 rows deleted.


Execution Plan
----------------------------------------------------------
   0      DELETE STATEMENT ptimizer=ALL_ROWS (Cost=1 Card=1 Bytes=3)
   1    0   DELETE OF 'A'
   2    1     INDEX (FULL SCAN) OF 'SYS_C005064' (INDEX (UNIQUE)) (Cos
          t=1 Card=1 Bytes=3)

 

 

Statistics
----------------------------------------------------------
          1  recursive calls
      10437  db block gets
         19  consistent gets
          0  physical reads
   2534564  redo size
        642  bytes sent via SQL*Net to client
        532  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      10000  rows processed


结论:在非force logging情况下,delete、update的命令使用nologging效果不大,insert的时候配合append效果明显

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

上一篇: 17mysql复制
请登录后发表评论 登录
全部评论

注册时间:2009-08-26

  • 博文量
    215
  • 访问量
    624806