{"id":11354,"date":"2018-06-18T15:23:37","date_gmt":"2018-06-18T13:23:37","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/how-to-fix-your-own-sql-plan-in-oracle\/"},"modified":"2018-06-18T15:23:37","modified_gmt":"2018-06-18T13:23:37","slug":"how-to-fix-your-own-sql-plan-in-oracle","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/how-to-fix-your-own-sql-plan-in-oracle\/","title":{"rendered":"How to fix your own SQL plan in Oracle ?"},"content":{"rendered":"

Method to build and fix your own SQL plans in Oracle and then you want to fix it for all next executions of the SQL by your application (thanks to SPM).<\/p>\n

In this post I show how fix a plan you have created by yourself.<\/p>\n

First we need to identified the query<\/strong><\/p>\n

SQL> select \/* INDEX_ACCESS *\/ line_item_id, product_id, unit_price, quantity from ORDER_ITEMS where order_id = 234098 ;\n\nLINE_ITEM_ID PRODUCT_ID UNIT_PRICE   QUANTITY\n------------ ---------- ---------- ----------\n           1        414        851          5\n           2        499        818          3\n\nExecution Plan\n----------------------------------------------------------\nPlan hash value: 4159986352\n\n-----------------------------------------------------------------------------------------------------\n| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |\n-----------------------------------------------------------------------------------------------------\n|   0 | SELECT STATEMENT                    |               |     3 |    57 |     5   (0)| 00:00:01 |\n|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| ORDER_ITEMS   |     3 |    57 |     5   (0)| 00:00:01 |\n|*  2 |   INDEX RANGE SCAN                  | ITEM_ORDER_IX |     3 |       |     3   (0)| 00:00:01 |\n-----------------------------------------------------------------------------------------------------\n\n<\/pre>\n
In that example I’ll take an application query against the ORDER_ITEMS table. I find hat query too fast because it is using the index ITEM_ORDER_IX which is based on the primary key. So we are going to force that query to be executed by accessing the whole table without using any index. Here I’ll use the hint FULL to do the job.<\/p>\n
SQL> select \/* FULL_ACCESS *\/ \/*+ full(ORDER_ITEMS) *\/ line_item_id, product_id, unit_price, quantity from ORDER_ITEMS where order_id = 234098 ;\n\nLINE_ITEM_ID PRODUCT_ID UNIT_PRICE   QUANTITY\n------------ ---------- ---------- ----------\n           1        414        851          5\n           2        499        818          3\n\nExecution Plan\n----------------------------------------------------------\nPlan hash value: 456270211\n\n---------------------------------------------------------------------------------\n| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |\n---------------------------------------------------------------------------------\n|   0 | SELECT STATEMENT  |             |     3 |    57 |  2022   (1)| 00:00:01 |\n|*  1 |  TABLE ACCESS FULL| ORDER_ITEMS |     3 |    57 |  2022   (1)| 00:00:01 |\n---------------------------------------------------------------------------------<\/pre>\n
 <\/p>\n
On both queries I added a comment to make it easier to retrieve information in the SQL views from Oracle. Now I can get the statistic about my queries:<\/p>\n
SQL> @sql\n\nSQL_ID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PLAN_HASH_VALUE SQL_PLAN_BASELINE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 EXECUTIONS ELAPSED_TIME EXACT_MATCHING_SIGNATURE SQL_TEXT\n-------------- ---------------- ------------------------------ ---------- ------------ ------------------------ --------------------------------------------------\n8ms87fhrq01xh\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4159986352\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 25\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5199\u00a0\u00a0\u00a0\u00a0 18199500880047668241 select \/* INDEX_ACCESS *\/ line_item_id, product_id\n55x955b31npwq\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 456270211\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 155947\u00a0\u00a0\u00a0\u00a0 10822814485518112755 select \/* FULL_ACCESS *\/ \/*+ full(ORDER_ITEMS) *\/\n<\/pre>\n
 <\/p>\n
Plan control<\/strong><\/p>\n
So my goal is to force the application query “8ms87fhrq01xh” to use the plan from my manual modified query “55x955b31npwq”. To do so, I’m going to use the\u00a0 “SQL Plan Management” from Oracle which is embedded from the release 11 and can be used with the DBMS_SPM<\/a> package.<\/p>\n
First I need to load the plan from my application query into SPM baseline:<\/strong><\/p>\n
SQL> set serveroutput on\ndeclare\n  plans_loaded pls_integer ;\nbegin\n  plans_loaded := dbms_spm.load_plans_from_cursor_cache( sql_id => '8ms87fhrq01xh' ) ;\n  dbms_output.put_line( 'plans loaded: '||plans_loaded ) ;\nend ;\n\/\nplans loaded: 1\n\nPL\/SQL procedure successfully completed.<\/pre>\n
 <\/p>\n
I have now a new cursor in the SQL view with the SQL_PLAN_BASELINE identifier:<\/strong><\/p>\n
SQL> @sql\n\nSQL_ID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PLAN_HASH_VALUE SQL_PLAN_BASELINE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 EXECUTIONS ELAPSED_TIME EXACT_MATCHING_SIGNATURE SQL_TEXT\n-------------- ---------------- ------------------------------ ---------- ------------ ------------------------ --------------------------------------------------\n8ms87fhrq01xh\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4159986352\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 25\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5199\u00a0\u00a0\u00a0\u00a0 18199500880047668241 select \/* INDEX_ACCESS *\/ line_item_id, product_id\n8ms87fhrq01xh\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4159986352 SQL_PLAN_gt4cxn0aacz0j91520601\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 21703\u00a0\u00a0\u00a0\u00a0 18199500880047668241 select \/* INDEX_ACCESS *\/ line_item_id, product_id\n55x955b31npwq\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 456270211\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 155947\u00a0\u00a0\u00a0\u00a0 10822814485518112755 select \/* FULL_ACCESS *\/ \/*+ full(ORDER_ITEMS) *\/<\/pre>\n
 <\/p>\n
I can now find the SPM content for my SQL:<\/strong><\/p>\n
SQL> @spm\nEnter value for signature: 18199500880047668241\nold\u00a0\u00a0 9: where signature = '&signature.'\nnew\u00a0\u00a0 9: where signature = '18199500880047668241'\n\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SIGNATURE SQL_HANDLE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PLAN_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ENABL ACCEP FIXED\n--------------------- ------------------------------ ------------------------------ ----- ----- -----\n\u00a018199500880047668241 SQL_fc919da014a67c11\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SQL_PLAN_gt4cxn0aacz0j91520601 YES\u00a0\u00a0 YES\u00a0\u00a0 NO<\/pre>\n
 <\/p>\n
What we need to do now is to inject into the SPM baseline the plan from my modified query. To do so, I need the SQL_HANDLE of my application query and the couple of SQL_ID+PLAN_HASH_VALUE of the modified query to inject its plan into the plan baseline of my application query:<\/p>\n
SQL> set serveroutput on\ndeclare\n  plans_loaded pls_integer ;\nbegin\n  plans_loaded := dbms_spm.load_plans_from_cursor_cache(\n    sql_id => '55x955b31npwq',\n    plan_hash_value => 456270211,\n    sql_handle => 'SQL_fc919da014a67c11'\n  ) ;\n  dbms_output.put_line( 'plans loaded: '||plans_loaded ) ;\nend ;\n\/\nplans loaded: 1\n\nPL\/SQL procedure successfully completed.<\/pre>\n
 <\/p>\n
Now, let’s seen what’s in the baseline of our application query:<\/strong><\/p>\n
SQL> @spm\nEnter value for signature: 18199500880047668241\nold   9: where signature = '&signature.'\nnew   9: where signature = '18199500880047668241'\n\n            SIGNATURE SQL_HANDLE                     PLAN_NAME                      ENABL ACCEP FIXED\n--------------------- ------------------------------ ------------------------------ ----- ----- -----\n 18199500880047668241 SQL_fc919da014a67c11           SQL_PLAN_gt4cxn0aacz0j91520601 YES   YES   NO\n 18199500880047668241 SQL_fc919da014a67c11           SQL_PLAN_gt4cxn0aacz0jf91228bb YES   YES   NO\n<\/pre>\n
 <\/p>\n
A new plan called “SQL_PLAN_gt4cxn0aacz0jf91228bb” has been generated and I know want to be sure it is the only one that are goin gto be used. Thus we need to fix it:<\/p>\n
SQL> set serveroutput on\ndeclare\n  plans_loaded pls_integer ;\nbegin\n  plans_loaded := dbms_spm.alter_sql_plan_baseline(\n    sql_handle => 'SQL_fc919da014a67c11',\nplan_name => 'SQL_PLAN_gt4cxn0aacz0jf91228bb',\n    attribute_name => 'fixed',\n    attribute_value => 'YES'\n  ) ;\n  dbms_output.put_line( 'plans modified: '||plans_loaded ) ;\nend ;\n\/\nplans modified: 1\n\nPL\/SQL procedure successfully completed.\n\nSQL> @spm\nEnter value for signature: 18199500880047668241\nold\u00a0\u00a0 9: where signature = '&signature.'\nnew\u00a0\u00a0 9: where signature = '18199500880047668241'\n\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SIGNATURE SQL_HANDLE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PLAN_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ENABL ACCEP FIXED\n--------------------- ------------------------------ ------------------------------ ----- ----- -----\n\u00a018199500880047668241 SQL_fc919da014a67c11\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SQL_PLAN_gt4cxn0aacz0j91520601 YES\u00a0\u00a0 YES\u00a0\u00a0 NO\n\u00a018199500880047668241 SQL_fc919da014a67c11\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SQL_PLAN_gt4cxn0aacz0jf91228bb YES\u00a0\u00a0 YES\u00a0\u00a0 YES\n<\/pre>\n
 <\/p>\n
Then, my Full access plan is fixed and I can check if the index on the primary key is still in used:<\/strong><\/p>\n
SQL> select \/* INDEX_ACCESS *\/ line_item_id, product_id, unit_price, quantity from ORDER_ITEMS where order_id = 234098\n\nLINE_ITEM_ID PRODUCT_ID UNIT_PRICE   QUANTITY\n------------ ---------- ---------- ----------\n           1        414        851          5\n           2        499        818          3\n\nExecution Plan\n----------------------------------------------------------\nPlan hash value: 456270211\n\n---------------------------------------------------------------------------------\n| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |\n---------------------------------------------------------------------------------\n|   0 | SELECT STATEMENT  |             |     3 |    57 |  2022   (1)| 00:00:01 |\n|*  1 |  TABLE ACCESS FULL| ORDER_ITEMS |     3 |    57 |  2022   (1)| 00:00:01 |\n---------------------------------------------------------------------------------<\/pre>\n
 <\/p>\n
I can monitor the plan usage from the SQL view to check if the application has is executing the query with the new plan:<\/strong><\/p>\n
SQL> @sql\n\nSQL_ID          PLAN_HASH_VALUE SQL_PLAN_BASELINE              EXECUTIONS ELAPSED_TIME EXACT_MATCHING_SIGNATURE SQL_TEXT\n-------------- ---------------- ------------------------------ ---------- ------------ ------------------------ --------------------------------------------------\n8ms87fhrq01xh         456270211 SQL_PLAN_gt4cxn0aacz0jf91228bb          6       145687     18199500880047668241 select \/* INDEX_ACCESS *\/ line_item_id, product_id\n55x955b31npwq         456270211                                         3       155947     10822814485518112755 select \/* FULL_ACCESS *\/ \/*+ full(ORDER_ITEMS) *\/<\/pre>\n
 <\/p>\n
As I see that both queries are currently using the same plan, I know that my application is now using the new plan with the full access to the ORDER_ITEMS table.<\/p>\n
I hope this demonstration may help and please do not hesitate to contact us if you have any further questions or observations.<\/p>\n
Scripts used in this article:<\/strong><\/p>\n
-- script sql.sql\nset lines 180 pages 500\ncol sql_id format a14\ncol sql_plan_baseline format a30\ncol plan_hash_value format 999999999999999\ncol exact_matching_signature format 99999999999999999999\ncol sql_text format a50\nselect sql_id,\nplan_hash_value,\nsql_plan_baseline,\nexecutions,\nelapsed_time,\nexact_matching_signature,\nsubstr(sql_text,0,50) sql_text\nfrom v$sql\nwhere parsing_schema_name != 'SYS'\nand sql_text like '%_ACCESS%' ;<\/pre>\n
-- script spm.sql\nset lines 200\nset pages 500\ncol signature format 99999999999999999999\ncol sql_handle format a30\ncol plan_name format a30\ncol enabled format a5\ncol accepted format a5\ncol fixed format a5\nselect\nsignature,\nsql_handle,\nplan_name,\nenabled,\naccepted,\nfixed\nfrom dba_sql_plan_baselines\nwhere signature = '&signature.'<\/pre>\n","protected":false},"excerpt":{"rendered":"
Method to build and fix your own SQL plans in Oracle and then you want to fix it for all next executions of the SQL by your application (thanks to SPM). In this post I show how fix a plan you have created by yourself. First we need to identified the query SQL> select \/* […]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,198,368],"tags":[],"type_dbi":[],"class_list":["post-11354","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","category-development-performance"],"acf":[],"yoast_head":"\nHow to fix your own SQL plan in Oracle ? - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/how-to-fix-your-own-sql-plan-in-oracle\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to fix your own SQL plan in Oracle ?\" \/>\n<meta property=\"og:description\" content=\"Method to build and fix your own SQL plans in Oracle and then you want to fix it for all next executions of the SQL by your application (thanks to SPM). In this post I show how fix a plan you have created by yourself. First we need to identified the query SQL> select \/* […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/how-to-fix-your-own-sql-plan-in-oracle\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2018-06-18T13:23:37+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-to-fix-your-own-sql-plan-in-oracle\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-to-fix-your-own-sql-plan-in-oracle\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"How to fix your own SQL plan in Oracle ?\",\"datePublished\":\"2018-06-18T13:23:37+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-to-fix-your-own-sql-plan-in-oracle\/\"},\"wordCount\":474,\"commentCount\":4,\"articleSection\":[\"Database Administration & Monitoring\",\"Database management\",\"Development & Performance\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/how-to-fix-your-own-sql-plan-in-oracle\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-to-fix-your-own-sql-plan-in-oracle\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/how-to-fix-your-own-sql-plan-in-oracle\/\",\"name\":\"How to fix your own SQL plan in Oracle ? - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2018-06-18T13:23:37+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-to-fix-your-own-sql-plan-in-oracle\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/how-to-fix-your-own-sql-plan-in-oracle\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-to-fix-your-own-sql-plan-in-oracle\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to fix your own SQL plan in Oracle ?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"How to fix your own SQL plan in Oracle ? - dbi Blog","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:\/\/www.dbi-services.com\/blog\/how-to-fix-your-own-sql-plan-in-oracle\/","og_locale":"en_US","og_type":"article","og_title":"How to fix your own SQL plan in Oracle ?","og_description":"Method to build and fix your own SQL plans in Oracle and then you want to fix it for all next executions of the SQL by your application (thanks to SPM). In this post I show how fix a plan you have created by yourself. First we need to identified the query SQL> select \/* […]","og_url":"https:\/\/www.dbi-services.com\/blog\/how-to-fix-your-own-sql-plan-in-oracle\/","og_site_name":"dbi Blog","article_published_time":"2018-06-18T13:23:37+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/how-to-fix-your-own-sql-plan-in-oracle\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/how-to-fix-your-own-sql-plan-in-oracle\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"How to fix your own SQL plan in Oracle ?","datePublished":"2018-06-18T13:23:37+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/how-to-fix-your-own-sql-plan-in-oracle\/"},"wordCount":474,"commentCount":4,"articleSection":["Database Administration & Monitoring","Database management","Development & Performance"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/how-to-fix-your-own-sql-plan-in-oracle\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/how-to-fix-your-own-sql-plan-in-oracle\/","url":"https:\/\/www.dbi-services.com\/blog\/how-to-fix-your-own-sql-plan-in-oracle\/","name":"How to fix your own SQL plan in Oracle ? - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2018-06-18T13:23:37+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/how-to-fix-your-own-sql-plan-in-oracle\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/how-to-fix-your-own-sql-plan-in-oracle\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/how-to-fix-your-own-sql-plan-in-oracle\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How to fix your own SQL plan in Oracle ?"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/11354","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=11354"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/11354\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=11354"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=11354"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=11354"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=11354"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}