Podo usar Stored Outlines cunha Standard Edition?

SI dende a versión 10g.

Podería semellar unha resposta evidente ou simple, pero non o é tanto xa que se trata dunha funcionalidade documentada de xeito contradictorio por Oracle.

 

Plan Stability vs SQL Plan Management

Hai dous conceptos que ter en conta relacionados cos Stored Outlines. Plan Stability (pre 11g) e SPM (>= 11g). Stored Outlines formaba parte das funcionalidades de Plan Stability, como podemos ver na doc de Oracle que documenta moi ben Charle’s Hooper no seu blog. Plan Stability, en 8i e 9i é unha funcionalidade documentada exclusivamente como Enterprise, polo que non é empregable en Standard Edition.

SQL Plan Management (SPM) é unha evolución de Plan Stability, pero non substitúe a este, simplemente crea una nova funcionalidade con novos compoñentes (como os SQL Baselines) para mellorar Plan Stability. Deste xeito, a partires de 11g, temos a posibilidade de empregar as dúas opción PS e SPM.

Nunha BD 12c, na que coexisten as dúas funcionalidades, vemos nunha Standard Edition unha importante diferencia entre as dúas opcións:

select parameter, value
from v$option
where parameter in ('Plan Stability','SQL Plan Management')

PARAMETER	     VALUE
-------------------- -----
Plan Stability	     TRUE
SQL Plan Management  FALSE

Plan Stability está habilitado, pero SPM non. Sabemos que SPM non é usable en SE e, de xeito lóxico está deshabilitado impedindo o seu uso involuntario. De feito, moitas funcionalidades Enterprise Edition están deshabilitadas en Standard Edition, como Spatial, Partitioning, Parallel Execution ou Basic Compression. Pese a elo, sabemos que non sempre opcións Oracle habilitadas implican que poden ser usadas, como ocorre habitualmente (máis antes de 11gR2 e SE) cos packs de Diagnostics e Tuning da BD, ou incluso o acceso á propia AWR que segue habilitada en SE por defecto aínda que os packs EM non o están.

 

Onde está a confirmación do uso de Stored Outlines?

Non coñezo un documento no que se amose de xeito explicito en versións 10g ou posteriores. Na defensa desta afirmación temos:

  • As versións 8i e 9i inclúen Plan Stability (Stored Outlines) entre as funcionalidades exclusivas de Enterprise. Esta distinción deixa de aparecer en 10g.
  • Coa aparición de SPM, de novo a documentación Oracle fai referencia a unha funcionalidade exclusiva para a edición Enterprise. Pese a elo, segue sen haber referencias a limitacións de licenciamento dos Stored Outlines en Standard Edition. SPM non inclúe Stored Outlines, senón que os substitúe.
  • A edición Standard, cando menos nas versións 10g en adiante, traen habilitada esta opción por defecto e permite sen problemas o uso de Stored Outlines.
  • Pese á dúbida, como partner Oracle escalei a dúbida internamente, obtendo como resposta que si, que é unha opción dispoñible en Oracle Standard Edition non precisando ningunha licencia adicional. De tódolos xeitos, unha consulta similar 3 anos antes recibira contestación contraria, pero nese caso a resposta falaba de xeito incorrecto de SPM, cando Stored Outlines queda fóra deste paquete funcional. Daquela insistira na pregunta, pero esta morreu sen resposta oficial.

Polo tanto, con estas premisas, incluíndo a confirmación explícita de Oracle, aceptamos Stored Outlines como opción a ter en conta para solucionar problemas con Standard Edition.

 

Pero as Stored Outlines están deprecated, por que usalas?

Certo, a versión 11g foi a primeira na que os Stored Outlines pasaron a ser un compoñente cuxa funcionalidade foi substituída (e ampliada) por unha nova funcionalidade agrupada no que Oracle nomeou SPM (SQL Plan Management). Así, 11g anunciou o fin do mantemento evolutivo dos Stored Outlines, pasaron a estar deprecated. Pese a elo, tanto 11g como 12c manteñen funcionalmente estes compoñentes, e de feito, a nota MOS fala deles como “legacy technique for providing plan stability”.

