resueltos - plan de ejecución y optimización de consultas sql server 2008 r2

Optimizando el rendimiento de la consulta SELECT

Question

Tengo una instrucción SELECT que se ejecuta realmente lenta, está frenando nuestro proceso nocturno.

La consulta es: (No comente sobre la sintaxis de la unión implícita, esto es generado automáticamente por Informatica que ejecuta este código):

SELECT *
FROM STG_DIM_CRM_CASES,V_CRM_CASE_ID_EXISTS_IN_DWH,stg_scd_customers_key
WHERE STG_DIM_CRM_CASES.CRM_CASE_ID = V_CRM_CASE_ID_EXISTS_IN_DWH.CASE_ID(+)
AND STG_DIM_CRM_CASES.account_number = stg_scd_customers_key.account_number(+)
and STG_DIM_CRM_CASES.Case_Create_Date between stg_scd_customers_key.start_date(+) and stg_scd_customers_key.end_date(+)

editar : La consulta real selecciona solo account_number,start_date,end_date y otra columna que no está indexada.

Tablas de información:

STG_DIM_CRM_CASES

Index - (Account_Number,Case_Create_Date)
size - 270k records.

stg_scd_customers_key

Index - Account_Number,Start_Date,End_Date
Partitioned - End_Date
Size - 500 million records.

V_CRM_CASE_ID_EXISTS_IN_DWH (Ver) -

select t.case_id
from crm_ps_rc_case t, dim_crm_cases x
where t.case_id=x.crm_case_id;

dim_crm_cases -

Indexed - (crm_case_id)
Size - 100 million .

crm_ps_rc_case

Size - 270k records

Editar : si no estaba claro, la vista devuelve 270k registros.

La consulta sin la unión a stg_scd está tomando segundos, parece que es la parte que causa los problemas de rendimiento, la vista también se ejecuta en segundos, aunque se está uniendo a una tabla de 100 millones de registros. En este momento, la consulta está tomando entre 12 y 30 minutos, dependiendo de cuán ocupadas estén nuestras fuentes.

Aquí está el plan de ejecución:

6 | 0 | SELECT STATEMENT | | 3278K| 1297M| 559K (4)| 02:10:37 | | | | | |
7 | 1 | PX COORDINATOR | | | | | | | | | | |
8 | 2 | PX SEND QC (RANDOM) | :TQ10003 | 3278K| 1297M| 559K (4)| 02:10:37 | | | Q1,03 | P->S | QC (RAND) |
9 |* 3 | HASH JOIN OUTER | | 3278K| 1297M| 559K (4)| 02:10:37 | | | Q1,03 | PCWP | |
10 | 4 | PX RECEIVE | | 29188 | 10M| 50662 (5)| 00:11:50 | | | Q1,03 | PCWP | |
11 | 5 | PX SEND HASH | :TQ10002 | 29188 | 10M| 50662 (5)| 00:11:50 | | | Q1,02 | P->P | HASH |
12 |* 6 | HASH JOIN RIGHT OUTER | | 29188 | 10M| 50662 (5)| 00:11:50 | | | Q1,02 | PCWP | |
13 | 7 | BUFFER SORT | | | | | | | | Q1,02 | PCWC | |
14 | 8 | PX RECEIVE | | 29188 | 370K| 50575 (5)| 00:11:49 | | | Q1,02 | PCWP | |
15 | 9 | PX SEND BROADCAST | :TQ10000 | 29188 | 370K| 50575 (5)| 00:11:49 | | | | S->P | BROADCAST |
16 | 10 | VIEW | V_CRM_CASE_ID_EXISTS_IN_DWH | 29188 | 370K| 50575 (5)| 00:11:49 | | | | | |
17 |* 11 | HASH JOIN | | 29188 | 399K| 50575 (5)| 00:11:49 | | | | | |
18 | 12 | TABLE ACCESS FULL | CRM_PS_RC_CASE | 29188 | 199K| 570 (1)| 00:00:08 | | | | | |
19 | 13 | INDEX FAST FULL SCAN| DIM_CRM_CASES$1PK | 103M| 692M| 48894 (3)| 00:11:25 | | | | | |
20 | 14 | PX BLOCK ITERATOR | | 29188 | 10M| 87 (2)| 00:00:02 | | | Q1,02 | PCWC | |
21 | 15 | TABLE ACCESS FULL | STG_DIM_CRM_CASES | 29188 | 10M| 87 (2)| 00:00:02 | | | Q1,02 | PCWP | |
22 | 16 | BUFFER SORT | | | | | | | | Q1,03 | PCWC | |
23 | 17 | PX RECEIVE | | 515M| 14G| 507K (3)| 01:58:28 | | | Q1,03 | PCWP | |
24 | 18 | PX SEND HASH | :TQ10001 | 515M| 14G| 507K (3)| 01:58:28 | | | | S->P | HASH |
25 | 19 | PARTITION RANGE ALL | | 515M| 14G| 507K (3)| 01:58:28 | 1 | 2982 | | | |
26 | 20 | TABLE ACCESS FULL | STG_SCD_CUSTOMERS_KEY | 515M| 14G| 507K (3)| 01:58:28 | 1 | 2982 | | | |
27 ------------------------------------------------------------------------------------------------------------------------------------------------------------
28
29 Predicate Information (identified by operation id):
30 ---------------------------------------------------
31
32 3 - access("STG_DIM_CRM_CASES"."ACCOUNT_NUMBER"="STG_SCD_CUSTOMERS_KEY"."ACCOUNT_NUMBER"(+))
33 filter("STG_DIM_CRM_CASES"."CASE_CREATE_DATE">="STG_SCD_CUSTOMERS_KEY"."START_DATE"(+) AND
34 "STG_DIM_CRM_CASES"."CASE_CREATE_DATE"<="STG_SCD_CUSTOMERS_KEY"."END_DATE"(+))
35 6 - access("STG_DIM_CRM_CASES"."CRM_CASE_ID"="V_CRM_CASE_ID_EXISTS_IN_DWH"."CASE_ID"(+))
36 11 - access("T"."CASE_ID"="X"."CRM_CASE_ID")

