KamYee, Can we take a look at the SQL*Loader control file? Also the output of select * from v$sql where lower(sql_text) like '%yourstagingtable%' and invalidations > 0
Yong Huang --- Jonathan Gennick <[EMAIL PROTECTED]> wrote: > 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 __________________________________ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang 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).
