{"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\/sk\/blog\/administrirovanie\/awr-naskolko-ekzadatitsya-rabota-bazy-dannyh","title":{"rendered":"AWR: Ako ve\u013emi sa \u201eex\u00e1datizuje\u201c pr\u00e1ca datab\u00e1zy?","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

T\u00fdmto kr\u00e1tkym pr\u00edspevkom by som r\u00e1d objasnil jedno nedorozumenie spojen\u00e9 s anal\u00fdzou AWR datab\u00e1z be\u017eiacich na Oracle Exadata. U\u017e takmer 10 rokov sa pravidelne stret\u00e1vam s ot\u00e1zkou: ak\u00fd je pr\u00ednos Exadata Software na v\u00fdkonnos\u0165? Alebo s pou\u017eit\u00edm novotvarov: do akej miery sa \u201eexadatuje\u201c pr\u00e1ca tej \u010di onej datab\u00e1zy?<\/p>\n

\"AWR:<\/p>\n

\u010casto na t\u00fato spr\u00e1vnu ot\u00e1zku, pod\u013ea m\u00f4jho n\u00e1zoru, prich\u00e1dza nespr\u00e1vna odpove\u010f, zvy\u010dajne s odkazom na \u0161tatistiku AWR. Obsahuje met\u00f3du syst\u00e9mov\u00fdch \u010dakac\u00edch \u010dasov, ktor\u00e1 interpretuje \u010das odozvy ako s\u00fa\u010det \u010dasu pr\u00e1ce procesorov (DB CPU) a \u010dasu \u010dakania r\u00f4znych tried.<\/p>\n

S pr\u00edchodom Exadata sa v \u0161tatistike AWR objavili \u0161pecifick\u00e9 syst\u00e9mov\u00e9 \u010dakacie \u010dasy spojen\u00e9 s pr\u00e1cou Exadata Software. Zvy\u010dajne n\u00e1zvy tak\u00fdchto \u010dakac\u00edch \u010dasov za\u010d\u00ednaj\u00fa slovom \"cell\" (\u010dl\u00e1nkom sa naz\u00fdva Exadata Storage server), pri\u010dom naj\u010dastej\u0161ie sa vyskytuj\u00fa \u010dakacie \u010dasy s v\u00fdsti\u017en\u00fdmi n\u00e1zvami \"cell smart table scan\", \"cell multiblock physical read\" a \"cell single block physical read\".<\/p>\n

V drvivej v\u00e4\u010d\u0161ine pr\u00edpadov podiel t\u00fdchto Exadata-\u010dakan\u00ed na celkovom \u010dase odozvy je mal\u00fd, a preto sa ani nedostan\u00fa do sekcie Top10 Foreground Events by Total Wait Time (v tomto pr\u00edpade ich treba h\u013eada\u0165 v sekcii Foreground Wait Events). S ve\u013ek\u00fdm \u00fasil\u00edm sme u na\u0161ich z\u00e1kazn\u00edkov objavili pr\u00edklad denn\u00fdch AWR, v ktorom Exadata-\u010dakania zasiahli sekciu Top10 a v s\u00fahrne predstavovali pribli\u017ene 5%:<\/p>\n

Udalos\u0165<\/strong><\/p>\n

\u010cakania<\/strong><\/p>\n

Celkov\u00fd \u010das \u010dakania (sec)<\/strong><\/p>\n

Priemern\u00e9 \u010dakanie<\/strong><\/p>\n

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

Trieda \u010dakania<\/strong><\/p>\n

DB CPU<\/p>\n

<\/p>\n

115.2K<\/p>\n

<\/p>\n

70.4<\/p>\n

<\/p>\n

SQL*Net \u010fal\u0161ie \u00fadaje z dblink<\/p>\n

670,196<\/p>\n

5471.5<\/p>\n

8.16ms<\/p>\n

3.3<\/p>\n

Sie\u0165<\/p>\n

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

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

3827.6<\/p>\n

676.07us<\/p>\n

2.3<\/p>\n

Pou\u017e\u00edvate\u013e I\/O<\/p>\n

Sync ASM rebalance<\/p>\n

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

3481.3<\/p>\n

800.30us<\/p>\n

2.1<\/p>\n

In\u00e9<\/p>\n

cell multiblock physical read<\/p>\n

759,885<\/p>\n

2252<\/p>\n

2.96ms<\/p>\n

1.4<\/p>\n

Pou\u017e\u00edvate\u013e I\/O<\/p>\n

priamy path read<\/p>\n

374,368<\/p>\n

1811.3<\/p>\n

4.84ms<\/p>\n

1.1<\/p>\n

Pou\u017e\u00edvate\u013e I\/O<\/p>\n

SQL*Net spr\u00e1va z dblink<\/p>\n

7,983<\/p>\n

1725<\/p>\n

216.08ms<\/p>\n

1.1<\/p>\n

Sie\u0165<\/p>\n

cell smart table scan<\/p>\n

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

1260.7<\/p>\n

1.25ms<\/p>\n

0.8<\/p>\n

Pou\u017e\u00edvate\u013e I\/O<\/p>\n

priamy path read temp<\/p>\n

520,211<\/p>\n

808.4<\/p>\n

1.55ms<\/p>\n

0.5<\/p>\n

Pou\u017e\u00edvate\u013e I\/O<\/p>\n

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

652<\/p>\n

795.8<\/p>\n

1220.55ms<\/p>\n

0.5<\/p>\n

Aplik\u00e1cia<\/p>\n

Z takejto AWR \u0161tatistiky sa \u010dasto robia nasleduj\u00face z\u00e1very:<\/p>\n

1. Pr\u00edspevok Exadata k v\u00fdkonnosti datab\u00e1zy nie je vysok\u00fd \u2014 nepresahuje 5 %, a datab\u00e1za sa \u201eexadatuje\u201c zle.<\/p>\n

2. Ak by sa t\u00e1to datab\u00e1za preniesla z Exadata na klasick\u00fa architekt\u00faru \u201eserver + pole\u201c, v\u00fdkonnos\u0165 sa nezmen\u00ed v\u00fdrazne. Preto\u017ee aj ak by sa toto pole uk\u00e1zalo trikr\u00e1t pomal\u0161ie ako \u00falo\u017en\u00fd syst\u00e9m Exadata (\u010do je sotva mo\u017en\u00e9 pre modern\u00e9 All Flash polia), tak vyn\u00e1soben\u00edm 5% tromi dostaneme zv\u00fd\u0161enie podielu \u010dakan\u00ed na vstup a v\u00fdstup na 15% \u2014 tak\u00fa datab\u00e1zu ur\u010dite pre\u017eije!<\/p>\n

Obidva tieto v\u00fdstupy s\u00fa nepresn\u00e9, navy\u0161e skres\u013euj\u00fa ch\u00e1panie my\u0161lienky, ktor\u00e1 je zakotven\u00e1 v Exadata Software. Exadata nielen zabezpe\u010duje r\u00fdchly vstup-v\u00fdstup, ale funguje z\u00e1sadne inak v porovnan\u00ed s klasickou architekt\u00farou \u201eserver + pole\u201c. Ak je pr\u00e1ca datab\u00e1zy naozaj \u201eexadatuj\u00faca\u201c, potom sa na syst\u00e9m ukladania pren\u00e1\u0161a SQL-logika. Storage servery<\/a> v\u010faka rade \u0161peci\u00e1lnych mechanizmov (predov\u0161etk\u00fdm Exadata Storage Indexes, ale nielen) samy nach\u00e1dzaj\u00fa potrebn\u00e9 \u00fadaje a pren\u00e1\u0161aj\u00fa ich DB serverom. Robia to dostato\u010dne efekt\u00edvne, preto podiel charakteristick\u00fdch Exadata \u010dakac\u00edch obdob\u00ed v celkovom \u010dase odozvy je mal\u00fd. <\/p>\n

Ako sa tento podiel zmen\u00ed mimo Exadata? Ako to ovplyvn\u00ed v\u00fdkon datab\u00e1zy ako celku? Najlep\u0161ie odpovede na tieto ot\u00e1zky d\u00e1 testovanie. Napr\u00edklad \u010dakanie \u201ecell smart table scan\u201c mimo Exadata sa m\u00f4\u017ee zmeni\u0165 na tak\u00fd \u0165a\u017ek\u00fd Table Full Scan, \u017ee vstup-v\u00fdstup zaberie cel\u00fd \u010das odozvy a v\u00fdkon sa dramaticky zhor\u0161\u00ed. Preto je nespr\u00e1vne pri anal\u00fdze AWR po\u010d\u00edta\u0165 celkov\u00fd percentu\u00e1lny podiel \u010dakac\u00edch obdob\u00ed Exadata ako pr\u00edspevok jej m\u00e1gie do v\u00fdkonu a u\u017e v\u00f4bec tento percentu\u00e1lny podiel pou\u017e\u00edva\u0165 na predpovedanie v\u00fdkonu mimo Exadata. Aby ste pochopili, ako ve\u013emi je pr\u00e1ca datab\u00e1zy \u201eexadatuj\u00faca\u201c, potrebujete sk\u00fama\u0165 \u0161tatistiky AWR sekcie \u201eInstance Activity Stats\u201c (tam je ve\u013ea \u0161tatist\u00edk s v\u00fdsti\u017en\u00fdmi n\u00e1zvami) a porovn\u00e1va\u0165 ich medzi sebou.<\/p>\n

Aby ste pochopili, ako sa bude datab\u00e1za c\u00edti\u0165 mimo Exadata, je najlep\u0161ie vytvori\u0165 z backupu k\u00f3piu datab\u00e1zy na cie\u013eovej architekt\u00fare a analyzova\u0165 v\u00fdkon tejto k\u00f3pie pod za\u0165a\u017een\u00edm. Tak\u00e1to mo\u017enos\u0165 je zvy\u010dajne dostupn\u00e1 pre vlastn\u00edkov Exadata.<\/p>\n

Autor: Alexey Struchenko, ved\u00faci oddelenia 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: Ako ve\u013emi je pr\u00e1ca datab\u00e1zy \u201eexadatuj\u00faca\u201c? | ProHoster","description":"T\u00fdmto mal\u00fdm pr\u00edspevkom by som chcel rozpt\u00fdli\u0165 jedno nedorozumenie t\u00fdkaj\u00face sa anal\u00fdzy AWR datab\u00e1z be\u017eiacich na Oracle Exadata.","canonical_url":"https:\/\/prohoster.info\/sk\/blog\/administrirovanie\/awr-naskolko-ekzadatitsya-rabota-bazy-dannyh","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"sk_SK","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\/sk\/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\/sk\/wp-json\/wp\/v2\/posts\/93138","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/comments?post=93138"}],"version-history":[{"count":1,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/posts\/93138\/revisions"}],"predecessor-version":[{"id":159143,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/posts\/93138\/revisions\/159143"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/media\/93139"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/media?parent=93138"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/categories?post=93138"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/tags?post=93138"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}