* 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.
.JPG)
No hay comentarios:
Publicar un comentario