{"id":645,"date":"2015-11-06T08:54:59","date_gmt":"2015-11-06T08:54:59","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=645"},"modified":"2015-11-06T08:54:59","modified_gmt":"2015-11-06T08:54:59","slug":"oracle-buffer-cache","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=645","title":{"rendered":"ORACLE BUFFER CACHE"},"content":{"rendered":"

\n <\/p>\n

    \n
  1. \n
     <\/div>\n<\/li>\n
  2. \n
    \n

    Buffer Cache Management
    \n<\/span><\/h1>\n<\/div>\n

    \n <\/p>\n

    Oracle\uc740 cache buffer\ub97c \uad00\ub9ac\ud558\uae30 \uc704\ud574\uc11c\ub294 \uc138\uac00\uc9c0\uc758 \ub0b4\ubd80\uc801\uc778 structure\ub97c \uc0ac\uc6a9\ud558\ub294\ub370, \uadf8 \uac01\uac01\uc740 cache buffer chain, dirty list, LRU(Least Recently Used list)\uc774\uba70, \uc544\ub798\uc5d0 \uc790\uc138\ud788 \uc124\uba85\ud558\ub2e4. buffer cache management\uc758 \uae30\ubcf8\uc744 \uc774\ub8e8\ub294 \uc774 \uc138\uac00\uc9c0 list\ub97c \uad00\ub9ac\ud558\uba74\uc11c \uc0ac\uc6a9\uc790\uc5d0\uac8c \ud544\uc694\ud55c buffer\ub97c \uc0ac\uc6a9\uac00\ub2a5\ud558\ub3c4\ub85d \uc81c\uacf5\ud558\uc5ec \uc8fc\ub294 \uc5ed\ud560\uc744 \ud558\ub294 \uac83\uc740 DBWR\uc774\ub2e4. \ub610\ud55c DBWR\uc740 startup\uc2dc \uac01 online datafile\uc5d0 \ub300\ud574\uc11c Media Recovery (MR) lock\uc744 \ud68d\ub4dd\ud558\ub294 \ub4f1 database file\uc758 \uad00\ub9ac\uc790\ub85c\ub3c4 \uac04\uc8fc\ub41c\ub2e4.
    \n<\/span><\/p>\n

    \uc124\uba85 \uc911\uc5d0 \ud3ec\ud568\ub41c \uc5ec\ub7ec\uac00\uc9c0 buffer cache\uc640 \uad00\ub828\ub41c parameter \ubc0f \ub2e4\ub978 \uac12\ub4e4\uc740 sys.X$KVII\uc640 sys.X$KVIT\uc5d0\uc11c \ud655\uc778\uac00\ub2a5\ud558\uba70, \uc774 table\ub4e4\uc758 \ubaa8\ub4e0 \ub0b4\uc6a9\uc740 \uc774 \ubb38\uc11c\uc758 \ub05d\uc5d0 \ucca8\ubd80\ud558\uc600\ub2e4. \ub610\ud55c hidden parameter\uc758 \uc124\uba85 \ubc0f \uc9c0\uc815\ub41c \uac12\uc744 \ud655\uc778\ud558\ub294 \ubc29\ubc95\ub3c4 \ucca8\ubd80\ud558\uc600\ub2e4.
    \n<\/span><\/p>\n

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

  3. \n
    \n

    Cache Buffer Chain
    \n<\/span><\/h2>\n<\/div>\n

    cache buffer chain list\ub294 hashed chain list\ub77c\uace0\ub3c4 \ud558\uba70, doubly-linked hash list\ub85c \uc5f0\uacb0\ub41c hash table (\ub610\ub294 hash bucket)\ub85c \uad6c\uc131\ub418\uc5b4\uc9c4\ub2e4. \uc774 has bucket\uc740 instance startup\uc2dc\uc5d0 \ud560\ub2f9\ub418\uba70, \uc2e4\uc81c buffer block\uc73c\ub85c \uad6c\uc131\ub41c \uac83\uc740 \uc544\ub2c8\uace0 buffer header\ub9cc\uc744 \ud3ec\ud568\ud558\uac8c \ub41c\ub2e4. hash bucket\uc758 \uac2f\uc218\ub294 \uae30\ubcf8\uc801\uc73c\ub85c db_block_buffers\/4\ubcf4\ub2e4 \ud070 \ucd5c\uc18c\uc758 \uc18c\uc218(prime number)\uac00 \ub418\uba70, init.ora file\uc5d0 \uba85\uc2dc\uc801\uc73c\ub85c _db_block_hash_buckets parameter\uc5d0 \uc758\ud574 \uc9c0\uc815\ud560 \uc218 \uc788\ub2e4. \uc774 cache buffer chain\uc5d0 \uc874\uc7ac\ud558\ub294 buffer\ub4e4\uc740 \ub4a4\uc5d0\uc11c \uc124\uba85\ud560 LRU list\ub098 LRUW list (dirty list) \uc911\uc758 \ud558\ub098\uc5d0 \uc704\uce58\ud558\uac8c \ub418\uba70, \ub450 list \ubaa8\ub450\uc5d0 \ud3ec\ud568\ub418\uc9c0\ub294 \uc54a\ub294\ub2e4.
    \n<\/span><\/p>\n

    buffer\ub4e4\uc740 data block address(DBA)\uc5d0 \uc758\ud574\uc11chash\ub418\uc5b4 hash table\uc5d0 \ud560\ub2f9\ub41c\ub2e4. buffer header\uc640 buffer block\uc740 \uc77c\ub300\uc77c \ub300\uc751\ub418\uba70, buffer header\uc5d0\ub294 \ub2e4\uc74c\uacfc \uac19\uc740 \uc815\ubcf4\ub4e4\uc774 \ub098\ud0c0\ub09c\ub2e4. \uc5ec\uae30\uc5d0\uc11c \uad04\ud638\uc548\uc5d0 \uc801\uc740 \uc57d\uc790\ub4e4\uc740 buffer dump\ud6c4 trace file\uc5d0 \ub098\ud0c0\ub098\ub294 \ud615\ud0dc\uc774\ub2e4.
    \n<\/span><\/p>\n

    \n <\/p>\n<\/li>\n<\/ol>\n

      \n
    • \n
      buffer status (st): xcurrent, CR, reading
      \n<\/span><\/div>\n<\/li>\n
    • \n
      lock mode (md): exclusive (excl), shared (shr), null (null)
      \n<\/span><\/div>\n<\/li>\n
    • \n
      buffer holder\uc640 waiters list
      \n<\/span><\/div>\n<\/li>\n
    • \n
      hash chain list\uc5d0\uc11c\uc758 \uc704\uce58, LRU\uc640 LRUW list\uc0c1\uc5d0\uc11c\uc758 buffer header\uc758 \uc704\uce58\ub97c \uac00\ub9ac\ud0a4\ub294 pointer\uc640 \uc2e4\uc81c buffer block\uc744 \uac00\ub9ac\ud0a4\ub294 pointer(ba)
      \n<\/span><\/div>\n<\/li>\n
    • \n
      buffer\uc5d0 \uc218\ud589\ub420 \uc5f0\uc0b0\ub4e4, recovery\uc815\ubcf4 \ub4f1\uc744 \ub098\ud0c0\ub0b4\ub294 flags
      \n<\/span><\/div>\n<\/li>\n
    • \n
      \uadf8 \uc678\uc5d0 dba, inc&seq, version, block type
      \n<\/span><\/div>\n<\/li>\n<\/ul>\n

      \n <\/p>\n

       <\/p>\n


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


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

      \n <\/p>\n

      [\uadf8\ub9bc 1]
      \n<\/strong><\/span><\/p>\n

        \n
      1. \n
        \n

        LRU list
        \n<\/span><\/h2>\n<\/div>\n

        least recently used list \ud639\uc740 replacement list\ub77c \ubd88\ub9ac\ub294 \uac83\uc73c\ub85c, \uc774 LRU list\uc758 head\ubd80\ubd84\uc5d0\ub294 \uac00\uc7a5 \ucd5c\uadfc\uc5d0 \uc0ac\uc6a9\ub41c MRU buffer\ub4e4\uc744 \ud3ec\ud568\ud55c\ub2e4. \ud2b9\ubcc4\ud55c \uacbd\uc6b0\ub97c \uc81c\uc678\ud558\uace0\ub294, \ubaa8\ub4e0 \uc0c8\ub85c\uc6b4 block\ub4e4\uc740 \ubaa8\ub450 MRU end\uc5d0 \uc704\uce58\ud558\uba70, LRU\uc758 \ub05d\ubd80\ubd84\uc740 \ucd5c\uadfc\uc5d0 \uc0ac\uc6a9\ub418\uc9c0 \uc54a\uc740, \uace7 \uc7ac\uc0ac\uc6a9\ub420 buffer\ub4e4\uc774 \uc704\uce58\ud55c\ub2e4. \uadf8\ub7ec\ubbc0\ub85c foreground process\ub294 \ube48 buffer\ub97c \uc5bb\uae30 \uc704\ud574 LRU\uc758 \ub05d\ubd80\ubd84\ubd80\ud130 \ucc3e\uae30 \uc2dc\uc791\ud55c\ub2e4. \uc774 LRU\uc758 buffer\ub4e4\uc740 free, pinned, dirty \uc138\uac00\uc9c0 \uc911 \ud558\ub098\ub97c \uac00\uc9c0\uba70 \uac01\uac01\uc740 \ub2e4\uc74c\uacfc \uac19\uc740 \ud2b9\uc131\uc744 \uac00\uc9c4\ub2e4.
        \n<\/span><\/p>\n<\/li>\n<\/ol>\n

          \n
        • \n
          pinned buffer<\/strong>: \ud604\uc7ac user\uc5d0 \uc758\ud574 \uc0ac\uc6a9\uc911\uc774\uc5b4\uc11c, \uc7ac\uc0ac\uc6a9\ub420 \uc218 \uc5c6\ub294 \uc0c1\ud0dc\uc758 buffer\uc774\uba70, pinned clean \ud639\uc740 pinned dirty\ub85c \ub2e4\uc2dc \ub098\ub258\uc5b4\uc9c8 \uc218 \uc788\ub2e4.
          \n<\/span><\/div>\n<\/li>\n
        • \n
          free buffer<\/strong>: \uc0ac\uc6a9\ub418\uc9c0 \uc54a\uc740 buffer, \uc989 disk block\uc774 \uc77d\ud600\uc838\uc11c \ud560\ub2f9\ub418\uc5b4 \uc0ac\uc6a9\ub420 buffer\uc774\ub2e4.
          \n<\/span><\/div>\n<\/li>\n
        • \n
          dirty buffer<\/strong>: dirty buffer\ub294 pinned dirty buffer\uc640 \ub9c8\ucc2c\uac00\uc9c0\ub85c user\uac00 \uc0ac\uc6a9\ud558\uc5ec \ub0b4\uc6a9\uc774 \ubcc0\uacbd\ub41c buffer\uc774\ub2e4. \uadf8\ub7ec\ub098 pinned buffer\uac00 \ud604\uc7ac \uc0ac\uc6a9\uc911\uc774\uc11c \uc7ac\uc0ac\uc6a9\ub420 \uc218 \uc5c6\ub294 \ubc18\uba74\uc5d0, dirty buffer\ub294 \ud604\uc7ac \uc0ac\uc6a9\uc911\uc778 user\ub098 waiter\ub294 \uc5c6\uae30 \ub54c\ubb38\uc5d0 LRUW list\ub85c \uc62e\uaca8\uc9c8 \uc218 \uc788\uace0, \uacb0\uad6d\uc740 disk\ub85c write\ub420 buffer\uc774\ub2e4.
          \n<\/span><\/div>\n<\/li>\n<\/ul>\n

          \n <\/p>\n

          \uc704\uc5d0\uc11c \uc5b8\uae09\ud55c \uc0c8\ub85c \uc77d\uc5b4 \ub4e4\uc778 block\uc911 MRU end\ubd80\ubd84\uc5d0 \uc704\uce58\ud558\uc9c0 \uc54a\ub294 \ud2b9\ubcc4\ud55c \uacbd\uc6b0\ub780, \ubc14\ub85c full table scan\uc774\ub2e4. full table scan\uc73c\ub85c \uc77d\uc740 table\uc740 LRU list\uc758 \ub05d\ubd80\ubd84\uc5d0 \uc704\uce58\ud558\uace0 \uadf8 \ud06c\uae30\ub3c4 db_block_multi_read_count\ub9cc\ud07c\uc758 buffer\uc5d0\ub9cc \uc77d\ud600\uc9c4\ub2e4. \uc774\ub807\uac8c LRU end\uc5d0 \uc704\uce58\uc2dc\ud0a4\ub294 \uc774\uc720\ub294 full table scan\uc73c\ub85c \uc77d\uc740 block\uc740 \ub2e4\uc2dc access\ud560 \ud655\ub960\uc774 \uc801\uc5b4\uc11c \uace7 \ub2e4\uc2dc \uc7ac\uc0ac\uc6a9\ub420 \uc218 \uc788\ub3c4\ub85d \ud55c \uac83\uc774\uba70, db_block_multi_read_count\ub85c \uc81c\ud55c\ud55c \uc774\uc720\ub294 full table scan\uc73c\ub85c \uc77d\ud78c table\uc774 \uc804\uccb4 LRU list\uc758 \ub300\ubd80\ubd84\uc744 \uc0ac\uc6a9\ud558\uac8c \ub418\ub294 \uac83\uc744 \ub9c9\uae30 \uc704\ud55c \uac83\uc774\ub2e4.
          \n<\/span><\/p>\n

          \uadf8\ub7ec\ub098 full table scan\uc758 \uacbd\uc6b0\uc5d0\ub3c4 \ub9e4\uc6b0 \uc911\uc694\ud558\uace0 \uc790\uc8fc \uc0ac\uc6a9\ub418\uc5b4 MRU end\uc5d0 \uc804\uccb4 table\uc758 \ub0b4\uc6a9\uc744 \ubaa8\ub450 cache\uc2dc\ud0a4\uace0 \uc2f6\uc740 \uacbd\uc6b0\ub3c4 \uc788\uc744 \uc218 \uc788\ub2e4. \uc774\ub7ec\ud55c \uacbd\uc6b0\uc5d0\ub294 \ub2e4\uc74c\uacfc \uac19\uc774 CACHE\uc744 \uc0ac\uc6a9\ud558\uba74 \ub41c\ub2e4.
          \n<\/span><\/p>\n

          \n <\/p>\n

          SQL> alter table dept cache;
          \n<\/span><\/p>\n

          SQL> select \/*+ cache(a) *\/ * from emp a;
          \n<\/span><\/p>\n

          \n <\/p>\n

          \uc774\ub7ec\ud55c CACHE\uc808\uc740 \uc791\uc740 table\uc5d0\ub9cc \uc0ac\uc6a9\ud558\ub294 \uac83\uc774 \ubc14\ub78c\uc9c1\ud558\uba70, \ud070 table\uc5d0 \uc0ac\uc6a9\ud558\uac8c \ub418\uba74 buffer\uc758 MRU end\ucabd\uc758 \ub300\ubd80\ubd84\uc758 buffer\ub97c \uc774 \ud558\ub098\uc758 table\uc774 \ucc28\uc9c0\ud558\uac8c \ub418\ub294 \ud604\uc0c1\uc774 \ubc1c\uc0dd\uac00\ub2a5\ud558\ub2e4. \uadf8\ub9ac\uace0 \uc774 CACHE\uc808\ub85c MRU end\uc5d0 \uc704\uce58\ud55c table\ub3c4 \uc774\ud6c4 \ub2e4\ub978 TABLE\uc774 \uacc4\uc18d \uc0ac\uc6a9\ub418\uc5b4\uc9d0\uc5d0 \ub530\ub77c LRU end\ucabd\uc73c\ub85c \uc810\ucc28 \uc774\ub3d9\ud558\ub2e4 disk\ub85c write\ub418\uace0 memory\uc5d0\uc11c\ub294 \uc0ac\ub77c\uc9c8 \uc218 \uc788\ub2e4. \uc989 \uc774\uac83\uc744 \uc0ac\uc6a9\ud55c\ub2e4\ud574\uc11c \uc2e4\uc81c table\uc758 \ub0b4\uc6a9\uc744 \uacc4\uc18d\ud574\uc11c cache\uc2dc\ud0a4\ub294 \uac83\uc744 \ubcf4\uc7a5\ud558\ub294 \uac83\uc740 \uc544\ub2c8\ub2e4.
          \n<\/span><\/p>\n

          [\ucc38\uace0]<\/strong> Oracle7.2 version\uae4c\uc9c0\ub294 _small_table_threshold\ub77c\ub294 parameter\uac00 \uc874\uc7ac\ud558\uc5ec, \uc5ec\uae30\uc5d0\uc11c \uc9c0\uc815\ub41c \ud06c\uae30\ubcf4\ub2e4 \uc791\uc740 table\uc740 full table scan\uc758 \uacbd\uc6b0\ub77c\ub3c4 MRU end\uc5d0 \uc704\uce58\ud558\uac8c \ub41c\ub2e4. \uc774 \uac12\uc740 default\ub85c max(4, db_block_buffers\/50)\uc744 \uac16\ub294\ub2e4.
          \n<\/em><\/span><\/p>\n

          \n <\/p>\n

            \n
          1. \n
            \n

            LRUW list
            \n<\/span><\/h2>\n<\/div>\n

            dirty list\ub77c\uace0\ub3c4 \ubd88\ub9ac\uba70, DBWR\ub294 \uc774 list\uc758 buffer\uc758 \ub0b4\uc6a9\uc744 disk\uc5d0 write\ud558\uc5ec \ube48 buffer\ub85c \ub9cc\ub4e0\ub2e4. \uc5b4\ub5bb\uac8c buffer\uac00 LRUW list\ub85c \uc62e\uaca8\uc9c0\uace0 \uacb0\uad6d\uc740 disk\uc5d0 write\ub418\ub294\uc9c0\uac00 DBWR\uc758 \uae30\ub2a5\uc758 \uae30\ubcf8\uc774\ub77c \ud560 \uc218 \uc788\ub2e4. DBWR\uac00 buffer\ub97c disk\uc5d0 \ubcf4\ub0b4\ub294 \uac83\uc740 \uc544\ub798 query\uc5d0\uc11c \ub098\ud0c0\ub09c signal\uc744 \ubc1b\ub294 \uacbd\uc6b0\uc774\ub2e4.
            \n<\/span><\/p>\n

            \n <\/p>\n

            SVRMGR> select * from x$messages where indx in (‘9′,’10’,’11’);
            \n<\/span><\/p>\n

            \n <\/p>\n

            ADDR INDX DESCRIPTION                     DEST
            \n<\/span><\/p>\n

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

            100F42FC 9     write dirty buffers when idle – timeout action     DBWR
            \n<\/span><\/p>\n

            100F430C 10     write dirty buffers\/find clean buffers         DBWR
            \n<\/span><\/p>\n

            100F431C 11     write checkpoint-needed buffers\/recovery end     DBWR
            \n<\/span><\/p>\n

            \n <\/p>\n

            \uc774 \uac01\uac01\uc758 signal \uacbd\uc6b0\uc5d0 \ub300\ud574\uc11c \uc544\ub798\uc5d0 \uc790\uc138\ud788 \uc124\uba85\ud55c\ub2e4.
            \n<\/span><\/p>\n

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

          2. \n
            \n

            DBWR write dirty buffers\/find clean buffers (indx :10)
            \n<\/span><\/h3>\n<\/div>\n

            foreground process\uac00 buffer cache\uc5d0 \uc874\uc7ac\ud558\uc9c0 \uc54a\ub294 block\uc744 disk\uc5d0 \uc77d\uae30 \uc704\ud574\uc11c\ub294 buffer cache \ub0b4\uc5d0 free buffer\uac00 \uc874\uc7ac\ud558\uc5ec\uc57c \ud55c\ub2e4. \uc774 free buffer\ub97c \ucc3e\uae30 \uc704\ud55c \uac04\ub2e8\ud788 \uc2dc\ub098\ub9ac\uc624\ub294 \ub2e4\uc74c\uacfc \uac19\ub2e4.
            \n<\/span><\/p>\n

              \n
            1. \n
              foreground process\uac00 LRU list\uc5d0 lock\uc744 \uac78\uace0 LRU\uc758 \ub05d\ubd80\ubd84\ubd80\ud130 \ucc3e\uae30 \uc2dc\uc791\ud55c\ub2e4.
              \n<\/span><\/div>\n<\/li>\n
            2. \n
              \ucc3e\ub294 \uc911\uc5d0 dirty buffer\ub97c \ub9cc\ub098\uba74, \uc774 dirty buffer\ub97c LRUW list\uc5d0 \uc62e\uae34\ub2e4.
              \n<\/span><\/div>\n

              _db_block_max_scan_count\uac12\uc5d0 \uc758\ud574 \uacb0\uc815\ub418\ub294 foreground scan depth<\/em> \uac2f\uc218\uc758 buffer\ub0b4\uc5d0\uc11c free buffer\ub97c \ucc3e\uc9c0 \ubabb\ud558\uba74, foreground process\ub294 \ub354 \uc774\uc0c1 LRU list\ub97c \uc77d\uc9c0 \uc54a\uac8c \ub41c\ub2e4.
              \n<\/span><\/p>\n<\/li>\n

            3. \n
              DBWR\ub85c \ud558\uc5ec\uae08 message\ub97c \ubcf4\ub0b4\ub294 \uc791\uc5c5\uc774 \uc218\ud589\ub418\uace0, LRU latch\ub294 \ud574\uc81c\ub41c\ub2e4.
              \n<\/span><\/div>\n<\/li>\n
            4. \n
              DBWR\uc5d0\uac8c \ubcf4\ub0b4\uc9c4 message\ub294 DBWR\uc5d0\uac8c LRU\uc758 \uaf2c\ub9ac \ubd80\ubd84\uc5d0 \uc704\uce58\ud55c buffer\ub4e4\uc744 \ube44\uc6b0\ub294 large batch write<\/em>\ub97c \uc218\ud589\ud558\ub3c4\ub85d \ud55c\ub2e4.
              \n<\/span><\/div>\n<\/li>\n
            5. \n
              DBWR\uc740 LRU latch\ub97c \uc7a1\uace0, LRU list\ub97c \uc77d\uc73c\uba74\uc11c disk\uc5d0 write\uc2dc\ud0ac dirty buffer\ub4e4\uc744 \ubaa8\uc740\ub2e4. \uc774\ub54c DBWR\uc774 \uc77d\ub294 buffer\uc758 \uac2f\uc218\ub97c DBWR scan depth<\/em>\ub77c\uace0 \ud558\uba70, \uc774 \uac12\uc740 \ub4a4\uc5d0\uc11c \uc790\uc138\ud788 \uc124\uba85\ud55c\ub2e4.
              \n<\/span><\/div>\n<\/li>\n<\/ol>\n

              \uc704\uc758 \uc2dc\ub098\ub9ac\uc624 \uc911, \ub9cc\uc57d foreground process\uac00 LRU\uc5d0\uc11c dirty buffer\ub97c \ucc3e\uc740 \ud6c4 LRUW list\uc5d0 \uc62e\uae30\ub824\uace0 \ud560 \ub54c, LRUW list\uac00 full\uc774 \ub418\uc5b4 \uc62e\uae38 \uc218 \uc5c6\uc744 \uc218 \uc5c6\ub294 \uacbd\uc6b0\uac00 \ubc1c\uc0dd\ud560 \uc218 \uc788\ub2e4. \uc5ec\uae30\uc5d0\uc11c LRUW list\uac00 full\uc774\ub77c\ub294 \uac83\uc740 _db_large_dirty_queue\ub098 2*_db_block_write_batch (_db_large_dirty_queue\uac00 \uc9c0\uc815\ub418\uc9c0 \uc54a\uc740 \uacbd\uc6b0)\uc5d0 \uc758\ud574\uc11c \uc815\ud574\uc9c0\ub294 max dirty queue<\/em> \uac12\uc5d0 \uc758\ud574 \uacb0\uc815\ub41c\ub2e4. \uc774\ub807\uac8c LRUW list\uac00 \ub354 \uc774\uc0c1\uc758 dirty buffer\ub97c \ud3ec\ud568\ud560 \uc218 \uc5c6\uac8c \ub418\uba74, DBWR\ub294 large batch write <\/em>\ub9cc\ud07c\uc758 buffer\ub97c \ube44\uc6b0\ub3c4\ub85d \uc2e0\ud638\ub97c \ubc1b\uac8c \ub41c\ub2e4. \uc774\ub7ec\ud55c \uc0c1\ud0dc\ub294 \ub9e4\uc6b0 \uc2ec\uac01\ud558\uac8c \uac04\uc8fc\ub418\uc5b4, DBWR\ub294 LRUW \uc640 LRU list\ub97c \ube44\uc6b0\ub294\ub370 \uc804\ub150\ud558\uac8c \ub418\uace0, foreground process\ub4e4\uc740 LRU list\uc5d0 \uc77d\uac70\ub098 \uc4f0\ub294 \uac83\uc774 \uae08\uc9c0\ub41c\ub2e4. \uc774\ub7ec\ud55c \uc0c1\ud669\uc740 OS\uc5d0\uc11c \ucd5c\uc18c\ud55c\uc758 limit\uc774\ud558\ub85c free page\uc758 \uac2f\uc218\uac00 \uc904\uc5b4\ub4dc\ub294 \uacbd\uc6b0 demanded paging\uc774 \ubc1c\uc0dd\ud558\ub294 \uac83\uacfc \uc720\uc0ac\ud558\ub2e4.
              \n<\/span><\/p>\n

              \uc704\uc5d0\uc11c \uc124\uba85\ud55c \uac83\uacfc \uac19\uc774 DBWR\ub294 batch write (\ub610\ub294 IO clumps)\ub97c \uc218\ud589\ud558\ub294\ub370 \uc774 \ucd5c\ub300\uac12\uc744 max write batch<\/em>\ub77c\uace0 \ud558\uba70 _db_block_write_batch\uac12\uc5d0 \uc758\ud574\uc11c \uacb0\uc815\ub418\uc5c8\ub2e4. \uadf8\ub7ec\ub098 tuning\uc774 \uc798\ub41c \uc2dc\uc2a4\ud15c\uc5d0\uc11c\ub294 \uc774 \uac12\uc774 \ub354 \uc791\uc740 \uac83\uc774 \ubc14\ub78c\uc9c1\ud55c \uacbd\uc6b0\ub3c4 \uc788\uc5b4\uc11c Oracle 7.2\ubd80\ud130\ub294 \uc774 parameter\uc758 \uac12\uc758 \uae30\ubcf8\uac12\uc744 0\uc73c\ub85c \ud558\uc600\uc73c\uba70, min(1\/2*db_block_simultaneous_writes (default 4)*db_files, max_batch_size<\/em>, db_block_buffers\/4)\uc758 \uac12\uc5d0 \uc758\ud574 \ub3d9\uc801\uc73c\ub85c \uc815\ud574\uc9c0\uac8c \ub418\uc5c8\ub2e4. \uc774\ub54c max_batch_size<\/em>\ub780 _db_block_write_batch\ub97c \uba85\uc2dc\uc801\uc73c\ub85c \uc124\uc815\ud55c \uacbd\uc6b0\uc758 \uac12\uc774\ub2e4. \uc774 _db_block_write_batch\ub294 max batch write\uc640 \uac19\uc740 \uc758\ubbf8\ub85c \uc0ac\uc6a9\ub418\uba70 DBWR buffer scan depth \ub4f1\uacfc \uac19\uc774 \ub2e4\ub978 \uc791\uc5c5\uc5d0\ub3c4 \uc601\ud5a5\uc744 \uc8fc\uae30 \ub54c\ubb38\uc5d0 \uc9c1\uc811 \ubcc0\uacbd\ud558\ub294 \uac83\uc740 \uad8c\uc7a5\ub418\uc9c0 \uc54a\uc73c\uba70, \ud604\uc7ac \uc2dc\uc2a4\ud15c\uc758 \ub3d9\uc801\uc73c\ub85c \ud560\ub2f9\ub41c \uac12\uc740 \ub2e4\uc74c\uacfc \uac19\uc774 x$kvii table\uc744 \ud1b5\ud574 \uc870\ud68c\ud560 \uc218 \uc788\ub2e4. (\ub2e8 underbar parameter\uac00 \uc815\ubcf4\uac00 \uc788\ub294 x$ksppsv\uc5d0\ub294 \uc0ac\uc6a9\uc790\uac00 \uc9c1\uc811 \uc218\uc815\ud55c \uac83\uc774 \uc544\ub2c8\ubbc0\ub85c, \uc5ec\uc804\ud788 \ud30c\ub77c\ubbf8\ud130\uc758 \uae30\ubcf8\uac12\uc774 \uc870\ud68c\ub41c\ub2e4)
              \n<\/span><\/p>\n

              [\ucc38\uace0] \uc544\ub798 5.4\uc808\uc5d0 \uc774\ub7ec\ud55c control variable\uc5d0 \ub300\ud55c \uac12\uc774 \uc5b4\ub5bb\uac8c \uacb0\uc815\ub418\ub294\uc9c0 \uc815\ub9ac\ud558\uc600\ub2e4.
              \n<\/em><\/span><\/p>\n

              \n <\/p>\n

              SVRMGRL> select kviival from x$kvii where kviidsc = ‘DB writer IO clump’;
              \n<\/span><\/p>\n

              \n <\/p>\n

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

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

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

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

            6. \n
              \n

              DBWR write dirty buffers when idle(indx:9)
              \n<\/span><\/h3>\n<\/div>\n

              DBWR\ub294 \uc544\ubb34\ub7f0 \uc791\uc5c5\uc774 \uc5c6\ub294 idle\ud55c \uc0c1\ud0dc\uac00 \ucd5c\ub300 3\ucd08\ub85c \uc9c0\uc815\ub418\uc5c8\ub2e4. \uadf8\ub798\uc11c idle\ud55c \uc0c1\ud0dc\uc774\ud6c4 3\ucd08\uac00 \uc9c0\ub098\uba74 \uae68\uc5b4\ub098 disk\uc5d0 write\uc2dc\ucf1c\ub3c4 \ub420 current block\uc774\ub098 dirty block (temporary\ub098 sort block\uc740 \uc81c\uc678)\uc744 \ucc3e\uae30 \uc704\ud574 buffer header\ub97c \uc77d\ub294\ub2e4. \uc774\ub54c \uc77d\ub294 buffer\uc758 \uac2f\uc218\ub97c DBWR scan size<\/em>\ub77c\uace0 \ud558\uba70 max scan size<\/em>\ub294 2*_db_block_write_batch\uac00 \ub41c\ub2e4. dirty list\uc5d0 buffer\uac00 \uc788\uc73c\uba74, \uc774\uac83\uc740 non-idle\ud55c \uc0c1\ud0dc\ub85c \uac04\uc8fc\ub41c\ub2e4.
              \n<\/span><\/p>\n

              \uc774\ub7ec\ud55c timeout\uc124\uc815\uc744 \uc5c6\uc560\uae30 \uc704\ud574\uc11c\ub294 _db_block_no_idle_writes\uac12\uc744 \uc124\uc815\ud560 \uc218 \uc788\uc73c\ub098 \uc774 \uac12\uc740 debugging\uc774\ub098 test\ub97c \uc81c\uc678\ud558\uace0\ub294 \uc124\uc815\ud558\uc9c0 \uc54a\uc544\uc57c \ud55c\ub2e4.
              \n<\/span><\/p>\n

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

            7. \n
              \n

              DBWR write checkpoint -needed buffers\/recovery end (indx:11)
              \n<\/span><\/h3>\n<\/div>\n

              checkpoint\uac00 \ubc1c\uc0dd\ud558\uba74, LGWR\uc740 DBWR\uc5d0\uac8c checkpoint flag\uac00 \uc124\uc815\ub418\uc5b4 \uc788\uace0 disk\ub85c \uc50c\uc5ec\uc838\uc57c \ud558\ub294 current, dirty, non-temporary buffer header\uc758 array\uc640 \ud568\uaed8 signal\uc744 \ubcf4\ub0b8\ub2e4. \ud55c\ubc88\uc5d0 write\ud574\uc57c \ud558\ub294 \ud06c\uae30\ub294 db_block_checkpoint_batch parameter\uc5d0 \uc758\ud574 \uacb0\uc815\ub41c\ub2e4.
              \n<\/span><\/p>\n

              DBWR\uac00 current\uc640 dirty buffer\ub97c disk\uc5d0 write\ud558\ub294 \uac83\uc740 DBWR write dirty buffers when idle event\uc640 \uac19\uc9c0\ub9cc, checkpoint\uac00 \ubc1c\uc0dd\ud558\uc5ec disk\uc5d0 write\ub41c buffer\ub294 free\ub85c mark\ub418\uc9c0\ub294 \uc54a\ub294\ub2e4. \uc774\ub807\uac8c \ud558\ub294 \uac83\uc774 hit ratio\ub97c \ud5a5\uc0c1\uc2dc\ud0a4\uace0, physical I\/O\ub294 \uc904\uc5ec\uc900\ub2e4.
              \n<\/span><\/p>\n

              \n <\/p>\n

              foreground process\uac00 buffer cache\ub97c \uc0ac\uc6a9\ud558\uc5ec dirty buffer\ub85c \ub9cc\ub4e6\uc5d0 \ub530\ub77c, free buffer\ub294 \uc810\ucc28 \uc904\uc5b4\ub4e0\ub2e4. \uc774\ub807\uac8c dirty buffer\uac00 \ub108\ubb34 \ucee4\uc9c0\ub294 \uac83\uc744 \ub9c9\uae30 \uc704\ud574, LRU \uc911 dirty \ub098 pinned buffer\uac00 \uc544\ub2cc clean buffer\uc758 \uac2f\uc218\ub97c \uc138\uae30 \uc704\ud55c \ubcc0\uc218\uac00 \uc815\uc758\ub418\uc5b4 \uc788\ub2e4. \uc774 \uac12\uc744 known clean buffers count<\/em>\ub77c\uace0 \ud558\uba70, dirty buffer\uac00 LRUW list\ub85c \uc62e\uaca8\uc9c4 \ud6c4 disk\uc5d0 write\ub418\uba74 \uac12\uc774 \uc99d\uac00\ud558\uace0, \ubc18\ub300\ub85c foreground process\uac00 free buffer\ub97c \uc0ac\uc6a9\ud558\uba74 \uc904\uc5b4\ub4e0\ub2e4.
              \n<\/span><\/p>\n

              [\ucc38\uace0] \uc2e4\uc81c known clean buffers count\ub294 \uc815\ud655\ud55c \uac12\uc744 \uac00\uc9c0\ub294 \uac83\uc774 \uc544\ub2c8\ub77c \ub3d9\uc801\uc73c\ub85c \ud56d\uc0c1 \ubcc0\ud558\ub294 \uadfc\uc0ac\uac12\uc744 \uac00\uc9c0\uac8c \ub41c\ub2e4.
              \n<\/em><\/span><\/p>\n

              \uc774 known clean buffer count\uac12\uc774 \uc77c\uc815\ud55c \uac12 \uc774\ud558\uac00 \ub418\uba74, DBWR\uc774 LRU list\ub97c clean-out\ud558\ub3c4\ub85d \uc2e0\ud638\ub97c \ubc1b\uace0, DBWR scan depth\ub9cc\ud07c buffer\ub97c \uc77d\ub294\ub2e4. DBWR scan depth\ub294 DBWR\uc774 LRU list\uc758 \ub05d\uc5d0 clean buffer\ub97c \uc5b4\ub290\ub9cc\ud07c \uc720\uc9c0\ud558\ub290\ub0d0\uc5d0 \ub530\ub77c \ub3d9\uc801\uc73c\ub85c \uac12\uc774 \ubcc0\uacbd\ub41c\ub2e4. \uc608\ub97c \ub4e4\uc5b4, foreground process\uac00 dirty buffer\ub97c \ubc1c\uacac\ud558\uace0 LRUW list\ub85c \uc62e\uae30\uba74, DBWR\uc740 \uc790\uc2e0\uc774 clean buffer\ub97c \ucda9\ubd84\ud788 \uc720\uc9c0\ud558\uc9c0 \ubabb\ud588\ub2e4\ub294 \uac83\uc744 \uc54c\uac8c \ub41c\ub2e4. \ub610\ud55c \ub9cc\uc57d known clean buffer count<\/em>\uac00 DBWR scan depth<\/em>\ubcf4\ub2e4 \uc791\uc73c\uba74, \uc774\uac83\ub3c4 DBWR\uc774 clean buffer\ub97c \ucda9\ubd84\ud788 \uc720\uc9c0\ud558\uc9c0 \ubabb\ud55c \uac83\uc774\uae30 \ub54c\ubb38\uc5d0 \uc774 \ub450 \uacbd\uc6b0 \ubaa8\ub450 scan depth increment size<\/em>\uc5d0 \uae30\ucd08\ud558\ub418, max scan depth size<\/em>\ub294 \ub118\uc9c0 \uc54a\ub294 \uac12\uc73c\ub85c DBWR scan depth\ub97c \uc99d\uac00\uc2dc\ud0a8\ub2e4. \ubc18\ub300\ub85c, known clean buffer count<\/em>\uac00 DBWR scan depth<\/em>\ubcf4\ub2e4 \ud06c\uace0 LRUW list\uac00 \ube48 \uacbd\uc6b0\uc5d0\ub294 DBWR scan depth<\/em>\ub97c scan depth decrement<\/em>\ub9cc\ud07c \uac10\uc18c\uc2dc\ud0a8\ub2e4. scan depth decrement<\/em>\ub294 scan depth increment<\/em>\ubcf4\ub2e4 \ud074 \uc218\ub294 \uc5c6\uc73c\uba70, \ub2e4\uc74c\uacfc \uac19\uc774 \ud604\uc7ac\uc758 \uac12\uc774 \ud655\uc778\uac00\ub2a5\ud558\ub2e4.
              \n<\/span><\/p>\n

              \n <\/p>\n

              SVRMGR> select kvitdsc, kvitval from x$kvit where kvitdsc like ‘DBWR scan depth%’;
              \n<\/span><\/p>\n

              \n <\/p>\n

              KVITDSC,                 KVITVAL
              \n<\/span><\/p>\n

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

              DBWR scan depth increment        5
              \n<\/span><\/p>\n

              DBWR scan depth decrement        1
              \n<\/span><\/p>\n

              \n <\/p>\n

              \uc77c\ubc18\uc801\uc73c\ub85c DBWR\uc774 dirty list\ub97c clean-out\ud558\ub3c4\ub85d \uc2e0\ud638\ub97c \ubc1b\uc73c\uba74, \uba3c\uc800 LRUW list\uc5d0\uc11c buffer\ub97c pin\uc2dc\ud0a4\uba74\uc11c _db_block_write_batch \ub9cc\ud07c\uc758 buffer\ub97c \ubaa8\uc740\ub2e4. \uc774\ub54c, \ub9cc\uc57d _db_block_write_batch\ub9cc\ud07c\uc758 buffer\uac00 LRUW list\uc5d0 \uc5c6\ub294 \uacbd\uc6b0\uc5d0\ub294 \ubd80\uc871\ud55c \uac2f\uc218\ub9cc\ud07c\uc758 dirty buffer\ub97c LRU list\uc5d0\uc11c \uc77d\uc5b4 LRUW list\ub85c \uc62e\uae34\ub2e4. \uc774\ub807\uac8c \ud544\uc694\ud55c \ub9cc\ud07c\uc758 buffer\ub97c disk\uc5d0 write\ud558\uace0 \ub09c \ud6c4\uc5d0\ub294 write complete acknowledgment<\/em>\ub97c \ucc0d\uace0 disk\uc5d0 write\ub41c buffer\ub4e4\uc744 unpin\uc2dc\ud0a8\ub2e4. \ub9cc\uc57d foreground process\uac00 LRUW list\uc5d0 \uc788\ub294 block\uc774 disk\uc5d0 write\ub418\uc5b4\uc838\uc11c disk\ub85c \ubd80\ud130 \ub2e4\uc2dc \uc77d\uc5b4\uc57c\ub9cc \ud55c\ub2e4\uba74, write complete wait event<\/em>\uac12\uc774 \uc99d\uac00\ub41c\ub2e4. \uadf8\ub9ac\uace0 foreground process\uac00 LRU list\uc5d0 free buffer\uac00 \uc804\ud600 \uc5c6\uc5b4\uc11c DBWR\uac00 buffer\ub4e4\uc744 disk\uc5d0 write\ud560 \ub54c\uac00\uc9c0 \uae30\ub2e4\ub824\uc57c \ud55c\ub2e4\uba74 free buffer waits <\/em>\ud1b5\uacc4\uac12\uc774 \uc99d\uac00\ub41c\ub2e4. \uadf8\ub7f0\ub370 Oracle7.2 \uc774\uc804\uc5d0\uc11c\ub294 \uc804\uccb4 buffer\uac00 disk\uc5d0 write\ub420 \ub54c\uae4c\uc9c0 \uae30\ub2e4\ub9b0 \ubc18\uba74, 7.2\ubd80\ud130\ub294 buffer\uac00 disk\uc5d0 write\ub418\uba74 \ubc14\ub85c \ub3c5\ub9bd\uc801\uc73c\ub85c unpin\ub418\uae30 \ub54c\ubb38\uc5d0 \uc774\uc804\ubcf4\ub2e4 \ud070 \ubb38\uc81c\uac00 \ub418\uc9c0\ub294 \uc54a\ub294\ub2e4. \uc77c\ub2e8 dirty buffer\uac00 disk\ub85c write\ub418\uba74 \uc774 buffer\ub294 \ube48 \uac83\uc73c\ub85c \uac04\uc8fc\ub418\uc5b4 LRU\uc758 \ub05d\ubd80\ubd84\uc5d0 \uc704\uce58\ud558\uac8c \ub41c\ub2e4.
              \n<\/span><\/p>\n

              DBWR\uac00 _db_block_write_batch \ub9cc\ud07c\uc758 write\ub97c \uc218\ud589\ud558\uace0 \ub09c \ud6c4\uc5d0 \uc0c8\ub85c\uc6b4 dirty buffer\uac00 \ub2e4\uc2dc \uc874\uc7ac\ud568\uc744 \ubc1c\uacac\ud560 \uc218 \uc788\ub2e4. \uc774\ub807\uac8c \uc0c8\ub85c \uc0dd\uc131\ub41c dirty buffer\ub4e4\uc758 \uae38\uc774\ub97c summed dirty queue length\ub77c\uace0 \ubd80\ub974\uba70, \uc774 \uac12\uc740 write batch<\/em> request\ub97c \uc131\uacf5\uc801\uc73c\ub85c \ub05d\ub0b8 \ud6c4\uc758 dirty queue\uc758 \ud06c\uae30\uac00 \ub41c\ub2e4. \uc774 queue\uac00 write batch<\/em>\ubcf4\ub2e4 \ud06c\ub2e4\uba74, \uc774\uac83\uc740 DBWR\uc774 \ub530\ub77c\uac00\uc9c0 \ubabb\ud560 \uc815\ub3c4\ub85c \ube60\ub974\uac8c dirty list\uac00 \uc0dd\uae40\uc744 \uc758\ubbf8\ud558\uac8c \ub41c\ub2e4. \ud3c9\uade0 queue \uae38\uc774\ub294 summed dirty queue length\/write request\uc640 \uac19\ub2e4. \uc774 \uac12\uc740 DBWR\uc758 \ud55c\ubc88\uc758 write request\ud6c4\uc5d0 dirty buffer write queue\uc758 \ud3c9\uade0 \uae38\uc774\uc774\ub2e4. \uc774 queue\uc758 \uae38\uc774\uac00 _db_block_write_batch\ubcf4\ub2e4 \ub450\ubc30 \uc774\uc0c1 \ud06c\ub2e4\uba74, max write batch\ub97c \uc99d\uac00\uc2dc\ucf1c queue\uc758 \uae38\uc774\ub97c \uc904\uc774\ub294 \uac83\uc774 \ub3c4\uc6c0\uc774 \ub420 \uc218 \uc788\ub2e4. \uc774\uac83\uc740 _db_block_write_batch\ub098 db_files_similtaneous_writes\uac12\uc744 \ubcc0\uacbd\ud568\uc5d0 \ub530\ub77c \ub2ec\ub77c\uc9c8 \uc218 \uc788\ub294\ub370 , \uc774 \uac12\uc774 \ub108\ubb34 \ud06c\uba74 DBWR\uac00 I\/O bound\uac00 \ub418\ub294 \ub4f1\uc758 \ubb38\uc81c\uac00 \ub420 \uc218\ub3c4 \uc788\uc73c\ubbc0\ub85c \uc2e0\uc911\ud788 \uace0\ub824\ud558\uc5ec\uc57c \ud55c\ub2e4.
              \n<\/span><\/p>\n<\/li>\n

            8. \n
              \n

              Process Flow
              \n<\/span><\/h1>\n<\/div>\n

              \n <\/p>\n

              \uc774\uc81c\uae4c\uc9c0 DBWR\uac00 buffer cache\ub97c \uad00\ub9ac\ud558\ub294 \uac83\uc5d0 \ub300\ud574\uc11c \uc8fc\ub85c \uc0b4\ud3b4\ubcf4\uc558\uace0, \uc5ec\uae30\uc5d0\uc11c\ub294 foreground process\uc785\uc7a5\uc5d0\uc11c\uc758 cache buffer management\uc5d0 \ub300\ud574\uc11c \uc0b4\ud3b4\ubcf4\ub3c4\ub85d \ud55c\ub2e4.
              \n<\/span><\/p>\n

              buffer cache\ub0b4\uc758 buffer header\uc5d0\ub294 \ub2e4\uc74c\uc758 \ub450\uac00\uc9c0 list\ub97c \ud3ec\ud568\ud558\uace0 \uc788\ub2e4.
              \n<\/span><\/p>\n<\/li>\n<\/ol>\n

                \n
              • \n
                user list: doubly linked list\ub85c \uc5f0\uacb0\ub418\uc5b4 \uc788\ub294 “handle”\uc744 \ud3ec\ud568\ud55c\ub2e4. \uc5ec\uae30\uc5d0\uc11c handle\uc740 \ud574\ub2f9 buffer\ub97c \uc0ac\uc6a9\ud558\uace0 \uc788\ub294 oracle process\ub97c \uac00\ub9ac\ud0a4\ub294 \uc815\ubcf4\ub97c \ub2f4\uace0 \uc788\ub2e4.
                \n<\/span><\/div>\n<\/li>\n
              • \n
                waiter list: \uc774 list\ub3c4 doubly linked \ub418\uc5b4 \uc788\ub294 “handle”\uc758 list\ub97c \ud3ec\ud568\ud558\uace0 \uc788\ub294\ub370, \uc5ec\uae30\uc5d0\uc11c\uc758 handle\uc740 \ud574\ub2f9 buffer\ub97c \uc0ac\uc6a9\ud558\uae30 \uc704\ud574 \uae30\ub2e4\ub9ac\uace0 \uc788\ub294 oracle process\uc5d0 \ub300\ud55c \uc815\ubcf4\ub97c \ub2f4\uace0 \uc788\ub2e4.
                \n<\/span><\/div>\n<\/li>\n<\/ul>\n

                \uc77c\ubc18\uc801\uc73c\ub85c user\uac00 \ud2b9\uc815\ud55c block\uc744 \uc77d\uace0\uc790 \ud560 \ub54c foreground process\uac00 \uc791\uc5c5\ud558\ub294 \uc21c\uc11c\ub97c \uac04\ub2e8\ud788 \uc815\ub9ac\ud558\uba74 \ub2e4\uc74c\uacfc \uac19\ub2e4.
                \n<\/span><\/p>\n

                  \n
                1. \n
                  cache buffer handle latch\ub97c \ud655\ubcf4\ud55c \ub4a4, process\uc5d0 buffer handle\uc744 \ud560\ub2f9\ud558\uace0\ub098\uc11c latch\ub97c \ud47c\ub2e4.
                  \n<\/span><\/div>\n<\/li>\n
                2. \n
                  cache buffer chain latch\ub97c \uc5bb\uc740 \ud6c4 \uc8fc\uc5b4\uc9c4 DBA\uc640 SCN\uac12\uc744 \uac00\uc9c0\uace0 cache buffer chain list\uc5d0\uc11c \ud544\uc694\ud55c buffer\ub97c \ucc3e\uc740 \ud6c4 cache buffer chain latch\ub97c \ub2e4\uc2dc release\ud55c\ub2e4.
                  \n<\/span><\/div>\n<\/li>\n
                3. \n
                  \ub9cc\uc57d \uc6d0\ud558\ub294 DBA\uac00 \ubc1c\uacac\ub418\uba74, \uadf8 buffer block\uc744 pin\ud55c \ud6c4 \uc77d\ub294\ub2e4. \uadf8\ub7f0\ub370 \uc774\ub54c \ub9cc\uc57d buffer block\uc774 dirty\uc774\uba74, \uc989 buffer\uc758 SCN\uc774 \uc6d0\ud558\ub294 SCN\uac12\ubcf4\ub2e4 \ud06c\ub2e4\uba74, before image\ub97c \uad6c\uc131\ud558\ub294 CR operation\uc774 \uc218\ud589\ub41c\ub2e4. \uc774\ub7ec\ud55c \uacbd\uc6b0\uc5d0 db block gets\uc640 consistent gets\uac12\uc740 \uc99d\uac00\ub418\uc5b4 logical hit\ub85c \uac04\uc8fc\ub41c\ub2e4. \uc77d\uc740 buffer\ub294 LRU\uc5d0 head\ubd80\ubd84\uc73c\ub85c \uc704\uce58\uac00 \ubcc0\uacbd\ub41c\ub2e4.
                  \n<\/span><\/div>\n<\/li>\n
                4. \n
                  DBA\uac00 cache buffer chain\uc5d0 \uc874\uc7ac\ud558\uc9c0 \uc54a\ub294\ub2e4\uba74, disk\ub85c\ubd80\ud130 \ub2e4\uc2dc \uc77d\uc5b4\uc57c \ud55c\ub2e4. \uc774\ub7ec\ud55c \uacbd\uc6b0\uc5d0 \uba3c\uc800 LRU latch\ub97c \uc5bb\uc740 \ud6c4 LRU list\uc5d0\uc11c free bufffer\ub97c \ucc3e\ub294\ub2e4. \ub9cc\uc57d \uc815\ud574\uc9c4 \ubc94\uc704\ub0b4\uc5d0\uc11c free buffer\uac00 \ubc1c\uacac\ub418\uc9c0 \uc54a\ub294 \uacbd\uc6b0\uc5d0 \uc218\ud589\ub418\uc5b4\uc9c0\ub294 \uc791\uc5c5\uc740 \uc774\ubbf8 \uc55e\uc5d0\uc11c \uc0b4\ud3b4 \ubcf4\uc558\ub2e4. \ub9cc\uc57d free buffer\uac00 \ubc1c\uacac\ub418\uba74, free buffer\ub294 pin\ub418\uc5b4\uc9c0\uace0, LRU\uc758 head \ubd80\ubd84\uc73c\ub85c \uc62e\uaca8\uc9c4 \ud6c4 disk\ub85c\ubd80\ud130 \uc77d\ub294\ub2e4. \uadf8\ub9ac\uace0 cache buffer chain latch\ub97c \ud655\ubcf4\ud55c \ud6c4 \uc0c8\ub85c\uc6b4 buffer header\uc758 \uc815\ubcf4\ub97c hash list\uc5d0 \ud3ec\ud568\uc2dc\ud0a8 \ud6c4 latch\ub97c \ud47c\ub2e4.
                  \n<\/span><\/div>\n<\/li>\n
                5. \n
                  (1)\uc5d0\uc11c \ud655\ubcf4\ud55c buffer handle\uc744 \ud574\ub2f9 buffer\uc758 user list\uc5d0 \ucca8\uac00\ud55c\ub2e4.
                  \n<\/span><\/div>\n<\/li>\n<\/ol>\n

                  \n <\/p>\n

                  buffer operation\uc744 \uc904\uc774\uba74 \uc804\uccb4\uc801\uc778 database \uc131\ub2a5\uc5d0 \ub3c4\uc6c0\uc774 \ub418\ub294\ub370 buffer operation\uc744 \uc904\uc774\ub294 \ubc29\ubc95\uc740 \ub2e4\uc74c\uacfc \uac19\uc774 \uc694\uc57d\ud560 \uc218 \uc788\ub2e4.
                  \n<\/span><\/p>\n

                  (1) temporary tablespace\ub97c \uc804\uc6a9\uc73c\ub85c \uc0ac\uc6a9
                  \n<\/span><\/p>\n

                  (2) direct sort reads
                  \n<\/span><\/p>\n

                  (3) direct sqload
                  \n<\/span><\/p>\n

                  (4) direct export
                  \n<\/span><\/p>\n

                  \uc704\uc5d0\uc11c \uc0b4\ud3b4\ubcf8 \ubc14\uc640 \uac19\uc774 \ud544\uc694\ud55c block\uc774 cache\ub418\uc5b4 \uc788\ub294 \uacbd\uc6b0 \ub9e4\uc6b0 \ub9ce\uc740 code\ubd80\ubd84\uc744 \uc218\ud589\ud558\uc9c0 \uc54a\uc544\ub3c4 \ub418\ubbc0\ub85c, buffer\uc758 hit ratio\uc744 \ub192\uac8c \uc720\uc9c0\ud558\ub294 \uac83\uc774 \ub9e4\uc6b0 \uc911\uc694\ud558\ub2e4.
                  \n<\/span><\/p>\n

                  \n <\/p>\n

                    \n
                  1. \n
                    \n

                    Multiple LRU Latches
                    \n<\/span><\/h2>\n<\/div>\n

                    process\uac00 buffer cache\uc758 list\ub4e4\uc744 access\ud558\uae30 \uc704\ud574\uc11c\ub294 \uba3c\uc800 latch\ub97c \uc5bb\uc5b4\uc57c \ud55c\ub2e4. latch\ub294 \ube60\ub974\uac8c \uc7a1\uace0 \ud480 \uc218 \uc788\ub294 lock\uc758 \uc77c\uc885\uc774\ub77c\uace0 \ubcfc \uc218 \uc788\uc73c\uba70, lock(enqueue)\uc774 \uc8fc\ub85c \ub458 \uc774\uc0c1\uc758 process\uac00 \ub3d9\uc2dc\uc5d0 \uac19\uc740 data structure\ub97c access\ud558\ub294 \uac83\uc744 \ub9c9\uae30 \uc704\ud55c \uac83\uc774\ub77c\uba74 latch\ub294 \ub3d9\uc2dc\uc5d0 \uac19\uc740 code(oracle source code)\ubd80\ubd84\uc744 \uc2e4\ud589\ud558\ub294 \uac83\uc744 \ub9c9\uae30 \uc704\ud55c \uac83\uc774\ub77c\uace0 \ud560 \uc218 \uc788\ub2e4. LRU\uc640 LRUW \ub294 \ubaa8\ub450 LRU LATCH\ub77c\uace0 \ubd88\ub9ac\ub294 \uac19\uc740 latch\ub97c \uc0ac\uc6a9\ud55c\ub2e4.
                    \n<\/span><\/p>\n

                    7.3 \uc544\ub798 version\uc5d0\ub294 \ud558\ub098\uc758 LRU LATCH\ub97c \uc774\uc6a9\ud558\uc5ec \uc804\uccb4 buffer cache\ub97c \uad00\ub9ac\ud558\uc600\uc73c\ub098, \uc5ec\ub7ec\uac1c\uc758 CPU, Parallel Query Option(PQO)\uc758 \uc0ac\uc6a9 \ubc0f \ub2e4\uc218\uc758 \ub3d9\uc2dc \uc0ac\uc6a9\uc790 \ub4f1\uc73c\ub85c \uc778\ud574 LRU latch contention\uc774 \ubc1c\uc0dd\ud558\ub294 \uacbd\uc6b0\uac00 \ub9ce\ub2e4. \uadf8 \uc678\uc5d0\ub3c4 DBWR\uc740 dirty block\uc744 disk\uc5d0 write\ud558\uae30 \uc704\ud574 \ub2e4\ub978 foreground process\ub4e4\uacfc\ub3c4 \uacbd\uc7c1\ud558\uae30\ub3c4 \ud558\uace0 db_lru_extended_statistics\uc640 \uac19\uc740 parameter\ub97c \uc124\uc815\ud55c \uacbd\uc6b0\uc5d0\ub3c4 LRU latch\uc5d0 \ub300\ud574\uc11c \uacfc\ub3c4\ud55c \uacbd\uc7c1\uc744 \uc77c\uc73c\ud0a8\ub2e4. \uadf8\ub798\uc11c Oracle7.3\ubd80\ud130\ub294 db_block_lru_latches parameter\ub97c \uc0ac\uc6a9\ud558\uc5ec \ud558\ub098 \uc774\uc0c1\uc758 LRU LATCH\ub97c \uc9c0\uc815\ud560 \uc218 \uc788\ub3c4\ub85d \ud558\uc600\uc73c\uba70, \uc8fc\ub85c \uc774 \uac12\uc740 CPU\uc758 \uac2f\uc218\ub85c \uc9c0\uc815\ud55c\ub2e4.
                    \n<\/span><\/p>\n

                    \uc774\ub7ec\ud55c multiple latch\ub294 set\uc774\ub77c\ub294 \uac1c\ub150\uc744 \uc774\uc6a9\ud558\uc5ec \uad6c\ud604\ub418\uc5c8\ub2e4. \uc989 \ud558\ub098\uc758 latch\uc5d0 \uc758\ud574 \ubcf4\ud638\ub418\ub294 LRU list\uc640 LRUW list\uc758 \ubb36\uc74c\uc774 \ud558\ub098\uc758 set\uc774 \ub418\ub294 \uac83\uc774\ub2e4. \uacb0\uad6d set\uc758 \uac2f\uc218\ub294 LRU LATCH\uc758 \uac2f\uc218\uc640 \uac19\uc73c\uba70, \uc774\uac83\uc740 \uae30\ubcf8\uc801\uc73c\ub85c CPU\uc758 \uac2f\uc218\/2\uc774\uba70 \ucd5c\ub300 CPU\uc758 \uac2f\uc218 *2(Oracle7.3) \ud639\uc740 CPU\uc758 \uac2f\uc218*6 (Oracle8)\uac1c\uae4c\uc9c0 \ub298\ub9b4 \uc218 \uc788\ub2e4. \uc774 \ud30c\ub77c\ubbf8\ud130\ub97c \uc9c0\uc815\ud560 \ub54c\ub294 \ud558\ub098\uc758 latch\ub2f9 \ucd5c\uc18c 50\uac1c \uc774\uc0c1\uc758 buffer\uac00 \ud560\ub2f9\ub418\ub3c4\ub85d \uc9c0\uc815\ud55c\ub2e4. \uc774\ub807\uac8c \ubcf5\uc218\uac1c\uc758 set\uc774 \uc0ac\uc6a9\ub418\ub294 \uacbd\uc6b0 round robin \ubc29\uc2dd\uc73c\ub85c \uac01 set\uc5d0 buffer\uac00 \ud560\ub2f9\ub418\uba70, \uc774\ub807\uac8c \ud558\uc5ec\uc57c \ubaa8\ub4e0 set\uc5d0 \ub300\ud574\uc11c buffer\uac00 \uade0\ub4f1\ud558\uac8c \ub098\ub204\uc5b4\uc9c0\uac8c \ub41c\ub2e4.
                    \n<\/span><\/p>\n

                    user process\uac00 free buffer\ub97c \uc5bb\uace0\uc790 \ud558\uba74 random\ud558\uac8c set\uc774 \uc9c0\uc815\ub418\uc5b4 buffer\ub97c \ucc3e\uac8c \ub418\uba70, \ub9cc\uc57d latch\ub97c \uc5bb\uc9c0 \ubabb\ud558\uba74 \uadf8 set\uc744 \uc2dc\uc791\uc73c\ub85c round robin \ubc29\uc2dd\uc73c\ub85c \ub2e4\ub978 set\ub4e4\ub3c4 \ucc3e\uac8c \ub41c\ub2e4. \ub9cc\uc57d \ubaa8\ub4e0 set\uc744 \ubc29\ubb38\ud55c \ub4a4\uc5d0\ub3c4 \uc5bb\uc9c0 \ubabb\ud558\uba74 latch miss\uac00 \ub418\uba70 \uc774 miss\ub41c \ud69f\uc218\ub97c \ud655\uc778\ud558\uae30 \uc704\ud574\uc11c\ub294 \ub2e4\uc74c\uacfc \uac19\uc740 query\uac00 \uc774\uc6a9\ub418\uc5b4\uc9c8 \uc218 \uc788\ub2e4.
                    \n<\/span><\/p>\n

                    \n <\/p>\n

                    SQL> select child#, sleeps\/gets ratio
                    \n<\/span><\/p>\n

                    from v$latch_children
                    \n<\/span><\/p>\n

                    where name = ‘cache buffers lru chain’;
                    \n<\/span><\/p>\n

                    \ub9cc\uc57d \uc774 \uacb0\uacfc\uc758 ratio\uac00 1% \ubcf4\ub2e4 \ud06c\uac8c \ub41c\ub2e4\uba74 set\uc758 \uac2f\uc218\ub97c \ub298\ub824\uc8fc\uc5b4\uc57c \ud55c\ub2e4.
                    \n<\/span><\/p>\n

                    \n <\/p>\n

                    \ubaa8\ub4e0 DBWR\/buffer cache\uc5d0 \uad00\ub828\ub41c parameter\ub294 set\uc758 \uac2f\uc218\uc5d0 \ub9df\ucd94\uc5b4\uc838\uc57c \ud55c\ub2e4\ub294\uac83\uc744 \uc81c\uc678\ud558\uace0\ub294 \ubaa8\ub4e0 \ubc29\uc2dd\uc774\ub098 \uacc4\uc0b0\uc740 \ud558\ub098\uc758 latch\uc778 \uacbd\uc6b0\uc640 \uac19\ub2e4. \uc989 \uc608\ub97c \ub4e4\uc5b4 user\uac00 DBWR\ub85c \ud558\uc5ec\uae08 dirty buffer\ub97c disk\uc5d0 write\ud558\ub3c4\ub85d \uc694\uccad\ud558\uae30 \uc804\uc5d0 \uc77d\uc5b4\uc57c \ud558\ub294 buffer\uc758 \uac2f\uc218\ub294 _DB_BLOCK_MAX_SCAN_CNT\/#sets\uc774 \ub41c\ub2e4.
                    \n<\/span><\/p>\n

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

                  2. \n
                    \n

                    Multiple DBWRs\uc640 Async I\/O
                    \n<\/span><\/h2>\n<\/div>\n

                    DBWR process\uc758 \uc8fc\uc694 \uae30\ub2a5\uc740 buffer cache\uc548\uc5d0 \uc788\ub294 dirty buffer\ub97c disk\uc5d0 \uae30\ub85d\ud558\uace0 buffer\ub97c clean\ud55c \uc0c1\ud0dc\ub85c \uc720\uc9c0\ud558\ub294 \uac83\uc774\ub77c\uace0 \ud560 \uc218 \uc788\ub2e4. DBWR\uac00 dirty buffer\ub97c \ube68\ub9ac disk\uc5d0 write\ud558\uae30 \uc704\ud574 multiple DBWR process\ub97c \uc720\uc9c0\ud558\ub294 \ubc29\ubc95\uacfc asynchronous I\/O \ubc29\ubc95\uc744 \uc81c\uacf5\ud55c\ub2e4.
                    \n<\/span><\/p>\n

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

                  3. \n
                    \n

                    Multiple DBWR
                    \n<\/span><\/h3>\n<\/div>\n

                    DBWR\uc758 write\uc791\uc5c5\uc744 \ubcd1\ub82c\ub85c \ud558\ub3c4\ub85d \ud574\uc8fc\uace0, I\/O\ub97c \ubd84\uc0b0\uc2dc\ud0a4\uae30 \uc704\ud574 DBWR process\ub97c \ubcf5\uc218\uac1c\ub85c \uc9c0\uc815\uac00\ub2a5\ud558\ub2e4. DBWR slave process\uc758 \uac2f\uc218\ub294 db_writers parameter\uc5d0 \uc758\ud574 \uacb0\uc815\ub418\uba70, \uae30\ubcf8\uc801\uc73c\ub85c \uc774 \uac12\uc740 1\uc774\uba70, \ucd5c\ub300 50(platform dependent)\uae4c\uc9c0 \uc99d\uac00\uc2dc\ud0ac \uc218 \uc788\ub2e4. \ub9cc\uc57d system\uc774 I\/O\uc791\uc5c5\uc774 \ub9ce\uc740 \ud3b8\uc774\ub77c\uba74, min(datafile\uc774 \uc704\uce58\ud55c disk\uc758 \uac2f\uc218, 2*CPU\uc758 \uac2f\uc218)\uac12\uc73c\ub85c \uc124\uc815\ud558\ub294 \uac83\uc774 \ubc14\ub78c\uc9c1\ud558\ub2e4.
                    \n<\/span><\/p>\n

                    DBWR slave process\ub294 SMON\uc774 \ucd08\uae30\ud654\ub418\uace0 \ub09c \ud6c4 startup\ub418\uba70, \uac01 salve process\ub9c8\ub2e4 \uc57d SGA\ub0b4\uc5d0\uc11c 3400 bytes\ub97c \ucc28\uc9c0\ud55c\ub2e4. slave process\uac00 \ucd08\uae30\ud654\ub418\uace0 \ub09c \ud6c4\uc5d0\ub294, slave process\ub294 DBWR timer event\ub97c \uae30\ub2e4\ub9ac\uba74\uc11c master DBWR\uc774 data\ub97c \uc804\ub2ec\ud574\uc8fc\uae30\ub97c \uae30\ub2e4\ub9ac\uace0, master DBWR\uc740 salve process\ub4e4\uc774 I\/O\uac00 \ub05d\ub09c\ub2e4\ub294 \uac83\uc744 \uc54c\ub824\uc8fc\ub294 DBWR I\/O to slave event\ub97c \uae30\ub2e4\ub9ac\uac8c \ub41c\ub2e4.
                    \n<\/span><\/p>\n

                    DBWR salve processing\uc740 main DBWR process\uac00 \uac01 salve process\ub4e4\uc5d0\uac8c I\/O write\ub97c \ub098\ub204\uc5b4 \uc8fc\ub3c4\ub85d \ud558\ub294 \uac83\uc774\ub2e4. master DBWR\uc774 batch write\uac00 \ud544\uc694\ud558\uac8c \ub418\uba74, \uac01 slave process\ub4e4\uc5d0\uac8c round-robin \ubc29\uc2dd\uc73c\ub85c write\ud560 block\ub4e4\uc744 \ub098\ub204\uc5b4 \uc900\ub2e4. \uc774\ub54c master DBWR\uc740 \uc2e4\uc81c I\/O\uc5d0\ub294 \ucc38\uc5ec\ud558\uc9c0 \uc54a\uc73c\uba70, \ub2e8\uc9c0 \uac10\ub3c5\ub9cc \ud558\uba74 \ub41c\ub2e4. \uadf8\ub9ac\uace0 \ubaa8\ub4e0 salve process\ub4e4\uc774 \ubaa8\ub450 I\/O request\uc5d0 \ucc38\uc5ec\ud558\ub294 \uac83\uc740 \uc544\ub2c8\uba70, write\ud560 \ud06c\uae30\uc640 salve process\uc758 \ud604\uc7ac \ud65c\ub3d9\uc5d0 \ub530\ub77c \ub2ec\ub77c\uc9c0\uac8c \ub41c\ub2e4.
                    \n<\/span><\/p>\n

                    DBWR\uc740 \ucd08\uae30\ud654\ub418\uba74 \ubc14\ub85c SGA\ub0b4\uc5d0 ssafaonfy\ub77c\uace0 \ud558\ub294 \uad6c\uc870\ub97c \ud560\ub2f9\ud55c\ub2e4. \uc774 structure\uac00 db_writers\uc758 \uac2f\uc218, datafile\uc758 \uc815\ubcf4\ub97c \uae30\uc220\ud558\ub294 SFFIB(File Identification Structure), \uadf8\ub9ac\uace0 open datafile status\ub97c \ub098\ud0c0\ub0b4\ub294 slave I\/O vector array status\ub97c \uad00\ub9ac\ud55c\ub2e4. multiple DBWR\uac00 \uad6c\ub3d9\ub418\uba74, \uac01 salve process\uac00 PGA\ub0b4\uc5d0 I\/O vector array (IOV) structure\ub97c \ud560\ub2f9\ud558\uace0, \uc774 structure\uc758 \ub0b4\uc6a9\uc740 master DBWR\uc758 SGA structure\ub85c\ubd80\ud130 IOV array data\ub97c \ubcf5\uc0ac\ud558\uc5ec \ucc44\uc6cc\uc9c4\ub2e4. master DBWR\uc740 SGA\ub0b4\uc5d0 ssfaorq\ub77c\uace0 \ud558\ub294 \ub2e4\ub978 structure\ub3c4 \uc0dd\uc131\ud558\ub294\ub370, \uc774 structure\ub294 master DBWR\uc774 slave process\uc640 write\/close operation\uc5d0 \ub300\ud574 \ud1b5\uc2e0\ud558\uae30 \uc704\ud55c \uac83\uc774\ub2e4. \uac01 slave\ub9c8\ub2e4 \ud558\ub098\uc529\uc758 ssfaorq structure\uac00 \uc874\uc7ac\ud558\uba70, SGA\uc640 slave process\ub4e4\uc758 PGA\ub0b4\uc5d0 \uc874\uc7ac\ud55c\ub2e4. SGA\ub0b4\uc758 ssfaorq structure\ub294 \uac01\uac01 ssfaorqa array\ud615\ud0dc\ub85c \ucc38\uc870\ub41c\ub2e4.
                    \n<\/span><\/p>\n

                    \uadf8\ub7ec\ubbc0\ub85c DBWR\uc774 write request\ub97c \ubc1b\uc73c\uba74 \ub2e4\uc74c\uacfc \uac19\uc740 \uc21c\uc11c\ub85c \ucc98\ub9ac\uac00 \uc774\ub8e8\uc5b4\uc9c4\ub2e4.
                    \n<\/span><\/p>\n

                      \n
                    1. \n
                      master DBWR\uc774 ssfaorqa array\ub97c \ub4a4\uc838 idle slave\ub97c \ucc3e\ub294\ub2e4.
                      \n<\/span><\/div>\n<\/li>\n
                    2. \n
                      idle slave\uac00 \ubc1c\uacac\ub418\uba74, master DBWR\uc740 file#, block#, SFFIB file identification structure\ub85c \uad6c\uc131\ub41c \uc774 I\/O request\ub97c PGA\ub0b4\uc5d0 \uc788\ub294 slave\uc758 ssaorq structure\uc5d0 \ubcf5\uc0ac\ud55c\ub2e4.
                      \n<\/span><\/div>\n<\/li>\n
                    3. \n
                      (2)\ubc88\uacfc \uac19\uc774 request\uac00 \ucd08\uae30\ud654\ub418\uace0 \ub09c \ud6c4, master DBWR\uc740 write pending I\/O flag\ub97c setting\ud55c\ub2e4.
                      \n<\/span><\/div>\n<\/li>\n
                    4. \n
                      slave process\ub294 \uc804\ub2ec\ub418\uc5b4\uc9c4 SFFIB file identification structure \uc815\ubcf4\ub97c \uc774\uc6a9\ud574 \uc790\uc2e0\uc758 PGA\ub0b4\uc5d0 \uc774\ubbf8 \uac00\uc9c0\uace0 \uc788\ub358 \uc815\ubcf4 \uac00 \uc62c\ubc14\ub978\uc9c0\ub97c \uc810\uac80\ud55c\ub2e4.
                      \n<\/span><\/div>\n<\/li>\n
                    5. \n
                      \uc774\ub807\uac8c \uc810\uac80\ud558\ub294 \uc774\uc720\ub294 datafile\uc758 \uc0c1\ud0dc\uac00 offline, \ud639\uc740 drop\ub418\ub294 \ub4f1 \ubcc0\uacbd\uc774 \uc774\ub8e8\uc5b4\uc84c\ub294\uc9c0 \ud655\uc778\ud558\uae30 \uc704\ud574\uc11c\uc774\ub2e4.
                      \n<\/span><\/div>\n<\/li>\n
                    6. \n
                      \uc704\uc5d0\uc11c \uc218\ud589\ud55c \uc810\uac80\uc774 \uc131\uacf5\ud558\uba74 I\/O\uac00 \uc774\ub8e8\uc5b4\uc9c4\ub2e4.
                      \n<\/span><\/div>\n<\/li>\n
                    7. \n
                      I\/O\uac00 \ub05d\ub098\uba74, slave process\ub294 (3)\ubc88\uc5d0\uc11c \uc124\uc815\ub41c SGA\ub0b4\uc758 write pending flag\ub97c \ud574\uc81c\ud55c\ub2e4.
                      \n<\/span><\/div>\n<\/li>\n<\/ol>\n

                      \n <\/p>\n

                      Oracle 7.2 version\uc5d0\uc11c\ub294 open datafile\uc758 \uc218\uac00 100\uac1c\uac00 \ub118\uc73c\uba74 \uc790\ub3d9\uc73c\ub85c multiple DBWR\uac00 \ubd88\uac00\ub2a5\ud558\uac8c \ub418\uc5c8\ub2e4. Oracle8\uc5d0\uc11c db_writes\ub97c \ubcf5\uc218\uac1c\ub85c \uc9c0\uc815\ud558\uba74 Oracle7\uacfc\ub294 \ub2e4\ub974\uac8c \uc791\ub3d9\ud558\uba70, \uc774\uac83\uc740 \ub4a4\uc5d0\uc11c \uc790\uc138\ud788 \uc124\uba85\ud55c\ub2e4.
                      \n<\/span><\/p>\n

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

                    8. \n
                      \n

                      asynchronous I\/O
                      \n<\/span><\/h3>\n<\/div>\n

                      \uc704\uc5d0\uc11c \uc124\uba85\ud55c DBWR\uc774 parallel write processing\uc744 \uc81c\uacf5\ud558\ub294\ub370 \ubc18\ud574 asynchronous I\/O\ub294 non-blocking I\/O\ub97c \uc81c\uacf5\ud55c\ub2e4. \uc774 \uae30\ub2a5\uc740 OS\uc5d0\uc11c async I\/O\ub97c \uc9c0\uc6d0\ud574\uc57c\ub9cc \uc0ac\uc6a9\uac00\ub2a5\ud55c\ub370, \uc6b4\uc601\uccb4\uc81c\uc5d0 \ub530\ub77c raw device\uc5d0 \ub300\ud574\uc11c\ub9cc async I\/O\ub97c \uc81c\uacf5\ud558\ub294 \uacbd\uc6b0\ub3c4 \uc788\ub2e4. AIX\uc758 \uacbd\uc6b0, async I\/O\ub294 \uc790\ub3d9\uc73c\ub85c \uc0ac\uc6a9\uac00\ub2a5\ud558\uba70, \ub2e4\ub978 OS\uc740 device driver configuration\uc744 \uc218\ud589\ud55c \ud6c4 \uc0ac\uc6a9\ud558\uc5ec\uc57c \ud55c\ub2e4. Oracle\uc5d0\uc11c \uc774\ub7ec\ud55c OS\uc5d0\uc11c \uc81c\uacf5\ud558\ub294 asynchrounous I\/O\ub97c \uc0ac\uc6a9\ud558\uae30 \uc704\ud574\uc11c\ub294 init.ora file\uc5d0 \ud544\uc694\ud55c parameter\ub97c \uc9c0\uc815\ud558\uc5ec\uc57c \ud558\ub294\ub370, \uc774 parameter\ub294 platform\ub9c8\ub2e4 \uc870\uae08\uc529 \ub2e4\ub974\ub2e4. \uc608\ub97c \ub4e4\uc5b4 Sun Solaris\uc758 \uacbd\uc6b0\uc5d0\ub294 async_read\uc640 async_write\uac00 \ubcc4\ub3c4\ub85c \ud544\uc694\ud55c \ub370 \ubc18\ud574, AIX\ub294 use_aync\ub77c\ub294 \ud558\ub098\uc758 parameter\ub9cc\uc774 \uc874\uc7ac\ud55c\ub2e4. Oracle\uc5d0\uc11c async I\/O\uac00 \uc124\uc815\ub418\uba74, read\ub098 write (\ud639\uc740 pread\ub098 pwrite)\ub300\uc2e0 aioread\ub098 aiowrite\uac00 \ud638\ucd9c\ub41c\ub2e4.
                      \n<\/span><\/p>\n

                      \n <\/p>\n

                        \n
                      1. \n
                        \n

                        Oracle\uc5d0\uc11c\uc758 buffer cache management (Multiple Buffer Pool)
                        \n<\/span><\/h1>\n<\/div>\n<\/li>\n<\/ol>\n

                        \n <\/p>\n

                        table\uc774\ub098 index \ub4f1 segment\ub294 \uadf8 \uc0ac\uc6a9 \ube48\ub3c4\ub098 \uc911\uc694\ub3c4 \ub4f1\uc5d0 \ub530\ub77c memory\uc5d0 buffering\ub418\ub294 \uac83\uc744 \ub2ec\ub9ac \ud560 \ud544\uc694\uac00 \uc788\ub2e4. Oracle8\uc5d0\uc11c\ub294 buffer cache\uc5d0 \ub300\ud574\uc11c multiple buffer pool\uc774\ub77c\ub294 \uc0c8\ub85c\uc6b4 \ud2b9\uc131\uc758 \uac1c\ub150\uc744 \uc81c\uacf5\ud558\uc5ec segment\ub9c8\ub2e4 \ub2e4\ub978 buffer\ub97c \uc0ac\uc6a9\ud560 \uc218 \uc788\ub3c4\ub85d \ud558\uace0 \uc788\ub2e4. multiple buffer pool\uc740 ‘keep’, ‘recycle’, \uadf8\ub9ac\uace0 ‘default’ buffer pool\ub85c \uad6c\uc131\ub418\uba70, \uc774\uac83\uc744 control\ud558\uae30 \uc704\ud55c internal algorithm\uc740 \ud558\ub098\uc758 buffer pool\uc744 \uc0ac\uc6a9\ud560 \ub54c\uc640 \ub300\ubd80\ubd84 \ub9c8\ucc2c\uac00\uc9c0\ub2e4. \uc989, \uae30\uc874\uc758 CACHE option\uc774\ub098 full table scan\uc2dc LRU end\uc5d0 \uc704\uce58\uc2dc\ud0a4\ub294 \uac83 \ub4f1\uc740 \ubaa8\ub450 \ubcc0\ud568\uc774 \uc5c6\uc73c\uba70, \ub2e8\uc9c0 \uadf8\ub7ec\ud55c \uae30\ubc95\ub4e4\uc774 \uac01 buffer\ub9c8\ub2e4 \ubcc4\ub3c4\ub85c \uc801\uc6a9\ub41c\ub2e4\ub294 \uac83 \ubfd0\uc774\ub2e4.
                        \n<\/span><\/p>\n

                        multiple buffer pool\uc758 \uc8fc\uc694 \ubaa9\uc801\uc740 \uc11c\ub85c \ub2e4\ub978 \ud615\ud0dc\ub85c \uc0ac\uc6a9\ub418\ub294 \uac83\uc744 \ub098\ub204\uc5b4 \ub193\uc544 \uc11c\ub85c \ubc29\ud574\uac00 \ub418\uc9c0 \uc54a\ub3c4\ub85d \ud558\ub294 \uac83\uc73c\ub85c \uc815\ub9ac\ud560 \uc218 \uc788\uc73c\uba70, \uac01\uac01 \ub2e4\uc74c\uacfc \uac19\uc740 \uacbd\uc6b0\uc5d0 \uc0ac\uc6a9\ud558\ub3c4\ub85d \ud55c\ub2e4.
                        \n<\/span><\/p>\n

                          \n
                        1. \n
                          KEEP buffer pool : \uac00\ub2a5\ud55c \ud55c memory\uc5d0 \uc624\ub7ab\ub3d9\uc548 \uc720\uc9c0\ub418\uc5b4\uc838\uc57c \ud558\ub294 segment\ub97c \uc704\ud574 \uc0ac\uc6a9\ub418\uc5b4\uc838\uc57c \ud55c\ub2e4. \uc790\uc8fc \uc0ac\uc6a9\ub418\uc5b4\uc9c0\uace0 cache size\uc758 \uc57d 10%\uc804 \ud6c4\uc758 \ud06c\uae30\ub97c \uac00\uc9c4 segment\uac00 \uc774 pool\uc744 \uc0ac\uc6a9\ud558\uae30\uc5d0 \uc801\ub2f9\ud558\ub2e4. \uadf8\ub7ec\ub098 \uc5ec\uae30\uc5d0\uc11c\ub3c4 Oracle7.3 \uc758 CACHE option\uacfc \ub9c8\ucc2c\uac00\uc9c0\ub85c \uc0c8\ub85c\uc774 access\ub418\ub294 segment\uc5d0 \uc758\ud574 LRU end\ucabd\uc73c\ub85c \uc774\ub3d9\ud558\ub294 \uac83\uc774 \uac00\ub2a5\ud558\ubbc0\ub85c \ud56d\uc0c1 cache\ub41c\ub2e4\uace0 \ubcf4\uc7a5\ud560 \uc218\ub294 \uc5c6\ub2e4. \uc801\ub2f9\ud55c \ud06c\uae30\ub85c \uc9c0\uc815\ud558\ub294 \uac83\uc774 \uc911\uc694\ud55c\ub370 \ub2f9\uc5f0\ud788, \ub3d9\uc2dc\uc5d0 memory\uc5d0 \uc62c\ub824\uc9c0\uae30\ub97c \ubc14\ub77c\ub294 object\ub4e4\uc758 \ud06c\uae30\uc758 \ud569\ubcf4\ub2e4\ub294 \ucee4\uc57c \ud55c\ub2e4.
                          \n<\/span><\/div>\n<\/li>\n
                        2. \n
                          RECYCLE buffer pool: \uc790\uc8fc \uc0ac\uc6a9\ub418\uc5b4\uc9c0\uc9c0 \uc54a\uac70\ub098, buffer pool\uc758 \ub450\ubc30\ubcf4\ub2e4 \ud070 \uc815\ub3c4\uc758 \ud070 segment\uac00 index search\ub97c \ud558\ub294 \uc791\uc5c5 \ub4f1\uc5d0 \uc0ac\uc6a9\ub418\uc5b4\uc9c0\ub3c4\ub85d \ud55c\ub2e4.
                          \n<\/span><\/div>\n<\/li>\n
                        3. \n
                          DEFAULT buffer pool: \uc704\uc758 \ub450 buffer pool\uc5d0 \ud560\ub2f9\ub418\uc9c0 \uc54a\uc740 \ub098\uba38\uc9c0\ub294 default buffer pool\uc774 \ub41c\ub2e4. \uadf8\ub7ec\ubbc0\ub85c KEEP\uc774\ub098 RECYCLE buffer pool\uc740 \uc5c6\uc5b4\ub3c4 \ubc18\ub4dc\uc2dc default buffer pool\uc740 \uc874\uc7ac\ud558\uac8c \ub41c\ub2e4. \uc774 buffer pool\uc740 Oracle7\uc758 \ud558\ub098\uc758 buffer pool\uacfc \uac19\ub2e4.
                          \n<\/span><\/div>\n<\/li>\n<\/ol>\n

                          \uc774\ub7ec\ud55c \uc885\ub958\uc758 buffer pool\uc744 \uc9c0\uc815\ud558\uae30 \uc704\ud574\uc11c BUFFER_POOL_KEEP\uacfc BUFFER_POOL_RECYCLE\uc774\ub77c\ub294 parameter\uac00 \uc874\uc7ac\ud558\uba70, DB_BLOCK_BUFFERS\uc640 DB_BLOCK_LRU_LATCHES parameter\ub3c4 \ud568\uaed8 \uace0\ub824\ud558\uc5ec\uc57c \ud55c\ub2e4.
                          \n<\/span><\/p>\n

                          syntax\ub294 \ub2e4\uc74c\uacfc \uac19\ub2e4.
                          \n<\/span><\/p>\n

                          \n <\/p>\n

                          BUFFER_POOL_KEEP=(buffers:<value>,lru_latches:<value>) \ud639\uc740
                          \n<\/span><\/p>\n

                          BUFFER_POOL_KEEP=<value>
                          \n<\/span><\/p>\n

                          BUFFER_POOL_RECYCLE=(buffers:<value>,lru_latches:<value>) \ud639\uc740
                          \n<\/span><\/p>\n

                          BUFFER_POOL_RECYCLE=<value>
                          \n<\/span><\/p>\n

                          \n <\/p>\n

                          \uc704\uc758 syntax\uc5d0\uc11c \ubcf4\ub294 \ubc14\uc640 \uac19\uc774 \uac01 pool\uc5d0 \ub300\ud574\uc11c buffer\uc758 \uac2f\uc218 \ubfd0 \uc544\ub2c8\ub77c LRU latch\uc758 \uac2f\uc218\ub3c4 \uc9c0\uc815\ud560 \uc218 \uc788\ub2e4. \ub9cc\uc57d \uc9c0\uc815\ud558\uc9c0 \uc54a\uc73c\uba74 \uadf8 pool\uc5d0 \ub300\ud574\uc11c \ud558\ub098\uc758 latch\uac00 \ud560\ub2f9\ub418\ub294 \uac83\uc774\ub2e4.
                          \n<\/span><\/p>\n

                          DEFAULT pool\uc5d0 \ub300\ud574\uc11c\ub294 \uba85\uc2dc\uc801\uc73c\ub85c block\uc758 \uac2f\uc218\ub098 latch\uc758 \uac2f\uc218\ub97c \uc9c0\uc815\ud560 \uc218 \uc5c6\uace0, \ub300\uc2e0 \uc804\uccb4 block\uc758 \uac2f\uc218 (DB_BLOCK_BUFFERS)\uc640 \uc804\uccb4 LRU latch\uc758 \uac2f\uc218 (DB_BLOCK_LRU_LATCHES)\uc5d0\uc11c KEEP\uacfc RECYCLE\uc5d0 \ud560\ub2f9\ub41c \uac01\uac01\uc758 \uac12\uc744 \ube80 \uac83\ub9cc\ud07c default pool\uc5d0 \ud560\ub2f9\ub41c\ub2e4.
                          \n<\/span><\/p>\n

                          \uac04\ub2e8\ud55c \uc608\uc81c\ub85c \uc124\uba85\ud558\uba74 \ub2e4\uc74c\uacfc \uac19\ub2e4.
                          \n<\/span><\/p>\n

                          \uc608\ub97c \ub4e4\uc5b4 initSID.ora file\uc5d0 \ub2e4\uc74c\uacfc \uac19\uc774 parameter\uac00 \uc124\uc815\ub418\uc5b4 \uc788\ub2e4\uace0 \uac00\uc815\ud55c\ub2e4.
                          \n<\/span><\/p>\n

                          \n <\/p>\n

                          DB_BLOCK_BUFFERS=1000
                          \n<\/span><\/p>\n

                          DB_BLOCK_LRU_LATCHES=6
                          \n<\/span><\/p>\n

                          BUFFER_POOL_KEEP=(buffers:400,lru_latches:2)
                          \n<\/span><\/p>\n

                          BUFFER_POOL_RECYCLE=100
                          \n<\/span><\/p>\n

                          \n <\/p>\n

                          \uc774\ub7ec\ud55c \uacbd\uc6b0 KEEP pool\uc5d0 \ub300\ud574\uc11c\ub294 400\uac1c\uc758 block\uacfc 2\uac1c\uc758 LRU latch\uac00 \ud560\ub2f9\ub418\uace0 RECYCLE pool\uc5d0\ub294 100\uac1c\uc758 block\uacfc 1\uac1c\uc758 LRU latch\uac00 \ud560\ub2f9\ub41c\ub2e4. \uadf8\ub9ac\uace0 DEFAULT pool\uc5d0\ub294 500 (1000-400-100) \uac1c\uc758 block\uacfc 3 (6-2-1)\uac1c\uc758 LRU latch\uac00 \ud560\ub2f9\ub418\uac8c \ub41c\ub2e4. \uac01 LRU queue\uc5d0 \ub300\ud574\uc11c block\uc740 \uade0\ub4f1\ud558\uac8c \ubc30\ubd84\ub41c\ub2e4. \uc989, \uc774 \uc608\uc5d0\uc11c DEFAULT queue\ub294 LRU 1\ubc88\uc774 167\uac1c\uc758 block\uc744 LRU2\ub3c4 167\uac1c, \uadf8\ub9ac\uace0 LRU3\uc740 166\uac1c\uc758 block\uc744 \uac00\uc9c0\uac8c \ub418\uba70, KEEP queue\ub294 \ub450\uac1c\uc758 latch\uac00 \uac01\uac01 200\uac1c\uc529\uc758 block\uc744 \uadf8\ub9ac\uace0 RECYCLE queue\ub294 100\uac1c\uc758 block\uc744 \uac00\uc9c0\uac8c \ub41c\ub2e4. \uc774\ub7ec\ud55c \uc815\ubcf4\ub294 v$buffer_pool\uc744 \ud1b5\ud574 \ud655\uc778\uc774 \uac00\ub2a5\ud558\uba70, \uc774 \uc608\uc758 \uacbd\uc6b0 \ub2e4\uc74c\uacfc \uac19\uc774 \uc870\ud68c\ub41c\ub2e4. \uc5ec\uae30\uc5d0\uc11c set_count\uac00 \uac01 pool\uc5d0 \ud560\ub2f9\ub41c latch\uc758 \uac2f\uc218\uc774\uba70, lo_bnum\uacfc hi_bnum\uc774 buffer\uc758 range\uc774\ub2e4.
                          \n<\/span><\/p>\n

                          \n <\/p>\n

                          SQL> select * from v$buffer_pool;
                          \n<\/span><\/p>\n

                          \n <\/p>\n

                          NAME     LO_SETID HI_SETID     SET_COUNT BUFFERS LO_BNUM HI_BNUM
                          \n<\/span><\/p>\n

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

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

                          KEEP 4 5 2 400 0 399
                          \n<\/span><\/p>\n

                          RECYCLE 6 6      1 100 400 499
                          \n<\/span><\/p>\n

                          DEFAULT 1     3 3 500 500 999
                          \n<\/span><\/p>\n

                          \n <\/p>\n

                          \uac01 queue\ub294 \ucd5c\uc18c 50\uac1c\uc758 block\uc740 \ud560\ub2f9\ubc1b\uc544\uc57c \ud558\uba70, \uadf8\ub807\uc9c0 \uc54a\uc740 \uacbd\uc6b0\uc5d0\ub294 \uc624\ub958\uac00 \ubc1c\uc0dd\ud55c\ub2e4. \uc989 \uc608\ub97c \ub4e4\uc5b4, BUFFER_POOL_KEEP=(buffers:100, lru_latches:3)\uacfc \uac19\uc774 \uc124\uc815\ud558\uba74 alert.log file\uc5d0 “Incorrect parameter specification for BUFFER_POOL_KEEP”\uc774\ub77c\ub294 \uc624\ub958 \uba54\uc2dc\uc9c0\uac00 \uc801\ud788\uac8c \ub418\uba70, 100\uac1c\uc758 block\uc5d0 \ub300\ud574\uc11c \ucd5c\ub300 \ub450\uac1c\uc758 LRU latch\ub9cc\uc774 \uac00\ub2a5\ud558\uac8c \ub41c\ub2e4.
                          \n<\/span><\/p>\n

                          \n <\/p>\n

                          BUFFER_POOL\uc774\ub77c\ub294 Oracle8\uc5d0\uc11c \uc0c8\ub85c \ucd94\uac00\ub41c storage \uc808\uc758 parameter\ub97c \uc774\uc6a9\ud558\uc5ec segment\uac00 \uc0ac\uc6a9\ud560 default pool\uc744 \uc9c0\uc815\ud560 \uc218 \uc788\ub2e4. segment\uc758 \ubaa8\ub4e0 block\uc740 \uc9c0\uc815\ub41c pool\uc744 \uc0ac\uc6a9\ud558\uac8c \ub418\uba70, \uc544\ub798\uc758 \uc608\uc81c\uc640 \uac19\uc774 \uc0ac\uc6a9\ud558\uba74 \ub41c\ub2e4.
                          \n<\/span><\/p>\n

                          \n <\/p>\n

                          CREATE TABLE keep_table(t NUMBER(10)) STORAGE (BUFFER_POOL KEEP);
                          \n<\/span><\/p>\n

                          ALTER TABLE recycle_table storage(BUFFER_POOL RECYCLE);
                          \n<\/span><\/p>\n

                          \n <\/p>\n

                          BUFFER_POOL\uc740 tablespace\ub098 rollback segment\uc5d0 \ub300\ud574\uc11c\ub294 \uc9c0\uc815\ud560 \uc218 \uc5c6\uc73c\uba70, clustered table\uc5d0 \ub300\ud574\uc11c\ub294 cluster level\uc5d0\uc11c\ub9cc \uc9c0\uc815\uc774 \uac00\ub2a5\ud558\ub2e4. partition table\uc5d0 \ub300\ud574\uc11c\ub294 \uac01 partition\ubcc4\ub85c pool\uc744 \uc9c0\uc815\ud558\ub294 \uac83\uc774 \uac00\ub2a5\ud558\ub2e4.
                          \n<\/span><\/p>\n

                          \uc77c\ub2e8 segments\uac00 \uc801\ub2f9\ud55c pool\uc5d0 \ud560\ub2f9\uc774 \ub418\uace0 \ub09c \ud6c4\uc5d0\ub294, logical hit ratio\ub098 free buffer waits\uc640 \uac19\uc740 \ub2e4\uc591\ud55c \ud1b5\uacc4\uc815\ubcf4\uac00 \ud655\uc778\uac00\ub2a5\ud558\ub2e4. \uc774\ub7ec\ud55c \ud1b5\uacc4 \uc815\ubcf4\ub97c \ub2f4\uacfc \uc788\ub294 view\ub294 v$buffer_pool_statistics\uc774\uba70, \uc774 view\ub294 $ORACLE_HOME\/rdbms\/admin\/catperf.sql\uc744 \uc218\ud589\ud558\uba74 \uc0dd\uc131\ub41c\ub2e4.
                          \n<\/span><\/p>\n

                          [\ucc38\uace0]\ud604\uc7ac\uae4c\uc9c0\ub294 utlbstat\/utlestat\uc5d0\uc11c\ub294 \uac01 pool\ubcc4\ub85c \ud1b5\uacc4\uc815\ubcf4\ub97c \uc0dd\uc131\ud558\uc9c0 \ubabb\ud558\uace0 \uc788\ub2e4.
                          \n<\/em><\/span><\/p>\n

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

                        4. \n
                          \n

                          \uc5d0\uc11c\uc758 DBWR
                          \n<\/span><\/h1>\n<\/div>\n

                          Oracle\uc5d0\uc11c\uc758 db_writers\ub294 master-slave processing\uc744 \ud1b5\ud574, async I\/O\ub97c simulate\ud558\uae30 \uc704\ud574 \uc0ac\uc6a9\ub418\uc5c8\ub2e4\uace0 \ubcfc \uc218 \uc788\ub2e4. Oracle8\uc5d0\uc11c DBWR\uc758 write processing\uc5d0 \ub354 \ub098\uc740 \uc131\ub2a5\uc744 \uc81c\uacf5\ud558\uae30 \uc704\ud574 \ubcf5\uc218\uac1c\uc758 database writer\ub97c \uc0ac\uc6a9\ud558\ub294 \ubc29\ubc95\uc740 \ub2e4\uc74c\uacfc \uac19\uc774 \ub450\uac00\uc9c0\ub85c \ub098\ub20c \uc218 \uc788\uc73c\uba70 \uc774 \ub450 \ubc29\ubc95\uc740 mutual exclusive\ud558\ub2e4.
                          \n<\/span><\/p>\n

                          [\ucc38\uace0] \uc2e4\uc81c test\ud574\ubcf8 \uacb0\uacfc \ud568\uaed8 \uc0ac\uc6a9\ud558\uba74 dbwr_io_slaves\ub9cc \ud6a8\uacfc\uac00 \uc788\uc5c8\ub2e4. \uc774\uac83\uc740 dbwr_io_slaves\ub294 master dbwr process\ub97c db_writer_proceses\uc5d0 \uad00\uacc4\uc5c6\uc774 \ud558\ub098\ub9cc \uac00\uc9c0\ub3c4\ub85d \ub418\uc5b4 \uc788\uae30 \ub54c\ubb38\uc774\ub2e4.
                          \n<\/em><\/span><\/p>\n

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

                        5. \n
                          \n

                          DBWR IO slaves
                          \n<\/span><\/h2>\n<\/div>\n

                          Oracle7\uc5d0\uc11c\uc758 mulitple DBWR process\ub4e4\uc740 \ub2e8\uc21c\ud788 slave process\ub85c\uc368, async I\/O call\uc744 \uc218\ud589\ud560 \uc218\ub294 \uc5c6\uc5c8\ub2e4. Oracle 8.0.3\ubd80\ud130, slave database writer code\uac00 kernal\uc5d0 \ud3ec\ud568\ub418\uc5c8\uace0, slave process\uc758 async I\/O\uac00 \uac00\ub2a5\ud558\uac8c \ub418\uc5c8\ub2e4. \uc774 \uac83\uc740 init.ora file\ub0b4\uc5d0 dbwr_io_slaves\ub77c\ub294 parameter\ub97c \ud1b5\ud574 \uac00\ub2a5\ud558\uba70, IO slave\uac00 asynchronous I\/O\uac00 \uac00\ub2a5\ud558\uc5ec I\/O call\uc774\ud6c4\uc5d0 slave\uac00 block\ub418\uc9c0 \uc54a\uc544 \ub354 \ub098\uc740 \uc131\ub2a5\uc744 \uc81c\uacf5\ud55c\ub2e4\ub294 \uac83\uc744 \uc81c\uc678\ud558\uace0\ub294 Oracle7\uacfc \ub9e4\uc6b0 \uc720\uc0ac\ud558\ub2e4. slave process\ub294 instance\uc0dd\uc131\uc2dc\uae30\uac00 \uc544\ub2cc database open\uc2dc\uc5d0 start\ub418\uae30 \ub54c\ubb38\uc5d0 oracle process id\uac00 9\ubc88\ubd80\ud130 \ud560\ub2f9\ub418\uba70, os\uc5d0\uc11c \ud655\uc778\ub418\ub294 process\uc774\ub984\ub3c4 ora_i10n_SID\uc640 \uac19\uc740 \ud615\ud0dc\uac00 \ub41c\ub2e4.
                          \n<\/span><\/p>\n

                          dbwr_io_slaves=3\uc73c\ub85c \uc9c0\uc815\ud55c \uacbd\uc6b0, \uc544\ub798\uc640 \uac19\uc740 oracle background process\uac00 \uad6c\ub3d9\ub418\uba70, ora_i101_V804, ora_i102_V804, ora_i103_V804\uc774 dbwr\uc758 slave process\ub4e4\uc774\ub2e4.
                          \n<\/span><\/p>\n

                          \n <\/p>\n

                          tcsol2% ps -ef | grep V804
                          \n<\/span><\/p>\n

                          usupport 5419 1 0 06:23:53 ? 0:00 ora_pmon_V804
                          \n<\/span><\/p>\n

                          usupport 5429 1 1 06:23:53 ? 0:00 ora_smon_V804
                          \n<\/span><\/p>\n

                          usupport 5421 1 0 06:23:53 ? 0:00 ora_dbw0_V804
                          \n<\/span><\/p>\n

                          usupport 5433 1 0 06:23:56 ? 0:00 ora_i101_V804
                          \n<\/span><\/p>\n

                          usupport 5423 1 0 06:23:53 ? 0:00 ora_arch_V804
                          \n<\/span><\/p>\n

                          usupport 5431 1 0 06:23:53 ? 0:00 ora_reco_V804
                          \n<\/span><\/p>\n

                          usupport 5435 1 0 06:23:56 ? 0:00 ora_i102_V804
                          \n<\/span><\/p>\n

                          usupport 5437 1 0 06:23:56 ? 0:00 ora_i103_V804
                          \n<\/span><\/p>\n

                          usupport 5425 1 0 06:23:53 ? 0:00 ora_lgwr_V804
                          \n<\/span><\/p>\n

                          usupport 5427 1 0 06:23:53 ? 0:00 ora_ckpt_V804
                          \n<\/span><\/p>\n

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

                        6. \n
                          \n

                          Multiple DBWR
                          \n<\/span><\/h2>\n<\/div>\n

                          multiple database writer\ub294 init.ora file\ub0b4\uc758 db_writer_processes\ub77c\ub294 parameter\uc5d0 \uc758\ud574 \uad6c\ud604\ub418\uba70, \uc774\uac83\uc740 Oracle 8.0.4\ubd80\ud130 \uc81c\uacf5\ub418\uc5c8\ub2e4. \uc774\uac83\uc740 \uae30\uc874\uc758 master-slave\uad00\uacc4\uac00 \uc544\ub2cc \uc9c4\uc815\ud55c \uc758\ubbf8\uc758 \ubcf5\uc218\uac1c\uc758 database writer\ub97c \uc0ac\uc6a9\ud558\ub294 \uac83\uc774\uba70, database writer process\ub4e4\uc740 PMON\uc774 start\ub41c \ud6c4\uc5d0 start\ub418\uc5b4\uc9c4\ub2e4. \uc774\ub984\uc740 ora_dbwn_SID\ud615\ud0dc\uc774\uba70, \uc544\ub798\uc5d0 db_block_lru_latches=2, db_writer_processes=2\ub85c \uc9c0\uc815\ud55c \uacbd\uc6b0 \uad6c\ub3d9\ub41c oracle background process\ub4e4\uc758 \uc608\uc774\ub2e4. \uc5ec\uae30\uc5d0\uc11c ora_dbw0_V804, dbw1_V804\uc774 dbwr process\ub4e4\uc774\ub2e4. \ub9cc\uc57d db_writer_processes\ub97c \uc9c0\uc815\ud558\uc9c0 \uc54a\uc73c\uba74 \uae30\ubcf8\uac12\uc740 1\uc778\ub370 \uc774\ub54c\ub3c4 Oracle7\uacfc \uac19\uc774 ora_dbwr_SID\ud615\ud0dc\uac00 \uc544\ub2cc ora_dbw0_SID\ud615\ud0dc\uc758 process\uac00 \uad6c\ub3d9\ub41c\ub2e4.
                          \n<\/span><\/p>\n

                          \n <\/p>\n

                          usupport 5522 1 0 06:31:39 ? 0:00 ora_dbw1_V804
                          \n<\/span><\/p>\n

                          usupport 5524 1 0 06:31:39 ? 0:00 ora_arch_V804
                          \n<\/span><\/p>\n

                          usupport 5532 1 0 06:31:39 ? 0:00 ora_reco_V804
                          \n<\/span><\/p>\n

                          usupport 5528 1 0 06:31:39 ? 0:00 ora_ckpt_V804
                          \n<\/span><\/p>\n

                          usupport 5530 1 0 06:31:39 ? 0:00 ora_smon_V804
                          \n<\/span><\/p>\n

                          usupport 5526 1 0 06:31:39 ? 0:00 ora_lgwr_V804
                          \n<\/span><\/p>\n

                          usupport 5520 1 0 06:31:39 ? 0:00 ora_dbw0_V804
                          \n<\/span><\/p>\n

                          usupport 5518 1 0 06:31:38 ? 0:00 ora_pmon_V804
                          \n<\/span><\/p>\n

                          \n <\/p>\n

                          db_writer_processes\ub85c \uc9c0\uc815\ub41c \uac01 writer process\ub294 \ud558\ub098\uc758 latch set\uc5d0 \ud560\ub2f9\ub41c\ub2e4. \uadf8\ub7ec\ubbc0\ub85c db_writer_processes\ub97c db_lru_block_latches\uc73c\ub85c \uc9c0\uc815\ub418\ub294 LRU latch\uc758 \uac2f\uc218\uc640 \uac19\uc740 \uac12\uc73c\ub85c \uc9c0\uc815\ud558\ub294 \uac83\uc774 \uad8c\uc7a5\ud560\ub9cc\ud558\uba70, \ub2e8 CPU\uc758 \uac2f\uc218\ub97c \ucd08\uacfc\ud558\ub294 \uac83\uc740 \ubc14\ub78c\uc9c1\ud558\uc9c0 \uc54a\ub2e4.
                          \n<\/span><\/p>\n

                          [\ucc38\uace0] \uc2e4\uc81c test\ud574\ubcf8 \uacb0\uacfc init.ora file\ub0b4\uc5d0 \uad6c\ub3d9\ub418\ub294 dbwr\uc758 \uac2f\uc218\ub294 db_block_lru_latches parameter\uc5d0 \uc758\ud574 \uc81c\ud55c\ub418\uc5c8\ub2e4. \uc989 db_writer_processes\uac12\uc744 db_block_lru_latches\ubcf4\ub2e4 \ud06c\uac8c \ud558\uc5ec\ub3c4 db_block_lru_latches\uac12 \uc774\uc0c1\uc73c\ub85c db_block_lru_latches\ub85c \uc9c0\uc815\ub41c \uc218\uc758 dbwr process\uac00 \uae30\ub3d9\ub418\uba70, \uc774\uac83\uc740 \ud604\uc7ac bug\uc778\uc9c0, \uc6d0\ub798\uc758 action\uc778\uc9c0\uac00 bugdb\ub0b4\uc5d0\uc11c\ub3c4 \ub4f1\ub85d\ub9cc\ub41c \uc0c1\ud0dc\uace0 \uacb0\uc815\uc774 \ub098\uc9c0 \uc54a\uc740 \uc0c1\ud0dc\uc774\ub2e4. PR\uc5d0\ub294 db_block_lru_latches problem solution\uc73c\ub85c \ub4f1\ub85d\ub418\uc5b4 \uc788\ub2e4.
                          \n<\/em><\/span><\/p>\n

                          \n <\/p>\n

                          Oracle\uc5d0\uc11c DBWR I\/O slave\ub098 \ubcf5\uc218\uac1c\uc758 DBWR\ub97c \uc81c\uacf5\ud558\ub294 \ubc29\ubc95 \uc911 \uc88b\uc740 \uc810\uc740 \uc774 \uae30\ubc95\uc744 \uc81c\uacf5\ud558\ub294 \uac83\uc774 kernal\uc548\uc5d0 \ud3ec\ud568\ub418\uc5b4 \uae30\uc874\uc758 OSD layer\ub85c \uad6c\ud604\ub418\uc5c8\ub358\uac83\ubcf4\ub2e4 port specific\ud55c \ubd80\ubd84\uc774 \uc5c6\uace0 generic\ud558\ub2e4\ub294 \uac83\uc774\ub2e4.
                          \n<\/span><\/p>\n

                          \n <\/p>\n

                          \uc774\ub7ec\ud55c \ub450\uac00\uc9c0 \ud615\ud0dc\uc758 DBWR \uae30\ubc95\uc774 \ubaa8\ub450 \ub3c4\uc6c0\uc774 \ub418\uae30\ub294 \ud558\uc9c0\ub9cc, \uc77c\ubc18\uc801\uc73c\ub85c \uc5b4\ub290\uac83\uc744 \uc0ac\uc6a9\ud560 \uac83\uc778\uc9c0\ub294 OS level\uc5d0\uc11c asynchronous I\/O\uac00 \uc81c\uacf5\ud558\ub294\uc9c0\uc640 CPU\uac2f\uc218\uc5d0 \uc758\uc874\ud55c\ub2e4. \uc989, system\uc774 \ubcf5\uc218\uac1c\uc758 CPU\ub97c \uac00\uc9c0\uace0 \uc788\uc73c\uba74 db_writer_processes\ub97c \uc0ac\uc6a9\ud558\ub294 \uac83\uc774 \ubc14\ub78c\uc9c1\ud558\uba70, aync I\/O\ub97c \uc81c\uacf5\ud558\ub294 \uacbd\uc6b0 \ub450\uac00\uc9c0 \ubaa8\ub450 \uc0ac\uc6a9\uc5d0 \ud6a8\uacfc\ub97c \uc5bb\uc744 \uc218 \uc788\ub2e4. \uadf8\ub7f0\ub370 \uc5ec\uae30\uc11c \uc8fc\uc758\ud560 \uac83\uc740 db_io_slaves\uac00 \uc57d\uac04\uc758 overhead\uac00 \uc788\ub2e4\ub294 \uac83\uc774\ub2e4. slave IO process\ub97c \uac00\ub2a5\ud558\uac8c \ud558\uba74, IO buffer\uc640 request queue\uc758 \ud560\ub2f9\uc744 \uc704\ud574 \ubd80\uac00\uc801\uc778 shred memory\uac00 \ud544\uc694\ud558\ub2e4. multiple writer processes\uc640 IO slave\ub294 \ub9e4\uc6b0 \ubd80\ud558\uac00 \ub9ce\uc740 OLTP\ud658\uacbd\uc5d0\uc11c \uc801\ud569\ud558\uba70, \uc77c\uc815 \uc218\uc900 \uc774\uc0c1\uc758 \uc131\ub2a5\uc744 \uc694\uad6c\ud560 \ub54c\ub9cc \uc0ac\uc6a9\ud558\ub3c4\ub85d \ud55c\ub2e4. \uc608\ub97c \ub4e4\uc5b4 async I\/O\uac00 \uc0ac\uc6a9\uac00\ub2a5\ud55c \uacbd\uc6b0, I\/O slave\ub3c4 \uc0ac\uc6a9\ud558\uc9c0 \uc54a\uace0 \ud558\ub098\uc758 DBWR\ub9cc\uc744 async I\/O mode\ub85c \uc0ac\uc6a9\ud558\ub294 \uac83\uc774 \ucda9\ubd84\ud558\uace0 \ubc14\ub78c\uc9c1\ud560 \uc218 \uc788\ub2e4. \ud604\uc7ac\uc758 \uc131\ub2a5\uc744 \uc870\uc0ac\ud558\uace0 bottleneck\uc774 \ub418\ub294 \ubd80\ubd84\uc774 DBWR\ubd80\ubd84\uc778\uc9c0 \uc815\ud655\ud788 \uc870\uc0ac\ud55c \ud6c4 \uc0ac\uc6a9\ud558\uc5ec\uc57c \ud55c\ub2e4.
                          \n<\/span><\/p>\n

                          [\ucc38\uace0] \uc2e4\uc81c Oracle7\uc5d0\uc11c\ubd80\ud130 async mode\uc5d0\uc11c \ubcf5\uc218\uac1c\uc758 DBWR\ub97c \uc0ac\uc6a9\uc2dc\uc5d0 Oracle bug\uc744 \ud3ec\ud568\ud55c \ub9ce\uc740 \ubb38\uc81c\uac00 \uc788\uc5b4 \uc654\uc73c\ubbc0\ub85c \uc774\uac83\uc744 \uc0ac\uc6a9\uc2dc\uc5d4 \uc815\ud655\ud55c \uad00\ucc30\uacfc test\uac00 \uc694\uad6c\ub418\uc5b4\uc9c4\ub2e4.
                          \n<\/em><\/span><\/p>\n

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

                        7. \n
                          \n

                          \ucca8\ubd80
                          \n<\/span><\/h1>\n<\/div>\n

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

                        8. \n
                          \n

                          underbar parameter\uc758 \uac12\uc744 \ud655\uc778\ud558\ub294 \ubc29\ubc95
                          \n<\/span><\/h2>\n<\/div>\n

                          _ parameter\ub97c \ud3ec\ud568\ud55c parameter\uc758 \uc774\ub984\uacfc \uc758\ubbf8\ub294 X$KSPPI table\uc5d0 \ub4e4\uc5b4 \uc788\uc73c\uba70, \uadf8 \uac12\uc740 X$KSPPSV \ud639\uc740 X$KSPPCV\uc5d0 \ub4e4\uc5b4 \uc788\ub2e4. \uc608\ub97c \ub4e4\uc5b4 _db_block_write_batch\uc758 \uac12\uc744 \ud655\uc778\ud558\ub824\uba74 \ub2e4\uc74c\uacfc \uac19\uc774 \uc870\ud68c\uac00\ub2a5\ud558\uba70, \uc774\ub54c X$KSPPSV\ub300\uc2e0 X$KSPPCV\ub97c \uc0ac\uc6a9\ud558\uc5ec\ub3c4 \uac19\uc740 \uacb0\uacfc\ub97c \uc5bb\uac8c \ub41c\ub2e4.
                          \n<\/span><\/p>\n

                          \n <\/p>\n

                          SQL> select a.ksppinm, b.ksppstvl from x$ksppi a, x$ksppsv b
                          \n<\/span><\/p>\n

                          where a.indx=b.indx
                          \n<\/span><\/p>\n

                          and a.ksppinm like ‘%batch%’;
                          \n<\/span><\/p>\n

                          \n <\/p>\n

                          KSPPINM         KSPPSTVL KSPPDESC
                          \n<\/span><\/p>\n

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

                          _trace_write_batch_size    32 trace write batch size
                          \n<\/span><\/p>\n

                          _db_block_write_batch        0 Number of blocks to group in each DB Writer IO
                          \n<\/span><\/p>\n

                          db_block_checkpoint_batch    8 Max number of blocks to checkpoint in a DB Writer IO
                          \n<\/span><\/p>\n

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

                        9. \n
                          \n

                          Buffer Cache\uc5d0 \uad00\ud574 database\ub0b4\uc5d0 \uc9c0\uc815\ub41c \uac12 \ud655\uc778
                          \n<\/span><\/h2>\n<\/div>\n

                          SQL> select kviidsc, kviival from x$kvii;
                          \n<\/span><\/p>\n

                          \n <\/p>\n

                          KVITDSC KVITVAL
                          \n<\/span><\/p>\n

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

                          large dirty queue if kcbclw reaches this     3
                          \n<\/span><\/p>\n

                          free buffer wanted request sent flag         0
                          \n<\/span><\/p>\n

                          number buffer headers         5000
                          \n<\/span><\/p>\n

                          number of recent buckets         0
                          \n<\/span><\/p>\n

                          DBWR blocks to scan looking for dirty         0
                          \n<\/span><\/p>\n

                          DBWR blocks to scan lowest value         10
                          \n<\/span><\/p>\n

                          DBWR blocks to scan highest value         160
                          \n<\/span><\/p>\n

                          DBWR scan depth increment             19
                          \n<\/span><\/p>\n

                          DBWR scan depth decrement         1
                          \n<\/span><\/p>\n

                          foreground blocks to scan looking for free         1250
                          \n<\/span><\/p>\n

                          background completion count         0
                          \n<\/span><\/p>\n

                          Error Log Number for thread open         0
                          \n<\/span><\/p>\n

                          SGA: true if a CheckPoint is Active         0
                          \n<\/span><\/p>\n

                          SGA: true if a Global Checkpoint is Waiting         0
                          \n<\/span><\/p>\n

                          SGA: true if ChkPt buf writes Done             1
                          \n<\/span><\/p>\n

                          SGA: true if CheckPointing Fast             0
                          \n<\/span><\/p>\n

                          number of latches         53
                          \n<\/span><\/p>\n

                          event range base         2185
                          \n<\/span><\/p>\n

                          # of base events         104
                          \n<\/span><\/p>\n

                          number of CPUs in the system         2
                          \n<\/span><\/p>\n

                          number of hash queue latch structures         1259
                          \n<\/span><\/p>\n

                          number of current buckets         0
                          \n<\/span><\/p>\n

                          DB writer IO clump             40
                          \n<\/span><\/p>\n

                          DB writer checkpoint clump             8
                          \n<\/span><\/p>\n

                          # background lock processes             1
                          \n<\/span><\/p>\n

                          number of hash locks         613
                          \n<\/span><\/p>\n

                          true if Statically Allocated Thread         0
                          \n<\/span><\/p>\n

                          THRead mounted by this instance – zero if none         1
                          \n<\/span><\/p>\n

                          sga shadow value of instance_number             0
                          \n<\/span><\/p>\n

                          min # of transaction free list             25
                          \n<\/span><\/p>\n

                          max undo class             46
                          \n<\/span><\/p>\n

                          number of MTS queues             12
                          \n<\/span><\/p>\n

                          \n <\/p>\n

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

                        10. \n
                          \n

                          v$sysstat\uc758 \ud1b5\uacc4 \uc815\ubcf4\uc758 \uc758\ubbf8 \uc815\ub9ac
                          \n<\/span><\/h2>\n<\/div>\n

                          \uc544\ub798\uc5d0 \ub098\uc5f4\ub41c \uac83\uc740 v$sysstat\uc774\ub098, bstat\/estat\uc5d0\uc11c buffer cache management\uc5d0 \uc601\ud5a5\uc744 \ubbf8\uce58\ub294 \uac83\ub4e4\uc5d0 \ub300\ud574 \uac04\ub2e8\ud788 \ub098\uc5f4\ud558\uc600\ub2e4.
                          \n<\/span><\/p>\n

                          \n <\/p>\n<\/li>\n<\/ol>\n

                            \n
                          • \n
                            cluster key scan block gets\/cluster scans<\/strong> = cluster key chaining\uc5d0 \ub300\ud55c chaining.
                            \n<\/span><\/div>\n

                            \ub9cc\uc57d \uc774 \uac12\uc774 1\ubcf4\ub2e4 \ud06c\uba74 chaining\uc774 \uc874\uc7ac\ud558\ub294 \uac83\uc774\ub2e4.
                            \n<\/span><\/p>\n

                              \n
                            • \n
                              cluster Key Scan Block Gets<\/strong> – acess\ub41c cluster block\uc758 \uac2f\uc218
                              \n<\/span><\/div>\n<\/li>\n
                            • \n
                              cluster Key Scans<\/strong> – cluster block\ub97c scan\ud55c \ud69f\uc218
                              \n<\/span><\/div>\n<\/li>\n<\/ul>\n<\/li>\n
                            • \n
                              consistent gets <\/strong>+ db<\/strong>_block_gets<\/strong> = logical reads<\/strong>
                              \n\t\t\t\t<\/span><\/div>\n

                              (logical_reads<\/strong> \/ (logical_reads<\/strong> + physical reads <\/strong>) ) * 100 = logical hitratio<\/strong>
                              \n\t\t\t\t<\/span><\/p>\n

                              \uc774 \uac12\uc740 filesystem\uc5d0 \ub300\ud574\uc11c\ub294 85-90%, raw device\uc5d0 \ub300\ud574\uc11c\ub294 90-95% \uc815\ub3c4\ub294 \uc720\uc9c0\ud558\uc5ec\uc57c \ud55c\ub2e4.
                              \n<\/span><\/p>\n<\/li>\n

                            • \n
                              DBWRs free buffers found<\/strong> – DBWR\uac00 free buffer\ub97c \ub9cc\ub4e4\uae30 \uc704\ud574 LRU list\uc758 \ub05d\ubd80\ubd84\ubd80\ud130 \uc870\ud68c\uc2dc \ubc1c\uacac\ud55c free buffer\uc758 \uac2f\uc218. fee buffer\ub97c \ud655\ubcf4\ud558\uae30 \uc704\ud574 scan\ud558\ub294 \uacbd\uc6b0\uac00 \uc544\ub2cc \ub2e4\ub978 \ubaa9\uc801\uc758 LRU scan\uc5d0 \ub300\ud574\uc11c\ub294 \uc774 \uac12\uc774 \uc601\ud5a5\uc744 \ubc1b\uc9c0 \uc54a\ub294\ub2e4.
                              \n<\/span><\/div>\n
                                \n
                              • \n
                                DBWR make free requests<\/strong> – DBWR\ub85c \ud558\uc5ec\uae08 LRU list\ub0b4\uc5d0 free buffer\ub97c \ud655\ubcf4\ud558\uae30 \uc704\ud574\uc11c dirty buffer\ub97c LRUW\ub85c \uc62e\uae30\ub3c4\ub85d request\ud55c \ud69f\uc218
                                \n<\/span><\/div>\n<\/li>\n
                              • \n
                                DBWR free buffers found <\/strong>\/ DBWR make free requests<\/strong> = LRU list\uc758 \ub05d\ubd80\ubd84\uc5d0 \uc704\uce58\ud55c reusable buffer\uc758 \ud3c9\uade0 \uac12
                                \n<\/span><\/div>\n<\/li>\n
                              • \n
                                dirty buffers inspected – <\/strong>foreground process\uac00 \uc7ac\uc0ac\uc6a9\ud560 buffer\ub97c \ucc3e\uae30 \uc704\ud574 LRU list\uc5d0\uc11c \ub9cc\ub09c dirty buffer\uc758 \uac2f\uc218. \uc774 \uac12\uc774 \uc791\uc544\uc57c DBWR\uac00 foreground process\uc5d0 \ub4a4\uc9c0\uc9c0 \uc54a\uac8c \uc218\ud589\ub418\uace0 \uc788\ub294 \uac83\uc774\ub2e4.
                                \n\t\t\t\t\t\t\t<\/em><\/span><\/div>\n<\/li>\n<\/ul>\n<\/li>\n
                              • \n
                                free buffer inspected <\/strong>\/ DBWR buffers scanned<\/strong> = Free Buffer Scan Ratio\ub77c\uace0\ub3c4 \ubd88\ub9ac\uba70, buffer cache\ub0b4\uc5d0\uc11c \ubcc0\uacbd\ub41c block\uc758 \ube44\uc728\uc774\ub2e4.
                                \n<\/span><\/div>\n

                                \ub9cc\uc57d \uc774 \uac12\uc774 \ud06c\ub2e4\uba74 buffer cache\ub0b4\uc5d0 unusable buffer\uac00 \ub108\ubb34 \ub9ce\uc544\uc11c DBWR\uc774 \ucad2\uc544\uac00\uc9c0 \ubabb\ud558\ub294 \uac83\uc774\ub2e4. \uc774\ub7f0 \uacbd\uc6b0\uc5d0 db_block_buffer\uc758 \uac12\uc744 \ub298\ub824\uc57c \ud560 \ud544\uc694\uac00 \uc788\ub2e4.
                                \n<\/span><\/p>\n

                                  \n
                                • \n
                                  dirty buffers inspected – user\uac00 \uc7a1\uace0 \uc788\ub294 buffer\uc758 \uac2f\uc218
                                  \n<\/span><\/div>\n<\/li>\n
                                • \n
                                  free buffers inspected – free buffer\ub97c \ucc3e\uae30 \uc704\ud574 DBWR\uac00 skip \ud55c buffer\uc758 \uac2f\uc218
                                  \n<\/span><\/div>\n

                                  \uc774 \uac12\uc740 dirty buffers inspected \uac12\uc744 \ud3ec\ud568\ud558\ub294 \uac83\uc73c\ub85c pinned buffer\ub098 dirty buffer\ub97c \ud3ec\ud568\ud55c\ub2e4.
                                  \n<\/span><\/p>\n<\/li>\n

                                • \n
                                  DBWR buffers scanned – LRU list\uc5d0\uc11c buffer\ub97c \ube44\uc6b0\uae30 \uc704\ud574 scan\ud55c \uc804\uccb4 buffer\uc758 \uac2f\uc218
                                  \n<\/span><\/div>\n<\/li>\n<\/ul>\n<\/li>\n
                                • \n
                                  free buffers requested<\/strong> – block\uc744 \uc0dd\uc131\ud558\uace0 load\ud558\uae30 \uc704\ud574 free buffer\uac00 \uc694\uccad\ub41c \ud69f\uc218
                                  \n<\/span><\/div>\n<\/li>\n
                                • \n
                                  summed dirty queue length<\/strong>\/write requests<\/strong> = dirty list\uc758 \ud3c9\uade0 \uae38\uc774. \uc774 \uac12\uc740 _db_block_write_batch<\/em> parameter\uc5d0 \uc758\ud574 \uc124\uc815\ub420 \uc218 \uc788\ub2e4.
                                  \n<\/span><\/div>\n<\/li>\n
                                • \n
                                  table fetch by rowid<\/strong> – rowid\uc5d0 \uc758\ud574 table\uc5d0\uc11c fetch\ub41c row\uc758 \uac2f\uc218. \uc989 rowid= \uc5f0\uc0b0\uc774\ub098 index\ub97c \ud1b5\ud55c \uc870\ud68c\uc778 \uacbd\uc6b0\uac00 \ud574\ub2f9\ub41c\ub2e4.
                                  \n<\/span><\/div>\n
                                    \n
                                  • \n
                                    table fetch continued row <\/strong>– \ubc1c\uacac\ub41c chained row\uc758 \uac2f\uc218
                                    \n<\/span><\/div>\n<\/li>\n
                                  • \n
                                    table fetch continued row to table fetch by rowid <\/strong>\ube44\uc728\uc774 1:1000\uc815\ub3c4\ub294 \ub418\uc5b4\uc57c \ud558\uba70, database\uac00 long datatype\uc744 \uac00\uc9c0\uc9c0 \uc54a\ub294 \uacbd\uc6b0\ub294 \uc608\uc678\uc774\ub2e4.
                                    \n<\/span><\/div>\n<\/li>\n<\/ul>\n<\/li>\n
                                  • \n
                                    table scan blocks gotten \uacfctable scan rows gotten <\/strong>
                                    \n\t\t\t\t<\/span><\/div>\n

                                    \uac01\uac01 full table scan\uc2dc fetch\ub41c \uc804\uccb4 block\uc758 \uc218\uc640 row\uc758 \uc218\uc774\ub2e4. \ud55c block\ub2f9 \ud3c9\uade0 row\uc758 \uc218\ub97c \uad6c\ud558\uae30 \uc704\ud574 \uc774\uc6a9\ub41c\ub2e4.
                                    \n<\/span><\/p>\n

                                    full table scan\uc758 \uacbd\uc6b0
                                    \n<\/span><\/p>\n

                                    table scan rows gotten<\/strong> \/ table scan blocks gotten * 100 – <\/strong>DSS system\uc758 \uacbd\uc6b0 \uc774 \uac12\uc774 \ucee4\uc57c \ud55c\ub2e4.
                                    \n<\/span><\/p>\n

                                      \n
                                    • \n
                                      Table Scans (short) * 5 blocks = Blocks Scanned (short)
                                      \n<\/span><\/div>\n<\/li>\n
                                    • \n
                                      Table Scan Blocks Gotten – Blocks Scanned (short) = Blocks Scanned (long)
                                      \n<\/span><\/div>\n<\/li>\n
                                    • \n
                                      locks Scanned (long) \/ Table Scans (long tables) = \ud558\ub098\uc758 long table\uc5d0 \ub300\ud574\uc11c scan\ud55c block\uc758 \ud3c9\uade0 \uac2f\uc218
                                      \n<\/span><\/div>\n<\/li>\n<\/ul>\n<\/li>\n
                                    • \n
                                      table scans (long tables)<\/strong> – 5\uac1c \uc774\uc0c1\uc758 block\uc744 \uac00\uc9c0\uace0 \uc788\ub294 table\uc5d0 \ub300\ud574\uc11c full table scan\uc774 \uc77c\uc5b4\ub09c \ud69f\uc218. “per trans”\uc5d0 \ub300\ud55c \uc774 \uac12, \uc989 \ud558\ub098\uc758 transaction\ub2f9 \uc774\ub8e8\uc5b4\uc9c4 \ud69f\uc218\uac00 0\ubcf4\ub2e4 \ucee4\uc11c\ub294 \uc548\ub41c\ub2e4. \ub9cc\uc57d 0\ubcf4\ub2e4 \ud06c\ub2e4\uba74 application\uc774 index\ub97c \uc0ac\uc6a9\ud558\ub3c4\ub85d \uac80\ud1a0\ud558\uc5ec\uc57c \ud55c\ub2e4.
                                      \n<\/span><\/div>\n<\/li>\n
                                    • \n
                                      table scans (short tables)<\/strong> – 5 block\ubbf8\ub9cc\uc778 table\uc5d0 \ub300\ud574 \uc218\ud589\ub41c full table scan\uc758 \ud69f\uc218
                                      \n<\/span><\/div>\n<\/li>\n
                                    • \n
                                      free buffer waits<\/strong> – \uc0ac\uc6a9 \uac00\ub2a5\ud55c buffer\uac00 \uc5c6\uc5b4\uc11c foreground process\uac00 \uae30\ub2e4\ub9b0 \ud69f\uc218. \uc774\uac83\uc740 DBWR\uac00 free buffers inspected limit\uc5d0 \ub3c4\ub2ec\ud55c \uacbd\uc6b0 \ubc1c\uc0dd\ud560 \uc218 \uc788\ub294\ub370, \uc774 \uac12\uc774 \ub108\ubb34 \ud06c\uba74, DBWR\uac00 disk\uc5d0 \ucda9\ubd84\ud788 write\ub97c \uc218\ud589\ud558\uc9c0 \ubabb\ud568\uc744 \ub098\ud0c0\ub0b8\ub2e4.
                                      \n<\/span><\/div>\n<\/li>\n
                                    • \n
                                      buffer busy waits<\/strong> – process\uac00 buffer\ub97c \uae30\ub2e4\ub9b0 \ud69f\uc218. buffer\uac00 \ub2e4\ub978 user\uc5d0 \uc758\ud574 cache\ub0b4\uc5d0 \uc77d\ud600\uc9c0\ub294 \uc911\uc774\uac70\ub098, buffer\uc758 status\uac00 \uc694\uccad\ud55c mode\uc640 \ud638\ud658\ub418\uc9c0 \uc54a\ub294 \uacbd\uc6b0\uc774\ub2e4.
                                      \n<\/span><\/div>\n

                                      (buffer busy waits)<\/strong>*100 \/ (db blocks gets + consistent gets<\/strong>) \uac12\uc740 5%\ubcf4\ub2e4 \ucee4\uc11c\ub294 \uc548\ub41c\ub2e4.
                                      \n<\/span><\/p>\n

                                      “select * from v$waitstat ” \ub97c \uc218\ud589\ud558\uc5ec \uc5b4\ub5a4 type\uc758 object\uac00 waiting \uc911\uc774\uc5c8\ub294\uc9c0 \ud655\uc778\ud558\uace0 \uc801\uc801\ud55c tuning\uc744 \uc218\ud589\ud558\uc5ec\uc57c \ud55c\ub2e4.
                                      \n<\/span><\/p>\n<\/li>\n

                                    • \n
                                      db file sequential read, db file scattered read, db file single write and db file parallel write<\/strong>
                                      \n\t\t\t\t<\/span><\/div>\n

                                      data files header, control file data file\uc5d0 \ub300\ud574 \uc218\ud589\ub41c I\/O\uc5d0 \ub300\ud55c \ubaa8\ub4e0 event\ub4e4. \ub9cc\uc57d \ub9cc\uc57d \uc774 wait event\ub4e4\uc774 Average Time\uc5d0 \ube44\ud574 \ud06c\ub2e4\uba74, sar\ub098 iostat\uc744 \uc774\uc6a9\ud558\uc5ec I\/O contention\uc744 \uc870\uc0ac\ud574\uc57c \ud55c\ub2e4.
                                      \n<\/span><\/p>\n<\/li>\n

                                    • \n
                                      cache buffer handles<\/strong> – This latch protects the State Objects that are needed by a process to make a change to a block\/buffer. Before the change a buffer handle is acquired either from the process’ private pool or the global pool if none are present. The access to the global pool is protected by this latch.
                                      \n<\/span><\/div>\n<\/li>\n
                                    • \n
                                      cache buffer chains<\/strong> – foreground process\uac00 buffer\ub97c \ubcc0\uacbd\ud558\uae30 \uc804\uc5d0 \uc7a1\uc544\uc57c \ud558\ub294latch\ub85c \ubcf5\uc218 \uc0ac\uc6a9\uc790\uc5d0 \uc758\ud574 \ub3d9\uc2dc\uc5d0 \ubcc0\uacbd\ub418\ub294 \uac83\uc744 \ub9c9\uc544\uc900\ub2e4. \ud558\ub098\uc758 latch\uc5d0 \ub300\ud574\uc11c \uc5ec\ub7ec\uac1c\uc758buffer\uac00 DBA\ub97c \uc774\uc6a9\ud558\uc5ec hash\ub418\uc5b4\uc9c4\ub2e4.
                                      \n<\/span><\/div>\n

                                      latch contention\uc774 \uc2ec\ud55c \uacbd\uc6b0, \ud2b9\uc815\ud55c particular hash list\uac00 \ud06c\uac8c \uc99d\uac00\ud558\uc600\uac70\ub098, \ud558\ub098\uc758 block\uc5d0 \ub300\ud574\uc11c CR copy\uac00 \uc5ec\ub7ec\uac1c \uc874\uc7ac\ud558\ub294 \uacbd\uc6b0\uc774\ub2e4. \ub2e4\uc74c\uacfc \uac19\uc740 query\ub97c \uc774\uc6a9\ud558\uc5ec \uadf8\ub7ec\ud55c \uacbd\uc6b0\uc778\uc9c0\ub97c \ud655\uc778\ud55c\ub2e4.
                                      \n<\/span><\/p>\n<\/li>\n<\/ul>\n

                                      \n <\/p>\n

                                      select dbafil “File #”, dbablk “Block #”,count(*)
                                      \n<\/span><\/p>\n

                                      from x$bh
                                      \n<\/span><\/p>\n

                                      group by dbafil, dbablk
                                      \n<\/span><\/p>\n

                                      having count(*) > 1 ;
                                      \n<\/span><\/p>\n

                                      \n <\/p>\n

                                        \n
                                      • \n
                                        cache buffer lru chain<\/strong> – LRU list\ub97c \ubcf4\ud638\ud558\uae30 \uc704\ud55c latch\uc774\ub2e4. buffer\ub97c \uc774 list\uc5d0 \uc62e\uae30\ub824\uba74 \uc77c\ub2e8 \uc774 latch\ub97c \uc7a1\uc544\uc57c \ud55c\ub2e4. \ub9cc\uc57d \uc774 latch\uc5d0 \ub300\ud55c contention\uc774 \ud06c\ub2e4\uba74, _db_block_write_batch\ub97c \uc99d\uac00\ud558\uac70\ub098 _db_writer_max_scan_cnt\ub97c \uac10\uc18c\ud558\uc5ec\uc57c \ud55c\ub2e4. SNP system\uc758 \uacbd\uc6b0 LRU latch\ub294 db_block_lru_latches\ub77c\ub294 instance\ub2f9 LRU latch\uc758 \uac1c\uc218\ub97c \ub098\ud0c0\ub0b4\ub294 initial parameter\uc5d0 \uc758\ud574 \uc81c\uc5b4\ub41c\ub2e4. \uc774 \uac12\uc740 \uae30\ubcf8\uc801\uc73c\ub85c \u00bd * CPU\uac1c\uc218\uc774\uba70 \ucd5c\ub300\uac12\uc740 2* CPU\uc758 \uac2f\uc218\uc774\ub2e4.
                                        \n<\/span><\/div>\n<\/li>\n
                                      • \n
                                        cache protection latch<\/strong> – buffer cache\uac00 \ubcc0\uacbd\ub418\uc5b4 CR copy\ub97c \uc720\uc9c0\ud558\uae30 \uc704\ud574 buffer\ub97c \ubcf5\uc0ac\ud558\ub294 \uacbd\uc6b0 \uc0c8\ub85c\uc774 \ubcf5\uc0ac\ub41c buffer\uc758 \uc704\uce58\uac00 \ubcc0\uacbd\ub418\uc5c8\uc73c\ubbc0\ub85c \uc774 \uc815\ubcf4\ub97c \uc800\uc7a5\ud558\uae30 \uc704\ud574\uc11c buffer head\uc758 \ub0b4\uc6a9\uc774 \uc790\ub3d9\uc73c\ub85c \ubcc0\uacbd\ub41c\ub2e4. \uc774\ub807\uac8c buffer\ub97c copy\ud558\ub294 \uac83\uc744 \ud55c \uc21c\uac04\uc5d0 \ud558\ub098\ub9cc \uc218\ud589\ud558\uae30 \uc704\ud55c latch\uc774\ub2e4.
                                        \n<\/span><\/div>\n<\/li>\n<\/ul>\n

                                        \n <\/p>\n

                                          \n
                                        1. \n
                                          \n

                                          Control variables:
                                          \n<\/span><\/h2>\n<\/div>\n
                                            \n
                                          1. \n
                                            foreground scan depth     : \ucd5c\ub300 \u00bc * buffer cache.
                                            \n<\/span><\/div>\n<\/li>\n
                                          2. \n
                                            max batch write     : min(1\/4 * buffer cache, OS specific)
                                            \n<\/span><\/div>\n<\/li>\n
                                          3. \n
                                            max dirty queue     : 2* max batch write = \u00bd buffer cache
                                            \n<\/span><\/div>\n<\/li>\n
                                          4. \n
                                            max scan depth     : min(4* max batch write, \u00bc buffer cache)
                                            \n<\/span><\/div>\n<\/li>\n
                                          5. \n
                                            min scan depth     : min(max batch write, \u00bc buffer cache)
                                            \n<\/span><\/div>\n<\/li>\n
                                          6. \n
                                            DBWR scan depth     : \ucd08\uae30\uc5d0\ub294 min scan depth\uc774\uba70, \uc774\ud6c4\uc5d0 \ub3d9\uc801\uc73c\ub85c \ubcc0\uacbd\ub41c\ub2e4.
                                            \n<\/span><\/div>\n<\/li>\n<\/ol>\n


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

                                                Buffer Cache Management   Oracle\uc740 cache buffer\ub97c \uad00\ub9ac\ud558\uae30 \uc704\ud574\uc11c\ub294 \uc138\uac00\uc9c0\uc758 \ub0b4\ubd80\uc801\uc778 structure\ub97c \uc0ac\uc6a9\ud558\ub294\ub370, \uadf8 \uac01\uac01\uc740 cache buffer chain, dirty list, LRU(Least Recently Used list)\uc774\uba70, \uc544\ub798\uc5d0 \uc790\uc138\ud788 \uc124\uba85\ud558\ub2e4. buffer cache management\uc758 \uae30\ubcf8\uc744 \uc774\ub8e8\ub294 \uc774 \uc138\uac00\uc9c0 list\ub97c \uad00\ub9ac\ud558\uba74\uc11c \uc0ac\uc6a9\uc790\uc5d0\uac8c \ud544\uc694\ud55c buffer\ub97c \uc0ac\uc6a9\uac00\ub2a5\ud558\ub3c4\ub85d \uc81c\uacf5\ud558\uc5ec \uc8fc\ub294 \uc5ed\ud560\uc744 \ud558\ub294 \uac83\uc740 DBWR\uc774\ub2e4. \ub610\ud55c DBWR\uc740 startup\uc2dc \uac01 online datafile\uc5d0 \ub300\ud574\uc11c […]<\/p>\n","protected":false},"author":1,"featured_media":646,"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":[302,71,303],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/haisins.synology.me\/wordpress\/wp-content\/uploads\/1\/cfile10.uf.261CA245563C6AE10EA6EA.png?fit=571%2C381","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/645"}],"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=645"}],"version-history":[{"count":0,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/645\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/646"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=645"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=645"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=645"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}