DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: problem with 817 on NT & database links

  1. #1
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342

    Angry

    Hi everyone,
    I have a strange problem. I try to explain.

    I have 2 db's.
    dbA 8.1.7.1 NT4 SP6a on server A
    dbB 8.1.7.1 AIX 4.3.3 on server B
    dbC 7.3.4.5 NT4 SP6a on server A
    prod 7.3.4.3 AIX 4.3.3 on server C

    I have a PL/SQL-block with a query that selects rather a large number of fields from 5 tables ( You can find the query at the end of the post )

    When I run it from dbC, it takes about 28 seconds.
    When I run it from dbB, it takes about 32 seconds.
    When I run it from dbA, it takes 5 minutes !!!!!!!!!!

    All my servers have a 100Mbit card, and are connected to the same 100Mbit switch. Why does it take so much time on dbA ???

    When I reduce the number of fields in the query, I see that the response on the dbC & dbB stays rather the same, on dbA the response-time reduces dramatically.
    When I do 'select 1 from... ' then it takes only 30 secs on dbA.

    I'm afraid i'm hitting an oracle bug?

    Anyone an idea ??

    Regards
    Gert

    Here comes the code :

    declare
    aantal number := 0;
    cursor c is
    select /*+ ORDERED
    use_nl(pdl)
    use_nl(dag)
    use_nl(pst)
    use_nl(afb1)
    use_nl(afb2)
    index (pdl pdl_7)
    index (dag dag_pk)
    index (pst pst_pk)
    index(afb1 afb_bat_i)
    index(afb2 afb_bat_i)
    */
    PST.KLA_ID PST_KLA_ID,
    PST.WEEK_VAN PST_WEEK_VAN,
    PST.WEEK_TOT PST_WEEK_TOT,
    nvl(PST.CREATIE_GEBR,'NA') PST_CREATIE_GEBR,
    nvl(PST.WIJZ_GEBR,'NA') PST_WIJZ_GEBR,
    nvl(PST.REFERENTIENR,'NA') PST_REFERENTIENR,
    nvl(PST.BEHEER_KAN_CD,'NA') PST_BEHEER_KAN_CD,
    PST.ID PST_ID,
    PST.PER_ID PST_PER_ID,
    PST.KAN_CD PST_KAN_CD,
    nvl(PST.DIV_CD,'NA') PST_DIV_CD,
    nvl(PST.BED_CD,'NA') PST_BED_CD,
    PST.TIMESHEETS PST_TIMESHEETS,
    PST.DIV_CAT PST_DIV_CAT,
    PST.CREATIE_DT PST_CREATIE_DT,
    PST.WIJZ_DT PST_WIJZ_DT,
    PST.AFDRUK_NUM PST_AFDRUK_NUM,
    PST.AFGEDRUKT PST_AFGEDRUKT,
    PST.BINNEN_PERIODE_IND PST_BINNEN_PERIODE_IND,
    nvl(DAG.KPL_CODE,'NA') DAG_KPL_CODE,
    DAG.PRESTATIE_DT DAG_PRESTATIE_DT,
    nvl(DAG.CREATIE_GEBR,'NA') DAG_CREATIE_GEBR,
    nvl(DAG.WIJZ_GEBR,'NA') DAG_WIJZ_GEBR,
    DAG.ABC_ID DAG_ABC_ID,
    nvl(DAG.SDL_SRG_CD,'NA') DAG_SDL_SRG_CD,
    DAG.URLN_BEDR DAG_URLN_BEDR,
    DAG.PERCENTAGE_INTERIM DAG_PERCENTAGE_INTERIM,
    DAG.BIJK_URLN_BEDR DAG_BIJK_URLN_BEDR,
    DAG.VOLGNR DAG_VOLGNR,
    nvl(DAG.VAL_CD,'NA') DAG_VAL_CD,
    DAG.KPL_KLA_ID DAG_KPL_KLA_ID,
    DAG.DAGEN_AANT DAG_DAGEN_AANT,
    DAG.UREN_AANT DAG_UREN_AANT,
    DAG.CREATIE_DT DAG_CREATIE_DT,
    DAG.WIJZ_DT DAG_WIJZ_DT,
    DAG.PRC_CD DAG_PRC_CD,
    DAG.SDL_SRG_KLA_ID DAG_SDL_SRG_KLA_ID,
    nvl(DAG.SDL_CD,'NA') DAG_SDL_CD,
    DAG.PRC_CD_BIJK DAG_PRC_CD_BIJK,
    DAG.SDL_WEEKDAGTYPE DAG_SDL_WEEKDAGTYPE,
    DAG.BIJK_UREN_AANT DAG_BIJK_UREN_AANT,
    DAG.PERCENTAGE_KLANT DAG_PERCENTAGE_KLANT,
    DAG.BET_BIJK_UREN_VOL_IND DAG_BET_BIJK_UREN_VOL_IND,
    DAG.FAC_BIJK_UREN_VOL_IND DAG_FAC_BIJK_UREN_VOL_IND,
    DAG.AUTO_GEN_IND DAG_AUTO_GEN_IND,
    DAG.FAC_PRIJS_INL_BEDR DAG_FAC_PRIJS_INL_BEDR,
    DAG.BIJK_FAC_PRIJS_INL_BEDR DAG_BIJK_FAC_PRIJS_INL_BEDR,
    PDL.CON_KLA_LE_ID PDL_CON_KLA_LE_ID,
    PDL.IRP_LOONMAAND PDL_IRP_LOONMAAND,
    PDL.FAC_PRIJS_BEDR PDL_FAC_PRIJS_BEDR,
    PDL.CREATIE_DT PDL_CREATIE_DT,
    PDL.WIJZ_DT PDL_WIJZ_DT,
    PDL.FAC_ID PDL_FAC_ID,
    nvl(PDL.IRP_KAN_CD,'NA') PDL_IRP_KAN_CD,
    PDL.BAT_ID_GERAC_VER PDL_VERSTUURD_BAT_ID,
    nvl(PDL.FAC_TEKST,'NA') PDL_FAC_TEKST,
    PDL.EPREST_AFGEDRUKT_IND PDL_EPREST_AFGEDRUKT_IND,
    PDL.CREATIE_CO_IND PDL_CREATIE_CO_IND,
    PDL.TEKEN PDL_TEKEN,
    PDL.CON_KLA_FAC_ID PDL_CON_KLA_FAC_ID,
    PDL.IRP_LOONJAAR PDL_IRP_LOONJAAR,
    PDL.VOLGNR PDL_VOLGNR,
    PDL.PRC_CD PDL_PRC_CD,
    PDL.GERAC_STATUS PDL_GERAC_STATUS,
    PDL.GEW_DGN_AANT PDL_GEW_DGN_AANT,
    PDL.UREN_AANT PDL_UREN_AANT,
    PDL.BEDRAG PDL_BEDRAG,
    PDL.INT_BEDR PDL_INT_BEDR,
    PDL.FAC_COEF PDL_FAC_COEF,
    PDL.FAC_BEDR PDL_FAC_BEDR,
    PDL.BET_DR_KLANT_IND PDL_BET_DR_KLANT_IND,
    PDL.TIJDSEENH PDL_TIJDSEENH,
    PDL.CREATIE_GEBR PDL_CREATIE_GEBR,
    PDL.WIJZ_GEBR PDL_WIJZ_GEBR,
    PDL.IRP_FICHENR PDL_IRP_FICHENR,
    PDL.BAT_ID_GERAC_ONT PDL_ONTVANGEN_BAT_ID,
    PDL.FAC_STATUS PDL_FAC_STATUS,
    PDL.PERCENTAGE_KLANT PDL_PERCENTAGE_KLANT,
    PDL.PERCENTAGE_INTERIM PDL_PERCENTAGE_INTERIM,
    PDL.FAC_TYPE PDL_FAC_TYPE,
    PDL.FICHELIJN_NUM PDL_FICHELIJN_NUM,
    -- PDL.OMZET_TOEGEVOEGD_IND PDL_OMZET_TOEGEVOEGD_IND,
    -- PDL.OMZET_GECORRIGEERD_IND PDL_OMZET_GECORRIGEERD_IND,
    'N' PDL_OMZET_TOEGEVOEGD_IND,
    'N' PDL_OMZET_GECORRIGEERD_IND,
    PDL.FAC_DT EPD_FACTUUR_DT,
    PDL.FAC_ST EPD_FACTUUR_STATUS,
    PDL.BAT_ID_OMZ_TOEV EPD_BAT_ID_OMZ_TOEV,
    PDL.BAT_ID_OMZ_CORR EPD_BAT_ID_OMZ_CORR,
    nvl(PDL.ABC_SSZ_CD,'NA') EPD_SSZ_CD,
    PDL.OMZ_OORSPRONG EPD_OMZ_OORSPRONG,
    to_number(to_char(AFB1.bkm_boekjaar) || to_char(afb1.bkm_boekmaand)) toev_maand,
    to_number(to_char(AFB2.bkm_boekjaar) || to_char(afb2.bkm_boekmaand)) terug_maand,
    to_number(to_char(pdl.dag_prestatie_dt,'yyyymm')) prestatiemaand,
    pst.rsz_cd PST_RSZ_CD,
    pst.sec_cd PST_SEC_CD
    -- pdl.rowid
    from prestatie_details@prod pdl
    , dagprestaties@prod dag
    , prestatiestaten@prod pst
    , afsluiten_boekmaanden@prod afb1
    , afsluiten_boekmaanden@prod afb2
    where pdl.wijz_dt >= to_date('28082001','DDMMYYYY')
    and pdl.dag_pst_kan_cd = '000'
    and dag.pst_id = pdl.dag_pst_id
    and dag.pst_kan_cd = pdl.dag_pst_kan_cd
    and dag.volgnr = pdl.dag_volgnr
    and pst.id = dag.pst_id
    and pst.kan_cd = dag.pst_kan_cd
    and afb1.bat_id (+) = pdl.bat_id_omz_toev
    and afb2.bat_id (+) = pdl.bat_id_omz_corr
    ;
    begin
    dbms_output.enable(9999999);
    for r in c
    loop
    aantal := aantal + 1;
    dbms_output.put_line(aantal);
    end loop;
    end;
    /

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Is the explain plan show the same plan model in all your DBs. What is the optimizer mode on DB-A and that of the other. Check the init.ora parameters of the NT and that of AIX.


    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width