Re: Fwd: Looking for help.

From: Yong Huang <yong321_at_yahoo.com>
Date: Wed, 12 Nov 2003 13:44:32 -0800
Message-ID: <F001.005D673F.20031112134432@fatcity.com>

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 <RROGERS_at_galottery.org> 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
  >
  > >>> Daniel.Fink_at_Sun.COM 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: 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 - 15:44:32 CST