Cursor Sharing e Histogramas

Hace poco vi una base de datos que daba servicio a un Call Center y es una aplicación que se desarrolló a medida. La aplicación corre sobre Oracle 10gR2 en UNIX y el nivel de concurrencia era considerable, en horas de pico llegaba a 1500 usuarios y una media de 1000.

La base de datos tiene un monton de problemas, la maquina se ahoga por excesivo uso de CPU de la base de datos y el CPU usage no bajaba de 85% en ningún momento. Lo primero que habia que investigar era el uso de CPU. Con el AWR se veía que tenian un parsing excesivo, todos eran Hard Parsing. Mirando los TOP SQL se veía que los SQL usaban variables tipo SYS_B_0, SYS_B_1 el cual quería decir que estaba fijado el parametro CURSOR_SHARING. Se revisó que valor tenía y se vio que estaba a SIMILAR.

Hasta aqui varios puntos a tener en cuenta:

1. La aplicación no utiliza bind variables por lo tanto es un sistema que no escalaría nunca, siempre irá corto de CPU ya que todos los parsing son Hard, los mas costosos.

2. Alguien se dio cuenta y se le ocurrió la idea de poner CURSOR_SHARING. Este parametro como sabemos desde la 9i nos da tres opciones, EXACT, SIMILAR y FORCE, antes de 9i la opción de SIMILAR no existía.

3. Por qué sigue habiendo tanto Hard Parsing si estaba puesto el parametro?

Para entender la situación habría que ver que es lo que hace SIMILAR, que diferencia tiene entre SIMILAR y FORCE?

SIMILAR a diferencia de FORCE utiliza varios criterios para determinar si se debe de compartir los cursores, uno de los criterios es, si la columna del predicado de la Query tiene histogramas no compartiría los cursores, es decir si se lanzan 1000 veces una misma query pero con diferentes valores en el predicado luego en el Library Cache habrá 1000 cursores representando las 1000 queries que se ha ejecutado.

Se procedió a revisar el metodo de pasar las estadísticas en la base de datos. Como se imaginaba estaba pasando las estadísticas con histogramas en todos los campos por lo tanto no se comparte ningun cursor.

Para ver el comportamiento lo mejor es seguir un pequeño ejemplo:


lsc@lnx920-PANDO>create table t1
  2  (
  3     ID number,
  4     ANIMAL varchar2(12)
  5  )
  6  tablespace users;

Table created.

-- Generamos los datos de tal manera que las histogramas nos irian de maravilla en las Queries
lsc@lnx920-PANDO>INSERT INTO t1
  2     WITH tdata AS
  3          (SELECT ROWNUM ID
  4             FROM all_objects
  5            WHERE ROWNUM <= 30)
  6     SELECT ROWNUM,
  7            'PERRO'
  8       FROM tdata a, tdata b
  9      WHERE ROWNUM <= 900;

900 rows created.

lsc@lnx920-PANDO>INSERT INTO t1
  2     WITH tdata AS
  3          (SELECT ROWNUM ID
  4             FROM all_objects
  5            WHERE ROWNUM <= 10)
  6     SELECT ROWNUM,
  7            'GATO'
  8       FROM tdata a, tdata b
  9      WHERE ROWNUM <= 10;

10 rows created.

lsc@lnx920-PANDO>commit;

Commit complete.
 lsc@lnx920-PANDO>create index t1_i1 on t1(animal);

Index created.

-- Pasamos dbms_stats con histogramas
lsc@lnx920-PANDO>exec dbms_stats.gather_table_stats(ownname => 'LSC', tabname => 'T1',-
> estimate_percent => 20, method_opt => 'FOR COLUMNS ANIMAL SIZE 254', cascade => TRUE)

PL/SQL procedure successfully completed.

-- Revisamos v$sqlarea y vemos que no hay ningun cursor referenciando a la tabla t1
lsc@lnx920-PANDO>select sql_text, version_count
  2    from v$sqlarea
  3   where sql_text like 'select%t1%';

no rows selected

lsc@lnx920-PANDO>select count(*)
  2  from t1
  3  where animal = 'PERRO';

  COUNT(*)
----------
       900

-- Revisamos otra vez v$sqlarea y vemos que ya existe una versión de la query
lsc@lnx920-PANDO>select sql_text, version_count
  2    from v$sqlarea
  3   where sql_text like 'select%t1%';

SQL_TEXT                                           VERSION_COUNT
-------------------------------------------------- -------------
select count(*) from t1 where animal = :"SYS_B_0"              1

