SIMULAR ACTIVE SESSION HISTORY- SASH - para Oracle Standard Edition (por Alejandro Aguirrez)

Durante mis años como trabajando como dba ORACLE en muchas oportunidades he necesitado  indicadores y métricas con mayor granularidad que los snapshops cada 1 hora o , en ambientes mas críticos,  cada 30 minutos para poder diagnosticar y aislar  un problema  . Oracle presento una solución con la aparicion de ASH (Active Session History ) a partir de la versión 10g en adelante. Desafortunadamente ASH solo está disponible en las versión Enterprise (EE)  y con el Diagnostic Pack licenciado. Es decir que en versiones "Standard Edition" (SE)y "Standard One" (SO) si bien la vista existe los datos no son colectados.

Como todos sabemos el costo de las licencias para EE muy superior al de SE y SO. Por esta razon muchas empresas optan por las verisones Standard que se ajusten a las prestaciones que necesita. Sin embargo como sabemos a nivel diagnostico y Performance deja a los dba sin AWR ni ASH .

Buscando  llegue  un blog Kyle Hailey donde presentaba SASH (Simulated ASH). Esta herramienta simulaba el collect de ASH mediante jobs y emulaba un catalogo de ASH. La idea me pareció excelente asi que empece a realizarle mejoras para poder  (desde el 2010 que no se actualizaba el blog) ya que como toda version inicial tenía varios bugs.

Ahora con la version 2.1 considero que esta lo suficiente madura para poder compartirla e ir mejorandola con las experiencias de los que la utilicen.

Prerequisitos

1. Versión de base de datos 10.2 en adelante (versiones anteriores a esta no fue probado por ende no estaría homologado pero no quiere decir que no funcione)
2. Tablespace Temporal TEMP . En caso de necesitar usar otro tablespace temporal  se debe modificar el script repo_0_user.sql con el nombre del tablespace temporal desado.
3. Tablespace de Datos dedicado para el repositorio de SASH.
4. Acceso como SYS a la base de datos.

Caracteristicas

1. Muestras en tiempo real con un intervalo configurable ( En mis pruebas la configuración ideal es cada 5 segundos ).
2. Los datos se visualizan el vista SASH.V$ACTIVE_SESSION_HISTORY
3. Los planes de ejecución se pueden visualizar en la tabla SASH.SASH_SQLPLANS

Instalación

Los pasos están en el archivo Manual_SASH.

Sentencias Básicas para utilizar la herramienta:

1 - Sentencias más consumidoras en un intervalo de tiempo

select sql_id as hash_value,sqlid,SQL_PLAN_HASH_VALUE,COUNT(*)*2 AS total_wait_time, round((count(*)*2)/(sum(count(*)*2) over() ) *100,2) pct

from sash.V$active_session_history  a

where a.sample_time >= to_date('03/02/2015 12:30','DD/MM/YYYY HH24:MI')

and a.sample_time <= to_date('03/02/2015 15:30','DD/MM/YYYY HH24:MI')

and sql_id!=0

group by a.sql_id ,a.sqlid,a.SQL_PLAN_HASH_VALUE

order by total_wait_time desc;

2 - Sentencias mas consumidoras en un intervalo de tiempo con sus evento asociado

select NVL(a.event, 'ON CPU') AS event,sql_id hash_value,sqlid,SQL_PLAN_HASH_VALUE,

COUNT(*)*2 AS total_wait_time, round((count(*)*2)/(sum(count(*)*2) over() ) *100,2) pct

from sash.V$active_session_history  a

where a.sample_time >= to_date('03/02/2015 12:30','DD/MM/YYYY HH24:MI')

and a.sample_time <= to_date('03/02/2015 15:30','DD/MM/YYYY HH24:MI')

and sql_id!=0

group by a.event,a.sql_id ,a.sqlid,a.SQL_PLAN_HASH_VALUE

order by total_wait_time desc;

3 - Eventos en un intervalo de tiempo

select  NVL(a.event, 'ON CPU') AS event

