Punto de partida

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ón. Estamos nunha BD 12.1.0.1. Que ocorre?

 

STATISTICS_LEVEL e E-row vs A-row

Por defecto o valor do parámetro STATISTICS_LEVEL é TYPICAL, aínda que Cloud Control nas súas recomendacións nos di que é recomendable empregar ALL. Cambiar este parámetro a ALL implica unha sobrecarga da que non teño criterio para identificar no momento da revisión. Tamén existen riscos de crecemento dalgunhas táboas relacionadas con AWR, pero pola contra ofrece algunhas vantaxes a hora de traballar especialmente en ciencia forense.

STATISTICS_NAME                          DESCRIPTION                                                  ACTIVAT
---------------------------------------- ------------------------------------------------------------ -------
Global Cache CPU Statistics              RAC Buffer Cache CPU statistics                              ALL
Plan Execution Statistics                Enables collection of plan execution statistics              ALL
Timed OS Statistics                      Enables gathering of timed operating system statistics       ALL

Plan Statistics Name fai a boca auga. Por que? Pois porque permite por exemplo lanzar un DISPLAY_CURSOR coa opción FORMAT=>’ALLSTATS LAST’ ou un ‘ALLSTATS ALL’ e comparar as estimacións do optimizador cos datos reais obtidos, algo que coa configuración por defecto non temos, vendo só as estimacións (E):

-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                     | Name        | E-Rows |E-Bytes  |E-Temp   |Cost (%CPU)| E-Time    |    OMem |    1Mem |O/1/M   | Max-Tmp |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT               |            |         |         |         | 20562 (100)|          |         |         |        |         |
| 1 | HASH JOIN OUTER                |            |     825 |     128K|         | 20562   (1)| 00:00:01 |     998K|     998K|   1/0/0|         |
(...)
-------------------------------------------------------------------------------------------------------------------------------------------------------------------

De tódolos xeitos, os datos A-row podemos obtelos para unha consulta de xeito manual empregando o hint /*+ GATHER_PLAN_STATISTICS */

 

Bug 16571451. 12.1.0.1 non emprega Plan Directives se non subimos o nivel por defecto de optimizer_dynamic_sampling!

Interesante bug xa que se trata da versión de BD coa que estamos a traballar. Tamén estamos empregando a configuración 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á incluído na versión 12.1.0.2.
Un workaround que se pode aplicar é alter system set optimizer_dynamic_sampling=3, co que o optimizador si empregará SPDs para crear o plan de execución

 

Xeración automatica de SPDs. Por que?

Vendo a actividade, estanse xerando SPDs para moitas das táboas que se empregan nesta consulta. Podemos ver que as combinacións nas táboas da nosa consulta son:

SELECT distinct d.type, d.state, d.reason
 FROM   dba_sql_plan_directives d, dba_sql_plan_dir_objects o
 WHERE  d.directive_id=o.directive_id
 AND    o.owner = 'OWNER'
 AND    o.object_name in ('T1','T2','T3','T4','T5')
 /
 
 TYPE         STATE           REASON
 ---------------- ------------- ------------------------------------
 DYNAMIC_SAMPLING NEW           JOIN CARDINALITY MISESTIMATE
 DYNAMIC_SAMPLING NEW           SINGLE TABLE CARDINALITY MISESTIMATE
 DYNAMIC_SAMPLING MISSING_STATS SINGLE TABLE CARDINALITY MISESTIMATE
 DYNAMIC_SAMPLING PERMANENT     GROUP BY CARDINALITY MISESTIMATE
 DYNAMIC_SAMPLING NEW           GROUP BY CARDINALITY MISESTIMATE
 DYNAMIC_SAMPLING PERMANENT     JOIN CARDINALITY MISESTIMATE
 DYNAMIC_SAMPLING PERMANENT     SINGLE TABLE CARDINALITY MISESTIMATE
 DYNAMIC_SAMPLING HAS_STATS     GROUP BY CARDINALITY MISESTIMATE
 DYNAMIC_SAMPLING HAS_STATS     SINGLE TABLE CARDINALITY MISESTIMATE
 DYNAMIC_SAMPLING MISSING_STATS GROUP BY CARDINALITY MISESTIMATE
 DYNAMIC_SAMPLING HAS_STATS     JOIN CARDINALITY MISESTIMATE
 DYNAMIC_SAMPLING MISSING_STATS JOIN CARDINALITY MISESTIMATE

Todas as razóns son erros na estimación de estatísticas (ó mellor non existen mais razóns para elo 🙂 ). Se nós non estamos a crear estos SPDs, quen o fai? Pois a funcionalidade Automatic Reoptimization das Adaptive Statistics. Esta funcionalidade salta cando se executa por primeira vez unha consulta, e mostrea datos para ver se a cardinalidade se corresponde coa prevista cando:

  • Hai táboas sen estatísticas.
  • Unha táboa contén múltiples predicados conxuntivos ou disxuntivos.
  • Existen predicados con operadores complexos que impiden ó optimizador estimar correctamente.

