| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Fwd: Looking for help.
Jonathan,
I don't see where the TRUNCATE command is used in the original request
message.
The 20 staging tables have a delete action against them.
The data is sqlldr action to load the tables.
The final tables are loaded from the staging tables using a PL/SQL
procedure.
The final tables are cleaned up using a PL/SQL procedure.
The ORA-04031 is most likely caused by one of the PL/SQL procedures not releasing the memory stack. It takes a few days of loading before the failure occures.
I would look into the os and possible the I/O. is it buffered and the sqlldr is looking at the physical not buffered area or vise-versa? Are the table cached?
The sqlldr command is run and the previous "delete from staging tables" commands are invalidated? Is there a change of ownership on the tables at this time?
Just a few random thoughts that may or may not invoke someone elses
thought process.
Ron
>>> jonathan_at_gennick.com 11/12/2003 9:34:33 AM >>>
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
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> 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: jonathan_at_gennick.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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers INET: RROGERS_at_galottery.org 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:09:25 CST
 |
 |