Lo Correcto e Incorrecto

Por Tom Kyte
Publicado en marzo de 2009

Nuestro especialista en tecnología controla la documentación y verifica las respuestas.

Tengo una pregunta sobre los cambios de parámetros y SPFILEs. Si realizo un cambio en un parámetro mientras se ejecuta la instancia (utilizando alter system set [parámetro]. . .), ¿este cambio se aplicará a la instancia que actualmente se está ejecutando o se aplicará al reiniciar la base de datos? La razón por la que planteo esta pregunta es porque mi colega dice que los cambios realizados al utilizar un archivo de parámetro almacenado (SPFILE) no se aplicarán hasta el momento de reiniciar la base de datos.

Sus colegas se equivocan; ellos deberían controlar la documentación o al menos intentar verificarla antes de desechar algo. Yo siempre realizo controles y pruebas, porque cada vez que respondo algo en Ask Tom y no puedo ofrecer pruebas para comprobar la veracidad de lo que digo ¡suelo equivocarme!

Y cuando se aplica un cambio, existe un parámetro SCOPE (alcance) en ALTER SYSTEM (alterar sistema) que le permite especificar cuando se efectúa un cambio. El parámetro SCOPE puede establecerse en MEMORY (memoria), SPFILE, o BOTH (ambos). Si el parámetro SCOPE se establece en MEMORY, ALTER SYSTEM, la instancia se cambiará (si el parámetro puede cambiarse sin reiniciar el sistema). Si el parámetro SCOPE se establece en SPFILE, ALTER SYSTEM, se actualizará solo el archivo de parámetro almacenado, y el cambio se efectuará al momento de reiniciarse el sistema. Si usted utiliza BOTH (solo disponible si el parámetro puede cambiarse mientras se ejecuta la base de datos), ALTER SYSTEM cambiará la instancia y el archivo del parámetro almacenado.

Simplemente muestre a sus colegas los contenidos del Listado 1.

Listado de Códigos 1: Cambio de parámetro para una instancia actual en ejecución

Copy


SQL> show parameter user_dump_dest

NAME                TYPE       VALUE
---------------     ------     ------------------------
user_dump_dest      string     /home/ora10gr2/rdbms/log

SQL> alter session set sql_trace=true;
Session altered.

SQL> select c.value || '/' ||
  2            d.instance_name ||
  3            '_ora_' ||
  4            a.spid ||
  5            '.trc' trace
  6    from v$process a, v$session b,
  7           v$parameter c, v$instance d
  8    where a.addr = b.paddr
  9      and b.audsid = userenv('sessionid')
 10     and c.name = 'user_dump_dest'
 11   /

TRACE
------------------------------
/home/ora10gr2/rdbms/log/ora10gr2_ora_18556.trc

SQL> !ls -l /home/ora10gr2/rdbms/log/ora10gr2_ora_18556.trc
-rw-rw----  1 ora10gr2 ora10gr2 286874 Oct 29 10:21 
/home/ora10gr2/rdbms/log/ora10gr2_ora_18556.trc

so trace files are going to /home/ora10gr2/rdbms/log/ora10gr2_ora_18556.trc 

SQL> alter system set user_dump_dest = '/tmp' scope=both;
System altered.

SQL> connect /
Connected.
SQL> alter session set sql_trace=true;
Session altered.

SQL> select c.value || '/' ||
  2             d.instance_name ||
  3             '_ora_' ||
  4             a.spid ||
  5             '.trc' trace
  6    from v$process a, v$session b,
  7           v$parameter c, v$instance d
  8    where a.addr = b.paddr
  9      and b.audsid = userenv('sessionid')
 10      and c.name = 'user_dump_dest'
 11   /

TRACE
------------------------------
/tmp/ora10gr2_ora_18562.trc

SQL> !ls -l /tmp/ora10gr2_ora_18562.trc
-rw-rw----  1 ora10gr2 ora10gr2 4014 Oct 29 10:22 
/tmp/ora10gr2_ora_18562.trc

..and now they are not.

No todos los parámetros pueden cambiarse cuando se ejecuta la instancia; los parámetros se clasifican en tres categorías generales:

1. No susceptibles a cambios online: Oracle Database Reference describe los parámetros de inicio y sus propiedades, entre otras cosas. La documentación incluye una propiedad “modificable” para cada parámetro, y si un parámetro no puede modificarse, no puede cambiarse online. AUDIT_TRAIL, por ejemplo, no puede modificarse (y no puede cambiarse online).

