Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: RE: URGENT : sql*loader performance problem on partionned tab

RE: RE: URGENT : sql*loader performance problem on partionned tab

From: NGUYEN Philippe (Cetelem) <philippe.nguyen_at_cetelem.fr>
Date: Thu, 04 Sep 2003 06:59:36 -0800
Message-ID: <F001.005CEC72.20030904065936@fatcity.com>


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 <<EOD >$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(500000);
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');

end loop;
COMMIT;     END;
/
exit;

EOD


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: NGUYEN Philippe (Cetelem)
  INET: philippe.nguyen_at_cetelem.fr

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: ListGuru_at_fatcity.com (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).
Received on Thu Sep 04 2003 - 09:59:36 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US