Problems with very slow QUERY

From: EliasFigueroa <Eliasfimo_at_gmail.com>
Date: Wed, 27 Aug 2008 13:49:55 -0700 (PDT)
Message-ID: <f519b589-596b-4e43-9dba-7577aee7b2a0@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(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

Received on Wed Aug 27 2008 - 15:49:55 CDT