2. Susceptibles a cambios online, pero solo para futuras sesiones. El cambio no afectará ninguna sesión actualmente conectada, no obstante afectará todas las nuevas sesiones creadas luego de ejecutarse ALTER SYSTEM. Por ejemplo, SORT_AREA_SIZE puede cambiarse online, pero solo para futuras sesiones:

Copy


SQL> alter system 
  2    set sort_area_size =32765 
  3    deferred scope=memory;
System altered.

SQL> show parameter sort_area_size

NAME             TYPE      VALUE
-------          -------   -----
sort_area_size   integer   65536

SQL> connect /
Connected.
SQL> show parameter sort_area_size

NAME             TYPE      VALUE
-------          -------   -----
sort_area_size   integer   32765

3. Susceptibles a cambios online e inmediatamente reflejados en todas las sesiones. El cambio conectará a todas las sesiones actualmente conectadas. Por ejemplo, USER_DUMP_DEST puede cambiarse online y esto se refleja inmediatamente en todas las sesiones:

Copy


SQL> show parameter user_dump_dest

NAME             TYPE      VALUE
-------          -------   -----
user_dump_dest   string   /tmp

SQL> alter system 
set user_dump_dest = 
'/home/ora10gr2/rdbms/log';
System altered.

SQL> show parameter user_dump_dest

NAME             TYPE      VALUE
-------          -------   -----
user_dump_dest   string   /home/ora10...

La siguiente pregunta que generalmente surge es: “OK, nosotros fijamos un valor, pero ahora queremos “deshacerlo”. En otras palabras, deseamos eliminar esa configuración de parámetro en nuestro SPFILE. Como no podemos editar el archivo utilizando un editor de texto, ¿como podemos lograrlo?” Esto también se realiza a través de ALTER SYSTEM, pero con la cláusula RESET (reconfigurar):

Copy


alter system 
reset parameter 
 sid='sid|*'

Entonces, por ejemplo, si queremos eliminar el parámetro SORT_AREA_SIZE para que éste adopte el valor por defecto anteriormente invalidado, podemos hacerlo de la siguiente manera:

Copy


SQL> alter system reset 
sort_area_size scope=spfile sid='*';
System altered.

El parámetro SORT_AREA_SIZE se elimina de SPFILE, lo cual puede verificarse al utilizar lo siguiente:

Copy


SQL> create pfile='/tmp/pfile.tst' 
from spfile;
File created.

Luego puede revisar los contenidos de /tmp/pfile.tst, los cuales se generarán en el servidor de la base de datos. Usted encontrará que el parámetro SORT_AREA_SIZE ya no existe en los archivos de parámetros.

Almacenamiento de Carga

Estoy trabajando con un cliente que tiene un depósito de datos con tablas de datos particionadas de alrededor de 400 columnas. El desempeño no es demasiado bueno y estoy intentando optimizar un poco el sistema.

El cliente me dijo que la mayoría de las columnas raramente son consultadas -si alguna vez se consultan- pero el cliente desea almacenar los datos en algún lugar en caso de que sea necesario, no obstante, mi idea es acelerar el inevitable escaneo completo de tablas al omitir todas las columnas nunca utilizadas, fue rechazada. Obviamente, podía almacenar un grupo redundante de tablas más pequeñas y comprimirlas para reducir aún más el tamaño, pero el espacio de almacenamiento es un problema (como sucede en la sección: “no podemos brindar más almacenamiento”), y los usuarios tendrían que saber qué tabla consultar para qué columna. El cliente solo desea utilizar la lógica ETL [extraer, transformar, y cargar] en la base de datos pero sin ninguna lógica de consultas, ya que “para eso tenemos nuestras herramientas de BI”. Por lo tanto, ¿tiene alguna sugerencia de cómo personalizar la base de datos?

“No podemos brindar más almacenamiento”: Bien, si a ellos les preocupa el desempeño, simplemente tendrían que reconsiderarlo. Algunas veces tener almacenamiento extra es necesario, las visualizaciones materializadas consumen almacenamiento adicional y ambos son esenciales para el desempeño del depósito de datos.

No obstante, usted podría utilizar el particionamiento vertical en este caso e incorporar una nueva restricción clave primaria, la cual dará como resultado la elaboración de un índice (pero en general, usted va a necesitar ese índice para volver a reunir los datos).

