Demora en la respuesta de una consulta SQL
Tengo un problema con una consulta SQL. En un tablespace se encuentran las tablas de nuestro sistema diferencidas por compañías de cliente. Hay 3 tablas que tienen la misma estructura pero con diferentes cantidades de datos y con distinto nombre. Cuando ejecuto una consulta SQL a dichas tablas 2 de ella me responde bien, pero hay una tercera que no termina nunca. Increíblemente es la que menos datos tiene.
¿Qué puedo hacer para lograr obtener un resultado?
¿Qué puedo hacer para lograr obtener un resultado?
1 Respuesta
Respuesta de cbalba
1
Las tablas puede que tengan la misma estructura pero internamente no se organizan igual. ¿Es posible que las tablas cuya consulta es satisfactoria tengan un índice creado, y la que no no lo tenga creado?
Compruébelo y si no lo tiene, le aconsejo la creación de un índice que contenga los campos que utiliza en la where de la consulta. Es muy probable que ésta mejore.
Si no es así, apórteme más datos sobre la consulta y sobre la descripción de las tablas y lo revisaremos con más detenimiento.
Compruébelo y si no lo tiene, le aconsejo la creación de un índice que contenga los campos que utiliza en la where de la consulta. Es muy probable que ésta mejore.
Si no es así, apórteme más datos sobre la consulta y sobre la descripción de las tablas y lo revisaremos con más detenimiento.
Los HINT le indican a la base de datos algunos "consejos" para ejecutar la sentencia concreta. De tal forma que si no se especifican, la base de datos evalúa la sentencia y decide qué método utilizar para acceder a los datos (FULL SCAN, por índice, etc.) Si se llegan a especificar puede indicarle a la b.d. qué índice utilizar para acceder a los datos y esto algunas veces resulta peligroso pues puede que no le estemos indicando el correcto.
Ya que menciona que está vd. utilizando el TOAD, le sugiero que ejecute la sentencia en el SQL Editor y pinche en la solapa "Explain Plan" para ver el plan de ejecución de su sentencia. Quizá podamos sacar algo más en claro.
Saludos,
CBAlba
No tengo mucho tiempo para extenderme en
Ya que menciona que está vd. utilizando el TOAD, le sugiero que ejecute la sentencia en el SQL Editor y pinche en la solapa "Explain Plan" para ver el plan de ejecución de su sentencia. Quizá podamos sacar algo más en claro.
Saludos,
CBAlba
No tengo mucho tiempo para extenderme en
Este es el resultado de la consulta "Explain Plan" de la query con las modificaciones señaladas. La segunda el con el HINT agregado según indica el SQlLab. Lamento el tiempo que le estoy quitando.
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE 2 1039
SORT GROUP BY 2 180 1039
HASH JOIN 2 180 1037
TABLE ACCESS FULL TTDILC301620 1 K 22 K 855
HASH JOIN 986 64 K 166
INDEX FAST FULL SCAN TTDILC101620ITEM 1 K 13 K 2
TABLE ACCESS FULL TTIITM001620 986 54 K 151
---------------------------------------
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE 1 1047
SORT GROUP BY 1 90 1047
HASH JOIN 1 90 1045
HASH JOIN 1 K 32 K 874
INDEX FAST FULL SCAN TTDILC101620ITEM 1 K 13 K 10
TABLE ACCESS FULL TTDILC301620 1 K 22 K 855
TABLE ACCESS FULL TTIITM001620 986 54 K 151
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE 2 1039
SORT GROUP BY 2 180 1039
HASH JOIN 2 180 1037
TABLE ACCESS FULL TTDILC301620 1 K 22 K 855
HASH JOIN 986 64 K 166
INDEX FAST FULL SCAN TTDILC101620ITEM 1 K 13 K 2
TABLE ACCESS FULL TTIITM001620 986 54 K 151
---------------------------------------
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE 1 1047
SORT GROUP BY 1 90 1047
HASH JOIN 1 90 1045
HASH JOIN 1 K 32 K 874
INDEX FAST FULL SCAN TTDILC101620ITEM 1 K 13 K 10
TABLE ACCESS FULL TTDILC301620 1 K 22 K 855
TABLE ACCESS FULL TTIITM001620 986 54 K 151
Gracias Experto por tu respuesta.
Las tablas son creadas por un sistema RP llamado MK Logistic, el cual crea las tablas con sus indices automáticamente. Estás tablas tienen en su estructura campos hash de las claves principales de la tabla. Los Indices están basados en dichos campos. Ante esto, estoy seguro que las tablas son idénticas en su estructura y configuración, solo cambia el nombre y los datos.
Estoy averiguando si la creación adicionales de indices provoca algún problema en el sistema, pero está tardando un poco la respuesta.
Te entrego la consulta que estoy ejecutando para que puedas analizarla y nuevamente te agradezco por tu tiempo. Muchas Gracias.
SELECT /*+ ORDERED USE_NL (il) */
il.t$item
, ma.t$dsca
, ma.t$seak
, ma.t$stoc
, ABS(SUM(ds.t$qstk)) AS despachado
FROM ttdilc301615 ds
, ttdilc101615 il
, ttiitm001615 ma
WHERE ds.t$koor = 3
AND ds.t$trdt
BETWEEN TO_DATE('100105', 'dd-mm-yy') AND TO_DATE('180205', 'dd-mm-yy')
AND ma.t$citg = 'BEM'
AND ds.t$item = ma.t$item
AND il.t$item = ds.t$item
AND il.t$item = ma.t$item
GROUP BY il.t$item, ma.t$dsca, ma.t$seak, ma.t$stoc
Las tablas son creadas por un sistema RP llamado MK Logistic, el cual crea las tablas con sus indices automáticamente. Estás tablas tienen en su estructura campos hash de las claves principales de la tabla. Los Indices están basados en dichos campos. Ante esto, estoy seguro que las tablas son idénticas en su estructura y configuración, solo cambia el nombre y los datos.
Estoy averiguando si la creación adicionales de indices provoca algún problema en el sistema, pero está tardando un poco la respuesta.
Te entrego la consulta que estoy ejecutando para que puedas analizarla y nuevamente te agradezco por tu tiempo. Muchas Gracias.
SELECT /*+ ORDERED USE_NL (il) */
il.t$item
, ma.t$dsca
, ma.t$seak
, ma.t$stoc
, ABS(SUM(ds.t$qstk)) AS despachado
FROM ttdilc301615 ds
, ttdilc101615 il
, ttiitm001615 ma
WHERE ds.t$koor = 3
AND ds.t$trdt
BETWEEN TO_DATE('100105', 'dd-mm-yy') AND TO_DATE('180205', 'dd-mm-yy')
AND ma.t$citg = 'BEM'
AND ds.t$item = ma.t$item
AND il.t$item = ds.t$item
AND il.t$item = ma.t$item
GROUP BY il.t$item, ma.t$dsca, ma.t$seak, ma.t$stoc
Hola Experto.
He hecho los cambios en la consulta y ha disminuido el tiempo de respuesta de las consultas. O sea, son más rápida. Pero sigo teniendo el mismo problema con la compañía que tiene las tdilc301620 ds
ttdilc101620 il
ttiitm001620 ma
La demora es bastante excesiva, de hecho ya lleva más de 5 minutos.
Tengo algunas preguntas:
¿Qué función cunmplen los HINT?, está opción la agrego automáticamente el programa TOAD con el cual manejo la base de datos.
¿En qué forma impacta en la consultas que la setencías JOIN estén al principio de la clausula Where y no al final?
Muchas Gracias por todo.
He hecho los cambios en la consulta y ha disminuido el tiempo de respuesta de las consultas. O sea, son más rápida. Pero sigo teniendo el mismo problema con la compañía que tiene las tdilc301620 ds
ttdilc101620 il
ttiitm001620 ma
La demora es bastante excesiva, de hecho ya lleva más de 5 minutos.
Tengo algunas preguntas:
¿Qué función cunmplen los HINT?, está opción la agrego automáticamente el programa TOAD con el cual manejo la base de datos.
¿En qué forma impacta en la consultas que la setencías JOIN estén al principio de la clausula Where y no al final?
Muchas Gracias por todo.
De entrada le digo que la creación de un índice si le afecta al funcionamiento de sus aplicaciones. Por defecto Oracle decidirá si lo utiliza o no a la hora de buscar los datos y por otra parte cuando inserte datos tiene que insertar el campo en el índice correspondiente. El impacto pues es mínimo.
Una vez revisada su consulta se me ocurren un par de ideas:
1ª opción: Elimine el HINT que le indica a la base de datos cómo acceder a los datos (borre desde /* hasta */
2ª opcion: reestructure la cláusula where y ponga:
1º los criterios de join de las tablas.
2º los criterios más restrictivos ; p.e. ma.t$citg = 'BEM'
3º los criterios menos restrictivos; p.e. BETWEEN ...
Una vez revisada su consulta se me ocurren un par de ideas:
1ª opción: Elimine el HINT que le indica a la base de datos cómo acceder a los datos (borre desde /* hasta */
2ª opcion: reestructure la cláusula where y ponga:
1º los criterios de join de las tablas.
2º los criterios más restrictivos ; p.e. ma.t$citg = 'BEM'
3º los criterios menos restrictivos; p.e. BETWEEN ...
Hola Experto:
1)Revise los Join y no falta ninguno. Es decir, para tres tablas hay tres Join que las unen entre si por el campo item.
2)Cómo te comente anteriormente, todas las tablas tienen sus indices asociados en especial por el campo item. Pero no se como forzar que Oracle ocupe el indice determinado. Más aun cuando los indices están creados por campos hash que son las combinación de los campos claves (en distinto orden), en código hexadecimal.
3)Cambie el orden de los FROM colocando la tabla ttdilc301 al final por se la que contiene la mayor cantidad de registros y modifique la clausula Where según se indico pero no consigo que la consulta demorea menos de 5 minutos. Con el SqlLab he estudiado la forma de lograr que está tabla no haga FULL SCAN pero no he logrado nada. Pienso que la solución es disminuir el acceso a dicha tabla...¿Tiene alguna sugerencia al respecto?
Otra vez. Mil, pero mil perdones y agradecimientos por todo este tiempo.
1)Revise los Join y no falta ninguno. Es decir, para tres tablas hay tres Join que las unen entre si por el campo item.
2)Cómo te comente anteriormente, todas las tablas tienen sus indices asociados en especial por el campo item. Pero no se como forzar que Oracle ocupe el indice determinado. Más aun cuando los indices están creados por campos hash que son las combinación de los campos claves (en distinto orden), en código hexadecimal.
3)Cambie el orden de los FROM colocando la tabla ttdilc301 al final por se la que contiene la mayor cantidad de registros y modifique la clausula Where según se indico pero no consigo que la consulta demorea menos de 5 minutos. Con el SqlLab he estudiado la forma de lograr que está tabla no haga FULL SCAN pero no he logrado nada. Pienso que la solución es disminuir el acceso a dicha tabla...¿Tiene alguna sugerencia al respecto?
Otra vez. Mil, pero mil perdones y agradecimientos por todo este tiempo.
Primero disculparme por mi mensaje entrecortado de ayer. Tuve que salir urgentemente.
REspecto a su pregunta:
"¿En que forma impacta en la consultas que la setencías JOIN estén al principio de la clausula Where y no al final?"
Le diré que impactan y mucho porque la base de datos va seleccionando los datos en el mismo orden en que se especifican en la cláusula WHERE. De esta forma si en una cláusula se especifica que:
...
WHERE ANIO ='2005' and id_empresa='123456'
Lo que hace es seleccionar primero todos los registros que cumplan que ANIO='2005', que pueden ser millones de registros, y después toma esos millones de registros y selecciona sólo los que cumplan id_empresa='123456'.
Sin embargo si hubiéramos especificado:
...WHERE id_empresa='123456'
and ANIO ='2005'
Primero recogería los correspondientes a la empresa especificada (¿unos miles de registros?) Y con el resultado de ese filtro seleccionaría sólo los del ANIO='2005'. El resultado final, que es el que se le da al usuario es el mismo, pero la forma de obtenerlo no.
Respecto a los resultados del "Explain Plan" habrá visto vd que en las tablas TTIITM001620 y TTDILC301620 está realizando un acceso completo de la tabla.
Preguntas:
1º (Esta es casi insultante, pero por si acaso) ¿Está vd. seguro que está realizanto la JOIN correctamente, es decir, que ´no se le ha olvidado establecer una condición de igualdad entre dos de las tablas especificadas? Si no fuera así, le estaría intentando mostrar el producto cartesiano de las dos tablas, y créame, ¿si estaría justificado que tardara tanto?
2º ¿Podría comprobar si las tablas que hacen FULL SCAN tienen algún índice que incluya en su lista de campos el campo por el que está realizando la join? Si no es así, es probable que necesite crear un índice por ese campo en concreto (creo que era item)
3º (Esta es una sugerencia) Cambie el orden en el que establece las tablas en la select. Ponga primero la tabla que menos registros tenga y después la que más registros tenga. Tanto en el FROM como en las condiciones de la WHERE. A veces los resultados de estos cambios sorprenden.
Un placer,
CBAlba.
REspecto a su pregunta:
"¿En que forma impacta en la consultas que la setencías JOIN estén al principio de la clausula Where y no al final?"
Le diré que impactan y mucho porque la base de datos va seleccionando los datos en el mismo orden en que se especifican en la cláusula WHERE. De esta forma si en una cláusula se especifica que:
...
WHERE ANIO ='2005' and id_empresa='123456'
Lo que hace es seleccionar primero todos los registros que cumplan que ANIO='2005', que pueden ser millones de registros, y después toma esos millones de registros y selecciona sólo los que cumplan id_empresa='123456'.
Sin embargo si hubiéramos especificado:
...WHERE id_empresa='123456'
and ANIO ='2005'
Primero recogería los correspondientes a la empresa especificada (¿unos miles de registros?) Y con el resultado de ese filtro seleccionaría sólo los del ANIO='2005'. El resultado final, que es el que se le da al usuario es el mismo, pero la forma de obtenerlo no.
Respecto a los resultados del "Explain Plan" habrá visto vd que en las tablas TTIITM001620 y TTDILC301620 está realizando un acceso completo de la tabla.
Preguntas:
1º (Esta es casi insultante, pero por si acaso) ¿Está vd. seguro que está realizanto la JOIN correctamente, es decir, que ´no se le ha olvidado establecer una condición de igualdad entre dos de las tablas especificadas? Si no fuera así, le estaría intentando mostrar el producto cartesiano de las dos tablas, y créame, ¿si estaría justificado que tardara tanto?
2º ¿Podría comprobar si las tablas que hacen FULL SCAN tienen algún índice que incluya en su lista de campos el campo por el que está realizando la join? Si no es así, es probable que necesite crear un índice por ese campo en concreto (creo que era item)
3º (Esta es una sugerencia) Cambie el orden en el que establece las tablas en la select. Ponga primero la tabla que menos registros tenga y después la que más registros tenga. Tanto en el FROM como en las condiciones de la WHERE. A veces los resultados de estos cambios sorprenden.
Un placer,
CBAlba.
Hola otra vez:
Me falto añadir lo siguiente: realize un DUMP de la tabla con sus indices y lo cargue en mi equipo que cuenta con una base oracle. El tiempo de respuesta fue de menos 10 segundos, con la misma cantidad de datos.
¿Por qué será la diferencia?
Mi base es mucho más pequeña en todos los aspectos en comparación con la base desl servidor, incluyendo el tamaño de los Log, TableSpace, etc.
Me falto añadir lo siguiente: realize un DUMP de la tabla con sus indices y lo cargue en mi equipo que cuenta con una base oracle. El tiempo de respuesta fue de menos 10 segundos, con la misma cantidad de datos.
¿Por qué será la diferencia?
Mi base es mucho más pequeña en todos los aspectos en comparación con la base desl servidor, incluyendo el tamaño de los Log, TableSpace, etc.
Como ya he comentado Oracle dispone de un sistema que decide qué forma utiliza para seleccionar los datos, si hace un full scan, si los busca por índices, etc.
El caso es que cuando tenemos un índice, el optimizador se basa en una serie de estadísticas de las tablas y sus índices para decidir si los utiliza o no. Es el administrador del sistema el responsable de actualizar estas estadísticas periódicamente con el comando "ANALIZE". Otra forma de actualizarlo sería volviendo a crear la tabla y/O sus índices. Es posible que eso sea lo que le esté ocurriendo, que al crear de nuevo todos los datos en su equipo. TOAD dispone de opciones para recrear los índices. Sería buena idea por lo menos "Analizarlos" tanto a los índices como a las tablas.
Por otra parte, el servidor da servicio a muchos clientes mientras que su equipo ejecuta la consulta casi en plena disposición.
El caso es que cuando tenemos un índice, el optimizador se basa en una serie de estadísticas de las tablas y sus índices para decidir si los utiliza o no. Es el administrador del sistema el responsable de actualizar estas estadísticas periódicamente con el comando "ANALIZE". Otra forma de actualizarlo sería volviendo a crear la tabla y/O sus índices. Es posible que eso sea lo que le esté ocurriendo, que al crear de nuevo todos los datos en su equipo. TOAD dispone de opciones para recrear los índices. Sería buena idea por lo menos "Analizarlos" tanto a los índices como a las tablas.
Por otra parte, el servidor da servicio a muchos clientes mientras que su equipo ejecuta la consulta casi en plena disposición.
Muchas Gracias Experto. Veré la forma de analizar las tablas comprometidas. Gracias por todas las respuesta y por el tiempo dado. Fue Super Bueno, Excelente.
