{"id":537,"date":"2017-08-06T10:00:27","date_gmt":"2017-08-06T08:00:27","guid":{"rendered":"http:\/\/explainit.pl\/wordpress\/?p=537"},"modified":"2017-09-06T10:11:58","modified_gmt":"2017-09-06T08:11:58","slug":"statistics_levelall-zyski-vs-koszty","status":"publish","type":"post","link":"https:\/\/explainit.pl\/wordpress\/2017\/08\/06\/statistics_levelall-zyski-vs-koszty\/","title":{"rendered":"STATISTICS_LEVEL=ALL – zyski vs. koszty"},"content":{"rendered":"

Dwa z najbardziej atrakcyjnych mechanizm\u00f3w optymalizatora zapyta\u0144 Oracle Database 12c – „cardinality feedback” i „adaptive cursor sharing” – do prawid\u0142owego dzia\u0142ania wymagaj\u0105 (o czym cz\u0119sto ze zdziwieniem przekonuj\u0105 si\u0119 administratorzy) prze\u0142\u0105czenia parametru inicjalizacyjnego STATISTICS_LEVEL<\/a><\/code> na poziom \"ALL\"<\/code> (domy\u015blnie \"TYPICAL\"<\/code>). Przy takim ustawieniu gromadzone s\u0105 dodatkowe wska\u017aniki wydajno\u015bciowe: Plan Execution Statistics i Timed OS Statistics (zainteresowanych tym, jakie wska\u017aniki s\u0105 gromadzone przy r\u00f3\u017cnych ustawieniach kieruj\u0119 do analizy wyniku zapytania \"select statistics_name,<\/code> description, activation_level<\/code> from v$statistics_level\"<\/code>).
\n
\nPoniewa\u017c cz\u0119sto powraca do nas pytanie o wielko\u015b\u0107 dodatkowych narzut\u00f3w systemowych zwi\u0105zanych z aktywacj\u0105 gromadzenia tych dodatkowych wska\u017anik\u00f3w (a np. Burleson<\/a> pisze tylko „there will be extra overhead „), to z tego powodu postanowili\u015bmy przeprowadzi\u0107 eksperyment na rzeczywistym systemie. Eksperyment polega\u0142 na serii powtarzanych pomiar\u00f3w sumarycznego czasu odpowiedzi (DB Time) serwera Oracle Database 12.2 na nast\u0119puj\u0105c\u0105 prost\u0105 mieszank\u0119 250 tysi\u0119cy operacji SQL: 15% pe\u0142ne odczyty du\u017cych tabel, 25% pe\u0142ne odczyty ma\u0142ych tabel, 30% odczyty indeksowe punktowe, 30% odczyty indeksowe zakresowe.
\n
\nOto rezultaty:
\n– STATISTICS_LEVEL=TYPICAL<\/code>: \u015bredni DB Time = 506 s
\n– STATISTICS_LEVEL=ALL<\/code>: \u015bredni DB Time = 512 s
\n
\nDodatkowy narzut systemowy zwi\u0105zany z prze\u0142\u0105czeniem STATISTICS_LEVEL<\/code> na \"ALL\"<\/code> wyni\u00f3s\u0142 zatem tylko 1.2%. Wydaje si\u0119 zatem, \u017ce kosztuje to mniej, ni\u017c wyni\u00f3s\u0142by zysk, jaki w systemie „mocno zaniedbanym” mogliby\u015bmy osi\u0105gn\u0105\u0107 dzi\u0119ki lepszej optymalizacji zapyta\u0144. Oczywi\u015bcie nasz eksperyment ma wy\u0142\u0105cznie charakter pogl\u0105dowy i jest uwarunkowany sprz\u0119tem, skomplikowaniem struktury bazy danych, specyfik\u0105 zapyta\u0144 SQL, itp.
\n
\nPS. Uwaga – STATISTICS_LEVEL=ALL<\/code> zwi\u0119ksza tak\u017ce ilo\u015b\u0107 informacji rejestrowanych w snapshotach AWR, wi\u0119c przy okazji nale\u017cy liczy\u0107 si\u0119 z wi\u0119kszym zu\u017cyciem miejsca w przestrzeni tabel SYSAUX.<\/p>\n","protected":false},"excerpt":{"rendered":"

Dwa z najbardziej atrakcyjnych mechanizm\u00f3w optymalizatora zapyta\u0144 Oracle Database 12c – „cardinality feedback” i „adaptive cursor sharing” – do prawid\u0142owego dzia\u0142ania wymagaj\u0105 (o czym cz\u0119sto ze zdziwieniem przekonuj\u0105 si\u0119 administratorzy) prze\u0142\u0105czenia parametru inicjalizacyjnego STATISTICS_LEVEL na poziom „ALL” (domy\u015blnie „TYPICAL”). Przy takim ustawieniu gromadzone s\u0105 dodatkowe wska\u017aniki wydajno\u015bciowe: Plan Execution Statistics i Timed OS Statistics (zainteresowanych Read more about STATISTICS_LEVEL=ALL – zyski vs. koszty<\/span>[…]<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[2],"tags":[12,21],"yoast_head":"\nSTATISTICS_LEVEL=ALL - zyski vs. koszty - Explain IT<\/title>\n<meta name=\"description\" content=\"STATISTICS_LEVEL=ALL - zyski vs. koszty\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/explainit.pl\/wordpress\/2017\/08\/06\/statistics_levelall-zyski-vs-koszty\/\" \/>\n<meta property=\"og:locale\" content=\"pl_PL\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"STATISTICS_LEVEL=ALL - zyski vs. koszty - Explain IT\" \/>\n<meta property=\"og:description\" content=\"STATISTICS_LEVEL=ALL - zyski vs. koszty\" \/>\n<meta property=\"og:url\" content=\"https:\/\/explainit.pl\/wordpress\/2017\/08\/06\/statistics_levelall-zyski-vs-koszty\/\" \/>\n<meta property=\"og:site_name\" content=\"Explain IT\" \/>\n<meta property=\"article:published_time\" content=\"2017-08-06T08:00:27+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-09-06T08:11:58+00:00\" \/>\n<meta name=\"author\" content=\"Maciej Zakrzewicz\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Napisane przez\" \/>\n\t<meta name=\"twitter:data1\" content=\"Maciej Zakrzewicz\" \/>\n\t<meta name=\"twitter:label2\" content=\"Szacowany czas czytania\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minuta\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/explainit.pl\/wordpress\/2017\/08\/06\/statistics_levelall-zyski-vs-koszty\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/explainit.pl\/wordpress\/2017\/08\/06\/statistics_levelall-zyski-vs-koszty\/\"},\"author\":{\"name\":\"Maciej Zakrzewicz\",\"@id\":\"https:\/\/explainit.pl\/wordpress\/#\/schema\/person\/740773131bc169a2dc9c0e5e07476219\"},\"headline\":\"STATISTICS_LEVEL=ALL – zyski vs. koszty\",\"datePublished\":\"2017-08-06T08:00:27+00:00\",\"dateModified\":\"2017-09-06T08:11:58+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/explainit.pl\/wordpress\/2017\/08\/06\/statistics_levelall-zyski-vs-koszty\/\"},\"wordCount\":274,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/explainit.pl\/wordpress\/#organization\"},\"keywords\":[\"optymalizator\",\"wydajno\u015b\u0107\"],\"articleSection\":[\"Oracle Database\"],\"inLanguage\":\"pl-PL\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/explainit.pl\/wordpress\/2017\/08\/06\/statistics_levelall-zyski-vs-koszty\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/explainit.pl\/wordpress\/2017\/08\/06\/statistics_levelall-zyski-vs-koszty\/\",\"url\":\"https:\/\/explainit.pl\/wordpress\/2017\/08\/06\/statistics_levelall-zyski-vs-koszty\/\",\"name\":\"STATISTICS_LEVEL=ALL - zyski vs. koszty - Explain IT\",\"isPartOf\":{\"@id\":\"https:\/\/explainit.pl\/wordpress\/#website\"},\"datePublished\":\"2017-08-06T08:00:27+00:00\",\"dateModified\":\"2017-09-06T08:11:58+00:00\",\"description\":\"STATISTICS_LEVEL=ALL - zyski vs. koszty\",\"breadcrumb\":{\"@id\":\"https:\/\/explainit.pl\/wordpress\/2017\/08\/06\/statistics_levelall-zyski-vs-koszty\/#breadcrumb\"},\"inLanguage\":\"pl-PL\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/explainit.pl\/wordpress\/2017\/08\/06\/statistics_levelall-zyski-vs-koszty\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/explainit.pl\/wordpress\/2017\/08\/06\/statistics_levelall-zyski-vs-koszty\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Strona g\u0142\u00f3wna\",\"item\":\"https:\/\/explainit.pl\/wordpress\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"STATISTICS_LEVEL=ALL – zyski vs. koszty\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/explainit.pl\/wordpress\/#website\",\"url\":\"https:\/\/explainit.pl\/wordpress\/\",\"name\":\"Explain IT\",\"description\":\"Autorska pracownia doradztwa i szkole\u0144 IT - Maciej Zakrzewicz - szkolenia, ekspertyzy, wdro\u017cenia. Technologie Oracle, PostgreSQL, MySQL.\",\"publisher\":{\"@id\":\"https:\/\/explainit.pl\/wordpress\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/explainit.pl\/wordpress\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"pl-PL\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/explainit.pl\/wordpress\/#organization\",\"name\":\"Explain IT\",\"url\":\"https:\/\/explainit.pl\/wordpress\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"pl-PL\",\"@id\":\"https:\/\/explainit.pl\/wordpress\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/explainit.pl\/wordpress\/wp-content\/uploads\/2016\/08\/explainITmini.png\",\"contentUrl\":\"https:\/\/explainit.pl\/wordpress\/wp-content\/uploads\/2016\/08\/explainITmini.png\",\"width\":196,\"height\":64,\"caption\":\"Explain IT\"},\"image\":{\"@id\":\"https:\/\/explainit.pl\/wordpress\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/explainit.pl\/wordpress\/#\/schema\/person\/740773131bc169a2dc9c0e5e07476219\",\"name\":\"Maciej Zakrzewicz\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"pl-PL\",\"@id\":\"https:\/\/explainit.pl\/wordpress\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/9c98d020128b142a480aa35a22300a69?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/9c98d020128b142a480aa35a22300a69?s=96&d=mm&r=g\",\"caption\":\"Maciej Zakrzewicz\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"STATISTICS_LEVEL=ALL - zyski vs. koszty - Explain IT","description":"STATISTICS_LEVEL=ALL - zyski vs. koszty","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/explainit.pl\/wordpress\/2017\/08\/06\/statistics_levelall-zyski-vs-koszty\/","og_locale":"pl_PL","og_type":"article","og_title":"STATISTICS_LEVEL=ALL - zyski vs. koszty - Explain IT","og_description":"STATISTICS_LEVEL=ALL - zyski vs. koszty","og_url":"https:\/\/explainit.pl\/wordpress\/2017\/08\/06\/statistics_levelall-zyski-vs-koszty\/","og_site_name":"Explain IT","article_published_time":"2017-08-06T08:00:27+00:00","article_modified_time":"2017-09-06T08:11:58+00:00","author":"Maciej Zakrzewicz","twitter_card":"summary_large_image","twitter_misc":{"Napisane przez":"Maciej Zakrzewicz","Szacowany czas czytania":"1 minuta"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/explainit.pl\/wordpress\/2017\/08\/06\/statistics_levelall-zyski-vs-koszty\/#article","isPartOf":{"@id":"https:\/\/explainit.pl\/wordpress\/2017\/08\/06\/statistics_levelall-zyski-vs-koszty\/"},"author":{"name":"Maciej Zakrzewicz","@id":"https:\/\/explainit.pl\/wordpress\/#\/schema\/person\/740773131bc169a2dc9c0e5e07476219"},"headline":"STATISTICS_LEVEL=ALL – zyski vs. koszty","datePublished":"2017-08-06T08:00:27+00:00","dateModified":"2017-09-06T08:11:58+00:00","mainEntityOfPage":{"@id":"https:\/\/explainit.pl\/wordpress\/2017\/08\/06\/statistics_levelall-zyski-vs-koszty\/"},"wordCount":274,"commentCount":0,"publisher":{"@id":"https:\/\/explainit.pl\/wordpress\/#organization"},"keywords":["optymalizator","wydajno\u015b\u0107"],"articleSection":["Oracle Database"],"inLanguage":"pl-PL","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/explainit.pl\/wordpress\/2017\/08\/06\/statistics_levelall-zyski-vs-koszty\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/explainit.pl\/wordpress\/2017\/08\/06\/statistics_levelall-zyski-vs-koszty\/","url":"https:\/\/explainit.pl\/wordpress\/2017\/08\/06\/statistics_levelall-zyski-vs-koszty\/","name":"STATISTICS_LEVEL=ALL - zyski vs. koszty - Explain IT","isPartOf":{"@id":"https:\/\/explainit.pl\/wordpress\/#website"},"datePublished":"2017-08-06T08:00:27+00:00","dateModified":"2017-09-06T08:11:58+00:00","description":"STATISTICS_LEVEL=ALL - zyski vs. koszty","breadcrumb":{"@id":"https:\/\/explainit.pl\/wordpress\/2017\/08\/06\/statistics_levelall-zyski-vs-koszty\/#breadcrumb"},"inLanguage":"pl-PL","potentialAction":[{"@type":"ReadAction","target":["https:\/\/explainit.pl\/wordpress\/2017\/08\/06\/statistics_levelall-zyski-vs-koszty\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/explainit.pl\/wordpress\/2017\/08\/06\/statistics_levelall-zyski-vs-koszty\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Strona g\u0142\u00f3wna","item":"https:\/\/explainit.pl\/wordpress\/"},{"@type":"ListItem","position":2,"name":"STATISTICS_LEVEL=ALL – zyski vs. koszty"}]},{"@type":"WebSite","@id":"https:\/\/explainit.pl\/wordpress\/#website","url":"https:\/\/explainit.pl\/wordpress\/","name":"Explain IT","description":"Autorska pracownia doradztwa i szkole\u0144 IT - Maciej Zakrzewicz - szkolenia, ekspertyzy, wdro\u017cenia. Technologie Oracle, PostgreSQL, MySQL.","publisher":{"@id":"https:\/\/explainit.pl\/wordpress\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/explainit.pl\/wordpress\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"pl-PL"},{"@type":"Organization","@id":"https:\/\/explainit.pl\/wordpress\/#organization","name":"Explain IT","url":"https:\/\/explainit.pl\/wordpress\/","logo":{"@type":"ImageObject","inLanguage":"pl-PL","@id":"https:\/\/explainit.pl\/wordpress\/#\/schema\/logo\/image\/","url":"https:\/\/explainit.pl\/wordpress\/wp-content\/uploads\/2016\/08\/explainITmini.png","contentUrl":"https:\/\/explainit.pl\/wordpress\/wp-content\/uploads\/2016\/08\/explainITmini.png","width":196,"height":64,"caption":"Explain IT"},"image":{"@id":"https:\/\/explainit.pl\/wordpress\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/explainit.pl\/wordpress\/#\/schema\/person\/740773131bc169a2dc9c0e5e07476219","name":"Maciej Zakrzewicz","image":{"@type":"ImageObject","inLanguage":"pl-PL","@id":"https:\/\/explainit.pl\/wordpress\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/9c98d020128b142a480aa35a22300a69?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/9c98d020128b142a480aa35a22300a69?s=96&d=mm&r=g","caption":"Maciej Zakrzewicz"}}]}},"_links":{"self":[{"href":"https:\/\/explainit.pl\/wordpress\/wp-json\/wp\/v2\/posts\/537"}],"collection":[{"href":"https:\/\/explainit.pl\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/explainit.pl\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/explainit.pl\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/explainit.pl\/wordpress\/wp-json\/wp\/v2\/comments?post=537"}],"version-history":[{"count":6,"href":"https:\/\/explainit.pl\/wordpress\/wp-json\/wp\/v2\/posts\/537\/revisions"}],"predecessor-version":[{"id":543,"href":"https:\/\/explainit.pl\/wordpress\/wp-json\/wp\/v2\/posts\/537\/revisions\/543"}],"wp:attachment":[{"href":"https:\/\/explainit.pl\/wordpress\/wp-json\/wp\/v2\/media?parent=537"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/explainit.pl\/wordpress\/wp-json\/wp\/v2\/categories?post=537"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/explainit.pl\/wordpress\/wp-json\/wp\/v2\/tags?post=537"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}