»öÀÎ º¸Á¶ ÇÁ·Î±×·¥Àº »ç¿ëÀÚÀÇ µ¥ÀÌÅÍ¿¡ ÀûÇÕÇÑ »öÀÎÀ» ¼³°è ¹× Á¤ÀÇÇÒ Çʿ伺À» ÁÙ¿©ÁÖ´Â °ü¸® µµ±¸ÀÔ´Ï´Ù.
»öÀÎ º¸Á¶ ÇÁ·Î±×·¥Àº ´ÙÀ½°ú °°Àº °æ¿ì¿¡ À¯¿ëÇÕ´Ï´Ù.
SQL º¸Á¶ ÇÁ·Î±×·¥ ±â´É¿¡ ¿¬°üµÈ °³³äÀº ´ÙÀ½°ú °°½À´Ï´Ù. ù¹øÂ°´Â ÀÛ¾÷ ·ÎµåÀÔ´Ï´Ù ÀÛ¾÷ ·Îµå´Â ÁÖ¾îÁø ±â°£ µ¿¾È¿¡ µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥ÀÌ Ã³¸®ÇØ¾ß ÇÏ´Â SQL¹® ¼¼Æ®ÀÔ´Ï´Ù. SQL¹®¿¡´Â SELECT, INSERT, UPDATE ¹× DELETE¹®ÀÌ Æ÷Ç﵃ ¼ö ÀÖ½À´Ï´Ù. ¿¹¸¦ µé¾î, 1°³¿ù µ¿¾È¿¡ µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥ÀÌ 1 000°ÇÀÇ INSERT, 10 000°ÇÀÇ UPDATE, 10 000°ÇÀÇ SELECT ¹× 1 000°ÇÀÇ DELETE¸¦ ó¸®ÇØ¾ß ÇÒ ¼ö ÀÖ½À´Ï´Ù. ÀÛ¾÷ ·Îµå ³»ÀÇ Á¤º¸´Â ÁÖ¾îÁø ±â°£ µ¿¾È¿¡ 󸮵Ǵ SQL¹®ÀÇ À¯Çü ¹× ºóµµ¿Í °ü·ÃÀÌ ÀÖ½À´Ï´Ù. º¸Á¶ ÇÁ·Î±×·¥ ¿£ÁøÀº ÀÌ ÀÛ¾÷ ·Îµå Á¤º¸¸¦ µ¥ÀÌÅͺ£À̽º Á¤º¸¿Í °áÇÕÇÏ¿© ±ÇÀå »öÀÎÀ» Á¦°øÇÕ´Ï´Ù. º¸Á¶ ÇÁ·Î±×·¥ ¿£ÁøÀÇ ¸ñÀûÀº ÃÑ ÀÛ¾÷ ·Îµå ºñ¿ëÀ» ÃÖ¼ÒÈÇÏ´Â °ÍÀÔ´Ï´Ù.
µÎ¹øÂ°´Â °¡»ó »öÀÎ °³³äÀÔ´Ï´Ù °¡»ó »öÀÎÀº ÇöÀç µ¥ÀÌÅͺ£À̽º ½ºÅ°¸¶¿¡ Á¸ÀçÇÏÁö ¾Ê´Â »öÀÎÀÔ´Ï´Ù. ÀÌ »öÀÎÀº º¸Á¶ ÇÁ·Î±×·¥ ±â´ÉÀÌ »ç¿ëÀÚ¿¡°Ô ±ÇÇÑ ±ÇÀå»çÇ×À̰ųª º¸Á¶ ÇÁ·Î±×·¥ ±â´É¿¡°Ô »ç¿ëÀÚ°¡ ãÀº °ÍÀ» Æò°¡ÇØ ÁÖµµ·Ï ¿äûÇÑ »öÀÎÀÔ´Ï´Ù. ¶ÇÇÑ, ÀÌ »öÀÎÀº º¸Á¶ ÇÁ·Î±×·¥ ±â´ÉÀÌ ÇÁ·Î¼¼½ºÀÇ ÀϺηΠ°£ÁÖÇÏ¿© ±ÇÀåÇÒ ¸¸ÇÑ »öÀÎÀÌ ¾Æ´Ï±â ¶§¹®¿¡ ¹ö¸®´Â °ÍÀÏ ¼öµµ ÀÖ½À´Ï´Ù. °¡»ó »öÀÎÀº ADVISE_INDEX Å×À̺íÀ» »ç¿ëÇÏ¿© »ç¿ëÀڷκÎÅÍ º¸Á¶ ÇÁ·Î±×·¥ ±â´ÉÀ¸·Î Àü´ÞµÇ°Å³ª µÇµ¹¾Æ¿Ã ¼ö ÀÖ½À´Ï´Ù.
º¸Á¶ ÇÁ·Î±×·¥ ±â´ÉÀº ÀÛ¾÷ ·Îµå¿Í µ¥ÀÌÅͺ£À̽ºÀÇ Åë°è¸¦ »ç¿ëÇÏ¿© ±ÇÀå »öÀÎÀ» »ý¼ºÇÕ´Ï´Ù.
Advise Facility´Â µÎ °¡ÁöÀÇ EXPLAIN Å×À̺íÀ» »ç¿ëÇÕ´Ï´Ù.
ÀÌ Å×À̺íÀº ¿¹»óµÇ´Â ÀÛ¾÷ ·Îµå¿¡ ´ëÇØ ¼³¸íÇÏ´Â Å×À̺íÀÔ´Ï´Ù. Å×À̺íÀÇ °¢ ÇàÀº SQL¹®À» ³ªÅ¸³»¸ç ¿¬°üµÈ ºóµµ¿¡ ÀÇÇØ ¼³¸íµË´Ï´Ù. ¿©±â¿¡´Â "WORKLOAD_NAME" Å×À̺íÀÇ ÇʵåÀÎ °¢ ÀÛ¾÷ ·Îµå¿¡ ´ëÇÑ ½Äº°ÀÚ°¡ ÀÖ½À´Ï´Ù. µ¿ÀÏÇÑ ÀÛ¾÷ ·ÎµåÀÇ ÀϺÎÀÎ ¸ðµç SQL¹®Àº µ¿ÀÏÇÑ WORKLOAD_NAMEÀ» °¡Á®¾ß ÇÕ´Ï´Ù.
»öÀÎ ¸¶¹ý»ç ¹× db2advis µµ±¸´Â Å×À̺íÀ» »ç¿ëÇÏ¿© ÀÛ¾÷ ·Îµå Á¤º¸¸¦ ã°í ÀúÀåÇÕ´Ï´Ù.
ÀÌ Å×À̺íÀº ±ÇÀå »öÀο¡ ´ëÇÑ Á¤º¸¸¦ ÀúÀåÇÕ´Ï´Ù. Å×À̺íÀÇ Á¤º¸´Â SQL ÄÄÆÄÀÏ·¯, »öÀÎ ¸¶¹ý»ç db2advis µµ±¸ ¶Ç´Â »ç¿ëÀÚ¿¡ ÀÇÇØ ä¿öÁý´Ï´Ù.
ÀÌ Å×À̺íÀº µÎ °¡Áö ¹æ¹ýÀ¸·Î »ç¿ëµË´Ï´Ù.
ÁÖ: | ÀÌ Å×À̺íÀ» ÀÛ¼ºÇÏ·Á¸é, sqllib ¼ºêµð·ºÅ丮ÀÇ misc ¼ºêµð·ºÅ丮¿¡ ÀÖ´Â EXPLAIN.DDL ½ºÅ©¸³Æ®¸¦ ¼öÇàÇϽʽÿÀ. ¾ÆÁ÷ ÀÛ¼ºµÇ¾î ÀÖÁö ¾ÊÀ¸¸é, »öÀÎ ¸¶¹ý»ç·Î Å×À̺íÀ» ÀÛ¼ºÇÒ ¼öµµ ÀÖ½À´Ï´Ù. |
»öÀÎ º¸Á¶ ÇÁ·Î±×·¥ »ç¿ë ÇÁ·Î¼¼½º¿¡´Â ÀÔ·Â, º¸Á¶ ÇÁ·Î±×·¥ÀÇ È£Ãâ, Ãâ·Â ¹× °í·ÁÇØ¾ß ÇÏ´Â ¸î °¡Áö Ư¼ö °æ¿ì°¡ Æ÷ÇԵ˴ϴÙ.
»öÀÎ º¸Á¶ ÇÁ·Î±×·¥¿¡ ´ëÇÑ ÀÔ·ÂÀº ´ÙÀ½ ¼¼ °¡Áö ¹æ¹ýÀ¸·Î ÀÛ¼ºÇÒ ¼ö ÀÖ½À´Ï´Ù.
Áï, ´ÙÀ½ ¹æ¹ý ÁßÀÇ Çϳª¸¦ »ç¿ëÇÏ¿© Æò°¡µÉ SQLÀ» ÀÛ¼ºÇÏ´Â ¹æ¹ýÀÔ´Ï´Ù.
»öÀÎ º¸Á¶ ÇÁ·Î±×·¥Àº ´ÙÀ½ ³× °¡Áö ¹æ¹ýÀ¸·Î È£ÃâÇÒ ¼ö ÀÖ½À´Ï´Ù.
¹Ù¶÷Á÷ÇÑ ¹æ¹ýÀº »öÀÎ º¸Á¶ ÇÁ·Î±×·¥À» »ç¿ëÇÏ´Â °ÍÀÔ´Ï´Ù. Á¦¾î ¼¾ÅͷκÎÅÍ »öÀÎ Æú´õ¸¦ ãÀ» ¶§±îÁö ¿ÀºêÁ§Æ® Æ®¸®¸¦ È®ÀåÇϽʽÿÀ »öÀÎ Æú´õ¸¦ ¸¶¿ì½º ¹öư 2·Î Ŭ¸¯ÇÑ ÈÄ, ÆË¾÷ ¸Þ´º¿¡¼ ÀÛ¼º-¸¶¹ý»ç¸¦ »ç¿ëÇÑ »öÀÎÀ» ¼±ÅÃÇϽʽÿÀ »öÀÎ ¸¶¹ý»ç°¡ ¿¸³´Ï´Ù. »öÀÎ ¸¶¹ý»ç¿¡´Â »ç¿ëÇϱ⠽¬¿î È®Àå µµ¿ò¸»ÀÌ ÀÖ½À´Ï´Ù. ¸¶¹ý»ç¿¡´Â ÃÖ±Ù ½ÇÇàµÈ SQLÀ» ã°Å³ª, ÃÖ±Ù »ç¿ëµÈ ÆÐŰÁö¸¦ ÅëÇØ ã°Å³ª, Á÷Á¢ SQL¹®À» Ãß°¡ÇÔÀ¸·Î½á ÀÛ¾÷ ·Îµå¸¦ ±¸¼ºÇÒ ¼ö ÀÖ´Â ±â´Éµµ µé¾î ÀÖ½À´Ï´Ù.
¸í·ÉÇà¿¡¼, db2advis¸¦ ÀÔ·ÂÇϽʽÿÀ. db2advis´Â ´ÙÀ½ ¼¼ °÷ Áß¿¡¼ ÀÛ¾÷ ·Îµå¸¦ ÀÐ¾î ½ÃÀÛÇÕ´Ï´Ù.
µµ±¸´Â CURRENT EXPLAIN MODE ·¹Áö½ºÅ͸¦ »ç¿ëÇÏ¿© ±ÇÀå »öÀÎÀ» È®º¸ÇÑ ÈÄ, ³»ºÎ ÃÖÀûÈ ¾Ë°í¸®Áò°ú °áÇÕÇÏ¿© ÃÖÀûÀÇ »öÀÎÀ» ã¾Æ³À´Ï´Ù. Ãâ·ÂÀº Å͹̳Πȸé, ADVISE_INDEX Å×ÀÌºí ¹× Ãâ·Â ÆÄÀÏ·Î Ãâ·ÂµË´Ï´Ù(¿øÇÒ °æ¿ì).
¿¹¸¦ µé¾î, µµ±¸¿¡°Ô °£´ÜÇÑ Á¶È¸ÀÎ "select count(*) from sales where region = 'Quebec'"¿¡ ´ëÇÑ »öÀÎÀ» ±ÇÀåÇØÁÙ °ÍÀ» ¿äûÇÒ ¼ö ÀÖ½À´Ï´Ù.
$ db2advis -d sample \ -s "select count(*) from sales where region = 'Quebec'" \ -t 1 performing auto-bind Bind is successful. Used bindfile: /home3/valentin/sqllib/bnd/db2advis.bnd Calculating initial cost (without recommended indexes) [31.198040] timerons Initial set of proposed indexes is ready. Found maximum set of [1] recommended indexes Cost of workload with all indexes included [2.177133] timerons cost without index [0] is [31.198040] timerons. Derived benefit is [29.020907] total disk space needed for initial set [1] MB total disk space constrained to [-1] MB 1 indexes in current solution [31.198040] timerons (without indexes) [2.177133] timerons (with current solution) [%93.02] improvement Trying variations of the solution set. Time elapsed. LIST OF RECOMMENDED INDEXES =========================== index[1], 1MB CREATE INDEX WIZ689 ON VALENTIN.SALES (REGION DESC) =========================== Index Advisor tool is finished.
db2advis µµ±¸´Â ÀÛ¾÷ ·Îµå¿¡ ´ëÇÑ »öÀÎÀ» ±ÇÀåÇÏ´Â µ¥µµ »ç¿ëµÉ ¼ö ÀÖ½À´Ï´Ù. "sample.sql"¶ó´Â ÀÔ·Â ÆÄÀÏÀ» ÀÛ¼ºÇÒ ¼ö ÀÖ½À´Ï´Ù.
--#SET FREQUENCY 100 select count(*) from sales where region = ?; --#SET FREQUENCY 3 select projno, sum(comm) tot_comm from employee, emp_act where employee.empno = emp_act.empno and employee.job='DESIGNER' group by projno order by tot_comm desc; --#SET FREQUENCY 50 select * from sales where sales_date = ?;
±×·± ÈÄ, ´ÙÀ½ ¸í·ÉÀ» ¼öÇàÇϽʽÿÀ.
$ db2advis -d sample -i sample.sql -t 0 found [3] SQL statements from the input file Calculating initial cost (without recommmended indexes) [62.331280] timerons Initial set of proposed indexes is ready. Found maximum set of [2] recommended indexes Cost of workload with all indexes included [29.795755] timerons cost without index [0] is [58.816662] timerons. Derived benefit is [29.020907] cost without index [1] is [33.310373] timerons. Derived benefit is [3.514618] total disk space needed for initial set [2] MB total disk space constrained to [-1] MB 2 indexes in current solution [62.331280] timerons (without indexes) [29.795755] timerons (with current solution) [%52.20] improvement Trying variations of the solution set. Time elapsed. LIST OF RECOMMENDED INDEXES =========================== index[1], 1MB CREATE INDEX WIZ119 ON VALENTIN.SALES (SALES_DATE DESC, SALES_PERSON DESC) index[2], 1MB CREATE INDEX WIZ63 ON VALENTIN.SALES (REGION DESC) =========================== Index Advisor tool is finished.
¿¹¸¦ µé¾î, CURRENT EXPLAIN MODE Ư¼ö ·¹Áö½ºÅÍ´Â RECOMMEND INDEXES·Î ¼³Á¤µË´Ï´Ù. ÀÌ·¸°Ô ¼³Á¤Çϸé, SQL ÄÄÆÄÀÏ·¯°¡ Explain µ¥ÀÌÅÍ ¹× ±ÇÀåµÇ´Â »öÀÎÀ» ĸÃÄÇÏ¿© ADVISE_INDEX Å×ÀÌºí¿¡ ÀúÀåÇÕ´Ï´Ù. ±×·¯³ª, SQL¹®Àº ½ÇÇàµÇÁö ¾Ê½À´Ï´Ù.
¾Æ´Ï¸é, CURRENT EXPLAIN MODE Ư¼ö ·¹Áö½ºÅÍ´Â EVALUATE INDEXES·Î ¼³Á¤µË´Ï´Ù. ÀÌ·¸°Ô ¼³Á¤Çϸé, SQL ÄÄÆÄÀÏ·¯´Â »ç¿ëÀÚ°¡ ADVISE_INDEX Å×ÀÌºí¿¡ ÀúÀåÇÑ »öÀÎÀ» »ç¿ëÇÏ°Ô µË´Ï´Ù. »ç¿ëÀÚ´Â Æò°¡µÇ¾î¾ß ÇÒ °¢ »öÀο¡ ´ëÇÑ »õ·Î¿î ÇàÀ» »ðÀÔÇÕ´Ï´Ù. °¢ »öÀο¡ ÇÊ¿äÇÑ Á¤º¸´Â »öÀÎ À̸§, Å×À̺í À̸§, Æò°¡µÇ´Â »öÀÎÀ» ±¸¼ºÇÏ´Â Ä÷³ À̸§ÀÔ´Ï´Ù. ÀÏ´Ü ÀÔ·ÂÇϸé, Ư¼ö ·¹Áö½ºÅÍÀÎ CURRENT EXPLAIN MODE°¡ EVALUATE INDEXES·Î ¼³Á¤µÇ¾î¾ß ÇÕ´Ï´Ù. ±×·¯¸é, SQL ÄÄÆÄÀÏ·¯´Â USE_INDEX Çʵ尡 "Y"·Î ¼³Á¤µÈ ADVISE_INDEX Å×À̺íÀ» ½ºÄµÇÕ´Ï´Ù. À̸¦ °¡»ó »öÀÎÀ̶ó°í ÇÕ´Ï´Ù. EVALUTE INDEXS ¸ðµå¿¡¼ ½ÇÇàµÇ´Â ¸ðµç µ¿Àû¹®¿¡´Â ÀÌ °¡»ó »öÀÎÀÌ »ç¿ë °¡´ÉÇÑ °ÍÀ¸·Î ¼³¸íµÇ¾î ÀÖ½À´Ï´Ù. ¸í·É¹®ÀÇ ¼º´ÉÀÌ Çâ»óµÉ °æ¿ì, SQL ÄÄÆÄÀÏ·¯´Â °¡»ó »öÀÎÀ» »ç¿ëÇϵµ·Ï ¼±ÅÃÇÕ´Ï´Ù. ±×·¸Áö ¾ÊÀ¸¸é, »öÀÎÀÌ ¹«½ÃµË´Ï´Ù. EXPLAIN °á°ú¸¦ °ËÅäÇÏ¿©, »ç¿ëÀÚ°¡ Á¦¾ÈÇÑ »öÀÎÀÌ SQL ÄÄÆÄÀÏ·¯¿¡ ÀÇÇØ »ç¿ëµÇ¾úÀ½À» ¾Ë ¼ö ÀÖ½À´Ï´Ù. »ç¿ëµÈ »öÀÎÀº ¾×¼¼½º °³¼±À» À§ÇØ ±¸ÇöµÉ °ÍÀ¸·Î °£ÁֵǾî¾ß ÇÕ´Ï´Ù.
ÀÀ¿ëÇÁ·Î±×·¥À» ÀÛ¼ºÇÒ ¶§ ÀÌ ÀÎÅÍÆäÀ̽º¸¦ »ç¿ëÇϰí ÀÖ´Ù¸é, º¸Á¶ ÇÁ·Î±×·¥À» »ç¿ëÇÒ ¼öµµ ÀÖ½À´Ï´Ù.
¿©·¯ °¡Áö ¹æ¹ýÀ¸·Î Á¶¾ð ÇÁ·Î±×·¥À¸·ÎºÎÅÍÀÇ °á°ú¸¦ »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù.
º¸Á¶ ÇÁ·Î±×·¥ ±â´ÉÀÌ ¾î¶² »öÀÎÀ» ±ÇÀåÇß´ÂÁö¸¦ ¾Ë±â À§ÇØ ´ÙÀ½ Á¶È¸¸¦ »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù.
SELECT CAST(CREATION_TEXT as CHAR(200)) FROM ADVISE_INDEX
ƯÁ¤ Á¶È¸¿¡ ´ëÇÑ Á»´õ ³ªÀº ±ÇÀå»çÇ×À» È®º¸Çϱâ À§ÇØ, »ç¿ëÀÚ°¡ ÇØ´ç Á¶È¸ ÀÚü¿¡ Á¶¾ðÀ» Çϵµ·Ï Á¦¾ÈÇÕ´Ï´Ù. »öÀÎ ¸¶¹ý»ç¸¦ »ç¿ëÇÏ¿© ÇØ´ç Á¶È¸ Çϳª¸¸À» Æ÷ÇÔÇÏ´Â ÀÛ¾÷ ·Îµå¸¦ ±¸ÃàÇÔÀ¸·Î½á ÇϳªÀÇ Á¶È¸¿¡ ´ëÇÑ »öÀÎÀ» ±ÇÀåÇϵµ·Ï ÇÒ ¼ö ÀÖ½À´Ï´Ù.
»ùÇà ÀÛ¾÷ ·Îµå´Â À̺¥Æ® ¸ð´ÏÅÍ Ãâ·ÂÀ» ÅëÇØ ¼öÁýÇÒ ¼ö ÀÖ½À´Ï´Ù. À̺¥Æ® ¸ð´ÏÅÍ´Â µ¿Àû SQL ½ÇÇàÀ» ¼öÁýÇÏ´Â µ¥ »ç¿ëµÉ ¼ö ÀÖ½À´Ï´Ù. ±×·± ´ÙÀ½, ÀÌ ¸í·É¹®Àº º¸Á¶ ÇÁ·Î±×·¥ ±â´ÉÀ¸·Î Çǵå¹éµÉ ¼ö ÀÖ½À´Ï´Ù.
»öÀÎ ¸¶¹ý»ç´Â º¸Á¶ ÇÁ·Î±×·¥ ±â´É¿¡ ¾×¼¼½ºÇϴ Ź¿ùÇÑ ¹æ¹ýÀ» Á¦°øÇÏ´Â, ´Ü¼øÇÏ°í °£´ÜÇÏ¸ç »ç¿ëÇϱ⠽¬¿î ½Ã°¢Àû ÀÎÅÍÆäÀ̽ºÀÔ´Ï´Ù.