{"id":1303,"date":"2016-03-23T07:49:05","date_gmt":"2016-03-23T07:49:05","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=1303"},"modified":"2016-03-23T07:49:05","modified_gmt":"2016-03-23T07:49:05","slug":"%ec%9d%b8%eb%8d%b1%ec%8a%a4-%ec%82%ac%ec%9a%a9-%ed%98%84%ed%99%a9-%eb%aa%a8%eb%8b%88%ed%84%b0%eb%a7%81","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=1303","title":{"rendered":"\uc778\ub371\uc2a4 \uc0ac\uc6a9 \ud604\ud669 \ubaa8\ub2c8\ud130\ub9c1"},"content":{"rendered":"

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

    \n
  1. \n
    \n

    \uac1c\uc694
    \n<\/span><\/h1>\n<\/div>\n


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

    \ub370\uc774\ud130\ubca0\uc774\uc2a4\ub97c \uc6b4\uc601 \uc911\uc5d0 \uc0c8\ub85c\uc6b4 \uc778\ub371\uc2a4\ub97c \uc0dd\uc131\ud558\uac70\ub098, \uae30\uc874\uc758 \uc778\ub371\uc2a4 \ubcc0\uacbd\ub428\uc73c\ub85c\uc368 \uc601\ud5a5\uc744 \ubc1b\uc544 \uc0ac\uc6a9\ub418\uc9c0 \uc54a\ub294 \uc778\ub371\uc2a4\uac00 \uc0dd\uaca8\ub0a0 \uc218 \uc788\ub2e4. \ub530\ub77c\uc11c \uadf8\ub7ec\ud55c \uc778\ub371\uc2a4\ub97c \ucc3e\uc544 \uc218\uc815\ud558\uac70\ub098 \uc81c\uac70\ud560 \ud544\uc694\uc131\uc774 \uc81c\uae30\ub41c\ub2e4. \uc2e4\uc81c\ub85c \ud29c\ub2dd\uc791\uc5c5 \uc774\ud6c4 \uae30\uc874\uc5d0 \uc788\ub358 \uc778\ub371\uc2a4\ub97c \uc81c\uac70\ud558\ub824\ub294 \uacbd\uc6b0, \uc2e4\uc81c\ub85c \uc5b4\ub5a4 \uc778\ub371\uc2a4\ub97c \uc81c\uac70\ud574\uc57c \ud560\uc9c0 \uacb0\uc815\ud558\uae30\ub780 \uc27d\uc9c0 \uc54a\ub2e4. \ud2b9\uc815 \uae30\uac04\ub3d9\uc548\uc758 \ucffc\ub9ac\uc5d0 \ub300\ud55c \uc5d1\uc138\uc2a4 \uc720\ud615\uc744 \ubd84\uc11d\ud558\uace0, \uc778\ub371\uc2a4\uc758 \uc0ac\uc6a9\uc720\ubb34\ub97c \ubaa8\ub2c8\ud130\ub9c1\ud55c \uc774\ud6c4\uc5d0 \uc0ac\uc6a9\ub418\uc9c0 \uc54a\ub294 \uc778\ub371\uc2a4\ub97c \uc81c\uac70\ud574\uc57c\ub9cc \ud560 \uac83\uc774\ub2e4.
    \n<\/span><\/p>\n


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

    \uc624\ub77c\ud074 9i \uc5d0\uc11c \uc81c\uacf5\ub418\ub294 \ub2e4\uc774\ub098\ubbf9 \ubdf0\uc778 v$object_usage\ub97c \uc774\uc6a9\ud558\uc5ec \uc704\uc758 \uc791\uc5c5\uc744 \uc218\ud589\ud558\uace0, \uadf8 \uacb0\uacfc\ub97c \ud1a0\ub300\ub85c \uc778\ub371\uc2a4\uc758 \uc0ac\uc6a9\ud604\ud669\uc744 \ud30c\uc545\ud560 \uc218 \uc788\ub2e4.
    \n<\/span><\/p>\n


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

  2. \n

    Index \uc0ac\uc6a9\ud604\ud669 \ubd84\uc11d\ubc29\ubc95
    \n<\/span><\/h1>\n


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

  3. \n

    V$OBJECT_USAGE
    \n<\/span><\/h2>\n


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

    \n\n\n\n\n\n<\/colgroup>\n\n\n\n\n\n\n\n\n
    \n

    Column Name<\/span><\/strong><\/span> <\/span><\/p>\n<\/td>\n

    		\n

    Null ?<\/span><\/strong><\/span> <\/span><\/p>\n<\/td>\n

    		\n

    Data Type<\/span><\/strong><\/span> <\/span><\/p>\n<\/td>\n

    		\n

    Description<\/span><\/strong><\/span> <\/span><\/p>\n<\/td>\n<\/tr>\n

    \n

    INDEX_NAME<\/span><\/p>\n<\/td>\n

    		\n

    NOT NULL<\/span> <\/span><\/p>\n<\/td>\n

    		\n

    VARCHAR2(30)<\/span><\/p>\n<\/td>\n

    		\n

    \uc778\ub371\uc2a4\uba85<\/span><\/p>\n<\/td>\n<\/tr>\n

    \n

    TABLE_NAME<\/span><\/p>\n<\/td>\n

    		\n

    NOT NULL<\/span> <\/span><\/p>\n<\/td>\n

    		\n

    VARCHAR2(30)<\/span><\/p>\n<\/td>\n

    		\n

    \uc778\ub371\uc2a4 \ud14c\uc774\ube14\uba85<\/span><\/p>\n<\/td>\n<\/tr>\n

    \n

    MONITORING<\/span><\/p>\n<\/td>\n

    		 <\/span><\/td>\n\n

    VARCHAR2(3)<\/span><\/p>\n<\/td>\n

    		\n

    \ubaa8\ub2c8\ud130\ub9c1 \uc218\ud589 \uc5ec\ubd80<\/span><\/p>\n<\/td>\n<\/tr>\n

    \n

    USED<\/span> <\/span><\/p>\n<\/td>\n

    		 <\/span><\/td>\n\n

    VARCHAR2(3)<\/span> <\/span><\/p>\n<\/td>\n

    		\n

    \uc778\ub371\uc2a4 Access \uc5ec\ubd80<\/span><\/p>\n<\/td>\n<\/tr>\n

    \n

    START_MONITORING<\/span> <\/span><\/p>\n<\/td>\n

    		 <\/span><\/td>\n\n

    VARCHAR2(19)<\/span> <\/span><\/p>\n<\/td>\n

    		\n

    \ubaa8\ub2c8\ud130\ub9c1 \uc218\ud589 \uc2dc\uc791<\/span><\/p>\n<\/td>\n<\/tr>\n

    \n

    END_MONITORING<\/span> <\/span><\/p>\n<\/td>\n

    		 <\/span><\/td>\n\n

    VARCHAR2(19)<\/span> <\/span><\/p>\n<\/td>\n

    		\n

    \ubaa8\ub2c8\ud130\ub9c1 \uc218\ud589 \uc885\ub8cc<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

    \ud45c 1 \u2013 Desciption of ‘v$object_usage
    \n<\/span><\/p>\n


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

  4. \n

    \uc0ac\uc6a9\ubc29\ubc95
    \n<\/span><\/h2>\n


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

    \n\n\n<\/colgroup>\n\n\n
    \n


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

    — # \uc778\ub371\uc2a4 \ubaa8\ub2c8\ud130\ub9c1 \uc0ac\uc6a9 \uc608\uc81c
    \n<\/span><\/p>\n


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


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

    — 1. Sample Table \ubc0f Index\ub97c \uc0dd\uc131\ud55c\ub2e4.
    \n<\/span><\/p>\n


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

    SQL> CREATE TABLE SAMPLE(
    \n<\/span><\/p>\n

    2 ID NUMBER(4),
    \n<\/span><\/p>\n

    3 DATA VARCHAR2(10)
    \n<\/span><\/p>\n

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

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


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

    Table created.
    \n<\/span><\/p>\n


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

    SQL> INSERT INTO SAMPLE VALUES (1, ‘aaaa’);
    \n<\/span><\/p>\n


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

    1 row created.
    \n<\/span><\/p>\n


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


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

    SQL> INSERT INTO SAMPLE VALUES (2, ‘bbbb’);
    \n<\/span><\/p>\n


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

    1 row created.
    \n<\/span><\/p>\n


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

    SQL> INSERT INTO SAMPLE VALUES (3, ‘cccc’);
    \n<\/span><\/p>\n


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

    1 row created.
    \n<\/span><\/p>\n


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

    SQL> INSERT INTO SAMPLE VALUES (4, ‘dddd’);
    \n<\/span><\/p>\n


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

    1 row created.
    \n<\/span><\/p>\n


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

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


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

    Commit complete.
    \n<\/span><\/p>\n


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


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

    — 2. \ud14c\uc774\ube14\uc5d0 PK \uc778\ub371\uc2a4\ub97c \ucd94\uac00\ud55c\ub2e4.
    \n<\/span><\/p>\n


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

    SQL> select * from sample;
    \n<\/span><\/p>\n


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

    ID DATA
    \n<\/span><\/p>\n

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

    1 aaaa
    \n<\/span><\/p>\n

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

    3 cccc
    \n<\/span><\/p>\n

    4 dddd
    \n<\/span><\/p>\n


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

    SQL> <\/span>ALTER TABLE SAMPLE ADD CONSTRAINT pk_sample_id PRIMARY KEY(id);<\/span><\/strong>
    \n\t\t\t\t\t\t\t\t<\/span><\/p>\n


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

    Table altered.
    \n<\/span><\/p>\n


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

    SQL> desc sample;
    \n<\/span><\/p>\n

    Name Null? Type
    \n<\/span><\/p>\n

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

    ID NOT NULL NUMBER(4)
    \n<\/span><\/p>\n

    DATA VARCHAR2(10)
    \n<\/span><\/p>\n


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

    SQL> SELECT index_name, table_name, index_type
    \n<\/span><\/p>\n

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

    3 WHERE table_name = ‘SAMPLE’;
    \n<\/span><\/p>\n


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

    INDEX_NAME TABLE_NAME INDEX_TYPE
    \n<\/span><\/p>\n

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

    PK_SAMPLE_ID SAMPLE NORMAL
    \n<\/span><\/p>\n


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


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

    — 3. v$object_usage \ubdf0\ub97c \ud655\uc778\ud55c\ub2e4. <\/span>\u2013<\/span> \uc544\uc9c1\uae4c\uc9c0\ub294 \uc544\ubb34 \uac83\ub3c4 \uc870\ud68c\ub418\uc9c0 \uc54a\ub294\ub2e4.
    \n<\/span><\/p>\n


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

    SQL> set lines 110
    \n<\/span><\/p>\n

    SQL> set pages 999
    \n<\/span><\/p>\n

    SQL> column index_name format a20
    \n<\/span><\/p>\n

    SQL> column monitoring format a10
    \n<\/span><\/p>\n

    SQL> column used format a10
    \n<\/span><\/p>\n

    SQL> column start_monitoring format a20
    \n<\/span><\/p>\n

    SQL> column end_monitoring format a20
    \n<\/span><\/p>\n

    SQL> SELECT index_name, monitoring, used, start_monitoring, end_monitoring
    \n<\/span><\/p>\n

    2 FROM v$object_usage;
    \n<\/span><\/p>\n


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

    no rows selected
    \n<\/span><\/p>\n


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


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

    — 4. \uc778\ub371\uc2a4 \uc0ac\uc6a9\uc5ec\ubd80\ub97c \uc54c\uae30 \uc704\ud574 \ubaa8\ub2c8\ud130\ub9c1\uc744 \uc0ac\uc6a9\ud55c\ub2e4.
    \n<\/span><\/p>\n


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

    SQL> <\/span>ALTER INDEX pk_sample_id MONITORING USAGE;<\/span><\/strong>
    \n\t\t\t\t\t\t\t\t<\/span><\/p>\n


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

    Index altered.
    \n<\/span><\/p>\n


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


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


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


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

    — 5. v$object_usage \ub97c \ucffc\ub9ac\ud558\uc5ec \ubaa8\ub2c8\ud130\ub9c1 \uc5ec\ubd80\ub97c \ud655\uc778\ud55c\ub2e4.
    \n<\/span><\/p>\n


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

    SQL> SELECT index_name, monitoring, used, start_monitoring, end_monitoring
    \n<\/span><\/p>\n

    2 FROM v$object_usage;
    \n<\/span><\/p>\n


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

    INDEX_NAME MONITORING USED START_MONITORING END_MONITORING
    \n<\/span><\/p>\n

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

    PK_SAMPLE_ID <\/span>YES<\/span><\/strong><\/span> NO <\/span>09\/16\/2007 15:40:41<\/span><\/strong><\/span>
    \n\t\t\t\t\t\t\t\t<\/span><\/p>\n


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

    — 6. \ud574\ub2f9 \ud14c\uc774\ube14\uc5d0 \ub300\ud574 \uc778\ub371\uc2a4\ub97c \ud0c0\ub3c4\ub85d \ucffc\ub9ac\ub97c \ud14c\uc2a4\ud2b8\ud55c\ub2e4.
    \n<\/span><\/p>\n

    — Execution Plan \uc744 \ubcf4\uae30 \uc704\ud574 plan table\uc744 \uc0dd\uc131\ud55c\ub2e4.
    \n<\/span><\/p>\n


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

    SQL> @$ORACLE_HOME\/rdbms\/admin\/utlxplan
    \n<\/span><\/p>\n


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

    Table created.
    \n<\/span><\/p>\n


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

    SQL> set autotrace on explain
    \n<\/span><\/p>\n

    SQL> SELECT * FROM sample WHERE <\/span>id = 2;<\/span><\/strong><\/span>
    \n\t\t\t\t\t\t\t\t<\/span><\/p>\n


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

    ID DATA
    \n<\/span><\/p>\n

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

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


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

    Execution Plan
    \n<\/span><\/p>\n

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

    0 SELECT STATEMENT Optimizer=CHOOSE
    \n<\/span><\/p>\n

    1 0 TABLE ACCESS (BY INDEX ROWID) OF ‘SAMPLE’
    \n<\/span><\/p>\n


    \n\t\t\t\t\t\t\t\t\t\t\t<\/span>2 1 INDEX (UNIQUE SCAN) OF ‘PK_SAMPLE_ID’ (UNIQUE)
    \n<\/span><\/strong><\/span><\/p>\n


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

    — 7. v$object_usage \ub97c \ucffc\ub9ac\ud558\uba74 \uc778\ub371\uc2a4 \uc0ac\uc6a9\uc5ec\ubd80\ub97c \uc54c \uc218 \uc788\ub2e4.
    \n<\/span><\/p>\n


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

    SQL> SELECT index_name, monitoring, used, start_monitoring, end_monitoring
    \n<\/span><\/p>\n

    2 FROM v$object_usage;
    \n<\/span><\/p>\n


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

    INDEX_NAME MONITORING USED START_MONITORING END_MONITORING
    \n<\/span><\/p>\n

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

    PK_SAMPLE_ID YES <\/span>YES<\/span><\/strong><\/span> 09\/16\/2007 15:40:41
    \n<\/span><\/p>\n


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

    — 7. \uc778\ub371\uc2a4 \ubaa8\ub2c8\ud130\ub9c1\uc744 \uc911\uc9c0\ud55c\ub2e4.
    \n<\/span><\/p>\n


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


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

    SQL> ALTER INDEX pk_sample_id NOMONITORING USAGE;
    \n<\/span><\/p>\n


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

    Index altered.
    \n<\/span><\/p>\n


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


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

    SQL> SELECT index_name, monitoring, used, start_monitoring, end_monitoring
    \n<\/span><\/p>\n

    2 FROM v$object_usage;
    \n<\/span><\/p>\n


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

    INDEX_NAME MONITORING USED START_MONITORING END_MONITORING
    \n<\/span><\/p>\n

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

    PK_SAMPLE_ID <\/span>NO <\/span><\/strong><\/span> YES 09\/16\/2007 15:40:41 <\/span>09\/16\/2007 18:34:47
    \n<\/span><\/strong><\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n


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

    <\ucc38\uace0> Index Monitoring\uc744 \uc218\ud589\ud560 \ub54c \ud55c \ubc88\uc774\ub77c\ub3c4 \uc0ac\uc6a9\ub418\uc5b4 v$object_usage \ubdf0\uc5d0 \uc800\uc7a5\uc774 \ub418\uc5c8\ub2e4\uba74, \ub354\uc774\uc0c1 \ud574\ub2f9 \uc778\ub371\uc2a4\uc5d0 \ub300\ud574 \ubaa8\ub2c8\ud130\ub9c1 \ub418\uc9c0 \uc54a\ub294\ub2e4. \uadf8\ub7ec\ubbc0\ub85c \ub2e4\ub7c9\uc758 \uc778\ub371\uc2a4\uc5d0 \ub300\ud55c \ubaa8\ub2c8\ud130\ub9c1\uc73c\ub85c \uc778\ud574 \uc2dc\uc2a4\ud15c\uc5d0 \ubd80\ud558\ub97c \uc904 \uc5fc\ub824\ub294 \uc5c6\ub2e4.
    \n<\/span><\/p>\n


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


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


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


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

  5. \n

    DB \uc804\uccb4 Index \uc0ac\uc6a9\ud604\ud669 \ubd84\uc11d
    \n<\/span><\/h1>\n<\/li>\n
  6. \n

    \uac1c\uc694
    \n<\/span><\/h2>\n

    \ud604\uc5c5\uc5d0\uc11c \uc6b4\uc601\uc911\uc778 DB\ub0b4\uc5d0\uc11c \ub370\uc774\ud130\uc758 \uc561\uc138\uc2a4\ub97c \ube60\ub974\uac8c \uc218\ud589\ud558\uae30 \uc704\ud574 \uc778\ub371\uc2a4\ub97c \uc0dd\uc131\ud55c\ub2e4. \uc0dd\uc131\ud55c \uc778\ub371\uc2a4\uac00 \ucffc\ub9ac\uc5d0\uc11c \ub354 \uc774\uc0c1 \uc0ac\uc6a9\ub418\uc9c0 \uc54a\ub294\ub2e4\uace0 \ud310\ub2e8\uc774 \ub418\uba74 \uc81c\uac70\ub97c \ud558\ub294 \uac83\uc774 \ud14c\uc774\ube14\uc758 \ub370\uc774\ud130 \uc870\uc791(DML)\uc2dc \uc778\ub371\uc2a4 \uac31\uc2e0\uc5d0 \ub300\ud55c \ubd80\ud558\ub97c \ucd5c\uc18c\ud654 \ud560 \uc218 \uc788\ub2e4. \ud2b9\ud788 OLTP \uc5c5\ubb34\uac00 \ub9ce\uc740 DB\uc758 \uacbd\uc6b0 \ud14c\uc774\ube14\uc5d0 \ubd88\ud544\uc694\ud55c \uc778\ub371\uc2a4\uac00 \ub9ce\uc774 \uc0dd\uc131\ub418\uc5b4 \uc788\ub2e4\uba74 DML \uc791\uc5c5\uc740 \uadf8 \ub9cc\ud07c \ub290\ub824\uc9c8 \uc218 \ubc16\uc5d0 \uc5c6\ub2e4.
    \n<\/span><\/p>\n

    \ud55c\ud3b8, \ud1b5\uacc4\uc815\ubcf4\uc758 \uac31\uc2e0\uc774\ub098 \ud29c\ub2dd\ub4f1\uc73c\ub85c \uc218\ud589\ub418\ub294 \ud50c\ub79c\uc774 \ubcc0\uacbd\ub41c \uacbd\uc6b0\uc5d0\ub3c4 \uc778\ub371\uc2a4 \uc0ac\uc6a9\uc5ec\ubd80\ub97c \ubbfc\uac10\ud558\uac8c \uccb4\ud06c\ud574 \ubcfc \ud544\uc694\uac00 \uc788\ub2e4. \uadf8\uac83\uc774 DBA\ub098 \ud29c\ub108\uac00 \uc758\ub3c4\ud55c \ub300\ub85c \uc778\ub371\uc2a4\ub97c \uc0ac\uc6a9\ud558\uac70\ub098 \uc0ac\uc6a9\ud558\uc9c0 \uc54a\uc74c\uc744 \ud310\ub2e8\ud55c \uacbd\uc6b0\ub77c\uba74, \uc778\ub371\uc2a4\uc758 \uc885\uc18d\uc5ec\ubd80\ub97c \uacb0\uc815\ud560 \uc218 \uc788\ub2e4. \ud558\uc9c0\ub9cc \uadf8\uc678 \ub2e4\ub978 \uc778\ub371\uc2a4\ub4e4\uc5d0 \ub300\ud574\uc11c\ub294 \uc6b4\uc601\uc911\uc5d0 \uc0ac\uc6a9\ub418\uace0 \uc788\ub294\uc9c0 \uace7\ubc14\ub85c \uc54c\uae30 \uc5b4\ub835\ub2e4.
    \n<\/span><\/p>\n

    \uadf8\ub798\uc11c DB\ub0b4\uc5d0 \uc720\uc800\uac00 \uc0dd\uc131\ud55c \ubaa8\ub4e0 \uc778\ub371\uc2a4\uc5d0 \ub300\ud558\uc5ec \uc77c\uc815\uae30\uac04 \ub3d9\uc548 \ubaa8\ub2c8\ud130\ub9c1\uc744 \uc218\ud589\ud558\uc5ec, \uc0ac\uc6a9\uc5ec\ubd80\ub97c \ud310\uac00\ub984\ud574 \ubcfc \uc218 \uc788\ub2e4. \uc55e\uc11c \uc624\ub77c\ud074\uc5d0\uc11c \uc81c\uacf5\ud558\ub294 \uc778\ub371\uc2a4 \ubaa8\ub2c8\ud130\ub9c1 \uae30\ub2a5\uc744 \uc0ac\uc6a9\ud558\uc5ec \uad6c\ud604\ud560 \uc218 \uc788\ub2e4.
    \n<\/span><\/p>\n


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

  7. \n

    \uc778\ub371\uc2a4 \ubaa8\ub2c8\ud130\ub9c1 \ubc29\ubc95
    \n<\/span><\/h2>\n<\/li>\n<\/ol>\n
      \n
    • \uc6b0\uc120 \uc804\uccb4 \uc778\ub371\uc2a4\ub97c \ud30c\uc545\ud574\uc11c \uc720\uc800\uac00 \uc0dd\uc131\ud55c \uc778\ub371\uc2a4 \ub9cc\uc744 \ucc3e\uc544\uc11c \ubaa8\ub2c8\ud130\ub9c1\uc744 \uc124\uc815\ud55c\ub2e4.
      \n<\/span><\/li>\n
    • \ub2e4\uc74c\uc740 \ubaa8\ub2c8\ud130\ub9c1 \ub300\uc0c1\uc744 \ucd94\ucd9c\ud558\uc5ec, \ubaa8\ub2c8\ud130\ub9c1\uc744 \uc218\ud589\ud558\ub294 \uc2a4\ud06c\ub9bd\ud2b8\ub97c \uc791\uc131\ud55c\ub2e4.
      \n<\/span><\/li>\n
    • \ub3c4\uba54\uc778 \uc778\ub371\uc2a4\ub098 LOB \ud0c0\uc785 \uc778\ub371\uc2a4\ub294 \ubaa8\ub2c8\ud130\ub9c1\uc744 \ud560 \uc218 \uc5c6\ub2e4.
      \n<\/span><\/li>\n
    • SYS \uc720\uc800\ub85c \uc218\ud589\ud55c\ub2e4.
      \n<\/span><\/li>\n<\/ul>\n


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

      \n\n\n<\/colgroup>\n\n\n
      \n


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

      — create_index_monitoring.sql————————————————————
      \n<\/span><\/p>\n


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

      set feedback off
      \n<\/span><\/p>\n

      set heading off
      \n<\/span><\/p>\n

      set lines 120
      \n<\/span><\/p>\n

      set pages 9999
      \n<\/span><\/p>\n


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

      spool index_monitoring.sql
      \n<\/span><\/p>\n


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

      SELECT ‘ALTER INDEX ‘ || owner || ‘.’ || index_name || ‘ MONITORING USAGE;’
      \n<\/span><\/p>\n

      FROM DBA_INDEXES
      \n<\/span><\/p>\n

      WHERE OWNER NOT IN (‘SYS’, ‘SYSTEM’, ‘DBSNMP’, ‘CTXSYS’, ‘OLAPSYS’, ‘DRSYS’, ‘MDSYS’, ‘XDB’,
      \n<\/span><\/p>\n

      ‘WMSYS’, ‘ORDSYS’, ‘ODM’, ‘WKSYS’, ‘OUTLN’, ‘SYSMAN’, ‘EXFSYS’, ‘DMSYS’, ‘RMAN’)
      \n<\/span><\/p>\n

      AND INDEX_TYPE NOT IN (‘DOMAIN’, ‘LOB’);
      \n<\/span><\/p>\n


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

      spool off
      \n<\/span><\/p>\n


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

      set heading on
      \n<\/span><\/p>\n

      set feedback on
      \n<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n


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

        \n
      • \ub2e4\uc74c\uacfc \uac19\uc774 v$object_usage \ub97c \uc870\ud68c\ud558\uba74 \uc544\ubb34\uac83\ub3c4 \ub098\uc624\uc9c0 \uc54a\ub294\ub2e4.
        \n<\/span><\/li>\n<\/ul>\n


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

        \n\n\n<\/colgroup>\n\n\n
        \n

        SQL> desc v$object_usage
        \n<\/span><\/p>\n

        Name Null? Type
        \n<\/span><\/p>\n

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

        INDEX_NAME NOT NULL VARCHAR2(30)
        \n<\/span><\/p>\n

        TABLE_NAME NOT NULL VARCHAR2(30)
        \n<\/span><\/p>\n

        MONITORING VARCHAR2(3)
        \n<\/span><\/p>\n

        USED VARCHAR2(3)
        \n<\/span><\/p>\n

        START_MONITORING VARCHAR2(19)
        \n<\/span><\/p>\n

        END_MONITORING VARCHAR2(19)
        \n<\/span><\/p>\n


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

        set lines 110
        \n<\/span><\/p>\n

        set pages 999
        \n<\/span><\/p>\n

        column index_name format a20
        \n<\/span><\/p>\n

        column monitoring format a10
        \n<\/span><\/p>\n

        column used format a10
        \n<\/span><\/p>\n

        column start_monitoring format a20
        \n<\/span><\/p>\n

        column end_monitoring format a20
        \n<\/span><\/p>\n

        SELECT index_name, monitoring, used, start_monitoring, end_monitoring
        \n<\/span><\/p>\n

        FROM v$object_usage;
        \n<\/span><\/p>\n


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

        no rows selected.
        \n<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n


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

          \n
        • \uc704 \ucffc\ub9ac\uc5d0\uc11c \ubaa8\ub2c8\ud130\ub9c1\ud55c \uc778\ub371\uc2a4\ub4e4\uc774 \uc870\ud68c\uac00 \ub418\uc9c0 \uc54a\ub294 \uc774\uc720\ub294 V$OBJECT_USAGE \uac00 \uc790\uae30 \uc790\uc2e0\uc758 \ubcf4\uc720\ud55c \uc624\ube0c\uc81d\ud2b8\uc5d0 \ub300\ud574\uc11c\ub9cc \ubcf4\uc5ec\uc8fc\uae30 \ub54c\ubb38\uc774\ub2e4. \uc5ec\uae30\uc11c\ub294 SYS \uc720\uc800\ub85c \ucffc\ub9ac\ub97c \uc2e4\ud589\ud558\uace0, SYS\uc5d0 \uad00\ub828\ub41c \uc778\ub371\uc2a4\uc5d0 \ub300\ud574\uc11c\ub294 \ubaa8\ub2c8\ud130\ub9c1 \ud558\uc9c0 \uc54a\uc558\uae30 \ub54c\ubb38\uc5d0 \uc544\ubb34\uac83\ub3c4 \ub098\ud0c0\ub098\uc9c0 \uc54a\ub294\ub2e4.
          \n<\/span><\/li>\n
        • V$OBJECT_USAGE \ub515\uc154\ub108\ub9ac \ubdf0\uc5d0 \ud574\ub2f9\ud558\ub294 \uc2e4\uc81c \ub370\uc774\ud130\ub294 OBJECT_USAGE\uc5d0 \ud3ec\ud568\ub418\uc5b4 \uc788\ub2e4.
          \n<\/span><\/li>\n<\/ul>\n


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

          \n\n\n<\/colgroup>\n\n\n
          \n


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

          SQL> desc object_usage
          \n<\/span><\/p>\n


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

          Name Null? Type
          \n<\/span><\/p>\n

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

          OBJ# NOT NULL NUMBER
          \n<\/span><\/p>\n

          FLAGS NOT NULL NUMBER
          \n<\/span><\/p>\n

          START_MONITORING VARCHAR2(19)
          \n<\/span><\/p>\n

          END_MONITORING VARCHAR2(19)
          \n<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n


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

          \n\n\n<\/colgroup>\n\n\n
          \n

          set lines 140
          \n<\/span><\/p>\n

          set pages 9999
          \n<\/span><\/p>\n

          col owner format a10
          \n<\/span><\/p>\n

          col index_name format a35
          \n<\/span><\/p>\n

          col table_name format a30
          \n<\/span><\/p>\n

          col monitoring format a10
          \n<\/span><\/p>\n

          col start_monitoring format a20
          \n<\/span><\/p>\n

          col end_monitoring format a20
          \n<\/span><\/p>\n


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

          spool report_index_usage.txt
          \n<\/span><\/p>\n

          select username as owner,
          \n<\/span><\/p>\n

          io.name as index_name,
          \n<\/span><\/p>\n

          t.name as table_name,
          \n<\/span><\/p>\n

          decode(bitand(i.flags, 65536), 0, ‘NO’, ‘YES’) as monitoring,
          \n<\/span><\/p>\n

          decode(bitand(ou.flags, 1), 0, ‘NO’, ‘YES’) as used,
          \n<\/span><\/p>\n

          ou.start_monitoring,
          \n<\/span><\/p>\n

          ou.end_monitoring
          \n<\/span><\/p>\n

          from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou, dba_users u
          \n<\/span><\/p>\n

          where i.obj# = ou.obj#
          \n<\/span><\/p>\n

          and io.obj# = ou.obj#
          \n<\/span><\/p>\n

          and t.obj# = i.bo#
          \n<\/span><\/p>\n

          and user_id=t.owner#;
          \n<\/span><\/p>\n

          spool off;<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

          *<\/span> <\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

            \uac1c\uc694   \ub370\uc774\ud130\ubca0\uc774\uc2a4\ub97c \uc6b4\uc601 \uc911\uc5d0 \uc0c8\ub85c\uc6b4 \uc778\ub371\uc2a4\ub97c \uc0dd\uc131\ud558\uac70\ub098, \uae30\uc874\uc758 \uc778\ub371\uc2a4 \ubcc0\uacbd\ub428\uc73c\ub85c\uc368 \uc601\ud5a5\uc744 \ubc1b\uc544 \uc0ac\uc6a9\ub418\uc9c0 \uc54a\ub294 \uc778\ub371\uc2a4\uac00 \uc0dd\uaca8\ub0a0 \uc218 \uc788\ub2e4. \ub530\ub77c\uc11c \uadf8\ub7ec\ud55c \uc778\ub371\uc2a4\ub97c \ucc3e\uc544 \uc218\uc815\ud558\uac70\ub098 \uc81c\uac70\ud560 \ud544\uc694\uc131\uc774 \uc81c\uae30\ub41c\ub2e4. \uc2e4\uc81c\ub85c \ud29c\ub2dd\uc791\uc5c5 \uc774\ud6c4 \uae30\uc874\uc5d0 \uc788\ub358 \uc778\ub371\uc2a4\ub97c \uc81c\uac70\ud558\ub824\ub294 \uacbd\uc6b0, \uc2e4\uc81c\ub85c \uc5b4\ub5a4 \uc778\ub371\uc2a4\ub97c \uc81c\uac70\ud574\uc57c \ud560\uc9c0 \uacb0\uc815\ud558\uae30\ub780 \uc27d\uc9c0 \uc54a\ub2e4. \ud2b9\uc815 \uae30\uac04\ub3d9\uc548\uc758 \ucffc\ub9ac\uc5d0 \ub300\ud55c \uc5d1\uc138\uc2a4 \uc720\ud615\uc744 \ubd84\uc11d\ud558\uace0, \uc778\ub371\uc2a4\uc758 \uc0ac\uc6a9\uc720\ubb34\ub97c \ubaa8\ub2c8\ud130\ub9c1\ud55c \uc774\ud6c4\uc5d0 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"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":[11],"tags":[734,163,735,227],"jetpack_featured_media_url":"","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1303"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1303"}],"version-history":[{"count":0,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1303\/revisions"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1303"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1303"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1303"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}