sábado, 29 de septiembre de 2007

Problemas utilizando Analytic Functions junto con PL/SQL en 8i

La idea de éste post no es explicar el funcionamiento de las Analytic Functions, sino el error PLS-00103 al utilizar Analytic Functions.
La mejor manera de explicar éste problema, es realizando un ejemplo.

La tabla de prueba que vamos a utilizar en nuestro ejemplo, contiene los siguientes registros:

SQL> SELECT * FROM test ;

ID NIVEL SALARIO
---------- ---------- ----------
10 1 2500
20 2 3000
30 1 3500
40 2 4000
50 1 4500
60 2 5000
70 1 5500
80 2 6000
90 1 6500
100 2 7000

10 rows selected.

Primero vamos a realizar el ejemplo en una base de datos 8.1.7.4.0

SQL_8i> SELECT id, nivel, salario, DENSE_RANK() OVER (PARTITION BY nivel ORDER BY salario DESC) AS rank
2 FROM test ;

ID NIVEL SALARIO RANK
---------- ---------- ---------- ----------
90 1 6500 1
70 1 5500 2
50 1 4500 3
30 1 3500 4
10 1 2500 5
100 2 7000 1
80 2 6000 2
60 2 5000 3
40 2 4000 4
20 2 3000 5

10 rows selected.

Como podemos observar, si ejecutamos esa consulta con Analytic Functions en SQL*Plus, funciona a la perfección. Veamos qué sucede si intentamos ejecutar la consulta dentro de PL/SQL:

SQL_8i> CREATE PROCEDURE pr_test
2 IS
3 BEGIN
4 --
5 FOR cur IN ( SELECT id, nivel, salario, DENSE_RANK() OVER (PARTITION BY
nivel ORDER BY salario DESC
) AS rank
6 FROM test ) LOOP
7 --
8 dbms_output.put_line('ID: '||cur.id||
9 ' - NIVEL: '||cur.nivel||
10 ' - SALARIO: '||cur.salario||
11 ' - RANK: '||cur.rank) ;
12 --
13 END LOOP ;
14 --
15 END pr_test ;
16 /

Warning: Procedure created with compilation errors.

SQL_8i> show errors

Errors for PROCEDURE PR_TEST:

LINE/COL ERROR
----------- ------------------------------------------------------------------------
5/63 PLS-00103: Encountered the symbol "(" when expecting one of the following:
, from

Este error se debe a que según la Nota 147808.1, no podemos utilizar Analytic Functions dentro de PL/SQL en versiones anteriores a 9i. Podemos utilizar éste tipo de funciones en SQL, pero no en PL/SQL. Desde las versiones de la 9i en adelante, podemos utilizar Analytic Functions tanto en SQL como en PL/SQL.

Hay 2 formas de solucionar éste problema:
- Crear vistas utilizando Analytic Function y luego hacer referencia a esas vistas dentro de PL/SQL.
- Usar Dynamic SQL.

Veamos la implementación de esas 2 soluciones:

Utilizando una vista...

SQL_8i> CREATE VIEW pr_test_view AS
2 SELECT id, nivel, salario, DENSE_RANK() OVER (PARTITION BY nivel ORDER BY s
alario DESC) AS rank
3 FROM test ;

View created.

SQL_8i> CREATE OR REPLACE PROCEDURE pr_test
2 IS
3 BEGIN
4 --
5 FOR cur IN ( SELECT id, nivel, salario, rank
6 FROM pr_test_view ) LOOP
7 --
8 dbms_output.put_line('ID: '||cur.id||
9 ' - NIVEL: '||cur.nivel||
10 ' - SALARIO: '||cur.salario||
11 ' - RANK: '||cur.rank) ;
12 --
13 END LOOP ;
14 --
15 END pr_test ;
16 /

Procedure created.

SQL_8i> EXEC pr_test

ID: 90 - NIVEL: 1 - SALARIO: 6500 - RANK: 1
ID: 70 - NIVEL: 1 - SALARIO: 5500 - RANK: 2
ID: 50 - NIVEL: 1 - SALARIO: 4500 - RANK: 3
ID: 30 - NIVEL: 1 - SALARIO: 3500 - RANK: 4
ID: 10 - NIVEL: 1 - SALARIO: 2500 - RANK: 5
ID: 100 - NIVEL: 2 - SALARIO: 7000 - RANK: 1
ID: 80 - NIVEL: 2 - SALARIO: 6000 - RANK: 2
ID: 60 - NIVEL: 2 - SALARIO: 5000 - RANK: 3
ID: 40 - NIVEL: 2 - SALARIO: 4000 - RANK: 4
ID: 20 - NIVEL: 2 - SALARIO: 3000 - RANK: 5

PL/SQL procedure successfully completed.

Utilizando Dynamic SQL...

SQL_8i> CREATE OR REPLACE PROCEDURE pr_test
2 IS
3 --
4 TYPE mi_cursor IS REF CURSOR ;
5 cur mi_cursor ;
6 --
7 l_consulta VARCHAR2(1000) ;
8 l_id test.id%TYPE ;
9 l_nivel test.nivel%TYPE ;
10 l_salario test.salario%TYPE ;
11 l_rank NUMBER ;
12 --
13 BEGIN
14 --
15 l_consulta := 'SELECT id, nivel, salario, DENSE_RANK() OVER (PARTITION
BY nivel ORDER BY salario DESC) AS rank FROM test' ;
16 --
17 OPEN cur FOR l_consulta ;
18 --
19 LOOP
20 --
21 FETCH cur INTO l_id, l_nivel, l_salario, l_rank ;
22 --
23 EXIT WHEN cur%NOTFOUND ;
24 --
25 dbms_output.put_line('ID: '||l_id||
26 ' - NIVEL: '||l_nivel||
27 ' - SALARIO: '||l_salario||
28 ' - RANK: '||l_rank) ;
29 --
30 END LOOP ;
31 --
32 CLOSE cur ;
33 --
34 END pr_test ;
35 /

Procedure created.

SQL_8i> EXEC pr_test

ID: 90 - NIVEL: 1 - SALARIO: 6500 - RANK: 1
ID: 70 - NIVEL: 1 - SALARIO: 5500 - RANK: 2
ID: 50 - NIVEL: 1 - SALARIO: 4500 - RANK: 3
ID: 30 - NIVEL: 1 - SALARIO: 3500 - RANK: 4
ID: 10 - NIVEL: 1 - SALARIO: 2500 - RANK: 5
ID: 100 - NIVEL: 2 - SALARIO: 7000 - RANK: 1
ID: 80 - NIVEL: 2 - SALARIO: 6000 - RANK: 2
ID: 60 - NIVEL: 2 - SALARIO: 5000 - RANK: 3
ID: 40 - NIVEL: 2 - SALARIO: 4000 - RANK: 4
ID: 20 - NIVEL: 2 - SALARIO: 3000 - RANK: 5

PL/SQL procedure successfully completed.

Ahora vamos a realizar el ejemplo anterior pero en una base de datos 9.2.0.8.0

SQL_9i> CREATE PROCEDURE pr_test
2 IS
3 BEGIN
4 --
5 FOR cur IN ( SELECT id, nivel, salario, DENSE_RANK() OVER (PARTITION BY
nivel ORDER BY salario DESC) AS rank
6 FROM test ) LOOP
7 --
8 dbms_output.put_line('ID: '||cur.id||
9 ' - NIVEL: '||cur.nivel||
10 ' - SALARIO: '||cur.salario||
11 ' - RANK: '||cur.rank) ;
12 --
13 END LOOP ;
14 --
15 END pr_test ;
16 /

Procedure created.

SQL_9i> EXEC pr_test

ID: 90 - NIVEL: 1 - SALARIO: 6500 - RANK: 1
ID: 70 - NIVEL: 1 - SALARIO: 5500 - RANK: 2
ID: 50 - NIVEL: 1 - SALARIO: 4500 - RANK: 3
ID: 30 - NIVEL: 1 - SALARIO: 3500 - RANK: 4
ID: 10 - NIVEL: 1 - SALARIO: 2500 - RANK: 5
ID: 100 - NIVEL: 2 - SALARIO: 7000 - RANK: 1
ID: 80 - NIVEL: 2 - SALARIO: 6000 - RANK: 2
ID: 60 - NIVEL: 2 - SALARIO: 5000 - RANK: 3
ID: 40 - NIVEL: 2 - SALARIO: 4000 - RANK: 4
ID: 20 - NIVEL: 2 - SALARIO: 3000 - RANK: 5

PL/SQL procedure successfully completed.

Realizamos el ejemplo anterior en una base de datos 10.1.0.2.0, veremos el mismo resultado que en 9i...

SQL_10g> CREATE PROCEDURE pr_test
2 IS
3 BEGIN
4 --
5 FOR cur IN ( SELECT id, nivel, salario, DENSE_RANK() OVER (PARTITION BY
nivel ORDER BY salario DESC) AS rank
6 FROM test ) LOOP
7 --
8 dbms_output.put_line('ID: '||cur.id||
9 ' - NIVEL: '||cur.nivel||
10 ' - SALARIO: '||cur.salario||
11 ' - RANK: '||cur.rank) ;
12 --
13 END LOOP ;
14 --
15 END pr_test ;
16 /

Procedure created.

SQL_10g> EXEC pr_test

ID: 90 - NIVEL: 1 - SALARIO: 6500 - RANK: 1
ID: 70 - NIVEL: 1 - SALARIO: 5500 - RANK: 2
ID: 50 - NIVEL: 1 - SALARIO: 4500 - RANK: 3
ID: 30 - NIVEL: 1 - SALARIO: 3500 - RANK: 4
ID: 10 - NIVEL: 1 - SALARIO: 2500 - RANK: 5
ID: 100 - NIVEL: 2 - SALARIO: 7000 - RANK: 1
ID: 80 - NIVEL: 2 - SALARIO: 6000 - RANK: 2
ID: 60 - NIVEL: 2 - SALARIO: 5000 - RANK: 3
ID: 40 - NIVEL: 2 - SALARIO: 4000 - RANK: 4
ID: 20 - NIVEL: 2 - SALARIO: 3000 - RANK: 5

PL/SQL procedure successfully completed.

Como pudimos observar, de la versión 9i en adelante, podemos utilizar Analytic Functions tanto en SQL como en PL/SQL.

lunes, 24 de septiembre de 2007

Problemas en Vistas Materializadas - Parte 2

Como pudimos ver en el post "Problemas en Vistas Materializadas - Parte 1", podemos identificar con el paquete DBMS_MVIEW (procedimiento EXPLAIN_MVIEW), los problemas que pueden haber en nuestra Vista Materializada e implementar las soluciones necesarias. La contra de ese procedimiento es que nos muestra los errores que tenemos en la Vista Materializada, pero no nos dice cómo solucionarlos.

En Oracle 10g se introduce el paquete DBMS_ADVISOR (procedimiento TUNE_MVIEW) que nos dice qué cambios debemos implementar en nuestra Vista Materializada para soportar las capacidades que necesitamos.

Veamos un ejemplo:

