{"id":427,"date":"2021-01-20T21:23:12","date_gmt":"2021-01-20T20:23:12","guid":{"rendered":"https:\/\/anjo.pt\/keyword-oracle\/?p=427"},"modified":"2021-01-30T13:49:06","modified_gmt":"2021-01-30T12:49:06","slug":"long-parsing-on-oracle-12-2-and-the-discovery-of-sqlt-xplore","status":"publish","type":"post","link":"https:\/\/anjo.pt\/keyword-oracle\/2021\/01\/20\/long-parsing-on-oracle-12-2-and-the-discovery-of-sqlt-xplore\/","title":{"rendered":"Long parsing on Oracle 12.2 and the discovery of SQLT Xplore"},"content":{"rendered":"\n

Today I discovered a fantastic free tool from Oracle: SQLT Xplore!<\/p>\n\n\n\n

At a client I had one query that took long time parsing. The tkprof result of the 10046 trace showed it took 5 seconds to parse:<\/p>\n\n\n

\ncall     count       cpu    elapsed       disk      query    current        rows\n------- ------  -------- ---------- ---------- ---------- ----------  ----------\nParse        1      5.17       5.30          0          0          0           0\nExecute      1      0.00       0.00          0          0          0           0\nFetch        1      0.00       0.00          0        665          0          26\n------- ------  -------- ---------- ---------- ---------- ----------  ----------\ntotal        3      5.17       5.30          0        665          0          26\n\n<\/pre><\/div>\n\n\n
Looking around I’ve noticed that disabling the cost based transformation:<\/p>\n\n\n
\nALTER SESSIONS SET "_optimizer_cost_based_transformation"=off;  \n<\/pre><\/div>\n\n\n
 …the query was much faster to parse: only 0,5 seconds.<\/p>\n\n\n\n\n\n\n\n
I’ve open an SR with the SQLT Xecute result, and after insisting the problem was not the execute but rather the parsing, the analyst said it was probably the non-public bug:<\/p>\n\n\n\n
HI Miguel\n\nThe issue seems to be matching with an internal document and for some reason it is not yet published to the customers.\n\nQuery Parse is Taking Long time After DB Upgraded From 12102 To 12201 with CBQT ( Doc ID 2520631.1<\/a> )\n\nBelow is a snippet from the 10053 trace and we could see that in the CBQT , query is taking 25.86 seconds and the overall query time is 26.303 seconds.\n\nTIMER: CBQT SU and CVM SEL$3D2C5B0A cpu: 25.367 sec elapsed: 25.864 sec >>>>>>>>>>>>>>>>\nTIMER: SQL Optimization (Overall) SEL$FF489B46 cpu: 25.796 sec elapsed: 26.303 sec >>>>>>>>>>>>>>>>>\n\nThere is no patch available for this issue currently and the only option available is to set the parameter to 'off'.<\/pre>\n\n\n\n
I insisted if there was a better or other solution than disabling the cost based transformation and he suggested to run SQLT Xplore<\/a>.<\/p>\n\n\n\n
This tool tested 1847 optimizer parameters changes, bug fixes (more on Exadata) and simply gave an html output with the summary result, the different plans and timings:<\/p>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
But I found easier to check directly on the database, ordered by the best solution timing:<\/p>\n\n\n
\nSELECT * FROM anjo.xplore_test WHERE plan_cost IS NOT NULL ORDER BY elapsed_time;\n<\/pre><\/div>\n\n\n
This showed me that with optimizer_features_enabled for any Oracle version 8i the parsing was much faster (and having the same results, important to check!), followed by using  “_unnest_subquery” = FALSE or “_optimizer_unnest_all_subqueries” = FALSE.<\/p>\n\n\n\n
To apply this, then the best is to use a SQL Patch if the query text is stable:<\/p>\n\n\n
\nDECLARE\n  l  VARCHAR2(32767);\nBEGIN\n  l := SYS.DBMS_SQLDIAG.create_sql_patch(\n    sql_id    => 'dy8x4h4bratan',\n    hint_text => q'[opt_param('optimizer_features_enable','8.1.3')]',\n    name      => 'optimizer_813');\nEND;\n\/\n<\/pre><\/div>\n\n\n
Or add the change as a hint directly to the SQL:<\/p>\n\n\n
\nselect \/*+ OPT_PARAM('_unnestsql_subquery','true') *\/\n...\n<\/pre><\/div>\n\n\n
Here how to use SQL Xplorer<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"
Today I discovered a fantastic free tool from Oracle: SQLT Xplore! At a client I had one query that took long time parsing. The tkprof result of the 10046 trace showed it took 5 seconds to parse: Looking around I’ve noticed that disabling the cost based transformation: …the query was much faster to parse: only […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,2,7,39,13],"tags":[],"class_list":{"0":"post-427","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-oracle","7":"category-oracle-11g","8":"category-oracle-12c","9":"category-oracle-19c","10":"category-performance","11":"czr-hentry"},"_links":{"self":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/427","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=427"}],"version-history":[{"count":5,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/427\/revisions"}],"predecessor-version":[{"id":444,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/427\/revisions\/444"}],"wp:attachment":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/media?parent=427"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/categories?post=427"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/tags?post=427"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}