Si usted informa a la Base de Datos de Oracle que existe una relación uno-a-uno obligatoria entre las dos tablas, entonces puede incluir la eliminación de tablas en su plan. Para que sea una obligación uno-a-uno, tendrá que existir una clave primaria en la columna de unión y claves externas recíprocas, las cuales pueden ser validadas o no, si usted carga los datos limpios—estará bien decir que existen. El código en el Listado 2 hace esto, informar a la base de datos que existen claves externas.

Listado de Códigos 2: Creación de restricciones y tablas T1 y T2

Copy


SQL> create table t1 as select
  2    OBJECT_ID,
  3    OWNER, OBJECT_NAME, SUBOBJECT_NAME,
  4    DATA_OBJECT_ID, OBJECT_TYPE
  5    from all_objects where 1=0;
Table created.

SQL> alter table t1
  2    add constraint t1_pk
  3    primary key(object_id)
  4    rely;
Table altered.

SQL> create table t2 as select
  2    OBJECT_ID,
  3    CREATED, LAST_DDL_TIME, TIMESTAMP,
  4    STATUS, TEMPORARY, GENERATED, SECONDARY
  5    from all_objects where 1=0;
Table created.

SQL> alter table t2
  2    add constraint t2_pk
  3    primary key(object_id)
  4    rely;
Table altered.

Utilizaremos DBMS_STATS para informar al optimizador que T1 y T2 son tablas grandes, como lo serían en la vida real:

Copy


SQL> begin
  2      dbms_stats.set_table_stats
  3      ( user, 'T1',
  4        numrows => 100000000,
  5        numblks => 1000000 );
  6      dbms_stats.set_table_stats
  7      ( user, 'T2',
  8        numrows => 100000000,
  9        numblks => 1000000 );
 10   end;
 11   /
PL/SQL procedure successfully completed.

Y luego las aplicaciones utilizarían la visualización VW, como se muestra en el Listado 3, lo cual oculta el hecho que realmente hay dos tablas debajo.

Listado de Códigos 3: Creación de restricciones y visualizaciones VW

Copy


SQL> create or replace view vw
  2    as
  3    select
  4    t1.OBJECT_ID,
  5    t1.OWNER, t1.OBJECT_NAME, t1.SUBOBJECT_NAME,
  6    t1.DATA_OBJECT_ID, t1.OBJECT_TYPE,
  7    t2.CREATED, t2.LAST_DDL_TIME, t2.TIMESTAMP,
  8    t2.STATUS, t2.TEMPORARY, t2.GENERATED, t2.SECONDARY
  9    from t1, t2
 10     where t1.object_id = t2.object_id;
View created.

SQL> alter table t2
  2    add constraint t2_fk_t1
  3    foreign key(object_id)
  4    references t1(object_id)
  5    rely disable novalidate;
Table altered.

SQL> alter table t1
  2    add constraint t1_fk_t2
  3    foreign key(object_id)
  4    references t2(object_id)
  5    rely disable novalidate;
Table altered.

Ahora bien, cuando necesitamos columnas de ambas tablas, el desempeño se verá afectado negativamente, ya que debemos reunirlas nuevamente. La consulta utiliza un índice si se trata de un pequeño grupo de filas o amplios procedimientos hash join si hay numerosas filas que producir, como se muestra en el Listado 4.

Listado de Códigos 4: Consulta sobre visualizaciones VW que requieren procesamientos hash join y escaneo completo de tablas

Copy


SQL> set autotrace traceonly explain
SQL> select * from vw;

Execution Plan
-----------------------------------------
Plan hash value: 2959412835

-------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes  |TempSpc|  Cost (%CPU)|
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   100M|    13G|        |   1490K  (3)|
|   1 |  HASH JOIN         |      |   100M|    13G|   6198M|   1490K  (3)|
|   2 |   TABLE ACCESS FULL| T2   |   100M|  5054M|        |    317K  (4)|
|   3 |   TABLE ACCESS FULL| T1   |   100M|  8392M|        |    316K  (4)|
-------------------------------------------------------------------------

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

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Pero si alguna de sus consultas accede a las columnas de la segunda tabla, T2, usted puede eliminar la segunda tabla del plan de consulta. Esto sucede de manera transparente; como se muestra en el Listado 5. No necesita hacer nada para que esto ocurra.

Listado de Códigos 5: Eliminación transparente de T2 del plan de consulta

Copy


SQL> select OWNER, OBJECT_NAME,
  2             SUBOBJECT_NAME,
  3             DATA_OBJECT_ID,
  4             OBJECT_TYPE
  5    from vw;

