Blog – Página 5 – Optimización SQL en Oracle

Optimización SQL. Ejemplo 1. Producto cartesiano.

Para Nacho, por haber descubierto la pieza que faltaba. 😛

La siguiente consulta se ha cancelado tras 15 horas y 37 minutos de ejecución.

SQL> select count(*) from (SELECT BITOWN03.BS_C_CLIENTES_BIT_03.DNICIF_DE,
  2    BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03.CONTRATO_ID,
  3    HSCONTRATOS_ESTADOS.ESTADO_DE,
  4    BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03.FEC_ESTADO_DT,
  5    BITOWN03.BS_C_CLIENTES_BIT_03.ABONADO_ID
  6  FROM
  7    BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03,
  8    BITOWN03.LK_C_ESTADOS_BIT_03  HSCONTRATOS_ESTADOS,
  9    BITOWN03.BS_C_CLIENTES_BIT_03,
10    BITOWN03.BS_C_CONTRATOS_BIT_03  CONTRATOS
11  WHERE  BITOWN03.BS_C_CLIENTES_BIT_03.ABONADO_ID          = CONTRATOS.ABONADO_ID
12    AND  BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03.CONTRATO_ID = CONTRATOS.CONTRATO_ID
13    AND  BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03.ESTADO_ID   = HSCONTRATOS_ESTADOS.ESTADO_ID
14    AND  BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03.FEC_ESTADO_DT
15                   BETWEEN  to_date(’01-06-2005 00:00:00′,’DD-MM-YYYY HH24:MI:SS’)
16                       AND  to_date(’30-06-2005 23:59:59′,’DD-MM-YYYY HH24:MI:SS’)
17   AND  BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03.ESTADO_ID  =  ‘BA’);
  BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03,
           *
ERROR at line 7:
ORA-00028: your session has been killed

Elapsed: 15:37:01.66

con el siguiente plan de ejecución:
SQL> @C:ORACLEORA92RDBMSADMINUTLXPLS

PLAN_TABLE_OUTPUT
————————————————————————————————————————

———————————————————————————————————————
| Id  | Operation                           |  Name                         | Rows  | Bytes | Cost  | Pstart| Pstop |
———————————————————————————————————————
|   0 | SELECT STATEMENT                    |                               |   461 | 24433 |    16 |       |       |
|   1 |  NESTED LOOPS                       |                               |   461 | 24433 |    16 |       |       |
|   2 |   MERGE JOIN CARTESIAN              |                               |  1448M|    58G|    16 |       |       |
|   3 |    NESTED LOOPS                     |                               |   971 | 35927 |    16 |       |       |
|   4 |     TABLE ACCESS BY INDEX ROWID     | LK_C_ESTADOS_BIT_03           |     1 |    20 |     1 |       |       |
|*  5 |      INDEX UNIQUE SCAN              | PK_C_ESTADOS_BIT_03           |     1 |       |       |       |       |
|*  6 |     INDEX RANGE SCAN                | IDX_FECHA_ESTADO_CONT_JAVIER  |   971 | 16507 |    15 |       |       |
|   7 |    BUFFER SORT                      |                               |  1491K|  8739K|     1 |       |       |
|   8 |     INDEX FULL SCAN                 | PK_C_CLIENTES_BIT_03          |  1491K|  8739K|       |       |       |
|*  9 |   TABLE ACCESS BY GLOBAL INDEX ROWID| BS_C_CONTRATOS_BIT_03         |     1 |    10 |       | ROWID | ROW L |
|* 10 |    INDEX UNIQUE SCAN                | PK_C_CONTRATOS_BIT_03         |     1 |       |       |       |       |
———————————————————————————————————————

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

   5 – access(«HSCONTRATOS_ESTADOS».»ESTADO_ID»=’BA’)
   6 – access(«HS_C_ESTADOS_CONTRATO_BIT_03″.»FEC_ESTADO_DT»>=TO_DATE(‘2005-06-01 00:00:00’, ‘yyyy-mm-dd
              hh24:mi:ss’) AND «HS_C_ESTADOS_CONTRATO_BIT_03».»ESTADO_ID»=’BA’ AND
              «HS_C_ESTADOS_CONTRATO_BIT_03».»FEC_ESTADO_DT»<=TO_DATE(‘2005-06-30 23:59:59’, ‘yyyy-mm-dd hh24:mi:ss’))
       filter(«HS_C_ESTADOS_CONTRATO_BIT_03″.»ESTADO_ID»=»HSCONTRATOS_ESTADOS».»ESTADO_ID» AND
              «HS_C_ESTADOS_CONTRATO_BIT_03».»ESTADO_ID»=’BA’)
   9 – filter(«BS_C_CLIENTES_BIT_03″.»ABONADO_ID»=»CONTRATOS».»ABONADO_ID»)
  10 – access(«HS_C_ESTADOS_CONTRATO_BIT_03″.»CONTRATO_ID»=»CONTRATOS».»CONTRATO_ID»)

Note: cpu costing is off

********************************************************************************
SOLUCIÓN al caso.
********************************************************************************

Las tablas contienen estadísticas fieles. El producto cartesiano tampoco engaña: cruzará 58 Gb. para atender nuestra petición. De todas maneras, algo falla. Ese plan no es óptimo.

Las estadísticas se generan mediante la siguiente llamada a DBMS_STATS.

SQL> begin
  2      DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>usuario,
  3         TABNAME=>tabla,
  4         ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,
  5         METHOD_OPT => ‘FOR ALL INDEXED COLUMNS SIZE 1’,
  6         DEGREE=>4);
  7  end;
  8  /

PL/SQL procedure successfully completed.

Es preciso no únicamente analizar las tablas, sino también los índices. Algunos de ellos están analizados con el procedimiento DBMS_STATS.GATHER_INDEX_STATS, pero una de las tablas está particionada. Lo mejor y más simple es incluir el parámetro CASCADE=>TRUE en la llamada a DBMS_STATS.

Es cierto que el coste de análisis de estas tablas incrementará. Además, el parámetro CASCADE afecta, no sólo a índices, sino también a vistas materializadas, particiones, etc. (es decir, a todas sus dependencias).

Con las tablas analizadas de este modo, el nuevo plan de ejecución es éste:

SQL> @c:oracleora92rdbmsadminutlxpls

PLAN_TABLE_OUTPUT
———————————————————————————————————-

————————————————————————————————
| Id  | Operation                      |  Name                         | Rows  | Bytes | Cost  |
————————————————————————————————
|   0 | SELECT STATEMENT               |                               |   471 | 30615 |   405 |
|*  1 |  HASH JOIN                     |                               |   471 | 30615 |   405 |
|*  2 |   INDEX RANGE SCAN             | IDX_FECHA_ESTADO_CONT_JAVIER  |  8740 |   145K|    75 |
|   3 |   NESTED LOOPS                 |                               |   771K|    35M|   257 |
|   4 |    NESTED LOOPS                |                               |  1626K|    46M|   257 |
|   5 |     TABLE ACCESS BY INDEX ROWID| LK_C_ESTADOS_BIT_03           |     1 |    20 |     1 |
|*  6 |      INDEX UNIQUE SCAN         | PK_C_ESTADOS_BIT_03           |     1 |       |       |
|   7 |     INDEX FAST FULL SCAN       | IDX_ABONADO_CONTRATO          |  1626K|    15M|   256 |
|   8 |    TABLE ACCESS BY INDEX ROWID | BS_C_CLIENTES_BIT_03          |     1 |    18 |       |
|*  9 |     INDEX UNIQUE SCAN          | PK_C_CLIENTES_BIT_03          |     1 |       |       |
————————————————————————————————

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

   1 – access(«HS_C_ESTADOS_CONTRATO_BIT_03″.»CONTRATO_ID»=»CONTRATOS».»CONTRATO_ID» AND
              «HS_C_ESTADOS_CONTRATO_BIT_03″.»ESTADO_ID»=»HSCONTRATOS_ESTADOS».»ESTADO_ID»)
   2 – access(«HS_C_ESTADOS_CONTRATO_BIT_03″.»FEC_ESTADO_DT»>=TO_DATE(‘2005-06-01 00:00:00’,
              ‘yyyy-mm-dd hh24:mi:ss’) AND «HS_C_ESTADOS_CONTRATO_BIT_03».»ESTADO_ID»=’BA’ AND
              «HS_C_ESTADOS_CONTRATO_BIT_03».»FEC_ESTADO_DT»<=TO_DATE(‘2005-06-30 23:59:59’, ‘yyyy-mm-dd
              hh24:mi:ss’))
       filter(«HS_C_ESTADOS_CONTRATO_BIT_03».»ESTADO_ID»=’BA’)
   6 – access(«HSCONTRATOS_ESTADOS».»ESTADO_ID»=’BA’)
   9 – access(«BS_C_CLIENTES_BIT_03″.»ABONADO_ID»=»CONTRATOS».»ABONADO_ID»)

