RE: Pinned packages with multiple reloads

Did you see this note:

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_datab ase_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

> -----Original Message-----
> From: Johnston, Tim [mailto:TJohnston_at_quallaby.com]
> Sent: Tuesday, January 07, 2003 2:35 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Pinned packages with multiple reloads
>
>
> What about invalidations? If something invalidates the
> packages does it
> reload?
>
> Just guessing...
>
> I remember this thread and was hoping you would have an
> answer by now...
> Damn...
>
> :-)
>
>
>
> -----Original Message-----
> Sent: Tuesday, January 07, 2003 1:18 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Hey all,
>
> I have a trigger that pins various packages on the startup of
> our 8.1.7.2.0
> DB on HP/UX 11.0. I check V$DB_OBJECT_CACHE every so often
> to see that it's
> working and to make any necessary additions/deletions from the list of
> pinned packages.
>
> However, the SYS.DBMS_APPLICATION_INFO package currently has
> 836 loads off
> of 97706 execs despite being successfully pinned on startup
> (we've only been
> up for a few days due a 7445 cascade crash). How is this possible?
> Furthermore, does it do any good to pin this package? And why?
>
> This is sort of a repost from months ago, but I can't find it
> on fatcity.
>
> TIA!
> Rich
>
>
> 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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Khedr, Waleed
  INET: Waleed.Khedr_at_FMR.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).