SQL_10gR2> CREATE TABLE test1 AS
2 SELECT level id, level*level total
3 FROM dual
4 CONNECT BY level <= 10 ;

Table created.

SQL_10gR2> CREATE TABLE test2 AS
2 SELECT level id, 'nom_'||level nom
3 FROM dual
4 CONNECT BY level <= 20 ;

Table created.

SQL_10gR2> COMMIT ;

Commit complete.

SQL_10gR2> ALTER TABLE test2 ADD CONSTRAINT id_2_pk PRIMARY KEY (id) ;

Table altered.

SQL_10gR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'TEST1') ;

PL/SQL procedure successfully completed.

SQL_10gR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'TEST2') ;

PL/SQL procedure successfully completed.

SQL_10gR2> SELECT test2.id,
2 test2.nom,
3 SUM(test1.total) sum_total
4 FROM test1,
5 test2
6 WHERE test1.id = test2.id(+)
7 GROUP BY test2.id,
8 test2.nom
9 ORDER BY id ;

ID NOM SUM_TOTAL
---------- ---------- ----------
1 nom_1 1
2 nom_2 4
3 nom_3 9
4 nom_4 16
5 nom_5 25
6 nom_6 36
7 nom_7 49
8 nom_8 64
9 nom_9 81
10 nom_10 100

10 rows selected.

Bien, ya creé mi ambiente de prueba. Ahora voy a crear las tablas de Log necesarias para la capacidad de REFRESH FAST. Pero las voy a crear de forma errónea para demostrar el poder que nos brinda el paquete DBMS_ADVISOR.

SQL_10gR2> CREATE MATERIALIZED VIEW LOG ON test1
2 WITH ROWID, SEQUENCE (id) ;

Materialized view log created.

SQL_10gR2> CREATE MATERIALIZED VIEW LOG ON test2
2 WITH ROWID, SEQUENCE (id,nom) ;

Materialized view log created.

Procedemos a crear la Vista Materializada:

SQL_10gR2> CREATE MATERIALIZED VIEW TEST_MV
2 NOCOMPRESS
3 LOGGING
4 BUILD IMMEDIATE
5 USING INDEX
6 REFRESH FAST ON DEMAND
7 USING DEFAULT LOCAL ROLLBACK SEGMENT
8 DISABLE QUERY REWRITE
9 AS
10 SELECT test2.id,
11 test2.nom,
12 SUM(test1.total) sum_total,
13 COUNT(*) cnt,
14 COUNT(test1.total) cnt_sum
15 FROM test1,
16 test2
17 WHERE test1.id = test2.id(+)
18 GROUP BY test2.id,
19 test2.nom
20 ORDER BY id ;
FROM test1,
*
ERROR at line 15:
ORA-32401: materialized view log on "CDW"."TEST2" does not have new values

Efectivamente como supusimos, tenemos errores en las tablas de Log que creamos.
Ahora vamos a ejecutar DBMS_ADVISOR.TUNE_MVIEW y ver los resultados en la tabla USER_TUNE_MVIEW...