Note: cpu costing is off

30 rows selected.

La ejecución ahora sólo tarda: 3 segundos.
El coste de análisis aumenta de 26 minutos a 32 minutos. La ganancia justifica el coste.

Muy bien. 🙂

Optimización SQL. Ejemplo 2. NOT IN vs. NOT EXISTS.

Para Fernando, por la que le espera. 😛

La siguiente consulta se ha cancelado tras 5 horas y 11 minutos en ejecutarse.

SELECT count(CLI.COD_ABONADO)
   FROM BITOWN02.TM_C_CLIENTES_SAC_02 CLI,
        BITOWN02.TE_ERRORES_BIT_02 TE
   WHERE CLI.ROWID = TE.FILA_ID (+)
     AND ‘TM_C_CLIENTES_SAC_02’ = TE.TABLA_DE (+)
     AND TE.ERROR_ID IS NULL
   AND CLI.COD_ABONADO NOT IN (
   SELECT CU.ABONADO_id
     FROM BITOWN03.BS_C_CONTRATOS_BIT_03 CONT, BITOWN03.BS_C_CUENTAS_BIT_03 CU
    WHERE CU.CUENTA_ID=CONT.CUENTA_ID
  );

con el siguiente plan de ejecución:
SQL> @c:oracleora92rdbmsadminutlxpls

PLAN_TABLE_OUTPUT
————————————————————————————-

———————————————————————————————————
| Id  | Operation                      |  Name                  | Rows  | Bytes | Cost  | Pstart| Pstop |
———————————————————————————————————
|   0 | SELECT STATEMENT               |                        |     1 |    44 |  3343 |       |       |
|   1 |  SORT AGGREGATE                |                        |     1 |    44 |       |       |       |
|*  2 |   FILTER                       |                        |       |       |       |       |       |
|*  3 |    FILTER                      |                        |       |       |       |       |       |
|*  4 |     HASH JOIN OUTER            |                        |       |       |       |       |       |
|   5 |      TABLE ACCESS FULL         | TM_C_CLIENTES_SAC_02   | 74323 |   870K|  2407 |       |       |
|   6 |      TABLE ACCESS FULL         | TE_M_ERRORES_BIT_01    |     1 |    32 |     2 |    13 |    13 |
|   7 |    NESTED LOOPS                |                        |  1640K|    50M|   922 |       |       |
|   8 |     PARTITION LIST ALL         |                        |       |       |       |     1 |     7 |
|   9 |      TABLE ACCESS FULL         | BS_C_CONTRATOS_BIT_03  |  1640K|    20M|   922 |     1 |     7 |
|* 10 |     TABLE ACCESS BY INDEX ROWID| BS_C_CUENTAS_BIT_03    |     1 |    19 |       |       |       |
|* 11 |      INDEX UNIQUE SCAN         | PK_C_CUENTAS_BIT_03    |     1 |       |       |       |       |
———————————————————————————————————

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

   2 – filter( NOT EXISTS (SELECT /*+ */ 0 FROM «BITOWN03».»BS_C_CUENTAS_BIT_03″
              «CU»,»BITOWN03″.»BS_C_CONTRATOS_BIT_03″ «CONT» WHERE «CU».»CUENTA_ID»=»CONT».»CUENTA_ID» AND
              LNNVL(«CU».»ABONADO_ID»<>:B1)))
   3 – filter(«TE_M_ERRORES_BIT_01″.»ERROR_ID» IS NULL)
   4 – access(«SYS_ALIAS_1″.ROWID=»TE_M_ERRORES_BIT_01».»FILA_ID»(+))
  10 – filter(LNNVL(«CU».»ABONADO_ID»<>:B1))
  11 – access(«CU».»CUENTA_ID»=»CONT».»CUENTA_ID»)

Note: cpu costing is off

30 rows selected.

********************************************************************************
SOLUCIÓN al caso.
********************************************************************************

En este caso, la consulta con IN tiene un coste aceptable. No obstante, después de cinco horas de ejecución, es de sospechar que la cosa no va muy bien. La estrategia de ejecución es realizar dos FILTER: el primero para el OuterJoin de Clientes sin errores y el segundo para combinarlo (en un pesadísimo NestedLoops) con las cuentas con contratos.

Como la subconsulta está resultando más pesada incluso que la principal, es posible que sustituir IN por la cláusula EXISTS sea una buena estrategia.

Sí, también tengo cuidado que no haya códigos con valor NULL para resolver la consulta, ya que NOT IN y NOT EXISTS no son lo mismo.

Sustituyo NOT IN por NOT EXISTS y la consulta queda de este modo:

explain plan for
SELECT count(CLI.COD_ABONADO)
   FROM BITOWN02.TM_C_CLIENTES_SAC_02 CLI,
        BITOWN02.TE_ERRORES_BIT_02 TE
   WHERE CLI.ROWID = TE.FILA_ID (+)
     AND ‘TM_C_CLIENTES_SAC_02’ = TE.TABLA_DE (+)
     AND TE.ERROR_ID IS NULL
   AND not exists (
   SELECT null
   FROM BITOWN03.BS_C_CONTRATOS_BIT_03 CONT, BITOWN03.BS_C_CUENTAS_BIT_03 CU
  WHERE CLI.COD_ABONADO=CU.ABONADO_ID AND
        CU.CUENTA_ID=CONT.CUENTA_ID);

El plan de ejecución resultante parece ser similar al anterior, incluso su coste parece peor.

SQL> @c:oracleora92rdbmsadminutlxpls

PLAN_TABLE_OUTPUT
——————————————————————————————————————–

——————————————————————————————————————–
| Id  | Operation                         |  Name                  | Rows  | Bytes |TempSpc| Cost  | Pstart| Pstop |
——————————————————————————————————————–
|   0 | SELECT STATEMENT                  |                        |     1 |    57 |       |  4946 |    |          |
|   1 |  SORT AGGREGATE                   |                        |     1 |    57 |       |       |    |          |
|*  2 |   FILTER                          |                        |       |       |       |       |    |          |
|*  3 |    HASH JOIN OUTER                |                        |       |       |       |       |    |          |
|*  4 |     HASH JOIN ANTI                |                        |  1486K|    35M|    34M|  4269 |    |          |
|   5 |      TABLE ACCESS FULL            | TM_C_CLIENTES_SAC_02   |  1486K|    17M|       |  2407 |    |          |
|   6 |      VIEW                         | VW_SQ_1                |  1640K|    20M|       |   922 |    |          |
|   7 |       NESTED LOOPS                |                        |  1640K|    50M|       |   922 |    |          |
|   8 |        PARTITION LIST ALL         |                        |       |       |       |       |  1 |        7 |
|   9 |         TABLE ACCESS FULL         | BS_C_CONTRATOS_BIT_03  |  1640K|    20M|       |   922 |  1 |        7 |
|  10 |        TABLE ACCESS BY INDEX ROWID| BS_C_CUENTAS_BIT_03    |     1 |    19 |       |       |    |          |
|* 11 |         INDEX UNIQUE SCAN         | PK_C_CUENTAS_BIT_03    |     1 |       |       |       |    |          |
|  12 |     TABLE ACCESS FULL             | TE_M_ERRORES_BIT_01    |     1 |    32 |       |     2 | 13 |       13 |
——————————————————————————————————————–

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

   2 – filter(«TE_M_ERRORES_BIT_01″.»ERROR_ID» IS NULL)
   3 – access(«CLI».ROWID=»TE_M_ERRORES_BIT_01″.»FILA_ID»(+))
   4 – access(«CLI».»COD_ABONADO»=»VW_SQ_1″.»ABONADO_ID»)
  11 – access(«CU».»CUENTA_ID»=»CONT».»CUENTA_ID»)

