SQL Statement Level တြင္ Optimize လုပ္၍ Query Execution ကုိ ျမန္ဆန္ေစျခင္း

SQL Statement Level တြင္ Optimize လုပ္၍ Query Execution ကုိ ျမန္ဆန္ေစျခင္း

မေန႕က စာေရးသူ အိမ္ကုိ ျပန္ေနတုန္း အစ္မေတာ္က ခ်က္ခ်င္းပဲ G-talk ေပၚကုိ ၾကြပါလုိ႕ Message ပုိ႕လာပါတယ္။ ဒါနဲ႕ပဲ ဖုန္းကေန G-talk ကုိ ဖြင့္လုိက္ေတာ့ Query တစ္ခုကုိ run ထားတာ ၃ နာရီ နီးပါးရွိၿပီ၊ အေျဖမထြက္ေသးလုိ႕ ဘယ့္ႏွယ္လုပ္ရပါ့လုိ႕ ေမးပါေတာ့တယ္။ ဒီ စာတမ္းငယ္မွာ အလားတူ ျပႆနာတစ္ရပ္ (အေရးႀကီး အခ်က္အလက္မ်ား ပါ၀င္ေနသျဖင့္ မူလျပႆနာကုိ မေဖာ္ျပႏုိင္ပါ) ကုိ SQL Statement Level မွာ Optimize လုပ္ပံုကုိ ေရးသားေဖာ္ျပမွာ ျဖစ္ပါတယ္။

ပရိေဘာဂနဲ႕ မီးဖုိေခ်ာင္သံုး ပစၥည္း ေရာင္းတဲ့ကုမၸဏီတစ္ခုမွာ ေဖာက္သည္ေတြရဲ႕ အမည္၊ ေမြးေန႕၊ လိပ္စာ၊ ဖုန္းနံပါတ္၊ အခ်က္အလက္ေတြကုိ Table တစ္ခုနဲ႕ သိမ္းထားပါတယ္ (Customer Table ဆုိပါစုိ႕)။ ေဖာက္သည္ေတြ ပစၥည္း မွာယူတုိင္းမွာ အမွာလက္ခံတဲ့ Table တစ္ခုလဲ ရွိပါတယ္ (Order Table ဆုိပါစုိ႕)။ Order Table ဟာ ကုမၸဏီ သက္တမ္းၾကာတာနဲ႕အမွ် အရမ္းႀကီး လာတတ္လုိ႕ Production run ေနတဲ့ server မွာ ၃ - ၄ ႏွစ္စာပဲ ထားၿပီး က်န္တာကုိ သပ္သပ္ သိမ္းထားေလ့ ရွိပါတယ္။

ျဖစ္ပံုက ပရိေဘာဂ မ၀ယ္ဖူးေသးတဲ့ (မီးဖုိေခ်ာင္သံုး ပစၥည္းပဲ ၀ယ္ဖူးတဲ့) ေဖာက္သည္ေတြကုိ အထူး အေရာင္းျမွင့္တင္ေရး အစီအစဥ္နဲ႕ ပရိေဘာဂေတြ ေစ်းခ်ေရာင္းမဲ့ အေၾကာင္း ေၾကာ္ျငာစာ ပုိ႕ခ်င္တာပါပဲ။ အဲဒါနဲ႕ ေအာက္ပါ Query ကုိ ေရးပါတယ္။

SELECT cust_id, cust_name, cust_add
FROM customer_table

WHERE cust_id NOT IN (SELECT cust_id FROM order_table WHERE ord_type = ‘furniture’);

အဲဒီလုိ ေရးလုိက္တဲ့ Query ဟာ ၃ နာရီနီးပါး ၾကာတဲ့အထိ မၿပီးႏုိင္လုိ႕ စာေရးသူကို အပူကပ္ျခင္း ျဖစ္၏။ ဘာျဖစ္သနည္း အေျဖရွာေသာ္ ...

