Problems with very slow QUERY

How to improve THIS QUERY is very slow
/
*------------------------------------------------------------------------------------------------------------------------------------
*/
OPERATION OPTIONS OBJECT_NAME
-----------------------------------------------------
SELECT STATEMENT
SORT GROUP BY
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID REG_TMPTRAFICO
INDEX RANGE SCAN INDEX_PERIODO
TABLE ACCESS BY INDEX ROWID ZONAL
INDEX UNIQUE SCAN ZONALIX_CODZONATIS
TABLE ACCESS BY INDEX ROWID SEGMENTO
INDEX RANGE SCAN INSEG2
TABLE ACCESS BY INDEX ROWID SUBTIPOPC
INDEX RANGE SCAN IXNSUBTIPO

/
*-------------------------------------------------------------------------------------------------------------------
*/
OBJECT_INSTANCE OBJECT_TYPE OPTIMIZER ID
--------------- ------------- --------------
CHOOSE 0
1
2
3
4
3 5
NON-UNIQUE 6
6 ANALYZED 7
UNIQUE 8
5 ANALYZED 9
NON-UNIQUE 10
4 ANALYZED 11
NON-UNIQUE ANALYZED 12







SELECT tab2.cliente,
tab2.segmento,
tab2.servicio,
tab2.trafico,
tab2.operador,
tab2.zonal,
tab2.horario,
tab2.Denominacion,
lpad(to_char(count(*)),15,0)llamadas,
lpad(to_char(sum(substr(tab2.Duracion,
1,2)*3600+substr(tab2.Duracion,3,2)*60+ substr(tab2.Duracion,5,2))),
15,0)segundos,
lpad(to_char(sum(substr(tab2.Duracion,
1,2)*60+substr(tab2.Duracion,3,2)+decode(substr(ta b2.Duracion,
5,2),'00',0,1))),9,0)minutos,
to_char(sum(to_number(tab2.soles)),'0999999.09') soles
FROM
(SELECT /*+index(TAB1 INDEX_TREE_TRAFICO) index(sg INSEG2)*/
sp.des_stip_pc AS cliente,
sg.nom_seg_cta AS segmento,
f_obtienetiposervicio (tab1.tipoorigen,
tab1.tipodestino,
tab1.callclas,
tab1.bparty
) AS servicio,
f_obtienetipotrafico (tab1.tipotelefono,
tab1.tipodestino, 1) AS trafico,
f_obteneroperador (tab1.aparty) AS operador,
zn.descripcion AS zonal,
tab1.codzon AS codzonal,
tab1.banda AS horario,
valorfacialnumtarjeta_w(tab1.refid) AS Denominacion,
tab1.soles AS soles,
tab1.stardate AS Fecha,
tab1.duracion AS Duracion,
tab1.rao AS Negocio,
tab1.expiredate AS Agencia,
tab1.tipollamada AS Indrural
-- tab1.periodo AS periodo,
-- tab1.tipotelefono AS tipotelefono
FROM (SELECT rg.cod_stip_pc AS subtipo,
rg.cod_seg_cta AS segcta,
rg.cod_tipopc AS tipopc,
rg.cod_nat_cta AS codnat,
rg.tipotelefonoorigen AS tipoorigen,
rg.tipotelefonodestino AS tipodestino,
rg.callclass AS callclas,
rg.bparty AS bparty,
rg.aparty AS aparty,
rg.tipollamada AS tipollamada,
rg.codigozonal AS codzon,
rg.startdatetime AS stardate,
rg.rao AS rao,
rg.DURATION AS duracion,
rg.expiredate AS expiredate,
rg.refid AS refid,
rg.periodo AS periodo,
rg.tipotelefono AS tipotelefono,
rg.bandahoraria AS banda,
rg.callcharge AS soles
FROM reg_tmptrafico rg
WHERE (rg.periodo=20080602||'10')
) tab1,
subtipopc sp,
segmento sg,
zonal zn
WHERE tab1.tipopc = sp.cod_tipopc
AND tab1.subtipo = sp.cod_stip_pc
AND tab1.segcta = sg.cod_seg_cta
AND tab1.codnat = sg.cod_nat_cta
AND tab1.codzon = zn.cod_zonal_atis)tab2
GROUP BY tab2.cliente,
tab2.segmento,
tab2.servicio,
tab2.trafico,
tab2.operador,
tab2.zonal,
tab2.horario,
tab2.Denominacion

