-
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;
/
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|