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');

• update $TABLE_RETOURS
• set DATE_MAJ=to_number(to_char(sysdate,'yyyymmdd'))
• 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: 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