SQL_10gR2> DECLARE
2 l_nombre VARCHAR2(100) := 'test_1' ;
3 BEGIN
4 DBMS_ADVISOR.TUNE_MVIEW(l_nombre, 'CREATE MATERIALIZED VIEW TEST_MV
5 NOCOMPRESS
6 LOGGING
7 BUILD IMMEDIATE
8 USING INDEX
9 REFRESH FAST ON DEMAND
10 USING DEFAULT LOCAL ROLLBACK SEGMENT
11 DISABLE QUERY REWRITE
12 AS
13 SELECT test2.id,
14 test2.nom,
15 SUM(test1.total) sum_total,
16 COUNT(*) cnt,
17 COUNT(test1.total) cnt_sum
18 FROM test1,
19 test2
20 WHERE test1.id = test2.id(+)
21 GROUP BY test2.id,
22 test2.nom
23 ORDER BY id') ;
24 END ;
25 /

PL/SQL procedure successfully completed.

SQL_10gR2> SELECT *
2 FROM user_tune_mview
3 WHERE task_name = 'test_1' ;

TASK_NAME ACTION_ID SCRIPT_TYPE STATEMENT
--------------- ---------- -------------- -----------------------------------
test_1 1 IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE O
N "CDW"."TEST2" ADD ROWID, SEQUENCE
("ID","NOM") INCLUDING NEW VALUES

test_1 2 IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE O
N "CDW"."TEST1" ADD ROWID, SEQUENCE
("ID","TOTAL") INCLUDING NEW VALU
ES


test_1 3 IMPLEMENTATION CREATE MATERIALIZED VIEW CDW.TEST_M
V NOCOMPRESS
LOGGING
BUILD IMMEDIATE
USING INDEX
REFRESH FAST WITH ROWID DISABLE QU
ERY REWRITE AS SELECT test2.id,
test2.nom,
SUM(test1.total) sum_total,
COUNT(*) cnt,
COUNT(test1.total) cnt_sum
FROM test1,
test2
WHERE test1.id = test2.id(+)
GROUP BY test2.id,
test2.nom
ORDER BY id

test_1 4 UNDO DROP MATERIALIZED VIEW CDW.TEST_MV

4 rows selected.

La columna SCRIPT_TYPE nos muestra el tipo de recomendación. Deberíamos ver las columnas con el valor 'IMPLEMENTED' (la recomendación ya está implementada); pero en cambio, estamos viendo que aparecen como 'IMPLEMENTATION' (la recomendación NO está implementada).
El próximo paso es ejecutar los script recomendados...

SQL_10gR2> ALTER MATERIALIZED VIEW LOG FORCE ON "CDW"."TEST2"
2 ADD ROWID, SEQUENCE("ID","NOM") INCLUDING NEW VALUES ;

Materialized view log altered.

SQL_10gR2> ALTER MATERIALIZED VIEW LOG FORCE ON "CDW"."TEST1"
2 ADD ROWID, SEQUENCE("ID","TOTAL") INCLUDING NEW VALUES ;

Materialized view log altered.

SQL_10gR2> CREATE MATERIALIZED VIEW TEST_MV
2 NOCOMPRESS
3 LOGGING
4 BUILD IMMEDIATE
5 USING INDEX
6 REFRESH FAST ON DEMAND
7 USING DEFAULT LOCAL ROLLBACK SEGMENT
8 DISABLE QUERY REWRITE
9 AS
10 SELECT test2.id,
11 test2.nom,
12 SUM(test1.total) sum_total,
13 COUNT(*) cnt,
14 COUNT(test1.total) cnt_sum
15 FROM test1,
16 test2
17 WHERE test1.id = test2.id(+)
18 GROUP BY test2.id,
19 test2.nom
20 ORDER BY id ;

Materialized view created.

Ejecutando las recomendaciones de la tabla, pudimos solucionar rápidamente el problema que teníamos en la Vista Materializada.

Qué sucede si ejecutamos el DBMS_ADVISOR.TUNE_MVIEW nuevamente?

SQL_10gR2> DECLARE
2 l_nombre VARCHAR2(100) := 'test_1' ;
3 BEGIN
4 DBMS_ADVISOR.TUNE_MVIEW(l_nombre, 'CREATE MATERIALIZED VIEW TEST_MV
5 NOCOMPRESS
6 LOGGING
7 BUILD IMMEDIATE
8 USING INDEX
9 REFRESH FAST ON DEMAND
10 USING DEFAULT LOCAL ROLLBACK SEGMENT
11 DISABLE QUERY REWRITE
12 AS
13 SELECT test2.id,
14 test2.nom,
15 SUM(test1.total) sum_total,
16 COUNT(*) cnt,
17 COUNT(test1.total) cnt_sum
18 FROM test1,
19 test2
20 WHERE test1.id = test2.id(+)
21 GROUP BY test2.id,
22 test2.nom
23 ORDER BY id') ;
24 END ;
25 /
DECLARE
*
ERROR at line 1:
ORA-13600: error encountered in Advisor
QSM-03116: The materialized view is already optimal and cannot be tuned any further
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.PRVT_ACCESS_ADVISOR", line 202
ORA-06512: at "SYS.PRVT_TUNE_MVIEW", line 1042
ORA-06512: at "SYS.DBMS_ADVISOR", line 754
ORA-06512: at line 4

[TEST@TEST_10GR2 ~]$ oerr qsm 03116
03116, 00000, "The materialized view is already optimal and cannot be tuned any further"
// *Cause: The materialized view has the capabilities that are specified in
// the statement.
// *Action: none


Oracle nos está diciendo que el script de creación de la Vista Materializada que nosotros ejecutamos, ya cuenta con todas las capacidades necesarias para su creación, y por lo tanto, ya se encuentra óptima y no se puede seguir tuneando.

viernes, 21 de septiembre de 2007

Exchange Partition

Exchange Partition permite cargar en tablas particionadas datos en forma rápida y con muy poco impacto para los usuarios que se encuentran activos.
En resumen, lo que hace la sentencia Exchange Partition es modificar el diccionario de datos y simular que los datos que ya tenemos cargados en una tabla, corresponden a una partición determinada de otra tabla.

Veamos un ejemplo muy sencillo para entender mejor éste tema:

SQL_10gR2> CREATE TABLE datos_1 AS
2 SELECT level id, timestamp'2000-11-02 09:00:00' fecha
3 FROM dual
4 CONNECT BY level <= 100000 ;

Table created.

Elapsed: 00:00:01.06

SQL_10gR2> CREATE TABLE datos_2 AS
2 SELECT level id, timestamp'2001-09-10 13:00:00' fecha
3 FROM dual
4 CONNECT BY level <= 100000 ;

Table created.

Lo que hicimos fue crear 2 tablas con distintas fechas en cada una de ellas.

Ahora creamos solamente la estructura de la tabla particionada en donde vamos a cargar los datos:

SQL_10gR2> CREATE TABLE test
2 ( id, fecha )
3 PARTITION BY RANGE ( fecha )
4 (
5 PARTITION year_2000 VALUES LESS THAN ( timestamp'2000-12-02 00:00:00' ),
6 PARTITION year_2001 VALUES LESS THAN ( timestamp'2001-10-10 00:00:00' )
7 )
8 AS
9 SELECT 1, timestamp'2000-11-02 09:00:00'
10 FROM dual
11 WHERE 1 = 0 ;

Vamos a realizar un alter para modificar el diccionario de datos y relacionar cada una de las 2 tablas que creamos con la respectiva partición de la tabla TEST...

SQL_10gR2> ALTER TABLE test
2 EXCHANGE PARTITION year_2000
3 WITH table datos_1
4 WITHOUT VALIDATION ;

Table altered.

Elapsed: 00:00:00.03

SQL_10gR2> ALTER TABLE test
2 EXCHANGE PARTITION year_2001
3 WITH table datos_2
4 WITHOUT VALIDATION ;

Table altered.

Elapsed: 00:00:00.02

SQL_10gR2> SELECT count(*)
2 FROM test ;

COUNT(*)
----------
200000

1 row selected.

SQL_10gR2> SELECT count(*)
2 FROM datos_1 ;

COUNT(*)
----------
0

1 row selected.

SQL_10gR2> SELECT count(*)
2 FROM datos_2 ;

COUNT(*)
----------
0

1 row selected.

Como podemos ver, con el Exchange Partition no tardamos casi nada en cargar los datos en la tabla particionada ya que en realidad no estamos cargando los datos, simplemente se modifica el diccionario de datos.

Pueden notar que agregué la sentencia WITHOUT VALIDATION. Que es ésto? WITHOUT VALIDATION suele ser una operación rápida porque sólo realiza modificaciones en el diccionario de datos. Si la tabla o tabla particionada que colocamos en el Exchange Partition tiene una primary key o unique constraint habilitado, entonces el Exchange Partition se realiza como WITH VALIDATION para mantener la integridad de las constraints.

Vamos a ejecutar nuevamente los 2 alter anteriores sin la sentencia WITHOUT VALIDATION...

SQL_10gR2> ALTER TABLE test
2 EXCHANGE PARTITION year_2000
3 WITH table datos_1 ;

Table altered.

Elapsed: 00:00:01.00

SQL_10gR2> ALTER TABLE test
2 EXCHANGE PARTITION year_2001
3 WITH table datos_2 ;

Table altered.

Elapsed: 00:00:01.05

Si hubiera ejecutado esos alter con un Trace, el reporte del Trace me mostraría, entre otras sentencias, las siguientes...

select 1
from
"DATOS_1" where TBL$OR$IDX$PART$NUM("TEST", 0, 3,1048576,"FECHA") != :1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 1 0 0
Fetch 1 0.04 0.04 0 65 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.04 0.04 0 66 0 0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 81 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS FULL DATOS_1 (cr=65 pr=0 pw=0 time=44582 us)


select 1
from
"DATOS_2" where TBL$OR$IDX$PART$NUM("TEST", 0, 3,1048576,"FECHA") != :1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 1 0 0
Fetch 1 0.04 0.04 0 65 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.04 0.04 0 66 0 0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 81 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS FULL DATOS_2 (cr=65 pr=0 pw=0 time=46957 us)

Antes que nada, notamos que los alter se ejecutaron en mayor tiempo, cierto? De las consultas que observamos del Trace, vemos que se está realizando un FULL SCAN de las tablas y que se está ejecutando una función en el WHERE de cada consulta. Imagínense si tenemos que realizar ésta clase de procesos en ambientes con gran volumen de datos y en donde el sistema se encuentra saturado por el I/O a disco.

Qué sucede si no tenemos los datos separados por año en distintas tablas, y en cambio, tenemos todos los datos en una misma tabla? Bueno, tomando como ejemplo la tabla TEST que acabamos de cargar, podríamos realizar lo siguiente...

SQL_10gR2> CREATE TABLE test_2
2 ( id, fecha )
3 PARTITION BY RANGE ( fecha )
4 (
5 PARTITION year_2000 VALUES LESS THAN ( timestamp'2000-12-02 00:00:00' ),
6 PARTITION year_2001 VALUES LESS THAN ( timestamp'2001-10-10 00:00:00' )
7 )
8 AS
9 SELECT *
10 FROM test ;

Table created.

Elapsed: 00:00:05.04

SQL_10gR2> DROP TABLE test ;

Table dropped.

SQL_10gR2> ALTER TABLE test_2 RENAME TO test ;

Table altered.

SQL_10gR2> SELECT count(*)
2 FROM test ;

COUNT(*)
----------
200000

1 row selected.

jueves, 20 de septiembre de 2007

Buscar valores NULL en forma eficiente

Un problema que veo seguidamente, se relaciona con la escritura de consultas que buscan valores NULL en una tabla. Este es un problema muy común.
A continuación vamos a ver algunas soluciones que podemos implementar para buscar los valores NULL en forma eficiente y evitar lecturas innecesarias de bloques de datos.

Veamos algunos ejemplos:

SQL_9iR2> CREATE TABLE test AS
2 SELECT decode(mod(rownum,100),0,null,level) id, 'nom_'||level nom
3 FROM dual
4 CONNECT BY level <= 1000000 ;

Table created.

SQL_9iR2> EXEC dbms_stats.GATHER_TABLE_STATS(USER,'TEST') ;

PL/SQL procedure successfully completed.

Veamos la cantidad de valores NULL que tiene la tabla que creamos:

SQL_9iR2> SELECT count(*)
2 FROM test
3 WHERE id IS NULL ;

COUNT(*)
----------
10000

1 row selected.

Veamos cómo se ejecuta internamente esa consulta:

SQL_9iR2> explain plan for
2 SELECT count(*)
3 FROM test
4 WHERE id IS NULL ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 304 |
| 1 | SORT AGGREGATE | | 1 | 7 | |
|* 2 | TABLE ACCESS FULL | TEST | 10000 | 70000 | 304 |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("TEST"."ID" IS NULL)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.16 0.16 3144 3150 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.17 0.16 3144 3150 0 1

Observamos que obviamente está realizando un Full Scan. Qué sucede si creamos un índice B*Tree?

SQL_9iR2> CREATE INDEX test_id_idx ON test(id) ;

Index created.

SQL_9iR2> EXEC dbms_stats.GATHER_TABLE_STATS(USER,'TEST',cascade=>true) ;

PL/SQL procedure successfully completed.

SQL_9iR2> explain plan for
2 SELECT count(*)
3 FROM test
4 WHERE id IS NULL ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 304 |
| 1 | SORT AGGREGATE | | 1 | 7 | |
|* 2 | TABLE ACCESS FULL | TEST | 10000 | 70000 | 304 |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("TEST"."ID" IS NULL)

Porqué nuestra consulta no está accediendo a través del índice? por la simple razón de que los índices B*Tree no indexan los valores NULL. Como estamos realizando un COUNT buscando la cantidad de valores NULL de la tabla, el CBO sabe que si accede por índice, la consulta nos va a retornar un resultado erróneo ya que no existen valores NULL en el índice. Es por eso, que en vez de acceder por índice, realiza un Full Scan de la tabla. Esto tiene como consecuencia una lectura innecesaria de bloques por no haber accedido a través de un índice.

Una solución para éste problema sería crear un FBI (Function Based-Index) para indexar sólo los valores NULL de la columna:

SQL_9iR2> CREATE INDEX test_id_nulo_idx ON test(NVL(id,'nulo')) ;

Index created.

SQL_9iR2> EXEC dbms_stats.GATHER_TABLE_STATS(USER,'TEST',cascade=>true) ;

PL/SQL procedure successfully completed.

SQL_9iR2> explain plan for
2 SELECT count(*)
3 FROM test
4 WHERE NVL(id,'nulo') = 'nulo' ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 27 |
| 1 | SORT AGGREGATE | | 1 | 7 | |
|* 2 | INDEX RANGE SCAN | TEST_ID_NULO_IDX | 10001 | 70007 | 27 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access(NVL("TEST"."ID",'nulo')='nulo')

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 1 26 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 1 26 0 1


Veamos qué sucede si creamos un índice Bitmap:

SQL_9iR2> DROP INDEX test_id_idx ;

Index dropped.

SQL_9iR2> CREATE BITMAP INDEX test_id_bitmap ON test(id) ;

Index created.

SQL_9iR2> EXEC dbms_stats.GATHER_TABLE_STATS(USER,'TEST',cascade=>true) ;

PL/SQL procedure successfully completed.

SQL_9iR2> explain plan for
2 SELECT count(*)
3 FROM test
4 WHERE id IS NULL ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 44 |
| 1 | SORT AGGREGATE | | 1 | 7 | |
| 2 | BITMAP CONVERSION COUNT | | | | |
|* 3 | BITMAP INDEX SINGLE VALUE| TEST_ID_BITMAP | | | |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("TEST"."ID" IS NULL)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 1 5 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 1 5 0 1

Observamos que también podemos utilizar índices Bitmap para buscar valores NULL ya que ésta clase de índices SI indexan éstos valores.
Lo importante a tener en cuenta si utilizamos éste índice es que cada modificación que se realiza sobre el índice, requiere un gran trabajo del sistema que si utilizamos índices B*tree. Por otro lado, si a ésto le sumamos las modificaciones concurrentes que se realizan sobre la columna indexada, puede llegar a ser mortal para el sistema.

miércoles, 19 de septiembre de 2007

Problemas en Vistas Materializadas - Parte 1

Suele pasarnos que cuando queremos crear Vistas Materializadas, suelen haber errores y no logramos crearlas. O bien, logramos crearlas pero no suelen estar optimizadas para las capacidades que nosotros queremos. Para analizar y optimizar nuestras vistas materializadas, vamos a utilizar el paquete DBMS_MVIEW.EXPLAIN_MVIEW y la tabla MV_CAPABILITIES_TABLE.

Veamos un ejemplo:

Primero vamos a crear las tablas con los datos que vamos a utilizar para crear nuestra vista materializada:

SQL_10gR2> CREATE TABLE test1 AS
2 SELECT level id, level*level total
3 FROM dual
4 CONNECT BY level <= 10 ;

Table created.

SQL_10gR2> CREATE TABLE test2 AS
2 SELECT level id, 'nom_'||level nom
3 FROM dual
4 CONNECT BY level <= 19 ;

Table created.

SQL_10gR2> INSERT INTO test2 VALUES(19,'nom_19') ;

1 row created.

SQL_10gR2> COMMIT ;

Commit complete.

SQL_10gR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'TEST1') ;

PL/SQL procedure successfully completed.

SQL_10gR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'TEST2') ;

PL/SQL procedure successfully completed.

Los datos quedan de la siguiente manera en las tablas:

SQL_10gR2> SELECT *
2 FROM test1 ;

ID TOTAL
---------- ----------
1 1
2 4
3 9
4 16
5 25
6 36
7 49
8 64
9 81
10 100

10 rows selected.

SQL_10gR2> SELECT *
2 FROM test2 ;

ID NOM
---------- ----------------
1 nom_1
2 nom_2
3 nom_3
4 nom_4
5 nom_5
6 nom_6
7 nom_7
8 nom_8
9 nom_9
10 nom_10
11 nom_11
12 nom_12
13 nom_13
14 nom_14
15 nom_15
16 nom_16
17 nom_17
18 nom_18
19 nom_19
19 nom_19


20 rows selected.

Bien, supongamos que queremos crear una vista materializada con la opción de REFRESH FAST ON DEMAND sobre la siguiente consulta:

SQL_10gR2> SELECT test2.id,
2 test2.nom,
3 SUM(test1.total) sum_total
4 FROM test1,
5 test2
6 WHERE test1.id = test2.id(+)
7 GROUP BY test2.id,
8 test2.nom
9 ORDER BY id ;

ID NOM SUM_TOTAL
---------- ---------- ----------
1 nom_1 1
2 nom_2 4
3 nom_3 9
4 nom_4 16
5 nom_5 25
6 nom_6 36
7 nom_7 49
8 nom_8 64
9 nom_9 81
10 nom_10 100

10 rows selected.

Antes de crear la vista materializada, vamos a crear las tablas de Log para el refresh fast:

