La semana pasada tuve que aplicar el parche 11.2.0.2 sobre un equipo de desarrollo. Cuando entré a metalink y busqué el parche que aplicaba a mi SO (Solaris SPARC) me llamó la atención el tamaño del parche. Los ultimos parches que instalé recuerdo que no pesaban mucho mas de 1Gb. El parche de 11.2.0.2 sobre la plataforma que necesitaba pesa 5.1Gb!, y para AIX mas de 6Gb. Recien una vez que leí la documentación entendí el porque. El tema es que Oracle cambió la politica de aplicación de parches a partir de 11.2.0.2. Ahora no son mas incrementales, sino totales y ademas contienen todo el bundle, es decir el server, cliente, gateway, grid, etc.
Recuerdo que en 9i venia todo junto, si querias instalar solo un cliente necesitabas bajarte 3 archivos que contenian todo, lo cual resultaba engorroso. En 10g independizaron las instalaciones de cliente, server, grid, etc. Ahora parece que nuevamente hay que bajar todo y luego elegir en la instalación lo que necesitamos.
Es obligatorio usar un home separado para la instalación, al ser total no se puede parchear sobre el home actual. En mi caso ese nuevo requisito no me molesta ya que siempre considero como una buena practica instalar los parches sobre una copia en un home nuevo, para minimizar riesgos por si el parche falla a la mitad de la instalación y la vuelta atrás requiere respaldar los binarios anteriores. Si estan muy cortos de espacio, se complica un poco crear un home separado asi que en ese caso habrá que bajar las bases, respaldar los binarios, borrarlos e instalar el nuevo home.
La instalación sobre Solaris SPARC no tuvo contratiempos. Tuve que upgradear un Oracle 11g R1 (11.1.0.7) y un Oracle 11g R2 (11.2.0.1). Ambas actualizaciones se realizaron perfectamente y sin ningun contratiempo.
Mostrando entradas con la etiqueta Upgrade. Mostrar todas las entradas
Mostrando entradas con la etiqueta Upgrade. Mostrar todas las entradas
miércoles, 17 de noviembre de 2010
jueves, 12 de agosto de 2010
Benchmarking de performance de sentencias usando SQL Performance Analyzer (SPA)
En esta nota voy a mostrarles como usar SQL Performance Analyzer (SPA) que es parte del Suite Real Application Testing, y sirve para evaluar y predecir de que forma se afectaran los planes de las sentencias luego de cambios en el entorno tales como:
* Upgrade de BD, HW o SO.
* Cambios en la configuración de BD, HW o SO.
* Cambio de parametrizacion de BD
* Cambios en el esquema de datos (agregado de indices, vistas materializadas)
* Estado de las estadisticas de objetos y de sistema.
Este nuevo feature es muy util para analizar el impacto de cambio ya que permite "jugar" facilmente con el entorno y realizar reportes comparativos, pruebas de regresión, impacto de carga, etc.
Ahora voy a armar un escenario de prueba en 11g para comparar un simple count de la tabla T usando el optimizador por reglas (RBO) contra el mismo count usando el optimizador CBO. Es claro, que RBO esta desoportado desde 10g y que el ejemplo no representa un caso real, o tal vez si, pero va a servir para mostrar como funciona SPA y de paso mostrarles que tan "ciego" es RBO en ciertos casos que intuitivamente parecen triviales.
Voy a crear un tabla T con 1M de registros y con pctfree del 90% para consumir muchos bloques tal que la diferencia entre las ejecuciones que voy a comparar sea mas notoria. Luego voy a crear una PK por el campo id. Con RBO va a hacer un FULL SCAN sobre la tabla T, ya que no se da cuenta que tiene una PK y que podría hacer un full index scan que es mas rapido. Obviamente CBO se percata de esto ya que al ser justamente PK tiene la misma cantidad de registros que la tabla y por lo tanto sirve para responder a la pregunta de cuantos registros tiene la tabla T.
Ejecuto la sentencia de prueba, uso un hint para ubicarla mas facilmente en la vista dinamica y obtener su sqlid:
Ya tengo el sqlid, asi que lo que voy a hacer es armar un SQL Tuning Set (STS) usando el paquete DBMS_SQLTUNE (esto existe desde 10g, asi que lo podria hacer en 10g y luego migrarlo a 11g, por ejemplo para evaluar un upgrade entre esas versiones)
El STS que cree se llaman Prueba, ahora le cargo la sentencia desde su cursor en memoria:
Verificamos que efectivamente se haya creado el STS y que contenga la sentencia:
En este punto, y teniendo creado el STS, que contiene la sentencia mas la información de contexto para evaluarla, podemos comenzar a utilizar el paquete DBMS_SQLPA (existe a partir de 11g R1) para realizar la comparación. Para el ejemplo los paquetes DBMS_SQLTUNE y DBMS_SQLPA son complementarios. Con el primero armo el workload (que puede contener una o mas sentencias obtenidas desde el AWR, desde un cursor, desde otro STS e incluso desde un archivo de trace) y con el segundo realizo el analisis comparativo (benchmarking). A continuación veamos como realizar dicho análisis:
Primero creo una tarea de análisis:
Luego, configuro el ambiente para simular "el antes". En nuestro ejemplo la idea es comparar un count con RBO y con CBO, asi que seteo a nivel sesión el optimizador para que use RBO y ejecuto el analisis con dicho entorno:
Hago lo mismo para comparar "el despues", seteando el optimizador a su valor default en 11g:
Para realizar la comparación, se puede configurar sobre que metrica focalizarse, si no se aclara nada, se usa como metrica de comparación: "elapsed_time". En este ejemplo preferí usar "buffer_gets", ya que esta metrica es una de las que mas cambia entre los dos casos a comparar y por lo tanto hace mas contundente el reporte final.
Ejectuo el sp para realizar la comparación:
Una vez ejecutado el analisis vemos mediante un reporte un resumen de las diferencias:
Como se observa en el reporte, con CBO se tiene una mejora del 92%
Otro reporte con detalle de cada sentencia:
El ejemplo realiza una comparación trivial del procedimiento de SPA usando solo sqlplus (se podria usar EM para una comparación mas visual), pero que sirve para graficar su utilidad. Usando un procedimiento similar se podria realizar un analisis pre-upgrade que sirva para garantizar la estabilidad de las sentencias criticas post-upgrade sobre una base 11g. Para ello habria que realizar los siguiente pasos previos al analisis con SPA:
1. Crear el STS en la base a upgradear
Si la base a upgradear es 10g se puede crear el STS desde AWR determinando un intervalo representativo de la carga. Si la base a upgradear es 9i se puede obtener el STS desde un trace previamente generado en 9i durante un intervalo de carga real.
A continuación, muestro un ejemplo, que esta en la documentación oficial 10g, para
crear un STS desde AWR, usando un baseline previamente creado correspondiente a un intervalo con carga maxima "peak baseline", y se filtra para que el STS solo incluya las sentencias que se ejecutaron mas de 10 veces y con un ratio entre lecturas de disco y buffer gets mayor al 50%. Tambien se especifica que se recolecten las 30 sentencias TOP ordenadas por disk_reads/buffer_gets:
Si no se creo un baseline, tambien se puede parametrizar usando dos snapshosts id de AWR para especificar el intervalo a procesar.
2. Migrar el STS a la nueva base (11g)
Una vez creada y cargada la tabla stage, resta pasarla a la nueva base. Aca se puede usar data pump o el exp/imp convencional.
En resumen, podriamos usar este procedimiento para evaluar rapidamente el impacto de cambios sobre las sentencias, y por ende en los planes de ejecución, producto de realizar cambios en el entorno, como por ejemplo, cambiar de equipo, de discos, agregar cpu, cambios de version de base de datos, cambios en parametrizacion, etc.
Se puede "jugar" con distintos entornos y ver como se comporta las sentencias, realizar benchmarking y analisis con diferentes estrategias y parametrizaciones, etc y asi poder inferir el comportamiento previo al cambio y prevenir la inestabilidad de las aplicaciones cuando ya es demasiado tarde y la vuelta atras implica un alto costo.
* Upgrade de BD, HW o SO.
* Cambios en la configuración de BD, HW o SO.
* Cambio de parametrizacion de BD
* Cambios en el esquema de datos (agregado de indices, vistas materializadas)
* Estado de las estadisticas de objetos y de sistema.
Este nuevo feature es muy util para analizar el impacto de cambio ya que permite "jugar" facilmente con el entorno y realizar reportes comparativos, pruebas de regresión, impacto de carga, etc.
Ahora voy a armar un escenario de prueba en 11g para comparar un simple count de la tabla T usando el optimizador por reglas (RBO) contra el mismo count usando el optimizador CBO. Es claro, que RBO esta desoportado desde 10g y que el ejemplo no representa un caso real, o tal vez si, pero va a servir para mostrar como funciona SPA y de paso mostrarles que tan "ciego" es RBO en ciertos casos que intuitivamente parecen triviales.
Voy a crear un tabla T con 1M de registros y con pctfree del 90% para consumir muchos bloques tal que la diferencia entre las ejecuciones que voy a comparar sea mas notoria. Luego voy a crear una PK por el campo id. Con RBO va a hacer un FULL SCAN sobre la tabla T, ya que no se da cuenta que tiene una PK y que podría hacer un full index scan que es mas rapido. Obviamente CBO se percata de esto ya que al ser justamente PK tiene la misma cantidad de registros que la tabla y por lo tanto sirve para responder a la pregunta de cuantos registros tiene la tabla T.
create table t (id int,val varchar2(10)) pctfree 90;
insert into t
as select rownum ,dbms_random.string('a',10)
from dual
connect by rownum <= 1000000 ;
alter table t add primary key (id);
Ejecuto la sentencia de prueba, uso un hint para ubicarla mas facilmente en la vista dinamica y obtener su sqlid:
select count(1) /*+ Prueba SPA */ from t;
select sql_id from v$sql
where sql_text like '%Prueba SPA%' and sql_text not like '%sql_text%';
5r2ufj2vqkk4p
Ya tengo el sqlid, asi que lo que voy a hacer es armar un SQL Tuning Set (STS) usando el paquete DBMS_SQLTUNE (esto existe desde 10g, asi que lo podria hacer en 10g y luego migrarlo a 11g, por ejemplo para evaluar un upgrade entre esas versiones)
begin
dbms_sqltune.create_sqlset(sqlset_name => 'Prueba',description => 'STS de Prueba');
end;
El STS que cree se llaman Prueba, ahora le cargo la sentencia desde su cursor en memoria:
DECLARE
l_cursor DBMS_SQLTUNE.sqlset_cursor;
BEGIN
OPEN l_cursor FOR
SELECT VALUE(p)
FROM TABLE (
DBMS_SQLTUNE.select_cursor_cache (
'sql_id = ''5r2ufj2vqkk4p''', -- basic_filter
NULL, -- object_filter
NULL, -- ranking_measure1
NULL, -- ranking_measure2
NULL, -- ranking_measure3
NULL, -- result_percentage
1) -- result_limit
) p;
DBMS_SQLTUNE.load_sqlset (
sqlset_name => 'Prueba',
populate_cursor => l_cursor);
END;
Verificamos que efectivamente se haya creado el STS y que contenga la sentencia:
select * from user_sqlset where name = 'Prueba';
NAME ID
------------------------------ ----------
DESCRIPTION
------------------------------------------------------------------------------------
CREATED LAST_MODI STATEMENT_COUNT
--------- --------- ---------------
Prueba 10
STS de Prueba
12-AGO-10 12-AGO-10 1
select sqlset_name,sql_id from user_sqlset_statements where sql_id = '5r2ufj2vqkk4p';
SQLSET_NAME SQL_ID
------------------------------ -------------
Prueba 5r2ufj2vqkk4p
En este punto, y teniendo creado el STS, que contiene la sentencia mas la información de contexto para evaluarla, podemos comenzar a utilizar el paquete DBMS_SQLPA (existe a partir de 11g R1) para realizar la comparación. Para el ejemplo los paquetes DBMS_SQLTUNE y DBMS_SQLPA son complementarios. Con el primero armo el workload (que puede contener una o mas sentencias obtenidas desde el AWR, desde un cursor, desde otro STS e incluso desde un archivo de trace) y con el segundo realizo el analisis comparativo (benchmarking). A continuación veamos como realizar dicho análisis:
Primero creo una tarea de análisis:
declare
l_out char(50);
begin
l_out:= dbms_sqlpa.create_analysis_task(
sqlset_name => 'Prueba',
task_name => 'Prueba_TSK');
end;
Luego, configuro el ambiente para simular "el antes". En nuestro ejemplo la idea es comparar un count con RBO y con CBO, asi que seteo a nivel sesión el optimizador para que use RBO y ejecuto el analisis con dicho entorno:
alter session set optimizer_mode = RULE;
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'Prueba_TSK',
execution_type => 'TEST EXECUTE',
execution_name => 'Prueba_EXEC_antes');
end;
Hago lo mismo para comparar "el despues", seteando el optimizador a su valor default en 11g:
alter session set optimizer_mode = ALL_ROWS;
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'Prueba_TSK',
execution_type => 'TEST EXECUTE',
execution_name => 'Prueba_EXEC_despues');
end;
Para realizar la comparación, se puede configurar sobre que metrica focalizarse, si no se aclara nada, se usa como metrica de comparación: "elapsed_time". En este ejemplo preferí usar "buffer_gets", ya que esta metrica es una de las que mas cambia entre los dos casos a comparar y por lo tanto hace mas contundente el reporte final.
BEGIN
DBMS_SQLPA.set_analysis_task_parameter('Prueba_TSK',
'comparison_metric',
'buffer_gets');
END;
Ejectuo el sp para realizar la comparación:
BEGIN
DBMS_SQLPA.execute_analysis_task(
task_name => 'Prueba_TSK',
execution_type => 'compare performance',
execution_params => dbms_advisor.arglist(
'execution_name1',
'Prueba_EXEC_antes',
'execution_name2',
'Prueba_EXEC_despues')
);
END;
Una vez ejecutado el analisis vemos mediante un reporte un resumen de las diferencias:
SET LONG 1000000
SET PAGESIZE 0
SET LINESIZE 200
SET LONGCHUNKSIZE 200
SET TRIMSPOOL ON
rop@DESA11G> SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('Prueba_TSK', 'TEXT', 'TYPICAL', 'SUMMARY') from
dual;
General Information
---------------------------------------------------------------------------------------------
Task Information: Workload Information:
--------------------------------------------- ---------------------------------------------
Task Name : Prueba_TSK SQL Tuning Set Name : Prueba
Task Owner : ROP SQL Tuning Set Owner : ROP
Description : Total SQL Statement Count : 1
Execution Information:
---------------------------------------------------------------------------------------------
Execution Name : EXEC_7692 Started : 08/12/2010 16:27:22
Execution Type : COMPARE PERFORMANCE Last Updated : 08/12/2010 16:27:22
Description : Global Time Limit : UNLIMITED
Scope : COMPREHENSIVE Per-SQL Time Limit : UNUSED
Status : COMPLETED Number of Errors : 0
Analysis Information:
---------------------------------------------------------------------------------------------
Comparison Metric: BUFFER_GETS
------------------
Workload Impact Threshold: 1%
--------------------------
SQL Impact Threshold: 1%
----------------------
Before Change Execution: After Change Execution:
--------------------------------------------- ---------------------------------------------
Execution Name : Prueba_EXEC_antes Execution Name : Prueba_EXEC_despues
Execution Type : TEST EXECUTE Execution Type : TEST EXECUTE
Scope : COMPREHENSIVE Scope : COMPREHENSIVE
Status : COMPLETED Status : COMPLETED
Started : 08/12/2010 16:26:29 Started : 08/12/2010 16:27:04
Last Updated : 08/12/2010 16:27:13 Last Updated : 08/12/2010 16:27:13
Global Time Limit : UNLIMITED Global Time Limit : UNLIMITED
Per-SQL Time Limit : UNUSED Per-SQL Time Limit : UNUSED
Number of Errors : 0 Number of Errors : 0
Report Summary
---------------------------------------------------------------------------------------------
Projected Workload Change Impact:
-------------------------------------------
Overall Impact : 92.05%
Improvement Impact : 92.05%
Regression Impact : 0%
SQL Statement Count
-------------------------------------------
SQL Category SQL Count Plan Change Count
Overall 1 1
Improved 1 1
Top SQL Statements Sorted by their Absolute Value of Change Impact on the Workload
--------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
| | | Impact on | Metric | Metric | Impact | % Workload | % Workload | Plan |
| object_id | sql_id | Workload | Before | After | on SQL | Before | After | Change |
----------------------------------------------------------------------------------------------------
| 4 | 5r2ufj2vqkk4p | 92.05% | 26429 | 2102 | 92.05% | 100% | 100% | y |
----------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
Como se observa en el reporte, con CBO se tiene una mejora del 92%
Otro reporte con detalle de cada sentencia:
Transcurrido: 00:00:00.32
rop@DESA11G> SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('Prueba_TSK',
'TEXT', 'TYPICAL', 'FINDINGS') from dual;
General Information
---------------------------------------------------------------------------------------------
Task Information: Workload Information:
--------------------------------------------- ---------------------------------------------
Task Name : Prueba_TSK SQL Tuning Set Name : Prueba
Task Owner : ROP SQL Tuning Set Owner : ROP
Description : Total SQL Statement Count : 1
Execution Information:
---------------------------------------------------------------------------------------------
Execution Name : EXEC_7692 Started : 08/12/2010 16:27:22
Execution Type : COMPARE PERFORMANCE Last Updated : 08/12/2010 16:27:22
Description : Global Time Limit : UNLIMITED
Scope : COMPREHENSIVE Per-SQL Time Limit : UNUSED
Status : COMPLETED Number of Errors : 0
Analysis Information:
---------------------------------------------------------------------------------------------
Comparison Metric: BUFFER_GETS
------------------
Workload Impact Threshold: 1%
--------------------------
SQL Impact Threshold: 1%
----------------------
Before Change Execution: After Change Execution:
--------------------------------------------- ---------------------------------------------
Execution Name : Prueba_EXEC_antes Execution Name : Prueba_EXEC_despues
Execution Type : TEST EXECUTE Execution Type : TEST EXECUTE
Scope : COMPREHENSIVE Scope : COMPREHENSIVE
Status : COMPLETED Status : COMPLETED
Started : 08/12/2010 16:26:29 Started : 08/12/2010 16:27:04
Last Updated : 08/12/2010 16:27:13 Last Updated : 08/12/2010 16:27:13
Global Time Limit : UNLIMITED Global Time Limit : UNLIMITED
Per-SQL Time Limit : UNUSED Per-SQL Time Limit : UNUSED
Number of Errors : 0 Number of Errors : 0
Report Details: Statements Sorted by their Absolute Value of Change Impact on the Workload
---------------------------------------------------------------------------------------------
SQL Details:
-----------------------------
Object ID : 4
Schema Name : ROP
SQL ID : 5r2ufj2vqkk4p
Execution Frequency : 1
SQL Text : select count(1) /*+ Prueba SPA */ from t
Execution Statistics:
-----------------------------
------------------------------------------------------------------------------------------------
| | Impact on | Value | Value | Impact | % Workload | % Workload |
| Stat Name | Workload | Before | After | on SQL | Before | After |
------------------------------------------------------------------------------------------------
| elapsed_time | 91.11% | 15.477 | 1.376 | 91.11% | 100% | 100% |
| parse_time | -1900% | 0 | .019 | -1.9% | 0% | 100% |
| cpu_time | 82.2% | 1.18 | .21 | 82.2% | 100% | 100% |
| buffer_gets | 92.05% | 26429 | 2102 | 92.05% | 100% | 100% |
| cost | -59500% | 0 | 595 | -59500% | 0% | 100% |
| reads | 92.08% | 26418 | 2092 | 92.08% | 100% | 100% |
| writes | 0% | 0 | 0 | 0% | 0% | 0% |
| io_interconnect_bytes | 92.08% | 216416256 | 17137664 | 92.08% | 100% | 100% |
| rows | | 1 | 1 | | | |
------------------------------------------------------------------------------------------------
Findings (3):
-----------------------------
1. Ha mejorado el rendimiento de este SQL.
2. La estructura del plan de ejecución SQL ha cambiado.
3. La estructura del plan de ejecución SQL de la versión anterior de la carga
de trabajo es distinta del correspondiente plan almacenado en el juego de
ajustes SQL.
Execution Plan Before Change:
-----------------------------
Plan Id : 10810
Plan Hash Value : 1842905362
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | SORT AGGREGATE | | | | | |
| 2 | TABLE ACCESS FULL | T | | | | |
-----------------------------------------------------------------
Execution Plan After Change:
-----------------------------
Plan Id : 10811
Plan Hash Value : 2499172778
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 595 | 00:00:08 |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | INDEX FAST FULL SCAN | SYS_C0046154 | 908242 | | 595 | 00:00:08 |
----------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
---------------------------------------------------------------------------------------------
Transcurrido: 00:00:00.59
El ejemplo realiza una comparación trivial del procedimiento de SPA usando solo sqlplus (se podria usar EM para una comparación mas visual), pero que sirve para graficar su utilidad. Usando un procedimiento similar se podria realizar un analisis pre-upgrade que sirva para garantizar la estabilidad de las sentencias criticas post-upgrade sobre una base 11g. Para ello habria que realizar los siguiente pasos previos al analisis con SPA:
1. Crear el STS en la base a upgradear
Si la base a upgradear es 10g se puede crear el STS desde AWR determinando un intervalo representativo de la carga. Si la base a upgradear es 9i se puede obtener el STS desde un trace previamente generado en 9i durante un intervalo de carga real.
A continuación, muestro un ejemplo, que esta en la documentación oficial 10g, para
crear un STS desde AWR, usando un baseline previamente creado correspondiente a un intervalo con carga maxima "peak baseline", y se filtra para que el STS solo incluya las sentencias que se ejecutaron mas de 10 veces y con un ratio entre lecturas de disco y buffer gets mayor al 50%. Tambien se especifica que se recolecten las 30 sentencias TOP ordenadas por disk_reads/buffer_gets:
DECLARE
baseline_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN baseline_cursor FOR
SELECT VALUE(p)
FROM TABLE (DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
'peak baseline',
'executions >= 10 AND disk_reads/buffer_gets >= 0.5',
NULL,
'disk_reads/buffer_gets',
NULL, NULL, NULL,
30)) p;
DBMS_SQLTUNE.LOAD_SQLSET(
sqlset_name => 'my_sql_tuning_set',
populate_cursor => baseline_cursor);
END;
Si no se creo un baseline, tambien se puede parametrizar usando dos snapshosts id de AWR para especificar el intervalo a procesar.
2. Migrar el STS a la nueva base (11g)
-- Crea la tabla stage para almacenar el STS para luego transferirlo a la nueva base
begin
DBMS_SQLTUNE.create_stgtab_sqlset(table_name => 'TBL_STG_STS',
schema_name => user);
end;
-- Graba el STS en la tabla stage
begin
DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name => 'Prueba',
staging_table_name => 'TBL_STG_STS');
end;
Una vez creada y cargada la tabla stage, resta pasarla a la nueva base. Aca se puede usar data pump o el exp/imp convencional.
-- Crea el STS generado en 10g desde la tabla stage en 11g
begin
DBMS_SQLTUNE.unpack_stgtab_sqlset(sqlset_name => 'Prueba',
staging_table_name => 'TBL_STG_STS',
replace => TRUE);
end;
En resumen, podriamos usar este procedimiento para evaluar rapidamente el impacto de cambios sobre las sentencias, y por ende en los planes de ejecución, producto de realizar cambios en el entorno, como por ejemplo, cambiar de equipo, de discos, agregar cpu, cambios de version de base de datos, cambios en parametrizacion, etc.
Se puede "jugar" con distintos entornos y ver como se comporta las sentencias, realizar benchmarking y analisis con diferentes estrategias y parametrizaciones, etc y asi poder inferir el comportamiento previo al cambio y prevenir la inestabilidad de las aplicaciones cuando ya es demasiado tarde y la vuelta atras implica un alto costo.
viernes, 30 de julio de 2010
Como realizar un Upgrade Manual desde versión 10g a 11g (Procedimiento Paso a Paso)
Para actualizar (upgrade) una la versión de una base Oracle existen principalmente cuatro métodos:
- Usar el Asistente Grafico (DBUA). Es el metodo sugerido en los manuales.
- Usar scripting o forma manual (la que yo siempre tiendo a usar)
- Export/Import
- CTAS (para mi gusto la mas complicada, hay que crear dblink, armar los scripts para el traspaso de los objetos, etc)
En esta nota voy a escribir un procedimiento paso a paso para actualizar usando el método manual (el 2 en la lista de arriba):
Pre-Upgrade
1. Si el upgrade es sobre el mismo equipo habria que instalar el software (el motor) 11g en un nuevo home. Se puede usar el mismo user oracle de la instalación 10g corriente y setear las variables de entorno para 11g o se podria usar un nuevo usuario oracle (ej: oracle11g).
2. Una vez instalado el software conectarse con el usuario oracle de la instalación 11g
3. Conectarse con el usuario 10g (ej: oracle) o si se esta usando el mismo usuario setear las variables de ambiente para usar el motor 10g, para ejecutar el script copiado en el paso 2. Este script brinda información previa al upgrade que se usará para preparar la versión actual para que no haya problemas durante el upgrade
4. Conectarse con sqlplus:
$ sqlplus / as sysdba
5. Setear el spool para que no quede registro de la ejecución del script
sqlplus> spool upg_info.log
6. Ejecutar el script:
sqlplus> @/tmp/utlu112i.sql;
7. Desactivar el spool:
sqlplus> spool off
A continuación se muestra una salida típica del script utlu112i.sql sobre una base llamada ROP10g sobre un equipo Solaris 10.
Oracle Database 11.2 Pre-Upgrade Information Tool 07-26-2010 15:14:36
**********************************************************************
Database:
**********************************************************************
--> name: ROP10G
--> version: 10.2.0.4.0
--> compatible: 10.2.0.1.0
--> blocksize: 8192
--> platform: Solaris[tm] OE (64-bit)
--> timezone file: V4
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 313 MB
.... AUTOEXTEND additional space required: 83 MB
--> UNDO tablespace is adequate for the upgrade.
.... minimum required size: 121 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 73 MB
.... AUTOEXTEND additional space required: 23 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 61 MB
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
WARNING: --> "sga_target" needs to be increased to at least 672 MB
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
--> "background_dump_dest" replaced by "diagnostic_dest"
--> "user_dump_dest" replaced by "diagnostic_dest"
--> "core_dump_dest" replaced by "diagnostic_dest"
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] VALID
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database contains stale optimizer statistics.
.... Refer to the 11g Upgrade Guide for instructions to update
.... statistics prior to upgrading the database.
.... Component Schemas with stale statistics:
.... SYS
WARNING: --> Database contains INVALID objects prior to upgrade.
.... The list of invalid SYS/SYSTEM objects was written to
.... registry$sys_inv_objs.
.... The list of non-SYS/SYSTEM objects was written to
.... registry$nonsys_inv_objs.
.... Use utluiobj.sql after the upgrade to identify any new invalid
.... objects due to the upgrade.
.... USER PUBLIC has 1 INVALID objects.
.... USER SYS has 2 INVALID objects.
....
....
SQL>
Controles que se realizan con la herramienta de pre-upgrade
El script “utlu112i.sql” realiza el chequeo de los siguientes puntos:
- Chequea si las estadísticas de diccionario están actualizadas.
- Chequea si existen objetos invalidos.
- Chequea si la configuración de SGA cumple con los requerimientos minimos en 11g.
- Chequea si hay database links con passwords (11g encripta las passwords).
- Se asegura que no haya archivos que necesiten recovery.
- Se asegura que no haya archivos en modo backup.
- Si el recyclebin esta activado chequea si esta vacio (si esta totalmente purgado).
- Chequea si los archivos de timezone son de tipo 4 (los archivos que estan en $ORACLE_HOME/oracore/zoneinfo).
- Revisa si hay refrescos de Vistas Materializadas pendientes.
- Revisa si hay transacciones distribuidas pendientes.
Una vez que se corrigieron los warnings reportados por el script anterior se puede proceder a realizar el upgrade
Upgrade
1. Conectarse con el owner (usuario oracle) de la instancia 10g.
1.1 Verificar que no haya procesos oracle con el mismo nombre de la instancia
$ps -ef | grep -i ora | grep -v grep
1.2 Verificar que las variables de ambiente esten bien configuradas
$env | grep -i ora
2. Crear archivo pfile desde el spfile y copiarlo a $ORACLE_HOME/dbs en el nuevo equipo
3. Editar el pfile en el equipo nuevo y modificar los parametros que sea necesario (deprecated).
4. Bajar la base 10g en modo normal, si no hubiera conexiones sino se podria bajar en modo transactional, para que deje que termine las transacciones, no deje que se abran nuevas conexiones y baje la base en modo consistente y que no requiere revover. Como caso extremo tambien se podria considerar bajar la base en modo immediate.
sqlplus> shutdown transactional
5. Si la base 11g esta en otro equipo hay que transferir los archivos de base de datos (datafiles,redologs,controlfiles) al nuevo equipo en el mismo directorio y con los mismos permisos (usar comando unix scp, ftp, algun mecanismo de copiado de logical groups, etc). Si se realizará el upgrade sobre el mismo equipo y sobre los mismos archivos de base de datos no hace falta hacer nada en este punto.
6. Conectarse a la instancia 11g con sysdba
$ sqlplus / as sysdba
7. Levantar la base 11g en modo upgrade
SQPLUS> STARTUP UPGRADE
8. Setear el spool
SQLPLUS> spool upgrade11g.log
9. Ejecutar el script para obtener la información pre-upgrade
SQLPLUS> @?/rdbms/admin/catupgrd.sql;
10. Desactivar el spooling
SQLPLUS> spool off
11. Ejecutar el script utlu112s.sql para ver el resultado del upgrade.
SQLPLUS>@?/rdbms/admin/utlu112s.sql
12. Ejecutar el script utlrp.sql para recompilar stored procedures y clases java.
i) SQLPLUS>@?/rdbms/admin/utlrp.sql
ii) SQLPLUS>exec UTL_RECOMP.RECOMP_SERIAL ();
13. Verificar que todos las paquetes y clases java quedaron compiladas
SQLPLUS> select count(1) from dba_invalid_objects.
14. Crear spfile desde el pfile original.
SQLPLUS>create spfile from pfile;
15. Reiniciar la instancia en forma normal.
Ejemplo de Salida del Reporte de información post-upgrade
A continuación se muestra una salida típica del script utlu112i.sql sobre una base llamada ROP10g sobre un equipo Solaris 10
SQL> @?/rdbms/admin/utlu112s.sql;
Oracle Database 11.2 Post-Upgrade Status Tool 07-26-2010 14:32:12
Component Status Version HH:MM:SS
Oracle Server
VALID 11.2.0.1.0 00:24:02
Gathering Statistics
. 00:01:32
Total Upgrade Time: 00:25:36
PL/SQL procedure successfully completed.
Post-Upgade
1. Analizar password case-sensitive
SQLPLUS>alter system set sec_case_sensitive_logon = false scope=both;
Lo ideal seria dejar este parametro en true (default) , ya que fortalece la seguridad, pero habría que analizar como afecta algunas aplicaciones (por ejemplo algunas versiones de TOAD no se podrán conectar)
2. Setear el parametro COMPATIBLE a 11.2.0
SQLPLUS>alter system set compatible = '11.2.0' scope=spfile;
3. Habilitar los umbrales para alertas sobre tablespaces.
4. Reiniciar la instancia
5. Verificar conexión a través del listener
6. Dependiendo del tipo de backup y la herramienta que se use a veces es necesario cambiar la identificación interna de la base para que se tome como una nueva base
Como Cambiar el dbid:
SQLPLUS>shutdown immediate
SQLPLUS>startup mount
$ nid TARGET=SYS
SQLPLUS>startup mount
SQLPLUS>alter database open resetlogs
6 Backup full de la base de datos.
miércoles, 16 de septiembre de 2009
Como minimizar problemas luego de un upgrade de versión de Oracle (Caso2: Cambio del orden de evaluación de predicados)
En esta nueva nota les voy a contar un problema con el que se pueden encontrar al migrar desde version 8i hacia 9i o superior. El principal tema radica en la evolución constante que va teniendo el optimizador para estimar el costo de acceso a los datos de las sentencias. En la versión 7, donde apareció por primera vez el optimizador por costos, el costo se calculaba simplemente ponderando por la cantidad de requerimientos de lectura a disco. Esto, como es sabido, provocó un rechazo a cambiar de RBO a CBO bastante generalizado en su momento, dado que los planes de ejecución comenzaban a hacer cosas extrañas, causando importantes problemas de rendimiento generalizado. Por tal motivo, la mayoria de las compañias continuaron usando el optimizador por reglas, ya que les garantizaba que no se alteraran los planes y que no se destabilizaran las aplicaciones. El principal problema de usar solo los read request para generar el costo en Oracle 7 fue no considerar el caching que existe en distintos niveles.
A partir de 8i, se comenzó a ponderar por tipo de lectura (single reads y multiblock reads) y por tamaño y tiempo de lectura. Esto mejoró bastante la calidad de los planes generados y dió mayor confianza a las empresas para animarse a cambiar a CBO, mas que nada porque Oracle Corporation comenzó a incentivar fuertemente a salir de RBO, discontinuado a partir de 7.x (año 1992) y desoportado desde 10g. Si bien el comportamiento en 8i fue mucho mas estable faltaba tomar en cuenta algo muy importante, el tiempo de cpu.
Recien a partir de 9i se incluyó en el calculo del costo el tiempo insumido en procesamiento de cpu, anteriormente la formula para calcular solo tomaba en cuenta caracteristicas de i/o. En consecuencia con esta nueva variable de ponderación el optimizador puede cambiar el orden de evaluación de los predicados si estima que con ese nuevo orden se minimiza el tiempo de cpu. Este reordenamiento puede causar que ciertas consultas fallen en 9i+ y no en 8i. Este fallo generalmente se debe a una inconsistencia de datos, que antes quedaba "tapada" y que ahora al evaluar en otro orden genera un error, por ejemplo con una conversión implicita.
Vayamos a los ejemplos para graficar mejor este tema:
Primero voy a crear la siguiente tabla:
La tabla T tiene 3 columnas:
C1: solo tendrá valores 30 posibles valores (de 0 a 29) y es de tipo varchar2.
N1: tiene valores distintos del 1 al 5000.
N2: tiene los mismos valores de C1 pero es de tipo number.
Para simular estar en 8i, voy a cambiar el parametro de session "optimizer_features_enable" para que el optimizador se comporte con un optimizador de 8i.
Ahora, ejecuto una sentencia que cuenta la cantidad de filas filtrando la tabla
por los tres campos.
Mirando la información predicados (Predicate Information) notamos dos cosas interesantes. Primero hay una nota que nos advierte que el costo por cpu esta en off. Lo cual es lógico, por lo que comenté mas arriba respecto a que 8i no ponderaba por cpu y si bien estamos en 10g, recordemos que cambié el comportamiento del optimizador a 8i. Segundo vemos que los predicados se evaluaron en orden y que hubo una conversión implicita (TO_NUMBER()).
Vuelvo a poner el optimizador a su valor default y me fijo evaluo el plan:
El plan no cambió, se hizo sampleo dinamico ya que no habia recolectado estadisticas, pero lo mas importante a destacar es el cambio en la evaluación de los filtros. Notemos que ahora la conversión implicita se dejó para lo ultimo. Por que hizo eso el optimizador?, bien, pensemos que una conversión implica ciclos de cpu y entonces, porque no mejor evaluarlo al final cuando seguramente queden menos filas, ya que se van filtrando con los dos predicados o filtros anteriores, y asi minimizar la cantidad de conversiones, no?. Como ya dijimos el optimizador en versiones 9i+ se preocupa por el costo de procesamiento de cpu y por lo tanto puede realizar ciertos ajustes (reordenamiento de predicados, merge de subqueries, etc) si con eso se reduce la utilización de cpu.
Analicemos mas en detalle el ejemplo y como se evalua con cpu costing en ON y en OFF
Con CPU Costing en OFF (8i)
filter(TO_NUMBER("C1")=1 AND "N1"=1111 AND "N2"=1)
El primer predicado (TO_NUMBER("C1")=1) evalua 5000 filas y como resultado de ese filtro se queda con 167 filas.
El segundo predicado (N1=1111) evalua 167 filas y se queda con 1.
El tercer predicado (N2=1) evalua 1 y devuelve 1 (el resultado del count()).
Con CPU Costing en ON (9i+)
filter("N1"=1111 AND "N2"=1 AND TO_NUMBER("C1")=1)
El primer predicado (N1=1111) evalua 5000 filas y solo devuelve 1 fila.
El segundo predicado (N2=1) compara la fila y se la pasa al siguiente predicado.
El tercer predicado (TO_NUMBER("C1")=1) evalua una sola fila y devuelve el resultado.
En base al ejemplo analizado se ve claramente que con cpu costing apagado se deben realizar 5000 operaciones implicitas contra 1 operacion implicita cuando se tiene activado el costeo por cpu. Con este ejemplo sencillo, pero representativo, se puede ver lo importante del ordenamiento de la evaluación para minimizar el uso de recurso de cpu y por ende mejorar el tiempo de respuesta general.
Ahora veamos como fijar el orden de evaluación sin cambiar el comportamiento general de la sesion usando el hint "ordered_predicates":
Con el hint la evaluación es ordenada, tal cual hubiese sido en 8i por default.
En el plan no se muestró el costo de cpu, pero consultando la tabla de soporte PLAN_TABLE podemos ver que costos fueron asignados de acuerdo al orden de evaluación.
El costo de cpu al evaluar en 10g fue de 1302275 (con reordenamiento) y el costo de cpu sin ordenamiento fue de 1802225 (8i)
Como demostré con el ejemplo, el optimizador evalua los predicados de forma tal de optimizar el uso de cpu. Este cambio podría hacer fallar ciertos codigos que antes funcionaban y que tenian inconsistencias de datos, como por ejemplo, tener valores no numericos sobre campos varchar que deben tener numeros, y entonces al convertir implicitamente se genere un error de invalid number (ORA-01722). Queda claro que si ocurre esto es debido a un mal diseño, ya que una columna que aloja solo números no debería ser de tipo varchar, ya que de esta forma se promueve, por un lado inconsistencias en los datos y por otro lado, se afecta el rendimiento debido a las conversiones implicitas.
A partir de 8i, se comenzó a ponderar por tipo de lectura (single reads y multiblock reads) y por tamaño y tiempo de lectura. Esto mejoró bastante la calidad de los planes generados y dió mayor confianza a las empresas para animarse a cambiar a CBO, mas que nada porque Oracle Corporation comenzó a incentivar fuertemente a salir de RBO, discontinuado a partir de 7.x (año 1992) y desoportado desde 10g. Si bien el comportamiento en 8i fue mucho mas estable faltaba tomar en cuenta algo muy importante, el tiempo de cpu.
Recien a partir de 9i se incluyó en el calculo del costo el tiempo insumido en procesamiento de cpu, anteriormente la formula para calcular solo tomaba en cuenta caracteristicas de i/o. En consecuencia con esta nueva variable de ponderación el optimizador puede cambiar el orden de evaluación de los predicados si estima que con ese nuevo orden se minimiza el tiempo de cpu. Este reordenamiento puede causar que ciertas consultas fallen en 9i+ y no en 8i. Este fallo generalmente se debe a una inconsistencia de datos, que antes quedaba "tapada" y que ahora al evaluar en otro orden genera un error, por ejemplo con una conversión implicita.
Vayamos a los ejemplos para graficar mejor este tema:
Primero voy a crear la siguiente tabla:
rop@DESA10G> create table t as
2 select to_char(mod(rownum,30)) c1,
3 rownum n1,
4 mod(rownum,30) n2
5 from dual
6 connect by rownum <= 5000; Tabla creada.
La tabla T tiene 3 columnas:
C1: solo tendrá valores 30 posibles valores (de 0 a 29) y es de tipo varchar2.
N1: tiene valores distintos del 1 al 5000.
N2: tiene los mismos valores de C1 pero es de tipo number.
Para simular estar en 8i, voy a cambiar el parametro de session "optimizer_features_enable" para que el optimizador se comporte con un optimizador de 8i.
rop@DESA10G> alter session set optimizer_features_enable = '8.1.7';
Sesión modificada.
Ahora, ejecuto una sentencia que cuenta la cantidad de filas filtrando la tabla
por los tres campos.
rop@DESA10G> ed
Escrito file afiedt.buf
1 explain plan for
2 select count(1) from t
3* where c1 = 1 and n1 = 1111 and n2 = 1
rop@DESA10G> /
Explicado.
rop@DESA10G> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1842905362
-----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 48 | 2 |
| 1 | SORT AGGREGATE | | 1 | 48 | |
|* 2 | TABLE ACCESS FULL| T | 1 | 48 | 2 |
-----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_NUMBER("C1")=1 AND "N1"=1111 AND "N2"=1)
Note
-----
- cpu costing is off (consider enabling it)
18 filas seleccionadas.
Mirando la información predicados (Predicate Information) notamos dos cosas interesantes. Primero hay una nota que nos advierte que el costo por cpu esta en off. Lo cual es lógico, por lo que comenté mas arriba respecto a que 8i no ponderaba por cpu y si bien estamos en 10g, recordemos que cambié el comportamiento del optimizador a 8i. Segundo vemos que los predicados se evaluaron en orden y que hubo una conversión implicita (TO_NUMBER()).
Vuelvo a poner el optimizador a su valor default y me fijo evaluo el plan:
rop@DESA10G> alter session set optimizer_features_enable = '10.2.0.4';
Sesión modificada.
rop@DESA10G> explain plan for
2 select count(1) from t
3 where c1 = 1 and n1 = 1111 and n2 = 1;
Explicado.
rop@DESA10G> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1842905362
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 48 | 5 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 48 | | |
|* 2 | TABLE ACCESS FULL| T | 1 | 48 | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("N1"=1111 AND "N2"=1 AND TO_NUMBER("C1")=1)
Note
-----
- dynamic sampling used for this statement
18 filas seleccionadas.
El plan no cambió, se hizo sampleo dinamico ya que no habia recolectado estadisticas, pero lo mas importante a destacar es el cambio en la evaluación de los filtros. Notemos que ahora la conversión implicita se dejó para lo ultimo. Por que hizo eso el optimizador?, bien, pensemos que una conversión implica ciclos de cpu y entonces, porque no mejor evaluarlo al final cuando seguramente queden menos filas, ya que se van filtrando con los dos predicados o filtros anteriores, y asi minimizar la cantidad de conversiones, no?. Como ya dijimos el optimizador en versiones 9i+ se preocupa por el costo de procesamiento de cpu y por lo tanto puede realizar ciertos ajustes (reordenamiento de predicados, merge de subqueries, etc) si con eso se reduce la utilización de cpu.
Analicemos mas en detalle el ejemplo y como se evalua con cpu costing en ON y en OFF
Con CPU Costing en OFF (8i)
filter(TO_NUMBER("C1")=1 AND "N1"=1111 AND "N2"=1)
El primer predicado (TO_NUMBER("C1")=1) evalua 5000 filas y como resultado de ese filtro se queda con 167 filas.
El segundo predicado (N1=1111) evalua 167 filas y se queda con 1.
El tercer predicado (N2=1) evalua 1 y devuelve 1 (el resultado del count()).
Con CPU Costing en ON (9i+)
filter("N1"=1111 AND "N2"=1 AND TO_NUMBER("C1")=1)
El primer predicado (N1=1111) evalua 5000 filas y solo devuelve 1 fila.
El segundo predicado (N2=1) compara la fila y se la pasa al siguiente predicado.
El tercer predicado (TO_NUMBER("C1")=1) evalua una sola fila y devuelve el resultado.
En base al ejemplo analizado se ve claramente que con cpu costing apagado se deben realizar 5000 operaciones implicitas contra 1 operacion implicita cuando se tiene activado el costeo por cpu. Con este ejemplo sencillo, pero representativo, se puede ver lo importante del ordenamiento de la evaluación para minimizar el uso de recurso de cpu y por ende mejorar el tiempo de respuesta general.
Ahora veamos como fijar el orden de evaluación sin cambiar el comportamiento general de la sesion usando el hint "ordered_predicates":
rop@DESA10G> ed
Escrito file afiedt.buf
1 explain plan for
2 select /*+ ordered_predicates */ count(1) from t
3* where c1 = 1 and n1 = 1111 and n2 = 1
rop@DESA10G> /
Explicado.
rop@DESA10G> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1842905362
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 48 | 5 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 48 | | |
|* 2 | TABLE ACCESS FULL| T | 1 | 48 | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_NUMBER("C1")=1 AND "N1"=1111 AND "N2"=1)
Note
-----
- dynamic sampling used for this statement
18 filas seleccionadas.
Con el hint la evaluación es ordenada, tal cual hubiese sido en 8i por default.
En el plan no se muestró el costo de cpu, pero consultando la tabla de soporte PLAN_TABLE podemos ver que costos fueron asignados de acuerdo al orden de evaluación.
rop@DESA10G> ed
Escrito file afiedt.buf
1 select filter_predicates,cpu_cost from plan_table
2* where filter_predicates is not null
rop@DESA10G> /
FILTER_PREDICATES CPU_COST
-------------------------------------------------- ----------
"N1"=1000 AND "N2"=1 AND TO_NUMBER("C1")=1 1302275
TO_NUMBER("C1")=1 AND "N1"=1111 AND "N2"=1 1802225
rop@DESA10G>
El costo de cpu al evaluar en 10g fue de 1302275 (con reordenamiento) y el costo de cpu sin ordenamiento fue de 1802225 (8i)
Como demostré con el ejemplo, el optimizador evalua los predicados de forma tal de optimizar el uso de cpu. Este cambio podría hacer fallar ciertos codigos que antes funcionaban y que tenian inconsistencias de datos, como por ejemplo, tener valores no numericos sobre campos varchar que deben tener numeros, y entonces al convertir implicitamente se genere un error de invalid number (ORA-01722). Queda claro que si ocurre esto es debido a un mal diseño, ya que una columna que aloja solo números no debería ser de tipo varchar, ya que de esta forma se promueve, por un lado inconsistencias en los datos y por otro lado, se afecta el rendimiento debido a las conversiones implicitas.
jueves, 10 de septiembre de 2009
Como minimizar problemas luego de un upgrade de versión de Oracle (Caso1: Ordenamiento implicito de GROUP BY en versiones anteriores a 10g R2)
Esta nota pretende ser la primera de una serie de notas en las que voy a comentarles problemas que se pueden dar luego de un upgrade de versión de base de datos. Este año estuve involucrado en unos cuantos upgrades de versiones, siempre hacia version 10g R2 pero partiendo desde distintas versiones base (8i, 9i R2 y 10g R1). Aqui en Argentina todavia son muy pocos los que migraron a 11g ya que es una practica habitual, y en mi opinión muy acertada, esperar al segundo release para upgradear. Al momento de esta nota acaba de salir el 11g R2, pero solo para linux, asi que estimo que en poco tiempo tendremos disponible el nuevo release para las plataformas unix "grandes", tales como solaris, aix, hp-ux y tambien para la familia de SO's de windows.
Es sabido que cada nueva versión de Oracle introduce nuevos features, y en especial features que cambian el comportamiento del optimizador y que producen cambios en los paths de los planes de ejecución. Los cambios en el optimizador son generalmente para mejorar el acceso a los datos y por ende reducir el tiempo de respuesta. Esta mejora en la "inteligencia" del optimizador no debería ocacionar cambios de comportamiento, a menos que no se cumplan las Buenas Practicas de confección de sentencias sql. Una mala práctica, y por desgracia bastante común, es confiar en el ordenamiento implicito que se da, por ejemplo, al usar distint/unique o en el ordenamiento que se produce con el group by. Este último es a veces innecesario y agrega un path implicito para ordenar que suma un tiempo mas antes de retornar la respuesta.
A partir de 10g R2 cambió el path SORT GROUP BY por el path HASH GROUP BY mejorando el rendimiento dado que no se infiere la necesidad de retornar el resultado ordenado. Todas las unidades de código que "confiaban" en este ordenamiento implicito y que necesitan por negocio un cierto orden van a comenzar a devolver resultados erroneos al upgradear a 10g R2 o versión superior, recordemos que las "Best Practices" dictan usar siempre ORDER BY cuando debe haber un orden ya que el comportamiento no esta garantizado a futuro.
Ahora, como suelo hacer, les voy a mostrar el ejemplo del group by, en próximas notas le voy a mostrar otros "issues" que pueden causar fuertes dolores de cabeza cuando no se detectan a tiempo.
Voy a usar mi conocida, y nunca bien ponderada, tablita de ejemplo T
El listado salio desordenado, veamos el plan que genera:
El path es HASH GROUP BY en reemplazo de SORT HASH GROUP
Ahora, voy a usar, el truco mas rápido para salir del paso, tipo de solución "quick and dirty", pero solución al fin, que me ha salvado varias veces cuando se pasó por alto algún nuevo mecanismo y se comienzan a ver los problemas en plena hora pico o cuando cancelan procesos baths al dia siguiente del upgrade. Podemos setear a nivel sesion el parámetro "optimizer_features_enable", tambien se puede setear a nivel de hint con opt_param(param,valor), para hacer un flashback al comportamiento de un versión anterior. Como el caso de esta nota se da a partir de 10g R2, como estrategia siempre tomo la decisión de ir al upgrade anterior mas próximo donde funciona como antes, para asi estabilizar el comportamiento y no tener que retornar al release original. Como dije antes, en este caso la solución definitiva sera disparar un requerimiento de cambio de código y que el sector de desarrollo agregue el order by en las sentencias que necesitan del ordenamiento para funcionar correctamente.
Cambié la version del optimizador y el resultado fue el esperado, salió ordenado. Miremos el nuevo plan:
Ahora uso en antiguo path SORT GROUP BY y en consecuencia la salida no altero el orden
Por ultimo, agregamos el order by y vemos como se agrega (ahora explicitamente) un path para ordenar el resultado antes de retornarlo.
Este es un caso interesante de cambio de comportamiento que saca a la luz problemas de mala programación, que en versiones anteriores pasaban desapercibidas. Por tal motivo es muy importante tener código de calidad ,y que no solo funcione, para evitar sorpresas a futuro. En la próxima nota les voy a mostrar otro caso de cambio interesante, relacionado con cambios en el orden de evaluación de predicados.
Es sabido que cada nueva versión de Oracle introduce nuevos features, y en especial features que cambian el comportamiento del optimizador y que producen cambios en los paths de los planes de ejecución. Los cambios en el optimizador son generalmente para mejorar el acceso a los datos y por ende reducir el tiempo de respuesta. Esta mejora en la "inteligencia" del optimizador no debería ocacionar cambios de comportamiento, a menos que no se cumplan las Buenas Practicas de confección de sentencias sql. Una mala práctica, y por desgracia bastante común, es confiar en el ordenamiento implicito que se da, por ejemplo, al usar distint/unique o en el ordenamiento que se produce con el group by. Este último es a veces innecesario y agrega un path implicito para ordenar que suma un tiempo mas antes de retornar la respuesta.
A partir de 10g R2 cambió el path SORT GROUP BY por el path HASH GROUP BY mejorando el rendimiento dado que no se infiere la necesidad de retornar el resultado ordenado. Todas las unidades de código que "confiaban" en este ordenamiento implicito y que necesitan por negocio un cierto orden van a comenzar a devolver resultados erroneos al upgradear a 10g R2 o versión superior, recordemos que las "Best Practices" dictan usar siempre ORDER BY cuando debe haber un orden ya que el comportamiento no esta garantizado a futuro.
Ahora, como suelo hacer, les voy a mostrar el ejemplo del group by, en próximas notas le voy a mostrar otros "issues" que pueden causar fuertes dolores de cabeza cuando no se detectan a tiempo.
Voy a usar mi conocida, y nunca bien ponderada, tablita de ejemplo T
rop@TEST10G> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
rop@TEST10G> create table t as select * from dba_objects;
Tabla creada.
rop@TEST10G> exec dbms_stats.gather_table_Stats(user,'T');
Procedimiento PL/SQL terminado correctamente.
rop@TEST10G> select object_type,count(1)
2 from t
3 group by object_type
OBJECT_TYPE COUNT(1)
------------------- ----------
INDEX 22538
JOB CLASS 2
CONTEXT 5
TABLE SUBPARTITION 18
TYPE BODY 174
INDEXTYPE 10
PROCEDURE 252
RESOURCE PLAN 4
RULE 4
JAVA CLASS 16417
SCHEDULE 1
TABLE PARTITION 625
WINDOW 2
WINDOW GROUP 1
JAVA RESOURCE 770
TABLE 22631
TYPE 1941
VIEW 3804
LIBRARY 150
FUNCTION 329
TRIGGER 565
PROGRAM 12
MATERIALIZED VIEW 3
DATABASE LINK 5
CLUSTER 10
SYNONYM 23307
PACKAGE BODY 807
QUEUE 27
CONSUMER GROUP 6
EVALUATION CONTEXT 14
RULE SET 19
DIRECTORY 16
UNDEFINED 6
OPERATOR 57
JAVA DATA 306
DIMENSION 5
SEQUENCE 2759
LOB 713
PACKAGE 866
JOB 18
INDEX PARTITION 617
LOB PARTITION 1
XML SCHEMA 26
43 filas seleccionadas.
El listado salio desordenado, veamos el plan que genera:
rop@TEST10G> explain plan for
2 select object_type,count(1)
3 from t
4 group by object_type;
Explicado.
rop@TEST10G> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2963600285
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 26 | 208 | 339 (7)| 00:00:05 |
| 1 | HASH GROUP BY | | 26 | 208 | 339 (7)| 00:00:05 |
| 2 | TABLE ACCESS FULL| T | 99843 | 780K| 324 (2)| 00:00:04 |
---------------------------------------------------------------------------
9 filas seleccionadas.
El path es HASH GROUP BY en reemplazo de SORT HASH GROUP
Ahora, voy a usar, el truco mas rápido para salir del paso, tipo de solución "quick and dirty", pero solución al fin, que me ha salvado varias veces cuando se pasó por alto algún nuevo mecanismo y se comienzan a ver los problemas en plena hora pico o cuando cancelan procesos baths al dia siguiente del upgrade. Podemos setear a nivel sesion el parámetro "optimizer_features_enable", tambien se puede setear a nivel de hint con opt_param(param,valor), para hacer un flashback al comportamiento de un versión anterior. Como el caso de esta nota se da a partir de 10g R2, como estrategia siempre tomo la decisión de ir al upgrade anterior mas próximo donde funciona como antes, para asi estabilizar el comportamiento y no tener que retornar al release original. Como dije antes, en este caso la solución definitiva sera disparar un requerimiento de cambio de código y que el sector de desarrollo agregue el order by en las sentencias que necesitan del ordenamiento para funcionar correctamente.
rop@TEST10G> alter session set optimizer_features_enable = '10.1.0.5';
Sesión modificada.
rop@TEST10G> select object_type,count(1)
2 from t
3 group by object_type;
OBJECT_TYPE COUNT(1)
------------------- ----------
CLUSTER 10
CONSUMER GROUP 6
CONTEXT 5
DATABASE LINK 5
DIMENSION 5
DIRECTORY 16
EVALUATION CONTEXT 14
FUNCTION 329
INDEX 22538
INDEX PARTITION 617
INDEXTYPE 10
JAVA CLASS 16417
JAVA DATA 306
JAVA RESOURCE 770
JOB 18
JOB CLASS 2
LIBRARY 150
LOB 713
LOB PARTITION 1
MATERIALIZED VIEW 3
OPERATOR 57
PACKAGE 866
PACKAGE BODY 807
PROCEDURE 252
PROGRAM 12
QUEUE 27
RESOURCE PLAN 4
RULE 4
RULE SET 19
SCHEDULE 1
SEQUENCE 2759
SYNONYM 23307
TABLE 22631
TABLE PARTITION 625
TABLE SUBPARTITION 18
TRIGGER 565
TYPE 1941
TYPE BODY 174
UNDEFINED 6
VIEW 3804
WINDOW 2
WINDOW GROUP 1
XML SCHEMA 26
43 filas seleccionadas.
Cambié la version del optimizador y el resultado fue el esperado, salió ordenado. Miremos el nuevo plan:
rop@TEST10G> explain plan for
2 select object_type,count(1)
3 from t
4 group by object_type;
Explicado.
rop@TEST10G> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3156910365
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 26 | 208 | 337 (6)| 00:00:05 |
| 1 | SORT GROUP BY | | 26 | 208 | 337 (6)| 00:00:05 |
| 2 | TABLE ACCESS FULL| T | 99843 | 780K| 322 (2)| 00:00:04 |
---------------------------------------------------------------------------
9 filas seleccionadas.
Ahora uso en antiguo path SORT GROUP BY y en consecuencia la salida no altero el orden
Por ultimo, agregamos el order by y vemos como se agrega (ahora explicitamente) un path para ordenar el resultado antes de retornarlo.
rop@TEST10G> ed
Escrito file afiedt.buf
1 explain plan for
2 select object_type,count(1)
3 from t
4 group by object_type
5* order by object_type
rop@TEST10G> /
Explicado.
rop@TEST10G> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3861070257
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 26 | 208 | 354 (11)| 00:00:05 |
| 1 | SORT ORDER BY | | 26 | 208 | 354 (11)| 00:00:05 |
| 2 | HASH GROUP BY | | 26 | 208 | 354 (11)| 00:00:05 |
| 3 | TABLE ACCESS FULL| T | 99843 | 780K| 324 (2)| 00:00:04 |
----------------------------------------------------------------------------
Este es un caso interesante de cambio de comportamiento que saca a la luz problemas de mala programación, que en versiones anteriores pasaban desapercibidas. Por tal motivo es muy importante tener código de calidad ,y que no solo funcione, para evitar sorpresas a futuro. En la próxima nota les voy a mostrar otro caso de cambio interesante, relacionado con cambios en el orden de evaluación de predicados.
Suscribirse a:
Entradas (Atom)
.JPG)