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 <jonathan_at_gennick.com> 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:jonathan@gennick.com
>
> 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 Oracle-article-request_at_gennick.com and
> include the word "subscribe" in either the subject or body.
>
>
> Wednesday, November 12, 2003, 8:34:24 AM, Jonathan Gennick
> (jonathan_at_gennick.com) 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:jonathan@gennick.com
>
> 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> kamyeelee_at_comcast.net (kamyeelee_at_comcast.net) 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: yong321_at_yahoo.com
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: ListGuru_at_fatcity.com (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).
Received on Wed Nov 12 2003 - 11:49:34 CST