On Aug 27, 3:49*pm, EliasFigueroa wrote:
> How to improve THIS QUERY is very slow
> /
> *--------------------------------------------------------------------------*----------------------------------------------------------
> */
> OPERATION * * * * OPTIONS * * * * * * * * * *OBJECT_NAME
> -----------------------------------------------------
> SELECT STATEMENT
> SORT * * * * * * *GROUP BY
> NESTED LOOPS
> NESTED LOOPS
> NESTED LOOPS
> TABLE ACCESS * * *BY INDEX ROWID *REG_TMPTRAFICO
> INDEX * * * * * * RANGE SCAN * * *INDEX_PERIODO
> TABLE ACCESS * * *BY INDEX ROWID *ZONAL
> INDEX * * * * * * UNIQUE SCAN * * ZONALIX_CODZONATIS
> TABLE ACCESS * * *BY INDEX ROWID *SEGMENTO
> INDEX * * * * * * RANGE SCAN * * *INSEG2
> TABLE ACCESS * * *BY INDEX ROWID *SUBTIPOPC
> INDEX * * * * * * RANGE SCAN * * *IXNSUBTIPO
>
> /
> *--------------------------------------------------------------------------*-----------------------------------------
> */
> OBJECT_INSTANCE OBJECT_TYPE * OPTIMIZER * ID
> --------------- ------------- --------------
> * * * * * * * * * * * * * * * CHOOSE * * * 0
> * * * * * * * * * * * * * * * * * * * * * *1
> * * * * * * * * * * * * * * * * * * * * * *2
> * * * * * * * * * * * * * * * * * * * * * *3
> * * * * * * * * * * * * * * * * * * * * * *4
> * * * * * * * 3 * * * * * * * * * * ** * *5
> * * * * * * * * NON-UNIQUE * * * * * * * * 6
> * * * * * * * 6 * * * * * * * ANALYZED * * 7
> * * * * * * * * UNIQUE * * * * * * * * * * 8
> * * * * * * * 5 * * * * * * * ANALYZED * * 9
> * * * * * * * * NON-UNIQUE * * * * * * * *10
> * * * * * * * 4 * * * * * * * ANALYZED * *11
> * * * * * * * * NON-UNIQUE * *ANALYZED * *12
>
> SELECT tab2.cliente,
> * * * *tab2.segmento,
> * * * *tab2.servicio,
> * * * *tab2.trafico,
> * * * *tab2.operador,
> * * * *tab2.zonal,
> * * * *tab2.horario,
> * * * *tab2.Denominacion,
> * * * *lpad(to_char(count(*)),15,0)llamadas,
> * * * *lpad(to_char(sum(substr(tab2.Duracion,
> 1,2)*3600+substr(tab2.Duracion,3,2)*60+ substr(tab2.Duracion,5,2))),
> 15,0)segundos,
> * * * *lpad(to_char(sum(substr(tab2.Duracion,
> 1,2)*60+substr(tab2.Duracion,3,2)+decode(substr(ta b2.Duracion,
> 5,2),'00',0,1))),9,0)minutos,
> * * * *to_char(sum(to_number(tab2.soles)),'0999999.09') soles
> FROM
> (SELECT /*+index(TAB1 INDEX_TREE_TRAFICO) *index(sg INSEG2)*/
> * * * * * * *sp.des_stip_pc AS cliente,
> * * * * * * *sg.nom_seg_cta AS segmento,
> * * * * * * *f_obtienetiposervicio (tab1.tipoorigen,
> * * * * * * * * * * * * * * * * * * tab1.tipodestino,
> * * * * * * * * * * * * * * * * * * tab1.callclas,
> * * * * * * * * * * * * * * * * * * tab1.bparty
> * * * * * * * * * * * * * * * * * *) AS servicio,
> * * * * * * *f_obtienetipotrafico (tab1.tipotelefono,
> tab1.tipodestino, 1) AS trafico,
> * * * * * * *f_obteneroperador (tab1.aparty) AS operador,
> * * * * * * *zn.descripcion AS zonal,
> * * * * * * *tab1.codzon AS codzonal,
> * * * * * * *tab1.banda AS horario,
> * * * * * * *valorfacialnumtarjeta_w(tab1.refid) AS Denominacion,
> * * * * * * *tab1.soles AS soles,
> * * * * * * *tab1.stardate AS Fecha,
> * * * * * * *tab1.duracion AS Duracion,
> * * * * * * *tab1.rao AS Negocio,
> * * * * * * *tab1.expiredate AS Agencia,
> * * * * * * *tab1.tipollamada AS Indrural
> * * * * * -- tab1.periodo AS periodo,
> * * * * * -- tab1.tipotelefono AS tipotelefono
> * * * * FROM (SELECT rg.cod_stip_pc AS subtipo,
> * * * * * * * * * * *rg.cod_seg_cta AS segcta,
> * * * * * * * * * * *rg.cod_tipopc AS tipopc,
> * * * * * * * * * * *rg.cod_nat_cta AS codnat,
> * * * * * * * * * * *rg.tipotelefonoorigen AS tipoorigen,
> * * * * * * * * * * *rg.tipotelefonodestino AS tipodestino,
> * * * * * * * * * * *rg.callclass AS callclas,
> * * * * * * * * * * *rg.bparty AS bparty,
> * * * * * * * * * * *rg.aparty AS aparty,
> * * * * * * * * * * *rg.tipollamada AS tipollamada,
> * * * * * * * * * * *rg.codigozonal AS codzon,
> * * * * * * * * * * *rg.startdatetime AS stardate,
> * * * * * * * * * * *rg.rao AS rao,
> * * * * * * * * * * *rg.DURATION AS duracion,
> * * * * * * * * * * *rg.expiredate AS expiredate,
> * * * * * * * * * * *rg.refid AS refid,
> * * * * * * * * * * *rg.periodo AS periodo,
> * * * * * * * * * * *rg.tipotelefono AS tipotelefono,
> * * * * * * * * * * *rg.bandahoraria AS banda,
> * * * * * * * * * * *rg.callcharge AS soles
> * * * * * * * * *FROM reg_tmptrafico rg
> * * * * * * * *WHERE (rg.periodo=20080602||'10')
> * * * * * * *) tab1,
> * * * * * * *subtipopc sp,
> * * * * * * *segmento sg,
> * * * * * * *zonal zn
> * * * *WHERE tab1.tipopc = sp.cod_tipopc
> * * * * *AND tab1.subtipo = sp.cod_stip_pc
> * * * * *AND tab1.segcta = sg.cod_seg_cta
> * * * * *AND tab1.codnat = sg.cod_nat_cta
> * * * * *AND tab1.codzon = zn.cod_zonal_atis)tab2
> GROUP BY tab2.cliente,
> * * * * *tab2.segmento,
> * * * * *tab2.servicio,
> * * * * *tab2.trafico,
> * * * * *tab2.operador,
> * * * * *tab2.zonal,
> * * * * *tab2.horario,
> * * * * *tab2.Denominacion

