😨 👨🏾‍🏫 🐧 इन दिनों प्रदर्शन ट्यूनिंग और डेटाबेस समस्या निवारण 💃🏼 😷 👰🏾

दुर्भाग्य से, अब प्रदर्शन ट्यूनिंग और डेटाबेस समस्या निवारण में विशेषज्ञों की भूमिका केवल अंतिम - समस्या निवारण तक ही कट रही है: वे लगभग हमेशा ही विशेषज्ञों की ओर रुख करते हैं, जब समस्याएं पहले से ही एक महत्वपूर्ण बिंदु पर पहुंच चुकी होती हैं, और उन्हें "कल" हल करने की आवश्यकता होती है। और फिर भी यह अच्छा है अगर वे मुड़ते हैं, और एक विस्तृत प्रदर्शन ऑडिट और तनाव परीक्षणों के बिना एक और अधिक महंगा और शक्तिशाली हार्डवेयर खरीदकर समस्या को स्थगित नहीं करते हैं। वास्तव में, निराशा अक्सर पर्याप्त होती है: उन्होंने 2-5 गुना अधिक महंगा उपकरण खरीदा, और प्रदर्शन में केवल 30-40% जीता, जिसमें से पूरी वृद्धि कुछ महीनों में या तो उपयोगकर्ताओं की संख्या में वृद्धि, या डेटा में तेजी से वृद्धि, तर्क की जटिलता के साथ मिलकर हुई है।

और अब, ऐसे समय में जब आर्किटेक्ट, परीक्षक और DevOps इंजीनियरों की संख्या तेजी से बढ़ रही है, और जावा कोर डेवलपर्स स्ट्रिंग्स के साथ भी काम का अनुकूलन कर रहे हैं , धीरे-धीरे लेकिन निश्चित रूप से डेटाबेस ऑप्टिमाइज़र के लिए समय आ रहा है। प्रत्येक रिलीज़ के साथ DBMS इतने अधिक स्मार्ट और अधिक जटिल हो जाते हैं कि प्रलेखित और अनिर्दिष्ट दोनों बारीकियों और अनुकूलन का अध्ययन करने के लिए भारी मात्रा में समय की आवश्यकता होती है। कई लेखों को मासिक रूप से प्रकाशित किया जाता है और ओरेकल को समर्पित प्रमुख सम्मेलन आयोजित किए जाते हैं। सामान्य अनुरूपता के लिए क्षमा करें, लेकिन इस स्थिति में, जब डेटाबेस प्रशासक अनगिनत टॉगल स्विच, बटन, लाइट और स्क्रीन के साथ हवाई जहाज के पायलटों के समान हो जाते हैं, तो प्रदर्शन अनुकूलन की सूक्ष्मताओं के साथ उन्हें लोड करना पहले से ही अशोभनीय है।

बेशक, पायलटों की तरह, डीबीए, ज्यादातर मामलों में आसानी से स्पष्ट और सरल समस्याओं को हल कर सकते हैं जब उन्हें या तो आसानी से निदान किया जाता है या विभिन्न "टॉप्स" (शीर्ष घटनाओं, शीर्ष SQL, शीर्ष खंडों ...) में ध्यान देने योग्य होता है। और जो MOS या Google पर ढूंढना आसान है, भले ही वे समाधान नहीं जानते हों। यह तब और अधिक जटिल होता है, जब लक्षण भी सिस्टम की जटिलता के पीछे छिपे होते हैं और उन्हें ओरेकल डीबीएमएस द्वारा एकत्र की गई नैदानिक जानकारी की भारी मात्रा के बीच फैंकने की आवश्यकता होती है।

इसका सबसे सरल और सबसे महत्वपूर्ण उदाहरण है फ़िल्टर और एक्सेस की भविष्यवाणी का विश्लेषण: बड़ी और भरी हुई प्रणालियों में, अक्सर ऐसा होता है कि इस तरह की समस्या को आसानी से अनदेखा कर दिया जाता है, क्योंकि लोड काफी समान रूप से अलग-अलग अनुरोधों पर फैला हुआ है (विभिन्न तालिकाओं के लिए जुड़ने के साथ, स्थितियों में मामूली अंतर के साथ, आदि), और शीर्ष खंड कुछ खास नहीं दिखाते हैं, वे कहते हैं, "ठीक है, हाँ, इन तालिकाओं से डेटा की सबसे अधिक आवश्यकता होती है और अधिक होती है" । ऐसे मामलों में, आप SYS.COL_USAGE $: col_usage.sql से आंकड़ों के साथ विश्लेषण शुरू कर सकते हैं