Note: cpu costing is off

Acabo de lanzar la ejecución: 28 segundos.
Très bien. 🙂

Optimización SQL. Ejemplo 3. El truco de los abanicos (fan trick).

Para Manel Moreno, que no me ha dado ningún beso por ésto. 😛

La siguiente consulta tardaba 11 horas en ejecutarse.

SELECT DISTINCT A.NODO_ID, B.NODO_B_ID NODO_EQ1
FROM BITOWN03.BS_R_NODOS_BIT_03 A,
         BITOWN03.RE_R_CONEX_EXTERNAS_BIT_03 B,
         BITOWN03.TMP_NODOS_OK_EST_BIT_03 C
WHERE A.NODO_ID = B.NODO_A_ID
  AND B.NODO_B_ID = C.NODO_ID;

con el siguiente plan de ejecución:
SQL> @c:oracleora92rdbmsadminutlxpls

PLAN_TABLE_OUTPUT
————————————————————————————-

———————————————————————————–
| Id  | Operation              |  Name                    | Rows  | Bytes | Cost  |
———————————————————————————–
|   0 | SELECT STATEMENT       |                          |  5554 |   124K|    23 |
|   1 |  SORT UNIQUE           |                          |  5554 |   124K|    23 |
|   2 |   NESTED LOOPS         |                          |  5554 |   124K|     2 |
|   3 |    MERGE JOIN CARTESIAN|                          |  5985M|    61G|     2 |
|   4 |     TABLE ACCESS FULL  | TMP_NODOS_OK_EST_BIT_03  |  1327 |  6635 |     2 |
|   5 |     BUFFER SORT        |                          |  4510K|    25M|       |
|   6 |      INDEX FULL SCAN   | PK_R_NODOS_BIT_03        |  4510K|    25M|       |
|*  7 |    INDEX RANGE SCAN    | IDX_NODO_CONEX_EXTERNA   |     1 |    12 |       |
———————————————————————————–

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

7 – access(«B».»NODO_B_ID»=»C».»NODO_ID» AND «A».»NODO_ID»=»B».»NODO_A_ID»)

Note: cpu costing is off

********************************************************************************
SOLUCIÓN al caso.
********************************************************************************

Otro producto cartesiano. En este caso el cartesiano sabe muy bien lo que hace. Cruza casi 6.000 millones de filas (en total 61 gigas de información) y un coste mínimo. Vaya paradoja.

Un detalle para entender esta decisión: no existen restricciones de Primary Key, ni
Foreign Key, ni índices únicos, ni restricciones de Not Null.

A causa de ello, Oracle encuentra práctico combinar todos los resultados de una tabla (4,5 millones) sobre las 1400 filas de la otra tabla, en un «todos con todos».

No está mal. No obstante, hay información que Oracle, por mucho que analice las tablas, no va a poder obtener a priori. Únicamente nos interesan valores únicos de la tabla de relación, que existan en sus respectivas tablas relacionadas, pero, como digo, no existe nada que aporte a Oracle esa información.

Cambio algunos detalles de la consulta y la dejo así:

SELECT DISTINCT A.NODO_ID, B.NODO_B_ID NODO_EQ1
FROM     BITOWN03.BS_R_NODOS_BIT_03 A,
      (select distinct nodo_a_id, nodo_b_id from BITOWN03.RE_R_CONEX_EXTERNAS_BIT_03) B,
         BITOWN03.TMP_NODOS_OK_EST_BIT_03 C
WHERE A.NODO_ID = B.NODO_A_ID
  AND B.NODO_B_ID = C.NODO_ID;

Informo de dos cosas: que tengo interés en obtener los códigos distintos de la tabla de relación, y que además existan en las otras dos tablas.

El plan de ejecución cambia totalmente para ejecutarse tal como lo he dicho de otra forma. Ahora Oracle realiza este otro plan de ejecución.

SQL> @c:oracleora92rdbmsadminutlxpls

PLAN_TABLE_OUTPUT
———————————————————————————————

———————————————————————————————
| Id  | Operation             |  Name                       | Rows  | Bytes |TempSpc| Cost  |
———————————————————————————————
|   0 | SELECT STATEMENT      |                             |  5554 |   200K|       | 13626 |
|   1 |  NESTED LOOPS         |                             |  5554 |   200K|       | 13626 |
|   2 |   NESTED LOOPS        |                             |  5554 |   168K|       | 13626 |
|   3 |    VIEW               |                             |  7159K|   177M|       | 13626 |
|   4 |     SORT UNIQUE       |                             |  7159K|    81M|   273M| 13626 |
|   5 |      TABLE ACCESS FULL| RE_R_CONEX_EXTERNAS_BIT_03  |  7159K|    81M|       |  2589 |
|*  6 |    INDEX UNIQUE SCAN  | PK_TMP_MANEL_03             |     1 |     5 |       |       |
|*  7 |   INDEX UNIQUE SCAN   | PK_R_NODOS_BIT_03           |     1 |     6 |       |       |
———————————————————————————————

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

6 – access(«B».»NODO_B_ID»=»C».»NODO_ID»)
7 – access(«A».»NODO_ID»=»B».»NODO_A_ID»)

El coste ahora parece haberse disparado por completo. 13626 unidades de coste. Respecto al coste anterior, de sólo 23. Pero ahora no aparece el cartesiano y parece que la ejecución es más fiel a lo que queremos.

Acabo de lanzar la ejecución: un minuto con veinte segundos.
Bien.

Optimización SQL. Ejemplo 4. Mala cardinalidad.

Para Cristina Álvarez, quien confió en mi desde el primer momento y lo disimuló con toda su alma. 🙂

La siguiente consulta ha sido cancelada tras una hora y veinte minutos de ejecución.

SELECT N2.ENTIDAD_ID,
       CLI.rowid row_id, cli.*,
       MAX(REL.ABONADO_PADRE_ID) OVER (PARTITION BY ABONADO_HIJO_ID) REL_ABONADO_PADRE_ID
FROM BITOWN03.BS_V_MGEC_NODO_BIT_03 N1,
     BITOWN03.BS_V_MGEC_NODO_BIT_03 N2,
     BITOWN03.BS_V_MGEC_REL_NODOS_BIT_03 RN,
     BITOWN02.TM_C_CLIENTES_SAC_02 CLI,
     BITOWN03.RE_C_RELACIONES_ABONADO_BIT_03 REL
WHERE N1.ENTIDAD_ID= CLI.COD_DNICIF
  AND N1.NODO_ID=RN.NODO_ID
  AND RN.TIPO_RELACION_ID=2 AND RN.FECHA_FIN_DT IS NULL
  AND RN.NODO_PADRE_ID=N2.NODO_ID
  AND CLI.COD_ABONADO = REL.ABONADO_HIJO_ID (+);

con el siguiente plan de ejecución:

PLAN_TABLE_OUTPUT
—————————————————————————————————

