Oracle Optimizer Statistics: DBMS_STATS কীভাবে সঠিকভাবে করবেন

আঠারো বছরে 'query গতকাল দ্রুত ছিল, আজ ধীর' - এমন প্রায় প্রতিটি টিকিট আমি খুঁজতে গিয়ে একটি জিনিসেই পৌঁছেছি: optimizer statistics। Oracle optimizer ঠিক ততটাই ভালো যতটা তাকে আপনার ডেটা সম্পর্কে দেওয়া সংখ্যাগুলো ভালো, আর সেই সংখ্যাগুলোই - statistics - একটি SQL স্টেটমেন্টকে একটি execution plan-এ পরিণত করে। এগুলো ঠিক থাকলে optimizer সাধারণত plan-ও ঠিক করে। এগুলো ভুল, stale বা অনুপস্থিত হলে সে অনুমান করে - বাজেভাবে। এই গাইড ব্যাখ্যা করে statistics আসলে কী, DBMS_STATS দিয়ে কীভাবে সেগুলো ঠিকঠাক গ্যাদার ও ম্যানেজ করবেন, আর কীভাবে খারাপ stats-কে এক রাতে production plan ধ্বংস করা থেকে থামাবেন।

১. Statistics আসলে কী

Oracle যখন একটি SQL স্টেটমেন্ট parse করে, তখন cost-based optimizer-কে ঠিক করতে হয় সেটি কীভাবে চালাবে: কোন index (যদি থাকে), কোন join order, কোন join method, পুরো টেবিল scan করবে নাকি কয়েকটি row খুঁজবে। প্রতিটি ধাপে কতগুলো row তৈরি হবে তা অনুমান করে সে এই সিদ্ধান্তগুলো নেয় - আর অনুমান করে statistics দিয়ে।

মূল statistics-গুলো আপনার ডেটা সম্পর্কে সহজ কিছু সংখ্যা: একটি টেবিলে কতগুলো row আছে, কতগুলো block দখল করে, একটি কলামে কতগুলো distinct value আছে, high ও low value কী, আর প্রতিটি index কতটা selective। এগুলো থেকে optimizer cardinality হিসাব করে - প্রতিটি ধাপের অনুমিত row সংখ্যা - এবং যে plan-কে সবচেয়ে সস্তা মনে করে সেটি বেছে নেয়।

গুরুত্বপূর্ণ অন্তর্দৃষ্টি: plan বাছার সময় optimizer কখনও আপনার আসল ডেটার দিকে তাকায় না। সে শুধু statistics-এর দিকে তাকায়। Statistics যদি বলে একটি টেবিলে ১,০০০ row আছে অথচ আসলে সেখানে ১ কোটি row, তবে optimizer আত্মবিশ্বাসের সঙ্গে এমন একটি plan বেছে নেবে যা মারাত্মক ভুল - আর সে নিশ্চিত থাকবে যে সে ঠিক করেছে।

২. DBMS_STATS দিয়ে Statistics গ্যাদার করা

প্রথমেই যে নিয়মটি গুরুত্বপূর্ণ: DBMS_STATS ব্যবহার করুন, পুরনো ANALYZE কমান্ড কখনও নয়। ANALYZE এখনও backward compatibility-র জন্য আছে কিন্তু এমন statistics তৈরি করে যা আধুনিক optimizer পুরোপুরি বিশ্বাস করে না, আর নিচের ফিচারগুলো তাতে নেই।

-- Gather stats for one table (and its indexes), Oracle-recommended defaults
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => 'SALES',
    tabname          => 'ORDERS',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
    cascade          => TRUE,
    degree           => 4);
END;
/

-- Whole schema
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SALES', degree=>4);

দুটি default এখানে বড় কাজ করছে। AUTO_SAMPLE_SIZE Oracle-কে sample বেছে নিতে দেয় - আধুনিক ভার্সন একটি দ্রুত, নির্ভুল অ্যালগরিদম ব্যবহার করে যা পুরনো নির্দিষ্ট শতাংশের চেয়ে অনেক বেশি পড়ে, তাই কারণ ছাড়া estimate_percent হার্ডকোড করবেন না। METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO' Oracle-কে সিদ্ধান্ত নিতে দেয় কোন কলামে histogram দরকার, সেগুলো আসলে কীভাবে ব্যবহৃত হচ্ছে তার ভিত্তিতে।

৩. স্বয়ংক্রিয় Statistics Job