ကုမၸဏီသည္ ပရိေဘာဂ အမွာစာ အမ်ားအျပား လက္ခံရရွိခဲ့ရာ Sub-query မွ Row အေျမာက္အမ်ားကုိ ထုတ္ေပးေလသည္။ Row ၃ ေသာင္းေက်ာ္တဲ့။ Customer Table တြင္လဲ Row အမ်ားအျပား ရွိေပလိမ့္မည္။ ယခု ေဆြးေႏြးခ်က္တြင္ Customer Table ၏ Size မွာ အေရးမႀကီးပါ။ Query Optimizer သည္ Sub-query မွ constant results ကုိ ထုတ္ေပးေၾကာင္း သိသျဖင့္ Sub-query ကုိ တစ္ႀကိမ္သာ Run မည္ပဲထား၊ Customer Table မွ Row တစ္ေၾကာင္းစီ (cust_id တစ္ခုစီ) အား Sub-query မွ ေပးေသာ Row ၃ ေသာင္းေက်ာ္တြင္ ပါ၊ မပါကုိ စိစစ္ရန္မွာ လြယ္ကူေသာ အလုပ္ မဟုတ္ေခ်။ အဘယ္ေၾကာင့္ဆုိေသာ္ တၿပိဳင္နက္ အသံုးျပဳသူ အေျမာက္အမ်ားရွိတတ္ေသာ Database Server တြင္ အသံုးျပဳသူတစ္ေယာက္ (Query တစ္ခု) အတြက္ ခြဲေ၀ေပးေသာ ပင္မ မွတ္ဥာဏ္ (Primary Memory or RAM) ပမာဏသည္ အကန္႕အသတ္ ရွိသည့္အျပင္ Order Table တြင္ cust_id ျဖင့္ index တစ္ခု မရွိႏုိင္ျခင္းေၾကာင့္ ျဖစ္သည္။

မွတ္ခ်က္ ။ ။ စာျပန္ဖတ္ၾကည့္ရာ NOT IN ျဖင့္ေရးလွ်င္ Sub-query ကုိ အႀကိမ္ႀကိမ္ run လိမ့္မည္ျဖစ္ၿပီး cust_id ျဖင့္ index ရွိလဲ မသံုးဟု ဆုိသည္။ NOT EXISTS ျဖင့္ ေျပာင္းေရးလွ်င္ေတာ့ အဆုိပါ Index ကုိ သံုးေကာင္း သံုးႏုိင္သည္။ အကယ္၍ Order Table အတြက္ cust_id ျဖင့္ index တစ္ခု၊ ord_type အတြက္ Index တစ္ခုရွိပါက NOT EXISTS ကုိ သံုးျခင္းျဖင့္ အေျဖ ျမန္ျမန္ ရႏုိင္သည္ ဆုိသည္။ သုိ႕ရာတြင္ Transaction Table ျဖစ္ေသာ Order Table ၏ Primary Key မွာ Composite ေသာ္လည္းေကာင္း၊ Order Number ေသာ္လည္းေကာင္း ျဖစ္မည္ ျဖစ္ၿပီး မည္သည့္ စိတ္မွန္ေသာ Database Administrator မွ် ၂ ႏွစ္ေနလုိ႕ တစ္ခါ မလုပ္ေသာ အေရာင္းျမွင့္တင္ေရးအတြက္ အဆုိပါ index မ်ားကုိ ေဆာက္ထားမည္ မဟုတ္။ Index ေဆာက္ျခင္းသည္ Insert ႏွင့္ Update Query မ်ားအတြက္ စြမ္းေဆာင္ရည္ကုိ က်ဆင္းေစသည္။

သုိ႕ျဖစ္၍ Customer Table မွ cust_id တစ္ခုစီအား Sub-query မွ ေပးေသာ Row ၃ ေသာင္းေက်ာ္အတြင္း ပါ၊ မပါကုိ စိစစ္ရန္ cust_id တစ္ခုစီအတြက္ Sub-query အေျဖ ၃ ေသာင္းေက်ာ္ကုိ တစ္ႀကိမ္စီ ဖတ္ရမည္ ျဖစ္သည္။ Sub-query အေျဖ ၃ ေသာင္းေက်ာ္သည္ ပင္မ မွတ္ဥာဏ္အတြင္း မဆန္႕ျပန္ရာ ၄င္းတုိ႕ကုိ အစိတ္စိတ္ပုိင္း၍ အရန္ မွတ္ဥာဏ္ (Auxiliary Memory or Disk) သုိ႕ ေရးကာ ျပန္ျပန္ဖတ္ရမည္။ အရန္ မွတ္ဥာဏ္သို႕ ေရး/ဖတ္ ရသည့္ အလုပ္သည္ ေႏွးေကြးေသာ အလုပ္ျဖစ္သျဖင့္ Customer Table မွ cust_id တစ္ခုအတြက္ CPU Cycle အမ်ားအျပား ၾကာျမင့္မည္။ ထုိၾကာေသာ အလုပ္ကုိ Customer Table အတြင္းမွ ေျမာက္မ်ားစြာေသာ Row မ်ားအတြက္ အခါခါ လုပ္ရရာ ၃ နာရီ နီးပါး ၾကာေသာ္လည္း မၿပီးသည္မွာ မဆန္း။