—————————————————————————————————
| Id  | Operation                       |  Name                           | Rows  | Bytes | Cost  |
—————————————————————————————————
|   0 | SELECT STATEMENT                |                                 |     1 |   278 |  2496 |
|   1 |  SORT UNIQUE                    |                                 |     1 |   278 |  2496 |
|   2 |   WINDOW SORT                   |                                 |     1 |   278 |  2496 |
|   3 |    NESTED LOOPS                 |                                 |     1 |   278 |  2479 |
|   4 |     NESTED LOOPS                |                                 |     1 |   261 |  2478 |
|   5 |      NESTED LOOPS OUTER         |                                 |     1 |   244 |  2477 |
|   6 |       MERGE JOIN CARTESIAN      |                                 |     1 |   232 |  2477 |
|*  7 |        TABLE ACCESS FULL        | BS_V_MGEC_REL_NODOS_BIT_03      |     1 |    22 |    70 |
|   8 |        BUFFER SORT              |                                 |  1486K|   297M|  2407 |
|   9 |         TABLE ACCESS FULL       | TM_C_CLIENTES_SAC_02            |  1486K|   297M|  2407 |
|* 10 |       INDEX FULL SCAN           | PK_C_RELACIONES_ABONADO_BIT_03  |     1 |    12 |       |
|* 11 |      TABLE ACCESS BY INDEX ROWID| BS_V_MGEC_NODO_BIT_03           |     1 |    17 |     1 |
|* 12 |       INDEX UNIQUE SCAN         | PK_V_MGEC_NODO_BIT_03           |     1 |       |       |
|  13 |     TABLE ACCESS BY INDEX ROWID | BS_V_MGEC_NODO_BIT_03           |     1 |    17 |     1 |
|* 14 |      INDEX UNIQUE SCAN          | PK_V_MGEC_NODO_BIT_03           |     1 |       |       |
—————————————————————————————————

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

   7 – filter(«RN».»TIPO_RELACION_ID»=2 AND «RN».»FECHA_FIN_DT» IS NULL)
  10 – access(«CLI».»COD_ABONADO»=»REL».»ABONADO_HIJO_ID»(+))
       filter(«CLI».»COD_ABONADO»=»REL».»ABONADO_HIJO_ID»(+))
  11 – filter(«N1″.»ENTIDAD_ID»=»CLI».»COD_DNICIF»)
  12 – access(«N1″.»NODO_ID»=»RN».»NODO_ID»)
  14 – access(«RN».»NODO_PADRE_ID»=»N2″.»NODO_ID»)

********************************************************************************
SOLUCIÓN al caso.
********************************************************************************

Omitiendo el detalle que la cláusula distinct sobra. La mantenemos para que los planes resulten de ejecuciones similares.

Parece un plan bastante aceptable. No obstante, un producto cartesiano llama la atención… ¿únicamente se obtiene una fila? en ese caso, el cartesiano no hace daño. Vamos a consultar el total de filas de la tabla, si la tabla tiene generadas estadísticas (la columna num_rows de user_tables puede darnos una pista) y si el filtro de la operación 7 realmente resuelve una única fila.

SQL> select count(*) from bitown03.BS_V_MGEC_REL_NODOS_BIT_03;

COUNT(*)
———-
118907

SQL> select table_name, num_rows from dba_tables where
2 table_name=’BS_V_MGEC_REL_NODOS_BIT_03′;

TABLE_NAME NUM_ROWS
—————————— ———-
BS_V_MGEC_REL_NODOS_BIT_03 118907

SQL> select count(*) from bitown03.BS_V_MGEC_REL_NODOS_BIT_03
2 where TIPO_RELACION_ID=2 AND FECHA_FIN_DT IS NULL; –> FILTRO OPERACION 7

COUNT(*)
———-
12844

Pues parece que no… Se está produciendo un producto cartesiano de 1,5 millones de filas (operación 9 TABLE FULL SCAN) sobre 12844 elementos. Oracle está estimando mal la cardinalidad de las filas. Cree que con el filtro 7 únicamente obtendrá una fila y por eso el producto cartesiano no dispara el coste.

Graso error.

Es preciso analizar CORRECTAMENTE las tablas implicadas, indicando que se analicen también los valores de las columnas implicadas. Los comandos para realizar ese análisis son:

SQL> exec dbms_stats.gather_table_stats(OWNNAME=>’usuario’,TABNAME=>’BS_V_MGEC_REL_NODOS_BIT_03′,METHOD_OPT=>’for all columns’);

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.71
SQL> exec dbms_stats.gather_table_stats(OWNNAME=>’usuario’,TABNAME=>’BS_V_MGEC_NODO_BIT_03′,METHOD_OPT=>’for all columns’);

PL/SQL procedure successfully completed.

Elapsed: 00:00:14.57
De esta forma, las estadísticas tienen también información sobre la cardinalidad de las columnas y el plan de ejecución cambia sustancialmente:

SQL> @c:oracleora92rdbmsadminutlxpls

PLAN_TABLE_OUTPUT
—————————————————————————————————

—————————————————————————————————
| Id  | Operation               |  Name                           | Rows  | Bytes |TempSpc| Cost  |
—————————————————————————————————
|   0 | SELECT STATEMENT        |                                 |   209K|    53M|       | 12462 |
|   1 |  SORT UNIQUE            |                                 |   209K|    53M|   112M| 12462 |
|   2 |   WINDOW SORT           |                                 |   209K|    53M|   112M| 12462 |
|   3 |    NESTED LOOPS OUTER   |                                 |   209K|    53M|       |  4217 |
|*  4 |     HASH JOIN           |                                 |   209K|    51M|       |  4217 |
|*  5 |      HASH JOIN          |                                 | 12841 |   589K|       |   320 |
|*  6 |       HASH JOIN         |                                 | 12841 |   388K|       |   192 |
|*  7 |        TABLE ACCESS FULL| BS_V_MGEC_REL_NODOS_BIT_03      | 12841 |   188K|       |    70 |
|   8 |        TABLE ACCESS FULL| BS_V_MGEC_NODO_BIT_03           |   128K|  2012K|       |   112 |
|   9 |       TABLE ACCESS FULL | BS_V_MGEC_NODO_BIT_03           |   128K|  2012K|       |   112 |
|  10 |      TABLE ACCESS FULL  | TM_C_CLIENTES_SAC_02            |  1486K|   297M|       |  2407 |
|* 11 |     INDEX FULL SCAN     | PK_C_RELACIONES_ABONADO_BIT_03  |     1 |    12 |       |       |
—————————————————————————————————

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

   4 – access(«N1″.»ENTIDAD_ID»=»CLI».»COD_DNICIF»)
   5 – access(«RN».»NODO_PADRE_ID»=»N2″.»NODO_ID»)
   6 – access(«N1″.»NODO_ID»=»RN».»NODO_ID»)
   7 – filter(«RN».»TIPO_RELACION_ID»=2 AND «RN».»FECHA_FIN_DT» IS NULL)
  11 – access(«CLI».»COD_ABONADO»=»REL».»ABONADO_HIJO_ID»(+))
       filter(«CLI».»COD_ABONADO»=»REL».»ABONADO_HIJO_ID»(+))

Note: cpu costing is off
Efectivamente, el coste del plan es mayor, pero REAL.
Sólo ha tardado 23 segundos!!

In vs. Exists

He escrito anteriormente en este artículo sobre el uso de NOT IN y NOT EXISTS, mencionando que se trataba de cláusulas diferentes y que, por lo tanto, podían generar diferentes resultados. También mencioné que en su forma natural, IN y EXISTS suponen semánticamente operaciones distintas con diferentes vías de ejecución la mayoría de los casos, pero que pueden implementarse de forma que los resultados sean idénticos y permitir así el sustituir IN por EXISTS y viceversa.

Antes de desglosar la ejecución de IN y EXISTS para una su optimización, menciono la forma de implementarse.

IN

SELECT campo1, campo2, …, campoN
FROM   tabla1
WHERE  campoX IN (–subconsulta
                  SELECT campo1
                  FROM   tabla2
                  WHERE  condicion);

Cierto.

Esta consulta podría implementarse como una natural join. Se trata de un ejemplo. Oracle en ambos casos, tratándose de subconsultas simples, las interpreta como joins y resuelven el mismo plan de ejecución.

SELECT t1.campo1, t1.campo2, …, t1.campoN
FROM   tabla1 t1, tabla2 t2
WHERE  t1.campoX = t2.campo1
  AND  condicion;