10g থেকে Oracle maintenance window-তে একটি স্বয়ংক্রিয় statistics-গ্যাদারিং job চালায়, যা সেই অবজেক্টগুলোর stats আবার গ্যাদার করে যাদের ডেটা উল্লেখযোগ্যভাবে বদলেছে। বেশিরভাগ টেবিলের জন্য, বেশিরভাগ সময়ে, এটাই যথেষ্ট - আর এটি বন্ধ করে দেওয়া একটি ক্লাসিক নিজের পায়ে কুড়াল মারা।

-- Is the auto job enabled?
SELECT client_name, status FROM dba_autotask_client
WHERE  client_name = 'auto optimizer stats collection';

-- What does 'significantly changed' mean? ~10% of rows by default:
SELECT table_name, stale_stats, last_analyzed
FROM   dba_tab_statistics
WHERE  owner='SALES' AND stale_stats='YES';

Oracle প্রতিটি টেবিলের বিপরীতে DML ট্র্যাক করে এবং মোটামুটি ১০% row বদলালে statistics-কে stale চিহ্নিত করে। auto job তখন stale-গুলো refresh করে। যে ফাঁকটি এটি ঢাকতে পারে না তা হলো timing: একটি বিশাল রাতের load রাত ২টায় শেষ হয়, রিপোর্টিং চলে ভোর ৬টায়, কিন্তু auto job চলে রাত ১০টায় - তাই রিপোর্টগুলো এমন stats-এ চলে যারা জানেই না গত রাতের ডেটার অস্তিত্ব আছে। পরের সেকশনটি ঠিক সেই জন্যই।

৪. বড় পরিবর্তনের পর গ্যাদার করুন - Job-এর জন্য অপেক্ষা নয়

যেকোনো অপারেশন যা একটি টেবিলকে ব্যাপকভাবে বদলায় - একটি bulk load, একটি বড় purge, একটি partition exchange, একটি migration - তার পরপরই job-এর অংশ হিসেবে statistics গ্যাদার করুন, পরের maintenance window পর্যন্ত optimizer-কে অন্ধ রেখে দেওয়ার বদলে।

