Re: Problems with very slow QUERY

"EliasFigueroa" <Eliasfimo_at_gmail.com> 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(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

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 Received on Tue Sep 02 2008 - 05:27:59 CDT