SQL_10gR2> CREATE MATERIALIZED VIEW LOG ON test1
2 WITH ROWID, SEQUENCE (total)
3 INCLUDING NEW VALUES ;

Materialized view log created.

SQL_10gR2> CREATE MATERIALIZED VIEW LOG ON test2
2 WITH ROWID, SEQUENCE (id,nom)
3 INCLUDING NEW VALUES ;

Materialized view log created.

Ahora creamos la vista materializada:

SQL_10gR2> CREATE MATERIALIZED VIEW TEST_MV
2 NOCOMPRESS
3 LOGGING
4 BUILD IMMEDIATE
5 USING INDEX
6 REFRESH FAST ON DEMAND
7 USING DEFAULT LOCAL ROLLBACK SEGMENT
8 DISABLE QUERY REWRITE
9 AS
10 SELECT test2.id,
11 test2.nom,
12 SUM(test1.total) sum_total
13 FROM test1,
14 test2
15 WHERE test1.id = test2.id(+)
16 GROUP BY test2.id,
17 test2.nom
18 ORDER BY id ;
FROM test1,
*
ERROR at line 13:
ORA-12015: cannot create a fast refresh materialized view from a complex query

Observamos que no podemos crear la vista materializada usando fast refresh sobre nuestra consulta. Pero cómo identificamos cuales son los errores por los cuales no podemos crear la vista materializada?
Lo que vamos a utilizar es el procedimiento EXPLAIN_MVIEW del paquete DBMS_MVIEW. Lo ejecutamos colocando como parámetro del procedimiento la consulta que vamos a utilizar en la vista materializada:

SQL_10gR2> BEGIN
2
3 DBMS_MVIEW.EXPLAIN_MVIEW
4 (
5 'SELECT test2.id,
6 test2.nom,
7 SUM(test1.total) sum_total
8 FROM test1,
9 test2
10 WHERE test1.id = test2.id(+)
11 GROUP BY test2.id,
12 test2.nom
13 ORDER BY id '
14 ) ;
15
16 END ;
17 /
BEGIN
*
ERROR at line 1:
ORA-30377: table SQL_10GR2.MV_CAPABILITIES_TABLE not found
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_XRWMV", line 23
ORA-06512: at "SYS.DBMS_XRWMV", line 43
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3007

El error que estamos viendo se debe a que no tenemos que creada en nuestro esquema la tabla MV_CAPABILITIES_TABLE que se encuentra en $ORACLE_HOME/rdbms/admin (el script se llama utlxmv.sql) que utiliza el procedimiento para guardar la información resultante de la ejecución.

Creamos la tabla MV_CAPABILITIES_TABLE:

SQL_10gR2> @$ORACLE_HOME/rdbms/admin/utlxmv.sql

Table created.

Ejecutamos nuevamente el procedimiento:

SQL_10gR2> BEGIN
2
3 DBMS_MVIEW.EXPLAIN_MVIEW
4 (
5 'SELECT test2.id,
6 test2.nom,
7 SUM(test1.total) sum_total
8 FROM test1,
9 test2
10 WHERE test1.id = test2.id(+)
11 GROUP BY test2.id,
12 test2.nom
13 ORDER BY id '
14 ) ;
15
16 END ;
17 /

PL/SQL procedure successfully completed.

Ahora consultamos la tabla:

SQL_10gR2> SELECT capability_name, possible, related_text, msgtxt
2 FROM mv_capabilities_table ;

CAPABILITY_NAME P RELATED_TE MSGTXT
------------------------------ - ---------- ------------------------------
PCT N
REFRESH_COMPLETE Y
REFRESH_FAST N
REWRITE Y
PCT_TABLE N TEST1 relation is not a partitioned
table

PCT_TABLE N TEST2 relation is not a partitioned
table

REFRESH_FAST_AFTER_INSERT N outer join in mv
REFRESH_FAST_AFTER_ONETAB_DML N see the reason why REFRESH_FAS
T_AFTER_INSERT is disabled

REFRESH_FAST_AFTER_ANY_DML N see the reason why REFRESH_FAS
T_AFTER_ONETAB_DML is disabled

REFRESH_FAST_PCT N PCT is not possible on any of
the detail tables in the mater
ialized view

REWRITE_FULL_TEXT_MATCH Y
REWRITE_PARTIAL_TEXT_MATCH Y
REWRITE_GENERAL Y
REWRITE_PCT N general rewrite is not possibl
e or PCT is not possible on an
y of the detail tables

PCT_TABLE_REWRITE N TEST1 relation is not a partitioned
table

PCT_TABLE_REWRITE N TEST2 relation is not a partitioned
table


16 rows selected.

Lo que nos muestra esta consulta es lo siguiente:
- Un listado de todas las capacidades de la vista materializada (columna CAPABILITY_NAME).
- Por cada capacidad, nos muestra si es posible o no (columna POSSIBLE con valores 'Y' o 'N').
- Si la capacidad no es posible, nos muestra porque no es posible (columna MSGTXT).

Como nosotros queremos crear la vista materializada con la opción de REFRESH FAST ON DEMAND, vemos la columna CAPABILITY_NAME y observamos los valores de la columna MSGTXT para lo que tiene que ver con REFRESH_FAST_%.
Podemos ver que en REFRESH_FAST_AFTER_INSERT vemos que dice "outer join in mv". Como nosotros tenemos un outer join en la vista materializada, no podemos realizar refresh fast porque existen ciertas restricciones que debemos cumplir y que en éste ejemplo no estamos cumpliendo. Una de esas condiciones dice que las columnas que contienen el outer join en la condición de join de la consulta, tienen que tener creadas constraints primary key o índices unique key.
En la TABLA2 de nuestro ejemplo, tenemos un valor duplicado (el ID 19). Veamos qué sucede si eliminamos uno de los ID 19 y creamos una primary key en la tabla TEST2 por el campo ID:

SQL_10gR2> DELETE FROM test2
2 WHERE id = 19
3 AND rownum = 1 ;

1 row deleted.

SQL_10gR2> COMMIT ;

Commit complete.

SQL_10gR2> ALTER TABLE test2 ADD CONSTRAINT id_2_pk PRIMARY KEY (id) ;

Table altered.

SQL_10gR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'TEST2', cascade => true) ;

PL/SQL procedure successfully completed.

SQL_10gR2> SELECT *
2 FROM test2 ;

ID NOM
---------- ----------
1 nom_1
2 nom_2
3 nom_3
4 nom_4
5 nom_5
6 nom_6
7 nom_7
8 nom_8
9 nom_9
10 nom_10
11 nom_11
12 nom_12
13 nom_13
14 nom_14
15 nom_15
16 nom_16
17 nom_17
18 nom_18
19 nom_19

19 rows selected.

Bien, ejecutamos nuevamente el procedimiento EXPLAIN_MVIEW:

SQL_10gR2> TRUNCATE TABLE mv_capabilities_table ;

Table truncated.

SQL_10gR2> BEGIN
2
3 DBMS_MVIEW.EXPLAIN_MVIEW
4 (
5 'SELECT test2.id,
6 test2.nom,
7 SUM(test1.total) sum_total
8 FROM test1,
9 test2
10 WHERE test1.id = test2.id(+)
11 GROUP BY test2.id,
12 test2.nom
13 ORDER BY id '
14 ) ;
15
16 END ;
17 /

PL/SQL procedure successfully completed.

Consultamos nuevamente la tabla:

SQL_10gR2> SELECT capability_name, possible, related_text, msgtxt
2 FROM mv_capabilities_table ;

CAPABILITY_NAME P RELATED_TE MSGTXT
------------------------------ - ---------- ------------------------------
PCT N
REFRESH_COMPLETE Y
REFRESH_FAST N
REWRITE Y
PCT_TABLE N TEST1 relation is not a partitioned
table

PCT_TABLE N TEST2 relation is not a partitioned
table

REFRESH_FAST_AFTER_INSERT N SQL_10GR2. mv log does not have all neces
TEST1 sary columns

REFRESH_FAST_AFTER_ONETAB_DML N SUM_TOTAL SUM(expr) without COUNT(expr)
REFRESH_FAST_AFTER_ONETAB_DML N see the reason why REFRESH_FAS
T_AFTER_INSERT is disabled

REFRESH_FAST_AFTER_ONETAB_DML N COUNT(*) is not present in the
select list


REFRESH_FAST_AFTER_ONETAB_DML N SUM(expr) without COUNT(expr)
REFRESH_FAST_AFTER_ANY_DML N see the reason why REFRESH_FAS
T_AFTER_ONETAB_DML is disabled

REFRESH_FAST_PCT N PCT is not possible on any of
the detail tables in the mater
ialized view

REWRITE_FULL_TEXT_MATCH Y
REWRITE_PARTIAL_TEXT_MATCH Y
REWRITE_GENERAL Y
REWRITE_PCT N general rewrite is not possibl
e or PCT is not possible on an
y of the detail tables

PCT_TABLE_REWRITE N TEST1 relation is not a partitioned
table

PCT_TABLE_REWRITE N TEST2 relation is not a partitioned
table


19 rows selected.

Podemos observar que el error anterior ya no aparece más. En REFRESH_FAST_AFTER_ONETAB_DML vemos el mensaje "COUNT(*) is not present in the select list". Esto nos está diciendo que debemos agregar a la consulta un COUNT(*).
Vamos a agregar el COUNT(*) en la consulta y realizar el mismo procedimiento de antes:

SQL_10gR2> TRUNCATE TABLE mv_capabilities_table ;

Table truncated.

SQL_10gR2> BEGIN
2
3 DBMS_MVIEW.EXPLAIN_MVIEW
4 (
5 'SELECT test2.id,
6 test2.nom,
7 SUM(test1.total) sum_total,
8 COUNT(*) cnt
9 FROM test1,
10 test2
11 WHERE test1.id = test2.id(+)
12 GROUP BY test2.id,
13 test2.nom
14 ORDER BY id '
15 ) ;
16
17 END ;
18 /

PL/SQL procedure successfully completed.

SQL_10gR2> SELECT capability_name, possible, related_text, msgtxt
2 FROM mv_capabilities_table ;

CAPABILITY_NAME P RELATED_TE MSGTXT
------------------------------ - ---------- ------------------------------
PCT N
REFRESH_COMPLETE Y
REFRESH_FAST N
REWRITE Y
PCT_TABLE N TEST1 relation is not a partitioned
table

PCT_TABLE N TEST2 relation is not a partitioned
table

REFRESH_FAST_AFTER_INSERT N SQL_10GR2. mv log does not have all neces
TEST1 sary columns

REFRESH_FAST_AFTER_ONETAB_DML N SUM_TOTAL SUM(expr) without COUNT(expr)
REFRESH_FAST_AFTER_ONETAB_DML N see the reason why REFRESH_FAS
T_AFTER_INSERT is disabled

REFRESH_FAST_AFTER_ONETAB_DML N SUM(expr) without COUNT(expr)
REFRESH_FAST_AFTER_ANY_DML N see the reason why REFRESH_FAS
T_AFTER_ONETAB_DML is disabled

