當(dāng)前位置：首頁(yè) > IT技術(shù) > 移動(dòng)平臺(tái) > 正文

Oracle insert /*+ APPEND */原理解析
2021-08-09 18:50:53

關(guān)于insert /*+ append */我們需要注意以下三點(diǎn)： a、非歸檔模式下，只需append就能大量減少redo的產(chǎn)生；歸檔模式下，只有append+nologging才能大量減少redo。 b、insert /*+ append */時(shí)會(huì)對(duì)表加鎖（排它鎖），會(huì)阻塞表上的除了select以外所有DML語(yǔ)句；傳統(tǒng)

關(guān)于insert /*+ append */我們需要注意以下三點(diǎn)：

a、非歸檔模式下，只需append就能大量減少redo的產(chǎn)生；歸檔模式下，只有append+nologging才能大量減少redo。
b、insert /*+ append */?時(shí)會(huì)對(duì)表加鎖（排它鎖），會(huì)阻塞表上的除了select以外所有DML語(yǔ)句；傳統(tǒng)的DML在TM enqueue上使用模式3（row exclusive），其允許其他DML在相同的模式上獲得TM enqueue。但是直接路徑加載在TM enqueue使用模式6（exclusive），這使其他DML在直接路徑加載期間將被阻塞。
c、insert /*+ append */?直接路徑加載，速度比常規(guī)加載方式快。因?yàn)槭菑腍WM的位置開(kāi)始插入，也許會(huì)造成空間浪費(fèi)。

1.環(huán)境介紹

數(shù)據(jù)庫(kù)版本：

1

2

3

4

5

6

7

8

SQL>?select?*?from?v$version;

BANNER

---------------------------------------------------------------------

Oracle?Database?11g Enterprise Edition Release 11.2.0.1.0 - 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

網(wǎng)上說(shuō)測(cè)試時(shí)不要使用auto trace來(lái)查看redo size，這個(gè)值是有偏差的.建議建立一個(gè)視圖：

1

2

3

4

5

6

SQL>?create?or?replace?view?redo_size?as

select?value

from?v$mystat, v$statname

where?v$mystat.statistic# = v$statname.statistic#

and?v$statname.name?=?'redo size';

-- 視圖已創(chuàng)建。

2.示例演示：

2.1 非歸檔模式

1

2

3

4

5

6

SQL> archive log list

數(shù)據(jù)庫(kù)日志模式???????????? 非存檔模式

自動(dòng)存檔???????????? 禁用

存檔終點(diǎn)??????????? USE_DB_RECOVERY_FILE_DEST

最早的聯(lián)機(jī)日志序列???? 95

當(dāng)前日志序列?????????? 97

2.1.1 nologging表

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

SQL>?create?table?test1 nologging?as?select?*?from?dba_objects?where?1=0;

表已創(chuàng)建。

??

SQL>?select?*?from?redo_size;

?????VALUE

----------

??25714940

??

SQL>?insert?into?test1?select?*?from?dba_objects;

已創(chuàng)建72753行。

??

SQL>?select?*?from?redo_size;

?????VALUE

----------

??34216916

??

SQL>?insert?/*+ APPEND */??into?test1?select?*?from?dba_objects;

已創(chuàng)建72753行。

??

SQL>?select?*?from?redo_size;

?????VALUE

----------

??34231736

??

SQL>?select?(34231736-34216916) redo_append , (34216916-25714940) redo_normalfrom?dual;

REDO_APPEND REDO_NORMAL

----------- -----------

??????14820???? 8501976

2.1.2 logging表：

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

SQL>?create?table?test2?as?select?*?from?dba_objects?where?1=0;

??

表已創(chuàng)建。

??

SQL>?select?*?from?redo_size;

?????VALUE

----------

??34273348

??

SQL>?insert?into?test2?select?*?from?dba_objects;

??

已創(chuàng)建72754行。

??

SQL>?select?*?from?redo_size;

?????VALUE

----------

??42775336

??

SQL>?insert?/*+ APPEND */??into?test2?select?*?from?dba_objects;

已創(chuàng)建72754行。

??

SQL>?select?*?from?redo_size;

?????VALUE

----------

??42790156

??

SQL>?select?(42790156-42775336) redo_append , (42775336-34273348) redo_normalfrom?dual;

REDO_APPEND REDO_NORMAL

----------- -----------

??????14820???? 8501988

2.2歸檔模式下：

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

SQL> shutdown immediate

數(shù)據(jù)庫(kù)已經(jīng)關(guān)閉。

已經(jīng)卸載數(shù)據(jù)庫(kù)。

ORACLE例程已經(jīng)關(guān)閉。

SQL> startup mount

