Re: RE: URGENT : sql*loader performance problem on partionned table
1. by 'filling' are you doing inserts also? I see a 'FOR UPDATE' statement which implies that you are doing updates from with in a cursor. 2. How many indexes do you have on this table? 3. Is anything else running at the same time? 4. Are you doing your DML from with in a cursor. This is very slow. Try to do it all in SQL. 5. Consider changing your not exists to Where NOT IN (SELECT /*+ hash_aj */ ...) That can be faster if your sub-query is significantly less costly than your your outer query(its the opposite for exists). Wont necessarily help in your case. You can also eliminate the join with the 'in'. This can improve performance as well. Please post a subset of your batch script. Please format it so its readable. Ill look at it. Also please post how many rows are in each table. Ill see if I can find anything. I think the two locks on the same object are locks on seperate partitions. Not sure. > > From: "NGUYEN Philippe (Cetelem)" <[EMAIL PROTECTED]> > Date: 2003/09/03 Wed AM 09:59:27 EDT > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: 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. > 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_USERNAMEOBJECT_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:
RE: URGENT : sql*loader performance problem on partionned table
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]
Re: URGENT : sql*loader performance problem on partionned table
Hello Did you check alert.log for any unusual messages? May be it is using lot of rollback / archiving. You can use unrecoverable option to load. You have not mentioned whether you are using direct load or not. Check if too many extents are getting allocated at runtime. That is going to slow down the load. Presorting data on indexed columns is another method to speeden up load. This is likely to minimise the use of temp segment. HTH GovindanK > 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: 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: URGENT : sql*loader performance problem on partionned table
Yesterday i posted a reply on this .. but did not reach. Check if too much logging taking place. Avoid this with loading as UNRECOVERABLE; Or else Presort the data on the index key to minimise the use of Temp segment. As of now i am able to think of only these two. HTH GovindanK > 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: 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: URGENT : sql*loader performance problem on partionned table
Title: 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. 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]
RE: URGENT : sql*loader performance problem on partionned table
Title: RE: URGENT : sql*loader performance problem on partionned table precision : Oracle 8.1.7.3 (64 bits) in Solaris 8 -Message d'origine- De : NGUYEN Philippe (Cetelem) Envoyé : 02 September 2003 18:14 À : '[EMAIL PROTECTED]' Objet : URGENT : sql*loader performance problem on partionned table 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!
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] 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).
URGENT : sql*loader performance problem on partionned table
Title: URGENT : sql*loader performance problem on partionned table 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!
