RE: RE: URGENT : sql*loader performance problem on partionned tab
Title: RE: RE: URGENT : sql*loader performance problem on partionned tab
here the trace :
SELECT STATEMENT, GOAL = CHOOSE 2 72 1368
FOR UPDATE
FILTER
PARTITION RANGE SINGLE
TABLE ACCESS BY LOCAL INDEX ROWID FICOM HREL_FUSION 2 72 1368
INDEX RANGE SCAN FICOM IDX_HREL_FUSION_P_COD_REL 1 72
TABLE ACCESS BY INDEX ROWID FICOM PRIMEDI_ENR2_TEMP_FUSION 2 1 32
INDEX RANGE SCAN FICOM IDX_PRIMCOD_ENR2_TEMP_FUSION 1 1
TIA
>
> Philippe Nguyen
> CETELEM - Administration, Architecture Décisionnelle
> Direction Customer Relationship Management
> E-Mail : [EMAIL PROTECTED]
> Tel : (+33) 1 46 39 92 16 - Fax : (+33) 1 46 39 59 88
>
-Message d'origine-
De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Envoyé : 05 September 2003 00:09
À : Multiple recipients of list ORACLE-L
Objet : RE: RE: URGENT : sql*loader performance problem on partionned
tab
Did you explain plan? I suspect FTS taking place in case of NOT EXISTS.
It must be using Range scan for the non partitioned table.
Can you confirm / post the explain plan.
GovindanK
> Here the informations :
> table HREL_FUSION :
> 63 millions rows
> 3 indexes on columns : nodos_or, nodos_or, numcli <--- too much
> indexes ??
> table primedi_enr2_temp_fusion : 133 000 rows
> I also took snaps with stastpack, can it help you ?
> this table is recreated and re-analyze each day, but the hanging
> part of the scritp in located here :
>
> **
> SELECT NULL
> FROM hrel_fusion
> WHERE cod_rel = :b1
> AND dat_rel = :b2
> AND NOT EXISTS (SELECT NULL
> FROM primedi_enr2_temp_fusion
> WHERE primedi_enr2_temp_fusion.nodos_or =
> hrel_fusion.nodos_or
> AND primedi_enr2_temp_fusion.code_logis =
> hrel_fusion.cod_rel
> AND primedi_enr2_temp_fusion.date_logis =
> hrel_fusion.dat_rel)
> FOR UPDATE
> **
>
> The mostly amazing thing is that the same script work in 12 min on
> non-partitionned table.
>
> Here a subset of the script :
> ***
>
> cat <$maj_histo_rel
>
>
> set serveroutput on;
> DECLARE
>
> vt_code_logis char(4);
> vt_date_logis number(4);
> n number;
>
> cursor curs_code_ctlm is
> select code_logis,date_logis from primedi_temp_fusion
> where exists (select null from $TABLE_RETOURS
> where $TABLE_RETOURS.code_logis=primedi_temp_fusion.code_logis
> and $TABLE_RETOURS.date_logis=primedi_temp_fusion.date_logis
> and $TABLE_RETOURS.code_ficom=primedi_temp_fusion.code_ficom
> )
> ;
>
>
> cursor curs_histo_ctlm1 (vt_code_logis char, vt_date_logis number) is
> select null from HREL_FUSION
> where cod_rel=vt_code_logis
> and dat_rel=vt_date_logis
> and not exists (select null from primedi_enr2_temp_fusion
> where
> primedi_enr2_temp_fusion.nodos_or=HREL_FUSION.nodos_or
> and
> primedi_enr2_temp_fusion.code_logis=HREL_FUSION.cod_rel
> and
> primedi_enr2_temp_fusion.date_logis=HREL_FUSION.dat_rel)
> for update
> ;
>
> BEGIN
> DBMS_OUTPUT.ENABLE(50);
> for curs in curs_code_ctlm
> loop
> n:=0;
> for enr_histo in curs_histo_ctlm1
> (curs.code_logis,curs.date_logis) loop
> delete from HREL_FUSION
> where current of curs_histo_ctlm1;
> n:=n+1;
> --if (mod(n,5000) = 0) then
> --commit;
> --end if;
> end loop;
> --commit;
> DBMS_OUTPUT.PUT_LINE('Supression de ' || n || ' ligne(s) '
> ||curs.code_logis || curs.date_logis || ' dans l historique HREL_FUSION');
>
>
> -- update $TABLE_RETOURS
> -- set DATE_MAJ=to_number(to_char(sysdate,'mmdd'))
> -- where exists (select null from primedi_temp_fusion
> -- where curs.code_logis=$TABLE_RETOURS.code_logis
> -- and curs.date_logis=$TABLE_RETOURS.date_logis
> -- )
> --;
>
> end loop;
> COMMIT;
>
> END;
> /
> exit;
>
> EOD
> ***
>
--
Plea
RE: RE: URGENT : sql*loader performance problem on partionned tab
Did you explain plan? I suspect FTS taking place in case of NOT EXISTS.
It must be using Range scan for the non partitioned table.
Can you confirm / post the explain plan.
GovindanK
> Here the informations :
> table HREL_FUSION :
> 63 millions rows
> 3 indexes on columns : nodos_or, nodos_or, numcli <--- too much
> indexes ??
> table primedi_enr2_temp_fusion : 133 000 rows
> I also took snaps with stastpack, can it help you ?
> this table is recreated and re-analyze each day, but the hanging
> part of the scritp in located here :
>
> **
> SELECT NULL
> FROM hrel_fusion
> WHERE cod_rel = :b1
> AND dat_rel = :b2
> AND NOT EXISTS (SELECT NULL
> FROM primedi_enr2_temp_fusion
> WHERE primedi_enr2_temp_fusion.nodos_or =
> hrel_fusion.nodos_or
> AND primedi_enr2_temp_fusion.code_logis =
> hrel_fusion.cod_rel
> AND primedi_enr2_temp_fusion.date_logis =
> hrel_fusion.dat_rel)
> FOR UPDATE
> **
>
> The mostly amazing thing is that the same script work in 12 min on
> non-partitionned table.
>
> Here a subset of the script :
> ***
>
> cat <$maj_histo_rel
>
>
> set serveroutput on;
> DECLARE
>
> vt_code_logis char(4);
> vt_date_logis number(4);
> n number;
>
> cursor curs_code_ctlm is
> select code_logis,date_logis from primedi_temp_fusion
> where exists (select null from $TABLE_RETOURS
> where $TABLE_RETOURS.code_logis=primedi_temp_fusion.code_logis
> and $TABLE_RETOURS.date_logis=primedi_temp_fusion.date_logis
> and $TABLE_RETOURS.code_ficom=primedi_temp_fusion.code_ficom
> )
> ;
>
>
> cursor curs_histo_ctlm1 (vt_code_logis char, vt_date_logis number) is
> select null from HREL_FUSION
> where cod_rel=vt_code_logis
> and dat_rel=vt_date_logis
> and not exists (select null from primedi_enr2_temp_fusion
> where
> primedi_enr2_temp_fusion.nodos_or=HREL_FUSION.nodos_or
> and
> primedi_enr2_temp_fusion.code_logis=HREL_FUSION.cod_rel
> and
> primedi_enr2_temp_fusion.date_logis=HREL_FUSION.dat_rel)
> for update
> ;
>
> BEGIN
> DBMS_OUTPUT.ENABLE(50);
> for curs in curs_code_ctlm
> loop
> n:=0;
>for enr_histo in curs_histo_ctlm1
> (curs.code_logis,curs.date_logis) loop
>delete from HREL_FUSION
>where current of curs_histo_ctlm1;
>n:=n+1;
>--if (mod(n,5000) = 0) then
> --commit;
>--end if;
>end loop;
>--commit;
>DBMS_OUTPUT.PUT_LINE('Supression de ' || n || ' ligne(s) '
> ||curs.code_logis || curs.date_logis || ' dans l historique HREL_FUSION');
>
>
>-- update $TABLE_RETOURS
>-- set DATE_MAJ=to_number(to_char(sysdate,'mmdd'))
>-- where exists (select null from primedi_temp_fusion
> -- where curs.code_logis=$TABLE_RETOURS.code_logis
> -- and curs.date_logis=$TABLE_RETOURS.date_logis
> -- )
> --;
>
> end loop;
> COMMIT;
>
> END;
> /
> exit;
>
> EOD
> ***
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: RE: URGENT : sql*loader performance problem on partionned tab
Title: RE: RE: URGENT : sql*loader performance problem on partionned table - not sql*loader problem but cursor pb!
Here the informations :
table HREL_FUSION :
63 millions rows
3 indexes on columns : nodos_or, nodos_or, numcli <--- too much indexes ??
table primedi_enr2_temp_fusion : 133 000 rows
I also took snaps with stastpack, can it help you ?
this table is recreated and re-analyze each day, but the hanging part of the scritp in located here :
**
SELECT NULL
FROM hrel_fusion
WHERE cod_rel = :b1
AND dat_rel = :b2
AND NOT EXISTS (SELECT NULL
FROM primedi_enr2_temp_fusion
WHERE primedi_enr2_temp_fusion.nodos_or =
hrel_fusion.nodos_or
AND primedi_enr2_temp_fusion.code_logis =
hrel_fusion.cod_rel
AND primedi_enr2_temp_fusion.date_logis =
hrel_fusion.dat_rel)
FOR UPDATE
**
The mostly amazing thing is that the same script work in 12 min on non-partitionned table.
Here a subset of the script :
***
cat <$maj_histo_rel
set serveroutput on;
DECLARE
vt_code_logis char(4);
vt_date_logis number(4);
n number;
cursor curs_code_ctlm is
select code_logis,date_logis from primedi_temp_fusion
where exists (select null from $TABLE_RETOURS
where $TABLE_RETOURS.code_logis=primedi_temp_fusion.code_logis
and $TABLE_RETOURS.date_logis=primedi_temp_fusion.date_logis
and $TABLE_RETOURS.code_ficom=primedi_temp_fusion.code_ficom
)
;
cursor curs_histo_ctlm1 (vt_code_logis char, vt_date_logis number) is
select null from HREL_FUSION
where cod_rel=vt_code_logis
and dat_rel=vt_date_logis
and not exists (select null from primedi_enr2_temp_fusion
where primedi_enr2_temp_fusion.nodos_or=HREL_FUSION.nodos_or
and primedi_enr2_temp_fusion.code_logis=HREL_FUSION.cod_rel
and primedi_enr2_temp_fusion.date_logis=HREL_FUSION.dat_rel)
for update
;
BEGIN
DBMS_OUTPUT.ENABLE(50);
for curs in curs_code_ctlm
loop
n:=0;
for enr_histo in curs_histo_ctlm1 (curs.code_logis,curs.date_logis) loop
delete from HREL_FUSION
where current of curs_histo_ctlm1;
n:=n+1;
--if (mod(n,5000) = 0) then
--commit;
--end if;
end loop;
--commit;
DBMS_OUTPUT.PUT_LINE('Supression de ' || n || ' ligne(s) ' ||curs.code_logis || curs.date_logis || ' dans l historique HREL_FUSION');
-- update $TABLE_RETOURS
-- set DATE_MAJ=to_number(to_char(sysdate,'mmdd'))
-- where exists (select null from primedi_temp_fusion
-- where curs.code_logis=$TABLE_RETOURS.code_logis
-- and curs.date_logis=$TABLE_RETOURS.date_logis
-- )
--;
end loop;
COMMIT;
END;
/
exit;
EOD
***