Es decir, ambas consultas combinan los resultados de Tabla1 y Tabla2, unidos por las columnas t1.campoX y t2.campo1 de modo que visualicen únicamente los resultados de la tabla1 que cumplen la condición de combinación.

La interpretación de la consulta, realizada con IN, es la siguiente:

Selecciona el campo1, campo2, … hasta campoN
de la tabla tabla1
unicamente para las filas cuyo campoX se encuentre en la siguiente lista de valores campo1 de la tabla2, que cumplan con la condición condicion.

La forma de resolver esta petición, para el servidor, es consultar la tabla2 en la subconsulta y delimitar después qué campos de la tabla1 cumplen la condición de join.

EXISTS

SELECT campo1, campo2, …, campoN
FROM   tabla1
WHERE  EXISTS  (–subconsulta
                  SELECT NULL
                  FROM   tabla2
                  WHERE  t1.campoX=t2.campo1
                    AND  condicion);

No existe en esta forma una comparación de campos, salvo en la subconsulta. Ésta devuelve NULL (ni siquiera tendría que devolver un valor concreto para ser válida) y su interpretación semántica es la siguiente:

Selecciona el campo1, campo2, … hasta campoN
de la tabla tabla1
unicamente cuando la subconsulta devuelva un registro (aunque éste sea NULL).
La subconsulta, por tanto, devolverá un registro para aquellos elementos de la tabla2 cuyo campo1 sea igual que el campoX de la tabla principal.

Por lo que Oracle entiende que la consulta más idónea a resolverse antes es la principal, resolver los valores de campoX, y posteriormente compararlos con tabla2.

Es decir, en la ejecución con EXISTS, Oracle entiende que la consulta principal debe resolverse primero, y la subconsulta después. Justo al contrario que IN, lo que puede producir diferentes planes de ejecución.

Generalmente, IN resulta más ventajoso cuando la ejecución de la subconsulta tiene poco coste respecto a la principal, y EXISTS cuando sucede al revés.

(…continuará…)

Clientes gratuitos. Clientes ligeros.

Uno de los principales problemas al desarrollar con PL/SQL sobre bases de datos es el uso de una buena herramienta de gestión del código. Los DBA’s estamos bastante acostumbrados a trabajar con SQL*Plus, la aplicación consola en modo texto, donde tecleamos con más o menos acierto el código SQL que necesitamos para ejecutar comandos de gestión.

No obstante, programar paquetes en PL/SQL donde puede haber miles de líneas de código, o ejecutar consultas sobre un número elevado de tablas, que pueden devolver un número también elevado de filas, implica necesitar un entorno un poco más cómodo para la programación de estos lenguajes contra la base de datos.

Todos los desarrolladores que conozco tienen predilección por Toad, de Quest Software. Lo entiendo. Es una de las mejores herramientas para desarrolladores y DBA’s que conozco. No obstante, es un cliente que lanza muchísimas consultas al diccionario por la gran cantidad de información que presenta y esto puede hacer que la aplicación funcione lenta al compilar, consultar dependencias de objetos y demás. Respecto al desarrollo, Toad no me parece la mejor aplicación para programar en PL/SQL.

Para los desarrolladores suelo recomendar PL/SQL Developer de Allround Automations. Mucho más ergonómica en la debugación, mayor comodidad en la gestión del código y, aunque no posea las herramientas de control de sesiones, comparación de base de datos o traceado de sesiones, lo cierto es que para el trabajo cotidiano del programador, la aplicación resulta mucho más cómoda.

No obstante, estas dos aplicaciones tienen un elevado coste de licencias que muchas empresas no pueden costearse. Mucho menos quien maneja Oracle para su aprendizaje, o programadores de otras áreas que puntualmente tienen que acceder a un servidor Oracle.

Para usuarios con una red lenta, que necesiten un cliente ligero o para programadores que únicamente necesiten ejecutar SQL plano a una base de datos, o para quienes no pueden permitirse licencias de Toad o PL/SQL Developer, aquí paso una relación de herramientas FREEWARE orientadas a plataformas Oracle y muy ligeras de instalar y ejecutar.

Query Reporter, de Allround Automations.

Se trata de una herramienta de SQL que genera los resultados en HTML. Muy cómoda, ligera y fácil de usar. El fabricante es el mismo que el de PL/SQL developer. Esta herramienta sería el «SQL Developer». Puede descargarse directamente aquí.

DBDesigner4, de Fabforce.

Herramienta CASE de modelaje de bases de datos. Cómoda y funcional, genera el código del modelo casi sin retoques. En esta página es posible descargar el programa para Windows y para Linux. Además, incluye manuales disponibles en HTML y PDF.

SQL Tools for Oracle, de SQLTools.

Esta herramienta de código abierto me ha sorprendido por lo versátil que es. Tanto permite sin más problemas resolver un plan de ejecución de una query como extraer la sentencia DDL de creación de un objeto y navegar por todos los objetos y sus dependencias de un usuario concreto. Una herramienta muy bien resuelta, con tutoriales en PDF. Puede descargarse aquí.

DB Comp, de Funduc.

Sobre esta herramienta poco tengo que decir, ya que no la he probado, pero es gratuita y los comentarios que he leído mencionan que es bastante buena. Puede descargarse aquí.

Espero que estos enlaces puedan resultar de utilidad a más de uno.

Búsquedas fuzzy, índices context y gestión documental con Oracle. Parte II.

(Continua a partir de parte I).

Recientemente he estado hablando con gente interesada en la gestión de contenidos de documentos y, en general, la mayoría utiliza formatos PDF. El anterior artículo se basaba únicamente en documentos word, y he creído interesante hacer un ejemplo de como Intermedia realiza la indexación de documentos word y PDF de forma totalmente transparente.

Algo importante a mencionar sobre los índices context es que éstos no se mantienen automáticamente con las inserciones. Hay una causa mayor referente al impacto del rendimiento que tendría dicha reconstrucción. En este caso, se incluyen seis nuevas filas BFILE vinculadas a ficheros PDF existentes en el mismo directorio. Posteriormente a la inserción, es preciso reconstruir el índice para recrearlo con la nueva relación de términos.

Para la implementación completa del ejemplo (creación de la tabla, el índice, el objeto directory y temas relativos a privilegios) es preciso seguir los pasos de la Parte I.

Pasos:

Parte I.

       1.- Creación de una tabla con columna BFILE
       2.- Creación de un DIRECTORY oracle para vincular los documentos Word.
       3.- Concesión de privilegios sobre el DIRECTORY al usuario CTXSYS, propietario de Oracle Context.
       4.- Inserción de las filas en la tabla DOCUMENTOS.
       5.- Indexación de la columna BFILE.
       6.- Ejemplos de consultas.

Parte II.

       7.- Inserción de las filas de documentos PDF.
       8.- Reconstrucción del índice context.
       9.- Ejemplos de consultas.

SQL> host dir c:documentos
El volumen de la unidad C no tiene etiqueta.
El número de serie del volumen es: 2416-B169

Directorio de c:documentos

09/05/2005  09:31       <DIR>          .
09/05/2005  09:31       <DIR>          ..
25/04/2005  13:21              271.360 EntornosNoProd_Permisos extendidos.doc
25/04/2005  13:21               31.232 normas_dba.doc
25/04/2005  13:21              379.904 creacion_entorno_bbdd.doc
25/04/2005  13:21              252.928 CREACION_INSTANCIA_DE_ORACLE_EN_UNIX_v2_4.doc
04/06/2002  21:58            1.479.383 Oracle9i – New Features for Administrators – Vol I.pdf
04/06/2002  21:59            1.542.651 Oracle9i – New Features for Administrators – Vol II.pdf
04/06/2002  21:55            1.244.186 Oracle9i – New Features for Developers – Vol I.pdf
04/06/2002  21:57            2.342.972 Oracle9i – New Features for Developers – Vol II.pdf
04/06/2002  21:13            1.245.254 Oracle9i – New Features Overview – Vol I.pdf
04/06/2002  21:14              801.119 Oracle9i – New Features Overview – Vol II.pdf