What does autotrace tell you? There is much more to consider here
than simply the query plan.

Also, provide the DDL for the tables and some sample data. if
possible.


David Fitzjarrell

On Aug 28, 12:49*am, EliasFigueroa wrote:
> How to improve THIS QUERY is very slow
> /
> *------------------------------------------------------------------------------------------------------------------------------------
> */
> OPERATION * * * * OPTIONS * * * * * * * * * *OBJECT_NAME
> -----------------------------------------------------
> SELECT STATEMENT
> SORT * * * * * * *GROUP BY
> NESTED LOOPS
> NESTED LOOPS
> NESTED LOOPS
> TABLE ACCESS * * *BY INDEX ROWID *REG_TMPTRAFICO
> INDEX * * * * * * RANGE SCAN * * *INDEX_PERIODO
> TABLE ACCESS * * *BY INDEX ROWID *ZONAL
> INDEX * * * * * * UNIQUE SCAN * * ZONALIX_CODZONATIS
> TABLE ACCESS * * *BY INDEX ROWID *SEGMENTO
> INDEX * * * * * * RANGE SCAN * * *INSEG2
> TABLE ACCESS * * *BY INDEX ROWID *SUBTIPOPC
> INDEX * * * * * * RANGE SCAN * * *IXNSUBTIPO
>
> /
> *-------------------------------------------------------------------------------------------------------------------
> */
> OBJECT_INSTANCE OBJECT_TYPE * OPTIMIZER * ID
> --------------- ------------- --------------
> * * * * * * * * * * * * * * * CHOOSE * * * 0
> * * * * * * * * * * * * * * * * * * * * * *1
> * * * * * * * * * * * * * * * * * * * * * *2
> * * * * * * * * * * * * * * * * * * * * * *3
> * * * * * * * * * * * * * * * * * * * * * *4
> * * * * * * * 3 * * * * * * * * * * ** * *5
> * * * * * * * * NON-UNIQUE * * * * * * * * 6
> * * * * * * * 6 * * * * * * * ANALYZED * * 7
> * * * * * * * * UNIQUE * * * * * * * * * * 8
> * * * * * * * 5 * * * * * * * ANALYZED * * 9
> * * * * * * * * NON-UNIQUE * * * * * * * *10
> * * * * * * * 4 * * * * * * * ANALYZED * *11
> * * * * * * * * NON-UNIQUE * *ANALYZED * *12
>
> SELECT tab2.cliente,
> * * * *tab2.segmento,
> * * * *tab2.servicio,
> * * * *tab2.trafico,
> * * * *tab2.operador,
> * * * *tab2.zonal,
> * * * *tab2.horario,
> * * * *tab2.Denominacion,
> * * * *lpad(to_char(count(*)),15,0)llamadas,
> * * * *lpad(to_char(sum(substr(tab2.Duracion,
> 1,2)*3600+substr(tab2.Duracion,3,2)*60+ substr(tab2.Duracion,5,2))),
> 15,0)segundos,
> * * * *lpad(to_char(sum(substr(tab2.Duracion,
> 1,2)*60+substr(tab2.Duracion,3,2)+decode(substr(ta b2.Duracion,
> 5,2),'00',0,1))),9,0)minutos,
> * * * *to_char(sum(to_number(tab2.soles)),'0999999.09') soles
> FROM
> (SELECT /*+index(TAB1 INDEX_TREE_TRAFICO) *index(sg INSEG2)*/
> * * * * * * *sp.des_stip_pc AS cliente,
> * * * * * * *sg.nom_seg_cta AS segmento,
> * * * * * * *f_obtienetiposervicio (tab1.tipoorigen,
> * * * * * * * * * * * * * * * * * * tab1.tipodestino,
> * * * * * * * * * * * * * * * * * * tab1.callclas,
> * * * * * * * * * * * * * * * * * * tab1.bparty
> * * * * * * * * * * * * * * * * * *) AS servicio,
> * * * * * * *f_obtienetipotrafico (tab1.tipotelefono,
> tab1.tipodestino, 1) AS trafico,
> * * * * * * *f_obteneroperador (tab1.aparty) AS operador,
> * * * * * * *zn.descripcion AS zonal,
> * * * * * * *tab1.codzon AS codzonal,
> * * * * * * *tab1.banda AS horario,
> * * * * * * *valorfacialnumtarjeta_w(tab1.refid) AS Denominacion,
> * * * * * * *tab1.soles AS soles,
> * * * * * * *tab1.stardate AS Fecha,
> * * * * * * *tab1.duracion AS Duracion,
> * * * * * * *tab1.rao AS Negocio,
> * * * * * * *tab1.expiredate AS Agencia,
> * * * * * * *tab1.tipollamada AS Indrural
> * * * * * -- tab1.periodo AS periodo,
> * * * * * -- tab1.tipotelefono AS tipotelefono
> * * * * FROM (SELECT rg.cod_stip_pc AS subtipo,
> * * * * * * * * * * *rg.cod_seg_cta AS segcta,
> * * * * * * * * * * *rg.cod_tipopc AS tipopc,
> * * * * * * * * * * *rg.cod_nat_cta AS codnat,
> * * * * * * * * * * *rg.tipotelefonoorigen AS tipoorigen,
> * * * * * * * * * * *rg.tipotelefonodestino AS tipodestino,
> * * * * * * * * * * *rg.callclass AS callclas,
> * * * * * * * * * * *rg.bparty AS bparty,
> * * * * * * * * * * *rg.aparty AS aparty,
> * * * * * * * * * * *rg.tipollamada AS tipollamada,
> * * * * * * * * * * *rg.codigozonal AS codzon,
> * * * * * * * * * * *rg.startdatetime AS stardate,
> * * * * * * * * * * *rg.rao AS rao,
> * * * * * * * * * * *rg.DURATION AS duracion,
> * * * * * * * * * * *rg.expiredate AS expiredate,
> * * * * * * * * * * *rg.refid AS refid,
> * * * * * * * * * * *rg.periodo AS periodo,
> * * * * * * * * * * *rg.tipotelefono AS tipotelefono,
> * * * * * * * * * * *rg.bandahoraria AS banda,
> * * * * * * * * * * *rg.callcharge AS soles
> * * * * * * * * *FROM reg_tmptrafico rg
> * * * * * * * *WHERE (rg.periodo=20080602||'10')
> * * * * * * *) tab1,
> * * * * * * *subtipopc sp,
> * * * * * * *segmento sg,
> * * * * * * *zonal zn
> * * * *WHERE tab1.tipopc = sp.cod_tipopc
> * * * * *AND tab1.subtipo = sp.cod_stip_pc
> * * * * *AND tab1.segcta = sg.cod_seg_cta
> * * * * *AND tab1.codnat = sg.cod_nat_cta
> * * * * *AND tab1.codzon = zn.cod_zonal_atis)tab2
> GROUP BY tab2.cliente,
> * * * * *tab2.segmento,
> * * * * *tab2.servicio,
> * * * * *tab2.trafico,
> * * * * *tab2.operador,
> * * * * *tab2.zonal,
> * * * * *tab2.horario,
> * * * * *tab2.Denominacion