Execution Plan
----------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  |  Byte | Cost (%CPU)  | Time
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100M|  8392M|     316K  (4)| 00:25:03
|   1 |  TABLE ACCESS FULL| T1   |   100M|  8392M|     316K  (4)| 00:25:03
--------------------------------------------------------------------------

Tenga en cuenta que la capacidad de eliminar las tablas innecesarias era nueva en Oracle Database 10g Release 2. ¡Y finalmente, utilice COMPRESS en las tablas!

Una solución alternativa sería utilizar un índice para crear una versión liviana de los datos frecuentemente consultados. El optimizador—cuando sea posible—utilizará un escaneo completo y rápido de índices (o proceso de ejecución de escaneo completo, utilizando I/O para múltiples bloques como el escaneo completo de tablas) para leer el subgrupo de columnas y tener acceso eficiente a todos los datos en la tabla cuando sea necesario. Por ejemplo, siguiendo el ejemplo anterior y suponiendo que las mismas seis columnas son las más destacadas, usted creará la versión liviana de los datos como se muestra en el Listado 6.

Listado de Códigos 6: Creación de versiones livianas de datos

Copy


SQL> create table t1 as
  2    select *
  3        from all_objects
  4      where 1=0;
Table created.

SQL> create index t1_idx on
  2    t1( OBJECT_ID, OWNER,
  3          OBJECT_NAME, SUBOBJECT_NAME,
  4          DATA_OBJECT_ID, OBJECT_TYPE );
Index created.

SQL> begin
  2       dbms_stats.set_table_stats
  3      ( user, 'T1',
  4        numrows => 100000000,
  5        numblks => 1000000 );
  6       dbms_stats.set_index_stats
  7      ( user, 'T1_IDX',
  8        numrows => 100000000,
  9        numlblks => 1000000/2 );
 10    end;
 11    /
PL/SQL procedure successfully completed.

Ahora el optimizador tiene dos estructuras a las que puede acceder—la tabla (T1) o el índice (T1_IDX)—para recuperar esas seis columnas.

Tenga en cuenta que estas estructuras dependen al menos de uno de los atributos en el índice definidos como NOT NULL. Si todas las columnas en el índice son anulables, este enfoque no funcionaría, ya que las entradas completamente nulas no se incluyen en el índice, por lo tanto no todas las filas serían necesariamente indexadas. En este ejemplo, OBJECT_ID es NOT NULL en la tabla, y el índice señalaría a todas las filas en la tabla.

Ahora, cuando consultemos todas las columnas (o al menos la columna no indexada), obtendremos un escaneo completo de la única tabla (T1), como se muestra en el Listado 7.

Listado de Códigos 7: Consultas utilizando el escaneo completo de tabla de T1

Copy


SQL> set autotrace traceonly explain
SQL> select * from t1;

Execution Plan
----------------
Plan hash value: 3617692013

-------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100M|  9536M|   320K  (5)| 00:25:25 |
|   1 |  TABLE ACCESS FULL| T1   |   100M|  9536M|   320K  (5)| 00:25:25 |
-------------------------------------------------------------------------

Y cuando solicitamos solo las columnas indexadas, obtendremos un escaneo completo y rápido del índice, como se muestra en el Listado 8. Tenga en cuenta que como hemos utilizado DBMS_STATS para informar al optimizador que el índice tiene aproximadamente la mitad de tamaño de la tabla, el costo del escaneo completo del índice (157K) será aproximadamente la mitad del costo del escaneo completo de la tabla (320K), y el tiempo de ejecución del escaneo completo del índice (12:31) es alrededor la mitad del tiempo de ejecución del escaneo completo de la tabla (25:25).

Listado de Códigos 8: Consultas utilizando el escaneo rápido y completo de índices de T1_IDX

Copy


SQL> select OWNER, OBJECT_NAME,
  2             SUBOBJECT_NAME,
  3             DATA_OBJECT_ID,
  4             OBJECT_TYPE
  5    from t1;

Execution Plan
--------------------------
Plan hash value: 1294651092

----------------------------------------------------------------------------
| Id | Operation           | Name   | Rows | Bytes | Cost (%CPU) | Time     |
----------------------------------------------------------------------------
| 0  | SELECT STATEMENT    |        | 100M | 7152M | 157K (4)    | 00:12:31 |
| 1  | INDEX FAST FULL SCAN| T1_IDX | 100M | 7152M | 157K (4)    | 00:12:31 |
----------------------------------------------------------------------------