RE: Pinned packages with multiple reloads

Thanks, Waleed. That's the article I remember seeing months ago when I last posed the question. The article seems to point toward my situation, but there are a few confusing items about the article.

First, it says for possible solutions:

1. Compile the package body only before pinning the package.
2. Make sure all the SYS objects are valid before pinning any packages.

Could SYS objects be invalidated at startup? As I mentioned, I check for invalid objects every five minutes and no SYS objects show up (the one exception possibly when I have installed Quest products like QCO in the past).

Also, the Metalink article states:

	There are a couple of reasons why reloads can occur:
	A. Objects have to be flushed out. 
	   Your shared_pool_size and shared_pool_reserved_size are tuned. 
	   It is not likely that the packages are aged out.
	B. The pinned object is invalidated.
	   The DBMS packages were reloaded as the dependent sys objects
	   became invalid.

Reason "A" sounds to me more like why reloads DON'T occur. Can pinned objects be flushed? Doesn't that defeat the purpose of pinning?

OK, after some more research (man, stuff can be hard to find on Metalink!), I've found article 175659.1 that states that package header reloads may be BUG 1164709. <sigh> I can't win.

Rich Jesse                           System/Database Administrator
Rich.Jesse_at_qtiworld.com              Quad/Tech International, Sussex, WI USA

> -----Original Message-----
> From: Khedr, Waleed [mailto:Waleed.Khedr_at_FMR.COM]
> Sent: Tuesday, January 07, 2003 6:10 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Pinned packages with multiple reloads
>
>
> Did you see this note:
>
> http://metalink.oracle.com/metalink/plsql/ml2_documents.showDo
> cument?p_database_id=NOT&p_id=1020366.102
>
> Regards,
>
> Waleed
>
> -----Original Message-----
> Sent: Tuesday, January 07, 2003 5:59 PM
> To: Multiple recipients of list ORACLE-L
>
>
> That rings a bell! But how would DBMS_APPLICATION_INFO
> become invalid 836
> times in two days? And I check for invalid objects every
> five minutes in
> our production DB (occasionally testing gets skipped) and
> none of my pinned
> packages with high loads has ever shown up. I know that
> there's a slim
> chance, but you'd think after some months that at least *one*
> would show.
> Here's a listing from V$DB_OBJECT_CACHE of my pinned objects:
>
> TYPE NAME Execs LOADS
> ------------- ------------------------------ ------- -----
> PACKAGE DBMS_APPLICATION_INFO 130670 836
> PACKAGE DBMS_OUTPUT 9395 272
> TRIGGER QT_PARTMASTER_LEADTIME_UPD 48945 110
> TRIGGER QT_PARTMASTER_ENG_CTL_AUDIT 1925 110
> TRIGGER QT_OE_PARTS_LABEL_DETAIL_UPD 24958 42
> TRIGGER QT_OE_PARTS_DETAIL_MODTIME 24958 41
> PACKAGE STANDARD 111100 1
> PACKAGE DBMS_SYS_SQL 731 1
> PACKAGE DBMS_SQL 731 1
> PACKAGE BODY QUEST_SOO_PKG 13941 1
> PACKAGE BODY DBMS_SYS_SQL 731 1
> PACKAGE BODY CTX_DDL 3956 1
> PACKAGE BODY STATSPACK 273 1
> PACKAGE CTX_DDL 7554 1
> PACKAGE BODY STANDARD 111101 1
> PACKAGE STATSPACK 444 1
> PACKAGE DBMS_STANDARD 4080 1
> PACKAGE QUEST_SOO_PKG 14319 1
> PACKAGE BODY DBMS_SQL 731 1
> PACKAGE QT_OE_PARTS 2131 1
> PACKAGE BODY QT_OE_PARTS 1385 1
> PACKAGE DBMS_SHARED_POOL 291 1
> PACKAGE BODY DBMS_SHARED_POOL 291 1
> PROCEDURE QTSETUSERNAME 64447 1
> PACKAGE BODY DBMS_OUTPUT 4773 1
> TRIGGER TIMESHT_INSERT_SCNTRL_UPDATE 2180 1
> TRIGGER QT_PARTMASTER_STATUS_AUDIT 1875 1
> TRIGGER QT_PARTMASTER_MB_UPD 1763 1
> PACKAGE BODY DBMS_APPLICATION_INFO 71437 1
>
> The triggers I can almost understand, because there could
> have been some
> maintenance on the underlying table (although I seriously
> doubt 110 times in
> two days). But I can't imagine what's going on with the SYS packages.
>
>
> Rich Jesse System/Database Administrator
> Rich.Jesse_at_qtiworld.com Quad/Tech International,
> Sussex, WI USA

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jesse, Rich
  INET: Rich.Jesse_at_qtiworld.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).