Re: Fwd: Looking for help.

From: Jonathan Gennick <jonathan_at_gennick.com> Date: Wed, 12 Nov 2003 06:34:33 -0800 Message-ID: <F001.005D66CE.20031112063433@fatcity.com> · --

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> 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 Oracle-article-request_at_gennick.com and 
JG> include the word "subscribe" in either the subject or body.

JG> Wednesday, November 12, 2003, 1:07:41 AM, 

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> 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?

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------