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.
