I wonder whether the invalidation comes about from the use of TRUNCATE, which is considered a DDL statement. I'd guess that any DDL to a table would invalidate existing SQL statements.
Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word "subscribe" in either the subject or body. Wednesday, November 12, 2003, 8:34:24 AM, Jonathan Gennick ([EMAIL PROTECTED]) wrote: JG> I don't usually forward my reader email to the list, but the JG> question below strikes me as rather interesting. In this JG> case, SQL*Loader appears to be causing all SQL statements JG> that refer to the table being loaded to be invalidated. Is JG> this normal behavior? Does anyone know why it might be the JG> case? JG> -- JG> Best regards, JG> Jonathan Gennick --- Brighten the corner where you are JG> http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] JG> Join the Oracle-article list and receive one JG> article on Oracle technologies per month by JG> email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, JG> or send email to [EMAIL PROTECTED] and JG> include the word "subscribe" in either the subject or body. JG> Wednesday, November 12, 2003, 1:07:41 AM, JG> [EMAIL PROTECTED] ([EMAIL PROTECTED]) wrote: JG> Hi Jonathan, JG> I was unable to find the answers from your book "SQL*Loader: The Definitive Guide" and the web. I am running out of sources. I hope you can help me with the following questions. JG> We are using Oracle 9i sqlldr, direct path to load data from external files into JG> staging tables. After data is loaded, we invoked stored procedures to JG> transform data and move them to the target tables. The steps are: JG> 1. delete all entries from 20 staging tables JG> 2. invoke "sqlldr userid=dbimpl/dbimpl control=<controlFile> direct=true" to JG> load data to all 20 staging tables JG> 3. invoke stored procedures to transform data from the staging tables to the JG> final tables. Currently these stored procedures are standalone. JG> 4. invoke stored procedures to remove out-of-date entries from the final JG> tables. JG> I monitor invalidations column in v$sqlarea. Every time JG> after sqlldr is invoked for data loading (step 2), all the JG> sql statements that reference the staging tables are JG> invalidated, including "delete from <stageing_table>" sql JG> statement. I setup a test and used a java program to loop JG> steps 1-4 every ~2 minutes. There were no other activities JG> in the database except data loading and transformation. JG> After a couple days, I got the following error: ORA-04031: JG> unable to allocate 4212 bytes of shared memory ("shared JG> pool","unknown object","sga heap(1,0)","stat array mem") JG> The questions are: JG> 1. Do we need to delete entries in the staging table prior to loading. Will JG> sqlldr remove the entires in the staging table first prior to loading? JG> 2. There are no changes in the stored procedures, how / why sqlldr would JG> invalidate the sql statement in the stored procedures? JG> 3. The error ORA-04031 in this case, is it due to shared memory fragmentation? I suspect that the culprint is invalidations. How do invalidations cause shared memory fragmentation? JG> I would appreciate if you can send me some pointers or suggestions. JG> Thanks, JG> KamYee JG> -- JG> Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick 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).