COUNT(*)*2 AS total_wait_time, round((count(*)*2)/(sum(count(*)*2) over() ) *100,2) pct

from sash.V$active_session_history  a

where a.sample_time >= to_date('03/02/2015 12:30','DD/MM/YYYY HH24:MI')

and a.sample_time <= to_date('03/02/2015 15:30','DD/MM/YYYY HH24:MI')

group by a.event

order by total_wait_time desc;

4 - obtener planes de ejecución

SELECT '| Operation | PHV/Object Name | Rows | Bytes| Cost |'

as "Optimizer Plan:"

FROM dual

UNION ALL

SELECT * FROM (SELECT

rpad('|'||substr(lpad(' ',1*(depth-1))||operation||

--rpad('|'||substr(lpad(' ',1)||operation||

decode(options, null,'',' '||options), 1, 32), 33, ' ')||'|'||

rpad(decode(id, 0, '----- '||to_char(hash_value)||' [' ||']-----'

, substr(decode(substr(object_name, 1, 7), 'SYS_LE_', null,

object_name)

||' ',1, 20)), 21, ' ')||'|'||

lpad(decode(cardinality,null,' ',

decode(sign(cardinality-1000), -1, cardinality||' ',

decode(sign(cardinality-1000000), -1, trunc(cardinality/1000)||'K',

decode(sign(cardinality-1000000000), -1, trunc(cardinality/1000000)||'M',

trunc(cardinality/1000000000)||'G')))), 7, ' ') || '|' ||

lpad(decode(bytes,null,' ',

decode(sign(bytes-1024), -1, bytes||' ',

decode(sign(bytes-1048576), -1, trunc(bytes/1024)||'K',

decode(sign(bytes-1073741824), -1, trunc(bytes/1048576)||'M',

trunc(bytes/1073741824)||'G')))), 6, ' ') || '|' ||

lpad(decode(cost,null,' ',

decode(sign(cost-10000000), -1, cost||' ',

decode(sign(cost-1000000000), -1, trunc(cost/1000000)||'M',

trunc(cost/1000000000)||'G'))), 8, ' ') || '|' as "Explain plan"

FROM sash.SASH_SQLPLANS

WHERE  hash_value =PLAN_HASH_VALUE

ORDER BY hash_value,id);

5 - Obtener el texto de una sentencia (en RAC puede aparecer dos veces)

select * from SASH.SASH_SQLTXT where sql_id=HASH_VALUE_SENTENCIA order by ADDRESS,PIECE;

Espero que les sea de utilidad!! :)

Como aplicar hints en sentencias sin necesidad de modificarlas desde la aplicación usando SQL-BASELINES (por Alejandro Aguirrez)

           Durante mi experiencia como DBA me he encontrado con incontables problemas de performace sobre sentencias que eran ejecutadas desde  aplicaciones donde el código de las sentencias no puede ser  modificado. 

           En versiones anteriores a 11g debias generar outlines y editarlas para que la sentencia elija el PATH optimo para resolver la consulta en el menor tiempo . En 11g disponemos de una nueva feature que nos permite manejar la estabilidad de los planes de ejecucion SQL BASELINES.

            En el siguiente documento se detallará el procedimiento para cargar una baseline generada a partir de la sentencia original (generada por la aplicación) con la adición de un hint/s que produce la obtención del plan optimo para esta sentencia .

Nomenclatura utilizada

ORIGINAL_SQL_ID = SQL_ID de la sentencia original sin HINTS a la cual le queremos fijar un plan optimo aplicando HINTS

1 - (Opcional si no fue realizado antes )

Capturar la sentencia de la sentencia original sin HINTS. El siguiente código solo cargara la baseline si la misma está actualmente en memoria.

BEGIN

 dbms_spm.load_plans_from_cursor_cache(sql_id => '&original_sql_id', plan_hash_value => &original_plan_hash_value );

END;

/

2 - Ejecutar el SQL con el HINT necesario para generar un plan de ejecución optimo

3 - Identificar el sql_id y plan de ejecuion  de la sentencia con HINTS de la vista V$SQL.

select * from table(dbms_xplan.display_cursor('&hinted_SQL_ID'));

4 - Verificar que la baseline generada en el paso 1 exista. Extraer el campo SQL_HANDLE

SELECT * FROM dba_sql_plan_baselines WHERE signature IN (SELECT force_matching_signature FROM dba_hist_sqlstat WHERE sql_id='&original_sql_id')

5 - Asociar el plan de ejecución de la sentencia con hints con la sentencia original

var res number

exec :res := dbms_spm.load_plans_from_cursor_cache( -

sql_id => '&hinted_SQL_ID', -

plan_hash_value => &hinted_plan_hash_value, -

sql_handle => '&sql_handle_for_original');

6 - Verificar que la baseline fue agregada

SELECT * FROM dba_sql_plan_baselines WHERE signature IN (SELECT force_matching_signature FROM dba_hist_sqlstat WHERE sql_id='&original_sql_id')

7 - Dropear o deshabilitar el plan de ejecución original de la sentencia sin hints

exec :res :=DBMS_SPM.DROP_SQL_PLAN_BASELINE ('&original_sql_handle','&original_plan_name');

8 - (OPCIONAL) Sacar la sentencia de la shared pool solo en caso que siga tomando el plan original

DECLARE

  name varchar2(50);

  version varchar2(3);

BEGIN

  select regexp_replace(version,'\..*') into version from v$instance;

  if version = '10' then

    execute immediate

      q'[alter session set events '5614566 trace name context forever']'; -- bug fix for 10.2.0.4 backport

  end if;

  select address||','||hash_value into name

  from v$sqlarea

  where sql_id like '&original_sql_id';

  sys.dbms_shared_pool.purge(name,'C',1);

END;

/

9 - Ejecutar la sentencia desde la aplicación

10 - verificar que tome la baseline

select  SQL_ID,SQL_PROFILE,SQL_PLAN_BASELINE,PLAN_HASH_VALUE,EXECUTIONS,ELAPSED_TIME,ELAPSED_TIME/1000000/EXECUTIONS,DISK_READS,OPTIMIZER_COST  from V$sql where sql_id='&original_sql_id';

11 - (OPCIONAL) Si se considera que el plan con hints es el plan mas optimo modificar el la baseline para que este FIXED (no seguira capturando baselines para esta sentencia particular, en oportunidades cierto tipo de sentencias capturan demasiados planes lo cual no es recomendado. Por esta razon en ocaciones he tenido que deshabilitar la captura de nuevos planes para el sql_handle asociados al sql_id original).

DECLARE

  l_plans_altered  PLS_INTEGER;

BEGIN

  l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(

    sql_handle      => '&sql_handle_for_original'',

    plan_name       => '& hinted_plan_name'',

    attribute_name  => 'fixed',

    attribute_value => 'YES');

  DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);

END;

/

Referencias:

Loading Hinted Execution Plans into SQL Plan Baseline. (Doc ID 787692.1)

Separar un string en filas de una tabla usando un separador

Hay varias formas de separar un string en varias partes (tokens) usando un separador determinado. En este caso voy a compartir una forma para hacerlo usando una sentencia sql. Dicha sentencia se podria usar en forma aislada o insertarla en un cursor para poder iterar la lista generada en un bloque PL/SQL.

La siguiente query transforma una lista de nombres separados por / en filas de una tabla:

with t as (select 'Pablo/Juan/Pedro/Anibal' as str from dual)
select DISTINCT REGEXP_SUBSTR (str, '[^/]+', 1, level) as nombre
from t
connect by level <= length(regexp_replace(str,'[^/]+'))+1

NOMBRE

Pedro
Pablo
Juan
Anibal

Esta forma usa solo una sentencia sql con expresiones regulares y subquery factoring (claúsula with) y no requiere de crear paquetes para separar el string o usar por ejemplo funciones como APEX_UTIL.STRING_TO_TABLE que necesita tener las utilidades APEX instaladas.