{"id":93138,"date":"2020-09-03T01:42:51","date_gmt":"2020-09-02T23:42:51","guid":{"rendered":"https:\/\/prohoster.info\/blog\/administrirovanie\/awr-naskolko-ekzadatitsya-rabota-bazy-dannyh"},"modified":"2020-09-03T01:42:51","modified_gmt":"2020-09-02T23:42:51","slug":"awr-naskolko-ekzadatitsya-rabota-bazy-dannyh","status":"publish","type":"post","link":"https:\/\/prohoster.info\/cs\/blog\/administrirovanie\/awr-naskolko-ekzadatitsya-rabota-bazy-dannyh","title":{"rendered":"AWR: jak moc je v\u00fdkon datab\u00e1ze \"exadatizov\u00e1n\"?","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

T\u00edmto kr\u00e1tk\u00fdm p\u0159\u00edsp\u011bvkem bych cht\u011bl objasnit jedno nedorozum\u011bn\u00ed souvisej\u00edc\u00ed s anal\u00fdzou datab\u00e1z\u00ed AWR b\u011b\u017e\u00edc\u00edch na Oracle Exadata. Ji\u017e t\u00e9m\u011b\u0159 10 let se \u010dasto setk\u00e1v\u00e1m s ot\u00e1zkou: jak\u00fd je p\u0159\u00edsp\u011bvek Exadata Software k v\u00fdkonu? Nebo, s pou\u017eit\u00edm novotvar\u016f: jak moc je v\u00fdkon n\u011bjak\u00e9 datab\u00e1ze \"exadatizov\u00e1n\"?<\/p>\n

\"AWR:<\/p>\n

Na tuto spr\u00e1vnou ot\u00e1zku, dle m\u00e9ho n\u00e1zoru, \u010dasto odpov\u00eddaj\u00ed nespr\u00e1vn\u011b s odkazem na statistiky AWR. Obsahuj\u00ed metodu syst\u00e9mov\u00e9ho \u010dek\u00e1n\u00ed, kter\u00e1 vykl\u00e1d\u00e1 \u010das odezvy jako sou\u010det doby pr\u00e1ce procesor\u016f (DB CPU) a doby \u010dek\u00e1n\u00ed r\u016fzn\u00fdch t\u0159\u00edd.<\/p>\n

S p\u0159\u00edchodem Exadata se ve statistik\u00e1ch AWR objevila specifick\u00e1 syst\u00e9mov\u00e1 \u010dek\u00e1n\u00ed spojen\u00e1 s Exadata Software. Obvykle n\u00e1zvy takov\u00fdch \u010dek\u00e1n\u00ed za\u010d\u00ednaj\u00ed slovem \"cell\" (co\u017e ozna\u010duje server Exadata Storage), nej\u010dast\u011bji se vyskytuj\u00edc\u00ed \u010dek\u00e1n\u00ed maj\u00ed v\u00fdsti\u017en\u00e9 n\u00e1zvy jako \"cell smart table scan\", \"cell multiblock physical read\" a \"cell single block physical read\".<\/p>\n

Ve v\u011bt\u0161in\u011b p\u0159\u00edpad\u016f je pod\u00edl t\u011bchto Exadata \u010dek\u00e1n\u00ed na celkov\u00e9 dob\u011b odezvy mal\u00fd, a proto se ani nedostanou do sekce Top10 Foreground Events by Total Wait Time (v tomto p\u0159\u00edpad\u011b je pot\u0159ebn\u00e9 je hledat v sekci Foreground Wait Events). S velk\u00fdm \u00fasil\u00edm jsme u na\u0161ich z\u00e1kazn\u00edk\u016f na\u0161li p\u0159\u00edklad denn\u00ed AWR, ve kter\u00e9m se Exadata \u010dek\u00e1n\u00ed dostala do sekce Top10 a celkov\u011b tvo\u0159ila p\u0159ibli\u017en\u011b 5 %:<\/p>\n

Ud\u00e1lost<\/strong><\/p>\n

\u010cek\u00e1n\u00ed<\/strong><\/p>\n

Celkov\u00fd \u010das \u010dek\u00e1n\u00ed (sekundy)<\/strong><\/p>\n

Pr\u016fm\u011brn\u00e9 \u010dek\u00e1n\u00ed<\/strong><\/p>\n

% DB \u010dasu<\/strong><\/p>\n

T\u0159\u00edda \u010dek\u00e1n\u00ed<\/strong><\/p>\n

DB CPU<\/p>\n

<\/p>\n

115,2K<\/p>\n

<\/p>\n

70.4<\/p>\n

<\/p>\n

SQL*Net more data from dblink<\/p>\n

670,196<\/p>\n

5471.5<\/p>\n

8,16 ms<\/p>\n

3.3<\/p>\n

S\u00ed\u0165<\/p>\n

cell single block physical read<\/p>\n

5,661,452<\/p>\n

3827.6<\/p>\n

676,07 \u00b5s<\/p>\n

2.3<\/p>\n

User I\/O<\/p>\n

Sync ASM rebalance<\/p>\n

4,350,012<\/p>\n

3481.3<\/p>\n

800,30 \u00b5s<\/p>\n

2.1<\/p>\n

Jin\u00e9<\/p>\n

cell multiblock physical read<\/p>\n

759,885<\/p>\n

2252<\/p>\n

2,96 ms<\/p>\n

1.4<\/p>\n

User I\/O<\/p>\n

direct path read<\/p>\n

374,368<\/p>\n

1811.3<\/p>\n

4,84 ms<\/p>\n

1.1<\/p>\n

User I\/O<\/p>\n

SQL*Net message from dblink<\/p>\n

7,983<\/p>\n

1725<\/p>\n

216,08 ms<\/p>\n

1.1<\/p>\n

S\u00ed\u0165<\/p>\n

cell smart table scan<\/p>\n

1,007,520<\/p>\n

1260.7<\/p>\n

1,25 ms<\/p>\n

0.8<\/p>\n

User I\/O<\/p>\n

direct path read temp<\/p>\n

520,211<\/p>\n

808.4<\/p>\n

1,55 ms<\/p>\n

0.5<\/p>\n

User I\/O<\/p>\n

enq: TM \u2014 contention<\/p>\n

652<\/p>\n

795.8<\/p>\n

1220,55 ms<\/p>\n

0.5<\/p>\n

Aplikace<\/p>\n

Z takov\u00e9 AWR statistiky se \u010dasto vyvozuj\u00ed n\u00e1sleduj\u00edc\u00ed z\u00e1v\u011bry:<\/p>\n

1. P\u0159\u00ednos magie Exadata k v\u00fdkonu datab\u00e1ze nen\u00ed vysok\u00fd \u2014 nep\u0159esahuje 5 %, a datab\u00e1ze s Exadata funguje \u0161patn\u011b.<\/p>\n

2. Pokud takovou datab\u00e1zi p\u0159evedeme z Exadata na klasickou architekturu \u201eserver + pole\u201c, v\u00fdkon se nezm\u011bn\u00ed v\u00fdrazn\u011b. Proto\u017ee i kdyby toto pole bylo t\u0159ikr\u00e1t pomalej\u0161\u00ed ne\u017e \u00falo\u017ei\u0161t\u011b Exadata (co\u017e je pro modern\u00ed All Flash pole st\u011b\u017e\u00ed mo\u017en\u00e9), n\u00e1sob\u00edc 5 % t\u0159emi, dostaneme zv\u00fd\u0161en\u00ed pod\u00edlu \u010dek\u00e1n\u00ed na vstup\/v\u00fdstup na 15 % \u2014 takovou datab\u00e1ze ur\u010dit\u011b p\u0159e\u017eije!<\/p>\n

Oba tato vyj\u00e1d\u0159en\u00ed jsou nep\u0159esn\u00e1, nav\u00edc zkresluj\u00ed porozum\u011bn\u00ed my\u0161lence, kterou Exadata Software p\u0159in\u00e1\u0161\u00ed. Exadata nezaji\u0161\u0165uje pouze rychlou I\/O operaci, ale funguje z\u00e1sadn\u011b jinak ve srovn\u00e1n\u00ed s klasickou architekturou \u201eserver + pole\u201c. Pokud datab\u00e1ze skute\u010dn\u011b \u201eexadatizuje\u201c, pak se SQL-logika p\u0159esune na \u00falo\u017en\u00fd syst\u00e9m. Storage servery<\/a> d\u00edky \u0159ad\u011b speci\u00e1ln\u00edch mechanism\u016f (p\u0159edev\u0161\u00edm Exadata Storage Indexes, ale nejen t\u011bmto) samy nach\u00e1zej\u00ed pot\u0159ebn\u00e1 data a p\u0159en\u00e1\u0161ej\u00ed je na DB servery. D\u011blaj\u00ed to dost efektivn\u011b, proto pod\u00edl charakteristick\u00fdch Exadata \u010dek\u00e1n\u00ed v celkov\u00e9m \u010dasu odezvy je mal\u00fd. <\/p>\n

Jak se tento pod\u00edl zm\u011bn\u00ed mimo Exadata? Jak to ovlivn\u00ed v\u00fdkon datab\u00e1ze jako celku? Nejl\u00e9pe na tyto ot\u00e1zky odpov\u00ed testov\u00e1n\u00ed. Nap\u0159\u00edklad \u010dek\u00e1n\u00ed \u201ecell smart table scan\u201c mimo Exadata se m\u016f\u017ee prom\u011bnit v tak t\u011b\u017ek\u00e9 Full Table Scan, \u017ee I\/O zabere cel\u00fd \u010das odezvy a v\u00fdkon se dramaticky zhor\u0161\u00ed. Pr\u00e1v\u011b proto je nespr\u00e1vn\u00e9 p\u0159i anal\u00fdze AWR po\u010d\u00edtat celkov\u00fd procentu\u00e1ln\u00ed pod\u00edl \u010dek\u00e1n\u00ed Exadata jako p\u0159\u00edsp\u011bvek jej\u00ed magie k v\u00fdkonu a t\u00edm m\u00e9n\u011b pou\u017e\u00edvat toto procento pro predikci v\u00fdkonu mimo Exadata. Abyste pochopili, jak moc je pr\u00e1ce datab\u00e1ze \u201eexadatizov\u00e1na\u201c, mus\u00edte prostudovat statistiky AWR sekce \u201eInstance Activity Stats\u201c (tam je spousta statistik s v\u00fdsti\u017en\u00fdmi n\u00e1zvy) a srovn\u00e1vat je mezi sebou.<\/p>\n

Abychom pochopili, jak se datab\u00e1ze bude c\u00edtit mimo Exadata, je nejlep\u0161\u00ed ud\u011blat z backupu klon datab\u00e1ze na c\u00edlov\u00e9 architektu\u0159e a analyzovat v\u00fdkon tohoto klonu pod z\u00e1t\u011b\u017e\u00ed. Takov\u00e1 mo\u017enost obvykle maj\u00ed vlastn\u00edci Exadata.<\/p>\n

Autor: Alexey Struchenko, vedouc\u00ed odd\u011blen\u00ed DB \u201eInfosystemy Jet\u201c<\/em><\/p>\n

Zdroj: habr.com<\/a> <\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"

\u042d\u0442\u0438\u043c \u043d\u0435\u0431\u043e\u043b\u044c\u0448\u0438\u043c \u043f\u043e\u0441\u0442\u043e\u043c \u0445\u043e\u0442\u0435\u043b\u043e\u0441\u044c \u0431\u044b \u0440\u0430\u0437\u0432\u0435\u044f\u0442\u044c \u043e\u0434\u043d\u043e \u043d\u0435\u0434\u043e\u0440\u0430\u0437\u0443\u043c\u0435\u043d\u0438\u0435, \u0441\u0432\u044f\u0437\u0430\u043d\u043d\u043e\u0435 \u0441 \u0430\u043d\u0430\u043b\u0438\u0437\u043e\u043c AWR \u0431\u0430\u0437 \u0434\u0430\u043d\u043d\u044b\u0445, \u0440\u0430\u0431\u043e\u0442\u0430\u044e\u0449\u0438\u0445 \u043d\u0430 Oracle Exadata. \u041f\u043e\u0447\u0442\u0438 10 \u043b\u0435\u0442 \u044f \u043f\u043e\u0441\u0442\u043e\u044f\u043d\u043d\u043e \u0441\u0442\u0430\u043b\u043a\u0438\u0432\u0430\u044e\u0441\u044c \u0441 \u0432\u043e\u043f\u0440\u043e\u0441\u043e\u043c: \u043a\u0430\u043a\u043e\u0432 \u0432\u043a\u043b\u0430\u0434 Exadata Software \u0432 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u044c? \u0418\u043b\u0438 \u0441 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u0435\u043c \u043d\u043e\u0432\u043e\u043e\u0431\u0440\u0430\u0437\u043e\u0432\u0430\u043d\u043d\u044b\u0445 \u0441\u043b\u043e\u0432: \u043d\u0430\u0441\u043a\u043e\u043b\u044c\u043a\u043e \u00ab\u044d\u043a\u0437\u0430\u0434\u0430\u0442\u0438\u0442\u0441\u044f\u00bb \u0440\u0430\u0431\u043e\u0442\u0430 \u0442\u043e\u0439 \u0438\u043b\u0438 \u0438\u043d\u043e\u0439 \u0431\u0430\u0437\u044b \u0434\u0430\u043d\u043d\u044b\u0445? \u0427\u0430\u0441\u0442\u043e \u043d\u0430 \u044d\u0442\u043e\u0442 \u043f\u0440\u0430\u0432\u0438\u043b\u044c\u043d\u044b\u0439 \u0432\u043e\u043f\u0440\u043e\u0441, \u043d\u0430 \u043c\u043e\u0439 \u0432\u0437\u0433\u043b\u044f\u0434, \u0434\u0430\u0435\u0442\u0441\u044f \u043d\u0435\u0432\u0435\u0440\u043d\u044b\u0439 […]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":93139,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-93138","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\n","aioseo_head_json":{"title":"\ud83e\udd47AWR: Jak moc je pr\u00e1ce datab\u00e1ze \u201eexadatizov\u00e1na\u201c? | ProHoster","description":"T\u00edmto mal\u00fdm p\u0159\u00edsp\u011bvkem bych cht\u011bl rozpt\u00fdlit jedno nedorozum\u011bn\u00ed spojen\u00e9 s anal\u00fdzou AWR datab\u00e1z\u00ed b\u011b\u017e\u00edc\u00edch na Oracle Exadata.","canonical_url":"https:\/\/prohoster.info\/cs\/blog\/administrirovanie\/awr-naskolko-ekzadatitsya-rabota-bazy-dannyh","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"cs_CZ","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47AWR: \u043d\u0430\u0441\u043a\u043e\u043b\u044c\u043a\u043e \u00ab\u044d\u043a\u0437\u0430\u0434\u0430\u0442\u0438\u0442\u0441\u044f\u00bb \u0440\u0430\u0431\u043e\u0442\u0430 \u0431\u0430\u0437\u044b \u0434\u0430\u043d\u043d\u044b\u0445? | ProHoster","og:description":"\u042d\u0442\u0438\u043c \u043d\u0435\u0431\u043e\u043b\u044c\u0448\u0438\u043c \u043f\u043e\u0441\u0442\u043e\u043c \u0445\u043e\u0442\u0435\u043b\u043e\u0441\u044c \u0431\u044b \u0440\u0430\u0437\u0432\u0435\u044f\u0442\u044c \u043e\u0434\u043d\u043e \u043d\u0435\u0434\u043e\u0440\u0430\u0437\u0443\u043c\u0435\u043d\u0438\u0435, \u0441\u0432\u044f\u0437\u0430\u043d\u043d\u043e\u0435 \u0441 \u0430\u043d\u0430\u043b\u0438\u0437\u043e\u043c AWR \u0431\u0430\u0437 \u0434\u0430\u043d\u043d\u044b\u0445, \u0440\u0430\u0431\u043e\u0442\u0430\u044e\u0449\u0438\u0445 \u043d\u0430 Oracle Exadata.","og:url":"https:\/\/prohoster.info\/cs\/blog\/administrirovanie\/awr-naskolko-ekzadatitsya-rabota-bazy-dannyh","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2020-09-02T23:42:51+00:00","article:modified_time":"2020-09-02T23:42:51+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"93138","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":null,"breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-02-28 11:54:01","updated":"2026-02-09 16:53:35","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts\/93138","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/comments?post=93138"}],"version-history":[{"count":1,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts\/93138\/revisions"}],"predecessor-version":[{"id":159143,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts\/93138\/revisions\/159143"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/media\/93139"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/media?parent=93138"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/categories?post=93138"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/tags?post=93138"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}