Yep, truncate invalidates parsed SQL:
SQL> select count(*) from
t3;
COUNT(*)
----------
0
----------
0
SQL> select sql_text, optimizer_mode,
invalidations from v$sql where sql_text like '%select count(*) from
t3%'
and sql_text not like '%v$sql%';
and sql_text not like '%v$sql%';
SQL_TEXT
OPTIMIZER_
INVALIDATIONS
------------------------------------------------------------ ---------- -------------
select count(*) from t3 CHOOSE 1
------------------------------------------------------------ ---------- -------------
select count(*) from t3 CHOOSE 1
SQL> truncate table
t3;
Table truncated.
SQL> select sql_text, optimizer_mode,
invalidations from v$sql where sql_text like '%select count(*) from
t3%'
and sql_text not like '%v$sql%';
and sql_text not like '%v$sql%';
SQL_TEXT
OPTIMIZER_
INVALIDATIONS
------------------------------------------------------------ ---------- -------------
select count(*) from t3 NONE 2
------------------------------------------------------------ ---------- -------------
select count(*) from t3 NONE 2
Tanel.
----- Original Message -----
From: "Jonathan Gennick" <[EMAIL PROTECTED]>
To: "Multiple recipients of list ORACLE-L"
<[EMAIL PROTECTED]>
Sent: Wednesday, November 12, 2003 4:34
PM
Subject: Re: Fwd: Looking for
help.
> 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).
>