Notas: Agregar índices puede ser un problema, depende del índice. Este no es el único lugar donde se utilizan estas tablas, por lo que los índices pueden interferir con otros comandos (Inserta principalmente) en estas tablas.

También he intentado agregar un filtro en stg_scd y excluir todas las fechas más pequeñas que la fecha mínima en Table_Cases , pero eso no ayudó porque solo filtró 1 año de registros.

Gracias por adelantado.

Answer 1

Consideraría crear una vista materializada (con refresh fast on demand ) para la unión entre Table_cases y stg_scd . Asumo que gran parte del trabajo en la unión está en filas que no cambian día a día.

Answer 2

Creo que el problema es la vista, que sospecho que se está ejecutando y devolviendo completamente todas las filas antes de que se apliquen las condiciones.

El efecto general de la vista es agregar la columna CASE_ID que no es nula si se encuentra CRM_CASE_ID , de lo contrario, es nula. He reemplazado la vista con dos uniones directas y una expresión CASE. Al reemplazar la conveniencia de la vista con lógica, puede unirse directamente a cada tabla en ella y así evitar un nivel de profundidad de unión.

Intenta ejecutar esta versión de la consulta:

SELECT
a.*, b.*, c.*,
CASE WHEN t.case_id is not null and X.case_id is not null then t.case_id END CASE_ID
FROM STG_DIM_CRM_CASES a
LEFT JOIN crm_ps_rc_case t
ON t.case_id = a.CRM_CASE_ID
LEFT JOIN dim_crm_cases x
ON x.crm_case_id = a.CRM_CASE_ID
LEFT JOIN V_CRM_CASE_ID_EXISTS_IN_DWH b
ON a.CRM_CASE_ID = b.CASE_ID
LEFT JOIN stg_scd_customers_key c
ON a.account_number = c.account_number
and a.Case_Create_Date between c.start_date and stg_scd_customers_key.end_date

Si reemplaza a.*, b.*, c.* Con solo las columnas exactas que realmente necesita, obtendrá una aceleración porque simplemente hay menos datos para devolver. Si también coloca índices en las claves buscadas más todas las columnas que realmente selecciona (un índice de cobertura ), lo acelerará considerablemente, ya que se puede usar el acceso de solo índice.

Debe verificar que haya índices en todas las columnas unidas como mínimo.

Answer 3

El problema está en escanear todas las particiones:

