{"id":1838,"date":"2018-04-03T21:30:42","date_gmt":"2018-04-03T19:30:42","guid":{"rendered":"http:\/\/van-maanen.com\/?p=1838"},"modified":"2018-04-03T21:30:42","modified_gmt":"2018-04-03T19:30:42","slug":"the-oracle-tuning-advisor","status":"publish","type":"post","link":"http:\/\/archief.van-maanen.com\/?p=1838","title":{"rendered":"the Oracle Tuning Advisor"},"content":{"rendered":"<p>Oracle has now included an Oracle tuning advisor. Its purpose is to advise on a SQL statement. For me, it provides an answer on whether to add an index or not. I was taught that one should always start with tables that have no indices. Only when it is demonstrated that indices are used, one should create an index. The idea is that maintenance of indices cost time, which must be offset against the gains from an index. If no gains are expected, it is useless to create such index. Of course, we could use the explain plan for that. This reveals whether an index is used or not.<br \/>\nFirst, such a SQL statement should be properly included in a structure where it can be analysed. Such a structure can be created with (with SQL Plus:):<\/p>\n<pre>\nDECLARE\n  my_task_name VARCHAR2(30);\n  my_sqltext   CLOB;\nBEGIN\n  my_sqltext := 'select * from C##HR.GROOT where n=500000';\n\n  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK (\n          sql_text    => my_sqltext\n,         user_name   => 'C##HR'\n,         scope       => 'COMPREHENSIVE'\n,         time_limit  => 60\n,         task_name   => 'STA_SPECIFIC_EMP_TASK'\n,         description => 'Task to tune a query on a specified employee');\nEND;\n\/\n<\/pre>\n<p>The existence of this structure can be shown if we look into the enterprise manager under the heading Performance > SQL Tuning Advisor > Manual.<br \/>\nThere, we see the entry that we just created.<br \/>\n<img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/van-maanen.com\/wp-content\/uploads\/2018\/04\/Naamloos-2-2.png\" alt=\"\" width=\"1027\" height=\"319\" class=\"alignnone size-full wp-image-1843\" \/><br \/>\nSubsequently, we may execute this entry with:<\/p>\n<pre>\nBEGIN\n  DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'STA_SPECIFIC_EMP_TASK');\nEND;\n\/\n<\/pre>\n<p>Clicking on it shows a suggestion:<br \/>\n<img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/van-maanen.com\/wp-content\/uploads\/2018\/04\/Naamloos-2-1.png\" alt=\"\" width=\"806\" height=\"294\" class=\"alignnone size-full wp-image-1845\" \/><br \/>\nIt states that an index could be added to improve performance.<br \/>\nOne could also receive such information with:<\/p>\n<pre>\nSET LONG 10000;\nSET PAGESIZE 1000\nSET LINESIZE 200\nSELECT DBMS_SQLTUNE.report_tuning_task('STA_SPECIFIC_EMP_TASK') AS recommendations FROM dual;\nSET PAGESIZE 24\n<\/pre>\n<p>If that is done (an index on the filter), and the entry is executed again, the suggestion is disappeared.<br \/>\nWe may finally remove the entry with:<\/p>\n<pre>\nexec dbms_sqltune.drop_tuning_task('STA_SPECIFIC_EMP_TASK');\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Oracle has now included an Oracle tuning advisor. Its purpose is to advise on a SQL statement. For me, it provides an answer on whether to add an index or not. I was taught that one should always start with tables that have no indices. Only when it is demonstrated that indices are used, one [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[],"class_list":["post-1838","post","type-post","status-publish","format-standard","hentry","category-nice-to-know"],"_links":{"self":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/1838","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1838"}],"version-history":[{"count":0,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/1838\/revisions"}],"wp:attachment":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1838"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1838"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1838"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}