{"id":3879,"date":"2013-02-25T17:12:37","date_gmt":"2013-02-25T16:12:37","guid":{"rendered":"http:\/\/blog.capdata.fr\/?p=3879"},"modified":"2019-09-13T14:12:20","modified_gmt":"2019-09-13T13:12:20","slug":"oracle-et-sql-server-les-statistiques","status":"publish","type":"post","link":"https:\/\/blog.capdata.fr\/index.php\/oracle-et-sql-server-les-statistiques\/","title":{"rendered":"Oracle et SQL Server: Les Statistiques"},"content":{"rendered":"<a class=\"synved-social-button synved-social-button-share synved-social-size-24 synved-social-resolution-single synved-social-provider-twitter nolightbox\" data-provider=\"twitter\" target=\"_blank\" rel=\"nofollow\" title=\"Share on Twitter\" href=\"https:\/\/twitter.com\/intent\/tweet?url=https%3A%2F%2Fblog.capdata.fr%2Findex.php%2Fwp-json%2Fwp%2Fv2%2Fposts%2F3879&#038;text=Article%20sur%20le%20blog%20de%20la%20Capdata%20Tech%20Team%20%3A%20\" style=\"font-size: 0px;width:24px;height:24px;margin:0;margin-bottom:5px;margin-right:5px\"><img loading=\"lazy\" decoding=\"async\" alt=\"twitter\" title=\"Share on Twitter\" class=\"synved-share-image synved-social-image synved-social-image-share\" width=\"24\" height=\"24\" style=\"display: inline;width:24px;height:24px;margin: 0;padding: 0;border: none;box-shadow: none\" src=\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/twitter.png\" \/><\/a><a class=\"synved-social-button synved-social-button-share synved-social-size-24 synved-social-resolution-single synved-social-provider-linkedin nolightbox\" data-provider=\"linkedin\" target=\"_blank\" rel=\"nofollow\" title=\"Share on Linkedin\" href=\"https:\/\/www.linkedin.com\/shareArticle?mini=true&#038;url=https%3A%2F%2Fblog.capdata.fr%2Findex.php%2Fwp-json%2Fwp%2Fv2%2Fposts%2F3879&#038;title=Oracle%20et%20SQL%20Server%3A%20Les%20Statistiques\" style=\"font-size: 0px;width:24px;height:24px;margin:0;margin-bottom:5px;margin-right:5px\"><img loading=\"lazy\" decoding=\"async\" alt=\"linkedin\" title=\"Share on Linkedin\" class=\"synved-share-image synved-social-image synved-social-image-share\" width=\"24\" height=\"24\" style=\"display: inline;width:24px;height:24px;margin: 0;padding: 0;border: none;box-shadow: none\" src=\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/linkedin.png\" \/><\/a><a class=\"synved-social-button synved-social-button-share synved-social-size-24 synved-social-resolution-single synved-social-provider-mail nolightbox\" data-provider=\"mail\" rel=\"nofollow\" title=\"Share by email\" href=\"mailto:?subject=Oracle%20et%20SQL%20Server%3A%20Les%20Statistiques&#038;body=Article%20sur%20le%20blog%20de%20la%20Capdata%20Tech%20Team%20%3A%20:%20https%3A%2F%2Fblog.capdata.fr%2Findex.php%2Fwp-json%2Fwp%2Fv2%2Fposts%2F3879\" style=\"font-size: 0px;width:24px;height:24px;margin:0;margin-bottom:5px\"><img loading=\"lazy\" decoding=\"async\" alt=\"mail\" title=\"Share by email\" class=\"synved-share-image synved-social-image synved-social-image-share\" width=\"24\" height=\"24\" style=\"display: inline;width:24px;height:24px;margin: 0;padding: 0;border: none;box-shadow: none\" src=\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/mail.png\" \/><\/a><p>Les statistiques jouent un r\u00f4le crucial dans le choix d&#8217;un plan d&#8217;ex\u00e9cution, puisqu&#8217;ils renseignent le Cost Based Optimizer sur le nombre de lignes et de blocs de donn\u00e9es \u00e0 brasser \u00e0 chaque \u00e9tape d&#8217;un plan d&#8217;ex\u00e9cution.<\/p>\n<p>Les statistiques sur les objets sont globalement les m\u00eames c\u00f4t\u00e9 Oracle et c\u00f4t\u00e9 SQL Server. Le m\u00e9canisme manuel de cr\u00e9ation\/mise \u00e0 jour est quasi similaire, mais leur mise \u00e0 jour automatique diff\u00e8re selon les moteurs.<\/p>\n<p>&nbsp;<\/p>\n<p>Pour illustrer cet article, j&#8217;ai cr\u00e9\u00e9 une petite table:<\/p>\n<pre name=\"code\" class=\"sql\"> ID PRENOM          NOM             CODE_POSTAL\r\n--- --------------- --------------- -----------\r\n  1 Jean            Dupont                75009\r\n  2 Michel          Dumoulin              75012\r\n  3 Paul            Despres               44000\r\n  4 Robert          Durand                75009\r\n  5 Bernard         Duval                 33120\r\n  6 Bertrand        Duval                 75009\r\n  7 Norbert         Duval                 75009\r\n  8 Jules           Duval                 72100\r\n  9 Paul            Duval                 75012\r\n 10 Michel          Duval                 75009<\/pre>\n<p>Une cl\u00e9 primaire a \u00e9t\u00e9 cr\u00e9\u00e9e sur la colonne ID.<\/p>\n<p><strong style=\"font-size: 1.5em;\">Contenu d&#8217;une statistique<\/strong><\/p>\n<h3><em>Partie commune:<\/em><\/h3>\n<p>Une statistique (sur un index, une colonne ou un ensemble de colonnes) contient des informations sur la table:<\/p>\n<ul>\n<li>Nombre de lignes dans la table<\/li>\n<li>Taille moyenne d&#8217;une ligne<\/li>\n<li>Nombre de blocs\/pages de la table<\/li>\n<\/ul>\n<p>Des informations sur la r\u00e9partition des valeurs pour la ou les colonnes:<\/p>\n<ul>\n<li>Nombre de valeurs distinctes, nombre de doublons<\/li>\n<li>Particularit\u00e9 d&#8217;une colonne (unicit\u00e9,\u00a0existence\u00a0de valeurs nulles)<\/li>\n<li>Histogramme de la r\u00e9partition des valeurs.<\/li>\n<\/ul>\n<p style=\"padding-left: 30px;\">L\u00e9g\u00e8res diff\u00e9rences pour cet histogramme:<\/p>\n<p style=\"padding-left: 30px;\"><em>C\u00f4t\u00e9 SQL Server<\/em>, il existe syst\u00e9matiquement et contient au maximum 200 valeurs (bornes d&#8217;intervalles ou &#8220;buckets&#8221;)<\/p>\n<p style=\"padding-left: 30px;\"><em>C\u00f4t\u00e9 Oracle<\/em>, il est optionnel, contient au maximum 254 valeurs et se d\u00e9cline en deux types (Frequency et Height Balanced) selon le nombre de valeurs distinctes dans la ou les colonnes.<\/p>\n<p>Des informations sur la taille de l&#8217;index:<\/p>\n<ul>\n<li>Profondeur (nombre maximum de blocs \u00e0 parcourir entre la racine et une feuille)<\/li>\n<li>Nombre de blocs\/pages feuilles<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<h3><em>Sp\u00e9cificit\u00e9s SQL Server<\/em><\/h3>\n<p>Une statistique peut porter sur un sous ensemble d&#8217;une table. On parle alors de <a href=\"http:\/\/msdn.microsoft.com\/fr-fr\/library\/cc280372.aspx\" target=\"_blank\" rel=\"noopener noreferrer\">statistique filtr\u00e9e<\/a><\/p>\n<p>&nbsp;<\/p>\n<h3><em>Sp\u00e9cificit\u00e9s Oracle<\/em><\/h3>\n<p>Une statistique peut porter sur une fonction appliqu\u00e9e \u00e0 une (ou plusieurs) colonne(s). On parle alors de <a href=\"http:\/\/docs.oracle.com\/cd\/E11882_01\/server.112\/e16638\/stats.htm#BEICJCJH\" target=\"_blank\" rel=\"noopener noreferrer\">statistique sur Expression <\/a><\/p>\n<p>Une statistique sur un index contient une information suppl\u00e9mentaire, le (fameux) \u00a0<a href=\"http:\/\/jonathanlewis.files.wordpress.com\/2011\/05\/le-clustering-factor.pdf\" target=\"_blank\" rel=\"noopener noreferrer\">Clustering Factor<\/a>. Il permet de savoir si les feuilles de l&#8217;index sont ordonn\u00e9es &#8220;de la m\u00eame fa\u00e7on&#8221; que les blocs de la table ou pas.<\/p>\n<p>Une statistique peut porter sur une requ\u00eate, gr\u00e2ce au m\u00e9canisme \u00a0SQL_profile (n\u00e9cessite le Tuning Pack).<\/p>\n<h3><em>Exemples<\/em><\/h3>\n<div>\u00a0Statistiques sur colonnes<\/div>\n<div><strong><em>Oracle<\/em><\/strong><\/div>\n<pre name=\"code\" class=\"sql\">SELECT TS.TABLE_NAME,TS.NUM_ROWS,TS.BLOCKS,TS.AVG_ROW_LEN,CS.COLUMN_NAME, CS.NUM_DISTINCT,CS.DENSITY,CS.HISTOGRAM, TO_CHAR(CS.LAST_ANALYZED,'YYYY-MM-DD HH:MI')\r\nFROM USER_TAB_STATISTICS TS\r\nINNER JOIN USER_TAB_COL_STATISTICS CS ON TS.TABLE_NAME = CS.TABLE_NAME\r\nWHERE TS.TABLE_NAME='TABLE_TEST';\r\n\r\nTABLE_NAME     NUM_ROWS     BLOCKS AVG_ROW_LEN COLUMN_NAME  NUM_DISTINCT    DENSITY HISTOGRAM       TO_CHAR(CS.LAST_\r\n------------ ---------- ---------- ----------- ------------ ------------ ---------- --------------- ----------------\r\nTABLE_TEST           10          1          22 CODE_POSTAL             5        .05 FREQUENCY       2013-02-19 10:57\r\nTABLE_TEST           10          1          22 NOM                     5         .2 NONE            2013-02-19 10:57\r\nTABLE_TEST           10          1          22 PRENOM                  8       .125 NONE            2013-02-19 10:57\r\nTABLE_TEST           10          1          22 ID                     10         .1 NONE            2013-02-19 10:57<\/pre>\n<div><strong><em>SQL Server<\/em><\/strong><\/div>\n<pre name=\"code\" class=\"sql\">select CAST(OBJECT_NAME(S.object_id) as VARCHAR(12)) AS 'TABLE', CAST(S.name AS VARCHAR(12)) AS 'STAT', S.user_created,S. has_filter,CAST( S.filter_definition AS varchar(45)) AS 'FILTRE',I.is_unique, I.is_primary_key, STATS_DATE(S.object_id,S.stats_id) AS 'DATE STAT'\r\nfrom sys.stats S\r\nleft join sys.indexes I ON (I.index_id = S.stats_id AND I.object_id = S.object_id ) \r\nwhere S.object_id=OBJECT_ID('TABLE_TEST')\r\nTABLE        STAT         user_created has_filter FILTRE           is_unique is_primary_key DATE STAT\r\n------------ ------------ ------------ ---------- --------------------------------------------- --------- ------------- -----------------------\r\nTABLE_TEST   PK__TABLE_TE 0            0          NULL              1         1              NULL\r\nTABLE_TEST   STAT_NOM     1            0          NULL              NULL      NULL           2013-02-19 17:45:43.380\r\nTABLE_TEST   STAT_CP      1            0          NULL              NULL      NULL           2013-02-19 17:45:43.380\r\nTABLE_TEST   STAT_FILTREE 1            1          ([prenom]&gt;'Alain' NULL      NULL           2013-02-19 17:45:43.383\r\n                                                   AND [prenom]&lt; 'Luc')<\/pre>\n<div>Statistique sur index<\/div>\n<p><strong><em>Oracle<\/em><\/strong><\/p>\n<pre name=\"code\" class=\"sql\">SELECT TABLE_NAME, INDEX_NAME, BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, CLUSTERING_FACTOR FROM USER_IND_STATISTICS \r\nWHERE TABLE_NAME='TABLE_TEST';\r\nTABLE_NAME   INDEX_NAME       BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR\r\n------------ ------------ ---------- ----------- ------------- -----------------\r\nTABLE_TEST   SYS_C006816           0           1            10                 1<\/pre>\n<p>Histogramme sur la colonne CODE_POSTAL<br \/>\n<strong><em>Oracle<\/em><\/strong><\/p>\n<pre name=\"code\" class=\"sql\">select ENDPOINT_VALUE, ENDPOINT_NUMBER  from user_histograms where table_name='TABLE_TEST' and column_name='CODE_POSTAL';\r\nENDPOINT_VALUE ENDPOINT_NUMBER\r\n-------------- ---------------\r\n         33120               1\r\n         44000               2\r\n         72100               3\r\n         75009               8\r\n         75012              10<\/pre>\n<p>Pour la description de la valeur du Endpoint Number, une &#8220;fr\u00e9quence cumulative&#8221;, je vous invite \u00e0 jeter un coup d&#8217;oeil \u00e0 <a href=\"http:\/\/jonathanlewis.wordpress.com\/2010\/09\/20\/frequency-histograms-2\/\" target=\"_blank\" rel=\"noopener noreferrer\">cet article de Jonathan Lewis<\/a><\/p>\n<p><strong><em>SQL Server<\/em><\/strong><\/p>\n<pre name=\"code\" class=\"sql\">DBCC SHOW_STATISTICS('TABLE_TEST','STAT_CP')\r\nName                                                                                                                             Updated              Rows                 Rows Sampled         Steps  Density       Average key length String Index Filter Expression                                                                                                                                                                                                                                                Unfiltered Rows\r\n-------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------- -------------------- ------ ------------- ------------------ ------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------\r\nSTAT_CP                                                                                                                          Feb 19 2013  5:45PM  10                   10                   5      0             4                  NO           NULL                                                                                                                                                                                                                                                             10\r\n\r\nAll density   Average Length Columns\r\n------------- -------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\n0,2           4              code_postal\r\n\r\nRANGE_HI_KEY RANGE_ROWS    EQ_ROWS       DISTINCT_RANGE_ROWS  AVG_RANGE_ROWS\r\n------------ ------------- ------------- -------------------- --------------\r\n33120        0             1             0                    1\r\n44000        0             1             0                    1\r\n72100        0             1             0                    1\r\n75009        0             5             0                    1\r\n75012        0             2             0                    1<\/pre>\n<h2>Cr\u00e9ation d&#8217;une statistique<\/h2>\n<p>Elle s&#8217;effectue de mani\u00e8re identique :<\/p>\n<p>Une statistique est automatiquement cr\u00e9\u00e9e lors de la cr\u00e9ation d&#8217;un index, sur la m\u00eame combinaison de colonnes.<\/p>\n<p>Elle peut \u00eatre cr\u00e9\u00e9e manuellement sur une ou plusieurs colonnes, en se basant sur toutes les donn\u00e9es de la table (FULLSCAN) ou sur un \u00e9chantillon des donn\u00e9es ( SAMPLE pour SQL Server, ESTIMATE pour Oracle).<\/p>\n<p>Une statistique est cr\u00e9\u00e9e automatiquement par l&#8217;Optimizer lorsque celui-ci estime qu&#8217;elle manque. Ceci est conditionn\u00e9 par un param\u00e8tre (<a href=\"http:\/\/msdn.microsoft.com\/fr-fr\/library\/ms190397.aspx#CreateStatistics\" target=\"_blank\" rel=\"noopener noreferrer\">auto_create_statistics<\/a> pour une base SQL Server, et <a href=\"http:\/\/docs.oracle.com\/cd\/E11882_01\/server.112\/e16638\/stats.htm#i42991\" target=\"_blank\" rel=\"noopener noreferrer\">Dynamic_Sampling<\/a> pour une instance Oracle), qui est positionn\u00e9 par d\u00e9faut.<\/p>\n<h3><em>\u00a0Exemple<\/em><\/h3>\n<p>SQL Server<\/p>\n<pre name=\"code\" class=\"sql\">CREATE STATISTICS STAT_NOM ON TABLE_TEST(nom)\r\nCREATE STATISTICS STAT_CP ON TABLE_TEST(code_postal)\r\nCREATE STATISTICS STAT_FILTREE_PRENOM ON TABLE_TEST(prenom) where prenom &gt;'Alain' AND prenom &lt; 'Luc'\r\nGO<\/pre>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>Oracle<\/p>\n<pre name=\"code\" class=\"sql\">exec dbms_stats.gather_table_stats(sys_context('USERENV', 'CURRENT_SCHEMA'),'TABLE_TEST');\r\nexec dbms_stats.gather_table_stats(sys_context('USERENV', 'CURRENT_SCHEMA'),'TABLE_TEST',METHOD_OPT =&gt;'FOR COLUMNS SIZE 200 code_postal');<\/pre>\n<h3><span style=\"font-size: 1.5em;\">Mise \u00e0 jour d&#8217;une statistique<\/span><\/h3>\n<p>La mise \u00e0 jour manuelle d&#8217;une statistique s&#8217;effectue de mani\u00e8re similaire \u00e0 sa cr\u00e9ation sur les deux moteurs, via la commande <a href=\"http:\/\/msdn.microsoft.com\/fr-fr\/library\/ms187348.aspx\" target=\"_blank\" rel=\"noopener noreferrer\">UPDATE STATISTICS<\/a> pour SQL Server et les proc\u00e9dures <a href=\"http:\/\/docs.oracle.com\/cd\/E11882_01\/server.112\/e16638\/stats.htm#i41448\" target=\"_blank\" rel=\"noopener noreferrer\">DBMS_STATS.GATHER_&#8230;_STATS<\/a> pour Oracle.<\/p>\n<p>Lorsqu&#8217;un index est reconstruit (Rebuild), sa statistique est automatiquement mise \u00e0 jour. Attention, les statistiques ne sont pas automatiquement mises \u00e0 jours lorsqu&#8217;un index est r\u00e9organis\u00e9 partiellement (REORGANIZE avec SQL Server et COALESCE avec Oracle).<\/p>\n<p>Le m\u00e9canisme de mise \u00e0 jour automatique des statistiques effectif par d\u00e9faut , et il est diff\u00e9rent sur les deux moteurs:<\/p>\n<h3><em>Sp\u00e9cificit\u00e9 SQL Server<\/em><\/h3>\n<p>Lorsqu&#8217;une colonne a \u00e9t\u00e9 modifi\u00e9e \u00e0 plus de 20%, toute statistique incluant cette colonne est consid\u00e9r\u00e9e comme\u00a0obsol\u00e8te. Une compilation de requ\u00eate sur une statistique obsol\u00e8te\u00a0entra\u00eene\u00a0la mise \u00e0 jour de cette statistique.<\/p>\n<p>l&#8217;Optimizer disposera donc toujours de statistiques fra\u00eeches, mais le temps d&#8217;ex\u00e9cution d&#8217;une requ\u00eate pourra \u00eatre allong\u00e9 par la mise \u00e0 jour d&#8217;une statistique.<\/p>\n<p>Ce m\u00e9canisme peut \u00eatre d\u00e9sactiv\u00e9 au niveau de la base (option auto_update_statistics \u00e0 passer \u00e0 false) ou d&#8217;une statistique (option NORECOMPUTE des ordres UPDATE STATISTICS et CREATE STATISTICS).<\/p>\n<h3><em>Sp\u00e9cificit\u00e9 Oracle<\/em><\/h3>\n<p>Lorsqu&#8217;une colonne a \u00e9t\u00e9 modifi\u00e9e \u00e0 plus de 10% (seuil modifiable en 11g), toute statistique incluant cette colonne est marqu\u00e9e \u00a0obsol\u00e8te (\u00e9tat Stale), mais l&#8217;Optimizer continue de l&#8217;utiliser. Une t\u00e2che de maintenance quotidienne (planifi\u00e9e par d\u00e9faut \u00e0 22h) met \u00e0 jour toute statistique obsol\u00e8te.<\/p>\n<p>L&#8217;optimizer pourra donc se baser sur des statistiques obsol\u00e8tes, qui seront mises \u00e0 jour au plus tard 24 heures apr\u00e8s avoir \u00e9t\u00e9 d\u00e9clar\u00e9es \u00a0obsol\u00e8tes.<\/p>\n<p>Exemple<\/p>\n<p>Oracle<\/p>\n<pre name=\"code\" class=\"sql\">exec dbms_stats.gather_table_stats(sys_context('USERENV', 'CURRENT_SCHEMA'),'TABLE_TEST');\r\nexec dbms_stats.gather_schema_stats(sys_context('USERENV', 'CURRENT_SCHEMA'),OPTIONS =&gt;'GATHER STALE');<\/pre>\n<p>SQL Server<\/p>\n<pre name=\"code\" class=\"sql\">UPDATE STATISTICS TABLE_TEST(STAT_CP)\r\nUPDATE STATISTICS TABLE_TEST WITH ALL\r\nUPDATE STATISTICS TABLE_TEST(STAT_NOM) WITH NORECOMPUTE<\/pre>\n<h2>Manipulations d&#8217;une Statistique<\/h2>\n<p>Il n&#8217;est pas possible (simplement) de cr\u00e9er de toutes pi\u00e8ces une statistique, ni avec Oracle ni avec SQL Server.<\/p>\n<p>Il est cependant possible d&#8217;extraire une statistique et de la r\u00e9importer, soit sur la m\u00eame base soit dans une autre base. Cette op\u00e9ration se fait via les proc\u00e9dures EXPORT_&lt;TYPE D&#8217;OBJET&gt;_STATS et\u00a0IMPORT_&lt;TYPE D&#8217;OBJET&gt;_STATS sur Oracle et l&#8217;option STATS_STREAM des ordres DBCC SHOW_STATISTICS, CREATE STATISTICS et UPDATE STATISTICS de SQL Server.<\/p>\n<h3><em>\u00a0Sp\u00e9cifit\u00e9 Oracle<\/em><\/h3>\n<p>Par d\u00e9faut, les anciennes statistiques sont pr\u00e9serv\u00e9es pendant 31 jours, cette dur\u00e9e \u00e9tant configurable avec\u00a0DBMS_STATS.ALTER_STATS_HISTORY_RETENTION, et peuvent \u00eatre restaur\u00e9es avec <a href=\"http:\/\/docs.oracle.com\/cd\/E11882_01\/appdev.112\/e25788\/d_stats.htm#i1046561\" target=\"_blank\" rel=\"noopener noreferrer\">DBMS_STATS.RESTORE_TABLE_STATS<\/a><\/p>\n<p>Exemple<\/p>\n<p>SQL Server<\/p>\n<pre name=\"code\" class=\"sql\">DBCC SHOW_STATISTICS('TABLE_TEST','STAT_CP') WITH STATS_STREAM\r\nCREATE STATISTICS STAT_IMPORT ON TABLE_TEST(CODE_POSTAL) WITH STATS_STREAM=0x01000000010000000000000000000000838304...<\/pre>\n<p>Oracle<\/p>\n<pre name=\"code\" class=\"sql\">EXEC dbms_stats.create_stat_table(sys_context('USERENV', 'CURRENT_SCHEMA'), 'STAT_TABLE_TEST');\r\nEXEC dbms_stats.export_table_stats(sys_context('USERENV', 'CURRENT_SCHEMA'),'TABLE_TEST',stattab=&gt;'STAT_TABLE_TEST',statid=&gt;'BACKUP_STAT_13_FEVRIER');\r\nSELECT STATID,VERSION,TO_CHAR(D1,'YYYY-MM-DD HH24:MI') FROM STAT_TABLE_TEST;\r\nEXEC dbms_stats.import_table_stats(sys_context('USERENV', 'CURRENT_SCHEMA'),'TABLE_TEST',stattab=&gt;'STAT_TABLE_TEST',statid=&gt;'BACKUP_STAT_13_FEVRIER');\r\n\r\nEXEC dbms_stats.restore_table_stats(sys_context('USERENV', 'CURRENT_SCHEMA'),'TABLE_TEST', as_of_timestamp=&gt;TO_DATE('2013-02-19 15:00', 'YYYY-MM-DD HH24:MI'));<\/pre>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<a class=\"synved-social-button synved-social-button-share synved-social-size-24 synved-social-resolution-single synved-social-provider-twitter nolightbox\" data-provider=\"twitter\" target=\"_blank\" rel=\"nofollow\" title=\"Share on Twitter\" href=\"https:\/\/twitter.com\/intent\/tweet?url=https%3A%2F%2Fblog.capdata.fr%2Findex.php%2Fwp-json%2Fwp%2Fv2%2Fposts%2F3879&#038;text=Article%20sur%20le%20blog%20de%20la%20Capdata%20Tech%20Team%20%3A%20\" style=\"font-size: 0px;width:24px;height:24px;margin:0;margin-bottom:5px;margin-right:5px\"><img loading=\"lazy\" decoding=\"async\" alt=\"twitter\" title=\"Share on Twitter\" class=\"synved-share-image synved-social-image synved-social-image-share\" width=\"24\" height=\"24\" style=\"display: inline;width:24px;height:24px;margin: 0;padding: 0;border: none;box-shadow: none\" src=\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/twitter.png\" \/><\/a><a class=\"synved-social-button synved-social-button-share synved-social-size-24 synved-social-resolution-single synved-social-provider-linkedin nolightbox\" data-provider=\"linkedin\" target=\"_blank\" rel=\"nofollow\" title=\"Share on Linkedin\" href=\"https:\/\/www.linkedin.com\/shareArticle?mini=true&#038;url=https%3A%2F%2Fblog.capdata.fr%2Findex.php%2Fwp-json%2Fwp%2Fv2%2Fposts%2F3879&#038;title=Oracle%20et%20SQL%20Server%3A%20Les%20Statistiques\" style=\"font-size: 0px;width:24px;height:24px;margin:0;margin-bottom:5px;margin-right:5px\"><img loading=\"lazy\" decoding=\"async\" alt=\"linkedin\" title=\"Share on Linkedin\" class=\"synved-share-image synved-social-image synved-social-image-share\" width=\"24\" height=\"24\" style=\"display: inline;width:24px;height:24px;margin: 0;padding: 0;border: none;box-shadow: none\" src=\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/linkedin.png\" \/><\/a><a class=\"synved-social-button synved-social-button-share synved-social-size-24 synved-social-resolution-single synved-social-provider-mail nolightbox\" data-provider=\"mail\" rel=\"nofollow\" title=\"Share by email\" href=\"mailto:?subject=Oracle%20et%20SQL%20Server%3A%20Les%20Statistiques&#038;body=Article%20sur%20le%20blog%20de%20la%20Capdata%20Tech%20Team%20%3A%20:%20https%3A%2F%2Fblog.capdata.fr%2Findex.php%2Fwp-json%2Fwp%2Fv2%2Fposts%2F3879\" style=\"font-size: 0px;width:24px;height:24px;margin:0;margin-bottom:5px\"><img loading=\"lazy\" decoding=\"async\" alt=\"mail\" title=\"Share by email\" class=\"synved-share-image synved-social-image synved-social-image-share\" width=\"24\" height=\"24\" style=\"display: inline;width:24px;height:24px;margin: 0;padding: 0;border: none;box-shadow: none\" src=\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/mail.png\" \/><\/a>","protected":false},"excerpt":{"rendered":"<p>Les statistiques jouent un r\u00f4le crucial dans le choix d&#8217;un plan d&#8217;ex\u00e9cution, puisqu&#8217;ils renseignent le Cost Based Optimizer sur le nombre de lignes et de blocs de donn\u00e9es \u00e0 brasser \u00e0 chaque \u00e9tape d&#8217;un plan d&#8217;ex\u00e9cution. Les statistiques sur les&hellip; <a href=\"https:\/\/blog.capdata.fr\/index.php\/oracle-et-sql-server-les-statistiques\/\" class=\"more-link\">Continuer la lecture <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":14,"featured_media":7870,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,5],"tags":[229,230,221,61],"class_list":["post-3879","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-oracle","category-sqlserver","tag-cbo","tag-optimizer","tag-performance","tag-statistiques"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v20.8 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Oracle et SQL Server: Les Statistiques - Capdata TECH 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:\/\/blog.capdata.fr\/index.php\/oracle-et-sql-server-les-statistiques\/\" \/>\n<meta property=\"og:locale\" content=\"fr_FR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Oracle et SQL Server: Les Statistiques - Capdata TECH BLOG\" \/>\n<meta property=\"og:description\" content=\"Les statistiques jouent un r\u00f4le crucial dans le choix d&#8217;un plan d&#8217;ex\u00e9cution, puisqu&#8217;ils renseignent le Cost Based Optimizer sur le nombre de lignes et de blocs de donn\u00e9es \u00e0 brasser \u00e0 chaque \u00e9tape d&#8217;un plan d&#8217;ex\u00e9cution. Les statistiques sur les&hellip; Continuer la lecture &rarr;\" \/>\n<meta property=\"og:url\" content=\"https:\/\/blog.capdata.fr\/index.php\/oracle-et-sql-server-les-statistiques\/\" \/>\n<meta property=\"og:site_name\" content=\"Capdata TECH BLOG\" \/>\n<meta property=\"article:published_time\" content=\"2013-02-25T16:12:37+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-09-13T13:12:20+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2013\/02\/tunnel-data.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1024\" \/>\n\t<meta property=\"og:image:height\" content=\"768\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Benjamin VESAN\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"\u00c9crit par\" \/>\n\t<meta name=\"twitter:data1\" content=\"Benjamin VESAN\" \/>\n\t<meta name=\"twitter:label2\" content=\"Dur\u00e9e de lecture estim\u00e9e\" \/>\n\t<meta name=\"twitter:data2\" content=\"8 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/oracle-et-sql-server-les-statistiques\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/oracle-et-sql-server-les-statistiques\/\"},\"author\":{\"name\":\"Benjamin VESAN\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/person\/ae56d1d3d5680d95ccc2c927e44bdc3e\"},\"headline\":\"Oracle et SQL Server: Les Statistiques\",\"datePublished\":\"2013-02-25T16:12:37+00:00\",\"dateModified\":\"2019-09-13T13:12:20+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/oracle-et-sql-server-les-statistiques\/\"},\"wordCount\":935,\"commentCount\":1,\"publisher\":{\"@id\":\"https:\/\/blog.capdata.fr\/#organization\"},\"keywords\":[\"CBO\",\"Optimizer\",\"performance\",\"statistiques\"],\"articleSection\":[\"Oracle\",\"SQL Server\"],\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/blog.capdata.fr\/index.php\/oracle-et-sql-server-les-statistiques\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/oracle-et-sql-server-les-statistiques\/\",\"url\":\"https:\/\/blog.capdata.fr\/index.php\/oracle-et-sql-server-les-statistiques\/\",\"name\":\"Oracle et SQL Server: Les Statistiques - Capdata TECH BLOG\",\"isPartOf\":{\"@id\":\"https:\/\/blog.capdata.fr\/#website\"},\"datePublished\":\"2013-02-25T16:12:37+00:00\",\"dateModified\":\"2019-09-13T13:12:20+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/oracle-et-sql-server-les-statistiques\/#breadcrumb\"},\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/blog.capdata.fr\/index.php\/oracle-et-sql-server-les-statistiques\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/oracle-et-sql-server-les-statistiques\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/blog.capdata.fr\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Oracle et SQL Server: Les Statistiques\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/blog.capdata.fr\/#website\",\"url\":\"https:\/\/blog.capdata.fr\/\",\"name\":\"Capdata TECH BLOG\",\"description\":\"Le blog technique sur les bases de donn\u00e9es de CAP DATA Consulting\",\"publisher\":{\"@id\":\"https:\/\/blog.capdata.fr\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/blog.capdata.fr\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"fr-FR\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/blog.capdata.fr\/#organization\",\"name\":\"Capdata TECH BLOG\",\"url\":\"https:\/\/blog.capdata.fr\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"fr-FR\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp\",\"contentUrl\":\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp\",\"width\":800,\"height\":254,\"caption\":\"Capdata TECH BLOG\"},\"image\":{\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.linkedin.com\/company\/cap-data-consulting\/mycompany\/\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/person\/ae56d1d3d5680d95ccc2c927e44bdc3e\",\"name\":\"Benjamin VESAN\",\"url\":\"https:\/\/blog.capdata.fr\/index.php\/author\/bvesan\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Oracle et SQL Server: Les Statistiques - Capdata TECH 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:\/\/blog.capdata.fr\/index.php\/oracle-et-sql-server-les-statistiques\/","og_locale":"fr_FR","og_type":"article","og_title":"Oracle et SQL Server: Les Statistiques - Capdata TECH BLOG","og_description":"Les statistiques jouent un r\u00f4le crucial dans le choix d&#8217;un plan d&#8217;ex\u00e9cution, puisqu&#8217;ils renseignent le Cost Based Optimizer sur le nombre de lignes et de blocs de donn\u00e9es \u00e0 brasser \u00e0 chaque \u00e9tape d&#8217;un plan d&#8217;ex\u00e9cution. Les statistiques sur les&hellip; Continuer la lecture &rarr;","og_url":"https:\/\/blog.capdata.fr\/index.php\/oracle-et-sql-server-les-statistiques\/","og_site_name":"Capdata TECH BLOG","article_published_time":"2013-02-25T16:12:37+00:00","article_modified_time":"2019-09-13T13:12:20+00:00","og_image":[{"width":1024,"height":768,"url":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2013\/02\/tunnel-data.jpg","type":"image\/jpeg"}],"author":"Benjamin VESAN","twitter_card":"summary_large_image","twitter_misc":{"\u00c9crit par":"Benjamin VESAN","Dur\u00e9e de lecture estim\u00e9e":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/blog.capdata.fr\/index.php\/oracle-et-sql-server-les-statistiques\/#article","isPartOf":{"@id":"https:\/\/blog.capdata.fr\/index.php\/oracle-et-sql-server-les-statistiques\/"},"author":{"name":"Benjamin VESAN","@id":"https:\/\/blog.capdata.fr\/#\/schema\/person\/ae56d1d3d5680d95ccc2c927e44bdc3e"},"headline":"Oracle et SQL Server: Les Statistiques","datePublished":"2013-02-25T16:12:37+00:00","dateModified":"2019-09-13T13:12:20+00:00","mainEntityOfPage":{"@id":"https:\/\/blog.capdata.fr\/index.php\/oracle-et-sql-server-les-statistiques\/"},"wordCount":935,"commentCount":1,"publisher":{"@id":"https:\/\/blog.capdata.fr\/#organization"},"keywords":["CBO","Optimizer","performance","statistiques"],"articleSection":["Oracle","SQL Server"],"inLanguage":"fr-FR","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/blog.capdata.fr\/index.php\/oracle-et-sql-server-les-statistiques\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/blog.capdata.fr\/index.php\/oracle-et-sql-server-les-statistiques\/","url":"https:\/\/blog.capdata.fr\/index.php\/oracle-et-sql-server-les-statistiques\/","name":"Oracle et SQL Server: Les Statistiques - Capdata TECH BLOG","isPartOf":{"@id":"https:\/\/blog.capdata.fr\/#website"},"datePublished":"2013-02-25T16:12:37+00:00","dateModified":"2019-09-13T13:12:20+00:00","breadcrumb":{"@id":"https:\/\/blog.capdata.fr\/index.php\/oracle-et-sql-server-les-statistiques\/#breadcrumb"},"inLanguage":"fr-FR","potentialAction":[{"@type":"ReadAction","target":["https:\/\/blog.capdata.fr\/index.php\/oracle-et-sql-server-les-statistiques\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/blog.capdata.fr\/index.php\/oracle-et-sql-server-les-statistiques\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/blog.capdata.fr\/"},{"@type":"ListItem","position":2,"name":"Oracle et SQL Server: Les Statistiques"}]},{"@type":"WebSite","@id":"https:\/\/blog.capdata.fr\/#website","url":"https:\/\/blog.capdata.fr\/","name":"Capdata TECH BLOG","description":"Le blog technique sur les bases de donn\u00e9es de CAP DATA Consulting","publisher":{"@id":"https:\/\/blog.capdata.fr\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/blog.capdata.fr\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"fr-FR"},{"@type":"Organization","@id":"https:\/\/blog.capdata.fr\/#organization","name":"Capdata TECH BLOG","url":"https:\/\/blog.capdata.fr\/","logo":{"@type":"ImageObject","inLanguage":"fr-FR","@id":"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/","url":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp","contentUrl":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp","width":800,"height":254,"caption":"Capdata TECH BLOG"},"image":{"@id":"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.linkedin.com\/company\/cap-data-consulting\/mycompany\/"]},{"@type":"Person","@id":"https:\/\/blog.capdata.fr\/#\/schema\/person\/ae56d1d3d5680d95ccc2c927e44bdc3e","name":"Benjamin VESAN","url":"https:\/\/blog.capdata.fr\/index.php\/author\/bvesan\/"}]}},"_links":{"self":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/3879","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/users\/14"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/comments?post=3879"}],"version-history":[{"count":136,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/3879\/revisions"}],"predecessor-version":[{"id":7871,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/3879\/revisions\/7871"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/media\/7870"}],"wp:attachment":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/media?parent=3879"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/categories?post=3879"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/tags?post=3879"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}