ORACLE例程已經(jīng)啟動(dòng)。

??

Total System?Global?Area? 477073408 bytes

Fixed?Size??????????????????1337324 bytes

Variable?Size?????????????293603348 bytes

Database?Buffers????????? 176160768 bytes

Redo Buffers??????????????? 5971968 bytes

數(shù)據(jù)庫(kù)裝載完畢。

SQL>?alter?database?archivelog;

數(shù)據(jù)庫(kù)已更改。

??

SQL>?alter?database?open;

數(shù)據(jù)庫(kù)已更改。

??

SQL> archive log list

數(shù)據(jù)庫(kù)日志模式??????????? 存檔模式

自動(dòng)存檔???????????? 啟用

存檔終點(diǎn)??????????? USE_DB_RECOVERY_FILE_DEST

最早的聯(lián)機(jī)日志序列???? 95

下一個(gè)存檔日志序列?? 97

當(dāng)前日志序列?????????? 97

2.2.1 nologging表

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

SQL>?select?*?from?redo_size;

????VALUE

----------

?????17936

??

SQL>?insert?into?test1?select?*?from?dba_objects;

已創(chuàng)建72754行。

??

SQL>?select?*?from?redo_size;

?????VALUE

----------

???8490972

??

SQL>?insert?/*+ APPEND */??into?test1?select?*?from?dba_objects;

已創(chuàng)建72754行。

??

SQL>?select?*?from?redo_size;

?????VALUE

----------

???8506164

??

SQL>?select?(8506164-8490972) redo_append , (8490972-17936) redo_normal?fromdual;

REDO_APPEND REDO_NORMAL

----------- -----------

??????15192???? 8473036

2.2.2 logging表

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

SQL>?select?*?from?redo_size;

?????VALUE

----------

???8506780

??

SQL>?insert?into?test2?select?*?from?dba_objects;

已創(chuàng)建72754行。

??

SQL>?select?*?from?redo_size;

?????VALUE

----------

??16979516

??

SQL>?insert?/*+ APPEND */??into?test2?select?*?from?dba_objects;

已創(chuàng)建72754行。

??

SQL>?select?*?from?redo_size;

?????VALUE

----------

??25518172

??

SQL>?select?(25518172-16979516) redo_append , (16979516-8506780) redo_normalfrom?dual;

REDO_APPEND REDO_NORMAL

----------- -----------

????8538656???? 8472736

在歸檔模式下,對(duì)于常規(guī)表的insert append產(chǎn)生和insert同樣的redo
此時(shí)的insert append實(shí)際上并不會(huì)有性能提高.
但是此時(shí)的append是生效了的。

3.insert /*+ append */會(huì)阻塞除select以外的DML語(yǔ)句，direct-path insert操作是單獨(dú)一個(gè)事務(wù)。

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

SQL>?select?count(*)?from?test2;

??COUNT(*)

----------

????291016

??

SQL>?insert?into?test2?select?*?from?dba_objects;

已創(chuàng)建72754行。

??

SQL>?select?count(*)?from?test2;

??COUNT(*)

----------

????363770

??

SQL>?insert?/*+ APPEND */?into?test2?select?*?from?dba_objects;

已創(chuàng)建72754行

??

同一個(gè)session下：

??

SQL>?select?count(*)?from?test2;

select?count(*)?from?test2

*

第 1 行出現(xiàn)錯(cuò)誤:

ORA-12838: 無(wú)法在并行模式下修改之后讀/修改對(duì)象

??

SQL>?commit;

提交完成。

??

SQL>?select?count(*)?from?test2;

??COUNT(*)

----------

????436524

??

SQL>?insert?/*+ APPEND */?into?test2?select?*?from?dba_objects;

已創(chuàng)建72754行。

??

SQL> shutdown immediate

ORA-01097: 無(wú)法在事務(wù)處理過(guò)程中關(guān)閉 - 請(qǐng)首先提交或回退

??

SQL>?select??*?from?v$mystat?where?rownum<2;

??

???????SID STATISTIC#????? VALUE

??

---------- ---------- ----------

??

???????224????????? 0????????? 1

??

SQL>?select?KADDR,TYPE,LMODE?from?v$lock?where?sid=224;

??

KADDR??????????? TY????? LMODE

---------------- -- ----------

0000000071BAE180 TM????????? 6

0000000070CB11B8 TX????????? 6

另外開(kāi)啟一個(gè)會(huì)話，就會(huì)發(fā)現(xiàn)只能select，其他DML全部阻塞。

?

本文摘自 ：https://blog.51cto.com/u

開(kāi)通會(huì)員，享受整站包年服務(wù)立即開(kāi)通 >
推薦內(nèi)容