{"id":1609,"date":"2018-01-23T18:47:01","date_gmt":"2018-01-23T09:47:01","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=1609"},"modified":"2018-01-25T22:51:40","modified_gmt":"2018-01-25T13:51:40","slug":"oracle-exadata%ec%9d%98-hcc-%ec%95%95%ec%b6%95-%ec%9d%b4%eb%9e%80","status":"publish","type":"post","link":"https:\/\/haisins.synology.me\/wordpress\/?p=1609","title":{"rendered":"Oracle Exadata\uc758 HCC \uc555\ucd95 \uc774\ub780"},"content":{"rendered":"

\uc2dc\uac04\uc774 \uc9c0\ub098\uba74\uc11c \ub370\uc774\ud130\ub294 \uae09\uc18d\ud558\uac8c \uc99d\uac00\ud558\uace0 \uc788\ub2e4. \ub370\uc774\ud130\uac00 \uc99d\uac00\ud568\uc5d0 \ub530\ub77c DBMS \uc5d0\uc11c \uad00\ub9ac \ub418\uc5b4\uc9c0\ub294 \uc815\ubcf4\ub3c4 \uae09\uc18d\ud558\uac8c \uc99d\uac00 \ud558\uace0 \uc788\ub2e4. \uc774\ub85c \uc778\ud574 \uc800\uc7a5\uacf5\uac04\uc758 \ubd80\uc871\uc73c\ub85c \ud558\ub4dc\uc6e8\uc5b4 \ube44\uc6a9\uc758 \uc99d \uac00\uc640 \ub370\uc774\ud130 \ucc98\ub9ac \uc131\ub2a5\uc5d0 \ub9ce\uc740 \ubb38\uc81c \uc810\ub4e4\uc774 \ub098\ud0c0\ub098\uace0 \uc788\ub2e4.<\/p>\n

\uc774\ub7ec\ud55c \ubb38\uc81c\uc810\ub4e4\uc744 \ud574\uacb0\ud558\uace0 \uc790 ORACLE \uc5d0\uc11c\ub294 EXADATA \ub77c\ub294 \uc2dc\uc2a4\ud15c\uc744 \ud1b5\ud574 \uc2a4\ud1a0\ub9ac\uc9c0 \uacf5\uac04 \ubd80\uc871\ud604\uc0c1 \uacfc \ub370\uc774\ud130 \ucc98\ub9ac \uc131\ub2a5\uc744 \ud5a5\uc0c1\uc2dc\ud0a4\uace0\uc790 \ud558\uc600\ub2e4. EXADATA \uc2dc\uc2a4\ud15c\uc774\ub780 \ub300\uc6a9\ub7c9 \ub370\uc774\ud130\ubca0\uc774\uc2a4\uc5d0\uc11c \ub514\uc2a4\ud06c \uc2a4\ud1a0\ub9ac\uc9c0 \uc2dc\uc2a4\ud15c\uc5d0\uc11c \ub370\uc774\ud130\ubca0\uc774\uc2a4 \uc11c\ubc84\ub85c \ub9ce\uc740 \ub370\uc774\ud130\ub97c \uc774\ub3d9 \uc2dc\ud0ac \ub54c \ubc1c\uc0dd\ud558 \ub294 \ub9ce\uc740 \ube44\ud6a8\uc728\uc744 \ud558\ub4dc\uc6e8\uc5b4\uc640 \uc18c\ud504\ud2b8\uc6e8\uc5b4\uc758 \uc870\ud569\uc744 \ud1b5\ud574 \ud574\uacb0 \ud558\uace0\uc790 \ud55c \uac83\uc774 EXADATA \uc2dc\uc2a4\ud15c \uc774\ub77c \ud558\ub294\ub370 HCC (HYBRID COLUMNAR COMPRESSION) \ub77c\ub294 \uc555\ucd95 \uae30\uc220\uc744 \ud1b5\ud574 \uc774\uc640 \uac19\uc740 \ubb38\uc81c\uc810\ub4e4\uc744 \ud574\uacb0 \ud560 \uc218 \uc788\ub2e4.<\/p>\n

\ubcf8 \uc7a5\uc5d0\uc11c\ub294 EXADATA \uc2dc\uc2a4\ud15c\uc758 HYBRID COLUMNAR COMPRESSION \uc555\ucd95\uc744 \uc911\uc2ec\uc73c\ub85c \uc774\uc57c \uae30 \ud560 \uac83\uc774\ub2e4. \ub370\uc774\ud130 \uc555\ucd95\uc758 \uc6d0\ub9ac\ub97c \uc54c\uc544\ubcf4\uace0 \ub514\uc2a4\ud06c \uacf5\uac04 \uc808\uc57d\uacfc \ub370\uc774\ud130 \uc870\ud68c\uc758 \uc131\ub2a5\uc5d0 \uc5b4\ub5a4 \uc601\ud5a5\uc744 \ubbf8\uce58\ub294\uc9c0 \ube44\uad50\ud574 \ubcf4\uace0\uc790 \ud55c\ub2e4.
\n<\/span><\/p>\n

HCC \ub780 \ubb34\uc5c7\uc774\uba70 \uc5b4\ub5bb\uac8c \ub3d9\uc791\ud558\ub294\uc9c0 \uc54c\uc544 \ubcf4\uc790.
\n<\/strong><\/span><\/p>\n

\ub370\uc774\ud130\ub97c \uc555\ucd95\uc744 \ud558\uac8c \ub418\uba74 \uc2a4\ud1a0\ub9ac\uc9c0 \uacf5\uac04\uc744 \uc808\uc57d \ud560 \uc218 \uc788\ub2e4. \uc555\ucd95\uc5d0\ub3c4 \uc5ec\ub7ec \uac00\uc9c0 \ubc29\ubc95\uc73c\ub85c \ub370\uc774\ud130\ub4e4\uc744 \uc555\ucd95 \ud560 \uc218 \uc788\uc73c\uba70 EXADATA \uc2dc\uc2a4\ud15c\uc5d0\uc11c\ub294 H CC \ub77c\ub294 \uc555\ucd95 \ubc29\uc2dd\uc744 \ud1b5\ud574 \ubcf4\ub2e4 \uc9c4\ubcf4\ub41c \ubc29\uc2dd\uc73c\ub85c \uc555\ucd95\uc744 \ud560 \uc218 \uc788\ub2e4. HCC \uc774\uc804\uc5d0 \uc555\ucd95 \ubc29\uc2dd\uc740 BLOCK \uc548\uc5d0 \uc911\ubcf5\ub41c \uac12\ub4e4\uc744 \ud558\ub098\uc758 \uac12\uc73c\ub85c \uac00\uc9c0\uace0 \uc788\uace0 \uadf8 \uac12\uc744 \ucc38\uc870\ud558\ub294 \uc8fc\uc18c \uac12\uc744 \ud1b5\ud574\uc11c \uc911\ubcf5 \ub370\uc774\ud130\ub97c \uc811\uadfc\ud558\ub294 \ubc29\uc2dd\uc778 ROW \ub370\uc774 \ud130\ub97c \uae30\ubc18\uc73c\ub85c \ud558\ub294 \uc555\ucd95 \ubc29\uc2dd \uc774\uc600\ub2e4.<\/p>\n

ROW \uae30\ubc18 \uc555\ucd95\uc5d0\uc11c \uc9c4\ubcf4\ub41c \ud615\ud0dc\uc758 \uc555\ucd95\uc774 HCC \uc555\ucd95\uc774\ub77c\uace0 \ud560 \uc218 \uc788\uc73c\uba70 HCC \uc555\ucd95 \ubc29\uc2dd\uc740 EXADATA \uc5d0\uc11c\ub9cc \uc0ac\uc6a9 \ud560 \uc218 \uc788\ub2e4. HCC(HYBRID COLUMNAR COMPRES SION) \uc774\ub780 \uce7c\ub7fc\uae30\ubc18 \uc555\ucd95 \ubc29\uc2dd\uc73c\ub85c \uac19\uc740 \uc720\ud615\uc758 \ub370\uc774\ud130\uc640 \ube44\uc2b7\ud55c \uce7c\ub7fc \uc18d\uc131\uc744 \uc778\uc811\ud558\uc5ec \uc800\uc7a5\ud558\uba74 \ubcf4\ub2e4 \ud6a8\uacfc\uc801\uc778 \uc555\ucd95\uc744 \ud558\uc5ec \uc2a4\ud1a0\ub9ac\uc9c0 \uacf5\uac04\uc744 \ud6a8\uc728\uc801\uc73c\ub85c \uc904\uc77c \uc218 \uc788\ub2e4. \uce7c\ub7fc\uacfc ROW \uc758 \uc870\ud569\uc73c\ub85c \uc800\uc7a5\uacf5\uac04\uc758 \ud6a8\uc728\uc131\uacfc \uc88b\uc740 \uc131\ub2a5\uc744 \uae30\ub300\ud560 \uc218 \uc788\ub294 HYBRID \uc555\ucd95 \ubc29\uc2dd\uc774\ub2e4.
\n<\/span><\/p>\n


\n\"\"
\n<\/span>
\n<\/span><\/p>\n

HCC \uc555\ucd95 \ubc29\uc2dd\uc740 \ub354 \uc774\uc0c1 ROW \ub2e8\uc704\ub85c \ub370\uc774\ud130\ub97c \uc800\uc7a5\ud558\uc9c0 \uc54a\uace0 CU(COMPRESSION UNIT) \ub2e8 \uc704\ub85c \uc800\uc7a5 \ub41c\ub2e4. \uadf8\ub9bc 1 \uacfc \uac19\uc774 \ud558\ub098\uc758 CU \uc744 \ubcf4\ud1b5 32K \ub098 64K \uad6c\uc131 \ub418\uc5b4 \uc788\ub2e4. CU \ub294 \uc5ec\ub7ec \uac1c \uc758 BLOCK \uc744 \uac00\uc9c0\uace0 \uc788\uc73c\uba70 CU \uc548\uc5d0 \ub370\uc774\ud130\ub4e4\uc740 \uce7c\ub7fc\uc73c\ub85c \ub2e4\uc2dc \uc7ac\uad6c\uc131\ub41c\ub2e4.<\/p>\n

\uc774\ub807\uac8c \uad6c\uc131\ub41c CU \uc758 \uc7a5\uc810\uc740 CU \uc744 \ud55c\ubc88\ub9cc \uc77d\uc73c\ubbc0\ub85c ROW \uc804\uccb4\ub97c \uc77d\uc744 \uc218 \uc788\ub294 \uc7a5\uc810\uc774 \uc788\uc9c0\ub9cc \uce7c\ub7fc \uc804\uccb4\ub97c \uc77d\uc73c\ub824\uba74 \ubaa8\ub4e0 UN \uc744 \uc77d\uc5b4\uc57c\ub9cc \ud558\uae30 \ub54c\ubb38\uc5d0 \uc644\ubcbd\ud55c \uce7c\ub7fc \uae30\ubc18 \uc555\ucd95 \ubc29\uc2dd\uc774\ub77c\uace0\ub294 \ud560 \uc218 \uc5c6\ub2e4. \uc644\ubcbd\ud55c \uce7c\ub7fc \ubc29\uc2dd\uc73c\ub85c \uad6c\uc131 \ub418\uc5c8\ub2e4\uba74 \ubaa8\ub4e0 CU \uc744 \uc77d\uc9c0 \uc54a\uace0 \ubaa8\ub4e0 \uce7c\ub7fc\ub4e4\uc744 \uc77d\uc5c8\uc744 \uac83\uc774\ub2e4.<\/p>\n

\ud558 \uc9c0\ub9cc HCC \uc555\ucd95\uc740 \uce7c\ub7fc \ud615\ud0dc\uc758 \uc555\ucd95\uc758 \uc7a5\uc810\uc778 \ub514\uc2a4\ud06c \uc808\uc57d\uacfc ROW \ud615\ud0dc\uc758 \uc555\ucd95\uc758 \uc7a5\uc810\uc778 \uc870 \ud68c \uc131\ub2a5\uc744 \ubaa8\ub450 \ub9cc\uc871 \uc2dc\ud0ac \uc218 \uc788\ub294 HYBRID \uc555\ucd95 \uc774\ub77c\uace0 \ud558\uaca0\ub2e4.
\n<\/span><\/p>\n

HCC \uc555\ucd95\uc5d0\ub294 \uc5b4\ub5a4 \uc720\ud615\ub4e4\uc774 \uc788\uc744\uae4c ?
\n<\/strong><\/span><\/p>\n

HCC \ubc29\uc2dd\uc740 \uc5ec\ub7ec \uac00\uc9c0 \uc720\ud615\uc774 \uc874\uc7ac\ud558\uba70 \uc544\ub798 \ud45c\ub97c \ud1b5\ud574\uc11c \uac01 \uc720\ud615\ub4e4\uc758 \ud2b9\uc9d5\uc744 \ubcfc \uc218 \uc788\ub2e4. \uc544\ub798 \ud45c\ub97c \ucc38\uace0\ud558\uc5ec \ud14c\uc2a4\ud2b8\ub97c \ud1b5\ud574 \uc555\ucd95 \uc720\ud615\ub4e4\uc758 \ud2b9\uc9d5\ub4e4\uc744 \uad6c\uccb4\uc801\uc73c\ub85c \uc54c\uc544 \ubcf4\ub3c4\ub85d \ud558\uc790.
\n<\/span><\/p>\n


\n\"\"
\n<\/span>
\n<\/span><\/p>\n

\ud45c 1 – 1 \uc5d0\uc11c \uc0b4\ud3b4\ubcf8 \uc555\ucd95 \uc720\ud615\ub4e4\uc744 \ud1b5\ud574 \uac01\uac01\uc758 \ud2b9\uc9d5\ub4e4\uc744 \ud14c\uc2a4\ud2b8\ub97c \ud1b5\ud574 \uc0b4\ud3b4 \ubcf4\ub3c4\ub85d \ud558\uc790.
\n<\/span><\/p>\n

\ud14c\uc2a4\ud2b8 \ud14c\uc774\ube14 \uc0dd\uc131 \uc2a4\ud06c\ub9bd\ud2b8
\n<\/strong><\/span><\/p>\n

CREATE TABLE HCC_TEST AS SELECT * FROM DBA_OBJECTS;
\n<\/span><\/p>\n

INSERT INTO HCC_TEST AS SELECT * FROM HCC_TEST ; — 10\ubc88 \ubc18\ubcf5
\n<\/span><\/p>\n

— \ud14c\uc2a4\ud2b8 \ud14c\uc774\ube14\uc758 SEGMENT SIZE
\n<\/span><\/p>\n

SELECT SUM(BYTES)\/1024\/1024 BYTES FROM DBA_SEGMENTS WHERE SEGMENT_NAME=’HCC_TEST’;
\n<\/span><\/p>\n

BYTES
\n<\/span><\/p>\n

———
\n<\/span><\/p>\n

1600
\n<\/span><\/p>\n

— HCC \ub808\ubca81
\n<\/strong><\/span><\/p>\n

CREATE TABLE HCC_TEST_HCC1 COMPRESS FOR QUERY LOW AS SELECT * FROM HCC_TEST ;
\n<\/strong><\/span><\/p>\n

SQL Execution Time > 00:00:20.031
\n<\/span><\/p>\n

SQL> SELECT SUM(BYTES)\/1024\/1024 MBYTES FROM DBA_SEGMENTS WHERE SEGMENT_NAME=’HCC_TEST_HCC1′;
\n<\/span><\/p>\n

MBYTES
\n<\/span><\/p>\n

———
\n<\/span><\/p>\n

200
\n<\/span><\/p>\n

— HCC \ub808\ubca82
\n<\/strong><\/span><\/p>\n

CREATE TABLE HCC_TEST_HCC2 COMPRESS FOR QUERY HIGH AS SELECT * FROM HCC_TEST ;
\n<\/strong><\/span><\/p>\n

SQL Execution Time > 00:00:40.794
\n<\/span><\/p>\n

SELECT SUM(BYTES)\/1024\/1024 MBYTES FROM DBA_SEGMENTS WHERE SEGMENT_NAME=’HCC_TEST_HCC2′;
\n<\/span><\/p>\n

MBYTES
\n<\/span><\/p>\n

———
\n<\/span><\/p>\n

104
\n<\/span><\/p>\n

— HCC \ub808\ubca83
\n<\/strong><\/span><\/p>\n

CREATE TABLE HCC_TEST_HCC3 COMPRESS FOR ARCHIVE LOW AS SELECT * FROM HCC_TEST ;
\n<\/strong><\/span><\/p>\n

SQL Execution Time > 00:00:40.108
\n<\/span><\/p>\n

SELECT SUM(BYTES)\/1024\/1024 MBYTES FROM DBA_SEGMENTS WHERE SEGMENT_NAME=’HCC_TEST_HCC3′;
\n<\/span><\/p>\n

MBYTES
\n<\/span><\/p>\n

———
\n<\/span><\/p>\n

104
\n<\/span><\/p>\n

\n

— HCC \ub808\ubca84
\n<\/strong><\/span><\/p>\n

CREATE TABLE HCC_TEST_HCC4 COMPRESS FOR ARCHIVE HIGH AS SELECT * FROM HCC_TEST ;
\n<\/strong><\/span><\/p>\n

SQL Execution Time > 00:04:01.146
\n<\/span><\/p>\n

SELECT SUM(BYTES)\/1024\/1024 MBYTES FROM DBA_SEGMENTS WHERE SEGMENT_NAME=’HCC_TEST_HCC4′;
\n<\/span><\/p>\n

MBYTES
\n<\/span><\/p>\n

———
\n<\/span><\/p>\n

72
\n<\/span><\/p>\n

\uc555\ucd95 \uc720\ud615\uc5d0 \ub530\ub978 \ud14c\uc2a4\ud2b8 \uacb0\uacfc
\n<\/strong><\/span><\/p>\n

\"\"
\n<\/span>
\n<\/span><\/p>\n

\ud45c 1 \uc555\ucd95 \uc720\ud615 \ud14c\uc2a4\ud2b8 \uacb0\uacfc\ub97c \uc0b4\ud3b4\ubcf4\uba74 \uc555\ucd95 \ub2e8\uacc4\uac00 \ub192\uc744 \uc218\ub85d \uc555\ucd95\ub960\uc774 \ub192\uc544 \uc9c4\ub2e4. \uc555\ucd95 \ub808 \ubca8\uc774 \ub192\uc744\uc218\ub85d \uc2a4\ud1a0\ub9ac\uc9c0 \uacf5\uac04\uc744 \uc808\uc57d \ud560 \uc218\uac00 \uc788\ub2e4. QUERY HIGH \uc744 \ubcf4\uba74 QUERY LOW \ube44\ud574 \uc555\ucd95\ub960\uc774 7 \ubc30 \uc815\ub3c4 \uc88b\uc544\uc84c\uc9c0\ub9cc \uc801\uc7ac \uc2dc\uac04\ub3c4 2 \ubc30 \uc815\ub3c4 \uc18c \uc694 \ub418\uc5c8\ub2e4. ARCHIVE LOW \ubcf4\uba74 QUERY HIGH \uc640 \uac70\uc758 \ucc28\uc774\ub97c \ubcf4\uc774\uc9c0 \uc54a\uace0 \uc788\ub2e4. ARCHIVE HIGH \uc744 \ubcf4\uba74 \uc555\ucd95\ub960\uc774 ARCHIVE LOW \ube44\ud574 7 \ubc30 \uc815\ub3c4 \uc88b\uc544 \uc84c\uc9c0\ub9cc \uc2dc\uac04\uc740 4 \ubc30 \uc815\ub3c4 \ub354 \ub290\ub824\uc84c\ub2e4.\u00a0<\/p>\n

\uacb0\uacfc\uc801\uc73c\ub85c \ub808\ubca8\uc758 \ub2e8\uacc4\uac00 \ub192\uc744 \uc218\ub85d \uc555\ucd95\ub960\uc740 \uc88b\uc544 \uc84c\uc9c0\ub9cc \uc801\uc7ac\ub294 \ub354 \ub9ce\uc740 \uc2dc\uac04\uc744 \uc694\uad6c \ud558\uace0 \uc788 \ub2e4.<\/strong> \ub610\ud55c \uc555\ucd95 \ud558\ub294 \ub3d9\uc548 \ub9ce\uc740 \uc2dc\uac04\uc774 \uc18c\uc694 \ub41c\ub2e4\uba74 \uc2dc\uc2a4\ud15c \uc790\uc6d0\ub3c4 \uc624\ub79c \uc2dc\uac04 \ub3d9\uc548 \uc0ac\uc6a9 \ud574\uc57c \ud560 \uac83\uc774\ub2e4.<\/strong><\/span>
\n<\/strong><\/span><\/span><\/p>\n

HCC \uc555\ucd95\uacfc QUERY \uc131\ub2a5\uacfc\uc758 \uad00\uacc4
\n<\/strong><\/span><\/p>\n

H CC \uc555\ucd95\uacfc QUERY \uc758 \uc131\ub2a5\uc744 \uc54c\uc544\ubcf4\ub824\uace0 \ud55c\ub2e4. \uc5ec\uae30\uc11c 2 \uac00\uc9c0 \ud14c\uc2a4\ud2b8\ub97c \ud574 \ubcf4\uaca0\ub2e4. I\/O \uc9d1\uc57d \uc801\uc778 QUERY \uc131\ub2a5 \ud14c\uc2a4\ud2b8\uc640 CPU \uc791\uc5c5 \uc9d1\uc57d\uc801\uc778 QUERY \uc131\ub2a5 \ud14c\uc2a4\ud2b8\ub97c \ud574 \ubcf4\uaca0\ub2e4. \uc774\ub807\uac8c \ud14c\uc2a4\ud2b8\ub97c \ud558\ub294 \uc774\uc720\ub294 \ucffc\ub9ac\uc5d0 \uc0c1\ud669\uc5d0 \ub530\ub77c\uc11c \uc131\ub2a5\uc5d0 \ucc28\uc774\ub97c \ubcf4\uc774\uae30 \ub54c\ubb38\uc774\ub2e4.
\n<\/span><\/p>\n

Java.lang.OutOfMemoryError : Java heap spaceI\/O \uc9d1\uc57d\uc801\uc778 QUERY
\n<\/strong><\/span><\/p>\n

SQL> select sum(OBJECT_ID) from HCC_TEST;
\n<\/span><\/p>\n

SUM(OBJECT_ID)
\n<\/span><\/p>\n

————–
\n<\/span><\/p>\n

7.4e+011
\n<\/span><\/p>\n

SQL Execution Time > 00:02:51.123
\n<\/span><\/p>\n

SQL> select sum(OBJECT_ID) from HCC_TEST_HCC1;
\n<\/span><\/p>\n

SUM(OBJECT_ID)
\n<\/span><\/p>\n

————–
\n<\/span><\/p>\n

7.4e+011
\n<\/span><\/p>\n

SQL Execution Time > 00:01:31.967
\n<\/span><\/p>\n

SQL> select sum(OBJECT_ID) from HCC_TEST_HCC2;
\n<\/span><\/p>\n

SUM(OBJECT_ID)
\n<\/span><\/p>\n

————–
\n<\/span><\/p>\n

7.4e+011
\n<\/span><\/p>\n

SQL Execution Time > 00:01:13.045
\n<\/span><\/p>\n

SQL> select sum(OBJECT_ID) from HCC_TEST_HCC3;
\n<\/span><\/p>\n

SUM(OBJECT_ID)
\n<\/span><\/p>\n

————–
\n<\/span><\/p>\n

7.4e+011
\n<\/span><\/p>\n

SQL Execution Time > 00:01:14.014
\n<\/span><\/p>\n

SQL> select sum(OBJECT_ID) from HCC_TEST_HCC4;
\n<\/span><\/p>\n

SUM(OBJECT_ID)
\n<\/span><\/p>\n

————–
\n<\/span><\/p>\n

7.4e+011
\n<\/span><\/p>\n

SQL Execution Time > 00:01:58.043
\n<\/span><\/p>\n

CPU \uc9d1\uc57d\uc801\uc778 QUERY
\n<\/strong><\/span><\/p>\n

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(‘UNIONE’, ‘HCC_TEST’,METHOD_OPT =>’ for all columns size 1′);
\n<\/span><\/p>\n

PL\/SQL executed.
\n<\/span><\/p>\n

SQL Execution Time > 00:00:13.054
\n<\/span><\/p>\n

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(‘UNIONE’, ‘HCC_TEST_HCC1′, METHOD_OPT =>’ for all columns size 1′);
\n<\/span><\/p>\n

PL\/SQL executed.
\n<\/span><\/p>\n

SQL Execution Time > 00:00:16.911
\n<\/span><\/p>\n

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(‘UNIONE’, ‘HCC_TEST_HCC2′, METHOD_OPT =>’ for all columns size 1′);
\n<\/span><\/p>\n

PL\/SQL executed.
\n<\/span><\/p>\n

SQL Execution Time > 00:00:19.859
\n<\/span><\/p>\n

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(‘UNIONE’, ‘HCC_TEST_HCC3′, METHOD_OPT =>’ for all columns size 1′);
\n<\/span><\/p>\n

PL\/SQL executed.
\n<\/span><\/p>\n

SQL Execution Time > 00:00:19.891
\n<\/span><\/p>\n

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS (‘UNIONE’, ‘HCC_TEST_HCC4′, METHOD_OPT =>’ for all columns size 1′);
\n<\/span><\/p>\n

PL\/SQL executed.
\n<\/span><\/p>\n

SQL Execution Time > 00:00:33.322
\n<\/span><\/p>\n

\n

HCC \uc555\ucd95\uacfc QUERY \uc131\ub2a5 \ud14c\uc2a4\ud2b8 \uacb0\uacfc
\n<\/strong><\/span><\/p>\n

\"\"
\n<\/span>
\n<\/span><\/p>\n

\ud45c 2 – 2 \uc744 \ubcf4\uba74 I\/O \uc9d1\uc57d\uc801\uc778 QUERY \uc640 CPU \uc9d1\uc57d\uc801\uc778 QUERY \uc5d0 \ub300\ud574 \uc131\ub2a5\uc5d0 \ucc28\uc774\uac00 \uc874\uc7ac\ud55c \ub2e4. I\/O \uc9d1\uc57d\uc801\uc778 \uc791\uc5c5\uc77c \uacbd\uc6b0\uc5d0\ub294 HCC \uc555\ucd95 \uc804\ud654 \ud6c4\ub97c \ube44\uad50\ud574 \ubcf4\uba74 \uac01 HCC \uc555\ucd95 \ub808\ubca8\ub9c8\ub2e4 \uc870 \uae08\uc758 \ucc28\uc774\ub294 \ubcf4\uc774\uc9c0\ub9cc \uc555\ucd95 \ud558\uae30 \uc804\ubcf4\ub2e4 \ubaa8\ub450 \uc88b\uc740 \uc131\ub2a5\uc744 \ubcf4\uc600\uc9c0\ub9cc CPU \uc9d1\uc57d\uc801 \uc791\uc5c5\uc740 \uc555\ucd95 \ud558 \uae30 \uc804 \ubcf4\ub2e4 \ubaa8\ub450 \ub354 \ub098\uc05c \uc131\ub2a5\uc744 \ubcf4\uc600\ub2e4.
\n<\/span><\/p>\n


\n\uc774\ub807\uac8c QUERY \uc758 \ud615\ud0dc (I\/O \uc9d1\uc57d\uc801\uc778 QUERY \uc640 CPU \uc9d1\uc57d\uc801\uc778 QUERY) \uc5d0 \ub530\ub77c\uc11c \uc2e4\ud589 \uacb0\uacfc \uac00 \ub2ec\ub77c\uc9c4 \uc774\uc720\ub294 I\/O \uc9d1\uc57d\uc801\uc778 \uc791\uc5c5\uc740 QUERY \uc870\ud68c \uc2dc \uc555\ucd95\ud574\uc81c\uc5d0 \ub530\ub978 CPU \uc0ac\uc6a9\ubcf4\ub2e4 HCC \uc555 \ucd95\uc73c\ub85c \uc77d\uc5b4\uc57c \ud560 BLOCK \uc218\uc758 \uac10\uc18c \ud6a8\uc728\uc774 \ub354 \uc88b\uc558\uae30 \ub54c\ubb38\uc774\uace0 CPU \uc9d1\uc57d\uc801 \uc791\uc5c5\uc774 \uc555\ucd95 \uc804 \ub370 \uc774\ud130 \ubcf4\ub2e4 \uc88b\uc9c0 \ubabb\ud55c \uc131\ub2a5\uc744 \ubcf4\uc778 \uc774\uc720\ub294 \uc555\ucd95\ud574\uc81c\uc5d0 \uc0ac\uc6a9\ub41c CPU \ub9ac\uc18c\uc2a4 \uc0ac\uc6a9\uacfc \ud1b5\uacc4\uc815\ubcf4 \uc0dd\uc131 \uc5d0\uc11c \uc0ac\uc6a9\ud55c CPU \uc0ac\uc6a9 \ub9ac\uc18c\uc2a4\uac00 HCC \uc555\ucd95\uc73c\ub85c \uc77d\uc5b4 \uc57c \ud560 BLOCK \uc218\uc758 \uac10\uc18c \ud6a8\uc728 \ubcf4\ub2e4 \uc88b\uc9c0 \ubabb \ud588\uae30 \ub54c\ubb38\uc774\ub2e4. \ub530\ub77c\uc11c \uc555\ucd95\ub41c \ub370\uc774\ud130\ub294 \uac01 QUERY \uc0c1\ud669\uc5d0 \ub530\ub77c\uc11c \uc131\ub2a5\uc774 \ub2ec\ub77c\uc9c8 \uac83\uc774\ub2e4.
\n<\/span><\/p>\n

DML(INSERT, UPDATE) \uc0ac\uc6a9\uc2dc HCC \uc555\ucd95 \ubc29\ubc95\uacfc \uc720\uc758\uc810
\n<\/strong><\/span><\/p>\n

INSERT \uc2dc HCC \uc555\ucd95\ud558\uae30
\n<\/strong><\/span><\/p>\n

INSERT \ud1b5\ud574 \ub370\uc774\ud130 \uc555\ucd95\uc744 \ud560 \uacbd\uc6b0 DIRECT PATH LOAD \uc77c \ub54c\ub9cc HCC \ud615\ud0dc\ub85c \uc555\ucd95\uc774 \uc774\ub8e8\uc5b4 \uc9c4\ub2e4.<\/p>\n

\uc544\ub798 \ud14c\uc2a4\ud2b8\ub97c \ud1b5\ud574 \ud655\uc778\ud574 \ubcf4\ub3c4\ub85d \ud558\uc790.
\n<\/span><\/p>\n

INSERT HCC \uc555\ucd95 \ud558\uae30
\n<\/strong><\/span><\/p>\n

SQL> TRUNCATE TABLE HCC_TEST;
\n<\/span><\/p>\n

ALTER TABLE HCC_TEST NOCOMPRESS;
\n<\/strong><\/span><\/p>\n

INSERT INTO HCC_TEST SELECT * FROM HCC_TEST_HCC1;
\n<\/span><\/p>\n

COMMIT;
\n<\/span><\/p>\n

SQL> SELECT ROUND(SUM(BYTES)\/1024\/1024) FROM DBA_SEGMENTS WHERE SEGMENT_NAME=’HCC_TEST’;
\n<\/span><\/p>\n

ROUND(SUM(BYTES)\/1024\/1024)
\n<\/span><\/p>\n

—————————
\n<\/span><\/p>\n

1600
\n<\/span><\/p>\n

SQL> TRUNCATE TABLE HCC_TEST;
\n<\/span><\/p>\n

Statement Processed.
\n<\/span><\/p>\n

SQL Execution Time > 00:00:01.982
\n<\/span><\/p>\n

ALTER TABLE HCC_TEST COMPRESS FOR ARCHIVE LOW ;
\n<\/strong><\/span><\/p>\n

INSERT INTO HCC_TEST SELECT * FROM HCC_TEST_HCC1;
\n<\/span><\/p>\n

COMMIT;
\n<\/span><\/p>\n

SQL> SELECT ROUND(SUM(BYTES)\/1024\/1024) FROM DBA_SEGMENTS WHERE SEGMENT_NAME=’HCC_TEST’;
\n<\/span><\/p>\n

ROUND(SUM(BYTES)\/1024\/1024)
\n<\/span><\/p>\n

—————————
\n<\/span><\/p>\n

1536
\n<\/span><\/p>\n

SQL> TRUNCATE TABLE HCC_TEST;
\n<\/span><\/p>\n

Statement Processed.
\n<\/span><\/p>\n

SQL Execution Time > 00:00:01.982
\n<\/span><\/p>\n

ALTER TABLE HCC_TEST COMPRESS FOR ARCHIVE LOW ;
\n<\/strong><\/span><\/p>\n

INSERT \/*+APPEND*\/ INTO HCC_TEST SELECT * FROM HCC_TEST_HCC1;
\n<\/span><\/p>\n

COMMIT;
\n<\/span><\/p>\n

SQL> SELECT ROUND(SUM(BYTES)\/1024\/1024) FROM DBA_SEGMENTS WHERE SEGMENT_NAME=’HCC_TEST’;
\n<\/span><\/p>\n

ROUND(SUM(BYTES)\/1024\/1024)
\n<\/span><\/p>\n

—————————
\n<\/span><\/p>\n

104
\n<\/span><\/p>\n

\n

CREATE TABLE HCC_TEST_HCC3 COMPRESS FOR ARCHIVE LOW AS SELECT * FROM HCC_TEST ;
\n<\/strong><\/span><\/p>\n

SQL Execution Time > 00:00:40.108
\n<\/span><\/p>\n

SELECT SUM(BYTES)\/1024\/1024 MBYTES FROM DBA_SEGMENTS WHERE SEGMENT_NAME=’HCC_TEST_HCC3′;
\n<\/span><\/p>\n

MBYTES
\n<\/span><\/p>\n

———
\n<\/span><\/p>\n

104
\n<\/span><\/p>\n

\n

\ud14c\uc774\ube14\uc744 \uc0dd\uc131 \ud6c4 ALTER \uba85\ub839\uc5b4\ub97c \ud1b5\ud574 \ud14c\uc774\ube14 \ud615\ud0dc\ub97c ARCHIVE LOW \ud615\ud0dc\uc758 \uc555\ucd95 \ud615\ud0dc\ub85c \ud14c \uc774\ube14\ub85c \ubcc0\uacbd \ud558\uc600\ub2e4. \ud14c\uc774 \ube14\uc740 \uc555\ucd95 \ud615\ud0dc\ub85c \ubcc0\uacbd \ub418\uc5c8\uc9c0\ub9cc DIRECT PATH LOAD \ubc1c\uc0dd\ud558\uc9c0 \uc54a\uc740 \uc0c1\ud0dc\uc5d0\uc11c \ub370\uc774\ud130 \uc801\uc7ac\uc640 \uc6d0\ubcf8 \ud14c\uc774\ube14\uc758 SEGMENT \ucc28\uc774\ub294 \uac70\uc758 \uc5c6\uc5c8\ub2e4. \ud558\uc9c0\ub9cc DIRECT PATH LOAD \ubc1c\uc0dd\ud55c \ub370\uc774\ud130 \uc801\uc7ac\ub294 HCC \ud615\ud0dc\uc758 ARCHIVE LOW \ud615\ud0dc\uc758 \uc555\ucd95\uc774 \uc774\ub8e8\uc5b4 \uc84c\ub2e4.
\n<\/span><\/p>\n

UPDATE \uc2dc HCC \uc555\ucd95\ud558\uae30
\n<\/strong><\/span><\/p>\n

HCC \uac00 \uc774\ub8e8\uc5b4\uc9c4 \ub370\uc774\ud130\uc5d0 \ub300\ud574\uc11c UPDATE \uac00 \ubc1c\uc0dd\ud558\uac8c \ub418\uba74 \uc555\ucd95\uc740 \ud574\uc81c \ub41c\ub2e4.
\n\uc544\ub798 \ud14c\uc2a4\ud2b8\ub97c \ud1b5\ud574\uc11c \uc54c\uc544 \ubcf4\ub3c4\ub85d \ud558\uc790.
\n<\/span><\/p>\n

DROP TABLE HCC_TEST_UPDATE PURGE;
\n<\/span><\/p>\n

CREATE TABLE HCC_TEST_UPDATE NOLOGGING AS SELECT * FROM HCC_TEST_HCC1 WHERE ROWNUM < 200000;
\n<\/span><\/p>\n

SELECT SUM(BYTES)\/1024\/1024 MBYTES FROM DBA_SEGMENTS WHERE SEGMENT_NAME=’HCC_TEST_UPDATE’;
\n<\/span><\/p>\n

MBYTES
\n<\/span><\/p>\n

———
\n<\/span><\/p>\n

24
\n<\/span><\/p>\n

EXEC DBMS_STATS.GATHER_TABLE_STATS (‘UNIONE’, ‘HCC_TEST_UPDATE’, METHOD_OPT =>’ for all columns size 1′)
\n<\/span><\/p>\n

SELECT TABLE_NAME, BLOCKS,CHAIN_CNT FROM DBA_TABLES WHERE TABLE_NAME = ‘HCC_TEST_UPDATE’;
\n<\/span><\/p>\n

TABLE_NAME BLOCKS CHAIN_CNT
\n<\/span><\/p>\n

—————————— ——— ———
\n<\/span><\/p>\n

HCC_TEST_UPDATE 2963 0
\n<\/span><\/p>\n

\n

ALTER TABLE HCC_TEST_UPDATE MOVE COMPRESS FOR ARCHIVE LOW ;
\n<\/strong><\/span><\/p>\n

SELECT SUM(BYTES)\/1024\/1024 MBYTES FROM DBA_SEGMENTS WHERE SEGMENT_NAME=’HCC_TEST_UPDATE’;
\n<\/span><\/p>\n

MBYTES
\n<\/span><\/p>\n

———
\n<\/span><\/p>\n

2
\n<\/span><\/p>\n

EXEC DBMS_STATS.GATHER_TABLE_STATS (‘UNIONE’, ‘HCC_TEST_UPDATE’, METHOD_OPT =>’ for all columns size 1′)
\n<\/span><\/p>\n

SELECT TABLE_NAME, BLOCKS, CHAIN_CNT FROM DBA_TABLES WHERE TABLE_NAME = ‘HCC_TEST_UPDATE’;
\n<\/span><\/p>\n

TABLE_NAME BLOCKS CHAIN_CNT
\n<\/span><\/p>\n

—————————— ——— ———
\n<\/span><\/p>\n

HCC_TEST_UPDATE 176 0
\n<\/span><\/p>\n

SELECT ROWID, OBJECT_ID FROM HCC_TEST_UPDATE WHERE OBJECT_ID =100 ;
\n<\/span><\/p>\n

ROWID OBJECT_ID
\n<\/span><\/p>\n

——————- ———
\n<\/span><\/p>\n

AABUaUAAHAABC+4Asp 100
\n<\/span><\/p>\n

UPDATE HCC_TEST_UPDATE SET TIMESTAMP = SYSDATE ;
\n<\/span><\/p>\n

199999 rows Updated.
\n<\/span><\/p>\n

SQL Execution Time > 00:00:15.897
\n<\/span><\/p>\n

COMMIT;
\n<\/span><\/p>\n

SELECT SUM(BYTES)\/1024\/1024 MBYTES FROM DBA_SEGMENTS WHERE SEGMENT_NAME=’HCC_TEST_UPDATE’;
\n<\/span><\/p>\n

MBYTES
\n<\/span><\/p>\n

———
\n<\/span><\/p>\n

9
\n<\/span><\/p>\n

\n

UPDATE \uc774\ud6c4\uc5d0 \ud14c\uc2a4\ud2b8 \uacb0\uacfc\ub97c \ubcf4\uba74 HCC \uc555\ucd95 \uc774\uc804\uc5d0 \ube44\ud574 SEGMENT \uac12\uacfc BLOCK \uc218\uac00 \uc99d\uac00 \ud55c \uac83\uc744 \ubcfc \uc218 \uc788\uc9c0\ub9cc \uc555\ucd95 \uc804 \ub370\uc774\ud130 \ubcf4\ub2e4\ub294 SEGMENT \uac12\uacfc BLCOK \uc774 \uc904\uc5b4\ub4e0 \uac83\uc744 \ubcfc \uc218 \uc788 \ub2e4. \uadf8\ub807\ub2e4\uba74 \uc65c SEGMENT \uac12\uacfc BLCOK \uc218\ub294 \uc555\ucd95 \uc804\uc73c\ub85c \ub3cc\uc544\uac00\uc9c0 \uc54a\uc558\uc744\uae4c ?<\/p>\n

\uc815\ud655\ud788 \ub9d0\ud574\uc11c \uae30\uc874 HCC \uc555\ucd95\ud615\ud0dc\uc5d0\uc11c OLTP \ud615\ud0dc\uc758 \uc555\ucd95\uc73c\ub85c \ub2e4\uc6b4\uadf8\ub808\uc774\ub4dc\uac00 \ub41c \uac83\uc774\ub2e4. OLTP \uc555\ucd95 \ud615\uc2dd\uc740 DIRECT PATH LOAD \uac00 \ubc1c\uc0dd\ud558\uc9c0 \uc54a\uc740 \uc0c1\ud0dc\uc5d0\uc11c\ub3c4 \uc555\ucd95\uc774 \uac00\ub2a5\ud558\uba70 HCC \ub3d9\uc791 \ubc29\uc2dd\uc5d0\uc11c HCC \ub3d9\uc791\ubc29\uc2dd\uc5d0\uc11c \uc7a0\uc2dc \uc5b8\uae09\ud55c ROW \uae30\ubc18\uc758 BLOCK \ub2e8\uc704 \uc555\ucd95 \ubc29\uc2dd\uc774\ub2e4. OLTP \ud615\ud0dc\uc758 \uc555\ucd95\uc740 HCC \uc555\ucd95 \ud615\ud0dc\uc758 \ud14c\uc774\ube14\uc5d0\uc11c HCC \uc555\ucd95\uc744 \ud560 \uc218 \uc5c6\ub294 \uacbd\uc6b0 ( \ube44 DIRECT PATH LOAD ) HCC \uc555\ucd95\uc5d0 \ub300\ud55c \ubcf4\uc548 \uc555\ucd95 \ubc29\uc2dd\uc778 \uac83\uc774\ub2e4.<\/p>\n

<\/span><\/p>\n

\ud14c\uc2a4\ud2b8\ub97c \ud1b5\ud574 \uc790\uc138\ud788 \uc54c\uc544 \ubcf4\ub3c4\ub85d \ud558\uc790.
\n<\/span><\/p>\n

ALTER TABLE HCC_TEST_UPDATE MOVE COMPRESS FOR ARCHIVE LOW ;
\n<\/strong><\/span><\/p>\n

SELECT SUM(BYTES)\/1024\/1024 MBYTES FROM DBA_SEGMENTS WHERE SEGMENT_NAME=’HCC_TEST_UPDATE’;
\n<\/span><\/p>\n

MBYTES
\n<\/span><\/p>\n

———
\n<\/span><\/p>\n

2
\n<\/span><\/p>\n

SELECT ROWID, OBJECT_ID FROM HCC_TEST_UPDATE WHERE OBJECT_ID =100;
\n<\/span><\/p>\n

ROWID OBJECT_ID
\n<\/span><\/p>\n

——————- ———
\n<\/span><\/p>\n

AABUaUAAHAABC+4Asp 100
\n<\/span><\/p>\n

UPDATE HCC_TEST_UPDATE SET TIMESTAMP = SYSDATE WHERE OBJECT_ID =100;
\n<\/span><\/p>\n

1 rows Updated.
\n<\/span><\/p>\n

COMMIT;
\n<\/span><\/p>\n

SELECT ROWID, OBJECT_ID FROM HCC_TEST_UPDATE WHERE OBJECT_ID =100;
\n<\/span><\/p>\n

ROWID OBJECT_ID
\n<\/span><\/p>\n

——————- ———
\n<\/span><\/p>\n

AABUaUAAHAAAC\/HABS 100
\n<\/span><\/p>\n

— UPDATE \uc774\uc804 ROWID \uc870\ud68c
\n<\/span><\/p>\n

SELECT ROWID, OBJECT_ID FROM HCC_TEST_UPDATE WHERE ROWID = ‘AABUaUAAHAABC+4Asp’
\n<\/span><\/p>\n

ROWID OBJECT_ID
\n<\/span><\/p>\n

——————- ———
\n<\/span><\/p>\n

AABUaUAAHAABC+4Asp 100
\n<\/span><\/p>\n

\n

UPDATE \uc774\ud6c4\uc5d0 \ud14c\uc774\ube14\uc5d0 ROWID \uc815\ubcf4\ub97c \ubcf4\uba74 ROWID \uac00 \ubcc0\uacbd\ub418\uc5b4 \uc774\uc8fc \ub41c \uac83\uc744 \ubcfc \uc218 \uc788\ub2e4.<\/p>\n

UPDATE \ud558\uae30 \uc804\uc5d0 ROWID \uc744 \uc870\ud68c\ud574 \ubcf4\uba74 \uc5ed\uc2dc \uc774\uc8fc\ud55c ROWID \uc640 \ub3d9\uc77c\ud55c OBJECT_ID \uac00 \uc874 \uc7ac\ud558\uba70 \uc774\ub807\uac8c ROWID \uac00 \uc774\uc8fc\ud558\uc5ec 2 \uac1c\uc758 ROWID \uc744 \uac00\uc9c0\ub294 \uc774\uc720\ub294 OLTP \uc555\ucd95 \ud615\ud0dc\uac00 DIRECT PATH LOAD \uac00 \uc544\ub2cc \uacbd\uc6b0 (DIRECT PATH LOAD \uc778 \uacbd\uc6b0\ub294 ROWID \uc5d0 \ub300\ud55c \uac12\uc774 \ud558\ub098\ub9cc \uc874\uc7ac) \uc5d0\ub294 \ub370\uc774\ud130\ub97c \uc801\uc7ac\ud558\uba74\uc11c \uc555\ucd95\uc744 \ud558\ub294 \uac83\uc774 \uc544\ub2c8\ub77c \uc555\ucd95\ub418\uc9c0 \uc54a\uc740 \ub370\uc774\ud130\uac00 \uc801\uc7ac \ub418 \ub2e4\uac00 \ub354 \uc774\uc0c1 BLOCK \uc5d0 \ub370\uc774\ud130\uac00 \uc801\uc7ac \ub420 \uacf5\uac04\uc774 \uc5c6\uc73c\uba74 \uadf8\ub54c \uc555\ucd95\uc744 \ud55c\ub2e4.<\/p>\n

\uc555\ucd95 \ud6c4\uc5d0 \ube48 \uacf5\uac04 \uc740 FREE LIST \uc5d0 \ubc18\ud658\ud558\uace0 \uc555\ucd95 \ub418\uc9c0 \uc54a\uc740 \ub370\uc774\ud130\ub97c \ub2e4\uc2dc \uc801\uc7ac\ud558\ub2e4\uac00 \ub2e4\uc2dc BLOCK \uc5d0 \uc801\uc7ac \ud560 \uacf5\uac04\uc774 \uc5c6\uc744 \ub54c \uc555\ucd95\ub418\uc9c0 \uc54a\uc740 \ub370\uc774\ud130\ub97c \uc555\ucd95\ud558\uac8c \ub41c\ub2e4.<\/p>\n

\uc774 \uacfc\uc815\uc5d0\uc11c \ud55c ROW \uac12\uc5d0 \ub300\ud574\uc11c \uc77c\ubd80 \ubd84\uc740 \uc555\ucd95\ub41c \ud615\ud0dc\ub85c \uc801\uc7ac\ub418\uc5b4 \uc788\uace0 \uac19\uc740 ROW \uc5d0 \ub300\ud55c \uc77c\ubd80 \ub370\uc774\ud130\ub294 \uc555\ucd95 \ub418\uc5b4 \uc788\uc9c0 \uc54a\uc740 \ud615\ud0dc \uc758 \ub370\uc774\ud130\ub85c \uc874\uc7ac\ud558\uac8c \ub418\uba74\uc11c ROWID \uac00 1 \uac1c \uc774\uc0c1 \uc874\uc7ac \ud558\uac8c \ub41c\ub2e4.<\/p>\n

\ub2e4\uc2dc \ub9d0\ud574 HCC \ud615\ud0dc\uc758 \uc555\ucd95\ub41c \uacf5\uac04\uc5d0\uc11c UPD ATE \uac00 \ubc1c\uc0dd\ud558\uac8c \ub418\uba74 OLTP \uc555\ucd95\uc73c\ub85c \ub2e4\uc6b4\uadf8\ub808\uc774\ub4dc\uac00 \ub418\ub294\ub370 \ud55c ROW \uc5d0 \ub300\ud574\uc11c UPDATE \ub418\uc5b4 \ubcc0\uacbd\ub41c \uac12\uc740 \uc555\ucd95\ub418\uc9c0 \uc54a\uc740 \ud615\ud0dc\ub85c \uc874\uc7ac\ud558\uac8c \ub418\uace0 UPDATE \uac00 \ubc1c\uc0dd\ud558\uc9c0 \uc54a\uc740 ROW \ub294 \uc555\ucd95\ub41c \ud615\ud0dc\ub85c \uc874\uc7ac\ud558\uac8c \ub418\uc5b4 ROWID \uac00 1 \uac1c \uc774\uc0c1 \uc874\uc7ac \ud558\ub294 \uac83\uc774\ub2e4.<\/p>\n

\ud604\uc7ac \ud544\uc790\uac00 \uc9c0\uc6d0\ud558\uace0 \uc788\ub294 \uace0\uac1d\uc0ac\uc5d0\uc11c \uae30\uc874 DB2 \uc2dc\uc2a4\ud15c\uc5d0\uc11c \ud1b5\uacc4 \uad00\ub828 \uc77c\ubd80 \uc5c5\ubb34\ub97c EXADATA \uc2dc\uc2a4\ud15c\uc73c\ub85c \uad6c\ucd95\ud558\uc600\ub294\ub370 HCC \uc555\ucd95\uc744 \ud558\uace0\uc790 \ud558\ub294 \ub300\uc0c1\uc5d0 \ud14c\uc774\ube14\uc5d0 \ub300\ud574\uc11c UPDATE \uc5c5\ubb34\ub97c \ubaa8 \ub450 DELE TE \ud6c4 INSERT \uc5c5\ubb34\ub85c \ubcc0\uacbd\ud558\uc600\ub2e4.
\n<\/span><\/p>\n

HCC \uc555\ucd95 \uc2dc \uc720\uc758 \uc0ac\ud56d
\n<\/strong><\/span><\/p>\n

\uc2dc\uc2a4\ud15c \ucd08\uae30\uc5d0\ub294 \ub370\uc774\ud130\uac00 \ub9ce\uc9c0 \uc54a\uc744 \uac83\uc774\ub2e4. \uc2dc\uac04\uc774 \uc9c0\ub0a8\uc5d0 \ub530\ub77c \ub370\uc774\ud130\ub4e4\uc774 \uc99d\uac00 \ud560 \uac83\uc774\uace0 \ubcc0\uacbd\ub418\uc9c0 \uc54a\uc740 \uc774\ub825\uc758 \uc131\uaca9\ub4e4\uc744 \uac00\uc9c0\uace0 \uc788\ub294 \ud14c\uc774\ube14 \ub4e4\uc740 HCC \uc555\ucd95\uc73c\ub85c \uc2a4\ud1a0\ub9ac\uc9c0 \uacf5\uac04\uc744 \uc808\uc57d \ud560 \uc218 \uc788\ub2e4. \uadf8\ub7ec\ub098 \ud2b8\ub79c\uc7ad\uc158\uc774 \ub9ce\uc740 \uc628\ub77c\uc778 \uc2dc\uac04\uc5d0 HCC \uc555\ucd95\uc744 \ud558\uac8c \ub41c\ub2e4\uba74 HCC \uc555\ucd95\uc744 \ud558\ub294 \ub3d9 \uc548 DML \ud2b8\ub79c\uc7ad\uc158\ub4e4\uc740 \ud14c\uc774\ube14 LOCK (enq : TM – contention) \uc774 \ubc1c\uc0dd\ud558\uc5ec \uc2dc\uc2a4\ud15c\uc5d0 \uc545 \uc601\ud5a5\uc744 \ubbf8\uce58\uac8c \ub418\uae30 \ub54c\ubb38\uc774\ub2e4.<\/strong>
\n<\/span><\/p>\n

<\/p>\n

\uc704\uc5d0 \uc0c1\ud669\uc744 \ud14c\uc2a4\ud2b8\ub85c \ud655\uc778\ud574 \ubcf4\uc790.
\n<\/span><\/p>\n

— SESSION 1
\n<\/span><\/p>\n

CREATE TABLE HCC_LOCK_TEST NOLOGGING AS SELECT * FROM HCC_TEST_HCC1 ;
\n<\/span><\/p>\n

ALTER TABLE HCC_LOCK_TEST MOVE COMPRESS FOR ARCHIVE HIGH ;
\n<\/strong><\/span><\/p>\n

— SESSION 2 (DML \uc218\ud589)
\n<\/span><\/p>\n

SELECT OWNER,OBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE FROM HCC_LOCK_TEST WHERE ROWNUM =1 ;
\n<\/span><\/p>\n

OWNER OBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
\n<\/span><\/p>\n

——————– ———————— ——— ————– ———–
\n<\/span><\/p>\n

SYS WARNING_SETTINGS$ 254 254 TABLE
\n<\/span><\/p>\n

UPDATE HCC_LOCK_TEST SET TIMESTAMP=SYSDATE WHERE ROWNUM < 1000;
\n<\/span><\/p>\n

\n

— SESSION 3 ( TM LOCK \ubc1c\uc0dd)
\n<\/span><\/p>\n

SQL> SELECT SID,SERIAL#,USERNAME,MACHINE,EVENT FROM V$SESSION WHERE STATUS=’ACTIVE’ AND USERNAME=’UNIONE’;
\n<\/span><\/p>\n

SID SERIAL# USERNAME MACHINE EVENT
\n<\/span><\/p>\n

——— ——— ——— ———————– ———————
\n<\/span><\/p>\n

4726 1017 UNIONE WORKGROUP\\PARADISE-PC Enq: TM – contention
\n<\/span><\/p>\n

\n

\ud558\uc9c0\ub9cc HCC \uc555\ucd95\uc744 \ud558\uace0\uc790 \ud558\ub294 \ub300\uc6a9\ub7c9 \ud14c\uc774\ube14\uc740 \ub0a0\uc9dc\ub97c \uae30\uc900\uc73c\ub85c \uc6d4\uc774\ub098 \uc77c\ubcc4 \uc555\ucd95\uc744 \ud558\ub824\uace0 \ud560 \uac83\uc774\ub2e4. \uadf8\ub807\ub2e4\uba74 \uacfc\uac70 \ub2ec\uc5d0 \ub300\ud574\uc11c HCC \uc555\ucd95\uc744 \ud55c\ub2e4\uba74 \uc704\uc640 \uac19\uc740 \ubb38\uc81c\ub294 \ubc1c\uc0dd\ud558\uc9c0 \uc54a \uaca0\uc9c0\ub9cc \uc5ec\uc804\ud788 HCC \uc555\ucd95\uc744 \ud558\uae30\uc5d0\ub294 \ub9ce\uc740 \uc5b4\ub824\uc6c0\uc774 \uc874\uc7ac\ud55c\ub2e4.<\/p>\n

\uacfc\uac70 \ud30c\ud2f0\uc158\uc5d0 \ub300\ud574\uc11c ALTER MOVE \uba85\ub839\uc5b4\ub85c HCC \uc555\ucd95\uc744 \ud558\uac8c \ub418\uba74 ROWID \uac12\uc774 \ubcc0\uacbd\ub418\uc5b4 \uae30\uc874\uc5d0 \uc778\ub371\uc2a4\ub97c \uc0ac\uc6a9\ud560 \uc218 \uc5c6\uac8c \ub41c\ub2e4. HCC \uc555\ucd95 \uc774\ud6c4\uc5d0 INDEX REBUILD \uc744 \ud574\uc57c\ub9cc \ud55c\ub2e4. \uc778 \ub371\uc2a4 REBUILD \uc2dc\uac04 \ub3d9\uc548\uc740 HCC \ud55c \ud30c\ud2f0\uc158\uc740 FULL SCAN \uc744 \ud558\uac8c \ub420 \uac83\uc774\uba70 \uc774\ub85c \uc778\ud574 \ub9ce\uc740 \uc2dc\uc2a4\ud15c\uc758 \ubd80\ud558\ub97c \ubc1c\uc0dd \uc2dc\ud0ac \uac83\uc774\ub2e4. \uc774\ub7ec\ud55c \ubb38\uc81c\uc810\ub4e4\uc744 \ud574\uacb0 \ud558\uae30 \uc704\ud574 \ud30c\ud2f0\uc158 EXCHAN GE \uc744 \uc0ac\uc6a9\ud558\uc5ec HCC \uc555\ucd95\uc744 \uc801\uc6a9 \ud560 \uc218 \uc788\ub2e4. \ub2e8 \ubcc0\uacbd\uc774 \ubc1c\uc0dd\ud558\uc9c0 \uc54a\ub294 \uacfc\uac70 \ud30c\ud2f0\uc158 \ud14c\uc774\ube14\uc5d0\uc11c\ub9cc \uac00 \ub2a5\ud558\ub2e4.
\n<\/span><\/p>\n


\n\ud14c\uc2a4\ud2b8\ub97c \ud1b5\ud574\uc11c \uc54c\uc544 \ubcf4\ub3c4\ub85d \ud558\uc790.
\n<\/span><\/p>\n

— \ud14c\uc2a4\ud2b8 \ub370\uc774\ud130 \uc0dd\uc131
\n<\/span><\/p>\n

drop table HCC_PART_EXCH purge;
\n<\/span><\/p>\n

create table HCC_PART_EXCH
\n<\/span><\/p>\n

PARTITION BY RANGE (LAST_DDL_TIME)
\n<\/span><\/p>\n

( PARTITION HCC_PART_EXCH_201709 VALUES LESS THAN (TO_DATE(‘2017-10-01′,’yyyy-mm-dd’))
\n<\/span><\/p>\n

, PARTITION HCC_PART_EXCH_201710 VALUES LESS THAN (TO_DATE(‘2017-11-01′,’yyyy-mm-dd’))
\n<\/span><\/p>\n

, PARTITION HCC_PART_EXCH_201711 VALUES LESS THAN (TO_DATE(‘2017-12-01′,’yyyy-mm-dd’))
\n<\/span><\/p>\n

)
\n<\/span><\/p>\n

as select * from DBA_OBJECTS where LAST_DDL_TIME is not null
\n<\/span><\/p>\n

UNION ALL
\n<\/span><\/p>\n

select * from DBA_OBJECTS where LAST_DDL_TIME is not null;
\n<\/span><\/p>\n

CREATE INDEX HCC_PART_EXCH_IX1 ON HCC_PART_EXCH (OWNER,OBJECT_NAME) LOCAL;
\n<\/span><\/p>\n

ALTER TABLE HCC_PART_EXCH MOVE PARTITION HCC_PART_EXCH_201709 COMPRESS FOR ARCHIVE LOW ;
\n<\/strong><\/span><\/p>\n

— ALTER MOVE \uba85\ub839\uc5b4\ub85c HCC \uc77c\ubd80 \ud30c\ud2f0\uc158 \uc555\ucd95
\n<\/strong><\/span><\/p>\n

SELECT TABLE_OWNER,TABLE_NAME,PARTITION_NAME,COMPRESSION,COMPRESS_FOR FROM
\n<\/span><\/p>\n

DBA_TAB_PARTITIONS WHERE TABLE_NAME=’HCC_PART_EXCH’;
\n<\/span><\/p>\n

TABLE_OWNER TABLE_NAME PARTITION_NAME COMPRESSION COMPRESS_FOR
\n<\/span><\/p>\n

————– —————– ————————- ———– ———-
\n<\/span><\/p>\n

UNIONE HCC_PART_EXCH HCC_PART_EXCH_201709 ENABLED ARCHIVE LOW
\n<\/span><\/p>\n

UNIONE HCC_PART_EXCH HCC_PART_EXCH_201710 DISABLED
\n<\/span><\/p>\n

UNIONE HCC_PART_EXCH HCC_PART_EXCH_201711 DISABLED
\n<\/span><\/p>\n

— \uc778\ub371\uc2a4 UNUSABLE \uc0c1\ud0dc
\n<\/span><\/p>\n

SELECT INDEX_NAME, PARTITION_NAME, STATUS FROM DBA_IND_PARTITIONS WHERE
\n<\/span><\/p>\n

INDEX_NAME=’HCC_PART_EXCH_IX1′;
\n<\/span><\/p>\n

INDEX_NAME PARTITION_NAME STATUS
\n<\/span><\/p>\n

————————- ————————- ——–
\n<\/span><\/p>\n

HCC_PART_EXCH_IX1 HCC_PART_EXCH_201709 UNUSABLE
\n<\/span><\/p>\n

HCC_PART_EXCH_IX1 HCC_PART_EXCH_201710 USABLE
\n<\/span><\/p>\n

HCC_PART_EXCH_IX1 HCC_PART_EXCH_201711 USABLE
\n<\/span><\/p>\n

\n

— \ud30c\ud2f0\uc158 TEMP \ud14c\uc774\ube14 \uc0dd\uc131
\n<\/strong><\/span><\/p>\n

CREATE TABLE HCC_PART_EXCH_201710_TMP NOLOGGING COMPRESS FOR ARCHIVE LOW
\n<\/strong><\/span><\/p>\n

AS SELECT * FROM HCC_PART_EXCH PARTITION(HCC_PART_EXCH_201710);
\n<\/strong><\/span><\/p>\n

SQL Execution Time > 00:00:01.560
\n<\/span><\/p>\n

CREATE INDEX HCC_PART_EXCH_201710_TMP_IX ON HCC_PART_EXCH_201710_TMP (OWNER,OBJECT_NAME);
\n<\/span><\/p>\n

SQL Execution Time > 00:00:00.015
\n<\/span><\/p>\n

— \ud30c\ud2f0\uc158 EXCHANGE
\n<\/span><\/p>\n

ALTER TABLE HCC_PART_EXCH EXCHANGE PARTITION HCC_PART_EXCH_201710 WITH TABLE HCC_PART_EXCH_201710_TMP INCLUDING INDEXES WITHOUT VALIDATION ;
\n<\/strong><\/span><\/p>\n

SQL Execution Time > 00:00:00.015
\n<\/span><\/p>\n

SQL> SELECT TABLE_OWNER,TABLE_NAME,PARTITION_NAME,COMPRESSION,COMPRESS_FOR FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME=’HCC_PART_EXCH’;
\n<\/span><\/p>\n

TABLE_OWNER TABLE_NAME PARTITION_NAME COMPRESSION COMPRESS_FOR
\n<\/span><\/p>\n

—————- —————– —————————— ———– ————
\n<\/span><\/p>\n

UNIONE HCC_PART_EXCH HCC_PART_EXCH_201709 ENABLED ARCHIVE LOW
\n<\/span><\/p>\n

UNIONE HCC_PART_EXCH HCC_PART_EXCH_201710 ENABLED ARCHIVE LOW
\n<\/span><\/p>\n

UNIONE HCC_PART_EXCH HCC_PART_EXCH_201711 DISABLED
\n<\/span><\/p>\n

SQL> SELECT INDEX_NAME,PARTITION_NAME,STATUS FROM DBA_IND_PARTITIONS WHERE
\n<\/span><\/p>\n

INDEX_NAME=’HCC_PART_EXCH_IX1′;
\n<\/span><\/p>\n

INDEX_NAME PARTITION_NAME STATUS
\n<\/span><\/p>\n

—————————— —————————— ——–
\n<\/span><\/p>\n

HCC_PART_EXCH_IX1 HCC_PART_EXCH_201709 UNUSABLE
\n<\/span><\/p>\n

HCC_PART_EXCH_IX1 HCC_PART_EXCH_201710 USABLE
\n<\/span><\/p>\n

HCC_PART_EXCH_IX1 HCC_PART_EXCH_201711 USABLE
\n<\/span><\/p>\n

\n

\uacb0\ub860
\n<\/strong><\/span><\/p>\n

\uc2dc\uac04\uc774 \uc9c0\ub0a8\uc5d0 \ub530\ub77c \ub370\uc774\ud130\uac00 \uc810\uc810 \ub9ce\uc544\uc9c0\uba74\uc11c DBMS \uc2dc\uc2a4\ud15c\uc758 \uc870\ud68c \uc131\ub2a5\uc758 \ubb38\uc81c\uc640 \uc2a4\ud1a0\ub9ac\uc9c0 \uacf5\uac04\uc758 \uc99d\uac00\ub85c \ub9ce\uc740 \ube44\uc6a9\uc774 \ubc1c\uc0dd\ud558\uace0 \uc788\ub2e4. \uc774\ub7ec\ud55c \ubb38\uc81c\ub97c \ud574\uacb0\ud558\uae30 \uc704\ud55c \ub300\uc548 \uc911\uc5d0 \ud558\ub098\uac00 \ubc14\ub85c \ub370\uc774\ud130\ub97c \uc555\ucd95\ud558\ub294 \uac83\uc774\ub2e4. \ud2b9\ud788 EXADATA \uc2dc\uc2a4\ud15c\uc5d0\uc11c\ub294 HCC \ub77c\ub294 \uc9c4\ud654\ub41c \uc555\ucd95 \ubc29\ubc95\uc73c\ub85c \uc2a4\ud1a0\ub9ac\uc9c0 \uc808\uc57d\uacfc \uc870\ud68c \uc131\ub2a5\uc744 \ucd5c\uc801\ud654 \uc2dc\ud0ac \uc218 \uc788\ub2e4.<\/p>\n

\ud558\uc9c0\ub9cc \uc774\ub7ec\ud55c \uc555\ucd95 \ubc29\uc2dd\uc744 \uc798 \uc774\ud574\ud558\uc9c0 \ubabb\ud558\uace0 \uc0ac\uc6a9 \ud55c\ub2e4\uba74 \uc2dc\uc2a4\ud15c\uc5d0 \uc5ec\ub7ec \uac00\uc9c0 \ubb38\uc81c\uac00 \ubc1c\uc0dd \ud560 \uc218 \uc788\ub2e4. \ub610\ud55c \uc555\ucd95\ud558\uace0\uc790 \ud558 \ub294 \ub300\uc0c1\ub3c4 \uc798 \uc120\ubcc4 \ud574\uc57c\ub9cc \ud55c\ub2e4. \uac31\uc2e0\uc774 \ub9ce\uc740 \ud14c\uc774\ube14\uc5d0 HCC \uc555\ucd95\uc744 \uc801\uc6a9 \ud55c\ub2e4\uba74 \uc2a4\ud1a0\ub9ac\uc9c0 \uacf5\uac04 \uc808\uac10\uc5d0 \ucd5c\ub300 \ud6a8\uacfc\ub97c \ubcfc \uc218 \uc5c6\ub2e4. \ub530\ub77c\uc11c \uac31\uc2e0\uc774 \ub9ce\uc774 \uc77c\uc5b4\ub098\uc9c0 \uc54a\uc740 \ub370\uc774\ud130\uc5d0 \ub300\ud574\uc11c \uc555\ucd95 \ub300\uc0c1\uc73c\ub85c \uc120\uc815\ud558\ub294 \uac83\uc774 \uc88b\ub2e4. \uc790\uc8fc \uac31\uc2e0\ub418\ub294 \ub370\uc774\ud130 \uc544\ub2cc \ubcc0\ud654\uac00 \uc801\uc740 \ub370\uc774\ud130\uc5d0 \ub300\ud574\uc11c HCC \uc555\ucd95 \uae30\uc220\uc744 \uc0ac\uc6a9 \ud55c\ub2e4\uba74 BIG DATA \uc2dc\ub300\uc5d0 \ud558\ub4dc\uc6e8\uc5b4 \ube44\uc6a9\uc758 \uc808\uac10\uacfc \ub370\uc774 \ud130 \ucc98\ub9ac \uc131\ub2a5\uc5d0 \ub300\ud574 \uc5ec\ub7ec \uac00\uc9c0 \uc2dc\ub108\uc9c0 \ud6a8\uacfc\ub97c \uc5bb\uc744 \uc218 \uc788\uc744 \uac83\uc774\ub2e4.
\n<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

\uc2dc\uac04\uc774 \uc9c0\ub098\uba74\uc11c \ub370\uc774\ud130\ub294 \uae09\uc18d\ud558\uac8c \uc99d\uac00\ud558\uace0 \uc788\ub2e4. \ub370\uc774\ud130\uac00 \uc99d\uac00\ud568\uc5d0 \ub530\ub77c DBMS \uc5d0\uc11c \uad00\ub9ac \ub418\uc5b4\uc9c0\ub294 \uc815\ubcf4\ub3c4 \uae09\uc18d\ud558\uac8c \uc99d\uac00 \ud558\uace0 \uc788\ub2e4. \uc774\ub85c \uc778\ud574 \uc800\uc7a5\uacf5\uac04\uc758 \ubd80\uc871\uc73c\ub85c \ud558\ub4dc\uc6e8\uc5b4 \ube44\uc6a9\uc758 \uc99d \uac00\uc640 \ub370\uc774\ud130 \ucc98\ub9ac \uc131\ub2a5\uc5d0 \ub9ce\uc740 \ubb38\uc81c \uc810\ub4e4\uc774 \ub098\ud0c0\ub098\uace0 \uc788\ub2e4. \uc774\ub7ec\ud55c \ubb38\uc81c\uc810\ub4e4\uc744 \ud574\uacb0\ud558\uace0 \uc790 ORACLE \uc5d0\uc11c\ub294 EXADATA \ub77c\ub294 \uc2dc\uc2a4\ud15c\uc744 \ud1b5\ud574 \uc2a4\ud1a0\ub9ac\uc9c0 \uacf5\uac04 \ubd80\uc871\ud604\uc0c1 \uacfc \ub370\uc774\ud130 \ucc98\ub9ac \uc131\ub2a5\uc744 \ud5a5\uc0c1\uc2dc\ud0a4\uace0\uc790 \ud558\uc600\ub2e4. EXADATA \uc2dc\uc2a4\ud15c\uc774\ub780 […]<\/p>\n","protected":false},"author":1,"featured_media":2100,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[405],"tags":[27,782,321],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/haisins.synology.me\/wordpress\/wp-content\/uploads\/2018\/01\/exadata3-featured.png?fit=845%2C483&ssl=1","amp_enabled":true,"_links":{"self":[{"href":"https:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1609"}],"collection":[{"href":"https:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1609"}],"version-history":[{"count":2,"href":"https:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1609\/revisions"}],"predecessor-version":[{"id":2101,"href":"https:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1609\/revisions\/2101"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/2100"}],"wp:attachment":[{"href":"https:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1609"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1609"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1609"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}