REFRESH_FAST_PCT N PCT is not possible on any of
the detail tables in the mater
ialized view

REWRITE_FULL_TEXT_MATCH Y
REWRITE_PARTIAL_TEXT_MATCH Y
REWRITE_GENERAL Y
REWRITE_PCT N general rewrite is not possibl
e or PCT is not possible on an
y of the detail tables

PCT_TABLE_REWRITE N TEST1 relation is not a partitioned
table

PCT_TABLE_REWRITE N TEST2 relation is not a partitioned
table


18 rows selected.

Podemos observar que el error anterior ya no aparece más. Ahora en REFRESH_FAST_AFTER_ONETAB_DML vemos "SUM(expr) without COUNT(expr)". Como estamos utilizando la función SUM, debemos agregar un COUNT(expr) a la consulta:

SQL_10gR2> TRUNCATE TABLE mv_capabilities_table ;

Table truncated.

SQL_10gR2> BEGIN
2
3 DBMS_MVIEW.EXPLAIN_MVIEW
4 (
5 'SELECT test2.id,
6 test2.nom,
7 SUM(test1.total) sum_total,
8 COUNT(*) cnt,
9 COUNT(test1.total) cnt_sum
10 FROM test1,
11 test2
12 WHERE test1.id = test2.id(+)
13 GROUP BY test2.id,
14 test2.nom
15 ORDER BY id'
16 ) ;
17
18 END ;
19 /

PL/SQL procedure successfully completed.

SQL_10gR2> SELECT capability_name, possible, related_text, msgtxt
2 FROM mv_capabilities_table ;

CAPABILITY_NAME P RELATED_TE MSGTXT
------------------------------ - ---------- ------------------------------
PCT N
REFRESH_COMPLETE Y
REFRESH_FAST N
REWRITE Y
PCT_TABLE N TEST1 relation is not a partitioned
table

PCT_TABLE N TEST2 relation is not a partitioned
table

REFRESH_FAST_AFTER_INSERT N SQL_10GR2. mv log does not have all neces
TEST1 sary columns


REFRESH_FAST_AFTER_ONETAB_DML N see the reason why REFRESH_FAS
T_AFTER_INSERT is disabled

REFRESH_FAST_AFTER_ANY_DML N see the reason why REFRESH_FAS
T_AFTER_ONETAB_DML is disabled

REFRESH_FAST_PCT N PCT is not possible on any of
the detail tables in the mater
ialized view

REWRITE_FULL_TEXT_MATCH Y
REWRITE_PARTIAL_TEXT_MATCH Y
REWRITE_GENERAL Y
REWRITE_PCT N general rewrite is not possibl
e or PCT is not possible on an
y of the detail tables

PCT_TABLE_REWRITE N TEST1 relation is not a partitioned
table

PCT_TABLE_REWRITE N TEST2 relation is not a partitioned
table


16 rows selected.

Podemos observar que el error anterior ya no aparece más. El último error que nos falta solucionar para poder crear la vista materializada con refresh fast es el que aparece en REFRESH_FAST_AFTER_INSERT con el mensaje "mv log does not have all necessary columns". Al parecer creamos de forma incorrecta la tabla de Logs de la tabla TEST1 y nos está diciendo que tenemos que agregar a la tabla de Log todas las columnas que aparecen en nuestra consulta.
Lo que vamos a hacer es dropear la tabla de Log que habíamos creado y recrearla agregando una columna:

SQL_10gR2> DROP MATERIALIZED VIEW LOG on test1 ;

Materialized view log dropped.

SQL_10gR2> CREATE MATERIALIZED VIEW LOG ON test1
2 WITH ROWID, SEQUENCE (id,total)
3 INCLUDING NEW VALUES ;

Materialized view log created.

SQL_10gR2> TRUNCATE TABLE mv_capabilities_table ;

Table truncated.

SQL_10gR2> BEGIN
2
3 DBMS_MVIEW.EXPLAIN_MVIEW
4 (
5 'SELECT test2.id,
6 test2.nom,
7 SUM(test1.total) sum_total,
8 COUNT(*) cnt,
9 COUNT(test1.total) cnt_sum
10 FROM test1,
11 test2
12 WHERE test1.id = test2.id(+)
13 GROUP BY test2.id,
14 test2.nom
15 ORDER BY id'
16 ) ;
17
18 END ;
19 /

PL/SQL procedure successfully completed.

SQL_10gR2> SELECT capability_name, possible, related_text, msgtxt
2 FROM mv_capabilities_table ;

CAPABILITY_NAME P RELATED_TE MSGTXT
------------------------------ - ---------- ------------------------------
PCT N
REFRESH_COMPLETE Y
REFRESH_FAST Y
REWRITE Y
PCT_TABLE N TEST1 relation is not a partitioned
table

PCT_TABLE N TEST2 relation is not a partitioned
table

REFRESH_FAST_AFTER_INSERT Y
REFRESH_FAST_AFTER_ONETAB_DML Y
REFRESH_FAST_AFTER_ANY_DML Y
REFRESH_FAST_PCT N PCT is not possible on any of
the detail tables in the mater
ialized view

REWRITE_FULL_TEXT_MATCH Y
REWRITE_PARTIAL_TEXT_MATCH Y
REWRITE_GENERAL Y
REWRITE_PCT N general rewrite is not possibl
e or PCT is not possible on an
y of the detail tables

PCT_TABLE_REWRITE N TEST1 relation is not a partitioned
table

PCT_TABLE_REWRITE N TEST2 relation is not a partitioned
table


16 rows selected.

Buenísimo! Al parecer ya estamos en condiciones de crear la vista materializada utilizando la opción de REFRESH FAST ON DEMAND. Veamos...

ihubowner@ihub1t> CREATE MATERIALIZED VIEW TEST_MV
2 NOCOMPRESS
3 LOGGING
4 BUILD IMMEDIATE
5 USING INDEX
6 REFRESH FAST ON DEMAND
7 USING DEFAULT LOCAL ROLLBACK SEGMENT
8 DISABLE QUERY REWRITE
9 AS
10 SELECT test2.id,
11 test2.nom,
12 SUM(test1.total) sum_total,
13 COUNT(*) cnt,
14 COUNT(test1.total) cnt_sum
15 FROM test1,
16 test2
17 WHERE test1.id = test2.id(+)
18 GROUP BY test2.id,
19 test2.nom
20 ORDER BY id ;

Materialized view created.

lunes, 17 de septiembre de 2007

Estadísticas en tablas temporales

Lo ideal es tener estadísticas "representativas" en las tablas temporales. Suele suceder que muchas veces no sabemos la cantidad de registros que se van a cargar en las tablas temporales, pero podemos tener estadísticas que representen la cantidad de registros que en general suelen cargarse en las tablas. Por otro lado, hay ocasiones en que no sabemos ni eso. No sabemos que cantidad de registros promedio se van a cargar en las tablas. Cuando sucede ésto podemos pensar en algunas alternativas.

Por default, Oracle asume que las tablas temporales van a contener X cantidad de registros (donde X representa a 8,168 de registros en las bases de datos que contiene bloques de 8K).
Como éste valor por default suele ser incierto en las ejecuciones de nuestros procesos utilizando tablas temporales, tenemos que ayudar al CBO en recolectar estadísticas reales de las tablas temporales.

Antes que nada, veamos las estadísticas por default que utiliza el CBO:

SQL_9iR2> SHOW PARAMETER DB_BLOCK_SIZE

db_block_size integer 8192

SQL_9iR2> CREATE GLOBAL TEMPORARY TABLE test_temp ( id NUMBER ) ;

Table created.

SQL_9iR2> EXPLAIN PLAN FOR
2 SELECT /*+ ALL_ROWS */ id
3 FROM test_temp ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 103K| 11 |
| 1 | TABLE ACCESS FULL | TEST_TEMP | 8168 | 103K| 11 |
--------------------------------------------------------------------

Observamos lo que explicamos anteriormente. El CBO estima las estadísticas en base al parámetro DB_BLOCK_SIZE seteado en la base de datos.

Las 3 soluciones disponibles en lo que concierne a las estadísticas en las tablas temporales son:
1.- Usar el hint DYNAMIC_SAMPLING
2.- Usar el hint CARDINALITY
3.- Usar DBMS_STATS.SET_TABLE_STATS

Veamos cada una de éstas soluciones:

1.- Usar el hint DYNAMIC_SAMPLING

Dynamic Sampling le dice a Oracle que realice rápidamente un "escaneo completo" de la tabla para obtener estadísticas que representen la realidad.

SQL_9iR2> EXPLAIN PLAN FOR
2 SELECT /*+ DYNAMIC_SAMPLING(2) */ id
3 FROM test_temp ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 11 |
| 1 | TABLE ACCESS FULL | TEST_TEMP | 1 | 13 | 11 |
--------------------------------------------------------------------

Utilizando Dynamic Sampling (nivel 2), podemos ver que las estadísticas son más representativas de la realidad.

Veamos qué sucede si insertamos 15,000 registros a la tabla temporal:

SQL_9iR2> INSERT INTO test_temp
2 SELECT level
3 FROM dual
4 CONNECT BY level <= 15000 ;

15000 rows created.

SQL_9iR2> EXPLAIN PLAN FOR
2 SELECT /*+ DYNAMIC_SAMPLING(2) */ id
3 FROM test_temp ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15000 | 190K| 11 |
| 1 | TABLE ACCESS FULL | TEST_TEMP | 15000 | 190K| 11 |
--------------------------------------------------------------------

En 10g, el CBO obtiene por default, estadísticas utilizando Dynamic Sampling (nivel 2) sobre las tablas temporales.

SQL_10gR2> CREATE GLOBAL TEMPORARY TABLE test_temp ( id NUMBER ) ;

Table created.

SQL_10gR2> EXPLAIN PLAN FOR
2 SELECT id
3 FROM test_temp ;

Explained.

SQL_10gR2> @explains
Plan hash value: 1559088631

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST_TEMP | 1 | 13 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

2.- Usar el hint CARDINALITY

Otra solución es utilizar el hint CARDINALITY, que le dice a Oracle la cantidad de registros que tiene la tabla. Pero éste valor debemos colocarlos nosotros. No se obtiene dinámicamente.

SQL_9iR2> EXPLAIN PLAN FOR
2 SELECT /*+ CARDINALITY(test_temp 10000) */ id
3 FROM test_temp ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 126K| 11 |
| 1 | TABLE ACCESS FULL | TEST_TEMP | 10000 | 126K| 11 |
--------------------------------------------------------------------

3.- Usar DBMS_STATS.SET_TABLE_STATS

Por último, podemos utilizar el paquete DBMS_STATS, para cargar estadísticas representativas de la tabla temporal. El procedimiento SET_TABLE_STATS no analiza la tabla en cuestión, sino que setea las estadísticas de la tabla como nosotros queramos.
Podemos realizar ésto cuando creamos la tabla o luego de cargar datos en la tabla.
Si sabemos la cantidad de registros que puede llegar a tener la tabla temporal, podemos ejecutar éste procedimiento y setear las estadísticas a un valor representativo en base a los registros de la tabla.
Algo muy importante a tener en cuenta es que la ejecución de éste procedimiento implica un COMMIT implícito; por lo que tenemos que tener cuidado en el momento en el cual lo ejecutemos. Podrían ejecutarlo dentro de un procedimiento utilizando AUTONOMOUS_TRANSACTION para que no cree ningún tipo de conflictos.

