{"id":5120,"date":"2016-12-15T10:50:06","date_gmt":"2016-12-15T09:50:06","guid":{"rendered":"http:\/\/arumel.com\/?p=5120\/"},"modified":"2017-06-29T18:22:09","modified_gmt":"2017-06-29T16:22:09","slug":"o-misterio-da-consulta-diabolica","status":"publish","type":"post","link":"https:\/\/arumel.com\/en\/o-misterio-da-consulta-diabolica\/","title":{"rendered":"O misterio da consulta diab\u00f3lica"},"content":{"rendered":"

Punto de partida<\/strong><\/span><\/h2>\n

Un cliente atopa un problema de rendemento intermitente executando unha mesma consulta. Esta pasa de executarse en 5″ a tardar minutos, facendo imposible o traballo normal en produci\u00f3n. Estamos nunha BD 12.1.0.1. Que ocorre?<\/p>\n

 <\/p>\n

STATISTICS_LEVEL e E-row vs A-row<\/strong><\/span><\/h3>\n

Por defecto o valor do par\u00e1metro STATISTICS_LEVEL \u00e9 TYPICAL, a\u00ednda que Cloud Control nas s\u00faas recomendaci\u00f3ns nos di que \u00e9 recomendable empregar ALL. Cambiar este par\u00e1metro a ALL implica unha sobrecarga da que non te\u00f1o criterio para identificar no momento da revisi\u00f3n. Tam\u00e9n existen riscos de crecemento dalgunhas t\u00e1boas relacionadas con AWR, pero pola contra ofrece algunhas vantaxes a hora de traballar especialmente en ciencia forense.<\/p>\n

STATISTICS_NAME                          DESCRIPTION                                                  ACTIVAT\r\n---------------------------------------- ------------------------------------------------------------ -------\r\nGlobal Cache CPU Statistics              RAC Buffer Cache CPU statistics                              ALL\r\nPlan Execution Statistics                Enables collection of plan execution statistics              ALL\r\nTimed OS Statistics                      Enables gathering of timed operating system statistics       ALL<\/pre>\n
Plan Statistics Name<\/strong> fai a boca auga. Por que? Pois porque permite por exemplo lanzar un DISPLAY_CURSOR coa opci\u00f3n FORMAT=>’ALLSTATS LAST’<\/strong> ou un ‘ALLSTATS ALL’ e comparar as estimaci\u00f3ns do optimizador cos datos reais obtidos, algo que coa configuraci\u00f3n por defecto non temos, vendo s\u00f3 as estimaci\u00f3ns (E):<\/p>\n
-------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\n| Id | Operation                     | Name        | E-Rows |E-Bytes  |E-Temp   |Cost (%CPU)| E-Time    |    OMem |    1Mem |O\/1\/M   | Max-Tmp |\r\n-------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\n| 0 | SELECT STATEMENT               |            |         |         |         | 20562 (100)|          |         |         |        |         |\r\n| 1 | HASH JOIN OUTER                |            |     825 |     128K|         | 20562   (1)| 00:00:01 |     998K|     998K|   1\/0\/0|         |\r\n(...)\r\n-------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/pre>\n
De t\u00f3dolos xeitos, os datos A-row podemos obtelos para unha consulta de xeito manual empregando o hint \/*+ GATHER_PLAN_STATISTICS *\/<\/p>\n
 <\/p>\n
Bug 16571451. 12.1.0.1 non emprega Plan Directives se non subimos o nivel por defecto de optimizer_dynamic_sampling!<\/strong><\/span><\/h3>\n
Interesante bug xa que se trata da versi\u00f3n de BD coa que estamos a traballar. Tam\u00e9n estamos empregando a configuraci\u00f3n por defecto para Dynamic Sampling (optimizer_dynamic_sampling=2), co que Oracle non emprega as SPDs (SQL Plan Directives) que existen na BD!!. O parche correspondente est\u00e1 inclu\u00eddo na versi\u00f3n 12.1.0.2.
\nUn workaround que se pode aplicar \u00e9 <\/u>alter system set optimizer_dynamic_sampling=3<\/b><\/u>, co que o optimizador si empregar\u00e1 SPDs para crear o plan de execuci\u00f3n
\n<\/b><\/p>\n
 <\/p>\n
Xeraci\u00f3n automatica de SPDs. Por que?<\/strong><\/span><\/h3>\n
Vendo a actividade, estanse xerando SPDs para moitas das t\u00e1boas que se empregan nesta consulta. Podemos ver que as combinaci\u00f3ns nas t\u00e1boas da nosa consulta son:<\/p>\n
SELECT distinct d.type, d.state, d.reason\r\n FROM   dba_sql_plan_directives d, dba_sql_plan_dir_objects o\r\n WHERE  d.directive_id=o.directive_id\r\n AND    o.owner = 'OWNER'\r\n AND    o.object_name in ('T1','T2','T3','T4','T5')\r\n \/\r\n \r\n TYPE         STATE           REASON\r\n ---------------- ------------- ------------------------------------\r\n DYNAMIC_SAMPLING NEW           JOIN CARDINALITY MISESTIMATE\r\n DYNAMIC_SAMPLING NEW           SINGLE TABLE CARDINALITY MISESTIMATE\r\n DYNAMIC_SAMPLING MISSING_STATS SINGLE TABLE CARDINALITY MISESTIMATE\r\n DYNAMIC_SAMPLING PERMANENT     GROUP BY CARDINALITY MISESTIMATE\r\n DYNAMIC_SAMPLING NEW           GROUP BY CARDINALITY MISESTIMATE\r\n DYNAMIC_SAMPLING PERMANENT     JOIN CARDINALITY MISESTIMATE\r\n DYNAMIC_SAMPLING PERMANENT     SINGLE TABLE CARDINALITY MISESTIMATE\r\n DYNAMIC_SAMPLING HAS_STATS     GROUP BY CARDINALITY MISESTIMATE\r\n DYNAMIC_SAMPLING HAS_STATS     SINGLE TABLE CARDINALITY MISESTIMATE\r\n DYNAMIC_SAMPLING MISSING_STATS GROUP BY CARDINALITY MISESTIMATE\r\n DYNAMIC_SAMPLING HAS_STATS     JOIN CARDINALITY MISESTIMATE\r\n DYNAMIC_SAMPLING MISSING_STATS JOIN CARDINALITY MISESTIMATE<\/pre>\n
Todas as raz\u00f3ns son erros na estimaci\u00f3n de estat\u00edsticas (\u00f3 mellor non existen mais raz\u00f3ns para elo \ud83d\ude42 ). Se n\u00f3s non estamos a crear estos SPDs, quen o fai? Pois a funcionalidade Automatic Reoptimization<\/strong> das Adaptive Statistics<\/strong>. Esta funcionalidade salta cando se executa por primeira vez unha consulta, e mostrea datos para ver se a cardinalidade se corresponde coa prevista cando:<\/p>\n