{"id":9987,"date":"2017-05-08T15:47:00","date_gmt":"2017-05-08T13:47:00","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-optimizer-statistics-advisor\/"},"modified":"2017-05-08T15:47:00","modified_gmt":"2017-05-08T13:47:00","slug":"oracle-12cr2-optimizer-statistics-advisor","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-optimizer-statistics-advisor\/","title":{"rendered":"Oracle 12cR2 : Optimizer Statistics Advisor"},"content":{"rendered":"

The Optimizer Statistics Advisor is a new Advisor in Oracle 12.2.
\nThe goal of this Advisor is to check the way you gather the statistics on your database, and depending on what is found, it will makes some recommendations on how you can improve the statistics gathering strategy in order to provide more efficient statistics to the CBO.
\nThis Advisor is also able to generate remediation scripts to apply the statistics gathering “best practices”.
\n\"adv\"<\/a>
\nThe recommendations are based on 23 predefined rules :
\n
\nSQL> select rule_id, name, rule_type, description from v$stats_advisor_rules;
\n<\/br>
\nRULE_ID NAME RULE_TYPE DESCRIPTION
\n---------- ----------------------------------- --------- -------------------------------------------------------------------------------------
\n0 SYSTEM
\n1 UseAutoJob SYSTEM Use Auto Job for Statistics Collection
\n2 CompleteAutoJob SYSTEM Auto Statistics Gather Job should complete successfully
\n3 MaintainStatsHistory SYSTEM Maintain Statistics History
\n4 UseConcurrent SYSTEM Use Concurrent preference for Statistics Collection
\n5 UseDefaultPreference SYSTEM Use Default Preference for Stats Collection
\n6 TurnOnSQLPlanDirective SYSTEM SQL Plan Directives should not be disabled
\n7 AvoidSetProcedures OPERATION Avoid Set Statistics Procedures
\n8 UseDefaultParams OPERATION Use Default Parameters in Statistics Collection Procedures
\n9 UseGatherSchemaStats OPERATION Use gather_schema_stats procedure
\n10 AvoidInefficientStatsOprSeq OPERATION Avoid inefficient statistics operation sequences
\n11 AvoidUnnecessaryStatsCollection OBJECT Avoid unnecessary statistics collection
\n12 AvoidStaleStats OBJECT Avoid objects with stale or no statistics
\n13 GatherStatsAfterBulkDML OBJECT Do not gather statistics right before bulk DML
\n14 LockVolatileTable OBJECT Statistics for objects with volatile data should be locked
\n15 UnlockNonVolatileTable OBJECT Statistics for objects with non-volatile should not be locked
\n16 MaintainStatsConsistency OBJECT Statistics of dependent objects should be consistent
\n17 AvoidDropRecreate OBJECT Avoid drop and recreate object seqauences
\n18 UseIncremental OBJECT Statistics should be maintained incrementally when it is beneficial
\n19 NotUseIncremental OBJECT Statistics should not be maintained incrementally when it is not beneficial
\n20 AvoidOutOfRange OBJECT Avoid Out of Range Histogram endpoints
\n21 UseAutoDegree OBJECT Use Auto Degree for statistics collection
\n22 UseDefaultObjectPreference OBJECT Use Default Object Preference for statistics collection
\n23 AvoidAnalyzeTable OBJECT Avoid using analyze table commands for statistics collection
\n<\/br>
\n24 rows selected.
\n<\/br>
\nSQL>
\n<\/code><\/p>\n

You can have a look at this blog<\/a> if you want a little bit more informations about these rules.
\nIf you want to exclude some rules or some database objects of the Advisor’s recommandation, you can define multiple filters. (I will do that below.)<\/p>\n

Well, let’s see how to use the Advisor. The first step is to create a task which will run it :
\n
\nDECLARE
\n tname VARCHAR2(32767);
\n ret VARCHAR2(32767);
\nBEGIN
\n tname := 'stat_advisor_1';
\n ret := DBMS_STATS.CREATE_ADVISOR_TASK(tname);
\nEND;
\n\/
\n<\/code><\/p>\n

The task is created :
\n
\nSQL> select task_name, advisor_name, created, status from dba_advisor_tasks where advisor_name = 'Statistics Advisor';
\n<\/br>
\nTASK_NAME ADVISOR_NAME CREATED STATUS
\n------------------------------ ------------------------------ ------------------- -----------
\nSTAT_ADVISOR_1 Statistics Advisor 04.05.2017-11:19:25 INITIAL
\n<\/br>
\nSQL>
\n<\/code><\/p>\n

Now, I want to define some filters.
\nThe first one will disable the Advisor for all objects, the 2nd will enable it only on a specific table and the 3th and 4th will exclude two rules :
\n
\nDECLARE
\n filter1 CLOB;
\n filter2 CLOB;
\n filter3 CLOB;
\n filter4 CLOB;
\nBEGIN
\nfilter1 := DBMS_STATS.CONFIGURE_ADVISOR_OBJ_FILTER(
\n task_name => 'STAT_ADVISOR_1',
\n stats_adv_opr_type => 'EXECUTE',
\n rule_name => NULL,
\n ownname => NULL,
\n tabname => NULL,
\n action => 'DISABLE' );
\n<\/br>
\nfilter2 := DBMS_STATS.CONFIGURE_ADVISOR_OBJ_FILTER(
\n task_name => 'STAT_ADVISOR_1',
\n stats_adv_opr_type => 'EXECUTE',
\n rule_name => NULL,
\n ownname => 'JOC',
\n tabname => 'T2',
\n action => 'ENABLE' );
\n<\/br>
\nfilter3 := DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER(
\n task_name => 'STAT_ADVISOR_1',
\n stats_adv_opr_type => 'EXECUTE',
\n rule_name => 'AvoidDropRecreate',
\n action => 'DISABLE' );
\n<\/br>
\nfilter4 := DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER(
\n task_name => 'STAT_ADVISOR_1',
\n stats_adv_opr_type => 'EXECUTE',
\n rule_name => 'UseGatherSchemaStats',
\n action => 'DISABLE' );
\nEND;
\n\/
\n<\/code><\/p>\n

All is ready, let’s run the task…
\n
\nDECLARE
\n tname VARCHAR2(32767);
\n ret VARCHAR2(32767);
\nBEGIN
\n tname := 'stat_advisor_1';
\n ret := DBMS_STATS.EXECUTE_ADVISOR_TASK(tname);
\nEND;
\n\/
\n<\/code><\/p>\n

…and generate the report :
\n
\nSQL> select dbms_stats.report_advisor_task('stat_advisor_1',null,'text','all','all') as report from dual;
\nGENERAL INFORMATION
\n-------------------------------------------------------------------------------
\nTask Name : STAT_ADVISOR_1
\nExecution Name : EXEC_2172
\nCreated : 05-04-17 11:34:51
\nLast Modified : 05-04-17 11:35:10
\n-------------------------------------------------------------------------------
\nSUMMARY
\n-------------------------------------------------------------------------------
\nFor execution EXEC_2172 of task STAT_ADVISOR_1, the Statistics Advisor has no
\nfindings.<\/span>
\n-------------------------------------------------------------------------------
\nSQL>
\n<\/code>
\nCool ! Nothing to report regarding statistics gathering on my the table JOC.T2 (see filter2 above).
\nBut how does the Advisor reacts when I run it after having deleted the statistics on this table ?
\nSQL> exec dbms_stats.delete_table_stats(ownname=>'JOC',tabname=>'T2');
\n<\/br>
\nPL\/SQL procedure successfully completed.
\n<\/br>
\nSQL> DECLARE
\n2 tname VARCHAR2(32767);
\n3 ret VARCHAR2(32767);
\n4 BEGIN
\n5 tname := 'stat_advisor_1';
\n6 ret := DBMS_STATS.EXECUTE_ADVISOR_TASK(tname);
\n7 END;
\n8 \/
\n<\/br>
\nPL\/SQL procedure successfully completed.
\n<\/br>
\nSQL> select dbms_stats.report_advisor_task('stat_advisor_1',null,'text','all','all') as report from dual;
\nGENERAL INFORMATION
\n-------------------------------------------------------------------------------
\nTask Name : STAT_ADVISOR_1
\nExecution Name : EXEC_2182
\nCreated : 05-04-17 11:34:51
\nLast Modified : 05-04-17 11:44:22
\n-------------------------------------------------------------------------------
\nSUMMARY
\n-------------------------------------------------------------------------------
\nFor execution EXEC_2182 of task STAT_ADVISOR_1, the Statistics Advisor has 1
\nfinding(s). The findings are related to the following rules: AVOIDSTALESTATS.
\nPlease refer to the finding section for detailed information.<\/span>
\n-------------------------------------------------------------------------------
\nFINDINGS
\n-------------------------------------------------------------------------------
\nRule Name: AvoidStaleStats<\/span>
\nRule Description: Avoid objects with stale or no statistics<\/span>
\nFinding: There are 1 object(s) with no statistics.
\nSchema:
\nJOC<\/span>
\nObjects:
\nT2<\/span>
\n<\/br>
\nRecommendation: Gather Statistics on those objects with no statistics.<\/span>
\nExample:
\n-- Gathering statistics for tables with stale or no statistics in schema, SH:
\nexec dbms_stats.gather_schema_stats('SH', options => 'GATHER AUTO')
\nRationale: Stale statistics or no statistics will result in bad plans.
\n-------------------------------------------------------------------------------
\n<\/code><\/p>\n

It looks to work well. The Advisor detected that there is no stats on the table, and a rule were triggered.
\nAnd what about the remediation scripts ? Firstly, we have to generate them :
\n
\nVARIABLE script CLOB
\nDECLARE
\n tname VARCHAR2(32767);
\nBEGIN
\n tname := 'stat_advisor_1';
\n :script := DBMS_STATS.SCRIPT_ADVISOR_TASK(tname);
\nEND;
\n\/
\n<\/br>
\nPL\/SQL procedure successfully completed.
\n<\/code><\/p>\n

And then display them :
\n
\nset linesize 3000
\nset long 500000
\nset pagesize 0
\nset longchunksize 100000
\nset serveroutput on
\n<\/br>
\nDECLARE
\n v_len NUMBER(10);
\n v_offset NUMBER(10) :=1;
\n v_amount NUMBER(10) :=10000;
\nBEGIN
\n v_len := DBMS_LOB.getlength(:script);
\n WHILE (v_offset < v_len)
\n LOOP
\n DBMS_OUTPUT.PUT_LINE(DBMS_LOB.SUBSTR(:script,v_amount,v_offset));
\n v_offset := v_offset + v_amount;
\n END LOOP;
\nEND;
\n13 \/
\n-- Script generated for the recommendations from execution EXEC_2182
\n-- in the statistics advisor task STAT_ADVISOR_1
\n-- Script version 12.2
\n-- No scripts will be provided for the rule USEAUTOJOB.Please check the report for more details.
\n-- No scripts will be provided for the rule COMPLETEAUTOJOB.Please check the report for more details.
\n-- No scripts will be provided for the rule MAINTAINSTATSHISTORY.Please check the report for more details.
\n-- No scripts will be provided for the rule TURNONSQLPLANDIRECTIVE.Please check the report for more details.
\n-- No scripts will be provided for the rule AVOIDSETPROCEDURES.Please check the report for more details.
\n-- No scripts will be provided for the rule USEDEFAULTPARAMS.Please check the report for more details.
\n-- No scripts will be provided for the rule USEGATHERSCHEMASTATS.Please check the report for more details.
\n-- No scripts will be provided for the rule AVOIDINEFFICIENTSTATSOPRSEQ.Please check the report for more details.
\n-- No scripts will be provided for the rule AVOIDUNNECESSARYSTATSCOLLECTION.Please check the report for more details.
\n-- No scripts will be provided for the rule GATHERSTATSAFTERBULKDML.Please check the report for more details.
\n-- No scripts will be provided for the rule AVOIDDROPRECREATE.Please check the report for more details.
\n-- No scripts will be provided for the rule AVOIDOUTOFRANGE.Please check the report for more details.
\n-- No scripts will be provided for the rule AVOIDANALYZETABLE.Please check the report for more details.
\n-- No scripts will be provided for the rule USEAUTOJOB.Please check the report for more details.
\n-- No scripts will be provided for the rule COMPLETEAUTOJOB.Please check the report for more details.
\n-- No scripts will be provided for the rule MAINTAINSTATSHISTORY.Please check the report for more details.
\n-- No scripts will be provided for the rule TURNONSQLPLANDIRECTIVE.Please check the report for more details.
\n-- No scripts will be provided for the rule AVOIDSETPROCEDURES.Please check the report for more details.
\n-- No scripts will be provided for the rule USEDEFAULTPARAMS.Please check the report for more details.
\n-- No scripts will be provided for the rule USEGATHERSCHEMASTATS.Please check the report for more details.
\n-- No scripts will be provided for the rule AVOIDINEFFICIENTSTATSOPRSEQ.Please check the report for more details.
\n-- No scripts will be provided for the rule AVOIDUNNECESSARYSTATSCOLLECTION.Please check the report for more details.
\n-- No scripts will be provided for the rule GATHERSTATSAFTERBULKDML.Please check the report for more details.
\n-- No scripts will be provided for the rule AVOIDDROPRECREATE.Please check the report for more details.
\n-- No scripts will be provided for the rule AVOIDOUTOFRANGE.Please check the report for more details.
\n-- No scripts will be provided for the rule AVOIDANALYZETABLE.Please check the report for more details.
\n-- Scripts for rule USECONCURRENT
\n-- Rule Description: Use Concurrent preference for Statistics Collection
\n-- No scripts will be provided for the rule USEAUTOJOB.Please check the report for more details.
\n-- No scripts will be provided for the rule COMPLETEAUTOJOB.Please check the report for more details.
\n-- No scripts will be provided for the rule MAINTAINSTATSHISTORY.Please check the report for more details.
\n-- No scripts will be provided for the rule TURNONSQLPLANDIRECTIVE.Please check the report for more details.
\n-- No scripts will be provided for the rule AVOIDSETPROCEDURES.Please check the report for more details.
\n-- No scripts will be provided for the rule USEDEFAULTPARAMS.Please check the report for more details.
\n-- No scripts will be provided for the rule USEGATHERSCHEMASTATS.Please check the report for more details.
\n-- No scripts will be provided for the rule AVOIDINEFFICIENTSTATSOPRSEQ.Please check the report for more details.
\n-- No scripts will be provided for the rule AVOIDUNNECESSARYSTATSCOLLECTION.Please check the report for more details.
\n-- No scripts will be provided for the rule GATHERSTATSAFTERBULKDML.Please check the report for more details.
\n-- No scripts will be provided for the rule AVOIDDROPRECREATE.Please check the report for more details.
\n-- No scripts will be provided for the rule AVOIDOUTOFRANGE.Please check the report for more details.
\n-- No scripts will be provided for the rule AVOIDANALYZETABLE.Please check the report for more details.
\n-- Scripts for rule USEDEFAULTPREFERENCE
\n-- Rule Description: Use Default Preference for Stats Collection
\n-- Set global preferenes to default values.
\n-- Scripts for rule USEDEFAULTOBJECTPREFERENCE
\n-- Rule Description: Use Default Object Preference for statistics collection
\n-- Setting object-level preferences to default values
\n-- setting CASCADE to default value for object level preference
\n-- setting ESTIMATE_PERCENT to default value for object level preference
\n-- setting METHOD_OPT to default value for object level preference
\n-- setting GRANULARITY to default value for object level preference
\n-- setting NO_INVALIDATE to default value for object level preference
\n-- Scripts for rule USEINCREMENTAL
\n-- Rule Description: Statistics should be maintained incrementally when it is beneficial
\n-- Turn on the incremental option for those objects for which using incremental is helpful.
\n-- Scripts for rule UNLOCKNONVOLATILETABLE
\n-- Rule Description: Statistics for objects with non-volatile should not be locked
\n-- Unlock statistics for objects that are not volatile.
\n-- Scripts for rule LOCKVOLATILETABLE
\n-- Rule Description: Statistics for objects with volatile data should be locked
\n-- Lock statistics for volatile objects.
\n-- Scripts for rule NOTUSEINCREMENTAL
\n-- Rule Description: Statistics should not be maintained incrementally when it is not beneficial
\n-- Turn off incremental option for those objects for which using incremental is not helpful.
\n-- Scripts for rule USEAUTODEGREE
\n-- Rule Description: Use Auto Degree for statistics collection
\n-- Turn on auto degree for those objects for which using auto degree is helpful.
\n-- Scripts for rule AVOIDSTALESTATS
\n-- Rule Description: Avoid objects with stale or no statistics
\n-- Gather statistics for those objcts that are missing or have no statistics.
\n-- Scripts for rule MAINTAINSTATSCONSISTENCY
\n-- Rule Description: Statistics of dependent objects should be consistent
\n-- Gather statistics for those objcts that are missing or have no statistics.
\ndeclare
\nobj_filter_list dbms_stats.ObjectTab;
\nobj_filter dbms_stats.ObjectElem;
\nobj_cnt number := 0;
\nbegin
\nobj_filter_list := dbms_stats.ObjectTab();
\nobj_filter.ownname := 'JOC';
\nobj_filter.objtype := 'TABLE';
\nobj_filter.objname := 'T2';
\nobj_filter_list.extend();
\nobj_cnt := obj_cnt + 1;
\nobj_filter_list(obj_cnt) := obj_filter;
\ndbms_stats.gather_database_stats(
\nobj_filter_list=>obj_filter_list);
\nend;
\n\/<\/span>
\nPL\/SQL procedure successfully completed.
\nSQL>
\n<\/code>
\nIt was a very simple demo, but as you can see above, the Advisor provides a small script to adjust what is wrong or what is missing concerning the statistics of the table.<\/p>\n

Conclusion<\/u> :
\nOnce you have upgraded your database to Oracle 12.2, don’t hesitate to set up the new Statistics Advisor. It is easy to deploy and can be fully personalized depending on what you want to check (which objects ? which rules ?). Moreover, it has been developped by the same team who develops and maintains the CBO. Therefore, they know which statistics the Optimizer needs !<\/p>\n","protected":false},"excerpt":{"rendered":"

The Optimizer Statistics Advisor is a new Advisor in Oracle 12.2. The goal of this Advisor is to check the way you gather the statistics on your database, and depending on what is found, it will makes some recommendations on how you can improve the statistics gathering strategy in order to provide more efficient statistics […]<\/p>\n","protected":false},"author":30,"featured_media":9988,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,198,368],"tags":[656,1081,736,96,255,1066,1082],"type_dbi":[],"class_list":["post-9987","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database-administration-monitoring","category-database-management","category-development-performance","tag-12-2","tag-advisor","tag-cbo","tag-oracle","tag-statistics","tag-statistics-advisor","tag-statistics-gathering"],"acf":[],"yoast_head":"\nOracle 12cR2 : Optimizer Statistics Advisor - 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\/oracle-12cr2-optimizer-statistics-advisor\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Oracle 12cR2 : Optimizer Statistics Advisor\" \/>\n<meta property=\"og:description\" content=\"The Optimizer Statistics Advisor is a new Advisor in Oracle 12.2. The goal of this Advisor is to check the way you gather the statistics on your database, and depending on what is found, it will makes some recommendations on how you can improve the statistics gathering strategy in order to provide more efficient statistics […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-optimizer-statistics-advisor\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2017-05-08T13:47:00+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/adv.png\" \/>\n\t<meta property=\"og:image:width\" content=\"636\" \/>\n\t<meta property=\"og:image:height\" content=\"161\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Jo\u00ebl Cattin\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Jo\u00ebl Cattin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"11 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-12cr2-optimizer-statistics-advisor\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-optimizer-statistics-advisor\/\"},\"author\":{\"name\":\"Jo\u00ebl Cattin\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/2c774f00321ee734515f0c2f6a96b780\"},\"headline\":\"Oracle 12cR2 : Optimizer Statistics Advisor\",\"datePublished\":\"2017-05-08T13:47:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-optimizer-statistics-advisor\/\"},\"wordCount\":370,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-optimizer-statistics-advisor\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/adv.png\",\"keywords\":[\"12.2\",\"advisor\",\"CBO\",\"Oracle\",\"Statistics\",\"Statistics Advisor\",\"statistics gathering\"],\"articleSection\":[\"Database Administration & Monitoring\",\"Database management\",\"Development & Performance\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-optimizer-statistics-advisor\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-optimizer-statistics-advisor\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-optimizer-statistics-advisor\/\",\"name\":\"Oracle 12cR2 : Optimizer Statistics Advisor - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-optimizer-statistics-advisor\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-optimizer-statistics-advisor\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/adv.png\",\"datePublished\":\"2017-05-08T13:47:00+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/2c774f00321ee734515f0c2f6a96b780\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-optimizer-statistics-advisor\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-optimizer-statistics-advisor\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-optimizer-statistics-advisor\/#primaryimage\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/adv.png\",\"contentUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/adv.png\",\"width\":636,\"height\":161},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-optimizer-statistics-advisor\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Oracle 12cR2 : Optimizer Statistics Advisor\"}]},{\"@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\/2c774f00321ee734515f0c2f6a96b780\",\"name\":\"Jo\u00ebl Cattin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/a4271811924694263d4de5a469f8bd4a90b14d3d90e6ad819b9e2e5ac035a2dc?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/a4271811924694263d4de5a469f8bd4a90b14d3d90e6ad819b9e2e5ac035a2dc?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/a4271811924694263d4de5a469f8bd4a90b14d3d90e6ad819b9e2e5ac035a2dc?s=96&d=mm&r=g\",\"caption\":\"Jo\u00ebl Cattin\"},\"description\":\"Jo\u00ebl Cattin has more than three years of experience in databases management. He is specialized in Oracle solutions such as Data Guard and RMAN and has a good background knowledge of Oracle Database Appliance (ODA), Real Application Cluster (RAC) and applications development on APEX. Jo\u00ebl Cattin\u2019s experience includes other RDBMS, such as PostgreSQL and MySQL. He is Oracle Database 12c Administrator Certified Professional, EDB Postgres Advanced Server 9.5 Certified Professional, RedHat Certified System Administrator and ITILv3 Foundation for Service Management Certified. Jo\u00ebl Cattin holds a degree from the \u00c9cole Sup\u00e9rieure d\u2019Informatique de Gestion (ESIG) in Del\u00e9mont and a Federal Certificate of Proficiency in Computer Science (Certificat f\u00e9d\u00e9ral de Capacit\u00e9 \u2013 CFC).\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/joel-cattin\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Oracle 12cR2 : Optimizer Statistics Advisor - 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\/oracle-12cr2-optimizer-statistics-advisor\/","og_locale":"en_US","og_type":"article","og_title":"Oracle 12cR2 : Optimizer Statistics Advisor","og_description":"The Optimizer Statistics Advisor is a new Advisor in Oracle 12.2. The goal of this Advisor is to check the way you gather the statistics on your database, and depending on what is found, it will makes some recommendations on how you can improve the statistics gathering strategy in order to provide more efficient statistics […]","og_url":"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-optimizer-statistics-advisor\/","og_site_name":"dbi Blog","article_published_time":"2017-05-08T13:47:00+00:00","og_image":[{"width":636,"height":161,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/adv.png","type":"image\/png"}],"author":"Jo\u00ebl Cattin","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Jo\u00ebl Cattin","Est. reading time":"11 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-optimizer-statistics-advisor\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-optimizer-statistics-advisor\/"},"author":{"name":"Jo\u00ebl Cattin","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/2c774f00321ee734515f0c2f6a96b780"},"headline":"Oracle 12cR2 : Optimizer Statistics Advisor","datePublished":"2017-05-08T13:47:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-optimizer-statistics-advisor\/"},"wordCount":370,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-optimizer-statistics-advisor\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/adv.png","keywords":["12.2","advisor","CBO","Oracle","Statistics","Statistics Advisor","statistics gathering"],"articleSection":["Database Administration & Monitoring","Database management","Development & Performance"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-optimizer-statistics-advisor\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-optimizer-statistics-advisor\/","url":"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-optimizer-statistics-advisor\/","name":"Oracle 12cR2 : Optimizer Statistics Advisor - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-optimizer-statistics-advisor\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-optimizer-statistics-advisor\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/adv.png","datePublished":"2017-05-08T13:47:00+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/2c774f00321ee734515f0c2f6a96b780"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-optimizer-statistics-advisor\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-optimizer-statistics-advisor\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-optimizer-statistics-advisor\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/adv.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/adv.png","width":636,"height":161},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-optimizer-statistics-advisor\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Oracle 12cR2 : Optimizer Statistics Advisor"}]},{"@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\/2c774f00321ee734515f0c2f6a96b780","name":"Jo\u00ebl Cattin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/a4271811924694263d4de5a469f8bd4a90b14d3d90e6ad819b9e2e5ac035a2dc?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/a4271811924694263d4de5a469f8bd4a90b14d3d90e6ad819b9e2e5ac035a2dc?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/a4271811924694263d4de5a469f8bd4a90b14d3d90e6ad819b9e2e5ac035a2dc?s=96&d=mm&r=g","caption":"Jo\u00ebl Cattin"},"description":"Jo\u00ebl Cattin has more than three years of experience in databases management. He is specialized in Oracle solutions such as Data Guard and RMAN and has a good background knowledge of Oracle Database Appliance (ODA), Real Application Cluster (RAC) and applications development on APEX. Jo\u00ebl Cattin\u2019s experience includes other RDBMS, such as PostgreSQL and MySQL. He is Oracle Database 12c Administrator Certified Professional, EDB Postgres Advanced Server 9.5 Certified Professional, RedHat Certified System Administrator and ITILv3 Foundation for Service Management Certified. Jo\u00ebl Cattin holds a degree from the \u00c9cole Sup\u00e9rieure d\u2019Informatique de Gestion (ESIG) in Del\u00e9mont and a Federal Certificate of Proficiency in Computer Science (Certificat f\u00e9d\u00e9ral de Capacit\u00e9 \u2013 CFC).","url":"https:\/\/www.dbi-services.com\/blog\/author\/joel-cattin\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/9987","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\/30"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=9987"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/9987\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/9988"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=9987"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=9987"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=9987"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=9987"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}