နည္းလမ္းစဥ္ သရုပ္ခြဲခ်က္ (Algorithm Analysis) အရ Customer Table တြင္ Row n ခု၊ Order Table တြင္ Row m ခု၊ ပင္မ မွတ္ဥာဏ္တြင္ cust_id ေပါင္း p ခု သိမ္းဆည္းထားႏုိင္၍ cust_id ေပါင္း p ခုကုိ ပင္မ မွတ္ဥာဏ္အတြင္းသို႕ ကူးယူရန္ အခ်ိန္ c ၾကာသည္ဆုိပါက ပင္မ မွတ္ဥာဏ္သို႕ ေရးသည့္ လုပ္ငန္းခ်ည္း သက္သက္ အတြက္ O(nmc/p) ၾကာျမင့္မည္။ Sub-query မွ Row ၃ ေသာင္း အေျဖရ၍ Customer Table တြင္ Row ၄ ေသာင္း၊ p သည္ ၄၀၀၀၊ c သည္ ၀.၀၅ စကၠန္႕ ျဖစ္ပါက ပင္မ မွတ္ဥာဏ္ေပၚသို႕ ကူးတင္သည့္ ကိစၥအတြက္ပင္ ၄၀ ၀၀၀ x ၃၀ ၀၀၀၀ x ၀.၀၅ စကၠန္႕ / ၄၀၀၀ = ၁၅၀၀၀ စကၠန္႕ = ၄ နာရီ ၁၀ မိနစ္ (ညတ္ခၽြာျဖား !!!) ၾကာျမင့္မည္။

အဲဒါနဲ႕ ေအာက္ပါ Query နဲ႕ စမ္းၾကည့္ဖုိ႕ စာေရးသူက ေျပာလုိက္ရပါတယ္။

SELECT cust_id, cust_name, cust_add
FROM customer_table

WHERE cust_id IN (SELECT cust_id FROM customer_table

MINUS

SELECT cust_id FROM order_table WHERE ord_type = ‘furniture’);

ခဏနဲ႕ အေျဖရသြားပါသတဲ့။ ဘာမ်ားကြာသြားသလဲ စဥ္းစားၾကည့္ရေအာင္ေနာ္။

ပထမဆံုးအခ်က္က Sub-query ရဲ႕ Results ေသးသြားပါတယ္၊ ဒုတိယအခ်က္ကေတာ့ IN က Customer Table ထဲက cust_id တစ္ခုခ်င္းစီအတြက္ Sub-query Results ထဲမွာ တူတာ တစ္ခုေတြ႕တာနဲ႕ တြက္ခ်က္တာကုိ ရပ္လုိက္လုိ႕ရၿပီး (Short-circuiting လုိ႕ ေခၚပါတယ္) မူလ NOT IN ကေတာ့ Sub-query Results ကုန္ေတာ့မွ ရပ္လုိ႕ရပါတယ္၊ တတိယနဲ႕ ေနာက္ဆံုး အခ်က္ကေတာ့ MINUS က စီၿပီးမွ အလုပ္လုပ္ပါတယ္။ အေသးစိတ္ ရွင္းျပမယ္ေနာ္။