18 | PX ENVIAR HASH | : TQ10001 | 515M | 14G | 507K (3) | 01:58:28 | | | | S-> P | HASH | 25 | 19 | RANGO DE PARTICIÓN TODOS |
| 515M | 14G | 507K (3) | 01:58:28 | 1 | 2982 | |
| | 26 | 20 | TABLA ACCESO COMPLETO | STG_SCD_CUSTOMERS_KEY | 515M | 14G |

Sucede porque estás usando la combinación izquierda a esta tabla. ¿Puedes seleccionar 1 partición usando la variable de enlace? ¿Qué es la clave de partición? No veo una pista para el paralelo, pero de acuerdo con su plan, se usa en paralelo. ¿Hay grado paralelo en algún nivel de objeto? ¿Puedes eliminar el plan paralelo y post explicado sin paralelo por favor?

Answer 4

Su problema es que Oracle realmente solo tiene dos formas de obtener las filas que necesita de stg_scd_customers_key . O bien (A) hace un solo FULL SCAN de esa tabla y luego filtra las filas que no quiere o de lo contrario (B) realiza 270,000 búsquedas de índices, de 3 a quizás 5 E / S lógicas cada una (según la altura) de su índice), más otra 1 E / S lógica para leer realmente el bloque de la tabla.

Dada la lectura de varios bloques y otras optimizaciones disponibles con un FULL SCAN , y en base a las estadísticas de su tabla, el optimizador de Oracle supone que la FULL SCAN sería más rápida. Y hay una buena probabilidad de que sea correcto.

Lo que necesitas hacer es darle a Oracle una mejor opción.

Si no puede usar las vistas materializadas donde se encuentra, una vista materializada de un "hombre pobre" es algo que se llama índice de cobertura. Ahora, eso no es razonable para su consulta, ya que hace un SELECT * . ¿Pero realmente necesitas cada columna de stg_scd_customers_key ?

Si puede reducir la lista de columnas que obtiene de stg_scd_customers_key , puede crear un índice que (A) comience con account_number , account_number y end_date y (B) incluya todas las demás columnas que debe seleccionar.

Por ejemplo:

SELECT stg_im_crm_cases.*, V_CRM_CASE_ID_EXISTS_IN_DWH.*, stg_scd_customers_key.column_1, stg_scd_customers_key.column_2
FROM STG_DIM_CRM_CASES,V_CRM_CASE_ID_EXISTS_IN_DWH,stg_scd_customers_key
WHERE STG_DIM_CRM_CASES.CRM_CASE_ID = V_CRM_CASE_ID_EXISTS_IN_DWH.CASE_ID(+)
AND STG_DIM_CRM_CASES.account_number = stg_scd_customers_key.account_number(+)
and STG_DIM_CRM_CASES.Case_Create_Date between stg_scd_customers_key.start_date(+) and stg_scd_customers_key.end_date(+)

Si pudiera realizar esa consulta y crear un índice en stg_scd_customers_key (account_number, start_date, end_date, column_1, column_2), entonces le habrá dado a Oracle una mejor alternativa. Ahora puede leer el índice solo, en lugar de la tabla.

Con tablas tan grandes, no hay garantías hasta que lo pruebes. Pero los índices de cobertura son a menudo justo lo que recetó el doctor. (Se aplican todas las advertencias habituales sobre nuevos índices, por supuesto).

Answer 5

algunas consideraciones:

1) INDICE
crm_ps_rc_case no tiene índice en case_id esto es un problema, se está uniendo a 270k <-> 100m con HASH JOIN (no es bueno)

2) COLUMNAS SELECCIONADAS
la vista V_CRM_CASE_ID_EXISTS_IN_DWH selecciona t.case_id pero debería seleccionar x.crm_case_id , al menos, hasta que no resuelva la indexación de t.case_id . Esto extenderá HASH JOIN a todo su plan de ejecución ... (no es bueno)

3) ENTRE
la combinación / filtrado de rango siempre es un problema, especialmente en tablas grandes, pero podría restringir el problema agregando condiciones en el rango. Déjame explicarte, intenta agregar estas condiciones a tu cláusula WHERE :