10 archivos 9.935.424 bytes
2 dirs 4.970.479.616 bytes libres

SQL> — Inserción de los seis documentos PDF sobre la tabla
SQL> — y recreación del índice CONTEXT
SQL> ———————————————————————————–
SQL> insert into documentos values
2 (5,’PDF’,’Oracle9i – New Features for Administrators – Vol I.pdf’,
3 BFILENAME(‘DOCUMENTOS_WORD’,’Oracle9i – New Features for Administrators – Vol I.pdf’));

1 row created.

SQL> insert into documentos values
2 (6,’PDF’,’Oracle9i – New Features for Administrators – Vol II.pdf’,
3 BFILENAME(‘DOCUMENTOS_WORD’,’Oracle9i – New Features for Administrators – Vol II.pdf’));

1 row created.

SQL> insert into documentos values
2 (7,’PDF’,’Oracle9i – New Features for Developers – Vol I.pdf’,
3 BFILENAME(‘DOCUMENTOS_WORD’,’Oracle9i – New Features for Developers – Vol I.pdf’));

1 row created.

SQL> insert into documentos values
2 (8,’PDF’,’Oracle9i – New Features for Developers – Vol II.pdf’,
3 BFILENAME(‘DOCUMENTOS_WORD’,’Oracle9i – New Features for Developers – Vol II.pdf’));

1 row created.

SQL> insert into documentos values
2 (9,’PDF’,’Oracle9i – New Features Overview – Vol I.pdf’,
3 BFILENAME(‘DOCUMENTOS_WORD’,’Oracle9i – New Features Overview – Vol I.pdf’));

1 row created.

SQL> insert into documentos values
2 (10,’PDF’,’Oracle9i – New Features Overview – Vol II.pdf’,
3 BFILENAME(‘DOCUMENTOS_WORD’,’Oracle9i – New Features Overview – Vol II.pdf’));

1 row created.

SQL> commit;

Commit complete.

SQL> ALTER INDEX idx_documentos_doc REBUILD;

Index altered.

— Las búsquedas CONTEXT sobre índices domain se realizan con la cláusula CONTAINS
— más documentación al respecto en http://download-west.oracle.com/docs/cd/B10501_01/text.920/a96518/cqoper.htm )
————————————————————

— documentos con la palabra ‘tablespace’ cerca de ‘storage’
SQL> select tipo, titulo
2 from documentos
3 where CONTAINS(documento, ‘tablespace near storage’) &gt 0;

TIPO TITULO
—- ————————————————————
PDF Oracle9i – New Features Overview – Vol II.pdf
PDF Oracle9i – New Features Overview – Vol I.pdf
WORD Creación de instancia Oracle en UNIX.
WORD Normativa de DBA.

— documentos con una palabra parecida a ‘locally’ en un 70%
SQL> select tipo, titulo
2 from documentos
3 where CONTAINS(documento, ‘fuzzy(locally, 70, 6, weight)’, 1) &gt 0;

TIPO TITULO
—- ————————————————————
PDF Oracle9i – New Features Overview – Vol II.pdf
PDF Oracle9i – New Features Overview – Vol I.pdf
WORD Creación de un entorno de BBDD.

— documentos con una palabra parecida a ‘locally’ en un 60%
— nota: la palabra LOCAL aparece en estos dos nuevos documentos
SQL> select tipo, titulo
2 from documentos
3 where CONTAINS(documento, ‘fuzzy(locally, 60, 6, weight)’, 1) &gt 0;

TIPO TITULO
—- ————————————————————
PDF Oracle9i – New Features Overview – Vol II.pdf
PDF Oracle9i – New Features Overview – Vol I.pdf
WORD Creación de instancia Oracle en UNIX.
WORD Creación de un entorno de BBDD.
WORD Entornos no producción. Permisos extendidos.

— documentos que hablen sobre ‘listener’
SQL> select tipo, titulo
2 from documentos
3 where CONTAINS(documento, ‘about(listener)’) &gt 0;

TIPO TITULO
—- ————————————————————
PDF Oracle9i – New Features Overview – Vol II.pdf
WORD Creación de instancia Oracle en UNIX.
WORD Normativa de DBA.

— relevancia de documentos que hablan de ‘listener’
— nota: en ‘Normativa de DBA’, efectivamente, se habla bastante menos sobre el listener.
SQL> select tipo, CONTAINS(documento, ‘about(listener)’)||’%’ relevancia, titulo
2 from documentos
3 where CONTAINS(documento, ‘about(listener)’) &gt 0;

TIPO RELEVANCIA TITULO
—- ———- ————————————————————
PDF  41%        Oracle9i – New Features Overview – Vol II.pdf
WORD 91%        Creación de instancia Oracle en UNIX.
WORD 23%        Normativa de DBA.

Más información en http://download-west.oracle.com/docs/cd/B10501_01/text.920/a96518/csql.htm#21732

Defragmentación de un tablespace mediante scripts.

En muchas ocasiones recurro a generar scripts a partir de consultas.
Ejecuto en SQL*Plus algo parecido a:

SQL> select ‘alter package ‘||owner||’.’||object_name||’ compile;’ sentencia
2> from dba_objects where status=’INVALID’ and object_type =’PACKAGE BODY’;

SENTENCIA
——————————————————————
alter package USUARIO.MAP02_CUACON02 compile;
alter package USUARIO.MAP01_CUACON03 compile;
alter package USUARIO.MAP01_FLUXHISTO03_CI compile;
alter package USUARIO.MAP01_BALANCEBE03 compile;
alter package USUARIO.MAP01_PAHISTOACT03 compile;
alter package USUARIO.MAP01_RAPPELS03 compile;

6 filas seleccionadas.

y simplemente copio y pego las filas como comandos en la consola SQL.

El procedimiento podrá parecer tosco, pero resulta muy versátil ejecutar una consulta que construya dinámicamente todas las sentencias SQL que se precisan para una determinada tarea y, lo más importante, que se adapten a cualquier entorno.

Un ejemplo de ésto podría ser aplicado a la reorganización de los segmentos de un tablespace.

Para la defragmentación de un tablespace, los objetos deben reconstruirse de la siguiente forma:

   – Tablas:   ALTER TABLE xxxx MOVE STORAGE INITIAL tamaño K/M);
   – Índices:   ALTER INDEX xxxx REBUILD [online];
   – Tablas particionadas:  ALTER TABLE xxxx MOVE PARTITION …….;
   – Índices particionados: ALTER INDEX xxxx REBUILD PARTITION …… [online];

Teniendo en cuenta que mover una tabla en Oracle9i deja inválidos los índices, una vez ejecutados estos comandos por todas las tablas e índices del tablespace, sería preciso comprobar qué índices (o partitiones de índice) pueden haber quedado afectados y reconstruirlos.

Habría otro detalle a tener presente: los segmentos reconstruidos sobre el mismo tablespace, dificilmente consiguen liberar el espacio contiguo al final de los ficheros, por lo que sería útil hacer un doble movimiento: mover primero a un tablespace vacío, y posteriormente mover los objetos al tablespace original de vuelta.

Si bien esto no es complicado, sí resulta minucioso. Este script, en este caso, puede resultar tremendamente útil.

Una vez defragmentado el tablespace, es preciso ejecutar el segundo script para reconstruir índices, lo que evitará que éstos se queden inválidos después del transporte.

* Nota: Es preciso tener en cuenta el tamaño total de los objetos. Si éstos son grandes, conviene aumentar el tamaño definido como inicial. Lo mejor es utilizar tablespaces gestionados localmente con gestión automática de las extensiones para que Oracle decida, en tablas grandes, hacer extensiones mayores dinámicamente.

— DEFRAGMENTA_TABLESPACE.SQL —
set verify off
set lines 120
set pages 1000
define tablespace_origen =&TABLESPACE_DATOS
define tablespace_destino=&TABLESPACE_VACIO
define t_extension_inicial=&TAMAÑO_EXTENSION_INICIAL
select ‘alter table ‘||owner||’.’||table_name||
       ‘ move tablespace &tablespace_destino storage (initial &t_extension_inicial M);’
