| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: URGENT : sql*loader performance problem on partionned table
thank U Dennis,
I use local index,
the script is still running (2hours now! instead of 10-20 min) and here is
the statement in question (the script who used non-partionned table is
already ended)
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.
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 ?
-----Message d'origine-----
De : DENNIS WILLIAMS [mailto:DWILLIAMS_at_lifetouch.com]
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.
dwilliams_at_lifetouch.com
-----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
Thankx in advance!
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: DENNIS WILLIAMS
INET: DWILLIAMS_at_LIFETOUCH.COM
--
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 servicesto: 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 Wed Sep 03 2003 - 08:59:27 CDT
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
 |
 |