AND stg_scd_customers_key.end_date = (
SELECT min(r.end_date)
FROM stg_scd_customers_key r
WHERE r.end_date >= STG_DIM_CRM_CASES.Case_Create_Date
)
AND stg_scd_customers_key.start_date = (
SELECT max(r.start_date)
FROM stg_scd_customers_key r
WHERE r.start_date <= STG_DIM_CRM_CASES.Case_Create_Date
)

sí, se calcularán las subconsultas de 270 k * 2, pero la unión final funcionará en muchas menos operaciones que limiten las operaciones de E / S (debería ser mejor)

4) ORDEN DE COLUMNA INDEX
hay informes contradictorios si lo hace, o si no importa, pero en mi experiencia ... lo hace. Podría ser solo una pequeña mejora, pero puede intentar modificar el índice en stg_scd_customers_key invirtiendo el orden de Start_Date y End_Date . En mi experiencia, me parece más eficiente que el filtro de rango tenga el límite superior antes del límite inferior en el índice.

Answer 6

TEORÍA: Lo que creo que está sucediendo es que el motor tiene que resolver los registros de más de 100 m desde la combinación de vista a los registros de 500 m ANTES de que aplique criterios limitantes (por lo tanto, crea una combinación cruzada e incluso si puede usar índices, que es una gran cantidad de registros que generar) Así que, aunque lo escribiste como una combinación externa, el motor no puede procesarlo de esa manera (no sé por qué)

Por lo tanto, a un mínimo de 100 m * 500 m = 50,000 m, es una gran cantidad de datos para generar y luego analizar / limitar.

Al eliminar la vista, el motor puede ser más capaz de optimizar y usar los índices, eliminando así la necesidad de una combinación de 50,000 m.

Áreas en las que enfocaría mi tiempo en la resolución de problemas:

• Elimine la vista solo para eliminarla como un posible problema de sobrecarga.
• No se reconoce ningún vínculo entre stg_scd_customers_key y V_CRM_CASE_ID_EXISTS_IN_DWH. Esto significa que el motor puede estar realizando una combinación cruzada ANTES de que se hayan resuelto los resultados de STG_DIM_CRM_CASES a stg_scd_customers_key.

CONSIDERE eliminar la vista, o usar una vista en línea

Eliminando la vista:

SELECT *
FROM STG_DIM_CRM_CASES
,crm_ps_rc_case t
,dim_crm_cases x
,stg_scd_customers_key
WHERE t.case_id=x.crm_case_id
AND STG_DIM_CRM_CASES.CRM_CASE_ID = t.CASE_ID(+)
AND STG_DIM_CRM_CASES.account_number = stg_scd_customers_key.account_number(+)
AND STG_DIM_CRM_CASES.Case_Create_Date
between stg_scd_customers_key.start_date(+) and stg_scd_customers_key.end_date(+)

utilizando una vista en línea:

SELECT *
FROM STG_DIM_CRM_CASES
(select t.case_id
from crm_ps_rc_case t, dim_crm_cases x
where t.case_id=x.crm_case_id) V_CRM_CASE_ID_EXISTS_IN_DWH
,stg_scd_customers_key
WHERE STG_DIM_CRM_CASES.CRM_CASE_ID = V_CRM_CASE_ID_EXISTS_IN_DWH.CASE_ID(+)
AND STG_DIM_CRM_CASES.account_number = stg_scd_customers_key.account_number(+)
AND STG_DIM_CRM_CASES.Case_Create_Date
between stg_scd_customers_key.start_date(+) and stg_scd_customers_key.end_date(+)

En cuanto a por qué: - http://www.dba-oracle.com/art_hints_views.htm

Mientras que el orden de la cláusula Where no se debe considerar: en la búsqueda fuera de serie, el motor se está ejecutando en el orden indicado, limitar los 500m hacia abajo y luego agregar los datos complementarios desde la vista sería lógicamente más rápido.

SELECT *
FROM STG_DIM_CRM_CASES,stg_scd_customers_key,V_CRM_CASE_ID_EXISTS_IN_DWH
WHERE STG_DIM_CRM_CASES.account_number = stg_scd_customers_key.account_number(+)
and STG_DIM_CRM_CASES.Case_Create_Date between stg_scd_customers_key.start_date(+) and stg_scd_customers_key.end_date(+)
and STG_DIM_CRM_CASES.CRM_CASE_ID = V_CRM_CASE_ID_EXISTS_IN_DWH.CASE_ID(+)