from dba_tables
where upper(tablespace_name)=’&tablespace_origen’
union all
select ‘alter table ‘||table_owner||’.’||table_name||
       ‘ move partition ‘||partition_name||
       ‘ tablespace &tablespace_destino storage (initial &t_extension_inicial M);’
from dba_tab_partitions
where upper(tablespace_name)=’&tablespace_origen’
union all
select ‘alter index ‘||owner||’.’||index_name||
       ‘ rebuild tablespace &tablespace_destino storage (initial &t_extension_inicial M);’
from dba_indexes
where upper(tablespace_name)=’&tablespace_origen’
union all
select ‘alter index ‘||index_owner||’.’||index_name||
       ‘ rebuild partition ‘||partition_name||
       ‘ tablespace &tablespace_destino storage (initial &t_extension_inicial M);’
from dba_ind_partitions
where upper(tablespace_name)=’&tablespace_origen’
union all
select ‘alter table ‘||owner||’.’||table_name||
       ‘ move tablespace &tablespace_origen storage (initial &t_extension_inicial M);’
from dba_tables
where upper(tablespace_name)=’&tablespace_origen’
union all
select ‘alter table ‘||table_owner||’.’||table_name||
       ‘ move partition ‘||partition_name||
       ‘ tablespace &tablespace_origen storage (initial &t_extension_inicial M);’
from dba_tab_partitions
where upper(tablespace_name)=’&tablespace_origen’
union all
select ‘alter index ‘||owner||’.’||index_name||
       ‘ rebuild tablespace &tablespace_origen storage (initial &t_extension_inicial M);’
from dba_indexes
where upper(tablespace_name)=’&tablespace_origen’
union all
select ‘alter index ‘||index_owner||’.’||index_name||
       ‘ rebuild partition ‘||partition_name||
       ‘ tablespace &tablespace_origen storage (initial &t_extension_inicial M);’
from dba_ind_partitions
where upper(tablespace_name)=’&tablespace_origen’;

— RECONSTRUYE_INDICES.SQL —
select ‘alter index ‘||index_owner||’.’||index_name||
       ‘ rebuild partition ‘||partition_name||
       ‘ tablespace &tablespace_origen storage (initial &t_extension_inicial M);’ SENTENCIA
from dba_ind_partitions
where upper(tablespace_name)=’&tablespace_origen’
union all
select ‘alter index ‘||owner||’.’||index_name||’ rebuild;’
from dba_indexes
where status=’UNUSABLE’
union all
select ‘alter index ‘||index_owner||’.’||index_name||
       ‘ rebuild partition ‘||partition_name||’;’
from dba_ind_partitions
where status=’UNUSABLE’;

TEST
====
SQL> @c:reorganiza_tablespace
Introduzca un valor para tablespace_datos: DM_ACTIVIDAD01
Introduzca un valor para tablespace_vacio: TS_VACIO
Introduzca un valor para tama±o_extension_inicial: 1

‘ALTERTABLE’||OWNER||’.’||TABLE_NAME||’MOVETABLESPACETS_VACIOSTORAGE(INITIAL1M);’
————————————————————————————————————–
alter table USUARIO.TABLA_XXX02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXX02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXASMA02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXXX02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXXXX02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXPMA02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXP02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXAS02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXON02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXPMES02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXASMES02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXCIBOSMES02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXCIBOSMA02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXCIBOS02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXTASMES02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXTAS02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXTASMA02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXXXXY02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXMA02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXMES02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXACION02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXACIONMA02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXACIONMES02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXTVS02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXTVSMA02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXTVSMES02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXYY02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXXY02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXXASMA02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXX02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXX02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXXPMA02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXXP02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXXAS02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXXON02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXXPMES02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXXASMES02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXXCIBOSMES02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXXCIBOSMA02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXXCIBOS02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXXTASMES02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXXTAS02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXXTASMA02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXX02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXXMA02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXXMES02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXXACION02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXXACIONMA02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXXACIONMES02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXXTVS02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXXTVSMA02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXXTVSMES02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);

52 filas seleccionadas.

Una vez ejecutados todos estos comandos, lanzamos el script que consulta los índices inválidos con el fin de recompilarlos.

SQL> @c:reconstruye_indices
SENTENCIA
——————————————————————-
alter index USUARIO.INDICEXXXOSP02_01 rebuild;
alter index USUARIO.INDICEXXXURAS02_01 rebuild;
alter index USUARIO.INDICEXXXSION02_01 rebuild;
alter index USUARIO.INDICEXXXOSPMES02_01 rebuild;
alter index USUARIO.INDICEXXXRECIBOS02_01 rebuild;
alter index USUARIO.INDICEXXXABTAS02_01 rebuild;
alter index USUARIO.INDICEXXXDA02_01 rebuild;
alter index USUARIO.INDICEXXXCIACION02_01 rebuild;
alter index USUARIO.INDICEXXXTVS02_01 rebuild;
alter index USUARIO.INDICEXXXTVSMA02_01 rebuild;
alter index USUARIO.INDICEXXXTVSMES02_01 rebuild;

11 filas seleccionadas.

Generación de XML mediante query.

En algunos proyectos en los que he participado, el equipo de front-end, que desarrollaba la interfaz web sobre plataforma .NET, tenía serios problemas para recuperar un conjunto de filas con un objeto tipo array incrustado.

La consulta debía devolver un vector de registros, algunos de ellos formados por una lista de valores. En aquel entonces, el driver de .NET no podía gestionar objetos UDT (tipos de datos definidos por el usuario) y hubo que comprar un componente aparte que tampoco dio buen resultado,pues no soportaba consultas a NESTED TABLES.

Una de las soluciones propuestas fue devolver el resultado (se trataba de fichas de clientes) en formato XML. La idea era buena, pero había alguno que pretendía «decodificar» columnas y filas para elaborar el resultado… ¡escribiendo un procedure con substrs!

No hace falta. La funcionalidad de crear UDT tiene una importancia relevante en las conversiones a XML. Definiendo los tipos como OBJECT y TABLE OF OBJECT podemos crear una estructura de datos facilmente exportable a XML tal y como muestra el ejemplo.

Las tablas tomadas como referencia corresponden a EMP y DEPT del esquema de ejemplo SCOTT.

SQL> create or replace type r_empleado is object(
  2     NUM_EMP          NUMBER(4),
  3     NOMBRE_EMP       VARCHAR2(10),
  4     OFICIO_EMP       VARCHAR2(9),
  5     JEFE_EMP         NUMBER(4),
  6     FCONTRATO_EMP    DATE,
  7     SALARIO_EMP      NUMBER(7,2),
  8     COMISION_EMP     NUMBER(7,2),
  9     DEPARTAMENTO_EMP NUMBER(2));
10  /

Tipo creado.

SQL>
SQL> create or replace type t_empleado is table of r_empleado;
2 /

Tipo creado.

SQL>
SQL> create or replace type r_dept is object(
  2     NUM_DEPT     NUMBER(2),
  3     NOMBRE_DEPT  VARCHAR2(14),
  4     CIUDAD_DEPT  VARCHAR2(13),
  5     empleados    t_empleado);
  6  /

Tipo creado.

SQL>
SQL> create or replace type t_dept is table of r_dept;
2 /

Tipo creado.

SQL>
SQL>
SQL> select sys_xmlgen(
  2               r_dept(dept.deptno, dept.dname, dept.loc,
  3                 cast(multiset(select emp.*
  4                                 from emp
  5                                where emp.deptno = dept.deptno) as t_empleado)
  6                    ),xmlformat.createFormat(‘DEPARTAMENTO’)).getClobVal() as XML
  7  from dept
  8  where deptno=10;

