RE: How to find the last execution time of a Procedure.

Raj,

I am no X$ expert either, but X$KGLOB is exposed to us lowly DBAs as V$DB_OBJECT_CACHE and KGLHDEXC is actually the EXECUTIONS column.

Prasada, you can check V$DB_OBJECT_CACHE for TYPE in ('PACKAGE','PACKAGE BODY') and KEPT = 'NO' and keep pinning these using DBMS_POOL.KEEP via a scheduled job. After a while, all those used packages will not only become KEPT (and provide some side benefit of reducing reloads), you will not have to store them back into the database... The KEPT = NO will avoid having to revisit/manipulate those objects that were previously pinned. Of course, this assumes that there is adeqauet Shared pool space and the Db is not restarted in-between :)

YMMV!
John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)

Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available!

• The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers **

>-----Original Message-----
>From: Jamadagni, Rajendra [mailto:Rajendra.Jamadagni_at_espn.com]
>Sent: Thursday, January 22, 2004 11:00 AM
>To: Multiple recipients of list ORACLE-L
>Subject: RE: How to find the last execution time of a Procedure.
>
>
>But you better check with experts as my knowledge of x$ is
>feather-weight ... also there is a column on x$kglob called
>kglhdexc ... to me it seems the execution count (I feel like
>"Mr. Monk" already). so if execution count is > 0 then you
>can say that it actually got executed.
>
>But if this doesn't work, in the next CTOUG meeting, I'll try
>to hide away from you.
>
>YMMV
>Raj
>---------------------------------------------------------------
>-----------------
>Rajendra dot Jamadagni at nospamespn dot com
>All Views expressed in this email are strictly personal.
>QOTD: Any clod can have facts, having an opinion is an art !
>
>
>-----Original Message-----
>[mailto:Prasada.Gunda_at_hartfordlife.com]
>Sent: Thursday, January 22, 2004 1:24 PM
>To: Multiple recipients of list ORACLE-L
>
>
>
>Thanks for input Raj.
>
>I was also thinking on the same lines (Querying v$views
>periodically and
>store it in some metadata table) if there is no easier way to
>figure out
>from DBA_ views.
>
>As far as changing the production code, as you know, It has
>to go thru the
>dev/test databases first and then go thru the release process
>to implement
>into the production. It is painful process.
>
>I will use x$kglob instead of changing production code and all
>that release
>stuff. Thanks for your help, Raj.
>
>Best Regards,
>Prasad
>860 843 8377
>
>***************************************************************
>***********************
>This e-mail message is confidential, intended only for the
>named recipient(s) above and may contain information that is
>privileged, attorney work product or exempt from disclosure
>under applicable law. If you have received this message in
>error, or are not the named recipient(s), please immediately
>notify corporate MIS at (860) 766-2000 and delete this e-mail
>message from your computer, Thank you.
>***************************************************************
>***********************4
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Jamadagni, Rajendra
> INET: Rajendra.Jamadagni_at_espn.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: John Kanagaraj
  INET: john.kanagaraj_at_hds.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).