အေပၚက ဥပမာအတုိင္းဆုိလွ်င္ Sub-query မွ ပထမ SELECT Statement ရဲ႕ အေျဖဟာ Row ၄ ေသာင္းရွိၿပီး ဒုတိယ SELECT Statement ရဲ႕ အေျဖဟာ Row ၃ ေသာင္း ရွိပါတယ္။ Referential Integrity Enforcement အရ Order Table ထဲက cust_id ေတြရဲ႕ အစုဟာ Customer Table ထဲက cust_id ေတြရဲ႕ အစုရဲ႕ အစုပုိင္း ျဖစ္တာမုိ႕ Sub-query ရဲ႕ အေျဖက Row ၁ ေသာင္းသာ ရွိေတာ့မွာေပါ့။ ဒါေၾကာင့္ ပင္မ မွတ္ဥာဏ္ထဲကုိ ေရးရသည့္ အခ်ိန္ဟာလဲ ၄၀ ၀၀၀ x ၁၀ ၀၀၀ x ၀.၀၅ စကၠန္႕ / ၄၀၀၀ = ၅၀၀၀ စကၠန္႕ = ၁ နာရီ ၂၄ မိနစ္ေအာက္သာ ရွိေတာ့မွာေပါ့။ အစ္မေတာ္ရဲ႕ ျပႆနာကေတာ့ အေရအတြက္ ကြာတာမုိ႕ မိနစ္ပုိင္းနဲ႕ အေျဖရပါသတဲ့၊ ေကာင္းေလစြ။

Customer Table ထဲက cust_id တစ္ခုခ်င္းစီအတြက္ ပင္မ မွတ္ဥာဏ္ထဲကုိ Sub-query Results ေရးထည့္ၿပီး တူတာရွိ၊ မရွိ တုိက္ၾကည့္ရပါတယ္။ IN ရဲ႕ သေဘာ၊ သဘာ၀က တူတာ တစ္ခု ေတြ႕တာနဲ႕ စစ္တာကုိ ရပ္လုိက္လုိ႕ရပါတယ္။ ပင္မ မွတ္ဥာဏ္ထဲ Sub-query Results အားလံုးကုိ တခ်ိန္ထဲ ထည့္ထားလုိ႕ မရတဲ့အတြက္ p ခုစီ အခါခါထည့္ၿပီး စစ္ေနရာမွာ (p က ၄၀၀၀ ဆုိရင္ Sub-query Results ၁ ေသာင္းကုိ Disk ထဲကေန RAM ေပၚကုိ ၃ ခါခြဲၿပီး ကူးတင္ရပါမယ္။) ရွာေတြ႕သြားရင္ အဲဒီ cust_id အတြက္ ဆက္ထည့္ေပးဖုိ႕ မလုိေတာ့ပါဘူး (ဥပမာ ပထမ ၄၀၀၀ ထဲမွာ ရွာေတြ႕သြားရင္ က်န္တဲ့ ၆၀၀၀ ကုိ RAM ေပၚကုိ ကူးတင္ေပးစရာ မလုိေတာ့ပါဘူး၊ ၂ ခါသက္သာသြားၿပီ)။ ဒါ့အျပင္ ေနာက္ cust_id တစ္ခုအတြက္လဲ လက္ရွိ ထည့္ထားတဲ့ ၄၀၀၀ နဲ႕ ဆက္အလုပ္လုပ္ႏုိင္ပါေသးတယ္ (၃ ခါ သက္သာသြားၿပီ၊ တခါကုိ ၀.၀၅ စကၠန္႕ဆုိေတာ့ ၀.၁၅ စကၠန္႕သက္သာသြားၿပီ)။ NOT IN မွာကေတာ့ အစအဆံုး တုိက္ရတဲ့အတြက္ ဒီလုိလုပ္လုိ႕ မရပါဘူး။