Cando se cumpre algunha destas premisas, entra en xogo a funcionalidade Statistics Feedback (SF) para verificar se todo vai OK, e, que fai SF?

select plan_table_output from table(dbms_xplan.display_cursor('81uujczagk9bf',null,'ALLSTATS ALL'));

(...)

Note
-----
   - statistics feedback used for this statement
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

Cuando se cumple alguna de estas premisas, salta la funcionalidad de Statistics Feedback para verificar si todo va OK, y ¿qué hace SF? Pois… sorpresa, crear unha directiva!

At the end of execution, the optimizer compares its initial cardinality estimates to the actual number of rows returned by each operation in the plan during execution. If estimates differ significantly from actual cardinalities, then the optimizer stores the correct estimates for subsequent use. The optimizer also creates a SQL plan directive so that other SQL statements can benefit from the information obtained during this initial execution.

Con esta información, é probable que polo motivo 2 da anterior lista, se estén a xerar as SPDs. Hai que ter en conta outro factor que coido é moi importante, as SPDs están asociadas a un conxunto de táboas e son creadas e empregadas por diferentes consultas, polo que unha SPD que sexa empregada por unha consulta non necesariamente se terá xerado nunha anterior execución da mesma consulta. Pode ter sido outra totalmente diferente, o que axudará a cimentar a toría de que é SF o que as está a xerar.

Ben, tamén axuda ver no plan de execución do cursor que foi empregado Statistics Feedback, non o podo negar 😛

 

Esta empregando a consulta un SPD?

Tendo en conta o bug en 12.1.0.1, é unha pregunta importante neste momento. Tento saber se se emprega a través das notas que amosa dbms_xplan.display_cursor que, para a consulta diabólica que estamos a tratar son:

Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

Note
-----
   - statistics feedback used for this statement
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

Interesante porque nesta proba só temos 2 child cursor, a diferencia dunha incidencia anterior onde chegaba a haber 6. A diferencia entre os plans encaixa co comportamento esperado para SF, xa que a primeira execución é completada e a segunda emprega o feedback deixado pola primeira como podemos ver na doc de Oracle.

Sabemos que empregamos SF, pero non temos nada mais salvo que revisemos con mais detalle os plans de execución. Cando se emprega unha SPD, o plan de execución amosado indicarao, tendo que aparecer algo similar a:

Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- 1 Sql Plan Directive used for this statement

Pese a que existen moitas SPDs, non atopamos plans de execución que as empreguen, cando menos relacionadas coa consulta analizada. Isto lévame a pensar, e se realmente temos malos plans de execución porque non estamos empregando SPDs pese a telas creadas para cimentar o resultado das SFs? Vemos que a consulta corrixe o primeiro plan de execución ó obter datos reais e comparalo coas previsións. Crea unha SPD, pero o bug 12.1.0.1 fai que futuras execucións non as teñan en conta, pero precisamente o cálculo de estatísticas invalida o anterior plan. Como podemos comprobalo? Vendo o motivo polo que o anterior plan foi invalidado, que no noso caso sempre acaba sendo o mesmo:

21:37:55 SYS@bd> select sql_id, child_number, reason from v$sql_shared_cursor where sql_id='81uujczagk9bf';

SQL_ID          CHILD_NUMBER REASON
------------- ------------ --------------------------------------------------------------------------------
81uujczagk9bf         0 <ChildNode><ChildNumber>0</ChildNumber><ID>49</ID><reason>Auto Reoptimization Mismatch(1)</reason><size>3x4</size><kxscflg>32</kxscflg><kxscfl4>4194560</kxscfl4><dnum_kksfcxe>262144</dnum_kksfcxe></ChildNode>

Conclusión

Como conclusión, a hipótese barallada tras analizar a incidencia é que en algún momento, a funcionalidade SF invalida un plan de execución e crea novas SPDs para correxilo, pero estas SPDs non serán empregadas por outras consultas en caso de que se invalide o cursor e se faga un novo hard parse. Simple, pero encaixa. Finalmente, todo isto para que? Pois para concluir que probablemente convén nunha versión 12.1.0.1 subir o parámetro optimizer_dynamic_sampling a 3. O mostreo é similar ó do nivel 2, pero engádense sentencias con expresións na cláusula WHERE que a priori parece tolerable en carga, aínda que este é un prexuizo non contrastado aínda. Hai que ter en conta que este cambio, nunha BD estable, pode facer cambiar plans de execución que non están a usar SPDs pero son bos.