Ron and Jonathan,
Glad to know the problem is solved. We do need SQL*Loader control file whenever
we have such a problem.
Any DDL would invalidate a cursor in library cache. I'm testing with the
"harmless" GRANT, which is a DDL.
SQL> create table yongtst (a number);
Table created.
SQL> delete from yongtst;
0 rows deleted.
SQL> select sql_text, invalidations from v$sql where sql_text like '%yongtst%';
SQL_TEXT
INVALIDATIONS
-------------------------------------------------------------------------------------
-------------
delete from yongtst
0
select sql_text, invalidations from v$sql where sql_text like '%yongtst%'
0
SQL> grant select on yongtst to system;
Grant succeeded.
SQL> select sql_text, invalidations from v$sql where sql_text like '%yongtst%';
SQL_TEXT
INVALIDATIONS
-------------------------------------------------------------------------------------
-------------
delete from yongtst
1
select sql_text, invalidations from v$sql where sql_text like '%yongtst%'
0
After GRANT, the delete statement has an invalidation of 1 so a hard parse will
occur next time. I think this hard parse is slightly less expensive than a
brand new SQL. After invalidation, the cursor (cursor head or parent cursor)
still shows up in v$sql, but it disappears from v$sql_plan. A brand new SQL
does not have an entry in v$sql.
Other "harmless" DDLs acting on tables are COMMENT, ANALYZE, REVOKE, but not
EXPLAIN PLAN as you might believe.
Yong Huang
--- Ron Rogers <[EMAIL PROTECTED]> wrote:
> Daniel,
> I understand what is you are saying and what you tested but I don't
> the why or what it means. Does it mean that the sql command is not
> going to work? Does it means that you have to issue it again to get it
> to work?
>
> Ref:
> Doc ID: Note:123214.1
> invalid
> Type: PROBLEM
> Status: PUBLISHED
> ....
> Seems that truncate command invalidates object definition and existence
> in library cache.
>
> Invalidation can also be seen on temporary tables!
>
> ..
>
> Jonathan,
> The memory problem is described in Doc id:1157495.8 Support
> Description of Bug 1157495
>
> Ron
>
> >>> [EMAIL PROTECTED] 11/12/2003 1:14:25 PM >>>
> Ron,
>
> I don't know about the TRUNCATE option w/ sql*loader, but the regular
> DDL
> TRUNCATE invalidates sql that references the table.
>
> Example:
>
> 1 select sql_text, invalidations
> 2 from v$sql
> 3* where sql_text = 'select * from emp'
> SQL> /
>
> SQL_TEXT INVALIDATIONS
> -------------------------------------------------- -------------
> select * from emp 0
>
> SQL> truncate table emp;
>
> Table truncated.
>
> SQL> select sql_text, invalidations
> 2 from v$sql
> 3 where sql_text = 'select * from emp'
> 4 /
>
> SQL_TEXT INVALIDATIONS
> -------------------------------------------------- -------------
> select * from emp 1
>
>
> Ron Rogers wrote:
>
> > Daniel,
> > How does using the TRUNCATE command is a sqlldr invalidate
> anything?
> > The sqlldr truncate command reuses the storage that the table
> originally
> > used and does not change the HW mark. If there are indexes on the
> tables
> > then they are placed in the "DIRECT PATH" state during the load and
> > updated with the now block info.
> > Please explain whet you mean by "invalidate".
> > Ron
__________________________________
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).
