{"id":4092,"date":"2014-11-07T20:28:30","date_gmt":"2014-11-07T19:28:30","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/oracle-sql-profiles-check-what-they-do-before-accepting-them-blindly\/"},"modified":"2014-11-07T20:28:30","modified_gmt":"2014-11-07T19:28:30","slug":"oracle-sql-profiles-check-what-they-do-before-accepting-them-blindly","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/oracle-sql-profiles-check-what-they-do-before-accepting-them-blindly\/","title":{"rendered":"Oracle SQL Profiles: Check what they do before accepting them blindly"},"content":{"rendered":"

By Franck Pachot<\/h2>\n

.
\nPeople often ask me if I use the SQL Tuning Advisor. The answer is no, at least not through the end of it. But don’t take me wrong. SQL Tuning Advisor is a really nice feature. If you like the CBO then you must love the Tuning Advisor, because it is just the CBO with more time to optimize, and more ways to optimize. The fact is that when a customer calls me, they expect a bit more than running an advisor and implement the result. I’m there to fix the root cause, not just workaround some missing stats. And this is why I use it very rarely.<\/p>\n

However when I have a big query, with a plan that covers several pages, it takes a lot of time to find what is wrong. The method is often based on comparing the estimated cardinalities with the actual ones. But If I have access to the Tuning Pack, then the SQL Tuning Advisor can help to find very quickly where the estimations are going wrong.<\/p>\n

The SQL Tuning Advisor proposes a SQL Profile to adjust the estimations. Then I just have to check the biggest adjustment and I can focus where the estimations are wrong. However, that information is not exposed. The SQL Tuning Advisor report shows the new plan, but not the ways it gets to it.<\/p>\n

The goal of this post is to give you the query I use to show exactly what the profile will implement when you accept it.<\/p>\n

Even if you’re going to accept the profile, It’s a good idea to check it before. It will help to choose if you need to enable ‘force matching’ or not. And if it is a good fix or if there are more sustainable ways to achieve the same.<\/p>\n

You probably know that a profile implements the estimation adjustment with the OPT_ESTIMATE hints which adjust it with a ‘scale_rows’ factor that can apply to tables, index selectivity or joins. They is very well explained on the Pythian blog<\/a><\/p>\n

So, let’s take an exemple. My favorite query to show bad estimations on the HR schema is:<\/p>\n

alter session set current_schema=HR;\nselect distinct DEPARTMENT_NAME\n from DEPARTMENTS join EMPLOYEES using(DEPARTMENT_ID)\n where DEPARTMENT_NAME like '%ing' and SALARY>20000;\n<\/pre>\n
For the simple reason that I know that lot of the department names are ending with ‘ing’ (Marketing, Purchasing,…) but the CBO doesn’t know that. And I can’t give that information through column statistics or histograms:<\/p>\n
SQL> select * from table( dbms_xplan.display_cursor(format=>'rowstats last ') );\n\nPLAN_TABLE_OUTPUT\n-------------------------------------------------------------------------------------------------------\nSQL_ID  4fz1vtn0w8aak, child number 0\n-------------------------------------\nselect distinct DEPARTMENT_NAME  from DEPARTMENTS join EMPLOYEES\nusing(DEPARTMENT_ID)  where DEPARTMENT_NAME like '%ing' and SALARY>20000\n\nPlan hash value: 3041748347\n\n----------------------------------------------------------------------------------------------\n| Id  | Operation                             | Name              | Starts | E-Rows | A-Rows |\n----------------------------------------------------------------------------------------------\n|   0 | SELECT STATEMENT                      |                   |      1 |        |      0 |\n|   1 |  HASH UNIQUE                          |                   |      1 |      1 |      0 |\n|   2 |   NESTED LOOPS SEMI                   |                   |      1 |      1 |      0 |\n|*  3 |    TABLE ACCESS FULL                  | DEPARTMENTS       |      1 |      1 |      7 |\n|*  4 |    TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES         |      7 |      1 |      0 |\n|*  5 |     INDEX RANGE SCAN                  | EMP_DEPARTMENT_IX |      7 |     10 |     55 |\n----------------------------------------------------------------------------------------------\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n\n   3 - filter(\"DEPARTMENTS\".\"DEPARTMENT_NAME\" LIKE '%ing')\n   4 - filter(\"EMPLOYEES\".\"SALARY\">20000)\n   5 - access(\"DEPARTMENTS\".\"DEPARTMENT_ID\"=\"EMPLOYEES\".\"DEPARTMENT_ID\")\n\nNote\n-----\n   - this is an adaptive plan\n\n<\/pre>\n
Look at E-Rows and A-Rows: I have 7 departments ending with ‘ing’ but the optimizer thinks that there is only one. In 11g you need a profile to help the optimizer or you’re going into an expensive nested loop. This example has few rows, but imagine the consequence when a nested loop is choosen but must execute on millon of rows. In 12c – and if you are in Enterprise Edition – the adaptive plan will help to avoid that situation. As soon as a threshold is reached another plan will be executed.<\/p>\n
But even with adaptive plan, there may be a better plan that is possible only with accurate estimations. Let’s see what the SQL Tuning Advisor will find.<\/p>\n
Running SQL Tuning Advisor<\/h3>\n
I create and execute the tuning task:<\/p>\n
begin\n dbms_output.put_line('task id: '||\n dbms_sqltune.create_tuning_task(\n  task_name=>'dbiInSite',\n  description=>'dbi InSite workshop Oracle Tuning',\n  scope=>dbms_sqltune.scope_comprehensive,\n  time_limit=>30,\n  sql_id=>'4fz1vtn0w8aak'\n  )\n );\n dbms_sqltune.execute_tuning_task('dbiInSite');\nend;\n\/\n<\/pre>\n
And show the report:<\/p>\n
SQL> set long 1000000 longc 1000000\nSQL> select dbms_sqltune.report_tuning_task('dbiInSite') FROM dual;\n\nDBMS_SQLTUNE.REPORT_TUNING_TASK('DBIINSITE')\n-------------------------------------------------------------------------------\nGENERAL INFORMATION SECTION\n-------------------------------------------------------------------------------\nTuning Task Name   : dbiInSite\nTuning Task Owner  : SYS\nWorkload Type      : Single SQL Statement\nScope              : COMPREHENSIVE\nTime Limit(seconds): 30\nCompletion Status  : COMPLETED\nStarted at         : 11\/08\/2014 00:03:22\nCompleted at       : 11\/08\/2014 00:03:23\n\n-------------------------------------------------------------------------------\nSchema Name: HR\nSQL ID     : 4fz1vtn0w8aak\nSQL Text   : select distinct DEPARTMENT_NAME\n              from DEPARTMENTS join EMPLOYEES using(DEPARTMENT_ID)\n              where DEPARTMENT_NAME like '%ing' and SALARY>20000\n\n-------------------------------------------------------------------------------\nFINDINGS SECTION (1 finding)\n-------------------------------------------------------------------------------\n\n1- SQL Profile Finding (see explain plans section below)\n--------------------------------------------------------\n  A potentially better execution plan was found for this statement.\n\n  Recommendation (estimated benefit: 33.67%)\n  ------------------------------------------\n  - Consider accepting the recommended SQL profile.\n    execute dbms_sqltune.accept_sql_profile(task_name => 'dbiInSite',\n            task_owner => 'SYS', replace => TRUE);\n\n  Validation results\n  ------------------\n  The SQL profile was tested by executing both its plan and the original plan\n  and measuring their respective execution statistics. A plan may have been\n  only partially executed if the other could be run to completion in less time.\n\n                           Original Plan  With SQL Profile  % Improved\n                           -------------  ----------------  ----------\n  Completion Status:            COMPLETE          COMPLETE\n  Elapsed Time (s):             .005964           .000177      97.03 %\n  CPU Time (s):                 .005999             .0002      96.66 %\n  User I\/O Time (s):                  0                 0\n  Buffer Gets:                       13                 9      30.76 %\n  Physical Read Requests:             0                 0\n  Physical Write Requests:            0                 0\n  Physical Read Bytes:                0                 0\n  Physical Write Bytes:               0                 0\n  Rows Processed:                     0                 0\n  Fetches:                            0                 0\n  Executions:                         1                 1\n\n  Notes\n  -----\n  1. Statistics for the original plan were averaged over 10 executions.\n  2. Statistics for the SQL profile plan were averaged over 10 executions.\n\n-------------------------------------------------------------------------------\nEXPLAIN PLANS SECTION\n-------------------------------------------------------------------------------\n\n1- Original With Adjusted Cost\n------------------------------\nPlan hash value: 3041748347\n\n-------------------------------------------------------------------------------------------------\n| Id  | Operation                             | Name              | Rows  | Bytes | Cost (%CPU)| \n-------------------------------------------------------------------------------------------------\n|   0 | SELECT STATEMENT                      |                   |     1 |    23 |     7  (15)| \n|   1 |  HASH UNIQUE                          |                   |     1 |    23 |     7  (15)| \n|   2 |   NESTED LOOPS SEMI                   |                   |     1 |    23 |     6   (0)| \n|*  3 |    TABLE ACCESS FULL                  | DEPARTMENTS       |     7 |   112 |     3   (0)| \n|*  4 |    TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES         |     1 |     7 |     1   (0)| \n|*  5 |     INDEX RANGE SCAN                  | EMP_DEPARTMENT_IX |    10 |       |     0   (0)| \n-------------------------------------------------------------------------------------------------\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n\n   3 - filter(\"DEPARTMENTS\".\"DEPARTMENT_NAME\" LIKE '%ing')\n   4 - filter(\"EMPLOYEES\".\"SALARY\">20000)\n   5 - access(\"DEPARTMENTS\".\"DEPARTMENT_ID\"=\"EMPLOYEES\".\"DEPARTMENT_ID\")\n\n2- Using SQL Profile\n--------------------\nPlan hash value: 2473492969\n\n---------------------------------------------------------------------------------------------\n| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |\n---------------------------------------------------------------------------------------------\n|   0 | SELECT STATEMENT              |             |     1 |    23 |     5  (20)| 00:00:01 |\n|   1 |  HASH UNIQUE                  |             |     1 |    23 |     5  (20)| 00:00:01 |\n|   2 |   NESTED LOOPS                |             |     1 |    23 |     4   (0)| 00:00:01 |\n|   3 |    NESTED LOOPS               |             |     1 |    23 |     4   (0)| 00:00:01 |\n|*  4 |     TABLE ACCESS FULL         | EMPLOYEES   |     1 |     7 |     3   (0)| 00:00:01 |\n|*  5 |     INDEX UNIQUE SCAN         | DEPT_ID_PK  |     1 |       |     0   (0)| 00:00:01 |\n|*  6 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |     1 |    16 |     1   (0)| 00:00:01 |\n---------------------------------------------------------------------------------------------\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n\n   4 - filter(\"EMPLOYEES\".\"SALARY\">20000)\n   5 - access(\"DEPARTMENTS\".\"DEPARTMENT_ID\"=\"EMPLOYEES\".\"DEPARTMENT_ID\")\n   6 - filter(\"DEPARTMENTS\".\"DEPARTMENT_NAME\" LIKE '%ing')\n\n-------------------------------------------------------------------------------\n\n<\/pre>\n
So even if I have adaptive plan, a better plan is possible. When the optimizer know that there are more rows from DEPARTMENTS than EMPLOYEES, it’s better to start with EMPLOYEES, for nested loop as well as for hash join.<\/p>\n
Showing the OPT_ESTIMATE hints<\/h3>\n
Then I don’t want to accept the profile yet, but want to see all those OPT_ESTIMATE hints that has been determined by the SQL Tuning Advisor and that will be added to the query when the profile is accepted. Here my script. Note that this script is for 11g and 12c. In 10g the information was stored elsewhere. You can go to Jonathan Levis post<\/a> for the 10g query.<\/p>\n
set serveroutput on echo off\ndeclare\n  -- input variables\n  input_task_owner dba_advisor_tasks.owner%type:='SYS';\n  input_task_name dba_advisor_tasks.task_name%type:='dbiInSite';\n  input_show_outline boolean:=false;\n  -- local variables\n  task_id  dba_advisor_tasks.task_id%type;\n  outline_data xmltype;\n  benefit number;\nbegin\n  for o in ( select * from dba_advisor_objects where owner=input_task_owner and task_name=input_task_name and type='SQL')\n  loop\n          -- get the profile hints (opt_estimate)\n          dbms_output.put_line('--- PROFILE HINTS from '||o.task_name||' ('||o.object_id||') statement '||o.attr1||':');\n          dbms_output.put_line('\/*+');\n          for r in (\n            select hint,benefit from (\n             select case when attr5 like 'OPT_ESTIMATE%' then cast(attr5 as varchar2(4000)) when attr1 like 'OPT_ESTIMATE%' then attr1 end hint,benefit\n             from dba_advisor_recommendations t join dba_advisor_rationale r using (task_id,rec_id)\n             where t.owner=o.owner and t.task_name = o.task_name and r.object_id=o.object_id and t.type='SQL PROFILE'\n             --and r.message='This attribute adjusts optimizer estimates.'\n            ) order by to_number(regexp_replace(hint,'^.*=([0-9.]+)[^0-9].*$','1'))\n          ) loop\n           dbms_output.put_line('   '||r.hint); benefit:=to_number(r.benefit)\/100;\n          end loop;\n          dbms_output.put_line('*\/');\n          -- get the outline hints\n          begin\n          select outline_data into outline_data from (\n              select case when other_xml is not null then extract(xmltype(other_xml),'\/*\/outline_data\/hint') end outline_data\n              from dba_advisor_tasks t join dba_sqltune_plans p using (task_id)\n              where t.owner=o.owner and t.task_name = o.task_name and p.object_id=o.object_id  and t.advisor_name='SQL Tuning Advisor' --11gonly-- and execution_type='TUNE SQL'\n              and p.attribute='Using SQL profile'\n          ) where outline_data is not null;\n          exception when no_data_found then null;\n          end;\n          exit when not input_show_outline;\n          dbms_output.put_line('--- OUTLINE HINTS from '||o.task_name||' ('||o.object_id||') statement '||o.attr1||':');\n          dbms_output.put_line('\/*+');\n          for r in (\n              select (extractvalue(value(d), '\/hint')) hint from table(xmlsequence(extract( outline_data , '\/'))) d\n          ) loop\n           dbms_output.put_line('   '||r.hint);\n          end loop;\n          dbms_output.put_line('*\/');\n          dbms_output.put_line('--- Benefit: '||to_char(to_number(benefit),'FM99.99')||'%');\n  end loop;\n  dbms_output.put_line('');\nend;\n\/\n\n\n<\/pre>\n
And here is the output:<\/p>\n
--- PROFILE HINTS from dbiInSite (1) statement 4fz1vtn0w8aak:\n\/*+\nOPT_ESTIMATE(@\"SEL$2CBA5DDD\", TABLE, \"EMPLOYEES\"@\"SEL$1\", SCALE_ROWS=2)\nOPT_ESTIMATE(@\"SEL$58A6D7F6\", TABLE, \"EMPLOYEES\"@\"SEL$1\", SCALE_ROWS=2)\nOPT_ESTIMATE(@\"SEL$6AE97DF7\", TABLE, \"DEPARTMENTS\"@\"SEL$1\", SCALE_ROWS=5.185185185)\nOPT_ESTIMATE(@\"SEL$58A6D7F6\", TABLE, \"DEPARTMENTS\"@\"SEL$1\", SCALE_ROWS=5.185185185)\n*\/\n\nPL\/SQL procedure successfully completed.\n<\/pre>\n
This is very interesting information. It says that the actual number of employees in this query (with that specific where clause) is 2 times what is estimated from statistics. And that the estimated number of departments is 5 times what is estimated.<\/p>\n
So what it is used for?<\/h3>\n
That gives me several ways to improve, even without implementing the profile.<\/p>\n
First, the number of employees for a ‘SALARY>2000’ predicate can be improved with histograms. That is better than a profile because it will improve all queries that filter on employee salary.<\/p>\n
Then, for DEPARTMENTS, histograms will not help because the bad estimation comes from the LIKE ‘%ing’ predicate and I’ve no way to give that information with statistics. Ok, let’s go for the profile. If you want to implement the profile, will you choose ‘force matching’ or not? Of course not. The estimation adjustment makes sense only with our ‘%ing’ values. This is were looking at the OPT_ESTIMATE is very important, or you can’t do the right choice.<\/p>\n
And you may choose something else than profile. Dynamic sampling makes sense in this case. Another solution may be to add OPT_ESTIMATE or CARDINALITY hints in the query.<\/p>\n
Conclusion<\/h3>\n
SQL Tuning Advisor is powerful, when used intelligently. It gives ideas about what is wrong and proposes a way to fix it. But you can have more when retrieving the internal hints that the profile generate. Better choice to implement the profile, or alternative solutions. As usual, if you see something wrong or to improve in my query, please comment.<\/p>\n
Quizz<\/h3>\n
The answer is left for another post, but if you have an idea, please don’t hesitate to comment.
\nQuestion is: In the above output, I’ve two OPT_ESTIMATE rows for each table. what is the reason for that?<\/p>\n","protected":false},"excerpt":{"rendered":"
By Franck Pachot . People often ask me if I use the SQL Tuning Advisor. The answer is no, at least not through the end of it. But don’t take me wrong. SQL Tuning Advisor is a really nice feature. If you like the CBO then you must love the Tuning Advisor, because it is […]<\/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":[198,59],"tags":[349,96],"type_dbi":[],"class_list":["post-4092","post","type-post","status-publish","format-standard","hentry","category-database-management","category-oracle","tag-optimizer","tag-oracle"],"acf":[],"yoast_head":"\nOracle SQL Profiles: Check what they do before accepting them blindly - dbi Blog<\/title>\n<meta name=\"description\" content=\"My script to get OPT_ESTIMATE hints from a SQL Profile suggested by SQL Tuning Advisor\" \/>\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\/oracle-sql-profiles-check-what-they-do-before-accepting-them-blindly\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Oracle SQL Profiles: Check what they do before accepting them blindly\" \/>\n<meta property=\"og:description\" content=\"My script to get OPT_ESTIMATE hints from a SQL Profile suggested by SQL Tuning Advisor\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/oracle-sql-profiles-check-what-they-do-before-accepting-them-blindly\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2014-11-07T19:28:30+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=\"10 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\/oracle-sql-profiles-check-what-they-do-before-accepting-them-blindly\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-sql-profiles-check-what-they-do-before-accepting-them-blindly\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Oracle SQL Profiles: Check what they do before accepting them blindly\",\"datePublished\":\"2014-11-07T19:28:30+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-sql-profiles-check-what-they-do-before-accepting-them-blindly\/\"},\"wordCount\":974,\"commentCount\":0,\"keywords\":[\"Optimizer\",\"Oracle\"],\"articleSection\":[\"Database management\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-sql-profiles-check-what-they-do-before-accepting-them-blindly\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-sql-profiles-check-what-they-do-before-accepting-them-blindly\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/oracle-sql-profiles-check-what-they-do-before-accepting-them-blindly\/\",\"name\":\"Oracle SQL Profiles: Check what they do before accepting them blindly - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2014-11-07T19:28:30+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"description\":\"My script to get OPT_ESTIMATE hints from a SQL Profile suggested by SQL Tuning Advisor\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-sql-profiles-check-what-they-do-before-accepting-them-blindly\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-sql-profiles-check-what-they-do-before-accepting-them-blindly\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-sql-profiles-check-what-they-do-before-accepting-them-blindly\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Oracle SQL Profiles: Check what they do before accepting them blindly\"}]},{\"@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":"Oracle SQL Profiles: Check what they do before accepting them blindly - dbi Blog","description":"My script to get OPT_ESTIMATE hints from a SQL Profile suggested by SQL Tuning Advisor","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\/oracle-sql-profiles-check-what-they-do-before-accepting-them-blindly\/","og_locale":"en_US","og_type":"article","og_title":"Oracle SQL Profiles: Check what they do before accepting them blindly","og_description":"My script to get OPT_ESTIMATE hints from a SQL Profile suggested by SQL Tuning Advisor","og_url":"https:\/\/www.dbi-services.com\/blog\/oracle-sql-profiles-check-what-they-do-before-accepting-them-blindly\/","og_site_name":"dbi Blog","article_published_time":"2014-11-07T19:28:30+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"10 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-sql-profiles-check-what-they-do-before-accepting-them-blindly\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-sql-profiles-check-what-they-do-before-accepting-them-blindly\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Oracle SQL Profiles: Check what they do before accepting them blindly","datePublished":"2014-11-07T19:28:30+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-sql-profiles-check-what-they-do-before-accepting-them-blindly\/"},"wordCount":974,"commentCount":0,"keywords":["Optimizer","Oracle"],"articleSection":["Database management","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/oracle-sql-profiles-check-what-they-do-before-accepting-them-blindly\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-sql-profiles-check-what-they-do-before-accepting-them-blindly\/","url":"https:\/\/www.dbi-services.com\/blog\/oracle-sql-profiles-check-what-they-do-before-accepting-them-blindly\/","name":"Oracle SQL Profiles: Check what they do before accepting them blindly - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2014-11-07T19:28:30+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"description":"My script to get OPT_ESTIMATE hints from a SQL Profile suggested by SQL Tuning Advisor","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-sql-profiles-check-what-they-do-before-accepting-them-blindly\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/oracle-sql-profiles-check-what-they-do-before-accepting-them-blindly\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-sql-profiles-check-what-they-do-before-accepting-them-blindly\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Oracle SQL Profiles: Check what they do before accepting them blindly"}]},{"@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\/4092","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=4092"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/4092\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=4092"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=4092"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=4092"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=4092"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}