col owner format a30 col oname format a30 heading "Object name" col cname format a30 heading "Column name" accept owner_mask prompt "Enter owner mask: "; accept tab_name prompt "Enter tab_name mask: "; accept col_name prompt "Enter col_name mask: "; SELECT a.username as owner ,o.name as oname ,c.name as cname ,u.equality_preds as equality_preds ,u.equijoin_preds as equijoin_preds ,u.nonequijoin_preds as nonequijoin_preds ,u.range_preds as range_preds ,u.like_preds as like_preds ,u.null_preds as null_preds ,to_char(u.timestamp, 'yyyy-mm-dd hh24:mi:ss') when FROM sys.col_usage$ u , sys.obj$ o , sys.col$ c , all_users a WHERE a.user_id = o.owner# AND u.obj# = o.obj# AND u.obj# = c.obj# AND u.intcol# = c.col# AND a.username like upper('&owner_mask') AND o.name like upper('&tab_name') AND c.name like upper('&col_name') ORDER BY a.username, o.name, c.name ; col owner clear; col oname clear; col cname clear; undef tab_name col_name owner_mask;

हालांकि, इस जानकारी के पूर्ण विश्लेषण के लिए पर्याप्त नहीं है, क्योंकि यह विधेय का संयोजन नहीं दिखाता है। इस मामले में, v $ active_session_history और v $ sql_plan का विश्लेषण हमारी सहायता कर सकता है:

 with ash as ( select sql_id ,plan_hash_value ,table_name ,alias ,ACCESS_PREDICATES ,FILTER_PREDICATES ,count(*) cnt from ( select h.sql_id ,h.SQL_PLAN_HASH_VALUE plan_hash_value ,decode(p.OPERATION ,'TABLE ACCESS',p.OBJECT_OWNER||'.'||p.OBJECT_NAME ,(select i.TABLE_OWNER||'.'||i.TABLE_NAME from dba_indexes i where i.OWNER=p.OBJECT_OWNER and i.index_name=p.OBJECT_NAME) ) table_name ,OBJECT_ALIAS ALIAS ,p.ACCESS_PREDICATES ,p.FILTER_PREDICATES -- ,         : -- ,h.sql_plan_operation -- ,h.sql_plan_options -- ,decode(h.session_state,'ON CPU','ON CPU',h.event) event -- ,h.current_obj# from v$active_session_history h ,v$sql_plan p where h.sql_opname='SELECT' and h.IN_SQL_EXECUTION='Y' and h.sql_plan_operation in ('INDEX','TABLE ACCESS') and p.SQL_ID = h.sql_id and p.CHILD_NUMBER = h.SQL_CHILD_NUMBER and p.ID = h.SQL_PLAN_LINE_ID --     3 : -- and h.sample_time >= systimestamp - interval '3' hour ) --       : -- where table_name='&OWNER.&TABNAME' group by sql_id ,plan_hash_value ,table_name ,alias ,ACCESS_PREDICATES ,FILTER_PREDICATES ) ,agg_by_alias as ( select table_name ,regexp_substr(ALIAS,'^[^@]+') ALIAS ,listagg(ACCESS_PREDICATES,' ') within group(order by ACCESS_PREDICATES) ACCESS_PREDICATES ,listagg(FILTER_PREDICATES,' ') within group(order by FILTER_PREDICATES) FILTER_PREDICATES ,sum(cnt) cnt from ash group by sql_id ,plan_hash_value ,table_name ,alias ) ,agg as ( select table_name ,'ALIAS' alias ,replace(access_predicates,'"'||alias||'".','"ALIAS".') access_predicates ,replace(filter_predicates,'"'||alias||'".','"ALIAS".') filter_predicates ,sum(cnt) cnt from agg_by_alias group by table_name ,replace(access_predicates,'"'||alias||'".','"ALIAS".') ,replace(filter_predicates,'"'||alias||'".','"ALIAS".') ) ,cols as ( select table_name ,cols ,access_predicates ,filter_predicates ,sum(cnt)over(partition by table_name,cols) total_by_cols ,cnt from agg ,xmltable( 'string-join(for $c in /ROWSET/ROW/COL order by $c return $c,",")' passing xmltype( cursor( (select distinct nvl( regexp_substr( access_predicates||' '||filter_predicates ,'("'||alias||'"\.|[^.]|^)"([A-Z0-9#_$]+)"([^.]|$)' ,1 ,level ,'i',2 ),' ') col from dual connect by level<=regexp_count( access_predicates||' '||filter_predicates ,'("'||alias||'"\.|[^.]|^)"([A-Z0-9#_$]+)"([^.]|$)' ) ) )) columns cols varchar2(400) path '.' )(+) order by total_by_cols desc, table_name, cnt desc ) select table_name ,cols ,sum(cnt)over(partition by table_name,cols) total_by_cols ,access_predicates ,filter_predicates ,cnt from cols where rownum<=50 order by total_by_cols desc, table_name, cnt desc;

जैसा कि आप स्वयं क्वेरी से देख सकते हैं, यह शीर्ष 50 खोज कॉलम प्रदर्शित करता है और पिछले 3 घंटों में ASH हिट की संख्या से खुद को भविष्यवाणी करता है। इस तथ्य के बावजूद कि एएसएच केवल हर सेकंड में स्नैपशॉट संग्रहीत करता है, लोड किए गए ठिकानों पर नमूना बहुत प्रतिनिधि है। यह कई बिंदुओं को स्पष्ट कर सकता है:

कर्नल फ़ील्ड - खोज कॉलम को स्वयं प्रदर्शित करता है, और इन कॉलमों के संदर्भ में कुल योगों का योग है।
मुझे लगता है कि यह स्पष्ट है कि यह जानकारी अपने आप में समस्या का एक पर्याप्त मार्कर नहीं है, क्योंकि उदाहरण के लिए, कई एक-समय के फुलस्कैन आसानी से आंकड़ों को बर्बाद कर सकते हैं, इसलिए आपको निश्चित रूप से प्रश्नों पर विचार करना होगा और उनकी आवृत्ति (v $ sqlstats, dba_hist_sqlstat)
SQL_ID के अंदर OBJECT_ALIAS द्वारा समूहीकृत करना, plan_hash_value वस्तु के आधार पर सूचकांक और तालिका के संयोजन के लिए महत्वपूर्ण है, क्योंकि सूचकांक के माध्यम से तालिका तक पहुँचने पर, विधेय को योजना की विभिन्न लाइनों में विभाजित किया जाएगा:

आवश्यकता के आधार पर, इस स्क्रिप्ट को अन्य अनुभागों में अतिरिक्त जानकारी एकत्र करने के लिए आसानी से संशोधित किया जा सकता है, उदाहरण के लिए, विभाजन या अपेक्षाओं को ध्यान में रखते हुए। और पहले से ही इस जानकारी का विश्लेषण किया है, तालिका के आँकड़ों और इसके अनुक्रमित, सामान्य डेटा योजना और व्यावसायिक तर्क के विश्लेषण के साथ युग्मित, आप समाधान चुनने के लिए डेवलपर्स या आर्किटेक्ट के लिए सिफारिशें पारित कर सकते हैं, उदाहरण के लिए: विभाजन के विकल्प या अनुक्रमण को बदलते हुए।

SQL * नेट ट्रैफ़िक का विश्लेषण करना भी अक्सर भूल जाता है, और कई सूक्ष्मताएं भी हैं, उदाहरण के लिए: fet-size, SQLNET.COMPRESSION, विस्तारित डेटाटिप्स, जो राउंडट्रिप की संख्या को कम करने की अनुमति देते हैं, आदि, लेकिन यह पहले से ही एक अलग लेख के लिए एक विषय है।

अंत में, मैं यह कहना चाहूंगा कि अब जब उपयोगकर्ता देरी के प्रति कम सहिष्णु हो रहे हैं, तो प्रदर्शन का अनुकूलन एक प्रतिस्पर्धात्मक लाभ बन रहा है।

इन दिनों प्रदर्शन ट्यूनिंग और डेटाबेस समस्या निवारण

More articles: