Re: Problems with very slow QUERY

On Aug 28, 12:49 am, EliasFigueroa <Eliasf..._at_gmail.com> 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(tab2.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 Received on Thu Aug 28 2008 - 00:58:39 CDT