SQL_9iR2> EXEC dbms_stats.set_table_stats( USER, 'TEST_TEMP', numrows => 10000 ) ;

PL/SQL procedure successfully completed.

SQL_9iR2> EXPLAIN PLAN FOR
2 SELECT id
3 FROM test_temp ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 126K| 11 |
| 1 | TABLE ACCESS FULL | TEST_TEMP | 10000 | 126K| 11 |
--------------------------------------------------------------------

viernes, 14 de septiembre de 2007

Las columnas más selectivas deberían ir al comienzo del índice?

El mito dice lo siguiente: Cuando creamos un índice, las columnas más selectivas (las columnas con la mayor cantidad de valores distintos) deberían ir al comienzo del índice.
Nuestro sentido común nos dice que es lógico crear los índices de esa forma; pero lo que no nos dice nuestro sentido común, es si en realidad éste mito es verdadero o falso.

La mejor manera de comprobar la veracidad de éste mito, es realizando un ejemplo:

SQL_9iR2> CREATE TABLE test
2 (nombre, edad, sexo)
3 AS
4 SELECT 'nom_'||level,
5 to_number(round(dbms_random.value(1,5))||round(dbms_random.value(1,5))),
6 decode(mod(level,2),1,'M','F')
7 FROM dual
8 CONNECT BY level <= 10000 ;

Table created.

Veamos la cantidad de valores distintos de cada columna:

SQL_9iR2> SELECT COUNT(DISTINCT nombre) nombre,
2 COUNT(DISTINCT edad) edad,
3 COUNT(DISTINCT sexo) sexo
4 FROM test ;

NOMBRE EDAD SEXO
---------- ---------- ----------
10000 25 2

1 row selected.

Con los valores que estamos viendo, nuestro sentido común nos diría que si tenemos que crear un índice sobre esas 3 columnas, las coloquemos en orden de selectividad (de las columnas con mayor cantidad de valores distintos a las de menor cantidad).

SQL_9iR2> CREATE INDEX test_selectivo_idx ON test(nombre,edad,sexo) ;

Index created.

El índice "test_selectivo_idx" que acabamos de crear es el que la gente está más acostumbrada a crear. Colocando las columnas con mayor cantidad de valores distintos en la cabecera del índice hasta llegar a la columna con menor cantidad de valores distintos.
Pero no sólo vamos a crear ese índice. También vamos a crear otro índice invirtiendo el orden de las columnas del índice anterior:

SQL_9iR2> CREATE INDEX test_no_selectivo_idx ON test(sexo,edad,nombre) ;

Index created.

Ahora vamos a analizar la estructura de los 2 índices para ver si el espacio que ocupa cada uno es el mismo:

SQL_9iR2> ANALYZE INDEX test_selectivo_idx VALIDATE STRUCTURE ;

Index analyzed.

SQL_9iR2> exec print_table('SELECT * FROM index_stats') ;
-----------------
HEIGHT : 2
BLOCKS : 40
NAME : TEST_SELECTIVO_IDX
PARTITION_NAME :
LF_ROWS : 10000
LF_BLKS : 35
LF_ROWS_LEN : 248894
LF_BLK_LEN : 7996
BR_ROWS : 34
BR_BLKS : 1
BR_ROWS_LEN : 543
BR_BLK_LEN : 8028
DEL_LF_ROWS : 0
DEL_LF_ROWS_LEN : 0
DISTINCT_KEYS : 10000
MOST_REPEATED_KEY : 1
BTREE_SPACE : 287888
USED_SPACE : 249437
PCT_USED : 87
ROWS_PER_KEY : 1
BLKS_GETS_PER_ACCESS : 3
PRE_ROWS : 0
PRE_ROWS_LEN : 0
OPT_CMPR_COUNT : 0
OPT_CMPR_PCTSAVE : 0
-----------------

PL/SQL procedure successfully completed.

SQL_9iR2> ANALYZE INDEX test_no_selectivo_idx VALIDATE STRUCTURE ;

Index analyzed.

SQL_9iR2> exec print_table('SELECT * FROM index_stats') ;
-----------------
HEIGHT : 2
BLOCKS : 40
NAME : TEST_NO_SELECTIVO_IDX
PARTITION_NAME :
LF_ROWS : 10000
LF_BLKS : 35
LF_ROWS_LEN : 248894
LF_BLK_LEN : 7996
BR_ROWS : 34
BR_BLKS : 1
BR_ROWS_LEN : 673
BR_BLK_LEN : 8028
DEL_LF_ROWS : 0
DEL_LF_ROWS_LEN : 0
DISTINCT_KEYS : 10000
MOST_REPEATED_KEY : 1
BTREE_SPACE : 287888
USED_SPACE : 249567
PCT_USED : 87
ROWS_PER_KEY : 1
BLKS_GETS_PER_ACCESS : 3
PRE_ROWS : 0
PRE_ROWS_LEN : 0
OPT_CMPR_COUNT : 2
OPT_CMPR_PCTSAVE : 19
-----------------

PL/SQL procedure successfully completed.

Vemos que los índices usan la misma cantidad de espacio (hay alguna diferencia mínima en bytes, pero en general no hay diferencia en cuanto al espacio que ocupan). Por otro lado, el orden en el cual se encuentran las columnas en un índice, pueden ser o no, mejor candidatos para usar "Index Key Compression" (observando el valor OPT_CMPR_PCTSAVE de la vista index_stats).

Lo que vamos a realizar ahora es un TKPROF ejecutando las 2 consultas dentro de un bloque PL/SQL para ver la diferencia en cuanto a la performance de utilizar cada uno de los índices:

SQL_9iR2> EXEC dbms_stats.GATHER_TABLE_STATS(USER,'TEST',cascade => true) ;

PL/SQL procedure successfully completed.


SQL_9iR2> ALTER SESSION SET SQL_TRACE = TRUE ;

Session altered.

SQL_9iR2> DECLARE
2 l_count PLS_INTEGER ;
3 BEGIN
4 FOR i IN ( SELECT * FROM test ) LOOP
5
6 SELECT /*+ INDEX(test test_selectivo_idx) */ COUNT(*)
7 INTO l_count
8 FROM test
9 WHERE nombre = i.nombre
10 AND edad = i.edad
11 AND sexo = i.sexo ;
12
13 SELECT /*+ INDEX(test test_no_selectivo_idx) */ COUNT(*)
14 INTO l_count
15 FROM test
16 WHERE nombre = i.nombre
17 AND edad = i.edad
18 AND sexo = i.sexo ;
19
20 END LOOP ;
21 END ;
22 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.08

SQL_9iR2> ALTER SESSION SET SQL_TRACE = FALSE ;

Session altered.

Veamos el reporte del TKPROF que generamos:

SELECT /*+ INDEX(test test_selectivo_idx) */ COUNT(*)
FROM
TEST WHERE NOMBRE = :B3 AND EDAD = :B2 AND SEXO = :B1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10000 0.17 0.12 0 0 0 0
Fetch 10000 0.13 0.09 0 20034 0 10000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 20001 0.30 0.22 0 20034 0 10000

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 137 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
10000 SORT AGGREGATE
10000 INDEX RANGE SCAN TEST_SELECTIVO_IDX (object id 82961)

SELECT /*+ INDEX(test test_no_selectivo_idx) */ COUNT(*)
FROM
TEST WHERE NOMBRE = :B3 AND EDAD = :B2 AND SEXO = :B1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10000 0.19 0.12 0 0 0 0
Fetch 10000 0.12 0.09 0 20034 0 10000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 20001 0.31 0.21 0 20034 0 10000

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 137 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
10000 SORT AGGREGATE
10000 INDEX RANGE SCAN TEST_NO_SELECTIVO_IDX (object id 82962)

Como podemos ver en el reporte, leen exactamente la misma cantidad de bloques de datos y se ejecutan aproximadamente en el mismo tiempo de CPU y elapsed. En conclusión, los 2 índices son exactamente iguales.

Este ejemplo nos sirve como prueba de que el mito no es cierto. No hay diferencias en cuanto a performance en colocar las columnas menos selectivas en la cabecera del índice o las más selectivas.

La realidad es que tenemos que decidir en qué orden colocar las columnas en el índice en base a cómo vamos a acceder a esas columnas en nuestras consultas. Si tenemos el índice compuesto por (sexo,edad,nombre) y accedemos sólo por la columna sexo, no tiene sentido crear un índice compuesto B*Tree y quizás nos convendría crear un índice Bitmap sólo por la columna sexo.

Por ejemplo, si tenemos estas 2 consultas ...

SELECT d FROM test WHERE a = :a AND b = :b ;

SELECT d FROM test WHERE b = :b ;

... lo más razonable es crear un índice en el siguiente orden: (b,a). De ésta manera, ese índice puede ser usado en las 2 consultas.

Diferencias entre COUNT(1) y COUNT(*) - Parte 1

Miles y miles y miles de veces veo que se arman consultas SQL que contiene el COUNT(1). Este es un viejo mito. Muchos piensan que colocar COUNT(1) en vez del COUNT(*) mejora la performance de la consulta.... pero en realidad mejora la performance?

Veamos...

SQL_9iR2> CREATE TABLE test AS
2 SELECT level id, 'texto_'||level texto
3 FROM dual
4 CONNECT BY level <= 100000 ; Table created. SQL_9iR2> EXEC dbms_stats.GATHER_TABLE_STATS(USER,'TEST') ;

PL/SQL procedure successfully completed.

Bien, ahora ejecutemos las 2 consultas:

SQL_9iR2> ALTER SESSION SET SQL_TRACE = TRUE ;

Session altered.

SQL_9iR2> SELECT COUNT(1)
2 FROM test ;

COUNT(1)
----------
100000

1 row selected.

SQL_9iR2> SELECT COUNT(*)
2 FROM test ;

COUNT(*)
----------
100000

1 row selected.

SQL_9iR2> ALTER SESSION SET SQL_TRACE = FALSE ;

Session altered.

Veamos lo que nos muestra el TKPROF:

select count(1)
from
test


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.01 0 306 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.01 0 306 0 1

select count(*)
from
test


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.01 0 306 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.01 0 306 0 1

Observamos que en las 2 consultas tenemos la misma cantidad de lecturas de bloques (disk, query, current) y el mismo tiempo de elapsed y cpu.
Las 2 consultas son idénticas y no hay un incremento en la performance por utilizar el COUNT(1) en vez del COUNT(*).

Incluso podría poner cualquier cosa en el COUNT... obtendríamos los mismos resultados:

SELECT COUNT(2222222)
FROM
TEST

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.01 0 306 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.01 0 306 0 1

SELECT COUNT('EJEMPLO')
FROM
TEST

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.01 0 306 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.01 0 306 0 1

