{"id":143,"date":"2013-08-11T11:22:44","date_gmt":"2013-08-11T09:22:44","guid":{"rendered":"http:\/\/explainit.pl\/blog\/?p=143"},"modified":"2019-01-11T08:53:57","modified_gmt":"2019-01-11T07:53:57","slug":"profile-czy-hinty","status":"publish","type":"post","link":"https:\/\/explainit.pl\/wordpress\/2013\/08\/11\/profile-czy-hinty\/","title":{"rendered":"Profile czy hinty?"},"content":{"rendered":"<p>Zar\u00f3wno SQL Profiles, jak i Hints to mechanizmy pozwalaj\u0105ce sterowa\u0107 zachowaniem optymalizatora &nbsp;zapyta\u0144 tak, aby wydajno\u015b\u0107 generowanych plan\u00f3w by\u0142a jak najlepsza. By\u0142yby one niepotrzebne, gdyby optymalizator potrafi\u0142 zawsze trafnie wybra\u0107 optymalny plan wykonania. Poniewa\u017c jednak nierzadko zdarza si\u0119 optymalizatorowi \u201echybi\u0107\u201d, to takie lekarstwa s\u0105 niezb\u0119dne.<\/p>\n<p>Hinty to zapisane w tre\u015bci polecenia SQL wskaz\u00f3wki (zalecenia), instruuj\u0105ce optymalizator zapyta\u0144 na temat tego, jakie kroki powinny, lub nie powinny znale\u017a\u0107 si\u0119 w planie wykonania. Umo\u017cliwiaj\u0105 one zaw\u0119\u017cenie przestrzeni poszukiwa\u0144 plan\u00f3w do wybranego przez nas podzbioru mo\u017cliwych rozwi\u0105za\u0144.<\/p>\n<p>Natomiast SQL Profiles to \u201eerraty\u201d s\u0142u\u017c\u0105ce do korekcji nietrafnych szacunk\u00f3w optymalizatora zapyta\u0144 dokonywanych podczas generowania planu wykonania (kardynalno\u015b\u0107, selektywno\u015b\u0107). SQL Profiles s\u0105 zwykle efektem ubocznym dzia\u0142ania SQL Tuning Advisora, kt\u00f3ry eksperymentalnie odkrywa niezgodno\u015b\u0107 szacowanych selektywno\u015bci z rzeczywistym przebiegiem wykonania zapytania. Istnieje te\u017c mo\u017cliwo\u015b\u0107 ca\u0142kowicie automatycznego generowania profili dla trudnych zapyta\u0144&nbsp; SQL w ramach nocnego zadania Automatic SQL Tuning (ACCEPT_SQL_PROFILES=TRUE).<\/p>\n<p>Wa\u017cna r\u00f3\u017cnica pomi\u0119dzy hintami a profilami dotyczy zatem sposobu zapisu informacji koryguj\u0105cych dzia\u0142anie optymalizatora: hinty wymuszaj\u0105 konkretne kroki w planie wykonania, natomiast profile dostarczaj\u0105 optymalizatorowi skorygowanych warto\u015bci selektywno\u015bci, dzi\u0119ki czemu optymalizator samodzielnie znajduje dobry plan.<\/p>\n<p>G\u0142\u00f3wne korzy\u015bci wynikaj\u0105ce ze stosowania profili to zatem:<\/p>\n<ul>\n<li>w przeciwie\u0144stwie do hint\u00f3w, profile SQL <strong>nie zmuszaj\u0105<\/strong> optymalizatora do stosowania konkretnego planu wykonania zapytania<\/li>\n<\/ul>\n<ul>\n<li>w przeciwie\u0144stwie do hint\u00f3w dla optymalizatora, profile SQL <strong>nie wymagaj\u0105 modyfikacji kodu<\/strong> aplikacji<\/li>\n<\/ul>\n<p>W wi\u0119kszo\u015bci zastosowa\u0144 powy\u017csze cechy sprawiaj\u0105, \u017ce profile s\u0105 rozwi\u0105zaniem skuteczniejszym i \u0142atwiej wdra\u017calnym.<\/p>\n<p>Zademonstrujmy przyk\u0142ady rozwi\u0105za\u0144 prostego problemu wydajno\u015bciowego zar\u00f3wno za pomoc\u0105 profili, jak i hint\u00f3w. Mamy tabel\u0119 ARMIA(ID, PLEC) z podstawowymi statystykami, zawieraj\u0105c\u0105 3200 rekord\u00f3w, w tym 1% kobiet, 99% m\u0119\u017cczyzn. Na kolumnie PLEC istnieje index B*-drzewo. Wykonujemy wysoce selektywne zapytanie wyszukuj\u0105ce wy\u0142\u0105cznie kobiet. Ze wzgl\u0119du na brak histogramu dla kolumny PLEC, optymalizator wyznacza selektywno\u015b\u0107 predykatu PLEC=\u2019K\u2019 na poziomie 50%, co skutkuje wyborem nieoptymalnego planu opartego na pe\u0142nym odczycie tabeli (FULL TABLE SCAN) zamiast u\u017cycia indeksu.<\/p>\n<p><code>SQL&gt; select sum(id) from armia where plec='K';<br \/>\n...<br \/>\nPlan wykonywania<br \/>\n----------------------------------------------------------------------------<br \/>\n| Id&nbsp; | Operation&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Name&nbsp; | Rows&nbsp; | Bytes | Cost (%CPU)| Time&nbsp;&nbsp;&nbsp;&nbsp; |<br \/>\n----------------------------------------------------------------------------<br \/>\n|&nbsp;&nbsp; 0 | SELECT STATEMENT&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp; 1 |&nbsp;&nbsp;&nbsp;&nbsp; 3 |&nbsp;&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp; (0)| 00:00:01 |<br \/>\n|&nbsp;&nbsp; 1 |&nbsp; SORT AGGREGATE&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp; 1 |&nbsp;&nbsp;&nbsp;&nbsp; 3 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<br \/>\n|*&nbsp; 2 |&nbsp;&nbsp; TABLE ACCESS FULL| ARMIA |&nbsp; 1600 |&nbsp; 4800 |&nbsp;&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp; (0)| 00:00:01 |<br \/>\n----------------------------------------------------------------------------<br \/>\n<\/code><\/p>\n<p>Aby zbudowa\u0107 profil SQL, kt\u00f3ry skoryguje b\u0142\u0119dne szacunki selektywno\u015bci, przygotujemy i wykonamy zadanie SQL Tuning Advisora:<\/p>\n<p><code>SQL&gt; var task_name varchar2(30);<br \/>\nSQL&gt; EXEC :task_name := dbms_sqltune.create_tuning_task(<br \/>\nsql_text =&gt; 'select sum(id) from armia where plec=''K''');<br \/>\n...<br \/>\nSQL&gt; EXEC dbms_sqltune.execute_tuning_task(:task_name);<br \/>\n...<br \/>\n<\/code><\/p>\n<p>Nast\u0119pnie odczytamy raport wygenerowany przez SQL Tuning Advisora. Zauwa\u017cmy, \u017ce zaproponowany zosta\u0142 profil SQL, kt\u00f3ry pozwoli poprawi\u0107 rzeczywisty czas wykonania zapytania o ponad 76%, a ponadto odci\u0105\u017cy procesor i zredukuje liczb\u0119 odczytywanych blok\u00f3w. Widzimy te\u017c jaki b\u0119dzie skutek zastosowania tego profilu z punktu widzenia planu wykonania zapytania \u2013 zamiast pe\u0142nego odczytu tabeli pojawi\u0142 si\u0119 zakresowy odczyt indeksu.<br \/>\n<code><br \/>\nSQL&gt; SELECT dbms_sqltune.report_tuning_task(:task_name) FROM dual;<br \/>\n...<br \/>\nSQL Text&nbsp;&nbsp; : select sum(id) from armia where plec='K'<br \/>\n&nbsp;<br \/>\n-------------------------------------------------------------------------------<br \/>\nFINDINGS SECTION (1 finding)<br \/>\n-------------------------------------------------------------------------------<br \/>\n&nbsp;<br \/>\n1- SQL Profile Finding (see explain plans section below)<br \/>\n--------------------------------------------------------<br \/>\nDla tej instrukcji znaleziono potencjalnie lepszy plan wykonywania.<br \/>\n&nbsp;<br \/>\nRecommendation (estimated benefit: 14.17%)<br \/>\n------------------------------------------<br \/>\n- Prosz\u0119 rozwa\u017cy\u0107 zaakceptowanie zalecanego profilu SQL.<br \/>\nexecute dbms_sqltune.accept_sql_profile(task_name =&gt; 'ZADANIE_1649',<br \/>\ntask_owner =&gt; 'SYS', replace =&gt; TRUE);<br \/>\n&nbsp;<br \/>\nValidation results<br \/>\n------------------<br \/>\nSQL profile zosta\u0142 przetestowany przez wykonanie zar\u00f3wno jego planu, jak i<br \/>\nplanu oryginalnego oraz przez pomiar ich statystyk wykonywania. Plan m\u00f3g\u0142<br \/>\nby\u0107 wykonany tylko cz\u0119\u015bciowo, je\u015bli uko\u0144czenie drugiego planu nast\u0105pi\u0142o<br \/>\nszybciej.<br \/>\n&nbsp;<br \/>\nOriginal Plan&nbsp; With SQL Profile&nbsp; % Improved<br \/>\n-------------&nbsp; ----------------&nbsp; ----------<br \/>\nCompletion Status:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; COMPLETE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; COMPLETE<br \/>\nElapsed Time(us):&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 594&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 138&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 76.76 %<br \/>\nCPU Time(us):&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1560&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 100 %<br \/>\nUser I\/O Time(us):&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;0<br \/>\nBuffer Gets:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 12.5 %<br \/>\nPhysical Read Requests:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0<br \/>\nPhysical Write Requests:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0<br \/>\nPhysical Read Bytes:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0<br \/>\nPhysical Write Bytes:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0<br \/>\nRows Processed:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1<br \/>\nFetches:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1<br \/>\nExecutions:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1<br \/>\n&nbsp;<br \/>\nNotes<br \/>\n-----<br \/>\n1. Najpierw wykonano original plan w celu uaktywnienia buforowej pami\u0119ci<br \/>\npodr\u0119cznej.<br \/>\n2. Statystyki dla original plan zosta\u0142y u\u015brednione na podstawie 9 nast\u0119pnych<br \/>\nuruchomie\u0144.<br \/>\n3. Najpierw wykonano SQL profile plan w celu uaktywnienia buforowej pami\u0119ci<br \/>\npodr\u0119cznej.<br \/>\n4. Statystyki dla the SQL profile plan zosta\u0142y u\u015brednione na podstawie 9<br \/>\nnast\u0119pnych uruchomie\u0144.<br \/>\n&nbsp;<br \/>\n-------------------------------------------------------------------------------<br \/>\nEXPLAIN PLANS SECTION<br \/>\n-------------------------------------------------------------------------------<br \/>\n&nbsp;<br \/>\n1- Original With Adjusted Cost<br \/>\n----------------------------------------------------------------------------<br \/>\n| Id&nbsp; | Operation&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Name&nbsp; | Rows&nbsp; | Bytes | Cost (%CPU)| Time&nbsp;&nbsp;&nbsp;&nbsp; |<br \/>\n----------------------------------------------------------------------------<br \/>\n|&nbsp;&nbsp; 0 | SELECT STATEMENT&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp; 1 |&nbsp;&nbsp;&nbsp;&nbsp; 3 |&nbsp;&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp; (0)| 00:00:01 |<br \/>\n|&nbsp;&nbsp; 1 |&nbsp; SORT AGGREGATE&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp; 1 |&nbsp;&nbsp;&nbsp;&nbsp; 3 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<br \/>\n|*&nbsp; 2 |&nbsp;&nbsp; TABLE ACCESS FULL| ARMIA |&nbsp;&nbsp;&nbsp; 32 |&nbsp;&nbsp;&nbsp; 96 |&nbsp;&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp; (0)| 00:00:01 |<br \/>\n----------------------------------------------------------------------------<br \/>\n...<br \/>\n&nbsp;<br \/>\n2- Using SQL Profile<br \/>\n------------------------------------------------------------------------------------------<br \/>\n| Id&nbsp; | Operation&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Rows&nbsp; | Bytes | Cost (%CPU)|Time&nbsp;&nbsp;&nbsp;&nbsp; |<br \/>\n------------------------------------------------------------------------------------------<br \/>\n|&nbsp;&nbsp; 0 | SELECT STATEMENT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp; 1 |&nbsp;&nbsp;&nbsp;&nbsp; 3 |&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp; (0)|00:00:01 |<br \/>\n|&nbsp;&nbsp; 1 |&nbsp; SORT AGGREGATE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp; 1 |&nbsp;&nbsp;&nbsp;&nbsp; 3 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |         |<br \/>\n|&nbsp;&nbsp; 2 |&nbsp;&nbsp; TABLE ACCESS BY INDEX ROWID| ARMIA&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp; 32 |&nbsp;&nbsp;&nbsp; 96 |&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp; (0)|00:00:01 |<br \/>\n|*&nbsp; 3 |&nbsp;&nbsp;&nbsp; INDEX RANGE SCAN&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | ARMIA_IDX |&nbsp;&nbsp;&nbsp; 32 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp; (0)|00:00:01 |<br \/>\n------------------------------------------------------------------------------------------<\/code><\/p>\n<p>Zaakceptujmy propozycj\u0119 utworzenia profilu SQL i zaobserwujmy jak ten profil wp\u0142ynie na przysz\u0142y plan wykonania naszego zapytania.<\/p>\n<p><code>SQL&gt; EXEC dbms_sqltune.accept_sql_profile(:task_name);<br \/>\n...<br \/>\nSQL&gt; select sum(id) from armia where plec='K';<br \/>\n...<br \/>\nPlan wykonywania<br \/>\n------------------------------------------------------------------------------------------<br \/>\n| Id&nbsp; | Operation&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Rows&nbsp; | Bytes | Cost (%CPU)|Time&nbsp;&nbsp;&nbsp;&nbsp; |<br \/>\n------------------------------------------------------------------------------------------<br \/>\n|&nbsp;&nbsp; 0 | SELECT STATEMENT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp; 1 |&nbsp;&nbsp;&nbsp;&nbsp; 3 |&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp; (0)|00:00:01 |<br \/>\n|&nbsp;&nbsp; 1 |&nbsp; SORT AGGREGATE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp; 1 |&nbsp;&nbsp;&nbsp;&nbsp; 3 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |         |<br \/>\n|&nbsp;&nbsp; 2 |&nbsp;&nbsp; TABLE ACCESS BY INDEX ROWID| ARMIA&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp; 32 |&nbsp;&nbsp;&nbsp; 96 |&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp; (0)|00:00:01 |<br \/>\n|*&nbsp; 3 |&nbsp;&nbsp;&nbsp; INDEX RANGE SCAN&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | ARMIA_IDX |&nbsp;&nbsp;&nbsp; 32 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp; (0)|00:00:01 |<br \/>\n------------------------------------------------------------------------------------------<br \/>\n...<br \/>\nNote<br \/>\n-----<br \/>\n- SQL profile \"SYS_SQLPROF_01430a0f52b30000\" used for this statement<br \/>\n<\/code><\/p>\n<p>Uda\u0142o nam si\u0119 pomy\u015blnie skorygowa\u0107 nieoptymalny plan wykonania zapytania dzi\u0119ki utworzeniu profilu SQL.<\/p>\n<p>A jak mogliby\u015bmy uzyska\u0107 taki sam efekt za pomoc\u0105 hint\u00f3w? Poni\u017cej tre\u015b\u0107 poprawionego zapytania. Oczywi\u015bcie podstawow\u0105 wad\u0105 takiego rozwi\u0105zania jest konieczno\u015b\u0107 ingerowania w kod \u017ar\u00f3d\u0142owy aplikacji w celu dopisania wskaz\u00f3wek dla optymalizatora.<br \/>\n<code><br \/>\nSQL&gt; select \/*+ INDEX(ARMIA ARMIA_IDX)*\/ sum(id) from armia where plec='K';<br \/>\n...<br \/>\nPlan wykonywania<br \/>\n--------------------------------------------------------------------------------<br \/>\n----------<br \/>\n| Id&nbsp; | Operation&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Rows&nbsp; | Bytes | Cost (%CPU)|<br \/>\nTime&nbsp;&nbsp;&nbsp;&nbsp; |<br \/>\n--------------------------------------------------------------------------------<br \/>\n----------<br \/>\n|&nbsp;&nbsp; 0 | SELECT STATEMENT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp; 1 |&nbsp;&nbsp;&nbsp;&nbsp; 3 |&nbsp;&nbsp;&nbsp; 10&nbsp;&nbsp; (0)|<br \/>\n00:00:01 |<br \/>\n|&nbsp;&nbsp; 1 |&nbsp; SORT AGGREGATE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp; 1 |&nbsp;&nbsp;&nbsp;&nbsp; 3 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<br \/>\n|<br \/>\n|&nbsp;&nbsp; 2 |&nbsp;&nbsp; TABLE ACCESS BY INDEX ROWID| ARMIA&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; 1600 |&nbsp; 4800 |&nbsp;&nbsp;&nbsp; 10&nbsp;&nbsp; (0)|<br \/>\n00:00:01 |<br \/>\n|*&nbsp; 3 |&nbsp;&nbsp;&nbsp; INDEX RANGE SCAN&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | ARMIA_IDX |&nbsp; 1600 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp; (0)|<br \/>\n00:00:01 |<br \/>\n--------------------------------------------------------------------------------<br \/>\n----------<br \/>\n...<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Zar\u00f3wno SQL Profiles, jak i Hints to mechanizmy pozwalaj\u0105ce sterowa\u0107 zachowaniem optymalizatora &nbsp;zapyta\u0144 tak, aby wydajno\u015b\u0107 generowanych plan\u00f3w by\u0142a jak najlepsza. By\u0142yby one niepotrzebne, gdyby optymalizator potrafi\u0142 zawsze trafnie wybra\u0107 optymalny plan wykonania. Poniewa\u017c jednak nierzadko zdarza si\u0119 optymalizatorowi \u201echybi\u0107\u201d, to takie lekarstwa s\u0105 niezb\u0119dne. Hinty to zapisane w tre\u015bci polecenia SQL wskaz\u00f3wki (zalecenia), instruuj\u0105ce <a href=\"https:\/\/explainit.pl\/wordpress\/2013\/08\/11\/profile-czy-hinty\/\" rel=\"nofollow\"><span class=\"sr-only\">Read more about Profile czy hinty?<\/span>[&hellip;]<\/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":[9,12,19],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v20.2.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Profile czy hinty? - Explain IT<\/title>\n<meta name=\"description\" content=\"Profile czy hinty?\" \/>\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\/2013\/08\/11\/profile-czy-hinty\/\" \/>\n<meta property=\"og:locale\" content=\"pl_PL\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Profile czy hinty? - Explain IT\" \/>\n<meta property=\"og:description\" content=\"Profile czy hinty?\" \/>\n<meta property=\"og:url\" content=\"https:\/\/explainit.pl\/wordpress\/2013\/08\/11\/profile-czy-hinty\/\" \/>\n<meta property=\"og:site_name\" content=\"Explain IT\" \/>\n<meta property=\"article:published_time\" content=\"2013-08-11T09:22:44+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-01-11T07:53:57+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=\"10 minut\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/explainit.pl\/wordpress\/2013\/08\/11\/profile-czy-hinty\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/explainit.pl\/wordpress\/2013\/08\/11\/profile-czy-hinty\/\"},\"author\":{\"name\":\"Maciej Zakrzewicz\",\"@id\":\"https:\/\/explainit.pl\/wordpress\/#\/schema\/person\/740773131bc169a2dc9c0e5e07476219\"},\"headline\":\"Profile czy hinty?\",\"datePublished\":\"2013-08-11T09:22:44+00:00\",\"dateModified\":\"2019-01-11T07:53:57+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/explainit.pl\/wordpress\/2013\/08\/11\/profile-czy-hinty\/\"},\"wordCount\":520,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/explainit.pl\/wordpress\/#organization\"},\"keywords\":[\"hinty\",\"optymalizator\",\"SQL Profile\"],\"articleSection\":[\"Oracle Database\"],\"inLanguage\":\"pl-PL\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/explainit.pl\/wordpress\/2013\/08\/11\/profile-czy-hinty\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/explainit.pl\/wordpress\/2013\/08\/11\/profile-czy-hinty\/\",\"url\":\"https:\/\/explainit.pl\/wordpress\/2013\/08\/11\/profile-czy-hinty\/\",\"name\":\"Profile czy hinty? - Explain IT\",\"isPartOf\":{\"@id\":\"https:\/\/explainit.pl\/wordpress\/#website\"},\"datePublished\":\"2013-08-11T09:22:44+00:00\",\"dateModified\":\"2019-01-11T07:53:57+00:00\",\"description\":\"Profile czy hinty?\",\"breadcrumb\":{\"@id\":\"https:\/\/explainit.pl\/wordpress\/2013\/08\/11\/profile-czy-hinty\/#breadcrumb\"},\"inLanguage\":\"pl-PL\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/explainit.pl\/wordpress\/2013\/08\/11\/profile-czy-hinty\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/explainit.pl\/wordpress\/2013\/08\/11\/profile-czy-hinty\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Strona g\u0142\u00f3wna\",\"item\":\"https:\/\/explainit.pl\/wordpress\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Profile czy hinty?\"}]},{\"@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":"Profile czy hinty? - Explain IT","description":"Profile czy hinty?","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\/2013\/08\/11\/profile-czy-hinty\/","og_locale":"pl_PL","og_type":"article","og_title":"Profile czy hinty? - Explain IT","og_description":"Profile czy hinty?","og_url":"https:\/\/explainit.pl\/wordpress\/2013\/08\/11\/profile-czy-hinty\/","og_site_name":"Explain IT","article_published_time":"2013-08-11T09:22:44+00:00","article_modified_time":"2019-01-11T07:53:57+00:00","author":"Maciej Zakrzewicz","twitter_card":"summary_large_image","twitter_misc":{"Napisane przez":"Maciej Zakrzewicz","Szacowany czas czytania":"10 minut"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/explainit.pl\/wordpress\/2013\/08\/11\/profile-czy-hinty\/#article","isPartOf":{"@id":"https:\/\/explainit.pl\/wordpress\/2013\/08\/11\/profile-czy-hinty\/"},"author":{"name":"Maciej Zakrzewicz","@id":"https:\/\/explainit.pl\/wordpress\/#\/schema\/person\/740773131bc169a2dc9c0e5e07476219"},"headline":"Profile czy hinty?","datePublished":"2013-08-11T09:22:44+00:00","dateModified":"2019-01-11T07:53:57+00:00","mainEntityOfPage":{"@id":"https:\/\/explainit.pl\/wordpress\/2013\/08\/11\/profile-czy-hinty\/"},"wordCount":520,"commentCount":0,"publisher":{"@id":"https:\/\/explainit.pl\/wordpress\/#organization"},"keywords":["hinty","optymalizator","SQL Profile"],"articleSection":["Oracle Database"],"inLanguage":"pl-PL","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/explainit.pl\/wordpress\/2013\/08\/11\/profile-czy-hinty\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/explainit.pl\/wordpress\/2013\/08\/11\/profile-czy-hinty\/","url":"https:\/\/explainit.pl\/wordpress\/2013\/08\/11\/profile-czy-hinty\/","name":"Profile czy hinty? - Explain IT","isPartOf":{"@id":"https:\/\/explainit.pl\/wordpress\/#website"},"datePublished":"2013-08-11T09:22:44+00:00","dateModified":"2019-01-11T07:53:57+00:00","description":"Profile czy hinty?","breadcrumb":{"@id":"https:\/\/explainit.pl\/wordpress\/2013\/08\/11\/profile-czy-hinty\/#breadcrumb"},"inLanguage":"pl-PL","potentialAction":[{"@type":"ReadAction","target":["https:\/\/explainit.pl\/wordpress\/2013\/08\/11\/profile-czy-hinty\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/explainit.pl\/wordpress\/2013\/08\/11\/profile-czy-hinty\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Strona g\u0142\u00f3wna","item":"https:\/\/explainit.pl\/wordpress\/"},{"@type":"ListItem","position":2,"name":"Profile czy hinty?"}]},{"@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\/143"}],"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=143"}],"version-history":[{"count":3,"href":"https:\/\/explainit.pl\/wordpress\/wp-json\/wp\/v2\/posts\/143\/revisions"}],"predecessor-version":[{"id":684,"href":"https:\/\/explainit.pl\/wordpress\/wp-json\/wp\/v2\/posts\/143\/revisions\/684"}],"wp:attachment":[{"href":"https:\/\/explainit.pl\/wordpress\/wp-json\/wp\/v2\/media?parent=143"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/explainit.pl\/wordpress\/wp-json\/wp\/v2\/categories?post=143"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/explainit.pl\/wordpress\/wp-json\/wp\/v2\/tags?post=143"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}