XML
——————————————————————————–
<?xml version=»1.0″?>
<DEPARTAMENTO>
<NUM_DEPT>10</NUM_DEPT>
<NOMBRE_DEPT>ACCOUNTING</NOMBRE_DEPT>
<CIUDAD_DEPT>NEW YORK</CIUDAD_DEPT>
<EMPLEADOS>
  <R_EMPLEADO>
   <NUM_EMP>7782</NUM_EMP>
   <NOMBRE_EMP>CLARK</NOMBRE_EMP>
   <OFICIO_EMP>MANAGER</OFICIO_EMP>
   <JEFE_EMP>7839</JEFE_EMP>
   <FCONTRATO_EMP>09/06/81</FCONTRATO_EMP>
   <SALARIO_EMP>2450</SALARIO_EMP>
   <DEPARTAMENTO_EMP>10</DEPARTAMENTO_EMP>
  </R_EMPLEADO>
  <R_EMPLEADO>
   <NUM_EMP>7839</NUM_EMP>
   <NOMBRE_EMP>KING</NOMBRE_EMP>
   <OFICIO_EMP>PRESIDENT</OFICIO_EMP>
   <FCONTRATO_EMP>17/11/81</FCONTRATO_EMP>
   <SALARIO_EMP>5000</SALARIO_EMP>
   <DEPARTAMENTO_EMP>10</DEPARTAMENTO_EMP>
  </R_EMPLEADO>
  <R_EMPLEADO>
   <NUM_EMP>7934</NUM_EMP>
   <NOMBRE_EMP>MILLER</NOMBRE_EMP>
   <OFICIO_EMP>CLERK</OFICIO_EMP>
   <JEFE_EMP>7782</JEFE_EMP>
   <FCONTRATO_EMP>23/01/82</FCONTRATO_EMP>
   <SALARIO_EMP>1300</SALARIO_EMP>
   <DEPARTAMENTO_EMP>10</DEPARTAMENTO_EMP>
  </R_EMPLEADO>
</EMPLEADOS>
</DEPARTAMENTO>

Copiar una base de datos.

Últimamente he tenido la necesidad de copiar bases de datos.

La petición textual era la creación de una base de datos igual que otra, pero con otro nombre. En algunos casos se trataba de replicar el entorno de producción para usarlo como test. En otros, simplemente, testear que el backup permitía recuperar la base de datos sobre otra máquina y dejar esa base de datos operativa como entorno auxiliar.

Cuando la base de datos puede conservar el mismo nombre y mismo SID, es tan fácil como copiar datafiles, redolog, controlfiles y archivos ORA sobre los mismos directorios originales en una máquina con un servidor Oracle instalado*.
* Por supuesto, misma plataforma O.S., versión y release de Oracle.

No obstante, en los casos en los que el nombre debe ser diferente, hay que ser muy preciso con los pasos a seguir. Incluyo dos pasos previos como opcionales por si son de utilidad.

En este ejemplo se asume que disponemos de la posibilidad de hacer un backup en frío de la base de datos.

Pasos preliminares (recomendado):

– Backup frío, backup caliente, export, Transport Tablespaces, backup del backup, backup de todo lo posible.
– Reducción de tamaño de los datafiles. (Tom Kyte tiene un fabuloso script para ello). Y otra vez backup.

Pasos a seguir:

  1.- Backup en frío de la BBDD original
  2.- Generación del pfile para la nueva BBDD
  3.- Edición del pfile para sustituir: CONTROL_FILES, DB_NAME, y carpetas _DUMP_DEST,…
  4.- Definir el nuevo ORACLE_SID
  5.- Crear el nuevo servicio.
  6.- Creación del fichero de passwords (sólo si el pfile tiene REMOTE_LOGIN_PASSWORDFILE=exclusive)
  7.- Conexión a sqlplus como SYSDBA
  8.- Arranque de la instancia y creación del fichero de parámetros SPFILE
  9.- Backup del controlfile de la BBDD original a traza
  10.- Recreación del controlfile con la cláusula SET NAME.
  11.- Abrir la base de datos con modo OPEN RESETLOGS.

1.- Backup en frío de la BBDD original.
Restauración del backup sobre nueva ubicación.

— Ejecutar el resultado de la siguiente sentencia como script.
— NOTA: cuidado con los nombres duplicados de fichero.
————————————————————-
select ‘shutdown immediate;’ from dual
union all
select ‘host copy ‘||name||’ &&directorio_destino’ from v$controlfile
union all
select ‘host copy ‘||member||’ &directorio_destino’ from v$logfile
union all
select ‘host copy ‘||name||’ &directorio_destino’ from v$datafile
union all
select ‘startup’ from dual;

— copia de los ficheros a los directorios destino
—————————————————

2.- Generación del pfile para la nueva base de datos

SQL> create pfile=’?adminsidpfileinittest.ora’ from spfile;

Archivo creado.

3.- Edición del pfile para sustituir: CONTROL_FILES, DB_NAME, y carpetas _DUMP_DEST,…

4.- Definir el nuevo ORACLE_SID

c:>set ORACLE_SID=test

5.- Crear el nuevo servicio.

c:>oradim -NEW -SRVC OracleServicetest -startmode auto

6.- Creación del fichero de passwords

c:>orapwd file=C:orantora92databasePWDtest.ora password=xxxxxxxxx

7.- Conexión a sqlplus como SYSDBA

C:>sqlplus

SQL*Plus: Release 9.2.0.6.0 – Production on Vie Jul 29 16:41:22 2005

Introduzca el nombre de usuario: sys/xxxxxxxxxx as sysdba
Conectado a una instancia inactiva.

8.- Arranque de la instancia y creación del fichero de parámetros SPFILE

SQL> STARTUP NOMOUNT PFILE=’C:DIRECTORIO_DESTINOinittest.ora’
Instancia ORACLE iniciada.

    Total System Global Area 1074866044 bytes
    Fixed Size                   456572 bytes
    Variable Size             905969664 bytes
    Database Buffers          167772160 bytes
    Redo Buffers                 667648 bytes
    SQL> create spfile from pfile=’C:DIRECTORIO_DESTINOinittest.ora’;

Archivo creado.

9.- Backup del controlfile de PROD a trace.

SQL> alter database backup controlfile to trace;

Base de datos modificada.

10.- A partir de la traza del fichero de control.
Recreación del controlfile con el SET NAME al nuevo nombre.

     CREATE CONTROLFILE SET DATABASE «TEST» RESETLOGS  NOARCHIVELOG REUSE
        MAXLOGFILES 50
        MAXLOGMEMBERS 5
        MAXDATAFILES 133
        MAXINSTANCES 1
        MAXLOGHISTORY 453
     LOGFILE
       GROUP 2 ‘C:DATATESTREDOREDO02.LOG’  SIZE 100M,
       GROUP 3 ‘C:DATATESTREDOREDO03.LOG’  SIZE 100M,
       GROUP 4 ‘C:DATATESTREDOREDO04.LOG’  SIZE 150M,
       GROUP 5 ‘C:DATATESTREDOREDO05.LOG’  SIZE 150M,
       GROUP 6 ‘C:DATATESTREDOREDO06.LOG’  SIZE 200M
      — STANDBY LOGFILE
     DATAFILE
        (… ficheros…)
        ‘C:DATATESTSYSTEMSYSTEM01.DBF’,
        ‘C:DATATESTDATADATA01.DBF’
     CHARACTER SET WE8MSWIN1252
     ;

Archivo de control creado.

11.- Abrir la base de datos con modo OPEN RESETLOGS.

SQL> alter database open resetlogs;

Base de datos modificada.

12.- Verificación.

SQL> select instance_name from v$instance;

     INSTANCE_NAME
     —————-
     test

SQL> select name from v$database;

     NAME
     ———
     TEST

SQL> select status from v$thread;

     STATUS
     ——
     OPEN

12.- Shutdown y Open de la base de datos.

     SQL> shutdown immediate;
     Base de datos cerrada.
     Base de datos desmontada.
     Instancia ORACLE cerrada.

SQL> startup
Instancia ORACLE iniciada.

     Total System Global Area 1074866044 bytes
     Fixed Size                   456572 bytes
     Variable Size             905969664 bytes
     Database Buffers          167772160 bytes
     Redo Buffers                 667648 bytes
     Base de datos montada.
     Base de datos abierta.