SELECT COUNT(12345)
FROM
TEST

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.01 0 306 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.01 0 306 0 1

Bien, por último veamos los siguiente. Qué sucede si realizo un COUNT de alguna de las columnas de la tabla? Veamos...

SQL_9iR2> SELECT COUNT(texto)
2 FROM test ;

COUNT(TEXTO)
------------
100000

1 row selected.

La consulta nos devolvió la cantidad de registros totales de la tabla. Ahora veamos qué me devuelve la consulta si la columna TEXTO tiene valores nulos.

SQL_9iR2> UPDATE test
2 SET texto = NULL
3 WHERE id > 50000 ;

50000 rows updated.

SQL_9iR2> EXEC dbms_stats.GATHER_TABLE_STATS(USER,'TEST') ;

PL/SQL procedure successfully completed.

SQL_9iR2> SELECT COUNT(texto)
2 FROM test ;

COUNT(TEXTO)
------------
50000

1 row selected.

Como podemos observar, el COUNT sobre una columna sólo cuenta la cantidad de valores que no son nulos. Por lo tanto, tenemos que tener cuidado a la hora de realizar un COUNT y tener en cuenta estos pequeños detalles.

BULK COLLECT con miles de registros y la cláusula LIMIT

Veo muchísimas veces, que cuando se utiliza BULK COLLECT en los códigos PL/SQL, no se coloca la cláusula LIMIT. Cuando no utilizamos ésta cláusula, lo único que ganamos es arruinar la memoria del proceso.
La cláusula LIMIT nos permite definir la cantidad de 'datos' que vamos a colocar en memoria. Cuando utilizamos LIMIT, lo ideal es definirlo en un valor entre 100 a 500. Personalmente, elijo el valor 100 porque en base a mi experiencia suele ser el mejor valor. Pero porque elijo un valor tan chico y no 1000 o 5000 por ejemplo? Bueno, por la simple razón que manejar gran cantidad de datos en memoria es más costoso que manejar poca cantidad.
Si elegimos un valor alto en el LIMIT, puede darse 3 casos:
- Que nuestro código se ejecute más rápidamente (improbable).
- Que nuestro código se ejecute en igual tiempo (improbable).
- Que nuestro código se ejecute más lentamente (probable).

Veamos un ejemplo para entender mejor las consecuencia de no utilizar la cláusula LIMIT.

Primero creamos una tabla TEST con 10.000 registros (para ver la diferencia del uso de la cláusula LIMIT, no hace falta realizar el ejemplo con millones de registros. Con unos miles de registros nos alcanza para entender el tema) y una tabla TEST_2 con la estructura de la tabla TEST pero sin registros:

SQL_9iR2> CREATE TABLE test AS
2 SELECT level id , 'oracle_'||level texto
3 FROM dual
4 CONNECT BY level <= 10000 ;

Table created.

SQL_9iR2> CREATE TABLE test_2 AS
2 SELECT *
3 FROM test
4 WHERE 1 = 2 ;

Table created.

Antes de ejecutar el primer código, voy a liberar la memoria que ya no se está utilizando en mi sesión actual para poder ver claramente la diferencia de las estadísticas tomadas en cada ejecución.

SQL_9iR2> exec dbms_session.FREE_UNUSED_USER_MEMORY ;

PL/SQL procedure successfully completed.

Ejecutamos un código PL/SQL con Bulk Collect pero SIN la cláusula LIMIT:

SQL_9iR2> DECLARE
2 TYPE t_array_number IS TABLE OF NUMBER ;
3 TYPE t_array_varchar2 IS TABLE OF VARCHAR2(50) ;
4 t_array_id t_array_number ;
5 t_array_texto t_array_varchar2 ;
6 CURSOR cur IS
7 SELECT * FROM test ;
8 BEGIN
9 OPEN cur ;
10 LOOP
11
12 FETCH cur BULK COLLECT INTO t_array_id , t_array_texto ;
13
14 FORALL i IN 1 .. t_array_id.COUNT
15 INSERT INTO test_2
16 VALUES (t_array_id(i) , t_array_texto(i)) ;
17
18 EXIT WHEN cur%NOTFOUND ;
19
20 END LOOP ;
21 COMMIT ;
22 CLOSE cur ;
23 END ;
24 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04

Antes de ejecutar el segundo código, voy a liberar nuevamente la memoria que ya no se está utilizando en mi sesión actual.

SQL_9iR2> exec dbms_session.FREE_UNUSED_USER_MEMORY ;

PL/SQL procedure successfully completed.

Ahora ejecutamos el mismo código PL/SQL con Bulk Collect pero CON la cláusula LIMIT:

SQL_9iR2> DECLARE
2 TYPE t_array_number IS TABLE OF NUMBER ;
3 TYPE t_array_varchar2 IS TABLE OF VARCHAR2(50) ;
4 t_array_id t_array_number ;
5 t_array_texto t_array_varchar2 ;
6 CURSOR cur IS
7 SELECT * FROM test ;
8 BEGIN
9 OPEN cur ;
10 LOOP
11
12 FETCH cur BULK COLLECT INTO t_array_id , t_array_texto LIMIT 100 ;
13
14 FORALL i IN 1 .. t_array_id.COUNT
15 INSERT INTO test_2
16 VALUES (t_array_id(i) , t_array_texto(i)) ;
17
18 EXIT WHEN cur%NOTFOUND ;
19
20 END LOOP ;
21 COMMIT ;
22 CLOSE cur ;
23 END ;
24 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04

Podemos ver que en la segunda ejecución, estoy cargando en memoria 100 registros en cada Fetch que realizo. A diferencia de la primer ejecución, que carga todos los datos de una vez.

Veamos las estadísticas obtenidas de las 2 ejecuciones anteriores:

Nombre Ejecución_1 Ejecución_2 Diferencia
------------------------------ ----------- ----------- -----------
LATCH.kwqit: protect wakeup ti 1 0 -1
LATCH.simulator lru latch 1 0 -1
LATCH.spilled msgs queues list 1 0 -1
LATCH.transaction allocation 3 0 -3
LATCH.session timer 5 0 -5
LATCH.multiblock read objects 8 0 -8
LATCH.channel operations paren 11 0 -11
LATCH.child cursor hash table 20 8 -12
LATCH.Consistent RBA 56 6 -50
LATCH.lgwr LWN SCN 56 6 -50
LATCH.mostly latch-free SCN 56 6 -50
LATCH.active checkpoint queue 63 7 -56
LATCH.session idle bit 183 45 -138
LATCH.enqueues 219 49 -170
LATCH.redo writing 241 25 -216
LATCH.SQL memory manager worka 337 0 -337
LATCH.messages 427 42 -385
LATCH.simulator hash latch 844 4 -840
LATCH.dml lock allocation 1,428 154 -1,274
LATCH.shared pool 1,586 271 -1,315
LATCH.row cache enqueue latch 1,648 194 -1,454
LATCH.cache buffers lru chain 1,521 5 -1,516
LATCH.library cache pin alloca 2,900 362 -2,538
LATCH.row cache objects 4,438 502 -3,936
LATCH.enqueue hash chains 4,841 508 -4,333
LATCH.checkpoint queue latch 6,296 521 -5,775
LATCH.session allocation 19,333 1,893 -17,440
LATCH.undo global data 30,208 2,945 -27,263
LATCH.redo allocation 30,716 3,231 -27,485
LATCH.sequence cache 42,506 4,124 -38,382
LATCH.library cache pin 60,580 6,241 -54,339
LATCH.library cache 76,555 7,911 -68,644

LATCHES:

Ejecución_1 Ejecución_2 Diferencia Porcentaje
670,187 67,521 -602,666 992.56%


Una de las cosas que me interesa mostrarles acerca de éstas ejecuciones son los LATCHES (loqueos). Si bien la ejecución de los 2 códigos PL/SQL demoraron exactamente lo mismo en ejecutarse (en éste ejemplo procesando solamente 10.000 registros), las estadísticas nos muestran que estamos empleados muchísimos más loqueos en la primer ejecución que en la segunda. Pero porqué sucede ésto? Recordemos que en la segunda ejecución, lo único que modificamos en el código fue el agregado de la cláusula LIMIT. Bien, como dijimos en el comienzo, manejar gran cantidad de memoria es más costoso que manejar poca cantidad, por lo que Oracle tiene que emplear mayor cantidad de loqueos para manejar los 10.000 registros que subimos a memoria en la primer ejecución, que manejar solamente 100 registros en la segunda ejecución.
Este ejemplo lo realizamos con un sólo usuario concurrente.... pero imagínese qué sucedería si tenemos muchos usuarios concurrentes realizando lo mismo que nosotros... y por lo tanto, generando gran cantidad de loqueos....

NOTA: Tenemos que evitar los loqueos a toda costa ya que los loqueos afectan la performance del sistema. Mientras mayor sea la cantidad de loqueos, nuestro sistema se vuelve cada vez menos escalable; y como consecuencia, cada vez soporta menor cantidad de usuarios concurrentes.

jueves, 13 de septiembre de 2007

Modificar millones de registros de una tabla

Muchas veces me hacen la siguiente pregunta: ¿ Cómo puedo modificar de manera eficiente los datos de una tabla que contiene millones de registros sin tener impacto en la performance del sistema ?

Suele ocurrir, que la solución que se utiliza es realizar un simple UPDATE. Si hablamos de performance, la ejecución de ésta sentencia puede tener un gran impacto en la base de datos. Porqué? porque principalmente, generamos muchísimos bytes de redo y undo.

Si tengo que modificar millones de datos de una tabla, optaría por NO modificarlos.
Implementaría la siguiente estrategia.

Veamos un ejemplo:

Supongamos que tenemos una tabla llamada TEST que contiene 5 millones de registros, una primary key y un índice único:

SQL_9iR2> CREATE TABLE test NOLOGGING PARALLEL 4 AS
2 SELECT level id, 'nom_'||level nom
3 FROM dual
4 CONNECT BY level <= 5000000 ;

Table created.

Elapsed: 00:00:16.06

SQL_9iR2> ALTER TABLE test ADD CONSTRAINT test_id_pk PRIMARY KEY (id) NOLOGGING PARALLEL 4 ;

Table altered.

Elapsed: 00:00:14.03

SQL_9iR2> CREATE UNIQUE INDEX test_id_nom_uq ON test(id,nom) NOLOGGING PARALLEL 4 ;

Index created.

SQL_9iR2> SELECT *
2 FROM test
3 WHERE rownum <= 10 ;

ID NOM
---------- --------------
37011 nom_37011
37012 nom_37012
37013 nom_37013
37014 nom_37014
37015 nom_37015
37016 nom_37016
37017 nom_37017
37018 nom_37018
37019 nom_37019
37020 nom_37020

10 rows selected.

Si nosotros quisiéramos modificar los datos de la columna NOM, no vamos a utilizar la cláusula UPDATE, sino que vamos a realizar los siguientes pasos:

