{"id":23,"date":"2012-12-10T19:58:15","date_gmt":"2012-12-10T18:58:15","guid":{"rendered":"http:\/\/explainit.pl\/blog\/?p=7"},"modified":"2016-08-09T21:05:27","modified_gmt":"2016-08-09T19:05:27","slug":"sledzenie-pracy-optymalizatora-zapytan-oracle-database","status":"publish","type":"post","link":"https:\/\/explainit.pl\/wordpress\/2012\/12\/10\/sledzenie-pracy-optymalizatora-zapytan-oracle-database\/","title":{"rendered":"\u015aledzenie pracy optymalizatora zapyta\u0144 Oracle Database"},"content":{"rendered":"

Dzia\u0142anie kosztowego optymalizatora zapyta\u0144 w uproszczeniu polega na: (1) transformacji tekstu zapytania SQL, (2) generowaniu alternatywnych plan\u00f3w wykonania zapytania, (3) estymacji ich koszt\u00f3w, (4) wyborze tego planu wykonania, kt\u00f3rego estymowany koszt jest najni\u017cszy. Transformacja tekstu zapytania SQL mo\u017ce obejmowa\u0107 np. scalanie definicji perspektywy, \u201ewpychanie\u201d predykat\u00f3w, rozwijanie podzapyta\u0144, przepisywanie zapyta\u0144 na perspektywy materializowane. Z kolei alternatywne plany wykonania zapytania mog\u0105 si\u0119 r\u00f3\u017cni\u0107 np. kolejno\u015bci\u0105 \u0142\u0105czenia tabel, algorytmami \u0142\u0105czenia tabel, wyborem indeks\u00f3w. Programista\/administrator mo\u017ce oczywi\u015bcie w \u0142atwy spos\u00f3b pozna\u0107 zwyci\u0119ski plan wykonania zapytania. Ale jak \u015bledzi\u0107 po\u015brednie czynno\u015bci wykonywane przez optymalizator na drodze do tego planu? Pomoc\u0105 mo\u017ce by\u0107 niejawna funkcja serwera bazy danych: zdarzenie 10053.<\/p>\n

Po wykonaniu w bie\u017c\u0105cej sesji polecenia:<\/p>\n

alter session set events '10053 trace name context forever, level 1';<\/code><\/p>\n

serwer utworzy plik \u015bladu (w lokalizacji USER_DUMP_DEST), w kt\u00f3rym opisywane b\u0119d\u0105 wewn\u0119trzne czynno\u015bci optymalizatora zapyta\u0144 (a\u017c do zako\u0144czenia sesji). Zawarto\u015b\u0107 pliku \u015bladu obejmie: parametry optymalizatora, statystyki indeks\u00f3w, statystyki kolumn, koszty operacji odczytu i \u0142\u0105czenia tabel, kolejno\u015bci i algorytmy \u0142\u0105czenia tabel. Lektura tego pliku pozwala lepiej rozumie\u0107 subtelno\u015bci funkcjonowania optymalizatora, a tak\u017ce rozpoznawa\u0107 powody jego nierzadkich b\u0142\u0119dnych wybor\u00f3w.<\/p>\n","protected":false},"excerpt":{"rendered":"

Dzia\u0142anie kosztowego optymalizatora zapyta\u0144 w uproszczeniu polega na: (1) transformacji tekstu zapytania SQL, (2) generowaniu alternatywnych plan\u00f3w wykonania zapytania, (3) estymacji ich koszt\u00f3w, (4) wyborze tego planu wykonania, kt\u00f3rego estymowany koszt jest najni\u017cszy. Transformacja tekstu zapytania SQL mo\u017ce obejmowa\u0107 np. scalanie definicji perspektywy, \u201ewpychanie\u201d predykat\u00f3w, rozwijanie podzapyta\u0144, przepisywanie zapyta\u0144 na perspektywy materializowane. Z kolei alternatywne Read more about \u015aledzenie pracy optymalizatora zapyta\u0144 Oracle Database<\/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],"yoast_head":"\n\u015aledzenie pracy optymalizatora zapyta\u0144 Oracle Database - Explain IT<\/title>\n<meta name=\"description\" content=\"\u015aledzenie pracy optymalizatora zapyta\u0144 Oracle Database\" \/>\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\/2012\/12\/10\/sledzenie-pracy-optymalizatora-zapytan-oracle-database\/\" \/>\n<meta property=\"og:locale\" content=\"pl_PL\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"\u015aledzenie pracy optymalizatora zapyta\u0144 Oracle Database - Explain IT\" \/>\n<meta property=\"og:description\" content=\"\u015aledzenie pracy optymalizatora zapyta\u0144 Oracle Database\" \/>\n<meta property=\"og:url\" content=\"https:\/\/explainit.pl\/wordpress\/2012\/12\/10\/sledzenie-pracy-optymalizatora-zapytan-oracle-database\/\" \/>\n<meta property=\"og:site_name\" content=\"Explain IT\" \/>\n<meta property=\"article:published_time\" content=\"2012-12-10T18:58:15+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2016-08-09T19:05:27+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\/2012\/12\/10\/sledzenie-pracy-optymalizatora-zapytan-oracle-database\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/explainit.pl\/wordpress\/2012\/12\/10\/sledzenie-pracy-optymalizatora-zapytan-oracle-database\/\"},\"author\":{\"name\":\"Maciej Zakrzewicz\",\"@id\":\"https:\/\/explainit.pl\/wordpress\/#\/schema\/person\/740773131bc169a2dc9c0e5e07476219\"},\"headline\":\"\u015aledzenie pracy optymalizatora zapyta\u0144 Oracle Database\",\"datePublished\":\"2012-12-10T18:58:15+00:00\",\"dateModified\":\"2016-08-09T19:05:27+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/explainit.pl\/wordpress\/2012\/12\/10\/sledzenie-pracy-optymalizatora-zapytan-oracle-database\/\"},\"wordCount\":200,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/explainit.pl\/wordpress\/#organization\"},\"keywords\":[\"optymalizator\"],\"articleSection\":[\"Oracle Database\"],\"inLanguage\":\"pl-PL\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/explainit.pl\/wordpress\/2012\/12\/10\/sledzenie-pracy-optymalizatora-zapytan-oracle-database\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/explainit.pl\/wordpress\/2012\/12\/10\/sledzenie-pracy-optymalizatora-zapytan-oracle-database\/\",\"url\":\"https:\/\/explainit.pl\/wordpress\/2012\/12\/10\/sledzenie-pracy-optymalizatora-zapytan-oracle-database\/\",\"name\":\"\u015aledzenie pracy optymalizatora zapyta\u0144 Oracle Database - Explain IT\",\"isPartOf\":{\"@id\":\"https:\/\/explainit.pl\/wordpress\/#website\"},\"datePublished\":\"2012-12-10T18:58:15+00:00\",\"dateModified\":\"2016-08-09T19:05:27+00:00\",\"description\":\"\u015aledzenie pracy optymalizatora zapyta\u0144 Oracle Database\",\"breadcrumb\":{\"@id\":\"https:\/\/explainit.pl\/wordpress\/2012\/12\/10\/sledzenie-pracy-optymalizatora-zapytan-oracle-database\/#breadcrumb\"},\"inLanguage\":\"pl-PL\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/explainit.pl\/wordpress\/2012\/12\/10\/sledzenie-pracy-optymalizatora-zapytan-oracle-database\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/explainit.pl\/wordpress\/2012\/12\/10\/sledzenie-pracy-optymalizatora-zapytan-oracle-database\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Strona g\u0142\u00f3wna\",\"item\":\"https:\/\/explainit.pl\/wordpress\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"\u015aledzenie pracy optymalizatora zapyta\u0144 Oracle Database\"}]},{\"@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":"\u015aledzenie pracy optymalizatora zapyta\u0144 Oracle Database - Explain IT","description":"\u015aledzenie pracy optymalizatora zapyta\u0144 Oracle Database","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\/2012\/12\/10\/sledzenie-pracy-optymalizatora-zapytan-oracle-database\/","og_locale":"pl_PL","og_type":"article","og_title":"\u015aledzenie pracy optymalizatora zapyta\u0144 Oracle Database - Explain IT","og_description":"\u015aledzenie pracy optymalizatora zapyta\u0144 Oracle Database","og_url":"https:\/\/explainit.pl\/wordpress\/2012\/12\/10\/sledzenie-pracy-optymalizatora-zapytan-oracle-database\/","og_site_name":"Explain IT","article_published_time":"2012-12-10T18:58:15+00:00","article_modified_time":"2016-08-09T19:05:27+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\/2012\/12\/10\/sledzenie-pracy-optymalizatora-zapytan-oracle-database\/#article","isPartOf":{"@id":"https:\/\/explainit.pl\/wordpress\/2012\/12\/10\/sledzenie-pracy-optymalizatora-zapytan-oracle-database\/"},"author":{"name":"Maciej Zakrzewicz","@id":"https:\/\/explainit.pl\/wordpress\/#\/schema\/person\/740773131bc169a2dc9c0e5e07476219"},"headline":"\u015aledzenie pracy optymalizatora zapyta\u0144 Oracle Database","datePublished":"2012-12-10T18:58:15+00:00","dateModified":"2016-08-09T19:05:27+00:00","mainEntityOfPage":{"@id":"https:\/\/explainit.pl\/wordpress\/2012\/12\/10\/sledzenie-pracy-optymalizatora-zapytan-oracle-database\/"},"wordCount":200,"commentCount":0,"publisher":{"@id":"https:\/\/explainit.pl\/wordpress\/#organization"},"keywords":["optymalizator"],"articleSection":["Oracle Database"],"inLanguage":"pl-PL","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/explainit.pl\/wordpress\/2012\/12\/10\/sledzenie-pracy-optymalizatora-zapytan-oracle-database\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/explainit.pl\/wordpress\/2012\/12\/10\/sledzenie-pracy-optymalizatora-zapytan-oracle-database\/","url":"https:\/\/explainit.pl\/wordpress\/2012\/12\/10\/sledzenie-pracy-optymalizatora-zapytan-oracle-database\/","name":"\u015aledzenie pracy optymalizatora zapyta\u0144 Oracle Database - Explain IT","isPartOf":{"@id":"https:\/\/explainit.pl\/wordpress\/#website"},"datePublished":"2012-12-10T18:58:15+00:00","dateModified":"2016-08-09T19:05:27+00:00","description":"\u015aledzenie pracy optymalizatora zapyta\u0144 Oracle Database","breadcrumb":{"@id":"https:\/\/explainit.pl\/wordpress\/2012\/12\/10\/sledzenie-pracy-optymalizatora-zapytan-oracle-database\/#breadcrumb"},"inLanguage":"pl-PL","potentialAction":[{"@type":"ReadAction","target":["https:\/\/explainit.pl\/wordpress\/2012\/12\/10\/sledzenie-pracy-optymalizatora-zapytan-oracle-database\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/explainit.pl\/wordpress\/2012\/12\/10\/sledzenie-pracy-optymalizatora-zapytan-oracle-database\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Strona g\u0142\u00f3wna","item":"https:\/\/explainit.pl\/wordpress\/"},{"@type":"ListItem","position":2,"name":"\u015aledzenie pracy optymalizatora zapyta\u0144 Oracle Database"}]},{"@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\/23"}],"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=23"}],"version-history":[{"count":2,"href":"https:\/\/explainit.pl\/wordpress\/wp-json\/wp\/v2\/posts\/23\/revisions"}],"predecessor-version":[{"id":349,"href":"https:\/\/explainit.pl\/wordpress\/wp-json\/wp\/v2\/posts\/23\/revisions\/349"}],"wp:attachment":[{"href":"https:\/\/explainit.pl\/wordpress\/wp-json\/wp\/v2\/media?parent=23"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/explainit.pl\/wordpress\/wp-json\/wp\/v2\/categories?post=23"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/explainit.pl\/wordpress\/wp-json\/wp\/v2\/tags?post=23"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}