SQL Tunning - Same SELECT diferent DATE parameters

[Serafín Cerezales]

Hi:

We have a SELECT sentence than takes a few minutes for a 2 years of data query (2) & it takes several hours for 1 year (1).

We realized that Oracle's Optimizer based on COSTS choose HASH JOIN getting the best results (2) and a MERGE JOIN on the worst case (1) & the cost on the HASH JOIN is higher than MERGE JOIN.

We've tried to force HASH JOIN but we can't get the same execution plan; another HINTS as /*+ INDEX ...*/ /*+ RULE */ have been checked without improvement.

(1)This SQL takes several hours:

SELECT DISTINCT RD_BUQUE.ID_BUQUE, RD_BUQUE.NOMBRE, RD_BUQUE.LLAMADA,
RD_TIPO_BUQUES.TIPO, TO_CHAR(rd_trayectoria.f_trayectoria_ini, 'dd/mm/yyyy
hh24:mi:ss') Fecha_INI,
TO_CHAR(rd_trayectoria.f_trayectoria_fin, 'dd/mm/yyyy hh24:mi:ss')
Fecha_FIN, rd_trayectoria.id_trayectoria,
RD_CARGA_BUQUE.TN_IMO_7, RD_CARGA_BUQUE.TN_IMO_TOTAL,
RD_CARGA_BUQUE.TN_TOTAL,RD_REPORTES.DIRECCION ,rd_callsign_pais.nombre_pais
as pais
,RD_TIPO_INCIDENCIAS.descripcion,RD_TIPO_EVENTO.DES_TIPO_EVENTO
FROM rd_buque, rd_callsign_pais, rd_carga_buque, rd_evento_track,
rd_incidencias, rd_reportes, rd_tipo_buques, rd_tipo_evento,
rd_trayectoria, rd_tipo_incidencias
WHERE rd_reportes.id_buque = rd_buque.id_buque
AND rd_tipo_buques.id_tipo_buque = rd_buque.id_tipo_buque
AND rd_reportes.id_reporte = rd_trayectoria.id_reporte
AND rd_reportes.id_reporte = rd_carga_buque.id_reporte (+)
AND rd_callsign_pais.llamada_ini (+) <= rd_buque.llamada
AND rd_callsign_pais.llamada_fin (+) >= rd_buque.llamada
AND rd_trayectoria.id_trayectoria = rd_incidencias.id_trayectoria (+)
AND rd_tipo_incidencias.id_tipo_incidencia (+) =
rd_incidencias.tipoincidencia
AND rd_evento_track.id_trayectoria (+) = rd_trayectoria.id_trayectoria
AND rd_evento_track.id_tipo_evento = rd_tipo_evento.id_tipo_evento (+)
AND rd_reportes.f_reporte BETWEEN to_date('02/12/2002 00.00.00',
'dd/mm/yyyy hh24:mi:ss') AND to_date('02/12/2003 23.59.59',
'dd/mm/yyyy hh24:mi:ss')
AND rd_callsign_pais.nombre_pais = 'Rusia'
AND rd_tipo_buques.tipo = 'TANQUE'

(2) And if we ask for " AND reportes.f_reporte BETWEEN to_date('02/12/2001
00.00.00', ..." instead " AND rd_reportes.f_reporte BETWEEN to_date('02/12/2002 00.00.00',..." takes only several minutes.

Any idea for improving first query?

Thanks

[Volker Borowski · Accepted solution]

Hi,

wow, quite a big join ....
sure you need everything of this :-)

Did you try /*+ USE_HASH */ as a hint ?

I suppose statistics are up-to-date on all tables and indexes ? Computed or estimated ?

Since you already narrowed it down to a specific column, how many distinct and absolute values do you have on column reportes.f_reporte in the intervals 2001-2002 and 2002-2003 ?

select count(*) from reportes where ...
and
select count(distinct f_reporte) from reportes where ...

Also a complete EXPLAIN_PLAN attached would be helpfull. I guess the number of distinct or absolute values of this column makes the optimizer change the access sequence of the tables due to a diffrent amount of expected date. With this change and due to expected data a merge may sound cheaper to friend "optimizer".

No piece of cake, but a real nut to crack
Volker

[Brian Crabtree]

Can you post the explain plan for both queries? Also, can you make sure that the object have current statistics.

The following is what I use to display information from the plan_table.

set linesize 150
set pagesize 1000
column operation format a50
column optimizer format a15
SELECT LPAD(' ',2*(LEVEL-1))||operation operation, optimizer,options, object_name,
position, cost from plan_table START WITH id = 0 CONNECT BY PRIOR id = parent_id;

Thanks,

Brian

[Yogeeraj_1]

hi,

Are the statistics up-to-date?

You may consider recalculating the statistics on these tables since you are using the CBO this is important.

Hopefully, you have monitoring enabled on all these tables and are using DBMS_STATS package to gather the statistics on all tables where the statistics are STALE.

E.g.

Alter table monitoring;

exec dbms_stats.gather_schema_stats( ownname => user, options => 'GATHER STALE' );

(these two commands will keep track for you and only analyze tables in which about 10% of the data has changed)

Then, retry your query.

You may wish to "alter session set sql_trace=true", use tkprof to generate a report on your query using the trace file generated and post it here.

hope this helps too!

regards
Yogeeraj