1) Lo primero que vamos a hacer es crear una tabla con las modificaciones que queremos realizar. En nuestro caso dijimos que vamos a modificar la columna NOM. Fijense que cuando creé la tabla, esa columna está en minúsculas. El cambio que vamos a realizar es colocar el contenido en mayúsculas:

SQL_9iR2> CREATE TABLE test_nueva NOLOGGING PARALLEL 4 AS
2 SELECT id, UPPER(nom) nom
3 FROM test ;

Table created.

Elapsed: 00:00:03.05

Como podemos observar, lo que hice fue colocar en el SELECT los cambios que quería realizar. En sólo 3 segundos tenemos nuestra tabla nueva creada y con los cambios realizados.

2)Agregamos las constraints a la nueva tabla y sus respectivos índices:

SQL_9iR2> ALTER TABLE test_nueva ADD CONSTRAINT test_id_pk_2 PRIMARY KEY(id) NOLOGGING PARALLEL 4 ;

Table altered.

Elapsed: 00:00:22.05

SQL_9iR2> CREATE UNIQUE INDEX test_id_nom_uq_2 ON test_nueva(id,nom) NOLOGGING PARALLEL 4 ;

Index created.

Elapsed: 00:00:20.03

3) Dropeamos la tabla TEST:

SQL_9iR2> DROP TABLE test ;

Table dropped.

Elapsed: 00:00:00.02

4) Modificamos el nombre de la tabla TEST_NUEVA, las constraints e índices:

SQL_9iR2> ALTER TABLE test_nueva RENAME TO test ;

Table altered.

Elapsed: 00:00:00.03

SQL_9iR2> ALTER INDEX test_id_nom_uq_2 RENAME TO test_id_nom_uq ;

Index altered.

Elapsed: 00:00:00.02

SQL_9iR2> ALTER TABLE test RENAME CONSTRAINT test_id_pk_2 TO test_id_pk ;

Table altered.

Elapsed: 00:00:00.02

5) Por último, granteamos los permisos que teníamos en la tabla vieja a la nueva tabla.

Para crear los objetos de éste ejemplo, utilicé las cláusulas NOLOGGING y PARALLEL. La cláusula NOLOGGING la utilicé para generar muy poco redo y nada de undo.
La cláusula PARALLEL la utilicé para paralelizar (a través de 4 CPU's en nuestro ejemplo) las operaciones que ejecutamos sobre los objetos.
En caso de que queramos volver a colocar la tabla en modo LOGGING y NOPARALLEL, ejecutaríamos...

SQL_9iR2> ALTER TABLE test LOGGING NOPARALLEL ;

Table altered.

Elapsed: 00:00:00.01

Cómo quedaron los datos de nuestra tabla? Veamos algunos registros...

SQL_9iR2> SELECT *
2 FROM test
3 WHERE rownum <= 10 ;

ID NOM
---------- --------------
37011 NOM_37011
37012 NOM_37012
37013 NOM_37013
37014 NOM_37014
37015 NOM_37015
37016 NOM_37016
37017 NOM_37017
37018 NOM_37018
37019 NOM_37019
37020 NOM_37020

10 rows selected.

Como podemos observar, realizamos las modificaciones que necesitábamos sin tener un impacto en la performance del sistema.

martes, 11 de septiembre de 2007

Clustering Factor

El Clustering Factor nos dice que tan ordenado se encuentran los registros de la tabla en base a los valores del índice.

Respecto al valor que obtenemos del Clustering Factor, podemos hacer 2 observaciones:

- Si el valor se acerca a la cantidad de bloques de la tabla, entonces se dice que la tabla está muy bien ordenada. En ese caso, el contendido del índice de un leaf block, tiende a apuntar a registros del mismo bloque de datos.
- Si el valor se acerca a la cantidad de registros de la tabla, entonces se dice que la tabla está muy mal ordenada. En ese caso, es improbable que el contendido del índice de un leaf block, tienda a apuntar a registros del mismo bloque de datos.

Bien, pero como afectaría el Clustering Factor en nuestra consulta?

Veamos un ejemplo:

Creamos una tabla llamada "ordenada":

SQL_9iR2> CREATE TABLE ordenada AS
2 SELECT level x, rpad(dbms_random.random,100,'*') y
3 FROM dual
4 CONNECT BY level <= 100000 ;

Table created.

SQL_9iR2> ALTER TABLE ordenada
2 ADD CONSTRAINT ord_pk
3 PRIMARY KEY(x) ;

Table altered.

SQL_9iR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDENADA', cascade => true) ;

PL/SQL procedure successfully completed.

Creamos una tabla llamada "desordenada":

SQL_9iR2> CREATE TABLE desordenada AS
2 SELECT x, y
3 FROM ordenada
4 ORDER BY y ;

Table created.

SQL_9iR2> ALTER TABLE desordenada
2 ADD CONSTRAINT desord_pk
3 PRIMARY KEY(x) ;

Table altered.

SQL_9iR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'DESORDENADA', cascade => true) ;

PL/SQL procedure successfully completed.

Ya tenemos nuestras 2 tablas creadas, analizadas y con sus respectivos índices.

Veamos un poco los valores que hay en cada una de las tablas...

SQL_9iR2> SELECT x, y
2 FROM ordenada
3 WHERE rownum <= 5 ;

X Y
------ --------------------------------------------------
1 1546142627****************************************
**************************************************

2 -1607493826***************************************
**************************************************

3 -1823003438***************************************
**************************************************

4 -385107593****************************************
**************************************************

5 -478367392****************************************
**************************************************

5 rows selected.

SQL_9iR2> SELECT x, y
2 FROM desordenada
3 WHERE rownum <= 5 ;

X Y
------ --------------------------------------------------
50230 -1000006552***************************************
**************************************************

37976 1000011102****************************************
**************************************************

71202 1000046989****************************************
**************************************************

54512 -1000054026***************************************
**************************************************

73252 -1000056784***************************************
**************************************************

5 rows selected.

Pueden notar que la columna X (donde creamos las 2 primary key) está ordenada en la tabla "ordenada" y desordenada en la tabla "desordenada".

Veamos el Clustering Factor de los índices de cada tabla:

SQL_9iR2> SELECT a.index_name, b.num_rows, b.blocks, a.clustering_factor
2 FROM dba_indexes a, dba_tables b
3 WHERE a.index_name IN ('ORD_PK','DESORD_PK')
4 AND a.table_name = b.table_name ;

INDEX_NAME NUM_ROWS BLOCKS CLUSTERING_FACTOR
------------------------------ ---------- ---------- -----------------
DESORD_PK 100000 1539 99932
ORD_PK 100000 1539 1539


2 rows selected.

Para el índice DESORD_PK podemos ver que el valor del CLUSTERING_FACTOR se acerca al valor de NUM_ROWS, esto nos quiere decir que la tabla se encuentra muy mal ordenada y que es improbable que el contendido del leaf block de éste índice, tienda a apuntar a registros del mismo bloque de datos.

Para el índice ORD_PK podemos ver que el valor del CLUSTERING_FACTOR es igual al valor de BLOCKS, esto nos quiere decir que la tabla se encuentra muy bien ordenada y que el contendido del leaf block de éste índice, tiende a apuntar a registros del mismo bloque de datos.

Qué consecuencias puede tener el Clustering Factor en la ejecución de nuestra consulta?

Supongamos la siguiente consulta en la tabla "ordenada":

SQL_9iR2> explain plan for
2 SELECT *
3 FROM ordenada
4 WHERE x BETWEEN 55000 AND 60000 ;

Explained.

SQL_9iR2> @explains

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5002 | 512K| 89 |
| 1 | TABLE ACCESS BY INDEX ROWID| ORDENADA | 5002 | 512K| 89 |
|* 2 | INDEX RANGE SCAN | ORD_PK | 5002 | | 12 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ORDENADA"."X">=55000 AND "ORDENADA"."X"<=60000)

SQL_9iR2> SET AUTOTRACE TRACEONLY STATISTICS

Statistics
---------------------------------------------------
0 recursive calls
0 db block gets
757 consistent gets
0 physical reads

0 redo size
562801 bytes sent via SQL*Net to client
4159 bytes received via SQL*Net from client
335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5001 rows processed

Vemos que el CBO optó por utilizar el índice ORD_PK para realizar un range scan. El CBO sabe, gracias al valor del Clustering Factor, que el índice de ésta tabla se encuentra ordenado; y como nosotros colocamos la condición "BETWEEN 55000 AND 60000", Oracle puede caminar en forma horizontal a través de los leaf blocks sin necesidad de realizar demasiadas lecturas de bloques ya que los valores se encuentran ordenados; y como se encuentran ordenados, el contenido de los leaf blocks tienden a apuntar a registros del mismo bloque de datos.

Ahora veamos que sucede si ejecutamos la misma consulta pero en la tabla "desordenada":

SQL_9iR2> explain plan for
2 SELECT *
3 FROM desordenada
4 WHERE x BETWEEN 55000 AND 60000 ;

Explained.

SQL_9iR2> @explains

---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5002 | 512K| 150 |
|* 1 | TABLE ACCESS FULL | DESORDENADA | 5002 | 512K| 150 |
---------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("DESORDENADA"."X">=55000 AND "DESORDENADA"."X"<=60000)

SQL_9iR2> SET AUTOTRACE TRACEONLY STATISTICS

Statistics
---------------------------------------------------
0 recursive calls
0 db block gets
1876 consistent gets
1539 physical reads

0 redo size
562801 bytes sent via SQL*Net to client
4159 bytes received via SQL*Net from client
335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5001 rows processed

En éste caso, el CBO sabe, gracias al valor del Clustering Factor, que el índice de ésta tabla se encuentra desordenado. Entonces, opta por realizar un full scan ya que si accede por el índice DESORD_PK, va a tener que leer mayor cantidad de bloques que si realiza un barrido completo de la tabla.

Hay personas fanáticas de los índices y tratan de evitar a toda costa el acceso por full scan. Bien, veamos que sucede si fuerzo al CBO a que utilice el índice de la tabla desordenada:

SQL_9iR2> explain plan for
2 SELECT /*+ INDEX(desordenada desord_pk) */ *
3 FROM desordenada
4 WHERE x BETWEEN 55000 AND 60000 ;

Explained.

SQL_9iR2> @explains

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5002 | 512K| 5011 |
| 1 | TABLE ACCESS BY INDEX ROWID| DESORDENADA | 5002 | 512K| 5011 |
|* 2 | INDEX RANGE SCAN | DESORD_PK | 5002 | | 12 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("DESORDENADA"."X">=55000 AND "DESORDENADA"."X"<=60000)

SQL_9iR2> SET AUTOTRACE TRACEONLY STATISTICS

Statistics
---------------------------------------------------
0 recursive calls
0 db block gets
5344 consistent gets
1495 physical reads

0 redo size
562801 bytes sent via SQL*Net to client
4159 bytes received via SQL*Net from client
335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5001 rows processed

Observamos que si utilizamos el índice de la tabla desordenada, leemos muchos más bloques de datos que si dejamos al CBO hacer lo que mejor sabe hacer; que en éste caso para la tabla desordenada, es realizar un full scan de la tabla.
The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.