In addition to David's suggestions: please define "slow". What are the
end user's expectations on this query (what's the longest tolerable
run time for it?) How far from these expectations it currently is?
What is your goal - shortest time to the first row or shortest time to
the whole result set?

And what this construct supposed to mean:

rg.periodo=20080602||'10'

You're concatenating a NUMBER to a VARCHAR2 here, and the number
actually looks like it encodes a date. What's in PERIODO column? A
DATE maybe? %-() What do you expect to be the type of the result? A
number or a string? Or a date? And am I reading it right: DURACION is
a time interval and you're storing it as a *string*??? And you expect
this query to run fast?

Have you tried without index hints? What did the CBO come up with if
you did? Was the run time worse, the same, or maybe better?

Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com

"EliasFigueroa" schreef in bericht
news:f519b589-596b-4e43-9dba-7577aee7b2a0-at-x35g2000hsb.googlegroups.com...
> How to improve THIS QUERY is very slow
> /
> *------------------------------------------------------------------------------------------------------------------------------------
> */
> OPERATION OPTIONS OBJECT_NAME
> -----------------------------------------------------
> SELECT STATEMENT
> SORT GROUP BY
> NESTED LOOPS
> NESTED LOOPS
> NESTED LOOPS
> TABLE ACCESS BY INDEX ROWID REG_TMPTRAFICO
> INDEX RANGE SCAN INDEX_PERIODO
> TABLE ACCESS BY INDEX ROWID ZONAL
> INDEX UNIQUE SCAN ZONALIX_CODZONATIS
> TABLE ACCESS BY INDEX ROWID SEGMENTO
> INDEX RANGE SCAN INSEG2
> TABLE ACCESS BY INDEX ROWID SUBTIPOPC
> INDEX RANGE SCAN IXNSUBTIPO
>
> /
> *-------------------------------------------------------------------------------------------------------------------
> */
> OBJECT_INSTANCE OBJECT_TYPE OPTIMIZER ID
> --------------- ------------- --------------
> CHOOSE 0
> 1
> 2
> 3
> 4
> 3 5
> NON-UNIQUE 6
> 6 ANALYZED 7
> UNIQUE 8
> 5 ANALYZED 9
> NON-UNIQUE 10
> 4 ANALYZED 11
> NON-UNIQUE ANALYZED 12
>
>
>
>
>
>
>
> SELECT tab2.cliente,
> tab2.segmento,
> tab2.servicio,
> tab2.trafico,
> tab2.operador,
> tab2.zonal,
> tab2.horario,
> tab2.Denominacion,
> lpad(to_char(count(*)),15,0)llamadas,
> lpad(to_char(sum(substr(tab2.Duracion,
> 1,2)*3600+substr(tab2.Duracion,3,2)*60+ substr(tab2.Duracion,5,2))),
> 15,0)segundos,
> lpad(to_char(sum(substr(tab2.Duracion,
> 1,2)*60+substr(tab2.Duracion,3,2)+decode(substr(ta b2.Duracion,
> 5,2),'00',0,1))),9,0)minutos,
> to_char(sum(to_number(tab2.soles)),'0999999.09') soles
> FROM
> (SELECT /*+index(TAB1 INDEX_TREE_TRAFICO) index(sg INSEG2)*/
> sp.des_stip_pc AS cliente,
> sg.nom_seg_cta AS segmento,
> f_obtienetiposervicio (tab1.tipoorigen,
> tab1.tipodestino,
> tab1.callclas,
> tab1.bparty
> ) AS servicio,
> f_obtienetipotrafico (tab1.tipotelefono,
> tab1.tipodestino, 1) AS trafico,
> f_obteneroperador (tab1.aparty) AS operador,
> zn.descripcion AS zonal,
> tab1.codzon AS codzonal,
> tab1.banda AS horario,
> valorfacialnumtarjeta_w(tab1.refid) AS Denominacion,
> tab1.soles AS soles,
> tab1.stardate AS Fecha,
> tab1.duracion AS Duracion,
> tab1.rao AS Negocio,
> tab1.expiredate AS Agencia,
> tab1.tipollamada AS Indrural
> -- tab1.periodo AS periodo,
> -- tab1.tipotelefono AS tipotelefono
> FROM (SELECT rg.cod_stip_pc AS subtipo,
> rg.cod_seg_cta AS segcta,
> rg.cod_tipopc AS tipopc,
> rg.cod_nat_cta AS codnat,
> rg.tipotelefonoorigen AS tipoorigen,
> rg.tipotelefonodestino AS tipodestino,
> rg.callclass AS callclas,
> rg.bparty AS bparty,
> rg.aparty AS aparty,
> rg.tipollamada AS tipollamada,
> rg.codigozonal AS codzon,
> rg.startdatetime AS stardate,
> rg.rao AS rao,
> rg.DURATION AS duracion,
> rg.expiredate AS expiredate,
> rg.refid AS refid,
> rg.periodo AS periodo,
> rg.tipotelefono AS tipotelefono,
> rg.bandahoraria AS banda,
> rg.callcharge AS soles
> FROM reg_tmptrafico rg
> WHERE (rg.periodo=20080602||'10')
> ) tab1,
> subtipopc sp,
> segmento sg,
> zonal zn
> WHERE tab1.tipopc = sp.cod_tipopc
> AND tab1.subtipo = sp.cod_stip_pc
> AND tab1.segcta = sg.cod_seg_cta
> AND tab1.codnat = sg.cod_nat_cta
> AND tab1.codzon = zn.cod_zonal_atis)tab2
> GROUP BY tab2.cliente,
> tab2.segmento,
> tab2.servicio,
> tab2.trafico,
> tab2.operador,
> tab2.zonal,
> tab2.horario,
> tab2.Denominacion

I would definitely check out the functions f_obtienetiposervicio,
f_obtienetipotrafico, f_obteneroperador used in your select statement,
and leave the rest to CBO, without hints and see what happens. If the
functions are deterministic, you could try using Function Based Indexes. If
they perform a mere select on other tables, you could try to merge these
into your query instead of using the functions.
If you're on Oracle 11g, you coluld use the function results caching
mechanism.

Shakespeare