-- End of a nightly load job:
BEGIN
  -- ... load data ...
  DBMS_STATS.GATHER_TABLE_STATS('SALES','ORDERS_STG',
     estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
END;
/

এই একটি অভ্যাসই 'পরদিন সকালের' পারফরম্যান্স আগুনের বড় একটি অংশ ঠেকিয়ে দেয়। গতকালের statistics নিয়ে বসে থাকা একটি সদ্য-load-করা টেবিল হলো সেই পাঠ্যপুস্তকীয় কারণ যেখানে একটি রিপোর্ট হঠাৎ একশ কোটি row full-scan করে - যা ORA-01555-এর পেছনের batch-window timing সমস্যা আর পারফরম্যান্স টিউনিং গাইড-এর পদ্ধতির সঙ্গে ঘনিষ্ঠভাবে সম্পর্কিত।

৫. Histogram: Skewed ডেটা সামলানো

মৌলিক কলাম statistics ধরে নেয় ডেটা সমানভাবে ছড়ানো। বাস্তব ডেটা তা কদাচিৎ। ভাবুন একটি ORDER_STATUS কলাম যার ৯৯% 'CLOSED' আর ১% 'OPEN'। histogram ছাড়া optimizer ধরে নেয় প্রতিটি status সমান সম্ভাব্য এবং সেই কলামে filter করা প্রতিটি query-র অনুমান ভুল করে।

একটি histogram optimizer-কে আসল বিন্যাস জানায় - যে 'OPEN' বিরল আর একটি index-এর যোগ্য, আর 'CLOSED' সাধারণ আর একটি full scan-এ ভালোভাবে সেবা পায়। Oracle এগুলো স্বয়ংক্রিয়ভাবে তৈরি করে (SIZE AUTO) এমন কলামে যেগুলো একইসঙ্গে skewed এবং predicate-এ ব্যবহৃত।

-- See which columns have histograms
SELECT column_name, histogram, num_distinct
FROM   dba_tab_col_statistics
WHERE  owner='SALES' AND table_name='ORDERS';

Histogram শক্তিশালী কিন্তু একটি বিখ্যাত জটিলতা নিয়ে আসে: bind variable peeking। Optimizer প্রথম bind value-তে উঁকি দেয়, তার উপযোগী একটি plan বানায়, আর ভিন্ন value নিয়ে পরের execution-গুলোতেও সেই plan পুনরায় ব্যবহার করে - তাই বিরল 'OPEN'-এর জন্য অপ্টিমাইজ করা একটি query সাধারণ 'CLOSED'-এর জন্য খারাপ plan-এ চলতে পারে, বা উল্টোটা। আধুনিক ভার্সনে Adaptive Cursor Sharing এটি কিছুটা প্রশমিত করে, কিন্তু যখন দেখবেন একটি query ভিন্ন ইনপুটের জন্য বিরাট ভিন্ন runtime দেখাচ্ছে, তখন skew সঙ্গে একটি histogram সঙ্গে bind - সেটাই সাধারণ সন্দেহভাজন।

৬. যখন তাজা Stats অবস্থা খারাপ করে: স্থিতিশীলতার টুল

মাঝে মাঝে উল্টো সমস্যা আঘাত করে: statistics ঠিক ছিল, একটি regather সেগুলো সামান্য বদলাল, আর একটি জরুরি plan খারাপ কিছুতে উল্টে গেল। যেসব টেবিলের আচরণ আপনি যত্ন করে টিউন করেছেন - বা যেগুলো এতটাই ভোলাটাইল যে 'নির্ভুল' stats-এর কোনো মানে নেই - সেগুলোতে আপনি নিয়ন্ত্রণ নিতে পারেন।

৬.১ Statistics lock করা

-- Freeze stats on a volatile staging/queue table
EXEC DBMS_STATS.LOCK_TABLE_STATS('SALES','ORDER_QUEUE');
-- (the auto job then skips it; unlock with UNLOCK_TABLE_STATS)

Locking ঠিক সেই ছোট ভোলাটাইল টেবিলগুলোর জন্য (একটি queue যা সারাদিন ০ থেকে ১,০০,০০০ row-এর মধ্যে দোলে) যেখানে আপনি একবার প্রতিনিধিত্বমূলক stats সেট করে সেগুলোই রাখেন, আর সেসব টেবিলের জন্য যেখানে plan গড়ে তুলতে আপনি ইচ্ছাকৃতভাবে stats সেট করেছেন।

৬.২ Pending statistics: commit করার আগে টেস্ট

-- Gather into a 'pending' area WITHOUT affecting live plans
EXEC DBMS_STATS.SET_TABLE_PREFS('SALES','ORDERS','PUBLISH','FALSE');
EXEC DBMS_STATS.GATHER_TABLE_STATS('SALES','ORDERS');

-- Test in your session against the pending stats
ALTER SESSION SET optimizer_use_pending_statistics = TRUE;
-- ... run and check the plans ...

-- Happy? Publish them. Not happy? Delete them.
EXEC DBMS_STATS.PUBLISH_PENDING_STATS('SALES','ORDERS');

একটি সংবেদনশীল সিস্টেমে নতুন stats আনার পেশাদার উপায় হলো pending statistics: সেগুলো গ্যাদার করুন, তারা যে plan তৈরি করে তা যাচাই করুন, আর শুধু তখনই সেগুলো live করুন - production regather-এর পর আর আঙুল ক্রস করে বসে থাকা নয়।

৭. Statistics-এর সাধারণ ভুল

• auto job বন্ধ করে দেওয়া কারণ 'একবার এটি একটি plan বদল ঘটিয়েছিল', তারপর আর কখনও হাতে গ্যাদার না করা - ফলে প্রতিটি টেবিল ধীরে ধীরে stale, ভুল stats-এর দিকে সরে যায়। সেই একটি regression বেসলাইন বা locked stats দিয়ে সারান; job রাখুন।
• পুরনো স্ক্রিপ্টে ANALYZE ব্যবহার। সব জায়গায় DBMS_STATS দিয়ে বদলে দিন।
• ছোট একটি sample হার্ডকোড করা (estimate_percent=>1) ২০০৫ সালের কোনো ব্লগ থেকে কপি করে - AUTO_SAMPLE_SIZE এখন দ্রুততর ও বেশি নির্ভুল।
• bulk load-এর পর গ্যাদার না করা - 'পরদিন সকালের' regression-এর একক বৃহত্তম কারণ।
• 'reset' করতে stats মুছে ফেলা - stats-হীন একটি টেবিল dynamic sampling বা বন্য অনুমান বাধ্য করে; মুছে ফেলার বদলে তাজা stats গ্যাদার করুন।

৮. একটি বাস্তব কেস: যে রিপোর্ট প্রতি সোমবার ভেঙে পড়ত

একটি ডিস্ট্রিবিউশন ক্লায়েন্টের একটি sales রিপোর্ট ছিল যা বেশিরভাগ দিন সেকেন্ডে চলত কিন্তু প্রতি সোমবার ৪০ মিনিট ধরে হামাগুড়ি দিত। প্যাটার্নটাই ছিল সূত্র: সপ্তাহান্তের একটি বড় ডেটা load রবিবার রাতে শেষ হতো, স্বয়ংক্রিয় stats job সর্বশেষ চলেছিল শনিবার, আর তাই সোমবারের রিপোর্ট এমন statistics-এর বিপরীতে অপ্টিমাইজ হতো যারা বিশ্বাস করত নতুন-সবচেয়ে-বড় partition-টি প্রায় খালি - তাই সে কয়েকটি row-এর জন্য বানানো একটি nested-loop plan বেছে নিয়ে সেটি লক্ষ লক্ষ row-এর বিপরীতে চালাত।

সমাধানটি ছিল load job-এ একটি লাইন: সপ্তাহান্তের load শেষ হওয়ার ঠিক পরপরই সংশ্লিষ্ট partition-এ AUTO_SAMPLE_SIZE দিয়ে statistics গ্যাদার করা। সোমবারের ধীরগতি উধাও হয়ে গেল। আমরা একটি ছোট, অতি-ভোলাটাইল lookup টেবিলেও stats lock করলাম যা মাঝে মাঝে plan উল্টে দিচ্ছিল, আর তাতে একবার প্রতিনিধিত্বমূলক value সেট করলাম। দুটি লক্ষ্যভেদী পরিবর্তন, শূন্য parameter অনুমান - কারণ নির্ণয়টি statistics থেকে শুরু হয়েছিল, যেখানে query-র রহস্য প্রায় সবসময়ই শেষ হয়।

সাধারণ জিজ্ঞাসা (FAQ)

Oracle-এ optimizer statistics কী?

এগুলো আপনার ডেটা সম্পর্কে সংরক্ষিত সংখ্যা ও বিন্যাস - টেবিলের row ও block সংখ্যা, কলামের distinct value, high/low value, index selectivity আর histogram - যেগুলো cost-based optimizer row সংখ্যা অনুমান করতে আর execution plan বেছে নিতে ব্যবহার করে। Plan বাছার সময় optimizer কখনও আপনার আসল ডেটা পড়ে না; এটি পুরোপুরি এই statistics-এর ওপর নির্ভর করে।

ANALYZE নাকি DBMS_STATS ব্যবহার করব?

সবসময় DBMS_STATS। পুরনো ANALYZE কমান্ড কেবল backward compatibility-র জন্য টিকে আছে এবং এমন statistics তৈরি করে যা আধুনিক optimizer পুরোপুরি ব্যবহার করে না, আর এতে AUTO_SAMPLE_SIZE, histogram নিয়ন্ত্রণ, locking ও pending statistics-এর মতো ফিচার নেই। সব জায়গায় ANALYZE-এর বদলে DBMS_STATS ব্যবহার করুন।

Statistics গ্যাদার করার পর আমার query ধীর হলো কেন?

নতুন statistics optimizer-এর অনুমান বদলে দিয়ে একটি plan-কে খারাপ plan-এ উল্টে দিতে পারে - বিশেষত histogram আর bind variable-সহ skewed ডেটায়। নতুন stats প্রকাশের আগে টেস্ট করতে pending statistics ব্যবহার করুন, যত্ন করে টিউন করা টেবিলে statistics lock করুন, আর জানা-ভালো plan পিন করতে SQL Plan Baseline বিবেচনা করুন।

Histogram কী আর কখন দরকার হয়?

Histogram skewed কলাম বিন্যাস বর্ণনা করে - যেমন একটি status কলাম যার ৯৯% একটিই value। এটি optimizer-কে বিরল value (index-এর যোগ্য) আর সাধারণ value (full-scan-এ ভালো) আলাদাভাবে বিবেচনা করতে দেয়। Oracle এগুলো স্বয়ংক্রিয়ভাবে তৈরি করে METHOD_OPT SIZE AUTO দিয়ে, এমন কলামে যেগুলো একইসঙ্গে skewed এবং predicate-এ ব্যবহৃত।

auto job-এর ওপর নির্ভর না করে কখন হাতে statistics গ্যাদার করব?

যেকোনো অপারেশনের ঠিক পরে যা টেবিলকে ব্যাপকভাবে বদলায় - bulk load, বড় purge, partition exchange, migration। স্বয়ংক্রিয় job maintenance window-তে চলে আর ~১০% পরিবর্তনে stats-কে stale চিহ্নিত করে, তাই এটি রাতের load আর সকালের রিপোর্টিংয়ের মাঝের ফাঁক ঢাকতে পারে না। load job-এর অংশ হিসেবে গ্যাদার করলে বেশিরভাগ 'পরদিন সকালের' regression ঠেকে যায়।