SQL*net message from client severly impact the Parse call of an insert statement
Dear All, I am faced with the following situation. Oracle 8.1.7.4. 64 bit , Solaris 8 There is a loader java process that when is executed against a test database(dwdsa)the response time is as expected to be. However when it is executed against the production instance (dwods) it is 2,5 to 3 times slower. I have traced the session on both occasions and reading the book of Carry Milsap I have spotted that the WAIT for SQL*Net message from client is very high for the PARSE call of an INSERT statement, whereas for the test instance there is no delay. I cannot however explain what might be the cause of this. Can somebody sched some light into this problem. Attached please find the SQL 10046 trace with level 8 a) Production trace (parser_dwods.zip) b) Test trace parser_dwdsa.zip Kind Regards, Hatzistavrou Yannis parser_dwods.zip Description: Zip compressed data The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. parser_dwdsa.zip Description: Zip compressed data
RE: SQL*net message from client severly impact the Parse call of an insert statement
John, These are just a couple of ideas coming to me (I haven't checked the attachments, answering to this through a web interface). First of all, having a _whole_ process much slowed by parsing proves, if nothing else, that you are doing too much of it. If it happened very few times you wouldn't even have noticed it. I am far from being a Java expert but there is the equivalent of bind variables with JDBC (forgot how it's called), your developers should use them. There is also the quick and dirty fix of forcing cursor sharing for the session, but don't mention it to them too quickly. Second, I am surprised by the kernel waiting for some client information in the parse phase. What does it need from the client side to parse? It has everything; unless, perhaps, it's the CBO requiring information about the session's own environment? But this resides on the server host too. The only explanation which makes sense to me is that parsing occurs as a separate phase, before execution, and that the wait is between parsing and execution. In C the 'parse' call can be either immediate (requiring one round-trip on its own) or delayed (parsing occurring when execution is requested). You probably are in the first case, and should ask your developers to do whatever is needed to be in the second one. HTH S Faroult - --- Original Message --- - From: Hatzistavrou John [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tue, 30 Dec 2003 23:34:25 Dear All, I am faced with the following situation. Oracle 8.1.7.4. 64 bit , Solaris 8 There is a loader java process that when is executed against a test database(dwdsa)the response time is as expected to be. However when it is executed against the production instance (dwods) it is 2,5 to 3 times slower. I have traced the session on both occasions and reading the book of Carry Milsap I have spotted that the WAIT for SQL*Net message from client is very high for the PARSE call of an INSERT statement, whereas for the test instance there is no delay. I cannot however explain what might be the cause of this. Can somebody sched some light into this problem. Attached please find the SQL 10046 trace with level 8 a) Production trace (parser_dwods.zip) b) Test trace parser_dwdsa.zip Kind Regards, Hatzistavrou Yannis -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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: undo and insert
What is ITL ? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 24, 2003 4:59 PM Just the previous version of the changed columns, plus an overhead of about 80 bytes which relates to ITLs, linked lists, operation descriptions etc. Bear in mind that undo relating to indexes is not the same as undo relating to tables, though. An update to an indexed column results in one index entry being deleted (so the whole index entry is coped to the undo) and another index entry being inserted (which also means the whole (new) index entry being copied to the undo). There is a statistic relating to undo size in v$sysstat/v$sesstat in the most recent versions of Oracle. While a transaction is active, you can track it in v$transaction, and there are two columns in that view giving you information about the undo - used_urec (undo records created) and used_ublk (undo block used). Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 24, 2003 8:44 PM I have a related question : What about update? In rollback segment : Will it store the whole row for before image or just the changed column and rowid. Is there a way to get the size of the rollback from some where in the database. or v$ views. Like we can get an idea about redo size from redo log files generated. Thank you -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Akshay Kumar 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: undo and insert
ITL = Interested Transaction List The entries are used for locking. See the following articles on ITL http://www.jlcomp.demon.co.uk/faq/locked_rows.html http://www.ixora.com.au/q+a/0010/13133621.htm Jared Akshay Kumar [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/26/2003 10:54 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: undo and insert What is ITL ? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 24, 2003 4:59 PM Just the previous version of the changed columns, plus an overhead of about 80 bytes which relates to ITLs, linked lists, operation descriptions etc. Bear in mind that undo relating to indexes is not the same as undo relating to tables, though. An update to an indexed column results in one index entry being deleted (so the whole index entry is coped to the undo) and another index entry being inserted (which also means the whole (new) index entry being copied to the undo). There is a statistic relating to undo size in v$sysstat/v$sesstat in the most recent versions of Oracle. While a transaction is active, you can track it in v$transaction, and there are two columns in that view giving you information about the undo - used_urec (undo records created) and used_ublk (undo block used). Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 24, 2003 8:44 PM I have a related question : What about update? In rollback segment : Will it store the whole row for before image or just the changed column and rowid. Is there a way to get the size of the rollback from some where in the database. or v$ views. Like we can get an idea about redo size from redo log files generated. Thank you -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Akshay Kumar 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).
undo and insert
An undo segment is used to save the old value of data. For insert operation, there is no old data to be saved. So, there should be no undo generated. Right? Roger Xu Database Administrator Dr Pepper Bottling Company of Texas (972)721-8337 This email has been scanned for all viruses by the MessageLabs Email Security System. For more information on a proactive email security service working around the clock, around the globe, visit http://www.messagelabs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Roger Xu 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: undo and insert
For insert, in order to rollback, Oracle will still have to get the rowid of the new inserted rows, so that it can rollback when needed. So there will still be undo. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, December 25, 2003 1:49 AM An undo segment is used to save the old value of data. For insert operation, there is no old data to be saved. So, there should be no undo generated. Right? Roger Xu Database Administrator Dr Pepper Bottling Company of Texas (972)721-8337 This email has been scanned for all viruses by the MessageLabs Email Security System. For more information on a proactive email security service working around the clock, around the globe, visit http://www.messagelabs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Roger Xu 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: zhu chao 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: undo and insert
I have a related question : What about update? In rollback segment : Will it store the whole row for before image or just the changed column and rowid. Is there a way to get the size of the rollback from some where in the database. or v$ views. Like we can get an idea about redo size from redo log files generated. Thank youzhu chao [EMAIL PROTECTED] wrote: For insert, in order to rollback, Oracle will still have to get the rowid of the new inserted rows, so that it can rollback when needed.So there will still be undo. Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard
Re: undo and insert
And then there's the previous version of whichever ITL entry gets taken by the transaction doing the insert. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 24, 2003 8:19 PM For insert, in order to rollback, Oracle will still have to get the rowid of the new inserted rows, so that it can rollback when needed. So there will still be undo. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, December 25, 2003 1:49 AM An undo segment is used to save the old value of data. For insert operation, there is no old data to be saved. So, there should be no undo generated. Right? Roger Xu Database Administrator Dr Pepper Bottling Company of Texas (972)721-8337 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: undo and insert
Just the previous version of the changed columns, plus an overhead of about 80 bytes which relates to ITLs, linked lists, operation descriptions etc. Bear in mind that undo relating to indexes is not the same as undo relating to tables, though. An update to an indexed column results in one index entry being deleted (so the whole index entry is coped to the undo) and another index entry being inserted (which also means the whole (new) index entry being copied to the undo). There is a statistic relating to undo size in v$sysstat/v$sesstat in the most recent versions of Oracle. While a transaction is active, you can track it in v$transaction, and there are two columns in that view giving you information about the undo - used_urec (undo records created) and used_ublk (undo block used). Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 24, 2003 8:44 PM I have a related question : What about update? In rollback segment : Will it store the whole row for before image or just the changed column and rowid. Is there a way to get the size of the rollback from some where in the database. or v$ views. Like we can get an idea about redo size from redo log files generated. Thank you -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: insert in batch loading
Janne, Thanks very much for your wonderful detail suggestion. I'll definitely use it to analyze the redo. David From: Jan Korecki [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: insert in batch loading Date: Tue, 25 Nov 2003 13:34:26 -0800 Hi! I suggest you set up a test where you check the redo. For example: - start test SQL select LOG_MODE from v$database; LOG_MODE ARCHIVELOG SQL create table append_test as select * from all_objects where 1=2; Table created. SQL create index append_test_ind on append_test(owner); Index created. SQL alter table append_test nologging; Table altered. SQL alter index append_test_ind nologging; Index altered. SQL analyze table append_test compute statistics for table for all indexes for all indexed columns; Table analyzed. SQL set serveroutput on size 10 SQL set autotrace on STATISTICS SQL insert /*+ append */ into append_test select * from [EMAIL PROTECTED]; 35605 rows created. Statistics -- 1213732 redo size 35605 rows processed SQL rollback; Rollback complete. SQL alter index append_test_ind unusable; Index altered. SQL alter session set skip_unusable_indexes=TRUE; Session altered. SQL insert /*+ append */ into append_test select * from [EMAIL PROTECTED]; 35605 rows created. Statistics -- 1172 redo size 35605 rows processed end test This works well with a non-unique index. If the index is unique or you have primary key/unique constraint you will run into problem because you cannot use +append with a unusable unique index. You have 2 choices. 1) have the constraints set to deferreble and disble them before load, set the unique index to unusable, load, rebuild index nologging, enable constraints 2) if you dont want to have the constraints deferrable- drop indexed before load and create them afterwards with nologging. Even if you do 2) you will save a lot of time and have a lot less redo. Regards, Janne! David Boyd wrote: Janne, Thanks for your reply. We have 6 redo log switchings during inserting a table that has 1 million records. Our redo log size (100 MB) dominates the checkpoint frequency. The table has two indexes. We don't set them to unusable during inserting. David From: Jan Korecki [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: insert in batch loading Date: Tue, 25 Nov 2003 08:19:26 -0800 David Boyd wrote: Hi All, We have some batch loading jobs that truncate the tables first, then insert into the tables as select from tables through database link. Those jobs run daily. Every time when those jobs run, they cause cannot allocate new log, Checkpoint not complete. All of tables and their indexes are in nologging mode. We have /*+append*/ hint in the insert statement. We have 5 redo groups with member of 100 MB. Some tables have more than 1 million records. I was wondering if any body knows a method that forces a commit after every 1000 records inserted, which is like delete_commit procedure. David _ Groove on the latest from the hot new rock groups! Get downloads, videos, and more here. http://special.msn.com/entertainment/wiredformusic.armx Hi! If you do incremental commits the batch will run slower or not at all (ora -01555). Have you checked how much redo your insert statement generates? You might have missed something. If you have indexes on the table you will have to set them unusable and alter session set skip_unusable_indexes=true Rebuild the indexes after the load with nologging. Janne! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jan Korecki 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). _ online games and music with a high-speed Internet connection! Prices start at less than $1 a day average. https://broadband.msn.com (Prices may vary by service area.) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jan Korecki INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web
insert in batch loading
Hi All, We have some batch loading jobs that truncate the tables first, then insert into the tables as select from tables through database link. Those jobs run daily. Every time when those jobs run, they cause cannot allocate new log, Checkpoint not complete. All of tables and their indexes are in nologging mode. We have /*+append*/ hint in the insert statement. We have 5 redo groups with member of 100 MB. Some tables have more than 1 million records. I was wondering if any body knows a method that forces a commit after every 1000 records inserted, which is like delete_commit procedure. David _ Groove on the latest from the hot new rock groups! Get downloads, videos, and more here. http://special.msn.com/entertainment/wiredformusic.armx -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Boyd 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: insert in batch loading
How big are yor redo logs? How many o them do you have? On 11/25/2003 10:29:37 AM, David Boyd wrote: Hi All, We have some batch loading jobs that truncate the tables first, then insert into the tables as select from tables through database link. Those jobs run daily. Every time when those jobs run, they cause cannot allocate new log, Checkpoint not complete. All of tables and their indexes are in nologging mode. We have /*+append*/ hint in the insert statement. We have 5 redo groups with member of 100 MB. Some tables have more than 1 million records. I was wondering if any body knows a method that forces a commit after every 1000 records inserted, which is like delete_commit procedure. David _ Groove on the latest from the hot new rock groups! Get downloads, videos, and more here. http://special.msn.com/entertainment/wiredformusic.armx -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Boyd 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). Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: insert in batch loading
David Boyd wrote: Hi All, We have some batch loading jobs that truncate the tables first, then insert into the tables as select from tables through database link. Those jobs run daily. Every time when those jobs run, they cause cannot allocate new log, Checkpoint not complete. All of tables and their indexes are in nologging mode. We have /*+append*/ hint in the insert statement. We have 5 redo groups with member of 100 MB. Some tables have more than 1 million records. I was wondering if any body knows a method that forces a commit after every 1000 records inserted, which is like delete_commit procedure. David _ Groove on the latest from the hot new rock groups! Get downloads, videos, and more here. http://special.msn.com/entertainment/wiredformusic.armx Hi! If you do incremental commits the batch will run slower or not at all (ora -01555). Have you checked how much redo your insert statement generates? You might have missed something. If you have indexes on the table you will have to set them unusable and alter session set skip_unusable_indexes=true Rebuild the indexes after the load with nologging. Janne! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jan Korecki 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: insert in batch loading
We have 5 groups of redo log. Each group has two members. Each member is 100 MB. David From: Mladen Gogala [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: insert in batch loading Date: Tue, 25 Nov 2003 07:44:33 -0800 How big are yor redo logs? How many o them do you have? On 11/25/2003 10:29:37 AM, David Boyd wrote: Hi All, We have some batch loading jobs that truncate the tables first, then insert into the tables as select from tables through database link. Those jobs run daily. Every time when those jobs run, they cause cannot allocate new log, Checkpoint not complete. All of tables and their indexes are in nologging mode. We have /*+append*/ hint in the insert statement. We have 5 redo groups with member of 100 MB. Some tables have more than 1 million records. I was wondering if any body knows a method that forces a commit after every 1000 records inserted, which is like delete_commit procedure. David _ Groove on the latest from the hot new rock groups! Get downloads, videos, and more here. http://special.msn.com/entertainment/wiredformusic.armx -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Boyd 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). Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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). _ Need a shot of Hank Williams or Patsy Cline? The classic country stars are always singing on MSN Radio Plus. Try one month free! http://join.msn.com/?page=offers/premiumradio -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Boyd 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: insert in batch loading
Janne, Thanks for your reply. We have 6 redo log switchings during inserting a table that has 1 million records. Our redo log size (100 MB) dominates the checkpoint frequency. The table has two indexes. We don't set them to unusable during inserting. David From: Jan Korecki [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: insert in batch loading Date: Tue, 25 Nov 2003 08:19:26 -0800 David Boyd wrote: Hi All, We have some batch loading jobs that truncate the tables first, then insert into the tables as select from tables through database link. Those jobs run daily. Every time when those jobs run, they cause cannot allocate new log, Checkpoint not complete. All of tables and their indexes are in nologging mode. We have /*+append*/ hint in the insert statement. We have 5 redo groups with member of 100 MB. Some tables have more than 1 million records. I was wondering if any body knows a method that forces a commit after every 1000 records inserted, which is like delete_commit procedure. David _ Groove on the latest from the hot new rock groups! Get downloads, videos, and more here. http://special.msn.com/entertainment/wiredformusic.armx Hi! If you do incremental commits the batch will run slower or not at all (ora -01555). Have you checked how much redo your insert statement generates? You might have missed something. If you have indexes on the table you will have to set them unusable and alter session set skip_unusable_indexes=true Rebuild the indexes after the load with nologging. Janne! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jan Korecki 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). _ online games and music with a high-speed Internet connection! Prices start at less than $1 a day average. https://broadband.msn.com (Prices may vary by service area.) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Boyd 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: insert in batch loading
What does it mean that redo log size dominates checkpoint frequency? It brings to mind cases of Edmund Blackadder and Baldrick or Ace Rimmer and Lister Kryten, both very clear cases of domination. I'm sure that J. Lewis, as a Britt, can tell you more about Black Adder and Red Dwarf, despite the fact that he missed those two subjects in his book. Redo logs of 100M are not really large. You, basically, have two choices: a) Increase redo logs to decent size (500M) b) Disable indexes during load. The third choice (out of two) would be to use _disable_logging parameter, recently mentioned by Mr. Anjo Kolk. Now, that would be practical, don't you think? On 11/25/2003 11:59:26 AM, David Boyd wrote: Janne, Thanks for your reply. We have 6 redo log switchings during inserting a table that has 1 million records. Our redo log size (100 MB) dominates the checkpoint frequency. The table has two indexes. We don't set them to unusable during inserting. David From: Jan Korecki [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: insert in batch loading Date: Tue, 25 Nov 2003 08:19:26 -0800 David Boyd wrote: Hi All, We have some batch loading jobs that truncate the tables first, then insert into the tables as select from tables through database link. Those jobs run daily. Every time when those jobs run, they cause cannot allocate new log, Checkpoint not complete. All of tables and their indexes are in nologging mode. We have /*+append*/ hint in the insert statement. We have 5 redo groups with member of 100 MB. Some tables have more than 1 million records. I was wondering if any body knows a method that forces a commit after every 1000 records inserted, which is like delete_commit procedure. David _ Groove on the latest from the hot new rock groups! Get downloads, videos, and more here. http://special.msn.com/entertainment/wiredformusic.armx Hi! If you do incremental commits the batch will run slower or not at all (ora -01555). Have you checked how much redo your insert statement generates? You might have missed something. If you have indexes on the table you will have to set them unusable and alter session set skip_unusable_indexes=true Rebuild the indexes after the load with nologging. Janne! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jan Korecki 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). _ online games and music with a high-speed Internet connection! Prices start at less than $1 a day average. https://broadband.msn.com (Prices may vary by service area.) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Boyd 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). Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL
Re: insert in batch loading
What I meant is that checkpoint occurs only when redo log switches. We set log_checkpoint_interval = 21 and our OS block size is 512. If I increase the size of redo log to 500 MB, checkpoint will occur before log switching. Will the transaction wait until checkpoint complete during checkpoint? Thanks for your inputs. David From: Mladen Gogala [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: insert in batch loading Date: Tue, 25 Nov 2003 12:49:28 -0800 What does it mean that redo log size dominates checkpoint frequency? It brings to mind cases of Edmund Blackadder and Baldrick or Ace Rimmer and Lister Kryten, both very clear cases of domination. I'm sure that J. Lewis, as a Britt, can tell you more about Black Adder and Red Dwarf, despite the fact that he missed those two subjects in his book. Redo logs of 100M are not really large. You, basically, have two choices: a) Increase redo logs to decent size (500M) b) Disable indexes during load. The third choice (out of two) would be to use _disable_logging parameter, recently mentioned by Mr. Anjo Kolk. Now, that would be practical, don't you think? On 11/25/2003 11:59:26 AM, David Boyd wrote: Janne, Thanks for your reply. We have 6 redo log switchings during inserting a table that has 1 million records. Our redo log size (100 MB) dominates the checkpoint frequency. The table has two indexes. We don't set them to unusable during inserting. David From: Jan Korecki [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: insert in batch loading Date: Tue, 25 Nov 2003 08:19:26 -0800 David Boyd wrote: Hi All, We have some batch loading jobs that truncate the tables first, then insert into the tables as select from tables through database link. Those jobs run daily. Every time when those jobs run, they cause cannot allocate new log, Checkpoint not complete. All of tables and their indexes are in nologging mode. We have /*+append*/ hint in the insert statement. We have 5 redo groups with member of 100 MB. Some tables have more than 1 million records. I was wondering if any body knows a method that forces a commit after every 1000 records inserted, which is like delete_commit procedure. David _ Groove on the latest from the hot new rock groups! Get downloads, videos, and more here. http://special.msn.com/entertainment/wiredformusic.armx Hi! If you do incremental commits the batch will run slower or not at all (ora -01555). Have you checked how much redo your insert statement generates? You might have missed something. If you have indexes on the table you will have to set them unusable and alter session set skip_unusable_indexes=true Rebuild the indexes after the load with nologging. Janne! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jan Korecki 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). _ online games and music with a high-speed Internet connection! Prices start at less than $1 a day average. https://broadband.msn.com (Prices may vary by service area.) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Boyd 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). Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose
Re: insert in batch loading
Hi! I suggest you set up a test where you check the redo. For example: - start test SQL select LOG_MODE from v$database; LOG_MODE ARCHIVELOG SQL create table append_test as select * from all_objects where 1=2; Table created. SQL create index append_test_ind on append_test(owner); Index created. SQL alter table append_test nologging; Table altered. SQL alter index append_test_ind nologging; Index altered. SQL analyze table append_test compute statistics for table for all indexes for all indexed columns; Table analyzed. SQL set serveroutput on size 10 SQL set autotrace on STATISTICS SQL insert /*+ append */ into append_test select * from [EMAIL PROTECTED]; 35605 rows created. Statistics -- 1213732 redo size 35605 rows processed SQL rollback; Rollback complete. SQL alter index append_test_ind unusable; Index altered. SQL alter session set skip_unusable_indexes=TRUE; Session altered. SQL insert /*+ append */ into append_test select * from [EMAIL PROTECTED]; 35605 rows created. Statistics -- 1172 redo size 35605 rows processed end test This works well with a non-unique index. If the index is unique or you have primary key/unique constraint you will run into problem because you cannot use +append with a unusable unique index. You have 2 choices. 1) have the constraints set to deferreble and disble them before load, set the unique index to unusable, load, rebuild index nologging, enable constraints 2) if you dont want to have the constraints deferrable- drop indexed before load and create them afterwards with nologging. Even if you do 2) you will save a lot of time and have a lot less redo. Regards, Janne! David Boyd wrote: Janne, Thanks for your reply. We have 6 redo log switchings during inserting a table that has 1 million records. Our redo log size (100 MB) dominates the checkpoint frequency. The table has two indexes. We don't set them to unusable during inserting. David From: Jan Korecki [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: insert in batch loading Date: Tue, 25 Nov 2003 08:19:26 -0800 David Boyd wrote: Hi All, We have some batch loading jobs that truncate the tables first, then insert into the tables as select from tables through database link. Those jobs run daily. Every time when those jobs run, they cause cannot allocate new log, Checkpoint not complete. All of tables and their indexes are in nologging mode. We have /*+append*/ hint in the insert statement. We have 5 redo groups with member of 100 MB. Some tables have more than 1 million records. I was wondering if any body knows a method that forces a commit after every 1000 records inserted, which is like delete_commit procedure. David _ Groove on the latest from the hot new rock groups! Get downloads, videos, and more here. http://special.msn.com/entertainment/wiredformusic.armx Hi! If you do incremental commits the batch will run slower or not at all (ora -01555). Have you checked how much redo your insert statement generates? You might have missed something. If you have indexes on the table you will have to set them unusable and alter session set skip_unusable_indexes=true Rebuild the indexes after the load with nologging. Janne! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jan Korecki 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). _ online games and music with a high-speed Internet connection! Prices start at less than $1 a day average. https://broadband.msn.com (Prices may vary by service area.) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jan Korecki 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
insert/read Blob from a table
Hi , Does anyone have a sample code to insert/update/delete a BLOB into a table from a stored procedure. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra 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: insert and commit 1000 records at a time
I still dont get it...
I dont know what I have done to have me confused more
than I first asked the question...
--- [EMAIL PROTECTED] wrote:
My bad. The SQL is not quite right: 'append' is a hint:
alter table resource nologging;
dont know what nologging does.
insert /*+ append */ into resource
select * from rqmt;
me no understand... me no see 1000 anywhere...
Read up on direct load insert in the concepts manual,
along with nologging.
And one more thing... If I asked the question, thats just it,
I asked a question. If anyone knows the answer, please offer it here,
but dont tell me to go read it up in the national enquirer or
I-dont-know-where-you-mean...
Folks, please, if we have something to offer, lets go ahead,
if not, just bypass that email and read another one...
thanks,
maa
Bypass the redo and undo - no need for commits.
Just back it up when finished.
Jared
MaryAnn Atkinson [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
10/16/2003 01:49 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:Re: insert and commit 1000 records at a time
--- [EMAIL PROTECTED] wrote:
That will work, slowly.
You might like to try something like this
insert into resource
nologging
select * from rqmt
append;
How's that commiting every 1000 records?
Read up on the 'append' and 'nologging' first.
???
Maryann Atkinson [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
10/16/2003 08:54 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:insert and commit 1000 records at a time
I have 2 tables, Rqmt and Resource, same structure.
I need to take all almost-one-million records from Rqmt and
insert them to Resource. So far this worked ok:
DECLARE
RowCount NUMBER:= 0;
BEGIN
SELECT Count(*)
INTO RowCount
FROM RQMT;
IF RowCount 0 THEN
INSERT INTO RESOURCE
SELECT Resource_Id, Classification
FROM RQMT;
RowCount := SQL%RowCount;
DBMS_OUTPUT.PUT_LINE ('TABLE Resource: ' || RowCount || '
Rows
transitioned.');
COMMIT;
ELSE
DBMS_OUTPUT.PUT_LINE ('TABLE Resource is empty. No data
transitioned.');
END IF;
EXCEPTION
WHEN OTHERS THEN
Raise;
END;
/
But now I need to commit every 1000 records. Any suggestions as to
what would be the best way? I dont think ROWNUM would help here,
because it would pick the same 1000 records every time, causing
primary key violation...
thx
maa
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Maryann Atkinson
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).
Please see the official ORACLE-L FAQ: http://www.orafaq.net
Author: MaryAnn Atkinson
INET: [EMAIL PROTECTED]
__
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: MaryAnn Atkinson
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: insert and commit 1000 records at a time
Nologging suspends redo log entries generation FOR DIRECT OPERATIONS ONLY! Direct operations do not use SQL, they use Lisp (just kidding). Direct insert preformats blocks and appends blocks as a whole after the highwater mark, without using SQL in the process. On 10/17/2003 02:43:29 PM, MaryAnn Atkinson wrote: I still dont get it... I dont know what I have done to have me confused more than I first asked the question... It's not you, it's the weather. Everything you need can be found here: http://otn.oracle.com/pls/db92/db92.docindex?remark=homepage Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: insert and commit 1000 records at a time
MaryAnn,
First of all, Wow! Is your reply to this group ever rude.
Several people have offered you very clear and very good suggestions.
And really, you SHOULD be reading the Oracle manuals. Otherwise, why
did you take a job working with Oracle technology?
I don't think the problem is with the answers you've received, but in
your disinterest in listening to them.
Melanie
-Original Message-
MaryAnn Atkinson
Sent: Friday, October 17, 2003 2:43 PM
To: Multiple recipients of list ORACLE-L
I still dont get it...
I dont know what I have done to have me confused more
than I first asked the question...
--- [EMAIL PROTECTED] wrote:
My bad. The SQL is not quite right: 'append' is a hint:
alter table resource nologging;
dont know what nologging does.
insert /*+ append */ into resource
select * from rqmt;
me no understand... me no see 1000 anywhere...
Read up on direct load insert in the concepts manual,
along with nologging.
And one more thing... If I asked the question, thats just it,
I asked a question. If anyone knows the answer, please offer it here,
but dont tell me to go read it up in the national enquirer or
I-dont-know-where-you-mean...
Folks, please, if we have something to offer, lets go ahead,
if not, just bypass that email and read another one...
thanks,
maa
Bypass the redo and undo - no need for commits.
Just back it up when finished.
Jared
MaryAnn Atkinson [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
10/16/2003 01:49 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:Re: insert and commit 1000 records at a time
--- [EMAIL PROTECTED] wrote:
That will work, slowly.
You might like to try something like this
insert into resource
nologging
select * from rqmt
append;
How's that commiting every 1000 records?
Read up on the 'append' and 'nologging' first.
???
Maryann Atkinson [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
10/16/2003 08:54 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:insert and commit 1000 records at a time
I have 2 tables, Rqmt and Resource, same structure.
I need to take all almost-one-million records from Rqmt and
insert them to Resource. So far this worked ok:
DECLARE
RowCount NUMBER:= 0;
BEGIN
SELECT Count(*)
INTO RowCount
FROM RQMT;
IF RowCount 0 THEN
INSERT INTO RESOURCE
SELECT Resource_Id, Classification
FROM RQMT;
RowCount := SQL%RowCount;
DBMS_OUTPUT.PUT_LINE ('TABLE Resource: ' || RowCount || '
Rows
transitioned.');
COMMIT;
ELSE
DBMS_OUTPUT.PUT_LINE ('TABLE Resource is empty. No data
transitioned.');
END IF;
EXCEPTION
WHEN OTHERS THEN
Raise;
END;
/
But now I need to commit every 1000 records. Any suggestions as to
what would be the best way? I dont think ROWNUM would help here,
because it would pick the same 1000 records every time, causing
primary key violation...
thx
maa
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Maryann Atkinson
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).
Please see the official ORACLE-L FAQ: http://www.orafaq.net
Author: MaryAnn Atkinson
INET: [EMAIL PROTECTED]
__
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: MaryAnn Atkinson
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).
--
Please see the official ORACLE-L FAQ
Re: insert and commit 1000 records at a time
MaryAnn, To get back to the original question, use a MOD function on rownum to get your commit point. Check the docs to make sure, but try If mod(rownum, 1000) = 0 then commit; end if; Daniel Fink MaryAnn Atkinson wrote: I still dont get it... I dont know what I have done to have me confused more than I first asked the question... -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel Fink 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: insert and commit 1000 records at a time
1. Define a variable to count inserted rows.
insert_count number := 0;
2. Increment it after inserting the row
insert_count:=insert_count + 1;
3. Check if insert_count = 1000
then commit and reset counter to zero insert_count :=0;
4. At the end when no rows found, and insert_count 0 then commit.
I think you get the idea
- Kirti
--- MaryAnn Atkinson [EMAIL PROTECTED] wrote:
I still dont get it...
I dont know what I have done to have me confused more
than I first asked the question...
--- [EMAIL PROTECTED] wrote:
My bad. The SQL is not quite right: 'append' is a hint:
alter table resource nologging;
dont know what nologging does.
insert /*+ append */ into resource
select * from rqmt;
me no understand... me no see 1000 anywhere...
Read up on direct load insert in the concepts manual,
along with nologging.
And one more thing... If I asked the question, thats just it,
I asked a question. If anyone knows the answer, please offer it here,
but dont tell me to go read it up in the national enquirer or
I-dont-know-where-you-mean...
Folks, please, if we have something to offer, lets go ahead,
if not, just bypass that email and read another one...
thanks,
maa
Bypass the redo and undo - no need for commits.
Just back it up when finished.
Jared
MaryAnn Atkinson [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
10/16/2003 01:49 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:Re: insert and commit 1000 records at a time
--- [EMAIL PROTECTED] wrote:
That will work, slowly.
You might like to try something like this
insert into resource
nologging
select * from rqmt
append;
How's that commiting every 1000 records?
Read up on the 'append' and 'nologging' first.
???
Maryann Atkinson [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
10/16/2003 08:54 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:insert and commit 1000 records at a time
I have 2 tables, Rqmt and Resource, same structure.
I need to take all almost-one-million records from Rqmt and
insert them to Resource. So far this worked ok:
DECLARE
RowCount NUMBER:= 0;
BEGIN
SELECT Count(*)
INTO RowCount
FROM RQMT;
IF RowCount 0 THEN
INSERT INTO RESOURCE
SELECT Resource_Id, Classification
FROM RQMT;
RowCount := SQL%RowCount;
DBMS_OUTPUT.PUT_LINE ('TABLE Resource: ' || RowCount || '
Rows
transitioned.');
COMMIT;
ELSE
DBMS_OUTPUT.PUT_LINE ('TABLE Resource is empty. No data
transitioned.');
END IF;
EXCEPTION
WHEN OTHERS THEN
Raise;
END;
/
But now I need to commit every 1000 records. Any suggestions as to
what would be the best way? I dont think ROWNUM would help here,
because it would pick the same 1000 records every time, causing
primary key violation...
thx
maa
__
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Kirtikumar Deshpande
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: insert and commit 1000 records at a time
Assuming that the resource_id column is the PK (or at least unique) of the
RQMT table something like this should work (albeit slowly). Its missing some
definitions, but this should give you an idea on how you _might_ proceed
Kevin
DECLARE
RowCount NUMBER:= 0;
MAXNUMBER:= -;
CURSOR c_src (id IN NUMBER) IS
SELECT *
FROM (SELECT resource_id, classification
FROM rqmt
WHERE resource_id id
ORDER BY resource_id
)
WHERE rownum = 1000;
BEGIN
SELECT Count(*)
INTO RowCount
FROM RQMT;
IF RowCount 0 THEN
LOOP
OPEN c_src(max);
FETCH c_src BULK COLLECT INTO tbl_resource_id,
tbl_classification;
IF (c_src%ROWCOUNT = 0) THEN
EXIT LOOP;
END IF;
Max = tbl_resource_id(tbl_resource_id.MAX)
FORALL j IN tbl_resource_id.FIRST .. tbl_resource_id.LAST
INSERT INTO RESOURCE VALUES
tbl_resource_id(j), tbl_classification(j);
DBMS_OUTPUT.PUT_LINE ('TABLE Resource: ' || sql%Rowcount || '
Rows transitioned.');
COMMIT;
CLOSE c_src;
ELSE
DBMS_OUTPUT.PUT_LINE ('TABLE Resource is empty. No data
transitioned.');
END IF;
EXCEPTION
WHEN OTHERS THEN
Raise;
END;
/
-Original Message-
Sent: Thursday, October 16, 2003 11:55 AM
To: Multiple recipients of list ORACLE-L
I have 2 tables, Rqmt and Resource, same structure.
I need to take all almost-one-million records from Rqmt and
insert them to Resource. So far this worked ok:
DECLARE
RowCount NUMBER:= 0;
BEGIN
SELECT Count(*)
INTO RowCount
FROM RQMT;
IF RowCount 0 THEN
INSERT INTO RESOURCE
SELECT Resource_Id, Classification
FROM RQMT;
RowCount := SQL%RowCount;
DBMS_OUTPUT.PUT_LINE ('TABLE Resource: ' || RowCount || ' Rows
transitioned.');
COMMIT;
ELSE
DBMS_OUTPUT.PUT_LINE ('TABLE Resource is empty. No data
transitioned.');
END IF;
EXCEPTION
WHEN OTHERS THEN
Raise;
END;
/
But now I need to commit every 1000 records. Any suggestions as to
what would be the best way? I dont think ROWNUM would help here,
because it would pick the same 1000 records every time, causing
primary key violation...
thx
maa
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Maryann Atkinson
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Kevin Toepke
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: insert and commit 1000 records at a time
At 04:44 PM 10/17/2003, you wrote: Got it, thanks to both of you, I really appreciate it. maa MaryAnn, To get back to the original question, use a MOD function on rownum to get your commit point. Check the docs to make sure, but try If mod(rownum, 1000) = 0 then commit; end if; Daniel Fink - Organization: Fat City Network Services, San Diego, California Precedence: bulk 1. Define a variable to count inserted rows. insert_count number := 0; 2. Increment it after inserting the row insert_count:=insert_count + 1; 3. Check if insert_count = 1000 then commit and reset counter to zero insert_count :=0; 4. At the end when no rows found, and insert_count 0 then commit. I think you get the idea - Kirti MaryAnn Atkinson wrote: I still dont get it... I dont know what I have done to have me confused more than I first asked the question... -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel Fink 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Maryann Atkinson 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).
insert and commit 1000 records at a time
I have 2 tables, Rqmt and Resource, same structure.
I need to take all almost-one-million records from Rqmt and
insert them to Resource. So far this worked ok:
DECLARE
RowCount NUMBER:= 0;
BEGIN
SELECT Count(*)
INTO RowCount
FROM RQMT;
IF RowCount 0 THEN
INSERT INTO RESOURCE
SELECT Resource_Id, Classification
FROM RQMT;
RowCount := SQL%RowCount;
DBMS_OUTPUT.PUT_LINE ('TABLE Resource: ' || RowCount || ' Rows
transitioned.');
COMMIT;
ELSE
DBMS_OUTPUT.PUT_LINE ('TABLE Resource is empty. No data
transitioned.');
END IF;
EXCEPTION
WHEN OTHERS THEN
Raise;
END;
/
But now I need to commit every 1000 records. Any suggestions as to
what would be the best way? I dont think ROWNUM would help here,
because it would pick the same 1000 records every time, causing
primary key violation...
thx
maa
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Maryann Atkinson
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: insert and commit 1000 records at a time
yeah dont commit every 1000 records and do it in one shot. this is going to be much
slower.
why do you want to do it this way? Ive done 100m inserts with just an insert select
and one commit.
From: Maryann Atkinson [EMAIL PROTECTED]
Date: 2003/10/16 Thu AM 11:54:33 EDT
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: insert and commit 1000 records at a time
I have 2 tables, Rqmt and Resource, same structure.
I need to take all almost-one-million records from Rqmt and
insert them to Resource. So far this worked ok:
DECLARE
RowCount NUMBER:= 0;
BEGIN
SELECT Count(*)
INTO RowCount
FROM RQMT;
IF RowCount 0 THEN
INSERT INTO RESOURCE
SELECT Resource_Id, Classification
FROM RQMT;
RowCount := SQL%RowCount;
DBMS_OUTPUT.PUT_LINE ('TABLE Resource: ' || RowCount || ' Rows
transitioned.');
COMMIT;
ELSE
DBMS_OUTPUT.PUT_LINE ('TABLE Resource is empty. No data
transitioned.');
END IF;
EXCEPTION
WHEN OTHERS THEN
Raise;
END;
/
But now I need to commit every 1000 records. Any suggestions as to
what would be the best way? I dont think ROWNUM would help here,
because it would pick the same 1000 records every time, causing
primary key violation...
thx
maa
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Maryann Atkinson
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: [EMAIL PROTECTED]
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: insert and commit 1000 records at a time
I believe that this would be the best solution:
DECLARE
RowCount NUMBER:= 0;
BEGIN
/* This will work if the RESOURCE table has the parallel
attribute set. In 8i, table needs to be partitioned as well */
EXECUTE IMMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
SELECT Count(*)
INTO RowCount
FROM RQMT;
IF RowCount 0 THEN
INSERT /*+ APPEND */ INTO RESOURCE
SELECT Resource_Id, Classification
FROM RQMT;
RowCount := SQL%RowCount;
DBMS_OUTPUT.PUT_LINE ('TABLE Resource: ' || RowCount || '
Rows
transitioned.');
COMMIT;
ELSE
DBMS_OUTPUT.PUT_LINE ('TABLE Resource is empty. No data
transitioned.');
END IF;
EXCEPTION
WHEN OTHERS THEN
Raise NOT_LOGGED_ON;
END;
/
On 10/16/2003 01:29:33 PM, [EMAIL PROTECTED] wrote:
yeah dont commit every 1000 records and do it in one shot. this is
going to be much slower.
why do you want to do it this way? Ive done 100m inserts with just an
insert select and one commit.
From: Maryann Atkinson [EMAIL PROTECTED]
Date: 2003/10/16 Thu AM 11:54:33 EDT
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: insert and commit 1000 records at a time
I have 2 tables, Rqmt and Resource, same structure.
I need to take all almost-one-million records from Rqmt and
insert them to Resource. So far this worked ok:
DECLARE
RowCount NUMBER:= 0;
BEGIN
SELECT Count(*)
INTO RowCount
FROM RQMT;
IF RowCount 0 THEN
INSERT INTO RESOURCE
SELECT Resource_Id, Classification
FROM RQMT;
RowCount := SQL%RowCount;
DBMS_OUTPUT.PUT_LINE ('TABLE Resource: ' || RowCount || '
Rows
transitioned.');
COMMIT;
ELSE
DBMS_OUTPUT.PUT_LINE ('TABLE Resource is empty. No data
transitioned.');
END IF;
EXCEPTION
WHEN OTHERS THEN
Raise;
END;
/
But now I need to commit every 1000 records. Any suggestions as to
what would be the best way? I dont think ROWNUM would help here,
because it would pick the same 1000 records every time, causing
primary key violation...
thx
maa
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Maryann Atkinson
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: [EMAIL PROTECTED]
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).
Mladen Gogala
Oracle DBA
Note:
This message is for the named person's use only. It may contain confidential,
proprietary or legally privileged information. No confidentiality or privilege is
waived or lost by any mistransmission. If you receive this message in error, please
immediately delete it and all copies of it from your system, destroy any hard copies
of it and notify the sender. You must not, directly or indirectly, use, disclose,
distribute, print, or copy any part of this message if you are not the intended
recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to
monitor all e-mail communications through its networks.
Any views expressed in this message are those of the individual sender, except where
the message states otherwise and the sender is authorized to state them to be the
views of any such entity.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mladen Gogala
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
Re: insert and commit 1000 records at a time
That will work, slowly.
You might like to try something like this
insert into resource
nologging
select * from rqmt
append;
Read up on the 'append' and 'nologging' first.
Jared
Maryann Atkinson [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
10/16/2003 08:54 AM
Please respond to ORACLE-L
To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:insert and commit 1000 records at a time
I have 2 tables, Rqmt and Resource, same structure.
I need to take all almost-one-million records from Rqmt and
insert them to Resource. So far this worked ok:
DECLARE
RowCountNUMBER:= 0;
BEGIN
SELECT Count(*)
INTO RowCount
FROM RQMT;
IF RowCount 0 THEN
INSERT INTO RESOURCE
SELECT Resource_Id, Classification
FROM RQMT;
RowCount := SQL%RowCount;
DBMS_OUTPUT.PUT_LINE ('TABLE Resource: ' || RowCount || ' Rows
transitioned.');
COMMIT;
ELSE
DBMS_OUTPUT.PUT_LINE ('TABLE Resource is empty. No data
transitioned.');
END IF;
EXCEPTION
WHEN OTHERS THEN
Raise;
END;
/
But now I need to commit every 1000 records. Any suggestions as to
what would be the best way? I dont think ROWNUM would help here,
because it would pick the same 1000 records every time, causing
primary key violation...
thx
maa
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Maryann Atkinson
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: insert and commit 1000 records at a time
--- [EMAIL PROTECTED] wrote:
That will work, slowly.
You might like to try something like this
insert into resource
nologging
select * from rqmt
append;
How's that commiting every 1000 records?
Read up on the 'append' and 'nologging' first.
???
Maryann Atkinson [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
10/16/2003 08:54 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:insert and commit 1000 records at a time
I have 2 tables, Rqmt and Resource, same structure.
I need to take all almost-one-million records from Rqmt and
insert them to Resource. So far this worked ok:
DECLARE
RowCount NUMBER:= 0;
BEGIN
SELECT Count(*)
INTO RowCount
FROM RQMT;
IF RowCount 0 THEN
INSERT INTO RESOURCE
SELECT Resource_Id, Classification
FROM RQMT;
RowCount := SQL%RowCount;
DBMS_OUTPUT.PUT_LINE ('TABLE Resource: ' || RowCount || '
Rows
transitioned.');
COMMIT;
ELSE
DBMS_OUTPUT.PUT_LINE ('TABLE Resource is empty. No data
transitioned.');
END IF;
EXCEPTION
WHEN OTHERS THEN
Raise;
END;
/
But now I need to commit every 1000 records. Any suggestions as to
what would be the best way? I dont think ROWNUM would help here,
because it would pick the same 1000 records every time, causing
primary key violation...
thx
maa
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Maryann Atkinson
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).
__
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: MaryAnn Atkinson
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: insert and commit 1000 records at a time
log into target database.
SQL set long 32000 (or whatever if you have long datatype involved)
SQL set arraysize 100
SQL set copycommit 1000 -- LOOKY!!
SQL COPY FROM ${REMOTE_LOGIN}/[EMAIL PROTECTED] INSERT
${LOCAL_SCHEMA}.${THE_TABLE} USING ${QUERY};
In this case QUERY will probably be select * from
schema_owner.that_other_table. This uses sqlnet, NOT a database link. So
the tnsnames.ora that your TNS_ADMIN points to must have an entry for the
remote database.
-Original Message-
I have 2 tables, Rqmt and Resource, same structure.
I need to take all almost-one-million records from Rqmt and
insert them to Resource. So far this worked ok:
DECLARE
RowCount NUMBER:= 0;
BEGIN
SELECT Count(*)
INTO RowCount
FROM RQMT;
IF RowCount 0 THEN
INSERT INTO RESOURCE
SELECT Resource_Id, Classification
FROM RQMT;
RowCount := SQL%RowCount;
DBMS_OUTPUT.PUT_LINE ('TABLE Resource: ' ||
RowCount || ' Rows
transitioned.');
COMMIT;
ELSE
DBMS_OUTPUT.PUT_LINE ('TABLE Resource is empty. No data
transitioned.');
END IF;
EXCEPTION
WHEN OTHERS THEN
Raise;
END;
/
But now I need to commit every 1000 records. Any suggestions as to
what would be the best way? I dont think ROWNUM would help here,
because it would pick the same 1000 records every time, causing
primary key violation...
thx
maa
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Maryann Atkinson
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephen Lee
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: insert and commit 1000 records at a time
My bad. The SQL is not quite right: 'append' is a hint:
alter table resource nologging;
insert /*+ append */ into resource
select * from rqmt;
Read up on direct load insert in the concepts manual,
along with nologging.
Bypass the redo and undo - no need for commits.
Just back it up when finished.
Jared
MaryAnn Atkinson [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
10/16/2003 01:49 PM
Please respond to ORACLE-L
To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:Re: insert and commit 1000 records at a time
--- [EMAIL PROTECTED] wrote:
That will work, slowly.
You might like to try something like this
insert into resource
nologging
select * from rqmt
append;
How's that commiting every 1000 records?
Read up on the 'append' and 'nologging' first.
???
Maryann Atkinson [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
10/16/2003 08:54 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:insert and commit 1000 records at a time
I have 2 tables, Rqmt and Resource, same structure.
I need to take all almost-one-million records from Rqmt and
insert them to Resource. So far this worked ok:
DECLARE
RowCountNUMBER:= 0;
BEGIN
SELECT Count(*)
INTO RowCount
FROM RQMT;
IF RowCount 0 THEN
INSERT INTO RESOURCE
SELECT Resource_Id, Classification
FROM RQMT;
RowCount := SQL%RowCount;
DBMS_OUTPUT.PUT_LINE ('TABLE Resource: ' || RowCount || '
Rows
transitioned.');
COMMIT;
ELSE
DBMS_OUTPUT.PUT_LINE ('TABLE Resource is empty. No data
transitioned.');
END IF;
EXCEPTION
WHEN OTHERS THEN
Raise;
END;
/
But now I need to commit every 1000 records. Any suggestions as to
what would be the best way? I dont think ROWNUM would help here,
because it would pick the same 1000 records every time, causing
primary key violation...
thx
maa
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Maryann Atkinson
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).
__
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: MaryAnn Atkinson
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: insert and commit 1000 records at a time
Jared's suggestion improves performance but does not implement batch
commits. I think most people are trying to steer you away from batch
commits - for a good reason. Often people say you have to commit every
1000 records - where I work that exists as a standard even. I've fought
long and hard to explain why this rule doesn't make sense. One approach is
to consider the amount of rollback - if the row length is (perhaps) 100
bytes then a 1000 row insert is only looking to use about 10k of
Rollback... How large is the rollback on your system.
Having said that, it's time to answer your question... You can create a
cursor in the declare section and then use some kind of loop (while, for,
etc) and then include a counter to determine when to commit. The most
simple approach (and slowest) would be to insert a single record each
iteration, include an if statement to check for commit points, and then
exit the loop when the cursor is finished and issue a final commit.
I do tend to agree with the others though - unless someone has an
exceptionally good reason to commit every 1000 records you need to show
them the light and explain why an enterprise ready database like Oracle can
easily chew a large amount of data. I've just finished scripts to convert
some 150,000,000 row tables and commiting every 1000 records would never
have worked. I compromised and settled on 20,000,000 records per commit
since the conversion will occur whilst the system is still live and
processing a lot of data.
MaryAnn Atkinson
[EMAIL PROTECTED]To: Multiple recipients of list
ORACLE-L [EMAIL PROTECTED]
.comcc:
Sent by: Subject: Re: insert and commit 1000
records at a time
[EMAIL PROTECTED]
.com
17/10/2003 06:49
Please respond to
ORACLE-L
--- [EMAIL PROTECTED] wrote:
That will work, slowly.
You might like to try something like this
insert into resource
nologging
select * from rqmt
append;
How's that commiting every 1000 records?
Read up on the 'append' and 'nologging' first.
???
Maryann Atkinson [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
10/16/2003 08:54 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:insert and commit 1000 records at a time
I have 2 tables, Rqmt and Resource, same structure.
I need to take all almost-one-million records from Rqmt and
insert them to Resource. So far this worked ok:
DECLARE
RowCount NUMBER:= 0;
BEGIN
SELECT Count(*)
INTO RowCount
FROM RQMT;
IF RowCount 0 THEN
INSERT INTO RESOURCE
SELECT Resource_Id, Classification
FROM RQMT;
RowCount := SQL%RowCount;
DBMS_OUTPUT.PUT_LINE ('TABLE Resource: ' || RowCount || '
Rows
transitioned.');
COMMIT;
ELSE
DBMS_OUTPUT.PUT_LINE ('TABLE Resource is empty. No data
transitioned.');
END IF;
EXCEPTION
WHEN OTHERS THEN
Raise;
END;
/
But now I need to commit every 1000 records. Any suggestions as to
what would be the best way? I dont think ROWNUM would help here,
because it would pick the same 1000 records every time, causing
primary key violation...
thx
maa
--
Please see the official ORACLE-L FAQ: http
Re: insert and commit 1000 records at a time
Mladen Gogala wrote:
I believe that this would be the best solution:
DECLARE
RowCount NUMBER:= 0;
BEGIN
/* This will work if the RESOURCE table has the parallel
attribute set. In 8i, table needs to be partitioned as well */
EXECUTE IMMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
SELECT Count(*)
INTO RowCount
FROM RQMT;
IF RowCount 0 THEN
INSERT /*+ APPEND */ INTO RESOURCE
SELECT Resource_Id, Classification
FROM RQMT;
RowCount := SQL%RowCount;
DBMS_OUTPUT.PUT_LINE ('TABLE Resource: ' || RowCount || '
Rows
transitioned.');
COMMIT;
ELSE
DBMS_OUTPUT.PUT_LINE ('TABLE Resource is empty. No data
transitioned.');
END IF;
EXCEPTION
WHEN OTHERS THEN
Raise NOT_LOGGED_ON;
END;
/
On 10/16/2003 01:29:33 PM, [EMAIL PROTECTED] wrote:
yeah dont commit every 1000 records and do it in one shot. this is
going to be much slower.
why do you want to do it this way? Ive done 100m inserts with just an
insert select and one commit.
From: Maryann Atkinson [EMAIL PROTECTED]
Date: 2003/10/16 Thu AM 11:54:33 EDT
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: insert and commit 1000 records at a time
I have 2 tables, Rqmt and Resource, same structure.
I need to take all almost-one-million records from Rqmt and
insert them to Resource. So far this worked ok:
DECLARE
RowCount NUMBER:= 0;
BEGIN
SELECT Count(*)
INTO RowCount
FROM RQMT;
IF RowCount 0 THEN
INSERT INTO RESOURCE
SELECT Resource_Id, Classification
FROM RQMT;
RowCount := SQL%RowCount;
DBMS_OUTPUT.PUT_LINE ('TABLE Resource: ' || RowCount || '
Rows
transitioned.');
COMMIT;
ELSE
DBMS_OUTPUT.PUT_LINE ('TABLE Resource is empty. No data
transitioned.');
END IF;
EXCEPTION
WHEN OTHERS THEN
Raise;
END;
/
But now I need to commit every 1000 records. Any suggestions as to
what would be the best way? I dont think ROWNUM would help here,
because it would pick the same 1000 records every time, causing
primary key violation...
thx
maa
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Maryann Atkinson
INET: [EMAIL PROTECTED]
Why do you need the first count(*) ? If the table you have to insert
from is big it's a waste of time. You can check SQL%ROWCOUNT after the
insert in all cases.
Otherwise I fully agree with the implicit suggestion that you should
question the reason for committing every 1000 rows. It would force you
to adopt a row-by-row logic which will kill performance.
The most acceptable solution might perhaps be an OCI program, in which
you would fetch 1000 rows per 1000 rows in arrays and insert likewise. I
don't see any way to do something similar in PL/SQL but it's close to
midnight here and I am getting pretty tired.
--
Regards,
Stephane Faroult
Oriole Software
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Faroult
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: insert and commit 1000 records at a time
[EMAIL PROTECTED] wrote:
Mladen Gogala wrote:
I believe that this would be the best solution:
DECLARE
RowCount NUMBER:= 0;
BEGIN
/* This will work if the RESOURCE table has the parallel
attribute set. In 8i, table needs to be partitioned as well */
EXECUTE IMMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
SELECT Count(*)
INTO RowCount
FROM RQMT;
IF RowCount 0 THEN
INSERT /*+ APPEND */ INTO RESOURCE
SELECT Resource_Id, Classification
FROM RQMT;
RowCount := SQL%RowCount;
DBMS_OUTPUT.PUT_LINE ('TABLE Resource: ' || RowCount || '
Rows
transitioned.');
COMMIT;
ELSE
DBMS_OUTPUT.PUT_LINE ('TABLE Resource is empty. No data
transitioned.');
END IF;
EXCEPTION
WHEN OTHERS THEN
Raise NOT_LOGGED_ON;
END;
/
On 10/16/2003 01:29:33 PM, [EMAIL PROTECTED] wrote:
yeah dont commit every 1000 records and do it in one shot. this is
going to be much slower.
why do you want to do it this way? Ive done 100m inserts with just an
insert select and one commit.
From: Maryann Atkinson [EMAIL PROTECTED]
Date: 2003/10/16 Thu AM 11:54:33 EDT
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: insert and commit 1000 records at a time
I have 2 tables, Rqmt and Resource, same structure.
I need to take all almost-one-million records from Rqmt and
insert them to Resource. So far this worked ok:
DECLARE
RowCount NUMBER:= 0;
BEGIN
SELECT Count(*)
INTO RowCount
FROM RQMT;
IF RowCount 0 THEN
INSERT INTO RESOURCE
SELECT Resource_Id, Classification
FROM RQMT;
RowCount := SQL%RowCount;
DBMS_OUTPUT.PUT_LINE ('TABLE Resource: ' || RowCount || '
Rows
transitioned.');
COMMIT;
ELSE
DBMS_OUTPUT.PUT_LINE ('TABLE Resource is empty. No data
transitioned.');
END IF;
EXCEPTION
WHEN OTHERS THEN
Raise;
END;
/
But now I need to commit every 1000 records. Any suggestions as to
what would be the best way? I dont think ROWNUM would help here,
because it would pick the same 1000 records every time, causing
primary key violation...
thx
maa
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Maryann Atkinson
INET: [EMAIL PROTECTED]
Why do you need the first count(*) ? If the table you have to insert
from is big it's a waste of time. You can check SQL%ROWCOUNT after the
insert in all cases.
Otherwise I fully agree with the implicit suggestion that you should
question the reason for committing every 1000 rows. It would force you
to adopt a row-by-row logic which will kill performance.
The most acceptable solution might perhaps be an OCI program, in which
you would fetch 1000 rows per 1000 rows in arrays and insert likewise. I
don't see any way to do something similar in PL/SQL but it's close to
midnight here and I am getting pretty tired.
The array fetch in OCI is equivalent to FETCH ... BULK COLLECT INTO ...
in PL/SQL. The array insert in OCI is equivalent to FORALL i IN 1..1000
INSERT INTO ... in PL/SQL. IMHO, doing it in OCI won't be faster than
the solution proposed by Stephen Lee using the COPY FROM command in
SQL*Plus while setting copycommit to 1000, which essentially does the
same thing. Doing it in PL/SQL won't be faster either because while
PL/SQL code runs in-process with the database, it's not compiled like
the OCI solution. So if the OP really wants to commit the insert every
1000 rows, IMHO the easiest and fastest solution would be to use COPY
FROM in SQL*Plus.
- Dave
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Dave Hau
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: Insert performance
Hi! Search for "surrogate key" in google for example. You have to modify your generated primary key valuesto not hit the same index blocks consecutively. For example, add another high-cardinality column to your primary key, or if using meaningless primary keys then just have your primary keys values to be spread over different value ranges (either by using two sequences, one is "main" seq, other is a "wrapping" cycling from 1 to 1000 for example - the result sequence would be wrapping seq * main seq, thus each insert is going to different part in index, other way would be using pre-generated primary key values). Other way would be to hash-partition your tables and indexes to spread inserts over partitions, but I suggest you to get your design ok first. Also, with lot's of concurrent inserts, you should increase the number of freelists for your tables to the number of concurrent inserts, also take a look to _bump_highwater_mark_count parameter and freelist groups when having really huge insert activity. Tanel. - Original Message - From: Rick Stephenson To: Multiple recipients of list ORACLE-L Sent: Tuesday, September 23, 2003 6:44 AM Subject: Insert performance Does anyone have any idea how to improve performance for multiple inserts into a table that uses a sequence generated primary key? I have approximately 6 concurrent inserts per second into this table which causes the primary key index to become a hot block. This in turn causes "buffer busy waits". I have increased initrans, but am not sure where to go from here Any ideas would be appreciated, Thanks, Rick Stephenson Oracle Database Administrator Ovid Technologies, Inc. [EMAIL PROTECTED] This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, forwarding or otherwise distributing or taking any action in reliance on the contents of this information is strictly prohibited.
RE: Insert performance
I have already bumped that up quite a bit as well, but I still get many waits. Thanks, Rick Stephenson Oracle Database Administrator Ovid Technologies, Inc. [EMAIL PROTECTED] -Original Message- From: Justin Cave [mailto:[EMAIL PROTECTED] Sent: Monday, September 22, 2003 11:10 PM To: Multiple recipients of list ORACLE-L Subject: Re: Insert performance At 09:44 PM 9/22/2003, you wrote: Does anyone have any idea how to improve performance for multiple inserts into a table that uses a sequence generated primary key? Is the sequence cache set to an appropriately large value? Justin Cave I have approximately 6 concurrent inserts per second into this table which causes the primary key index to become a hot block. This in turn causes buffer busy waits. I have increased initrans, but am not sure where to go from here Any ideas would be appreciated, Thanks, Rick Stephenson Oracle Database Administrator Ovid Technologies, Inc. [EMAIL PROTECTED] This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, forwarding or otherwise distributing or taking any action in reliance on the contents of this information is strictly prohibited. Justin Cave Distributed Database Consulting This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, forwarding or otherwise distributing or taking any action in reliance on the contents of this information is strictly prohibited.
RE: Insert performance
Rick, Find out what kind(s) of buffer busy waits you have. MetaLink, Anjo Kolks YAPP paper, or Steve Adamss book can tell you how to read the p1, p2, p3 values on the waits you see. Then eliminate the motive for the waits. The same documents will tell you how. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic101 in Sydney - Hotsos Symposium 2004 March 710 Dallas - Visit www.hotsos.com for schedule details... -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Rick Stephenson Sent: Tuesday, September 23, 2003 4:05 PM To: Multiple recipients of list ORACLE-L Subject: RE: Insert performance I have already bumped that up quite a bit as well, but I still get many waits. Thanks, Rick Stephenson Oracle Database Administrator Ovid Technologies, Inc. [EMAIL PROTECTED] -Original Message- From: Justin Cave [mailto:[EMAIL PROTECTED] Sent: Monday, September 22, 2003 11:10 PM To: Multiple recipients of list ORACLE-L Subject: Re: Insert performance At 09:44 PM 9/22/2003, you wrote: Does anyone have any idea how to improve performance for multiple inserts into a table that uses a sequence generated primary key? Is the sequence cache set to an appropriately large value? Justin Cave I have approximately 6 concurrent inserts per second into this table which causes the primary key index to become a hot block. This in turn causes buffer busy waits. I have increased initrans, but am not sure where to go from here Any ideas would be appreciated, Thanks, Rick Stephenson Oracle Database Administrator Ovid Technologies, Inc. [EMAIL PROTECTED] This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, forwarding or otherwise distributing or taking any action in reliance on the contents of this information is strictly prohibited. Justin Cave Distributed Database Consulting This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, forwarding or otherwise distributing or taking any action in reliance on the contents of this information is strictly prohibited.
Insert performance
Does anyone have any idea how to improve performance for multiple inserts into a table that uses a sequence generated primary key? I have approximately 6 concurrent inserts per second into this table which causes the primary key index to become a hot block. This in turn causes "buffer busy waits". I have increased initrans, but am not sure where to go from here Any ideas would be appreciated, Thanks, Rick Stephenson Oracle Database Administrator Ovid Technologies, Inc. [EMAIL PROTECTED] This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, forwarding or otherwise distributing or taking any action in reliance on the contents of this information is strictly prohibited.
RE: Insert performance
Rick, I haven't tried this myself but you could consider a reverse key index (depending on your version). That way multiple inserts won't go to the same block. However, (from Perf Tuning 101) you will incur much more IO than a normal index if you do range scans, so you'd need to consider how your index is accessed (probably not doing range scans on a primary key sequence). Obviously this will need testing to see how well it goes for you. HTH, Bruce Reardon -Original Message- Sent: Tuesday, 23 September 2003 1:45 PM Does anyone have any idea how to improve performance for multiple inserts into a table that uses a sequence generated primary key? I have approximately 6 concurrent inserts per second into this table which causes the primary key index to become a hot block. This in turn causes buffer busy waits. I have increased initrans, but am not sure where to go from here Any ideas would be appreciated, Thanks, Rick Stephenson Oracle Database Administrator Ovid Technologies, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reardon, Bruce (CALBBAY) 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: Insert performance
Re: Can't insert into partition
Btw, have you noticed that you have a dot instead of comma in your hash hint: SELECT /*+ use_hash(cjs, ps. md, a, o, x, wv, apc, wv1) */ How big are your tables - why do you want to have hash join on all of them? Hash joins aren't fast if you got huge datasets and little hash_area_size... especially when statistics aren't correct. Check for v$session_event for your session and for what does it wait the most (also compare with CPU used by this session statistic from v$sesstat although from execution plan I'd say you got IO problem rather than CPU bottleneck). Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, August 06, 2003 6:39 PM We have a situation where a process can't insert into a partition of a partitioned table. The process just keeps running. - A stored procedure executes a SQL insert statement (listing below). - It normally completes in 30 minutes, but now just runs for hours. - Oracle 8.1.6 on Dec/Compaq/HP Alpha - In tracing the process, it is waiting on db file scattered read. - This is a relatively new process, but it has completed successfully twice in production and numerous times in test. - I was able to perform a simple insert into the partition. - I created a test table (non-partitioned) and the process worked fine there, completing in the normal 30 minutes. - I rebuilt the partition (drop partition and create partition), to no effect. I'm stumped and looking for any suggestions: Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] Here is the SQL and the explain plan: SQL explain plan for 2 INSERT /*+ APPEND */INTO CURRJOBFACT NOLOGGING 3 ( bunch of columns ) 21SELECT /*+ use_hash(cjs, ps. md, a, o, x, wv, apc, wv1) */ 22 CJS.JOBNBR,CJS.SOURCEFISCALYEAR,CJS.LIFETOUCHID,MD.PROGRAMID, 23 CJS.MARKETINGCODE,CJS.PLANTRECEIPTDATE,CJS.PHOTOGRAPHYDATE, 24 CJS.SHIPDATE,CJS.SELLINGMETHODCODE,CJS.MDRPRIMARYID,CJS.SUBPROGRAMCODE, 25 CJS.TERRCODE,CJS.SUBTERRCODE,CJS.BIDIND,CJS.PDKIND,CJS.PDKPARTNBR, 26 CJS.RETAKEIND, 27 TO_NUMBER(TRANSLATE(UPPER(SUBSTR(CJS.JOBNBR,10,1)),'ABCDEFGHIJKLMNOPQRSTUV , 28DECODE(TO_DATE(CJS.SHIPDATE,'/MM/DD'), NULL ,'N','Y'), 29DECODE(NVL(GREATEST(CJS.YTDCASHRECEIVEDAMT,0),0),0, 30DECODE(CJS.PAYSTATUSIND,'F','Y','N'),'Y') PAIDJOBIND, 31 CJS.PAYSTATUSIND,PS.PAIDSHIPPEDJOBIND, 32 DECODE(SUBSTR(CJS.JOBNBR,9,1),'I','Y','N') PREJOBIND,CJS.PLANTCODE, 33 CJS.SHOTQTY,PS.MTDSHIPPEDPKGQTY,PS.MTDCHARGEBACKAMT,PS.MTDTERRCMSNAMT, 34 NVL(PS.MTDTERRCMSNAMT,0) - NVL(PS.MTDCHARGEBACKAMT,0) ,PS.MTDCASHRECEIVEDA, 35 PS.MTDCASHRETAINEDAMT,PS.MTDESTACCTCMSNAMT,PS.MTDACCTCMSNPAIDAMT, 36 PS.MTDGROSSCASHAMT,PS.MTDSALESTAXAMT,CJS.YTDSHIPPEDPKGQTY,CJS.YTDCHARGEBAC , 37 CJS.YTDTERRCMSNAMT,CJS.YTDTERREARNINGSAMT,CJS.YTDCASHRECEIVEDAMT, 38 CJS.YTDCASHRETAINEDAMT,CJS.YTDESTACCTCMSNAMT,CJS.YTDACCTCMSNPAIDAMT, 39 CJS.YTDCASHRECEIVEDAMT +YTDESTACCTCMSNAMT + YTDACCTCMSNPAIDAMT , 40 CJS.YTDSALESTAXAMT,CJS.YTDPERFECTSALEAMT,PS.PRELIMYTDSHIPPEDPKGQTY, 41 PS.PRELIMYTDCHARGEBACKAMT,PS.PRELIMYTDTERRCMSNAMT, 42 PS.PRELIMYTDTERRCMSNAMT - PS.PRELIMYTDCHARGEBACKAMT ,PS.PRELIMYTDCASHRECEI, 43 PS.PRELIMYTDCASHRETAINEDAMT,PS.PRELIMYTDESTACCTCMSNAMT, 44 PS.PRELIMYTDACCTCMSNPAIDAMT,PS.PRELIMYTDGROSSCASHAMT,PS.PRELIMYTDSALESTAXA , 45 PS.PRELIMYTDPERFECTSALEAMT,CJS.YTDJTEPAIDPKGQTY,CJS.YTDPAIDPKGQTY, 46 CJS.YTDUNPAIDPKGQTY,CJS.YTDXNOPURCHASEQTY,PS.YTDPAIDPKGSHIPQTY, 47 PS.YTDUNPAIDPKGSHIPQTY,CJS.PROOFPOSEQTY,CJS.PROOFCOUNTQTY,CJS.EXTRACTDATE, 48 CJS.ORIGINALRECEIVEDDATE,PS.CMSNSTATUSCODE,WV1.FIRSTPOSITIVECASHDATE, 49 DECODE(C.LIFETOUCHID, NULL ,'New','Retained') RENEWALSTATUSCODE, 50 NVL(WV.PRELIMYTDESTACCTCMSNAMT, 51 DECODE(NVL(PS.PRELIMYTDESTACCTCMSNAMT,0),0, NULL ,PS.PRELIMYTDESTACCTCMSNA, 52 APC.AVGPKGPRICE 53 FROM CURRJOB_STAGE CJS,PERIOD_STAGE PS,MARKETINGDIM MD, 54 (SELECT A1.JOBNBR,DECODE(SUM(PAIDOFFERQTY),0,0, 55 SUM(A1.OFFERPRICEAMT * A1.PAIDOFFERQTY ) / SUM(A1.PAIDOFFERQTY) ) AVGPKGPR 56 FROM (SELECT DISTINCT A.JOBNBR,A.OFFERNAME,A.PAIDOFFERQTY,A.OFFERPRICEAT 57 FROM OFFERLOAD_STAGE A 58 WHERE A.OFFERNAME IN ( 'A','B','C','D','E','F','G','H','I','J','K 59 GROUP BY A1.JOBNBR ) APC, 60 (SELECT DISTINCT C.LIFETOUCHID,C.PROGRAMID,MD.MARKETINGCODE 61 FROM CURRJOBFACT C,MARKETINGDIM MD WHERE C.SOURCEFISCALYEAR 2004 62 AND C.MARKETINGCODE = MD.MARKETINGCODE ) C, 63 (SELECT JOBNBR,MIN(DEPOSITDATE) FIRSTPOSITIVECASHDATE 64 FROM CASHTXNFACT 65 WHERE SOURCEFISCALYEAR = 2004 GROUP BY JOBNBR ) WV1, 66 (SELECT X.JOBNBR,X.WEEKENDDATE,X.PRELIMYTDESTACCTCMSNAMT 67 FROM WKLYJOBFACT X, 68 (SELECT JOBNBR,MAX(WEEKENDDATE) MAXWEEKENDDATE 69 FROM WKLYJOBFACT WHERE SOURCEFISCALYEAR = 2004 70AND NVL(PRELIMYTDESTACCTCMSNAMT,0) 0
RE: Can't insert into partition
Wolfgang Thank you so much! You spotted something that we had overlooked! The dot/comma was indeed the problem. And thanks to you and everyone else for the help that helped narrow the problem down to this point. It seems that as you pointed out, the hint had a syntax error all along, but CBO was making a good decision anyway for awhile, then for some reason didn't make a good decision anymore. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, August 07, 2003 5:49 PM To: Multiple recipients of list ORACLE-L No, you can put a hint in inner sql and subselects. Some hints you NEED to put on a subselect to make any sense. Is that sql verbatim? The hint has a syntax error. There is a dot rather than a comma after ps which - pooof - may turn the princely hint into an ugly toad (no pun intended) comment. Another thnig you can try, since you are using bind variables, is to jack up db_file_multiblock_read_count, hash_multiblock_io_count, hash_area_size, optimizer_index_cost_adj (to 1), create a stored outline of the sql - hopefully it will use hash joins with all the help, and then revert to the normal init_ora settings and tell oracle to use the stored outline. At 02:19 PM 8/7/2003 -0800, you wrote: Thanks Wolfgang! And thanks to the others who have helped us unravel this problem. Your suggestion put us on the right track. I started running a SQL trace/tkprof, and lo and behold, when the stored procedure submits the SQL, CBO does everything as NESTED LOOPS. The next question is how to induce CBO to consider HASH JOIN? The original query had USE_HASH hints on the subqueries. Somewhere I thought I recalled that you could only put hints on the outer SQL statement -- is that true? So we tried adding the USE_HASH hint to the overall INSERT statement, to no effect. Here is the relevant portion of the tkprof output. Thanks again to eveyone. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] INSERT /*+ APPEND use_hash(CJS, PS, APC, MD, C, WV1, WV) */INTO CURRJOBFACT NOLOGGING ( JOBNBR,SOURCEFISCALYEAR,LIFETOUCHID,PROGRAMID,MARKETINGCODE, PLANTRECEIPTDATE,PHOTOGRAPHYDATE,SHIPDATE,SELLINGMETHODCODE,MDRPRIMARYID, SUBPROGRAMCODE,TERRCODE,SUBTERRCODE,BIDIND,PDKIND,PDKPARTNBR,RETAKEIND, RETAKENBR,SHIPPEDJOBIND,PAIDJOBIND,PAYSTATUSIND,PAIDSHIPPEDJOBIND,PREJOBIND , PLANTCODE,SHOTQTY,MTDSHIPPEDPKGQTY,MTDCHARGEBACKAMT,MTDTERRCMSNAMT, MTDTERREARNINGSAMT,MTDCASHRECEIVEDAMT,MTDCASHRETAINEDAMT,MTDESTACCTCMSNAMT, MTDACCTCMSNPAIDAMT,MTDGROSSCASHAMT,MTDSALESTAXAMT,YTDSHIPPEDPKGQTY, YTDCHARGEBACKAMT,YTDTERRCMSNAMT,YTDTERREARNINGSAMT,YTDCASHRECEIVEDAMT, YTDCASHRETAINEDAMT,YTDESTACCTCMSNAMT,YTDACCTCMSNPAIDAMT,YTDGROSSCASHAMT, YTDSALESTAXAMT,YTDPERFECTSALEAMT,PRELIMYTDSHIPPEDPKGQTY, PRELIMYTDCHARGEBACKAMT,PRELIMYTDTERRCMSNAMT,PRELIMYTDTERREARNINGSAMT, PRELIMYTDCASHRECEIVEDAMT,PRELIMYTDCASHRETAINEDAMT,PRELIMYTDESTACCTCMSNAMT, PRELIMYTDACCTCMSNPAIDAMT,PRELIMYTDGROSSCASHAMT,PRELIMYTDSALESTAXAMT, PRELIMYTDPERFECTSALEAMT,YTDJTEPAIDPKGQTY,YTDPAIDPKGQTY,YTDUNPAIDPKGQTY, YTDXNOPURCHASEQTY,PRELIMYTDPAIDPKGSHIPQTY,PRELIMYTDUNPAIDPKGSHIPQTY, PROOFPOSEQTY,PROOFCOUNTQTY,EXTRACTDATE,ORIGINALRECEIVEDDATE,CMSNSTATUSCODE, FIRSTPOSITIVECASHDATE,RENEWALSTATUSCODE,ORIGESTACCTCMSNAMT,AVGPKGPRICE ) SELECT /*+ use_hash(cjs, ps. md, a, o, x, wv, apc, wv1) */CJS.JOBNBR, (columns omitted) PS.PRELIMYTDESTACCTCMSNAMT)) ORIGESTACCTCMSNAMT,APC.AVGPKGPRICE FROM CURRJOB_STAGE CJS,PERIOD_STAGE PS,MARKETINGDIM MD,(SELECT A1.JOBNBR, DECODE(SUM(PAIDOFFERQTY),0,0,SUM(A1.OFFERPRICEAMT * A1.PAIDOFFERQTY ) / SUM(A1.PAIDOFFERQTY) ) AVGPKGPRICE FROM (SELECT DISTINCT A.JOBNBR, A.OFFERNAME,A.PAIDOFFERQTY,A.OFFERPRICEAMT FROM OFFERLOAD_STAGE A WHERE A.OFFERNAME IN ( 'A','B','C','D','E','F','G','H','I','J','K' )) A1 GROUP BY A1.JOBNBR ) APC,(SELECT DISTINCT C.LIFETOUCHID,C.PROGRAMID, MD.MARKETINGCODE FROM CURRJOBFACT C,MARKETINGDIM MD WHERE C.SOURCEFISCALYEAR :b1 AND C.MARKETINGCODE = MD.MARKETINGCODE ) C, (SELECT JOBNBR,MIN(DEPOSITDATE) FIRSTPOSITIVECASHDATE FROM CASHTXNFACT WHERE SOURCEFISCALYEAR = :b1 GROUP BY JOBNBR ) WV1,(SELECT X.JOBNBR, X.WEEKENDDATE,X.PRELIMYTDESTACCTCMSNAMT FROM WKLYJOBFACT X,(SELECT JOBNBR, MAX(WEEKENDDATE) MAXWEEKENDDATE FROM WKLYJOBFACT WHERE SOURCEFISCALYEAR = :b1 AND NVL(PRELIMYTDESTACCTCMSNAMT,0) 0 GROUP BY JOBNBR ) W1 WHERE X.JOBNBR = W1.JOBNBR AND X.WEEKENDDATE = W1.MAXWEEKENDDATE ) WV WHERE CJS.JOBNBR = PS.JOBNBR (+)AND CJS.JOBNBR = APC.JOBNBR (+)AND CJS.MARKETINGCODE = MD.MARKETINGCODE AND CJS.LIFETOUCHID = C.LIFETOUCHID (+)AND CJS.MARKETINGCODE = C.MARKETINGCODE (+)AND CJS.JOBNBR = WV1.JOBNBR (+)AND CJS.JOBNBR = WV.JOBNBR (+) call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse
RE: Can't insert into partition
file scattered read' ela= 0 p1=197 p2=104077 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=104085 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=104093 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=104101 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=104109 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104117 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=104125 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104133 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104141 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104149 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104157 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=104165 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104173 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104181 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104189 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104197 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104205 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104213 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104221 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104229 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104237 p3=8 -Original Message- Sent: Wednesday, August 06, 2003 12:20 PM To: Multiple recipients of list ORACLE-L Dennis, Could you plese post the v$session_wait. Do you have a 10046 trace? Henry -Original Message- DENNIS WILLIAMS Sent: Wednesday, August 06, 2003 11:40 AM To: Multiple recipients of list ORACLE-L We have a situation where a process can't insert into a partition of a partitioned table. The process just keeps running. - A stored procedure executes a SQL insert statement (listing below). - It normally completes in 30 minutes, but now just runs for hours. - Oracle 8.1.6 on Dec/Compaq/HP Alpha - In tracing the process, it is waiting on db file scattered read. - This is a relatively new process, but it has completed successfully twice in production and numerous times in test. - I was able to perform a simple insert into the partition. - I created a test table (non-partitioned) and the process worked fine there, completing in the normal 30 minutes. - I rebuilt the partition (drop partition and create partition), to no effect. I'm stumped and looking for any suggestions: Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] Here is the SQL and the explain plan: SQL explain plan for 2 INSERT /*+ APPEND */INTO CURRJOBFACT NOLOGGING 3 ( bunch of columns ) 21SELECT /*+ use_hash(cjs, ps. md, a, o, x, wv, apc, wv1) */ 22 CJS.JOBNBR,CJS.SOURCEFISCALYEAR,CJS.LIFETOUCHID,MD.PROGRAMID, 23 CJS.MARKETINGCODE,CJS.PLANTRECEIPTDATE,CJS.PHOTOGRAPHYDATE, 24 CJS.SHIPDATE,CJS.SELLINGMETHODCODE,CJS.MDRPRIMARYID,CJS.SUBPROGRAMCODE, 25 CJS.TERRCODE,CJS.SUBTERRCODE,CJS.BIDIND,CJS.PDKIND,CJS.PDKPARTNBR, 26 CJS.RETAKEIND, 27 TO_NUMBER(TRANSLATE(UPPER(SUBSTR(CJS.JOBNBR,10,1)),'ABCDEFGHIJKLMNOPQRSTUV , 28DECODE(TO_DATE(CJS.SHIPDATE,'/MM/DD'), NULL ,'N','Y'), 29DECODE(NVL(GREATEST(CJS.YTDCASHRECEIVEDAMT,0),0),0, 30DECODE(CJS.PAYSTATUSIND,'F','Y','N'),'Y') PAIDJOBIND, 31 CJS.PAYSTATUSIND,PS.PAIDSHIPPEDJOBIND, 32 DECODE(SUBSTR(CJS.JOBNBR,9,1),'I','Y','N') PREJOBIND,CJS.PLANTCODE, 33 CJS.SHOTQTY,PS.MTDSHIPPEDPKGQTY,PS.MTDCHARGEBACKAMT,PS.MTDTERRCMSNAMT, 34 NVL(PS.MTDTERRCMSNAMT,0) - NVL(PS.MTDCHARGEBACKAMT,0) ,PS.MTDCASHRECEIVEDA, 35 PS.MTDCASHRETAINEDAMT,PS.MTDESTACCTCMSNAMT,PS.MTDACCTCMSNPAIDAMT, 36 PS.MTDGROSSCASHAMT,PS.MTDSALESTAXAMT,CJS.YTDSHIPPEDPKGQTY,CJS.YTDCHARGEBAC , 37 CJS.YTDTERRCMSNAMT,CJS.YTDTERREARNINGSAMT,CJS.YTDCASHRECEIVEDAMT, 38 CJS.YTDCASHRETAINEDAMT,CJS.YTDESTACCTCMSNAMT,CJS.YTDACCTCMSNPAIDAMT, 39 CJS.YTDCASHRECEIVEDAMT +YTDESTACCTCMSNAMT + YTDACCTCMSNPAIDAMT , 40 CJS.YTDSALESTAXAMT,CJS.YTDPERFECTSALEAMT,PS.PRELIMYTDSHIPPEDPKGQTY, 41 PS.PRELIMYTDCHARGEBACKAMT,PS.PRELIMYTDTERRCMSNAMT, 42 PS.PRELIMYTDTERRCMSNAMT - PS.PRELIMYTDCHARGEBACKAMT ,PS.PRELIMYTDCASHRECEI, 43 PS.PRELIMYTDCASHRETAINEDAMT,PS.PRELIMYTDESTACCTCMSNAMT, 44 PS.PRELIMYTDACCTCMSNPAIDAMT,PS.PRELIMYTDGROSSCASHAMT,PS.PRELIMYTDSALESTAXA , 45 PS.PRELIMYTDPERFECTSALEAMT,CJS.YTDJTEPAIDPKGQTY,CJS.YTDPAIDPKGQTY, 46 CJS.YTDUNPAIDPKGQTY,CJS.YTDXNOPURCHASEQTY,PS.YTDPAIDPKGSHIPQTY, 47 PS.YTDUNPAIDPKGSHIPQTY,CJS.PROOFPOSEQTY,CJS.PROOFCOUNTQTY,CJS.EXTRACTDATE, 48 CJS.ORIGINALRECEIVEDDATE,PS.CMSNSTATUSCODE,WV1.FIRSTPOSITIVECASHDATE, 49 DECODE(C.LIFETOUCHID, NULL ,'New','Retained') RENEWALSTATUSCODE, 50 NVL(WV.PRELIMYTDESTACCTCMSNAMT, 51 DECODE(NVL(PS.PRELIMYTDESTACCTCMSNAMT,0),0, NULL ,PS.PRELIMYTDESTACCTCMSNA, 52 APC.AVGPKGPRICE 53 FROM CURRJOB_STAGE CJS,PERIOD_STAGE PS,MARKETINGDIM MD, 54 (SELECT A1.JOBNBR,DECODE
RE: Can't insert into partition
Dennis, I am not pretty sure but you can try to increase degree of your table/index to 1 .. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Thu, 07 Aug 2003 14:19:23 -0800 Thanks Wolfgang! And thanks to the others who have helped us unravel this problem. Your suggestion put us on the right track. I started running a SQL trace/tkprof, and lo and behold, when the stored procedure submits the SQL, CBO does everything as NESTED LOOPS. The next question is how to induce CBO to consider HASH JOIN? The original query had USE_HASH hints on the subqueries. Somewhere I thought I recalled that you could only put hints on the outer SQL statement -- is that true? So we tried adding the USE_HASH hint to the overall INSERT statement, to no effect. Here is the relevant portion of the tkprof output. Thanks again to eveyone. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] INSERT /*+ APPEND use_hash(CJS, PS, APC, MD, C, WV1, WV) */INTO CURRJOBFACT NOLOGGING ( JOBNBR,SOURCEFISCALYEAR,LIFETOUCHID,PROGRAMID,MARKETINGCODE, PLANTRECEIPTDATE,PHOTOGRAPHYDATE,SHIPDATE,SELLINGMETHODCODE,MDRPRIMARYID, SUBPROGRAMCODE,TERRCODE,SUBTERRCODE,BIDIND,PDKIND,PDKPARTNBR,RETAKEIND, RETAKENBR,SHIPPEDJOBIND,PAIDJOBIND,PAYSTATUSIND,PAIDSHIPPEDJOBIND,PREJOBIND, PLANTCODE,SHOTQTY,MTDSHIPPEDPKGQTY,MTDCHARGEBACKAMT,MTDTERRCMSNAMT, MTDTERREARNINGSAMT,MTDCASHRECEIVEDAMT,MTDCASHRETAINEDAMT,MTDESTACCTCMSNAMT, MTDACCTCMSNPAIDAMT,MTDGROSSCASHAMT,MTDSALESTAXAMT,YTDSHIPPEDPKGQTY, YTDCHARGEBACKAMT,YTDTERRCMSNAMT,YTDTERREARNINGSAMT,YTDCASHRECEIVEDAMT, YTDCASHRETAINEDAMT,YTDESTACCTCMSNAMT,YTDACCTCMSNPAIDAMT,YTDGROSSCASHAMT, YTDSALESTAXAMT,YTDPERFECTSALEAMT,PRELIMYTDSHIPPEDPKGQTY, PRELIMYTDCHARGEBACKAMT,PRELIMYTDTERRCMSNAMT,PRELIMYTDTERREARNINGSAMT, PRELIMYTDCASHRECEIVEDAMT,PRELIMYTDCASHRETAINEDAMT,PRELIMYTDESTACCTCMSNAMT, PRELIMYTDACCTCMSNPAIDAMT,PRELIMYTDGROSSCASHAMT,PRELIMYTDSALESTAXAMT, PRELIMYTDPERFECTSALEAMT,YTDJTEPAIDPKGQTY,YTDPAIDPKGQTY,YTDUNPAIDPKGQTY, YTDXNOPURCHASEQTY,PRELIMYTDPAIDPKGSHIPQTY,PRELIMYTDUNPAIDPKGSHIPQTY, PROOFPOSEQTY,PROOFCOUNTQTY,EXTRACTDATE,ORIGINALRECEIVEDDATE,CMSNSTATUSCODE, FIRSTPOSITIVECASHDATE,RENEWALSTATUSCODE,ORIGESTACCTCMSNAMT,AVGPKGPRICE ) SELECT /*+ use_hash(cjs, ps. md, a, o, x, wv, apc, wv1) */CJS.JOBNBR, (columns omitted) PS.PRELIMYTDESTACCTCMSNAMT)) ORIGESTACCTCMSNAMT,APC.AVGPKGPRICE FROM CURRJOB_STAGE CJS,PERIOD_STAGE PS,MARKETINGDIM MD,(SELECT A1.JOBNBR, DECODE(SUM(PAIDOFFERQTY),0,0,SUM(A1.OFFERPRICEAMT * A1.PAIDOFFERQTY ) / SUM(A1.PAIDOFFERQTY) ) AVGPKGPRICE FROM (SELECT DISTINCT A.JOBNBR, A.OFFERNAME,A.PAIDOFFERQTY,A.OFFERPRICEAMT FROM OFFERLOAD_STAGE A WHERE A.OFFERNAME IN ( 'A','B','C','D','E','F','G','H','I','J','K' )) A1 GROUP BY A1.JOBNBR ) APC,(SELECT DISTINCT C.LIFETOUCHID,C.PROGRAMID, MD.MARKETINGCODE FROM CURRJOBFACT C,MARKETINGDIM MD WHERE C.SOURCEFISCALYEAR :b1 AND C.MARKETINGCODE = MD.MARKETINGCODE ) C, (SELECT JOBNBR,MIN(DEPOSITDATE) FIRSTPOSITIVECASHDATE FROM CASHTXNFACT WHERE SOURCEFISCALYEAR = :b1 GROUP BY JOBNBR ) WV1,(SELECT X.JOBNBR, X.WEEKENDDATE,X.PRELIMYTDESTACCTCMSNAMT FROM WKLYJOBFACT X,(SELECT JOBNBR, MAX(WEEKENDDATE) MAXWEEKENDDATE FROM WKLYJOBFACT WHERE SOURCEFISCALYEAR = :b1 AND NVL(PRELIMYTDESTACCTCMSNAMT,0) 0 GROUP BY JOBNBR ) W1 WHERE X.JOBNBR = W1.JOBNBR AND X.WEEKENDDATE = W1.MAXWEEKENDDATE ) WV WHERE CJS.JOBNBR = PS.JOBNBR (+)AND CJS.JOBNBR = APC.JOBNBR (+)AND CJS.MARKETINGCODE = MD.MARKETINGCODE AND CJS.LIFETOUCHID = C.LIFETOUCHID (+)AND CJS.MARKETINGCODE = C.MARKETINGCODE (+)AND CJS.JOBNBR = WV1.JOBNBR (+)AND CJS.JOBNBR = WV.JOBNBR (+) call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 0.01 0.01 0 0 0 0 Execute 1351.171349.5112086871212777866 0 Fetch0 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total2351.181349.5212086871212777866 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 21 (recursive depth: 1) Rows Row Source Operation --- --- 0 LOAD AS SELECT 0 NESTED LOOPS OUTER 1NESTED LOOPS OUTER 2 NESTED LOOPS OUTER 2 HASH JOIN OUTER 6412 HASH JOIN OUTER 6412HASH JOIN 246 TABLE ACCESS FULL MARKETINGDIM 6412 TABLE ACCESS FULL CURRJOB_STAGE 3093VIEW 3093 SORT GROUP BY 13728 VIEW 13728 SORT UNIQUE 35929TABLE ACCESS FULL OFFERLOAD_STAGE 47 VIEW 47SORT UNIQUE 177 NESTED LOOPS 178
RE: Can't insert into partition
Not certain that this is the case, but could it be that statistics are missing for one partition, thus occulting (as in 'undefined and something is undefined') statistics for the other partitions and the table ? Not necessarily for the table you are trying to insert into. Not sure that it is a good idea but I have a knack for picking up customers where air conditioning is out of order and above 25C I work at 50% of my capacity with an exponential decrease. As the temperature stands, if anybody is willing to water me from time to time, I'll appreciate. - --- Original Message --- - From: DENNIS WILLIAMS [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wed, 06 Aug 2003 07:39:36 We have a situation where a process can't insert into a partition of a partitioned table. The process just keeps running. - A stored procedure executes a SQL insert statement (listing below). - It normally completes in 30 minutes, but now just runs for hours. - Oracle 8.1.6 on Dec/Compaq/HP Alpha - In tracing the process, it is waiting on db file scattered read. - This is a relatively new process, but it has completed successfully twice in production and numerous times in test. - I was able to perform a simple insert into the partition. - I created a test table (non-partitioned) and the process worked fine there, completing in the normal 30 minutes. - I rebuilt the partition (drop partition and create partition), to no effect. I'm stumped and looking for any suggestions: Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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: Can't insert into partition
But then it's not the same sql anymore and the access plan can be wildly different. You need to use bind variables in your sqlplus session as well. Unfortunately, even then it is not guaranteed that you'll get the same plan as you get in the plsql proc. At 06:44 AM 8/7/2003 -0800, you wrote: Wolfgang - Yes, you are correct, it is using bind variables. To run the SQL standalone, we manually change these to literal variables. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, August 07, 2003 12:19 AM To: Multiple recipients of list ORACLE-L Is the sql you posted the exact sql as it is executed in the PLSQL procedure, i.e. is the procedure using literals such as 2004 in the predicates for sourcefiscalyear, or is it really using a bindvariable? Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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: Can't insert into partition
file scattered read' ela= 0 p1=197 p2=103941 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103949 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103957 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103965 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103973 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103981 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103989 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103997 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104005 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=104013 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104021 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104029 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=104037 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=104045 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=104053 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=104061 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=104069 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=104077 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=104085 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=104093 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=104101 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=104109 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104117 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=104125 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104133 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104141 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104149 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104157 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=104165 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104173 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104181 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104189 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104197 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104205 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104213 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104221 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104229 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104237 p3=8 -Original Message- Sent: Wednesday, August 06, 2003 12:20 PM To: Multiple recipients of list ORACLE-L Dennis, Could you plese post the v$session_wait. Do you have a 10046 trace? Henry -Original Message- DENNIS WILLIAMS Sent: Wednesday, August 06, 2003 11:40 AM To: Multiple recipients of list ORACLE-L We have a situation where a process can't insert into a partition of a partitioned table. The process just keeps running. - A stored procedure executes a SQL insert statement (listing below). - It normally completes in 30 minutes, but now just runs for hours. - Oracle 8.1.6 on Dec/Compaq/HP Alpha - In tracing the process, it is waiting on db file scattered read. - This is a relatively new process, but it has completed successfully twice in production and numerous times in test. - I was able to perform a simple insert into the partition. - I created a test table (non-partitioned) and the process worked fine there, completing in the normal 30 minutes. - I rebuilt the partition (drop partition and create partition), to no effect. I'm stumped and looking for any suggestions: Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] Here is the SQL and the explain plan: SQL explain plan for 2 INSERT /*+ APPEND */INTO CURRJOBFACT NOLOGGING 3 ( bunch of columns ) 21SELECT /*+ use_hash(cjs, ps. md, a, o, x, wv, apc, wv1) */ 22 CJS.JOBNBR,CJS.SOURCEFISCALYEAR,CJS.LIFETOUCHID,MD.PROGRAMID, 23 CJS.MARKETINGCODE,CJS.PLANTRECEIPTDATE,CJS.PHOTOGRAPHYDATE, 24 CJS.SHIPDATE,CJS.SELLINGMETHODCODE,CJS.MDRPRIMARYID,CJS.SUBPROGRAMCODE, 25 CJS.TERRCODE,CJS.SUBTERRCODE,CJS.BIDIND,CJS.PDKIND,CJS.PDKPARTNBR, 26 CJS.RETAKEIND, 27 TO_NUMBER(TRANSLATE(UPPER(SUBSTR(CJS.JOBNBR,10,1)),'ABCDEFGHIJKLMNOPQRSTUV , 28DECODE(TO_DATE(CJS.SHIPDATE,'/MM/DD'), NULL ,'N','Y'), 29DECODE(NVL(GREATEST(CJS.YTDCASHRECEIVEDAMT,0),0),0, 30DECODE(CJS.PAYSTATUSIND,'F','Y','N'),'Y') PAIDJOBIND, 31 CJS.PAYSTATUSIND,PS.PAIDSHIPPEDJOBIND, 32 DECODE(SUBSTR(CJS.JOBNBR,9,1),'I','Y','N') PREJOBIND,CJS.PLANTCODE, 33 CJS.SHOTQTY,PS.MTDSHIPPEDPKGQTY,PS.MTDCHARGEBACKAMT,PS.MTDTERRCMSNAMT, 34 NVL(PS.MTDTERRCMSNAMT,0) - NVL(PS.MTDCHARGEBACKAMT,0) ,PS.MTDCASHRECEIVEDA, 35 PS.MTDCASHRETAINEDAMT,PS.MTDESTACCTCMSNAMT,PS.MTDACCTCMSNPAIDAMT, 36
RE: Can't insert into partition
You are welcome. Happens a lot. You see what should be there rather than what IS there and wonder why it's not working as designed. Someone else, uninvolved, comes along, takes one look at the thing, points out the error and leaves you (me) feeling like an idiot. At 12:54 PM 8/8/2003 -0800, you wrote: Wolfgang Thank you so much! You spotted something that we had overlooked! The dot/comma was indeed the problem. And thanks to you and everyone else for the help that helped narrow the problem down to this point. It seems that as you pointed out, the hint had a syntax error all along, but CBO was making a good decision anyway for awhile, then for some reason didn't make a good decision anymore. Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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: Can't insert into partition
read' ela= 1 p1=197 p2=103981 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103989 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103997 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104005 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=104013 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104021 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104029 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=104037 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=104045 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=104053 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=104061 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=104069 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=104077 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=104085 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=104093 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=104101 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=104109 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104117 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=104125 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104133 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104141 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104149 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104157 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=104165 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104173 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104181 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104189 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104197 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104205 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104213 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104221 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104229 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104237 p3=8 -Original Message- Sent: Wednesday, August 06, 2003 12:20 PM To: Multiple recipients of list ORACLE-L Dennis, Could you plese post the v$session_wait. Do you have a 10046 trace? Henry -Original Message- DENNIS WILLIAMS Sent: Wednesday, August 06, 2003 11:40 AM To: Multiple recipients of list ORACLE-L We have a situation where a process can't insert into a partition of a partitioned table. The process just keeps running. - A stored procedure executes a SQL insert statement (listing below). - It normally completes in 30 minutes, but now just runs for hours. - Oracle 8.1.6 on Dec/Compaq/HP Alpha - In tracing the process, it is waiting on db file scattered read. - This is a relatively new process, but it has completed successfully twice in production and numerous times in test. - I was able to perform a simple insert into the partition. - I created a test table (non-partitioned) and the process worked fine there, completing in the normal 30 minutes. - I rebuilt the partition (drop partition and create partition), to no effect. I'm stumped and looking for any suggestions: Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] Here is the SQL and the explain plan: SQL explain plan for 2 INSERT /*+ APPEND */INTO CURRJOBFACT NOLOGGING 3 ( bunch of columns ) 21SELECT /*+ use_hash(cjs, ps. md, a, o, x, wv, apc, wv1) */ 22 CJS.JOBNBR,CJS.SOURCEFISCALYEAR,CJS.LIFETOUCHID,MD.PROGRAMID, 23 CJS.MARKETINGCODE,CJS.PLANTRECEIPTDATE,CJS.PHOTOGRAPHYDATE, 24 CJS.SHIPDATE,CJS.SELLINGMETHODCODE,CJS.MDRPRIMARYID,CJS.SUBPROGRAMCODE, 25 CJS.TERRCODE,CJS.SUBTERRCODE,CJS.BIDIND,CJS.PDKIND,CJS.PDKPARTNBR, 26 CJS.RETAKEIND, 27 TO_NUMBER(TRANSLATE(UPPER(SUBSTR(CJS.JOBNBR,10,1)),'ABCDEFGHIJKLMNOPQRSTUV , 28DECODE(TO_DATE(CJS.SHIPDATE,'/MM/DD'), NULL ,'N','Y'), 29DECODE(NVL(GREATEST(CJS.YTDCASHRECEIVEDAMT,0),0),0, 30DECODE(CJS.PAYSTATUSIND,'F','Y','N'),'Y') PAIDJOBIND, 31 CJS.PAYSTATUSIND,PS.PAIDSHIPPEDJOBIND, 32 DECODE(SUBSTR(CJS.JOBNBR,9,1),'I','Y','N') PREJOBIND,CJS.PLANTCODE, 33 CJS.SHOTQTY,PS.MTDSHIPPEDPKGQTY,PS.MTDCHARGEBACKAMT,PS.MTDTERRCMSNAMT, 34 NVL(PS.MTDTERRCMSNAMT,0) - NVL(PS.MTDCHARGEBACKAMT,0) ,PS.MTDCASHRECEIVEDA, 35 PS.MTDCASHRETAINEDAMT,PS.MTDESTACCTCMSNAMT,PS.MTDACCTCMSNPAIDAMT, 36 PS.MTDGROSSCASHAMT,PS.MTDSALESTAXAMT,CJS.YTDSHIPPEDPKGQTY,CJS.YTDCHARGEBAC , 37 CJS.YTDTERRCMSNAMT,CJS.YTDTERREARNINGSAMT,CJS.YTDCASHRECEIVEDAMT, 38 CJS.YTDCASHRETAINEDAMT,CJS.YTDESTACCTCMSNAMT,CJS.YTDACCTCMSNPAIDAMT, 39 CJS.YTDCASHRECEIVEDAMT +YTDESTACCTCMSNAMT + YTDACCTCMSNPAIDAMT , 40 CJS.YTDSALESTAXAMT,CJS.YTDPERFECTSALEAMT,PS.PRELIMYTDSHIPPEDPKGQTY, 41 PS.PRELIMYTDCHARGEBACKAMT,PS.PRELIMYTDTERRCMSNAMT, 42 PS.PRELIMYTDTERRCMSNAMT
RE: Can't insert into partition
Wolfgang - Yes, you are correct, it is using bind variables. To run the SQL standalone, we manually change these to literal variables. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, August 07, 2003 12:19 AM To: Multiple recipients of list ORACLE-L Is the sql you posted the exact sql as it is executed in the PLSQL procedure, i.e. is the procedure using literals such as 2004 in the predicates for sourcefiscalyear, or is it really using a bindvariable? At 02:29 PM 8/6/2003 -0800, you wrote: Henry - Thanks. I feel like I'm getting an education today on the Oracle Wait Interface today. Nothing like a live problem for everything to make sense. Thanks for pointing out that I could find the table. It is our WKLYJOBFACT table. Not one we suspected. We have been doing an EXPLAIN PLAN by extracting the SQL from the stored procedure. I posted that. But when the SQL is extracted from the stored procedure, it runs just fine. Does anyone know how to get the explain plan that the PL/SQL procedure is seeing? Thanks to everyone for helping narrow the problem down this far. It has kept me from rebuilding the table which probably would have accomplished nothing. [...] SQL explain plan for 2 INSERT /*+ APPEND */INTO CURRJOBFACT NOLOGGING 3 ( bunch of columns ) 21SELECT /*+ use_hash(cjs, ps. md, a, o, x, wv, apc, wv1) */ 22 CJS.JOBNBR,CJS.SOURCEFISCALYEAR,CJS.LIFETOUCHID,MD.PROGRAMID, 23 CJS.MARKETINGCODE,CJS.PLANTRECEIPTDATE,CJS.PHOTOGRAPHYDATE, 24 CJS.SHIPDATE,CJS.SELLINGMETHODCODE,CJS.MDRPRIMARYID,CJS.SUBPROGRAMCODE, 25 CJS.TERRCODE,CJS.SUBTERRCODE,CJS.BIDIND,CJS.PDKIND,CJS.PDKPARTNBR, 26 CJS.RETAKEIND, 27 TO_NUMBER(TRANSLATE(UPPER(SUBSTR(CJS.JOBNBR,10,1)),'ABCDEFGHIJKLMNOPQRSTUV , 28DECODE(TO_DATE(CJS.SHIPDATE,'/MM/DD'), NULL ,'N','Y'), 29DECODE(NVL(GREATEST(CJS.YTDCASHRECEIVEDAMT,0),0),0, 30DECODE(CJS.PAYSTATUSIND,'F','Y','N'),'Y') PAIDJOBIND, 31 CJS.PAYSTATUSIND,PS.PAIDSHIPPEDJOBIND, 32 DECODE(SUBSTR(CJS.JOBNBR,9,1),'I','Y','N') PREJOBIND,CJS.PLANTCODE, 33 CJS.SHOTQTY,PS.MTDSHIPPEDPKGQTY,PS.MTDCHARGEBACKAMT,PS.MTDTERRCMSNAMT, 34 NVL(PS.MTDTERRCMSNAMT,0) - NVL(PS.MTDCHARGEBACKAMT,0) ,PS.MTDCASHRECEIVEDA, 35 PS.MTDCASHRETAINEDAMT,PS.MTDESTACCTCMSNAMT,PS.MTDACCTCMSNPAIDAMT, 36 PS.MTDGROSSCASHAMT,PS.MTDSALESTAXAMT,CJS.YTDSHIPPEDPKGQTY,CJS.YTDCHARGEBAC , 37 CJS.YTDTERRCMSNAMT,CJS.YTDTERREARNINGSAMT,CJS.YTDCASHRECEIVEDAMT, 38 CJS.YTDCASHRETAINEDAMT,CJS.YTDESTACCTCMSNAMT,CJS.YTDACCTCMSNPAIDAMT, 39 CJS.YTDCASHRECEIVEDAMT +YTDESTACCTCMSNAMT + YTDACCTCMSNPAIDAMT , 40 CJS.YTDSALESTAXAMT,CJS.YTDPERFECTSALEAMT,PS.PRELIMYTDSHIPPEDPKGQTY, 41 PS.PRELIMYTDCHARGEBACKAMT,PS.PRELIMYTDTERRCMSNAMT, 42 PS.PRELIMYTDTERRCMSNAMT - PS.PRELIMYTDCHARGEBACKAMT ,PS.PRELIMYTDCASHRECEI, 43 PS.PRELIMYTDCASHRETAINEDAMT,PS.PRELIMYTDESTACCTCMSNAMT, 44 PS.PRELIMYTDACCTCMSNPAIDAMT,PS.PRELIMYTDGROSSCASHAMT,PS.PRELIMYTDSALESTAXA , 45 PS.PRELIMYTDPERFECTSALEAMT,CJS.YTDJTEPAIDPKGQTY,CJS.YTDPAIDPKGQTY, 46 CJS.YTDUNPAIDPKGQTY,CJS.YTDXNOPURCHASEQTY,PS.YTDPAIDPKGSHIPQTY, 47 PS.YTDUNPAIDPKGSHIPQTY,CJS.PROOFPOSEQTY,CJS.PROOFCOUNTQTY,CJS.EXTRACTDATE, 48 CJS.ORIGINALRECEIVEDDATE,PS.CMSNSTATUSCODE,WV1.FIRSTPOSITIVECASHDATE, 49 DECODE(C.LIFETOUCHID, NULL ,'New','Retained') RENEWALSTATUSCODE, 50 NVL(WV.PRELIMYTDESTACCTCMSNAMT, 51 DECODE(NVL(PS.PRELIMYTDESTACCTCMSNAMT,0),0, NULL ,PS.PRELIMYTDESTACCTCMSNA, 52 APC.AVGPKGPRICE 53 FROM CURRJOB_STAGE CJS,PERIOD_STAGE PS,MARKETINGDIM MD, 54 (SELECT A1.JOBNBR,DECODE(SUM(PAIDOFFERQTY),0,0, 55 SUM(A1.OFFERPRICEAMT * A1.PAIDOFFERQTY ) / SUM(A1.PAIDOFFERQTY) ) AVGPKGPR 56 FROM (SELECT DISTINCT A.JOBNBR,A.OFFERNAME,A.PAIDOFFERQTY,A.OFFERPRICEAT 57 FROM OFFERLOAD_STAGE A 58 WHERE A.OFFERNAME IN ( 'A','B','C','D','E','F','G','H','I','J','K 59 GROUP BY A1.JOBNBR ) APC, 60 (SELECT DISTINCT C.LIFETOUCHID,C.PROGRAMID,MD.MARKETINGCODE 61 FROM CURRJOBFACT C,MARKETINGDIM MD WHERE C.SOURCEFISCALYEAR 2004 62 AND C.MARKETINGCODE = MD.MARKETINGCODE ) C, 63 (SELECT JOBNBR,MIN(DEPOSITDATE) FIRSTPOSITIVECASHDATE 64 FROM CASHTXNFACT 65 WHERE SOURCEFISCALYEAR = 2004 GROUP BY JOBNBR ) WV1, 66 (SELECT X.JOBNBR,X.WEEKENDDATE,X.PRELIMYTDESTACCTCMSNAMT 67 FROM WKLYJOBFACT X, 68 (SELECT JOBNBR,MAX(WEEKENDDATE) MAXWEEKENDDATE 69 FROM WKLYJOBFACT WHERE SOURCEFISCALYEAR = 2004 70AND NVL(PRELIMYTDESTACCTCMSNAMT,0) 0 71 GROUP BY JOBNBR ) W1 72 WHERE X.JOBNBR = W1.JOBNBR AND X.WEEKENDDATE = W1.MAXWEEKENDDATE ) WV 73 WHERE CJS.JOBNBR = PS.JOBNBR (+) 74 AND CJS.JOBNBR = APC.JOBNBR (+) 75 AND CJS.MARKETINGCODE = MD.MARKETINGCODE 76 AND CJS.LIFETOUCHID = C.LIFETOUCHID (+) 77 AND CJS.MARKETINGCODE = C.MARKETINGCODE (+) 78 AND CJS.JOBNBR = WV1
RE: Can't insert into partition
Thanks Wolfgang! And thanks to the others who have helped us unravel this problem. Your suggestion put us on the right track. I started running a SQL trace/tkprof, and lo and behold, when the stored procedure submits the SQL, CBO does everything as NESTED LOOPS. The next question is how to induce CBO to consider HASH JOIN? The original query had USE_HASH hints on the subqueries. Somewhere I thought I recalled that you could only put hints on the outer SQL statement -- is that true? So we tried adding the USE_HASH hint to the overall INSERT statement, to no effect. Here is the relevant portion of the tkprof output. Thanks again to eveyone. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] INSERT /*+ APPEND use_hash(CJS, PS, APC, MD, C, WV1, WV) */INTO CURRJOBFACT NOLOGGING ( JOBNBR,SOURCEFISCALYEAR,LIFETOUCHID,PROGRAMID,MARKETINGCODE, PLANTRECEIPTDATE,PHOTOGRAPHYDATE,SHIPDATE,SELLINGMETHODCODE,MDRPRIMARYID, SUBPROGRAMCODE,TERRCODE,SUBTERRCODE,BIDIND,PDKIND,PDKPARTNBR,RETAKEIND, RETAKENBR,SHIPPEDJOBIND,PAIDJOBIND,PAYSTATUSIND,PAIDSHIPPEDJOBIND,PREJOBIND, PLANTCODE,SHOTQTY,MTDSHIPPEDPKGQTY,MTDCHARGEBACKAMT,MTDTERRCMSNAMT, MTDTERREARNINGSAMT,MTDCASHRECEIVEDAMT,MTDCASHRETAINEDAMT,MTDESTACCTCMSNAMT, MTDACCTCMSNPAIDAMT,MTDGROSSCASHAMT,MTDSALESTAXAMT,YTDSHIPPEDPKGQTY, YTDCHARGEBACKAMT,YTDTERRCMSNAMT,YTDTERREARNINGSAMT,YTDCASHRECEIVEDAMT, YTDCASHRETAINEDAMT,YTDESTACCTCMSNAMT,YTDACCTCMSNPAIDAMT,YTDGROSSCASHAMT, YTDSALESTAXAMT,YTDPERFECTSALEAMT,PRELIMYTDSHIPPEDPKGQTY, PRELIMYTDCHARGEBACKAMT,PRELIMYTDTERRCMSNAMT,PRELIMYTDTERREARNINGSAMT, PRELIMYTDCASHRECEIVEDAMT,PRELIMYTDCASHRETAINEDAMT,PRELIMYTDESTACCTCMSNAMT, PRELIMYTDACCTCMSNPAIDAMT,PRELIMYTDGROSSCASHAMT,PRELIMYTDSALESTAXAMT, PRELIMYTDPERFECTSALEAMT,YTDJTEPAIDPKGQTY,YTDPAIDPKGQTY,YTDUNPAIDPKGQTY, YTDXNOPURCHASEQTY,PRELIMYTDPAIDPKGSHIPQTY,PRELIMYTDUNPAIDPKGSHIPQTY, PROOFPOSEQTY,PROOFCOUNTQTY,EXTRACTDATE,ORIGINALRECEIVEDDATE,CMSNSTATUSCODE, FIRSTPOSITIVECASHDATE,RENEWALSTATUSCODE,ORIGESTACCTCMSNAMT,AVGPKGPRICE ) SELECT /*+ use_hash(cjs, ps. md, a, o, x, wv, apc, wv1) */CJS.JOBNBR, (columns omitted) PS.PRELIMYTDESTACCTCMSNAMT)) ORIGESTACCTCMSNAMT,APC.AVGPKGPRICE FROM CURRJOB_STAGE CJS,PERIOD_STAGE PS,MARKETINGDIM MD,(SELECT A1.JOBNBR, DECODE(SUM(PAIDOFFERQTY),0,0,SUM(A1.OFFERPRICEAMT * A1.PAIDOFFERQTY ) / SUM(A1.PAIDOFFERQTY) ) AVGPKGPRICE FROM (SELECT DISTINCT A.JOBNBR, A.OFFERNAME,A.PAIDOFFERQTY,A.OFFERPRICEAMT FROM OFFERLOAD_STAGE A WHERE A.OFFERNAME IN ( 'A','B','C','D','E','F','G','H','I','J','K' )) A1 GROUP BY A1.JOBNBR ) APC,(SELECT DISTINCT C.LIFETOUCHID,C.PROGRAMID, MD.MARKETINGCODE FROM CURRJOBFACT C,MARKETINGDIM MD WHERE C.SOURCEFISCALYEAR :b1 AND C.MARKETINGCODE = MD.MARKETINGCODE ) C, (SELECT JOBNBR,MIN(DEPOSITDATE) FIRSTPOSITIVECASHDATE FROM CASHTXNFACT WHERE SOURCEFISCALYEAR = :b1 GROUP BY JOBNBR ) WV1,(SELECT X.JOBNBR, X.WEEKENDDATE,X.PRELIMYTDESTACCTCMSNAMT FROM WKLYJOBFACT X,(SELECT JOBNBR, MAX(WEEKENDDATE) MAXWEEKENDDATE FROM WKLYJOBFACT WHERE SOURCEFISCALYEAR = :b1 AND NVL(PRELIMYTDESTACCTCMSNAMT,0) 0 GROUP BY JOBNBR ) W1 WHERE X.JOBNBR = W1.JOBNBR AND X.WEEKENDDATE = W1.MAXWEEKENDDATE ) WV WHERE CJS.JOBNBR = PS.JOBNBR (+)AND CJS.JOBNBR = APC.JOBNBR (+)AND CJS.MARKETINGCODE = MD.MARKETINGCODE AND CJS.LIFETOUCHID = C.LIFETOUCHID (+)AND CJS.MARKETINGCODE = C.MARKETINGCODE (+)AND CJS.JOBNBR = WV1.JOBNBR (+)AND CJS.JOBNBR = WV.JOBNBR (+) call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 0.01 0.01 0 0 0 0 Execute 1351.171349.5112086871212777866 0 Fetch0 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total2351.181349.5212086871212777866 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 21 (recursive depth: 1) Rows Row Source Operation --- --- 0 LOAD AS SELECT 0 NESTED LOOPS OUTER 1NESTED LOOPS OUTER 2 NESTED LOOPS OUTER 2 HASH JOIN OUTER 6412 HASH JOIN OUTER 6412HASH JOIN 246 TABLE ACCESS FULL MARKETINGDIM 6412 TABLE ACCESS FULL CURRJOB_STAGE 3093VIEW 3093 SORT GROUP BY 13728 VIEW 13728 SORT UNIQUE 35929TABLE ACCESS FULL OFFERLOAD_STAGE 47 VIEW 47SORT UNIQUE 177 NESTED LOOPS 178 PARTITION RANGE ITERATOR PARTITION: KEY (null) 180 TABLE ACCESS FULL CURRJOBFACT PARTITION: KEY (null) 177 INDEX UNIQUE SCAN (object id 2941) 2 TABLE ACCESS FULL PERIOD_STAGE 0
RE: Can't insert into partition
Is the sql you posted the exact sql as it is executed in the PLSQL procedure, i.e. is the procedure using literals such as 2004 in the predicates for sourcefiscalyear, or is it really using a bindvariable? At 02:29 PM 8/6/2003 -0800, you wrote: Henry - Thanks. I feel like I'm getting an education today on the Oracle Wait Interface today. Nothing like a live problem for everything to make sense. Thanks for pointing out that I could find the table. It is our WKLYJOBFACT table. Not one we suspected. We have been doing an EXPLAIN PLAN by extracting the SQL from the stored procedure. I posted that. But when the SQL is extracted from the stored procedure, it runs just fine. Does anyone know how to get the explain plan that the PL/SQL procedure is seeing? Thanks to everyone for helping narrow the problem down this far. It has kept me from rebuilding the table which probably would have accomplished nothing. [...] SQL explain plan for 2 INSERT /*+ APPEND */INTO CURRJOBFACT NOLOGGING 3 ( bunch of columns ) 21SELECT /*+ use_hash(cjs, ps. md, a, o, x, wv, apc, wv1) */ 22 CJS.JOBNBR,CJS.SOURCEFISCALYEAR,CJS.LIFETOUCHID,MD.PROGRAMID, 23 CJS.MARKETINGCODE,CJS.PLANTRECEIPTDATE,CJS.PHOTOGRAPHYDATE, 24 CJS.SHIPDATE,CJS.SELLINGMETHODCODE,CJS.MDRPRIMARYID,CJS.SUBPROGRAMCODE, 25 CJS.TERRCODE,CJS.SUBTERRCODE,CJS.BIDIND,CJS.PDKIND,CJS.PDKPARTNBR, 26 CJS.RETAKEIND, 27 TO_NUMBER(TRANSLATE(UPPER(SUBSTR(CJS.JOBNBR,10,1)),'ABCDEFGHIJKLMNOPQRSTUV , 28DECODE(TO_DATE(CJS.SHIPDATE,'/MM/DD'), NULL ,'N','Y'), 29DECODE(NVL(GREATEST(CJS.YTDCASHRECEIVEDAMT,0),0),0, 30DECODE(CJS.PAYSTATUSIND,'F','Y','N'),'Y') PAIDJOBIND, 31 CJS.PAYSTATUSIND,PS.PAIDSHIPPEDJOBIND, 32 DECODE(SUBSTR(CJS.JOBNBR,9,1),'I','Y','N') PREJOBIND,CJS.PLANTCODE, 33 CJS.SHOTQTY,PS.MTDSHIPPEDPKGQTY,PS.MTDCHARGEBACKAMT,PS.MTDTERRCMSNAMT, 34 NVL(PS.MTDTERRCMSNAMT,0) - NVL(PS.MTDCHARGEBACKAMT,0) ,PS.MTDCASHRECEIVEDA, 35 PS.MTDCASHRETAINEDAMT,PS.MTDESTACCTCMSNAMT,PS.MTDACCTCMSNPAIDAMT, 36 PS.MTDGROSSCASHAMT,PS.MTDSALESTAXAMT,CJS.YTDSHIPPEDPKGQTY,CJS.YTDCHARGEBAC , 37 CJS.YTDTERRCMSNAMT,CJS.YTDTERREARNINGSAMT,CJS.YTDCASHRECEIVEDAMT, 38 CJS.YTDCASHRETAINEDAMT,CJS.YTDESTACCTCMSNAMT,CJS.YTDACCTCMSNPAIDAMT, 39 CJS.YTDCASHRECEIVEDAMT +YTDESTACCTCMSNAMT + YTDACCTCMSNPAIDAMT , 40 CJS.YTDSALESTAXAMT,CJS.YTDPERFECTSALEAMT,PS.PRELIMYTDSHIPPEDPKGQTY, 41 PS.PRELIMYTDCHARGEBACKAMT,PS.PRELIMYTDTERRCMSNAMT, 42 PS.PRELIMYTDTERRCMSNAMT - PS.PRELIMYTDCHARGEBACKAMT ,PS.PRELIMYTDCASHRECEI, 43 PS.PRELIMYTDCASHRETAINEDAMT,PS.PRELIMYTDESTACCTCMSNAMT, 44 PS.PRELIMYTDACCTCMSNPAIDAMT,PS.PRELIMYTDGROSSCASHAMT,PS.PRELIMYTDSALESTAXA , 45 PS.PRELIMYTDPERFECTSALEAMT,CJS.YTDJTEPAIDPKGQTY,CJS.YTDPAIDPKGQTY, 46 CJS.YTDUNPAIDPKGQTY,CJS.YTDXNOPURCHASEQTY,PS.YTDPAIDPKGSHIPQTY, 47 PS.YTDUNPAIDPKGSHIPQTY,CJS.PROOFPOSEQTY,CJS.PROOFCOUNTQTY,CJS.EXTRACTDATE, 48 CJS.ORIGINALRECEIVEDDATE,PS.CMSNSTATUSCODE,WV1.FIRSTPOSITIVECASHDATE, 49 DECODE(C.LIFETOUCHID, NULL ,'New','Retained') RENEWALSTATUSCODE, 50 NVL(WV.PRELIMYTDESTACCTCMSNAMT, 51 DECODE(NVL(PS.PRELIMYTDESTACCTCMSNAMT,0),0, NULL ,PS.PRELIMYTDESTACCTCMSNA, 52 APC.AVGPKGPRICE 53 FROM CURRJOB_STAGE CJS,PERIOD_STAGE PS,MARKETINGDIM MD, 54 (SELECT A1.JOBNBR,DECODE(SUM(PAIDOFFERQTY),0,0, 55 SUM(A1.OFFERPRICEAMT * A1.PAIDOFFERQTY ) / SUM(A1.PAIDOFFERQTY) ) AVGPKGPR 56 FROM (SELECT DISTINCT A.JOBNBR,A.OFFERNAME,A.PAIDOFFERQTY,A.OFFERPRICEAT 57 FROM OFFERLOAD_STAGE A 58 WHERE A.OFFERNAME IN ( 'A','B','C','D','E','F','G','H','I','J','K 59 GROUP BY A1.JOBNBR ) APC, 60 (SELECT DISTINCT C.LIFETOUCHID,C.PROGRAMID,MD.MARKETINGCODE 61 FROM CURRJOBFACT C,MARKETINGDIM MD WHERE C.SOURCEFISCALYEAR 2004 62 AND C.MARKETINGCODE = MD.MARKETINGCODE ) C, 63 (SELECT JOBNBR,MIN(DEPOSITDATE) FIRSTPOSITIVECASHDATE 64 FROM CASHTXNFACT 65 WHERE SOURCEFISCALYEAR = 2004 GROUP BY JOBNBR ) WV1, 66 (SELECT X.JOBNBR,X.WEEKENDDATE,X.PRELIMYTDESTACCTCMSNAMT 67 FROM WKLYJOBFACT X, 68 (SELECT JOBNBR,MAX(WEEKENDDATE) MAXWEEKENDDATE 69 FROM WKLYJOBFACT WHERE SOURCEFISCALYEAR = 2004 70AND NVL(PRELIMYTDESTACCTCMSNAMT,0) 0 71 GROUP BY JOBNBR ) W1 72 WHERE X.JOBNBR = W1.JOBNBR AND X.WEEKENDDATE = W1.MAXWEEKENDDATE ) WV 73 WHERE CJS.JOBNBR = PS.JOBNBR (+) 74 AND CJS.JOBNBR = APC.JOBNBR (+) 75 AND CJS.MARKETINGCODE = MD.MARKETINGCODE 76 AND CJS.LIFETOUCHID = C.LIFETOUCHID (+) 77 AND CJS.MARKETINGCODE = C.MARKETINGCODE (+) 78 AND CJS.JOBNBR = WV1.JOBNBR (+) 79 AND CJS.JOBNBR = WV.JOBNBR (+); Explained. SQL @explain Query Plan -- -- INSERT STATEMENT Cost = 181253 LOAD AS SELECT HASH JOIN OUTER HASH JOIN OUTER HASH JOIN OUTER HASH JOIN OUTER HASH JOIN OUTER HASH JOIN TABLE
RE: Can't insert into partition
More information: - Running the insert statement from SQL*Plus works fine. - Normally this is run by executing a stored procedure that is in a package. Specifically, a master procedure calls a series of procedures within the package. The first 5 work fine, then this one doesn't complete. - Next we plan to attempt directly executing this stored procedure. - Stephane, thanks for your suggestion. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, August 06, 2003 10:40 AM To: Multiple recipients of list ORACLE-L We have a situation where a process can't insert into a partition of a partitioned table. The process just keeps running. - A stored procedure executes a SQL insert statement (listing below). - It normally completes in 30 minutes, but now just runs for hours. - Oracle 8.1.6 on Dec/Compaq/HP Alpha - In tracing the process, it is waiting on db file scattered read. - This is a relatively new process, but it has completed successfully twice in production and numerous times in test. - I was able to perform a simple insert into the partition. - I created a test table (non-partitioned) and the process worked fine there, completing in the normal 30 minutes. - I rebuilt the partition (drop partition and create partition), to no effect. I'm stumped and looking for any suggestions: Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] Here is the SQL and the explain plan: SQL explain plan for 2 INSERT /*+ APPEND */INTO CURRJOBFACT NOLOGGING 3 ( bunch of columns ) 21SELECT /*+ use_hash(cjs, ps. md, a, o, x, wv, apc, wv1) */ 22 CJS.JOBNBR,CJS.SOURCEFISCALYEAR,CJS.LIFETOUCHID,MD.PROGRAMID, 23 CJS.MARKETINGCODE,CJS.PLANTRECEIPTDATE,CJS.PHOTOGRAPHYDATE, 24 CJS.SHIPDATE,CJS.SELLINGMETHODCODE,CJS.MDRPRIMARYID,CJS.SUBPROGRAMCODE, 25 CJS.TERRCODE,CJS.SUBTERRCODE,CJS.BIDIND,CJS.PDKIND,CJS.PDKPARTNBR, 26 CJS.RETAKEIND, 27 TO_NUMBER(TRANSLATE(UPPER(SUBSTR(CJS.JOBNBR,10,1)),'ABCDEFGHIJKLMNOPQRSTUV , 28DECODE(TO_DATE(CJS.SHIPDATE,'/MM/DD'), NULL ,'N','Y'), 29DECODE(NVL(GREATEST(CJS.YTDCASHRECEIVEDAMT,0),0),0, 30DECODE(CJS.PAYSTATUSIND,'F','Y','N'),'Y') PAIDJOBIND, 31 CJS.PAYSTATUSIND,PS.PAIDSHIPPEDJOBIND, 32 DECODE(SUBSTR(CJS.JOBNBR,9,1),'I','Y','N') PREJOBIND,CJS.PLANTCODE, 33 CJS.SHOTQTY,PS.MTDSHIPPEDPKGQTY,PS.MTDCHARGEBACKAMT,PS.MTDTERRCMSNAMT, 34 NVL(PS.MTDTERRCMSNAMT,0) - NVL(PS.MTDCHARGEBACKAMT,0) ,PS.MTDCASHRECEIVEDA, 35 PS.MTDCASHRETAINEDAMT,PS.MTDESTACCTCMSNAMT,PS.MTDACCTCMSNPAIDAMT, 36 PS.MTDGROSSCASHAMT,PS.MTDSALESTAXAMT,CJS.YTDSHIPPEDPKGQTY,CJS.YTDCHARGEBAC , 37 CJS.YTDTERRCMSNAMT,CJS.YTDTERREARNINGSAMT,CJS.YTDCASHRECEIVEDAMT, 38 CJS.YTDCASHRETAINEDAMT,CJS.YTDESTACCTCMSNAMT,CJS.YTDACCTCMSNPAIDAMT, 39 CJS.YTDCASHRECEIVEDAMT +YTDESTACCTCMSNAMT + YTDACCTCMSNPAIDAMT , 40 CJS.YTDSALESTAXAMT,CJS.YTDPERFECTSALEAMT,PS.PRELIMYTDSHIPPEDPKGQTY, 41 PS.PRELIMYTDCHARGEBACKAMT,PS.PRELIMYTDTERRCMSNAMT, 42 PS.PRELIMYTDTERRCMSNAMT - PS.PRELIMYTDCHARGEBACKAMT ,PS.PRELIMYTDCASHRECEI, 43 PS.PRELIMYTDCASHRETAINEDAMT,PS.PRELIMYTDESTACCTCMSNAMT, 44 PS.PRELIMYTDACCTCMSNPAIDAMT,PS.PRELIMYTDGROSSCASHAMT,PS.PRELIMYTDSALESTAXA , 45 PS.PRELIMYTDPERFECTSALEAMT,CJS.YTDJTEPAIDPKGQTY,CJS.YTDPAIDPKGQTY, 46 CJS.YTDUNPAIDPKGQTY,CJS.YTDXNOPURCHASEQTY,PS.YTDPAIDPKGSHIPQTY, 47 PS.YTDUNPAIDPKGSHIPQTY,CJS.PROOFPOSEQTY,CJS.PROOFCOUNTQTY,CJS.EXTRACTDATE, 48 CJS.ORIGINALRECEIVEDDATE,PS.CMSNSTATUSCODE,WV1.FIRSTPOSITIVECASHDATE, 49 DECODE(C.LIFETOUCHID, NULL ,'New','Retained') RENEWALSTATUSCODE, 50 NVL(WV.PRELIMYTDESTACCTCMSNAMT, 51 DECODE(NVL(PS.PRELIMYTDESTACCTCMSNAMT,0),0, NULL ,PS.PRELIMYTDESTACCTCMSNA, 52 APC.AVGPKGPRICE 53 FROM CURRJOB_STAGE CJS,PERIOD_STAGE PS,MARKETINGDIM MD, 54 (SELECT A1.JOBNBR,DECODE(SUM(PAIDOFFERQTY),0,0, 55 SUM(A1.OFFERPRICEAMT * A1.PAIDOFFERQTY ) / SUM(A1.PAIDOFFERQTY) ) AVGPKGPR 56 FROM (SELECT DISTINCT A.JOBNBR,A.OFFERNAME,A.PAIDOFFERQTY,A.OFFERPRICEAT 57 FROM OFFERLOAD_STAGE A 58 WHERE A.OFFERNAME IN ( 'A','B','C','D','E','F','G','H','I','J','K 59 GROUP BY A1.JOBNBR ) APC, 60 (SELECT DISTINCT C.LIFETOUCHID,C.PROGRAMID,MD.MARKETINGCODE 61 FROM CURRJOBFACT C,MARKETINGDIM MD WHERE C.SOURCEFISCALYEAR 2004 62 AND C.MARKETINGCODE = MD.MARKETINGCODE ) C, 63 (SELECT JOBNBR,MIN(DEPOSITDATE) FIRSTPOSITIVECASHDATE 64 FROM CASHTXNFACT 65 WHERE SOURCEFISCALYEAR = 2004 GROUP BY JOBNBR ) WV1, 66 (SELECT X.JOBNBR,X.WEEKENDDATE,X.PRELIMYTDESTACCTCMSNAMT 67 FROM WKLYJOBFACT X, 68 (SELECT JOBNBR,MAX(WEEKENDDATE) MAXWEEKENDDATE 69 FROM WKLYJOBFACT WHERE SOURCEFISCALYEAR = 2004 70AND NVL(PRELIMYTDESTACCTCMSNAMT,0) 0 71 GROUP BY JOBNBR ) W1 72 WHERE X.JOBNBR = W1.JOBNBR AND X.WEEKENDDATE = W1.MAXWEEKENDDATE ) WV 73 WHERE CJS.JOBNBR = PS.JOBNBR (+) 74
RE: Can't insert into partition
No, you can put a hint in inner sql and subselects. Some hints you NEED to put on a subselect to make any sense. Is that sql verbatim? The hint has a syntax error. There is a dot rather than a comma after ps which - pooof - may turn the princely hint into an ugly toad (no pun intended) comment. Another thnig you can try, since you are using bind variables, is to jack up db_file_multiblock_read_count, hash_multiblock_io_count, hash_area_size, optimizer_index_cost_adj (to 1), create a stored outline of the sql - hopefully it will use hash joins with all the help, and then revert to the normal init_ora settings and tell oracle to use the stored outline. At 02:19 PM 8/7/2003 -0800, you wrote: Thanks Wolfgang! And thanks to the others who have helped us unravel this problem. Your suggestion put us on the right track. I started running a SQL trace/tkprof, and lo and behold, when the stored procedure submits the SQL, CBO does everything as NESTED LOOPS. The next question is how to induce CBO to consider HASH JOIN? The original query had USE_HASH hints on the subqueries. Somewhere I thought I recalled that you could only put hints on the outer SQL statement -- is that true? So we tried adding the USE_HASH hint to the overall INSERT statement, to no effect. Here is the relevant portion of the tkprof output. Thanks again to eveyone. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] INSERT /*+ APPEND use_hash(CJS, PS, APC, MD, C, WV1, WV) */INTO CURRJOBFACT NOLOGGING ( JOBNBR,SOURCEFISCALYEAR,LIFETOUCHID,PROGRAMID,MARKETINGCODE, PLANTRECEIPTDATE,PHOTOGRAPHYDATE,SHIPDATE,SELLINGMETHODCODE,MDRPRIMARYID, SUBPROGRAMCODE,TERRCODE,SUBTERRCODE,BIDIND,PDKIND,PDKPARTNBR,RETAKEIND, RETAKENBR,SHIPPEDJOBIND,PAIDJOBIND,PAYSTATUSIND,PAIDSHIPPEDJOBIND,PREJOBIND, PLANTCODE,SHOTQTY,MTDSHIPPEDPKGQTY,MTDCHARGEBACKAMT,MTDTERRCMSNAMT, MTDTERREARNINGSAMT,MTDCASHRECEIVEDAMT,MTDCASHRETAINEDAMT,MTDESTACCTCMSNAMT, MTDACCTCMSNPAIDAMT,MTDGROSSCASHAMT,MTDSALESTAXAMT,YTDSHIPPEDPKGQTY, YTDCHARGEBACKAMT,YTDTERRCMSNAMT,YTDTERREARNINGSAMT,YTDCASHRECEIVEDAMT, YTDCASHRETAINEDAMT,YTDESTACCTCMSNAMT,YTDACCTCMSNPAIDAMT,YTDGROSSCASHAMT, YTDSALESTAXAMT,YTDPERFECTSALEAMT,PRELIMYTDSHIPPEDPKGQTY, PRELIMYTDCHARGEBACKAMT,PRELIMYTDTERRCMSNAMT,PRELIMYTDTERREARNINGSAMT, PRELIMYTDCASHRECEIVEDAMT,PRELIMYTDCASHRETAINEDAMT,PRELIMYTDESTACCTCMSNAMT, PRELIMYTDACCTCMSNPAIDAMT,PRELIMYTDGROSSCASHAMT,PRELIMYTDSALESTAXAMT, PRELIMYTDPERFECTSALEAMT,YTDJTEPAIDPKGQTY,YTDPAIDPKGQTY,YTDUNPAIDPKGQTY, YTDXNOPURCHASEQTY,PRELIMYTDPAIDPKGSHIPQTY,PRELIMYTDUNPAIDPKGSHIPQTY, PROOFPOSEQTY,PROOFCOUNTQTY,EXTRACTDATE,ORIGINALRECEIVEDDATE,CMSNSTATUSCODE, FIRSTPOSITIVECASHDATE,RENEWALSTATUSCODE,ORIGESTACCTCMSNAMT,AVGPKGPRICE ) SELECT /*+ use_hash(cjs, ps. md, a, o, x, wv, apc, wv1) */CJS.JOBNBR, (columns omitted) PS.PRELIMYTDESTACCTCMSNAMT)) ORIGESTACCTCMSNAMT,APC.AVGPKGPRICE FROM CURRJOB_STAGE CJS,PERIOD_STAGE PS,MARKETINGDIM MD,(SELECT A1.JOBNBR, DECODE(SUM(PAIDOFFERQTY),0,0,SUM(A1.OFFERPRICEAMT * A1.PAIDOFFERQTY ) / SUM(A1.PAIDOFFERQTY) ) AVGPKGPRICE FROM (SELECT DISTINCT A.JOBNBR, A.OFFERNAME,A.PAIDOFFERQTY,A.OFFERPRICEAMT FROM OFFERLOAD_STAGE A WHERE A.OFFERNAME IN ( 'A','B','C','D','E','F','G','H','I','J','K' )) A1 GROUP BY A1.JOBNBR ) APC,(SELECT DISTINCT C.LIFETOUCHID,C.PROGRAMID, MD.MARKETINGCODE FROM CURRJOBFACT C,MARKETINGDIM MD WHERE C.SOURCEFISCALYEAR :b1 AND C.MARKETINGCODE = MD.MARKETINGCODE ) C, (SELECT JOBNBR,MIN(DEPOSITDATE) FIRSTPOSITIVECASHDATE FROM CASHTXNFACT WHERE SOURCEFISCALYEAR = :b1 GROUP BY JOBNBR ) WV1,(SELECT X.JOBNBR, X.WEEKENDDATE,X.PRELIMYTDESTACCTCMSNAMT FROM WKLYJOBFACT X,(SELECT JOBNBR, MAX(WEEKENDDATE) MAXWEEKENDDATE FROM WKLYJOBFACT WHERE SOURCEFISCALYEAR = :b1 AND NVL(PRELIMYTDESTACCTCMSNAMT,0) 0 GROUP BY JOBNBR ) W1 WHERE X.JOBNBR = W1.JOBNBR AND X.WEEKENDDATE = W1.MAXWEEKENDDATE ) WV WHERE CJS.JOBNBR = PS.JOBNBR (+)AND CJS.JOBNBR = APC.JOBNBR (+)AND CJS.MARKETINGCODE = MD.MARKETINGCODE AND CJS.LIFETOUCHID = C.LIFETOUCHID (+)AND CJS.MARKETINGCODE = C.MARKETINGCODE (+)AND CJS.JOBNBR = WV1.JOBNBR (+)AND CJS.JOBNBR = WV.JOBNBR (+) call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 0.01 0.01 0 0 0 0 Execute 1351.171349.5112086871212777866 0 Fetch0 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total2351.181349.5212086871212777866 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 21 (recursive depth: 1) Rows Row Source Operation --- --- 0 LOAD AS SELECT 0 NESTED LOOPS OUTER 1
RE: Can't insert into partition
Dennis, Could you plese post the v$session_wait. Do you have a 10046 trace? Henry -Original Message- DENNIS WILLIAMS Sent: Wednesday, August 06, 2003 11:40 AM To: Multiple recipients of list ORACLE-L We have a situation where a process can't insert into a partition of a partitioned table. The process just keeps running. - A stored procedure executes a SQL insert statement (listing below). - It normally completes in 30 minutes, but now just runs for hours. - Oracle 8.1.6 on Dec/Compaq/HP Alpha - In tracing the process, it is waiting on db file scattered read. - This is a relatively new process, but it has completed successfully twice in production and numerous times in test. - I was able to perform a simple insert into the partition. - I created a test table (non-partitioned) and the process worked fine there, completing in the normal 30 minutes. - I rebuilt the partition (drop partition and create partition), to no effect. I'm stumped and looking for any suggestions: Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] Here is the SQL and the explain plan: SQL explain plan for 2 INSERT /*+ APPEND */INTO CURRJOBFACT NOLOGGING 3 ( bunch of columns ) 21SELECT /*+ use_hash(cjs, ps. md, a, o, x, wv, apc, wv1) */ 22 CJS.JOBNBR,CJS.SOURCEFISCALYEAR,CJS.LIFETOUCHID,MD.PROGRAMID, 23 CJS.MARKETINGCODE,CJS.PLANTRECEIPTDATE,CJS.PHOTOGRAPHYDATE, 24 CJS.SHIPDATE,CJS.SELLINGMETHODCODE,CJS.MDRPRIMARYID,CJS.SUBPROGRAMCODE, 25 CJS.TERRCODE,CJS.SUBTERRCODE,CJS.BIDIND,CJS.PDKIND,CJS.PDKPARTNBR, 26 CJS.RETAKEIND, 27 TO_NUMBER(TRANSLATE(UPPER(SUBSTR(CJS.JOBNBR,10,1)),'ABCDEFGHIJKLMNOPQRSTUV , 28DECODE(TO_DATE(CJS.SHIPDATE,'/MM/DD'), NULL ,'N','Y'), 29DECODE(NVL(GREATEST(CJS.YTDCASHRECEIVEDAMT,0),0),0, 30DECODE(CJS.PAYSTATUSIND,'F','Y','N'),'Y') PAIDJOBIND, 31 CJS.PAYSTATUSIND,PS.PAIDSHIPPEDJOBIND, 32 DECODE(SUBSTR(CJS.JOBNBR,9,1),'I','Y','N') PREJOBIND,CJS.PLANTCODE, 33 CJS.SHOTQTY,PS.MTDSHIPPEDPKGQTY,PS.MTDCHARGEBACKAMT,PS.MTDTERRCMSNAMT, 34 NVL(PS.MTDTERRCMSNAMT,0) - NVL(PS.MTDCHARGEBACKAMT,0) ,PS.MTDCASHRECEIVEDA, 35 PS.MTDCASHRETAINEDAMT,PS.MTDESTACCTCMSNAMT,PS.MTDACCTCMSNPAIDAMT, 36 PS.MTDGROSSCASHAMT,PS.MTDSALESTAXAMT,CJS.YTDSHIPPEDPKGQTY,CJS.YTDCHARGEBAC , 37 CJS.YTDTERRCMSNAMT,CJS.YTDTERREARNINGSAMT,CJS.YTDCASHRECEIVEDAMT, 38 CJS.YTDCASHRETAINEDAMT,CJS.YTDESTACCTCMSNAMT,CJS.YTDACCTCMSNPAIDAMT, 39 CJS.YTDCASHRECEIVEDAMT +YTDESTACCTCMSNAMT + YTDACCTCMSNPAIDAMT , 40 CJS.YTDSALESTAXAMT,CJS.YTDPERFECTSALEAMT,PS.PRELIMYTDSHIPPEDPKGQTY, 41 PS.PRELIMYTDCHARGEBACKAMT,PS.PRELIMYTDTERRCMSNAMT, 42 PS.PRELIMYTDTERRCMSNAMT - PS.PRELIMYTDCHARGEBACKAMT ,PS.PRELIMYTDCASHRECEI, 43 PS.PRELIMYTDCASHRETAINEDAMT,PS.PRELIMYTDESTACCTCMSNAMT, 44 PS.PRELIMYTDACCTCMSNPAIDAMT,PS.PRELIMYTDGROSSCASHAMT,PS.PRELIMYTDSALESTAXA , 45 PS.PRELIMYTDPERFECTSALEAMT,CJS.YTDJTEPAIDPKGQTY,CJS.YTDPAIDPKGQTY, 46 CJS.YTDUNPAIDPKGQTY,CJS.YTDXNOPURCHASEQTY,PS.YTDPAIDPKGSHIPQTY, 47 PS.YTDUNPAIDPKGSHIPQTY,CJS.PROOFPOSEQTY,CJS.PROOFCOUNTQTY,CJS.EXTRACTDATE, 48 CJS.ORIGINALRECEIVEDDATE,PS.CMSNSTATUSCODE,WV1.FIRSTPOSITIVECASHDATE, 49 DECODE(C.LIFETOUCHID, NULL ,'New','Retained') RENEWALSTATUSCODE, 50 NVL(WV.PRELIMYTDESTACCTCMSNAMT, 51 DECODE(NVL(PS.PRELIMYTDESTACCTCMSNAMT,0),0, NULL ,PS.PRELIMYTDESTACCTCMSNA, 52 APC.AVGPKGPRICE 53 FROM CURRJOB_STAGE CJS,PERIOD_STAGE PS,MARKETINGDIM MD, 54 (SELECT A1.JOBNBR,DECODE(SUM(PAIDOFFERQTY),0,0, 55 SUM(A1.OFFERPRICEAMT * A1.PAIDOFFERQTY ) / SUM(A1.PAIDOFFERQTY) ) AVGPKGPR 56 FROM (SELECT DISTINCT A.JOBNBR,A.OFFERNAME,A.PAIDOFFERQTY,A.OFFERPRICEAT 57 FROM OFFERLOAD_STAGE A 58 WHERE A.OFFERNAME IN ( 'A','B','C','D','E','F','G','H','I','J','K 59 GROUP BY A1.JOBNBR ) APC, 60 (SELECT DISTINCT C.LIFETOUCHID,C.PROGRAMID,MD.MARKETINGCODE 61 FROM CURRJOBFACT C,MARKETINGDIM MD WHERE C.SOURCEFISCALYEAR 2004 62 AND C.MARKETINGCODE = MD.MARKETINGCODE ) C, 63 (SELECT JOBNBR,MIN(DEPOSITDATE) FIRSTPOSITIVECASHDATE 64 FROM CASHTXNFACT 65 WHERE SOURCEFISCALYEAR = 2004 GROUP BY JOBNBR ) WV1, 66 (SELECT X.JOBNBR,X.WEEKENDDATE,X.PRELIMYTDESTACCTCMSNAMT 67 FROM WKLYJOBFACT X, 68 (SELECT JOBNBR,MAX(WEEKENDDATE) MAXWEEKENDDATE 69 FROM WKLYJOBFACT WHERE SOURCEFISCALYEAR = 2004 70AND NVL(PRELIMYTDESTACCTCMSNAMT,0) 0 71 GROUP BY JOBNBR ) W1 72 WHERE X.JOBNBR = W1.JOBNBR AND X.WEEKENDDATE = W1.MAXWEEKENDDATE ) WV 73 WHERE CJS.JOBNBR = PS.JOBNBR (+) 74 AND CJS.JOBNBR = APC.JOBNBR (+) 75 AND CJS.MARKETINGCODE = MD.MARKETINGCODE 76 AND CJS.LIFETOUCHID = C.LIFETOUCHID (+) 77 AND CJS.MARKETINGCODE = C.MARKETINGCODE (+) 78 AND CJS.JOBNBR = WV1.JOBNBR (+) 79 AND CJS.JOBNBR = WV.JOBNBR (+); Explained. SQL @explain Query Plan -- -- INSERT STATEMENT Cost = 181253 LOAD AS SELECT
RE: Can't insert into partition
=197 p2=103877 p3=8 WAIT #14: nam='db file scattered read' ela= 3 p1=197 p2=103885 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103893 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103901 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103909 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=103917 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103925 p3=8 WAIT #14: nam='db file scattered read' ela= 3 p1=197 p2=103933 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103941 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103949 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103957 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103965 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103973 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103981 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103989 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103997 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104005 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=104013 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104021 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104029 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=104037 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=104045 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=104053 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=104061 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=104069 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=104077 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=104085 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=104093 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=104101 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=104109 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104117 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=104125 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104133 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104141 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104149 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104157 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=104165 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104173 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104181 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104189 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104197 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104205 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104213 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104221 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104229 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104237 p3=8 -Original Message- Sent: Wednesday, August 06, 2003 12:20 PM To: Multiple recipients of list ORACLE-L Dennis, Could you plese post the v$session_wait. Do you have a 10046 trace? Henry -Original Message- DENNIS WILLIAMS Sent: Wednesday, August 06, 2003 11:40 AM To: Multiple recipients of list ORACLE-L We have a situation where a process can't insert into a partition of a partitioned table. The process just keeps running. - A stored procedure executes a SQL insert statement (listing below). - It normally completes in 30 minutes, but now just runs for hours. - Oracle 8.1.6 on Dec/Compaq/HP Alpha - In tracing the process, it is waiting on db file scattered read. - This is a relatively new process, but it has completed successfully twice in production and numerous times in test. - I was able to perform a simple insert into the partition. - I created a test table (non-partitioned) and the process worked fine there, completing in the normal 30 minutes. - I rebuilt the partition (drop partition and create partition), to no effect. I'm stumped and looking for any suggestions: Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] Here is the SQL and the explain plan: SQL explain plan for 2 INSERT /*+ APPEND */INTO CURRJOBFACT NOLOGGING 3 ( bunch of columns ) 21SELECT /*+ use_hash(cjs, ps. md, a, o, x, wv, apc, wv1) */ 22 CJS.JOBNBR,CJS.SOURCEFISCALYEAR,CJS.LIFETOUCHID,MD.PROGRAMID, 23 CJS.MARKETINGCODE,CJS.PLANTRECEIPTDATE,CJS.PHOTOGRAPHYDATE, 24 CJS.SHIPDATE,CJS.SELLINGMETHODCODE,CJS.MDRPRIMARYID,CJS.SUBPROGRAMCODE, 25 CJS.TERRCODE,CJS.SUBTERRCODE,CJS.BIDIND,CJS.PDKIND,CJS.PDKPARTNBR, 26 CJS.RETAKEIND, 27 TO_NUMBER(TRANSLATE(UPPER(SUBSTR(CJS.JOBNBR,10,1)),'ABCDEFGHIJKLMNOPQRSTUV , 28DECODE(TO_DATE(CJS.SHIPDATE,'/MM/DD'), NULL ,'N','Y'), 29DECODE(NVL(GREATEST(CJS.YTDCASHRECEIVEDAMT,0),0),0, 30
RE: Can't insert into partition
#14: nam='db file scattered read' ela= 0 p1=197 p2=103789 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103797 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=103805 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103813 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103821 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103829 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103837 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103845 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103853 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103861 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103869 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103877 p3=8 WAIT #14: nam='db file scattered read' ela= 3 p1=197 p2=103885 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103893 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103901 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103909 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=103917 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103925 p3=8 WAIT #14: nam='db file scattered read' ela= 3 p1=197 p2=103933 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103941 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103949 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103957 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103965 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103973 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103981 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103989 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103997 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104005 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=104013 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104021 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104029 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=104037 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=104045 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=104053 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=104061 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=104069 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=104077 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=104085 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=104093 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=104101 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=104109 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104117 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=104125 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104133 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104141 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104149 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104157 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=104165 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104173 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104181 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104189 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104197 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104205 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104213 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104221 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104229 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104237 p3=8 -Original Message- Sent: Wednesday, August 06, 2003 12:20 PM To: Multiple recipients of list ORACLE-L Dennis, Could you plese post the v$session_wait. Do you have a 10046 trace? Henry -Original Message- DENNIS WILLIAMS Sent: Wednesday, August 06, 2003 11:40 AM To: Multiple recipients of list ORACLE-L We have a situation where a process can't insert into a partition of a partitioned table. The process just keeps running. - A stored procedure executes a SQL insert statement (listing below). - It normally completes in 30 minutes, but now just runs for hours. - Oracle 8.1.6 on Dec/Compaq/HP Alpha - In tracing the process, it is waiting on db file scattered read. - This is a relatively new process, but it has completed successfully twice in production and numerous times in test. - I was able to perform a simple insert into the partition. - I created a test table (non-partitioned) and the process worked fine there, completing in the normal 30 minutes. - I rebuilt the partition (drop partition and create partition), to no effect. I'm stumped and looking for any suggestions: Dennis Williams DBA
Re: Maximum Open Cursors on Insert Trigger
Thank you for the replies. It turn out to be a code issue. The developer added olecmd.dispose(), which fixed the problem. [EMAIL PROTECTED] 07/28/03 11:54AM There are some relevant notes on MetaLink that may be of help. Search on 'visual basic ora-1000' Jared On Monday 28 July 2003 07:54, Jay Hostetter wrote: We have a developer that is inserting a large number of records using a VB program. An insert trigger exists on the table. This trigger checks a parent table for records. I know this trigger really is not needed, since a Foreign Key exists to enforce referential integrity, so I plan to disable it (furthermore, it does a SELECT FOR UPDATE, which doesn't make sense). However, I'm trying to understand why the developer keeps getting ORA-01000: maximum open cursors exceeded during the inserts. I've bumped OPEN_CURSORS up to 1000. Is there something unique to VB that could be causing this problem? I've done mass inserts before on tables that have triggers without running into this type of problem. I can't see any problem in the trigger logic, since the cursor is always closed. This is a 9.2.0.3 database on Tru64. Thanks, Jay The source for the trigger is: .. **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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).
Maximum Open Cursors on Insert Trigger
We have a developer that is inserting a large number of records using a VB program. An insert trigger exists on the table. This trigger checks a parent table for records. I know this trigger really is not needed, since a Foreign Key exists to enforce referential integrity, so I plan to disable it (furthermore, it does a SELECT FOR UPDATE, which doesn't make sense). However, I'm trying to understand why the developer keeps getting ORA-01000: maximum open cursors exceeded during the inserts. I've bumped OPEN_CURSORS up to 1000. Is there something unique to VB that could be causing this problem? I've done mass inserts before on tables that have triggers without running into this type of problem. I can't see any problem in the trigger logic, since the cursor is always closed. This is a 9.2.0.3 database on Tru64. Thanks, Jay The source for the trigger is: CREATE OR REPLACE TRIGGER TOPAS.TI_CABLE_PAIRS BEFORE INSERT ON CABLE_PAIRS FOR EACH ROW DECLARE INTEGRITY_ERROR EXCEPTION; ERRNOINTEGER; ERRMSG CHAR(200); DUMMYINTEGER; FOUNDBOOLEAN; -- DECLARATION OF INSERTCHILDPARENTEXIST CONSTRAINT FOR THE PARENT EXCHANGES CURSOR CPK1_CABLE_PAIRS(VAR_EXCHANGE VARCHAR) IS SELECT 1 FROM EXCHANGES WHERE EXCHANGE = VAR_EXCHANGE AND VAR_EXCHANGE IS NOT NULL FOR UPDATE OF EXCHANGE; BEGIN -- PARENT EXCHANGES MUST EXIST WHEN INSERTING A CHILD IN CABLE_PAIRS IF :NEW.EXCHANGE IS NOT NULL THEN OPEN CPK1_CABLE_PAIRS(:NEW.EXCHANGE); FETCH CPK1_CABLE_PAIRS INTO DUMMY; FOUND := CPK1_CABLE_PAIRS%FOUND; CLOSE CPK1_CABLE_PAIRS; IF NOT FOUND THEN ERRNO := -20002; ERRMSG := 'Parent does not exist in EXCHANGES. Cannot create child in CABLE_PAIRS.'; RAISE INTEGRITY_ERROR; END IF; END IF; -- ERRORS HANDLING EXCEPTION WHEN INTEGRITY_ERROR THEN RAISE_APPLICATION_ERROR(ERRNO, ERRMSG); END; / **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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: Maximum Open Cursors on Insert Trigger
Title: RE: Maximum Open Cursors on Insert Trigger Just for a more _completeness_ I'd put a curser close statement in the exception clause ... I agree with you that for-update is a no no in this context. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Jay Hostetter [mailto:[EMAIL PROTECTED]] Sent: Monday, July 28, 2003 10:54 AM To: Multiple recipients of list ORACLE-L Subject: Maximum Open Cursors on Insert Trigger We have a developer that is inserting a large number of records using a VB program. An insert trigger exists on the table. This trigger checks a parent table for records. I know this trigger really is not needed, since a Foreign Key exists to enforce referential integrity, so I plan to disable it (furthermore, it does a SELECT FOR UPDATE, which doesn't make sense). However, I'm trying to understand why the developer keeps getting ORA-01000: maximum open cursors exceeded during the inserts. I've bumped OPEN_CURSORS up to 1000. Is there something unique to VB that could be causing this problem? I've done mass inserts before on tables that have triggers without running into this type of problem. I can't see any problem in the trigger logic, since the cursor is always closed. This is a 9.2.0.3 database on Tru64. Thanks, Jay The source for the trigger is: CREATE OR REPLACE TRIGGER TOPAS.TI_CABLE_PAIRS BEFORE INSERT ON CABLE_PAIRS FOR EACH ROW DECLARE INTEGRITY_ERROR EXCEPTION; ERRNO INTEGER; ERRMSG CHAR(200); DUMMY INTEGER; FOUND BOOLEAN; -- DECLARATION OF INSERTCHILDPARENTEXIST CONSTRAINT FOR THE PARENT EXCHANGES CURSOR CPK1_CABLE_PAIRS(VAR_EXCHANGE VARCHAR) IS SELECT 1 FROM EXCHANGES WHERE EXCHANGE = VAR_EXCHANGE AND VAR_EXCHANGE IS NOT NULL FOR UPDATE OF EXCHANGE; BEGIN -- PARENT EXCHANGES MUST EXIST WHEN INSERTING A CHILD IN CABLE_PAIRS IF :NEW.EXCHANGE IS NOT NULL THEN OPEN CPK1_CABLE_PAIRS(:NEW.EXCHANGE); FETCH CPK1_CABLE_PAIRS INTO DUMMY; FOUND := CPK1_CABLE_PAIRS%FOUND; CLOSE CPK1_CABLE_PAIRS; IF NOT FOUND THEN ERRNO := -20002; ERRMSG := 'Parent does not exist in EXCHANGES. Cannot create child in CABLE_PAIRS.'; RAISE INTEGRITY_ERROR; END IF; END IF; -- ERRORS HANDLING EXCEPTION WHEN INTEGRITY_ERROR THEN RAISE_APPLICATION_ERROR(ERRNO, ERRMSG); END; / **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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). *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
Re: Maximum Open Cursors on Insert Trigger
There are some relevant notes on MetaLink that may be of help. Search on 'visual basic ora-1000' Jared On Monday 28 July 2003 07:54, Jay Hostetter wrote: We have a developer that is inserting a large number of records using a VB program. An insert trigger exists on the table. This trigger checks a parent table for records. I know this trigger really is not needed, since a Foreign Key exists to enforce referential integrity, so I plan to disable it (furthermore, it does a SELECT FOR UPDATE, which doesn't make sense). However, I'm trying to understand why the developer keeps getting ORA-01000: maximum open cursors exceeded during the inserts. I've bumped OPEN_CURSORS up to 1000. Is there something unique to VB that could be causing this problem? I've done mass inserts before on tables that have triggers without running into this type of problem. I can't see any problem in the trigger logic, since the cursor is always closed. This is a 9.2.0.3 database on Tru64. Thanks, Jay The source for the trigger is: CREATE OR REPLACE TRIGGER TOPAS.TI_CABLE_PAIRS BEFORE INSERT ON CABLE_PAIRS FOR EACH ROW DECLARE INTEGRITY_ERROR EXCEPTION; ERRNOINTEGER; ERRMSG CHAR(200); DUMMYINTEGER; FOUNDBOOLEAN; -- DECLARATION OF INSERTCHILDPARENTEXIST CONSTRAINT FOR THE PARENT EXCHANGES CURSOR CPK1_CABLE_PAIRS(VAR_EXCHANGE VARCHAR) IS SELECT 1 FROM EXCHANGES WHERE EXCHANGE = VAR_EXCHANGE AND VAR_EXCHANGE IS NOT NULL FOR UPDATE OF EXCHANGE; BEGIN -- PARENT EXCHANGES MUST EXIST WHEN INSERTING A CHILD IN CABLE_PAIRS IF :NEW.EXCHANGE IS NOT NULL THEN OPEN CPK1_CABLE_PAIRS(:NEW.EXCHANGE); FETCH CPK1_CABLE_PAIRS INTO DUMMY; FOUND := CPK1_CABLE_PAIRS%FOUND; CLOSE CPK1_CABLE_PAIRS; IF NOT FOUND THEN ERRNO := -20002; ERRMSG := 'Parent does not exist in EXCHANGES. Cannot create child in CABLE_PAIRS.'; RAISE INTEGRITY_ERROR; END IF; END IF; -- ERRORS HANDLING EXCEPTION WHEN INTEGRITY_ERROR THEN RAISE_APPLICATION_ERROR(ERRNO, ERRMSG); END; / **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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).
Insert 8000 Byte into LONG column
Hi dear list members, we run a database 8.1.6.2 I've run into a problem. I've got a table that contains a LONG column (I know, I know). I have to insert a string into it that has a length 8500 Byte. Whenever I issue the insert statement SQL/PLUS throws the error 'ORA-01704: string literal too long'. Does anyone have an idea how I can get the data into the table? Any help (except: change the datatype ;-)) is appreciated. Greetings, Guido -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guido Konsolke 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: Insert 8000 Byte into LONG column
Try using sql*loader. Put the input data string in a single file as the input file. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, July 16, 2003 9:54 AM Hi dear list members, we run a database 8.1.6.2 I've run into a problem. I've got a table that contains a LONG column (I know, I know). I have to insert a string into it that has a length 8500 Byte. Whenever I issue the insert statement SQL/PLUS throws the error 'ORA-01704: string literal too long'. Does anyone have an idea how I can get the data into the table? Any help (except: change the datatype ;-)) is appreciated. Greetings, Guido -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guido Konsolke 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda 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).
Antw: Re: Insert 8000 Byte into LONG column
Thanks for the answer, but this no way for me. Ain't got sqlldr on my client pc and no telnet to the server 8-(( [EMAIL PROTECTED] 16.07.2003 16.59 Uhr Try using sql*loader. Put the input data string in a single file as the input file. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, July 16, 2003 9:54 AM Hi dear list members, we run a database 8.1.6.2 I've run into a problem. I've got a table that contains a LONG column (I know, I know). I have to insert a string into it that has a length 8500 Byte. Whenever I issue the insert statement SQL/PLUS throws the error 'ORA-01704: string literal too long'. Does anyone have an idea how I can get the data into the table? Any help (except: change the datatype ;-)) is appreciated. Greetings, Guido -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guido Konsolke 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guido Konsolke 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: Insert 8000 Byte into LONG column
Guido, Can you use utl_file for this within a PL/SQL block? Tom Mercadante Oracle Certified Professional -Original Message- [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 16, 2003 9:54 AM To: Multiple recipients of list ORACLE-L Hi dear list members, we run a database 8.1.6.2 I've run into a problem. I've got a table that contains a LONG column (I know, I know). I have to insert a string into it that has a length 8500 Byte. Whenever I issue the insert statement SQL/PLUS throws the error 'ORA-01704: string literal too long'. Does anyone have an idea how I can get the data into the table? Any help (except: change the datatype ;-)) is appreciated. Greetings, Guido -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guido Konsolke 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F 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: Antw: Re: Insert 8000 Byte into LONG column
Guido... change the datatype! :-) Of course, you can't. Well, have you tried with a Pl-Sql anonymous block? or... Oracle says that using BIND variables you can get more than 4000 chars... have you tried with bind vars? HTH JL --- Guido Konsolke Hi dear list members, we run a database 8.1.6.2 I've run into a problem. I've got a table that contains a LONG column (I know, I know). I have to insert a string into it that has a length 8500 Byte. Whenever I issue the insert statement SQL/PLUS throws the error 'ORA-01704: string literal too long'. Does anyone have an idea how I can get the data into the table? Any help (except: change the datatype ;-)) is appreciated. Greetings, Guido -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guido Konsolke 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guido Konsolke 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). __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jose Luis Delgado 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).
Antw: RE: Insert 8000 Byte into LONG column
Hi Tom, thanks for trying to help. Unfortunately I have no chance because I've no account with write permissions. So it seems impossible to put the data on the server through ftp. Oh: and (of course!) no UTL_FILE_DIR defined in the init...ora. Any other suggestions? tia, Guido [EMAIL PROTECTED] 16.07.2003 17.34 Uhr Guido, Can you use utl_file for this within a PL/SQL block? Tom Mercadante Oracle Certified Professional -Original Message- [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 16, 2003 9:54 AM To: Multiple recipients of list ORACLE-L Hi dear list members, we run a database 8.1.6.2 I've run into a problem. I've got a table that contains a LONG column (I know, I know). I have to insert a string into it that has a length 8500 Byte. Whenever I issue the insert statement SQL/PLUS throws the error 'ORA-01704: string literal too long'. Does anyone have an idea how I can get the data into the table? Any help (except: change the datatype ;-)) is appreciated. Greetings, Guido -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guido Konsolke 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guido Konsolke 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: Antw: RE: Insert 8000 Byte into LONG column
Guido, Where are getting the data from - other database tables? Because, you could build a PL/SQL block that contains PL/SQL variables that can hold up to 32k of data. You could build the string in the variable and insert it into the LONG column. Something like: declare str_var varchar2(8000); begin select big_string into str_var from dual; insert into table (long_col) values (str_var); end; / Good Luck! Tom Mercadante Oracle Certified Professional -Original Message- [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 16, 2003 12:25 PM To: Multiple recipients of list ORACLE-L Hi Tom, thanks for trying to help. Unfortunately I have no chance because I've no account with write permissions. So it seems impossible to put the data on the server through ftp. Oh: and (of course!) no UTL_FILE_DIR defined in the init...ora. Any other suggestions? tia, Guido [EMAIL PROTECTED] 16.07.2003 17.34 Uhr Guido, Can you use utl_file for this within a PL/SQL block? Tom Mercadante Oracle Certified Professional -Original Message- [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 16, 2003 9:54 AM To: Multiple recipients of list ORACLE-L Hi dear list members, we run a database 8.1.6.2 I've run into a problem. I've got a table that contains a LONG column (I know, I know). I have to insert a string into it that has a length 8500 Byte. Whenever I issue the insert statement SQL/PLUS throws the error 'ORA-01704: string literal too long'. Does anyone have an idea how I can get the data into the table? Any help (except: change the datatype ;-)) is appreciated. Greetings, Guido -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guido Konsolke 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guido Konsolke 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F 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: insert stmt disk reads
Let's follow the transaction and see what happens. I am sure there are some errors and omissions, so please take with a grain of salt and add other steps/clarifications. 1) Object resolution. If the object definition does not already exist in the dictionary cache, it must be read from the data dictionary. 2) Translate the object to a physical location. Again, this requires a read from the data dictionary. 3) Read the undo segment header and allocate a slot in the transaction table 4) Read the undo block indicated by the undo header 5) Read the segment header. If there are indexes, read those headers as well. 6) Read the first free block on the list to memory. Insert data. 7) Read the appropriate branch and leaf blocks for each index 8) If space management is required, read the extent map(s) (dictionary or bitmap) As you can see, an insert of a single record can cause many reads to occur. IIRC, you can see the file and block output in the raw trace output from sql_trace. Track these back to the file/block values in dba_extents and it will tell you which objects you are reading. -- Daniel W. Fink http://www.optimaldba.com IOUG-A Live! April 27 - May 1, 2003 Orlando, FL Sunday, April 27 8:30am - 4:30pm - Problem Solving with Oracle 9i SQL Wednesday, May 1 1:00pm - 2:00pm - Automatic Undo Internals AK wrote: I dont have any foreign key const on the table . Yes but there are two indexes on this table . Would that cause this high disk reads ? -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, March 17, 2003 10:14 AM -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel W. Fink 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).
Using the /*+ append */ insert hint
Hi all, In a discussion with an Oracle rep last week it was suggested we use the /*+ append */ insert hint to allow some inserts to use direct-path. The suggestion is interesting - the business logic won't have any problems with the limitations this implies. Has anyone had any experience with this hint? Specifically, does anyone know what would happen on Oracle 7.3.x or 8.0.x if I used this (assuming it's supported ... would it silently ignore the hint if not supported?) Thanks Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grant Allen 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: Using the /*+ append */ insert hint
I can't say in regards to 7.3.x or 8.0.x, but in an 8.1.7.4, I've traced a complete snapshot refresh and seen that Oracle is using an insert /*+ append */. Good, bad, or otherwise, someone at Oracle believes in it. I will say that it is very likely the hint will just be ignored if not supported. For example: (this is the exact text of a query against an 8.0.6 instance) SQL select /*+ BADHINT */ * from dual; D - X SQL Darrell [EMAIL PROTECTED] 03/17/03 04:23AM Hi all, In a discussion with an Oracle rep last week it was suggested we use the /*+ append */ insert hint to allow some inserts to use direct-path. The suggestion is interesting - the business logic won't have any problems with the limitations this implies. Has anyone had any experience with this hint? Specifically, does anyone know what would happen on Oracle 7.3.x or 8.0.x if I used this (assuming it's supported ... would it silently ignore the hint if not supported?) Thanks Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grant Allen 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum 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: Using the /*+ append */ insert hint
From experience, do not use the APPEND hint for singular inserts. You will get tons of wasted space. Only use it for bulk inserts such as INSERT INTO .. SELECT FROM, sqlldr, PL/SQL bulk inserts and the like. Converting from buld inserts without the append hint to bulk inserts with the append hint, I've seen as much as a 50% reduction in execution time. Adding the append hint to single-row inserts not only wastes space but generally slows things down. Kevin -Original Message- Sent: Monday, March 17, 2003 7:29 AM To: Multiple recipients of list ORACLE-L I can't say in regards to 7.3.x or 8.0.x, but in an 8.1.7.4, I've traced a complete snapshot refresh and seen that Oracle is using an insert /*+ append */. Good, bad, or otherwise, someone at Oracle believes in it. I will say that it is very likely the hint will just be ignored if not supported. For example: (this is the exact text of a query against an 8.0.6 instance) SQL select /*+ BADHINT */ * from dual; D - X SQL Darrell [EMAIL PROTECTED] 03/17/03 04:23AM Hi all, In a discussion with an Oracle rep last week it was suggested we use the /*+ append */ insert hint to allow some inserts to use direct-path. The suggestion is interesting - the business logic won't have any problems with the limitations this implies. Has anyone had any experience with this hint? Specifically, does anyone know what would happen on Oracle 7.3.x or 8.0.x if I used this (assuming it's supported ... would it silently ignore the hint if not supported?) Thanks Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grant Allen 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Toepke, Kevin M 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: Using the /*+ append */ insert hint
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Connor McDonald Sent: Monday, March 17, 2003 13:34 To: Multiple recipients of list ORACLE-L Subject: Re: Using the /*+ append */ insert hint APPEND came in at 8.0 so it will work there. The hint can be very useful, but it works best with unindexed tables (that are set to NOLOGGING). If tables are indexed, then you still might get some benefit but the gains are not as dramatic. Of course, anything in NOLOGGING mode often requires a rethink of your backup strategy. hth connor Thanks Connor (and Darrell in the previous post). Connor, I saw a post that suggested you might be heading back to Oz - is that true? Ciao Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grant Allen 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: Using the /*+ append */ insert hint
APPEND came in at 8.0 so it will work there. The hint can be very useful, but it works best with unindexed tables (that are set to NOLOGGING). If tables are indexed, then you still might get some benefit but the gains are not as dramatic. Of course, anything in NOLOGGING mode often requires a rethink of your backup strategy. hth connor --- Grant Allen [EMAIL PROTECTED] wrote: Hi all, In a discussion with an Oracle rep last week it was suggested we use the /*+ append */ insert hint to allow some inserts to use direct-path. The suggestion is interesting - the business logic won't have any problems with the limitations this implies. Has anyone had any experience with this hint? Specifically, does anyone know what would happen on Oracle 7.3.x or 8.0.x if I used this (assuming it's supported ... would it silently ignore the hint if not supported?) Thanks Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grant Allen 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). = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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: Using the /*+ append */ insert hint
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Toepke, Kevin M Sent: Monday, March 17, 2003 13:24 To: Multiple recipients of list ORACLE-L Subject: RE: Using the /*+ append */ insert hint From experience, do not use the APPEND hint for singular inserts. You will get tons of wasted space. Only use it for bulk inserts such as INSERT INTO .. SELECT FROM, sqlldr, PL/SQL bulk inserts and the like. Converting from buld inserts without the append hint to bulk inserts with the append hint, I've seen as much as a 50% reduction in execution time. That's pretty much what we're targeting. Bulk inserts using insert ... select ... , possibly in the millions of rows. That kind of speed increase would be good. Ciao Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grant Allen 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: Using the /*+ append */ insert hint
The append hint works since Oracle 8 if my memory is good. I've used the append hint with tables in nologging mode to load a lot of data very fast. It works well. You must do a backup afterwards. If the hint is not supported, it should be ignored, like when you make a typo in the hint's name. Stephane -Original Message- Sent: Monday, March 17, 2003 5:24 AM To: Multiple recipients of list ORACLE-L Hi all, In a discussion with an Oracle rep last week it was suggested we use the /*+ append */ insert hint to allow some inserts to use direct-path. The suggestion is interesting - the business logic won't have any problems with the limitations this implies. Has anyone had any experience with this hint? Specifically, does anyone know what would happen on Oracle 7.3.x or 8.0.x if I used this (assuming it's supported ... would it silently ignore the hint if not supported?) Thanks Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grant Allen 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Paquette 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).
insert stmt disk reads
In sqlarea I am finding some insert statements with high disk reads . why would an insert statement will generate disk reads ? Any idea . -ak
Re: insert stmt disk reads
Partial List Foreign Key Validation Primary Key Validation Reading blocks on the freelist for insert Before Insert/After Insert Triggers -- Daniel W. Fink http://www.optimaldba.com IOUG-A Live! April 27 - May 1, 2003 Orlando, FL Sunday, April 27 8:30am - 4:30pm - Problem Solving with Oracle 9i SQL Wednesday, May 1 1:00pm - 2:00pm - Automatic Undo Internals AK wrote: In sqlarea I am finding some insert statements with high disk reads . why would an insert statement will generate disk reads ? Any idea . -ak
RE: insert stmt disk reads
How about foreign key constraints? - going to the parent table to see if the value exists. Tom Mercadante Oracle Certified Professional -Original Message-From: AK [mailto:[EMAIL PROTECTED]Sent: Monday, March 17, 2003 12:22 PMTo: Multiple recipients of list ORACLE-LSubject: insert stmt disk reads In sqlarea I am finding some insert statements with high disk reads . why would an insert statement will generate disk reads ? Any idea . -ak
RE: insert stmt disk reads
Constraint validations? RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 3/17/2003 11:22 AM In sqlarea I am finding some insert statements with high disk reads . why would an insert statement will generate disk reads ? Any idea . -ak -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL 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: insert stmt disk reads
Do you have many indexes on your system ? A table insert often results in index updates; and for large tables with many indexes you usually find that some of the index leaf blocks have to be read from disk. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___April 8th UK___April 22nd Denmark May 21-23rd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 17 March 2003 17:22 In sqlarea I am finding some insert statements with high disk reads . why would an insert statement will generate disk reads ? Any idea . -ak -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: insert stmt disk reads
insert into ... select * from .. -Original Message-From: AK [mailto:[EMAIL PROTECTED]Sent: Monday, March 17, 2003 12:22 PMTo: Multiple recipients of list ORACLE-LSubject: insert stmt disk reads In sqlarea I am finding some insert statements with high disk reads . why would an insert statement will generate disk reads ? Any idea . -ak
Re: insert stmt disk reads
I dont have any foreign key const on the table . Yes but there are two indexes on this table . Would that cause this high disk reads ? -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, March 17, 2003 10:14 AM Constraint validations? RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 3/17/2003 11:22 AM In sqlarea I am finding some insert statements with high disk reads . why would an insert statement will generate disk reads ? Any idea . -ak -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: AK 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: Using the /*+ append */ insert hint
Greetings from sunny Perth, Western Australia This is why the www.oracledba.co.uk hasn't been updated for a while, the UK ISP won't let me dialup from abroad, so I'm trying to find a workaround :-( Left UK in Jan, spent a month in Canada, got back to Perth in Feb, got married in 110 degree heat, and now scanning the barren wilderness otherwise known as the Perth job market :-( Cheers Connor --- Grant Allen [EMAIL PROTECTED] wrote: -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Connor McDonald Sent: Monday, March 17, 2003 13:34 To: Multiple recipients of list ORACLE-L Subject: Re: Using the /*+ append */ insert hint APPEND came in at 8.0 so it will work there. The hint can be very useful, but it works best with unindexed tables (that are set to NOLOGGING). If tables are indexed, then you still might get some benefit but the gains are not as dramatic. Of course, anything in NOLOGGING mode often requires a rethink of your backup strategy. hth connor Thanks Connor (and Darrell in the previous post). Connor, I saw a post that suggested you might be heading back to Oz - is that true? Ciao Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grant Allen 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). = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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: High current mode buffer gets on insert
I thought I'd repost to see if I could get a response. Anyone? --- Paul Baumgartel [EMAIL PROTECTED] wrote: I'm looking at a client's tkprof output, showing among other things that the insertion of about 135,000 rows taking 450 seconds of CPU, and with current mode buffer gets numbering almost 800,000. This is a daily warehouse load process, and I know that indexes are left in place during the load. Am I correct in concluding that the high CPU, and especially the current mode block gets numbering over 6 times the number of rows inserted, are due to index updates? Thanks! = Paul Baumgartel, Adept Computer Associates, Inc. [EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Paul Baumgartel 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). __ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Paul Baumgartel 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).
High current mode buffer gets on insert
I'm looking at a client's tkprof output, showing among other things that the insertion of about 135,000 rows taking 450 seconds of CPU, and with current mode buffer gets numbering almost 800,000. This is a daily warehouse load process, and I know that indexes are left in place during the load. Am I correct in concluding that the high CPU, and especially the current mode block gets numbering over 6 times the number of rows inserted, are due to index updates? Thanks! = Paul Baumgartel, Adept Computer Associates, Inc. [EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Paul Baumgartel 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: slow insert
-Original Message- Sent: Saturday, February 22, 2003 7:54 AM To: Multiple recipients of list ORACLE-L You can read more abt it in PL/SQL User's Guide and Reference - Chapter 4 - Collections and Records ! anyway, below is the simple code to copy data from emp table. note that, after u fetch from the cursor, u have to check for the notfound condition at the end. say, the emp table has 15 rows and u r fetching 2 rows at a time. during the 1st 7 fetches u will be ok. but during the 8th fetch, the fetch will bring only 1 row and the condition notfound will be true. thus u will never process the last set of rows. hence, check for the condition after u have inserted/updated/deleted the rows. finally, try playing with the limit clause in the fetch and see what number is best for u. considering my hardware etc, i got better performance with 5000 fetches at a time. also, till 8.1.7. u could not have any stmts in the FORALL loop. only 1, i.e. insert/update/delete. but with 9i u can have a pl/sql block any dynamic stmts too. moreover, with 9i u can also trap for individual exception whereas with 8.1.7. if there was an error, entire bulk process would rollback. hope this helps let me know if u need any more help. -- code follows declare cursor c_emp is select empno, ename, hiredate from emp ; type empno_arr_type is table of number(4); v_empno_arr empno_arr_type; type name_arr_type is table of varchar2(10); v_ename_arr name_arr_type; type date_arr_type is table of date; v_hiredate_arr date_arr_type; v_arr_idx binary_integer := 0; v_arr_cnt binary_integer := 0; begin open c_emp; loop fetch c_emp bulk collect into v_empno_arr, v_ename_arr, v_hiredate_arr limit 2; -- DO NOT CHECK for notfound here v_arr_cnt := v_empno_arr.count(); dbms_output.put_line(v_arr_cnt); forall v_arr_idx in 1 .. v_arr_cnt insert into emp_copy(empno, ename, hiredate) values(v_empno_arr(v_arr_idx),v_ename_arr(v_arr_idx),v_hiredate_arr(v_arr_id x)); commit; -- note : the condition has to be checked here ONLY exit when c_emp%notfound; end loop; close c_emp; end; / -Original Message- Ravindra Sent: Friday, February 21, 2003 7:09 PM To: Multiple recipients of list ORACLE-L Yes I am usng 8.1.7 EE.How do I use bulk inserts..Any docs or links on metalink plss? -Original Message- Sent: Friday, February 21, 2003 3:34 PM To: Multiple recipients of list ORACLE-L if u r having oracle 8.1.7 EE or higher, try using bulk inserts ! that surely will speed up ! and of course, u surely might have thought of APPEND hint dropping/disabling indexes etc. -Original Message- Ravindra Sent: Friday, February 21, 2003 3:08 PM To: Multiple recipients of list ORACLE-L hi, I have an insert statement that will insert about 40 records into a table having 43million records.The values for the insert statement are from a select statement that has a join.This query take about 5-10minutes.What are the ways in which we can speed up this process.the statement looks like insert into ... select ... from a,b where a.col1=b.col1 ---index columns and a.col2=x --non index and b.col2=x --non index Thanks -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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
RE: slow insert
I tried this way and infact bulk collect took about 1 minutes more than my current case? Any thoughts thanks -Original Message- Sent: Saturday, February 22, 2003 7:54 AM To: Multiple recipients of list ORACLE-L You can read more abt it in PL/SQL User's Guide and Reference - Chapter 4 - Collections and Records ! anyway, below is the simple code to copy data from emp table. note that, after u fetch from the cursor, u have to check for the notfound condition at the end. say, the emp table has 15 rows and u r fetching 2 rows at a time. during the 1st 7 fetches u will be ok. but during the 8th fetch, the fetch will bring only 1 row and the condition notfound will be true. thus u will never process the last set of rows. hence, check for the condition after u have inserted/updated/deleted the rows. finally, try playing with the limit clause in the fetch and see what number is best for u. considering my hardware etc, i got better performance with 5000 fetches at a time. also, till 8.1.7. u could not have any stmts in the FORALL loop. only 1, i.e. insert/update/delete. but with 9i u can have a pl/sql block any dynamic stmts too. moreover, with 9i u can also trap for individual exception whereas with 8.1.7. if there was an error, entire bulk process would rollback. hope this helps let me know if u need any more help. -- code follows declare cursor c_emp is select empno, ename, hiredate from emp ; type empno_arr_type is table of number(4); v_empno_arr empno_arr_type; type name_arr_type is table of varchar2(10); v_ename_arr name_arr_type; type date_arr_type is table of date; v_hiredate_arr date_arr_type; v_arr_idx binary_integer := 0; v_arr_cnt binary_integer := 0; begin open c_emp; loop fetch c_emp bulk collect into v_empno_arr, v_ename_arr, v_hiredate_arr limit 2; -- DO NOT CHECK for notfound here v_arr_cnt := v_empno_arr.count(); dbms_output.put_line(v_arr_cnt); forall v_arr_idx in 1 .. v_arr_cnt insert into emp_copy(empno, ename, hiredate) values(v_empno_arr(v_arr_idx),v_ename_arr(v_arr_idx),v_hiredate_arr(v_arr_id x)); commit; -- note : the condition has to be checked here ONLY exit when c_emp%notfound; end loop; close c_emp; end; / -Original Message- Ravindra Sent: Friday, February 21, 2003 7:09 PM To: Multiple recipients of list ORACLE-L Yes I am usng 8.1.7 EE.How do I use bulk inserts..Any docs or links on metalink plss? -Original Message- Sent: Friday, February 21, 2003 3:34 PM To: Multiple recipients of list ORACLE-L if u r having oracle 8.1.7 EE or higher, try using bulk inserts ! that surely will speed up ! and of course, u surely might have thought of APPEND hint dropping/disabling indexes etc. -Original Message- Ravindra Sent: Friday, February 21, 2003 3:08 PM To: Multiple recipients of list ORACLE-L hi, I have an insert statement that will insert about 40 records into a table having 43million records.The values for the insert statement are from a select statement that has a join.This query take about 5-10minutes.What are the ways in which we can speed up this process.the statement looks like insert into ... select ... from a,b where a.col1=b.col1 ---index columns and a.col2=x --non index and b.col2=x --non index Thanks -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: slow insert
Ignoring bulk insert, and assuming you are performing a single insert statement have you looked at the following (as already suggested): 1) What, and how many, indexes exist on the destination table. Each index requires updating as records are inserted. If there are indexes not required then remove them. Alternatively you might even consider disabling the indexes before inserting the data then rebuilding them... Although not likely if you are performing a single insert with no other work. 2) Is col2 (in your example query) indexed in either table? What proportion of the table meets this selection criteria? IE: If col2 was gender and your data was evenly spread then selecting male would return ~50% of rows. If col2 was age and your data was evenly spread between 1 and 100 then selecting 20 would return ~1% of rows. If either of the selection returns a relatively low number of rows then look at indexing these columns. 3) Have you looked at an explain plan for this statement? If not, start sql*plus, type set autotrace on, then execute the query. This will show if indexes are being used, etc. We may be able to help further if we know this information and know about your data. 4) Have you tried running the select statement on it's own, or inserting into a table which contains no indexes (and maybe has nologging set). This may give an idea of whether the time is consumed retrieving the data or inserting it into the destination. This will give you a good idea of where to focus your tuning efforts. 5) Have you tried a parallel hint? This may help depending on your physical configuration. Regards, Mark. Basavaraja, Ravindra [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] obile.com cc: Sent by: Subject: RE: slow insert [EMAIL PROTECTED] 25/02/2003 13:14 Please respond to ORACLE-L I tried this way and infact bulk collect took about 1 minutes more than my current case? Any thoughts thanks -Original Message- Sent: Saturday, February 22, 2003 7:54 AM To: Multiple recipients of list ORACLE-L You can read more abt it in PL/SQL User's Guide and Reference - Chapter 4 - Collections and Records ! anyway, below is the simple code to copy data from emp table. note that, after u fetch from the cursor, u have to check for the notfound condition at the end. say, the emp table has 15 rows and u r fetching 2 rows at a time. during the 1st 7 fetches u will be ok. but during the 8th fetch, the fetch will bring only 1 row and the condition notfound will be true. thus u will never process the last set of rows. hence, check for the condition after u have inserted/updated/deleted the rows. finally, try playing with the limit clause in the fetch and see what number is best for u. considering my hardware etc, i got better performance with 5000 fetches at a time. also, till 8.1.7. u could not have any stmts in the FORALL loop. only 1, i.e. insert/update/delete. but with 9i u can have a pl/sql block any dynamic stmts too. moreover, with 9i u can also trap for individual exception whereas with 8.1.7. if there was an error, entire bulk process would rollback. hope this helps let me know if u need any more help. -- code follows declare cursor c_emp is select empno, ename, hiredate from emp ; type empno_arr_type is table of number(4); v_empno_arr empno_arr_type; type name_arr_type is table of varchar2(10); v_ename_arr name_arr_type; type date_arr_type is table of date; v_hiredate_arr date_arr_type; v_arr_idx
RE: slow insert
Thanks for the inputs. Here are some important results. 1)The insert into Original table with 43million records takes about 10minutes and the explain plan as attached in the text file original table insert into original table... select ... from a,b where a.col1=b.col1 ---index columns and a.col2=x --non index and b.col2=x --non index Note:Original table is having a composite primary key of 3 columns 2)I created a temp table similiar to the original table as create table temp1 as select * from originaltable where 1=2; this didn't create any index on the table and the insert got executed in 4 seconds. I am attaching the explain plan in the attachment new table 3)I created a composite primary key of the 3 cols on this new table and the insert took 13 seconds. The execution can be found in the same file new table at the end. pls advice thanks -Original Message- Sent: Monday, February 24, 2003 7:04 PM To: Multiple recipients of list ORACLE-L Ignoring bulk insert, and assuming you are performing a single insert statement have you looked at the following (as already suggested): 1) What, and how many, indexes exist on the destination table. Each index requires updating as records are inserted. If there are indexes not required then remove them. Alternatively you might even consider disabling the indexes before inserting the data then rebuilding them... Although not likely if you are performing a single insert with no other work. 2) Is col2 (in your example query) indexed in either table? What proportion of the table meets this selection criteria? IE: If col2 was gender and your data was evenly spread then selecting male would return ~50% of rows. If col2 was age and your data was evenly spread between 1 and 100 then selecting 20 would return ~1% of rows. If either of the selection returns a relatively low number of rows then look at indexing these columns. 3) Have you looked at an explain plan for this statement? If not, start sql*plus, type set autotrace on, then execute the query. This will show if indexes are being used, etc. We may be able to help further if we know this information and know about your data. 4) Have you tried running the select statement on it's own, or inserting into a table which contains no indexes (and maybe has nologging set). This may give an idea of whether the time is consumed retrieving the data or inserting it into the destination. This will give you a good idea of where to focus your tuning efforts. 5) Have you tried a parallel hint? This may help depending on your physical configuration. Regards, Mark. Basavaraja, Ravindra [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] obile.com cc: Sent by: Subject: RE: slow insert [EMAIL PROTECTED] 25/02/2003 13:14 Please respond to ORACLE-L I tried this way and infact bulk collect took about 1 minutes more than my current case? Any thoughts thanks -Original Message- Sent: Saturday, February 22, 2003 7:54 AM To: Multiple recipients of list ORACLE-L You can read more abt it in PL/SQL User's Guide and Reference - Chapter 4 - Collections and Records ! anyway, below is the simple code to copy data from emp table. note that, after u fetch from the cursor, u have to check for the notfound condition at the end. say, the emp table has 15 rows and u r fetching 2 rows at a time. during the 1st 7 fetches u will be ok. but during the 8th fetch, the fetch will bring only 1 row and the condition notfound will be true. thus u will never
RE: slow insert
You can read more abt it in PL/SQL User's Guide and Reference - Chapter 4 - Collections and Records ! anyway, below is the simple code to copy data from emp table. note that, after u fetch from the cursor, u have to check for the notfound condition at the end. say, the emp table has 15 rows and u r fetching 2 rows at a time. during the 1st 7 fetches u will be ok. but during the 8th fetch, the fetch will bring only 1 row and the condition notfound will be true. thus u will never process the last set of rows. hence, check for the condition after u have inserted/updated/deleted the rows. finally, try playing with the limit clause in the fetch and see what number is best for u. considering my hardware etc, i got better performance with 5000 fetches at a time. also, till 8.1.7. u could not have any stmts in the FORALL loop. only 1, i.e. insert/update/delete. but with 9i u can have a pl/sql block any dynamic stmts too. moreover, with 9i u can also trap for individual exception whereas with 8.1.7. if there was an error, entire bulk process would rollback. hope this helps let me know if u need any more help. -- code follows declare cursor c_emp is select empno, ename, hiredate from emp ; type empno_arr_type is table of number(4); v_empno_arr empno_arr_type; type name_arr_type is table of varchar2(10); v_ename_arr name_arr_type; type date_arr_type is table of date; v_hiredate_arr date_arr_type; v_arr_idx binary_integer := 0; v_arr_cnt binary_integer := 0; begin open c_emp; loop fetch c_emp bulk collect into v_empno_arr, v_ename_arr, v_hiredate_arr limit 2; -- DO NOT CHECK for notfound here v_arr_cnt := v_empno_arr.count(); dbms_output.put_line(v_arr_cnt); forall v_arr_idx in 1 .. v_arr_cnt insert into emp_copy(empno, ename, hiredate) values(v_empno_arr(v_arr_idx),v_ename_arr(v_arr_idx),v_hiredate_arr(v_arr_id x)); commit; -- note : the condition has to be checked here ONLY exit when c_emp%notfound; end loop; close c_emp; end; / -Original Message- Ravindra Sent: Friday, February 21, 2003 7:09 PM To: Multiple recipients of list ORACLE-L Yes I am usng 8.1.7 EE.How do I use bulk inserts..Any docs or links on metalink plss? -Original Message- Sent: Friday, February 21, 2003 3:34 PM To: Multiple recipients of list ORACLE-L if u r having oracle 8.1.7 EE or higher, try using bulk inserts ! that surely will speed up ! and of course, u surely might have thought of APPEND hint dropping/disabling indexes etc. -Original Message- Ravindra Sent: Friday, February 21, 2003 3:08 PM To: Multiple recipients of list ORACLE-L hi, I have an insert statement that will insert about 40 records into a table having 43million records.The values for the insert statement are from a select statement that has a join.This query take about 5-10minutes.What are the ways in which we can speed up this process.the statement looks like insert into ... select ... from a,b where a.col1=b.col1 ---index columns and a.col2=x --non index and b.col2=x --non index Thanks -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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
slow insert
hi, I have an insert statement that will insert about 40 records into a table having 43million records.The values for the insert statement are from a select statement that has a join.This query take about 5-10minutes.What are the ways in which we can speed up this process.the statement looks like insert into ... select ... from a,b where a.col1=b.col1 ---index columns and a.col2=x --non index and b.col2=x --non index Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra 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: slow insert
if u r having oracle 8.1.7 EE or higher, try using bulk inserts ! that surely will speed up ! and of course, u surely might have thought of APPEND hint dropping/disabling indexes etc. -Original Message- Ravindra Sent: Friday, February 21, 2003 3:08 PM To: Multiple recipients of list ORACLE-L hi, I have an insert statement that will insert about 40 records into a table having 43million records.The values for the insert statement are from a select statement that has a join.This query take about 5-10minutes.What are the ways in which we can speed up this process.the statement looks like insert into ... select ... from a,b where a.col1=b.col1 ---index columns and a.col2=x --non index and b.col2=x --non index Thanks -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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: slow insert
Yes I am usng 8.1.7 EE.How do I use bulk inserts..Any docs or links on metalink plss? -Original Message- Sent: Friday, February 21, 2003 3:34 PM To: Multiple recipients of list ORACLE-L if u r having oracle 8.1.7 EE or higher, try using bulk inserts ! that surely will speed up ! and of course, u surely might have thought of APPEND hint dropping/disabling indexes etc. -Original Message- Ravindra Sent: Friday, February 21, 2003 3:08 PM To: Multiple recipients of list ORACLE-L hi, I have an insert statement that will insert about 40 records into a table having 43million records.The values for the insert statement are from a select statement that has a join.This query take about 5-10minutes.What are the ways in which we can speed up this process.the statement looks like insert into ... select ... from a,b where a.col1=b.col1 ---index columns and a.col2=x --non index and b.col2=x --non index Thanks -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra 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: Index update = Delete + insert ?
No - If you update a column that is involved in an index, but the update is a 'no-change' update a) the TABLE row is locked b) undo and redo are generated for the TABLE row but The index is not even visited, let alone locked and modified. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 12 February 2003 14:14 Vijay,List When Updating to the Field to the SAME (Previously Existent) Data Value , Does a DELETE RE-Insert of the Same Row to the index happen nevertheless ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Index update = Delete + insert ?
Effectively so, unless you regularly put the same value back into the index. I haven't verified it, but in this case my understanding is that oracle does not the work at all. Of course, any index operation could invoke more work in terms of re-arranging blocks to keep the validity of the index structure. hth connor --- VIVEK_SHARMA [EMAIL PROTECTED] wrote: Is an index Fields' update actually a DELETE followed by an INSERT of the index row ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA 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). = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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: Index update = Delete + insert ?
Vijay,List When Updating to the Field to the SAME (Previously Existent) Data Value , Does a DELETE RE-Insert of the Same Row to the index happen nevertheless ? Thanks -Original Message- Sent: Wednesday, February 12, 2003 12:50 PM To: VIVEK_SHARMA Hi Vivek, Index rows are first deleted and then inserted rather than update... regards, Vijaya Chander V.S -Original Message- Sent: Wednesday, February 12, 2003 12:43 PM To: LazyDBA.com Discussion Is an index Fields' update actually a DELETE followed by an INSERT of the index row ? Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage To unsubscribe: send a blank email to [EMAIL PROTECTED] To subscribe: send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com By using this list you agree to these terms:http://www.lazydba.com/legal.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA 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).
INSERT ... RETURNING ROWIDTOCHAR(ROWID) INTO problem on 9.2.0.2.1
Hello All,
We have Oracle 9.2.0.2.1 running on our server. When we run the following
command from Sql Plus, we get the end-of-file on communication channel.
This occurs consistently on all four of our 9.2.0.2.1 instances on this
server. It has also occured on a new installation of 9.2.0.2.1 on a
different server. When we run the insert statement separately, it succeeds.
We have not encountered this problem when running the command from developer
workstations running 9i release 2 without the patch. It has also worked on
the other Oracle versions.
We have NT 4.0 SP6.
Does anybody have any insight into what the problem might be? Here is the
command.
SQL connect canadian_575/sql@paristest
Connected.
SQL begin
2 declare
3 cRow varchar2(18);
4 begin
5 INSERT INTO MEMBER_PLAN_FUND
6 (CLNT,PLAN,MKEY,FUND,TRADATE,ERKEY) VALUES
7 ('0001','1', '000-3','ER',SYSDATE,Misc.GetDefaultErKey('0001','000-3'))
8 RETURNING ROWIDTOCHAR(ROWID) INTO cRow;
9 end;
10 end;
11 /
begin
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
SQL connect canadian_575/sql@paristest
Connected.
Thanks for any help ...
Sam Bootsma, OCP
Technical Support Analyst
CPAS Systems Inc.
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Sam Bootsma
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).
