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

[NGUYEN Philippe (Cetelem)]

Title: RE: URGENT : sql*loader performance problem on partionned table

thankx for all those advbices, actually, the problem does not come from the sql*loader but from this particular statement :

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 explain plan show that hrel_fusion table is ACCESS BY LOCAL INDEX ROWID and primedi table used INDEX too.
When I looking at lock tables it show me 2 session locked :

SQL> select session_id, oracle_username, object_name
  2  from v$locked_object lo, dba_objects o
  3  where lo.object_id = o.object_id
  4  ;

SESSION_ID ORACLE_USERNAME                OBJECT_NAME
---------- ------------------------------ --------------------------------------------------------------------------------

         7 FICOM                          HREL_FUSION
         7 FICOM                          HREL_FUSION

-Why are there 2 locks on this table even there only one session ?
- Yesterday I did gather_statitic on this table and it worked fine (12 min), should I do this each day ?
TIA
Philippe

-----Message d'origine-----
De : DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]]
Envoyé : 02 September 2003 19:45
À : Multiple recipients of list ORACLE-L
Objet : RE: URGENT : sql*loader performance problem on partionned table

Philippe
     You aren't providing many details on which to base some guesses.
However, your statement "brand new disks" implies that you are adding
additional partitions to an existing table. Then, your statement "should I
drop indexes" implies that you have indexes on the partitioned table,
possibly global indexes. If you are continuing to grow a partitioned (or
non-partitioned) table than has indexes, then the load time will increase
because Oracle must integrate each new entry into the index, which will take
more time as the index grows. Take a look at local indexes. If I not
understood your situation correctly, please clarify your situation further.

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]

-----Original Message-----
Sent: Tuesday, September 02, 2003 12:14 PM
To: Multiple recipients of list ORACLE-L

Hi gurus,
we have two daily loads that one after the other.
The first fill up a non partitionned table and the second do the same into a
partitionned table.
First times the second load ran very quickly : 1 min instead of 5 min (non
partitionnned table).
But now since few days, the partitionned table filling take more than 1-2
hours ! yesterday it took 14 hours !!!!
Any one has any clue ?
- Tablespaces for partitionned table used brand new separate disks
- Does it come from indexes ? should I drop them first ?

Thankx in advance!

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]