{"id":431,"date":"2021-01-21T10:00:00","date_gmt":"2021-01-21T09:00:00","guid":{"rendered":"https:\/\/anjo.pt\/keyword-oracle\/?p=431"},"modified":"2021-01-30T13:59:43","modified_gmt":"2021-01-30T12:59:43","slug":"explore-multiple-optimizer-features-and-fixes-with-sqlt-xplore","status":"publish","type":"post","link":"https:\/\/anjo.pt\/keyword-oracle\/2021\/01\/21\/explore-multiple-optimizer-features-and-fixes-with-sqlt-xplore\/","title":{"rendered":"Explore multiple optimizer features and fixes with SQLT Xplore"},"content":{"rendered":"\n<p><a href=\"https:\/\/anjo.pt\/keyword-oracle\/2021\/01\/20\/long-parsing-on-oracle-12-2-and-the-discovery-of-sqlt-xplore\/\" data-type=\"post\" data-id=\"427\">Yesterday I described<\/a> how I come to SQLT Xplore and it helped me to find out, on Oracle 12.2.0.1, that optimizer_features_enable=8.1.3 decreased the parsing time of a query from 5 seconds to 0.2 seconds. Today I show how to use SQLT Xplore.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">What is SQLT Xplore?<\/h3>\n\n\n\n<p>SQL Xplore automatises the test of almost 2&#8217;000 optimizer parameters and bug fixes control against one query, allowing to discover which parameter was eventually the reason of a performance regression.<\/p>\n\n\n\n<!--more-->\n\n\n\n<p>SQLT Xplore should only be used when ALL these conditions are met (a bit shorter version than the one mentioned by Oracle):<\/p>\n\n\n\n<ol class=\"wp-block-list\"><li>SQL performs poorly or returns wrong results while using a &#8220;bad&#8221; plan.<\/li><li>You can reproduced the problem on a test system.<\/li><li>A &#8220;better&#8221; plan can be reproduced on the test system by changing the value of optimizer_features_enabled parameter.<\/li><li>You want to narrow reason to specific parameter or bug fix control.<\/li><li>You have full access to the test system, including&nbsp;<code>SYS<\/code>&nbsp;access.<\/li><\/ol>\n\n\n\n<p>Do not use SQLT Xplore when ANY of these conditions is true:<\/p>\n\n\n\n<ol class=\"wp-block-list\"><li>The SQL statement may cause corruption or update data.<\/li><li>The execution of the SQL may take longer than a few seconds.<\/li><\/ol>\n\n\n\n<h3 class=\"wp-block-heading\">Download, docs and unzip:<\/h3>\n\n\n\n<ul class=\"wp-block-list\"><li><a href=\"https:\/\/support.oracle.com\/epmos\/main\/downloadattachmentprocessor?attachid=215187.1:SQLT_LATEST\">Download SQLT (including SQLT Xplore<\/a>) &#8211; current version June 2020<\/li><li><a rel=\"noreferrer noopener\" href=\"https:\/\/support.oracle.com\/epmos\/faces\/DocContentDisplay?id=215187.1\" target=\"_blank\">All About the SQLT Diagnostic Tool (Doc ID 215187.1)<\/a><\/li><li><a rel=\"noreferrer noopener\" href=\"https:\/\/support.oracle.com\/epmos\/faces\/DocContentDisplay?id=1614107.1\" target=\"_blank\">SQLT Usage Instructions (Doc ID 1614107.1)<\/a><\/li><\/ul>\n\n\n\n<p>SQLT Xplore is completely independent from other SQLT tools. It is not necessary to install other SQLT tools.<\/p>\n\n\n\n<p>After unzipping SQLT, the SQLT Xplore is located at:<\/p>\n\n\n\n<p><code>&lt;unzip location&gt;\/sqlt\/utl\/xplore<\/code><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Create file with SQL to analyze<\/h3>\n\n\n\n<p>Important in the SQL to analyze, is that this SQL includes as comment the line with <code>^^unique_id<\/code> and finishes with a semicolon. Remember to use full qualified names, to access the data on the correct schema. I created a file called <code>anjo.sql<\/code> with the content:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nselect\n  \/* ^^unique_id *\/\n       &amp;lt;rest of SQL&gt; ;\n<\/pre><\/div>\n\n\n<p>Attention that the full output of the query will appear and will be spooled hundred of times. If the the output has thousands of lines, maybe consider to change the query to have a smaller output.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Install and configure SQLT Xplore<\/h3>\n\n\n\n<p>In case you know the password of the user running the query, you can use that user. Otherwise, use a your personal user. This user will receive DBA and SELECT ANY DICTIONARY privileges and some tables and procedures will be created. In my case, I installed into my user ANJO. The actual data is in another schema:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\ncd sqlt\/utl\/xplore\nsqlplus \/ as sysdba\nSQL&gt; @install\nTest Case User: anjo\nPassword: ******\n...\nPackage created.\nNo errors.\nPackage body created.\nNo errors.\n\nInstallation completed.\nYou are now connected as anjo.\n\n1. Set CBO env if needed\n2. Execute @create_xplore_script.sql\n\nSQL&gt;\n<\/pre><\/div>\n\n\n<p>Now, connected with my user ANJO, we configure how Xplore will run and which tests will it run. In my case, as I&#8217;m not on Exadata, I&#8217;ve excluded that. The rest I&#8217;ve left the default values:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSQL&gt; @create_xplore_script.sql\n\nParameter 1:\nXPLORE Method: XECUTE (default) or XPLAIN\n&quot;XECUTE&quot; requires \/* ^^unique_id *\/ token in SQL\n&quot;XPLAIN&quot; uses &quot;EXPLAIN PLAN FOR&quot; command\nRemember EXPLAIN PLAN FOR does not perform bind peeking\nRemember CREATE TABLE AS SELECT statements are not supported (See Note 1963444.1)\nEnter &quot;XPLORE Method&quot; &#x5B;XECUTE]:\n\nParameter 2:\nInclude CBO Parameters: Y (default) or N\nEnter &quot;CBO Parameters&quot; &#x5B;Y]:\n\nParameter 3:\nInclude Exadata Parameters: Y (default) or N\nEnter &quot;EXADATA Parameters&quot; &#x5B;Y]: N\n\nParameter 4:\nInclude Fix Control: Y (default) or N\nEnter &quot;Fix Control&quot; &#x5B;Y]:\n\nParameter 5:\nGenerate SQL Monitor Reports: N (default) or Y\nOnly applicable when XPLORE Method is XECUTE\nEnter &quot;SQL Monitor&quot; &#x5B;N]:\n\nReview and execute @xplore_script_1.sql\n<\/pre><\/div>\n\n\n<h3 class=\"wp-block-heading\">Run SQLT Xplore<\/h3>\n\n\n\n<p>We can now start Xplore (still on my user ANJO), which will asks the file with the SQL to analyse and the password of our user:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSQL&gt;  @xplore_script_1.sql\n\nCONNECTED_USER\n------------------------------\nANJO\n\nParameter 1:\nName of SCRIPT file that contains SQL to be xplored (required)\nNote: SCRIPT must contain comment \/* ^^unique_id *\/\n\nEnter value for 1: anjo.sql\n\nParameter 2:\nPassword for ANJO (required)\n\nEnter value for 2: ******\n\nValue passed to xplore_script.sql:\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\nSCRIPT_WITH_SQL: anjo.sql\n\n-- begin common\n...\n<\/pre><\/div>\n\n\n<p>I had one error short after:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSQL&gt;EXEC xplore.snapshot_plan(&#039;xplore_{001}_&#x5B;^^run_id.]_(00000)&#039;, &#039;XECUTE&#039;, &#039;N&#039;);\nBEGIN xplore.snapshot_plan(&#039;xplore_{001}_&#x5B;0001]_(00000)&#039;, &#039;XECUTE&#039;, &#039;N&#039;); END;\n\n*\nERROR at line 1:\nORA-01422: exact fetch returns more than requested number of rows\nORA-06512: at &quot;ANJO.XPLORE&quot;, line 744\nORA-06512: at line 1\n<\/pre><\/div>\n\n\n<p>The problem is that for some reason the query he looks for in v$sql is already more than once in the shared pool. The radical solution is to flush the shared pool:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSQL&gt; ALTER SYSTEM FLUSH SHARED_POOL;\n<\/pre><\/div>\n\n\n<p>Other more targeted solution, is to clean only the queries from the Xplore first test:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nbegin\n  for c in (select address,hash_value,sql_text from v$sqlarea WHERE sql_text LIKE &#039;%xplore_{00%(000%&#039; AND plan_hash_value &amp;lt;&gt; 0 AND sql_text NOT LIKE &#039;%FROM v$sql%&#039;) \n    loop \n      sys.dbms_shared_pool.purge(c.address||&#039;,&#039;||c.hash_value,&#039;...&#039;); \n    end loop;\n  end;\n\/\n<\/pre><\/div>\n\n\n<p>The run should now successfully go. Depending on the normal time the query takes to run, it can take a couple of hours for all the almost 2&#8217;000 tests. <\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Checking results of SQLT Xplorer<\/h3>\n\n\n\n<p>You can follow the results, ordered by the faster solution so far, using:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSQL&gt; SELECT * FROM ANJO.XPLORE_TEST WHERE plan_cost IS NOT NULL ORDER BY elapsed_time;\n<\/pre><\/div>\n\n\n<p>There is a test named &#8216;BASELINE&#8217; which has the result of the run without any change.<\/p>\n\n\n\n<p>For all other runs, there are a full  hand of statistics, like the plan cost, buffer gets, disk reads, cpu and elapset time, cardinality and rows processed.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/anjo.pt\/keyword-oracle\/wp-content\/uploads\/sites\/3\/2021\/01\/image-1.png\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"354\" src=\"https:\/\/anjo.pt\/keyword-oracle\/wp-content\/uploads\/sites\/3\/2021\/01\/image-1-1024x354.png\" alt=\"\" class=\"wp-image-433\" srcset=\"https:\/\/anjo.pt\/keyword-oracle\/wp-content\/uploads\/sites\/3\/2021\/01\/image-1-1024x354.png 1024w, https:\/\/anjo.pt\/keyword-oracle\/wp-content\/uploads\/sites\/3\/2021\/01\/image-1-300x104.png 300w, https:\/\/anjo.pt\/keyword-oracle\/wp-content\/uploads\/sites\/3\/2021\/01\/image-1-768x266.png 768w, https:\/\/anjo.pt\/keyword-oracle\/wp-content\/uploads\/sites\/3\/2021\/01\/image-1.png 1093w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/a><\/figure>\n\n\n\n<p>There are other interesting tables:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>XPLORE_TEST &#8211; the main result table, as from above. There is run_id and test_id column that match for the following table.<\/li><li>DISCOVERED_PLAN &#8211; the explain plan for each of the test, with column test_id<\/li><li>BASELINE_PARAMETER_CBO &#8211; with the current database optimizer settings<\/li><li>PLAN_TABLE_ALL &#8211; probably used internally<\/li><li>SQL_MONITOR &#8211; used in case this option selected<\/li><\/ul>\n\n\n\n<p>At the end of the run there is a zip file created, with three files:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>xplore_report_1.html &#8211; a summary of the runs, based on the different plans generated; details of result per plan and the baseline and the plan generated on each of the test<\/li><li>xplore_script1.log &#8211; output of the run<\/li><li>xplore_script1.sql &#8211; the script that was run<\/li><\/ul>\n\n\n\n<p>Example of summary generated in html:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/anjo.pt\/keyword-oracle\/wp-content\/uploads\/sites\/3\/2021\/01\/image-2.png\"><img loading=\"lazy\" decoding=\"async\" width=\"813\" height=\"705\" src=\"https:\/\/anjo.pt\/keyword-oracle\/wp-content\/uploads\/sites\/3\/2021\/01\/image-2.png\" alt=\"\" class=\"wp-image-435\" srcset=\"https:\/\/anjo.pt\/keyword-oracle\/wp-content\/uploads\/sites\/3\/2021\/01\/image-2.png 813w, https:\/\/anjo.pt\/keyword-oracle\/wp-content\/uploads\/sites\/3\/2021\/01\/image-2-300x260.png 300w, https:\/\/anjo.pt\/keyword-oracle\/wp-content\/uploads\/sites\/3\/2021\/01\/image-2-768x666.png 768w\" sizes=\"auto, (max-width: 813px) 100vw, 813px\" \/><\/a><figcaption>Different plans, with elapsed time ranging from 0.2 and 361 seconds!<\/figcaption><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/anjo.pt\/keyword-oracle\/wp-content\/uploads\/sites\/3\/2021\/01\/image-3.png\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"1021\" src=\"https:\/\/anjo.pt\/keyword-oracle\/wp-content\/uploads\/sites\/3\/2021\/01\/image-3-1024x1021.png\" alt=\"\" class=\"wp-image-436\" srcset=\"https:\/\/anjo.pt\/keyword-oracle\/wp-content\/uploads\/sites\/3\/2021\/01\/image-3-1024x1021.png 1024w, https:\/\/anjo.pt\/keyword-oracle\/wp-content\/uploads\/sites\/3\/2021\/01\/image-3-300x300.png 300w, https:\/\/anjo.pt\/keyword-oracle\/wp-content\/uploads\/sites\/3\/2021\/01\/image-3-150x150.png 150w, https:\/\/anjo.pt\/keyword-oracle\/wp-content\/uploads\/sites\/3\/2021\/01\/image-3-768x766.png 768w, https:\/\/anjo.pt\/keyword-oracle\/wp-content\/uploads\/sites\/3\/2021\/01\/image-3.png 1051w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/a><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">Uninstall and cleanup<\/h3>\n\n\n\n<p>As some &#8216;alter system .. scope=memory&#8217; are performed, Oracle recommends to bounce the instance at the end of the test. If all went well, the previous parameters should be normally set.<\/p>\n\n\n\n<p>To uninstall SQLT Xplorer, just run the right script as SYS:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nsqlplus \/ as sysdba\n\nSQL&gt; @uninstall\nTest Case User: anjo\nold   1: DROP PACKAGE BODY &amp;amp;&amp;amp;test_case_user..xplore\nnew   1: DROP PACKAGE BODY anjo.xplore\n\nPackage body dropped.\n...\n<\/pre><\/div>","protected":false},"excerpt":{"rendered":"<p>Yesterday I described how I come to SQLT Xplore and it helped me to find out, on Oracle 12.2.0.1, that optimizer_features_enable=8.1.3 decreased the parsing time of a query from 5 seconds to 0.2 seconds. Today I show how to use SQLT Xplore. What is SQLT Xplore? SQL Xplore automatises the test of almost 2&#8217;000 optimizer [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,8,2,7,39,13],"tags":[62,63,61],"class_list":{"0":"post-431","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-oracle","7":"category-oracle-10g","8":"category-oracle-11g","9":"category-oracle-12c","10":"category-oracle-19c","11":"category-performance","12":"tag-parsing","13":"tag-regression","14":"tag-sqlt","15":"czr-hentry"},"_links":{"self":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/431","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/comments?post=431"}],"version-history":[{"count":7,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/431\/revisions"}],"predecessor-version":[{"id":445,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/431\/revisions\/445"}],"wp:attachment":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/media?parent=431"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/categories?post=431"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/tags?post=431"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}