A MINUS B ကုိ တြက္တဲ့အခါမွာ Oracle Database Server က A ကုိ စီ၊ B ကုိ စီၿပီးမွ Sorted File Merging နဲ႕တူတဲ့ နည္းလမ္းစဥ္ကုိ အသံုးျပဳ တြက္ခ်က္တာမုိ႕ စာေရးသူတင္ျပတဲ့ Sub-query ရဲ႕ အခ်ိန္အားျဖင့္ ခက္ခဲမႈ (Time Complexity) ဟာ O(nlog(n) + mlog(m) + m + n) ပဲ ရွိပါတယ္။ (m ႏွင့္ n သည္ Customer Table ရဲ႕ Size ႏွင့္ Order Table မွ မတူညီေသာ cust_id အေရအတြက္တုိ႕ အသီးသီး ျဖစ္သည္။) ဒါေပမဲ့ Customer Table မွာ cust_id ဟာ primary key ျဖစ္လုိ႕ သူ႕ Index ကုိ သံုးမယ္ဆုိရင္ O(mlog(m) + m + n) ပဲ က်န္ပါလိမ့္မယ္။ Cache ရွိမယ္ဆုိရင္ေတာ့ Sub-query ရဲ႕ ကုန္က်စရိတ္ဟာ မရွိသေလာက္ နည္းသြားမွာ ျဖစ္ပါတယ္။

မွတ္ခ်က္ ။ ။ NOT EXISTS ကုိ အသံုးျပဳေရးသားလွ်င္ကား Customer Table မွ cust_id တစ္ခုစီအတြက္ Sub-query တစ္မ်ိဳးစီ တြက္ထုတ္မည္ ျဖစ္သည္။ သုိ႕ရာတြင္ Order Table တြင္ cust_id ျဖင့္ index မရွိႏုိင္ရာ Short-circuiting သံုးႏုိင္ေသာ္လည္း Order Table ကုိ full table scan လုပ္ရမွာပဲ ျဖစ္သည္။ ထုိသုိ႕ဆုိလွ်င္ စာေရးသူ တင္ျပေသာ နည္းလမ္းႏွင့္ ၾကာခ်ိန္တူတူေလာက္ပဲ ျဖစ္မည္။ သုိ႕ရာတြင္ Database Server မ်ားတြင္ Query Results မ်ားကုိ Cache လုပ္ထားတတ္ရာ cust_id တစ္ခုစီအတြက္ Sub-query တစ္မ်ိဳးစီ တြက္ထုတ္ေနသာ နည္းလမ္းသည္ အၿမဲ Cache Miss ျဖစ္ေနၿပီး Sub-query တစ္မ်ိဳးတည္းသာရွိေသာ စာေရးသူ တင္ျပသည့္ နည္းလမ္းမွာကား Cache ကို အက်ိဳးရွိစြာ အသံုးခ်ႏုိင္သျဖင့္ စာေရးသူ တင္ျပေသာ နည္းလမ္းက ပုိမုိေကာင္းမြန္သည္ဟု ဆုိႏုိင္သည္။

Database နည္းပညာဟာ အလုပ္လုပ္ပံု အေသးစိတ္ကို သံုးစြဲသူ ေခါင္းမရႈပ္ရေအာင္ ၀ွက္ထားေပးတယ္ဆုိေပမဲ့ တခါတေလမွာ သူ႕ရဲ႕ Statistics နဲ႕ Query Optimizer Algorithm ဟာ သံုးစြဲသူရဲ႕ Knowledge ကုိ မယွဥ္ႏုိင္တာကုိ ေတြ႕ရပါတယ္။ ဒါေၾကာင့္ သင့္ရဲ႕ Query ကုိ Execute ရာမွာ လိုတာထက္ ပုိၾကာေနၿပီဆုိရင္ သင့္အေနနဲ႕ ၀င္ပါရေတာ့မွာ ျဖစ္ပါတယ္။ System Level Optimization, DBA Level Optimization စတာေတြကို အသံုးျပဳသူတုိင္း ေဆာင္ရြက္ခြင့္ မရၾကပါဘူး။ အဲဒီအခါမ်ိဳးမွာ Query Optimizer ရဲ႕ Query Plan Formulation Logic နဲ႕ အေျခခံ နည္းလမ္းစဥ္ သရုပ္ခြဲပညာတုိ႕နဲ႕ ယဥ္ပါးတဲ့ သံုးစြဲသူမ်ားအေနနဲ႕ အထက္ေဖာ္ျပပါကဲ့သုိ႕ SQL Statement Level Optimization မ်ားကို ေဆာင္ရြက္ႏုိင္ေၾကာင္း တင္ျပလုိက္ရပါသည္။

http://download.oracle.com/docs/html/A86647_01/vmqtune.htm#1004143 ကုိမွီျငမ္းပါသည္။