lsc@lnx920-PANDO>select count(*)
  2  from t1
  3  where animal = 'GATO';

  COUNT(*)
----------
        10

-- Revisamos otra vez v$sqlarea y vemos que el cursor no se comparte porque existe una versión por cada query
lsc@lnx920-PANDO>select address, sql_text, version_count
  2    from v$sqlarea
  3   where sql_text like 'select%t1%';

ADDRESS  SQL_TEXT                                           VERSION_COUNT
-------- -------------------------------------------------- -------------
5BC43B48 select count(*) from t1 where animal = :"SYS_B_0"              2

-- Con el ADDRESS podemos verificar en v$sql_shared_cursor que no existe ningún tipo de mismatch
lsc@lnx920-PANDO>select * from V$SQL_SHARED_CURSOR
  2  where KGLHDPAR = '5BC43B48'
  3  ;

ADDRESS  KGLHDPAR U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F L
-------- -------- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
5BCFC0CC 5BC43B48 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
5B9D636C 5BC43B48 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N

-- Esto quiere decir que no comparte cursor por otras razones, en este caso por las histogramas

-- Verificamos el numero de los Hard Parses
lsc@lnx920-PANDO>alter system flush shared_pool;

System altered.

lsc@lnx920-PANDO>@sesstat
Nombre de usuario:  [LSC]
SID:  [11] 11
Statistic name:  [NONE] Parse

   SID USERNAME         NAME                                                                 VALUE
------ ---------------- ------------------------------------------------------------ -------------
    11 LSC              parse count (failures)                                                   0
    11 LSC              parse count (hard)                                                       0
    11 LSC              parse time cpu                                                           4
    11 LSC              parse time elapsed                                                       4
    11 LSC              parse count (total)                                                     20

-- Ejecutamos 1000 veces la misma query con SQL dinamico y con diferente valores en el predicado
lsc@lnx920-PANDO>begin
  2          for i in 1..1000
  3          loop
  4                  execute immediate 'select * from t1 where animal = ' || i;
  5          end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

lsc@lnx920-PANDO>@sesstat
Nombre de usuario:  [LSC] .
SID:  [11] 11
Statistic name:  [NONE] Parse

   SID USERNAME         NAME                                                                 VALUE
------ ---------------- ------------------------------------------------------------ -------------
    11 LSC              parse count (failures)                                                   0
    11 LSC              parse time cpu                                                         126
    11 LSC              parse time elapsed                                                     331
    11 LSC              parse count (hard)                                                     971
    11 LSC              parse count (total)                                                   1028

-- Vemos que el numero de Hard Parses ha incrementado 971 y el total 1008

-- Revisamos el numero de versiones del cursor - 1000, una por cada query ejecutada
lsc@lnx920-PANDO>select sql_text, version_count
  2    from v$sqlarea
  3   where sql_text like 'select%t1%';

SQL_TEXT                                                         VERSION_COUNT
---------------------------------------------------------------- -------------
select * from t1 where animal = :"SYS_B_0"                                1000

La solución temporal para el cliente era o poner a FORCE el CURSOR_SHARING ó pasar estadísticas sin histogramas. Las dos soluciones son parches temporales, la definitiva es reescribir la aplicación para que use Bind Variables.

5 thoughts on “Cursor Sharing e Histogramas

  1. Ligarius

    Hola Li

    Realmente muy bueno el post, pero sabes… me parece que la solución va por colocar el CURSOR_SHARING a FORCE, sólo que este paso posee un problema, el hecho de que si cambian en demasia los valores de la tabla , por ende el mismo plan de ejecución para sentencias que sólo varien en sus literales, puede resultar peligroso, al ejecutarse en momentos que las tablas han cambiado mucho, me explico?

    select campo1 , campo2 from a where campo3 = ‘X1’ –Tabla con 10 filas
    un par de horas después
    select campo1 , campo2 from a where campo3 = ‘X2’ –Tabla con 100 millones de filas

    Reply
  2. ligarius

    Hola Li

    Una última pregunta …

    Utilizas el utilitario sesstat , viene por defecto en la instalación de Oracle10g?

    Muchas gracias ye spero un nuevo post tuyo

    Bye

    Desde Santiago de Chile

    Reply
  3. whest

    Hola,

    para modificar el parámetro CURSOR_SHARING a EXACT hay que estar seguro que la mayoría de las tablas no sufre cambios importantes de datos y/o estados.

    Un saludo,

    Whest.

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s