Aínda así, se o teu ecosistema é Standard Edition, pronto verás que non existen alternativas para fixar un plan de execución, polo que esta opción se convirte nun auténtico luxo cando se trata de solucionar problemas de rendemento en SE, incluso cando estamos a traballar nunha versión 12c. Salvo o emprego de hints, que pasan pola modificación do código da aplicación, moitas veces algo non posible no curto prazo, non contamos con outra ferramenta que nos permita fixar como debe executarse unha consulta.

 

Exemplo de creación dun Stored Outline

Trátase neste caso dun exemplo nunha BD real en versión 12.1.0.1 na que por motivos non completamente identificados (aplicáronse varias solucións, como a aplicación dun parche específico para un bug identificado nun SR, como a deshabilitación de determinadas funcionalidades do optimizador coa supervisión de Soporte Oracle), en determinadas ocasións, unha consulta que dura menos de 5 segundos, pasa a tardar varios minutos tras sufrir un cambio non esperado no plan. Ante a imposibilidade de identificar a orixe do problema, optamos pola creación dun Stored Outline tras ter identificado un plan de execución que se mostra válido independentemente dos valores das bind variables empregadas en múltiples execucións.

Partimos sun SQL con sqlid=81uujczagk9bf. No contorno dispoñemos en memoria do cursor correspondente, do que obtemos a súa información.

select sql_id, hash_value, child_number, plan_hash_value, loaded_versions, open_versions, users_opening, users_executing, fetches, executions, loads, parse_calls, invalidations, first_load_time
from v$sql
where sql_id='81uujczagk9bf'  2    3  ;

SQL_ID	      HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE LOADED_VERSIONS OPEN_VERSIONS USERS_OPENING USERS_EXECUTING    FETCHES EXECUTIONS      LOADS PARSE_CALLS INVALIDATIONS FIRST_LOAD_TIME
------------- ---------- ------------ --------------- --------------- ------------- ------------- --------------- ---------- ---------- ---------- ----------- ------------- ------------------------------
81uujczagk9bf 3573097838	    0	    317020229		    1		  0		0		0	   5	      1 	 5	     1		   2 2017-07-06/10:20:13
81uujczagk9bf 3573097838	    1	   3438832455		    1		  0		0		0	 166	     37 	 5	    33		   3 2017-07-06/10:20:13

Vemos que no cursor contamos con dous plans. Tras ter monitorizado o comportamento das consultas, sabemos que o segundo plan, con 37 execucións, é correcto e a duración das consultas con este plan son as esperadas. Cando se produce a incidencia, este plan ou outro similar é invalidado e se carga en memoria o plan incorrecto. Imos a crear o Stored Outline para fixar o plan con plan_hash_value 3438832455, que como vemos, é o fillo número número 1 do cursor 3573097838.

begin
 	dbms_outln.create_outline(
		hash_value => '3573097838',
		child_number => 1,
		category => 'default'
	);
end;
/

O que acabamos de facer é invocar ó procedemento dbms_outln.create_online, indicando o SQL para o que o imos crear (empregando o hash_value e non o sql_id), o identificador de fillo do cursos, e a categoría é a default, que é a que empregaría o procedemento por defecto tamén se non especificamos nada. Neste caso non precisamos facer ningún tipo de distinción por categoría, polo que este valor é correcto para nós. Vemos o novo obxecto na BD:

column name format a30
column owner format a15
column category format a10
column version format a10
select name, owner, category, used, enabled, version
from dba_outlines
/

NAME			       OWNER	       CATEGORY   USED	 ENABLED  VERSION
------------------------------ --------------- ---------- ------ -------- ----------
SYS_OUTLINE_17071016074710901  ARUMEL	       default	  UNUSED ENABLED  12.1.0.1.0

Ó empregar dbms_outl.create_online para crear o outline a partires da información na shared pool, non nos é posible establecer un nome, obtendo un xerado polo sistema co formato SYS_OUTLINE_Timestamp. Modificamos o nome para que sexa mais descritivo.

alter outline SYS_OUTLINE_17071016074710901 rename to fixacion_plan_batch_arumel;

Outline altered.


column name format a30
column owner format a15
column category format a10
column version format a10
select name, owner, category, used, enabled, version
from dba_outlines
/

NAME			       OWNER	       CATEGORY   USED	 ENABLED  VERSION
------------------------------ --------------- ---------- ------ -------- ----------
FIXACION_PLAN_BATCH_ARUMEL     ARUMEL	       default	  UNUSED ENABLED  12.1.0.1.0