Hi ,
I was reading for pinning in shared pool article from your side ,so that I can clear some of doubts.
1) I have read Tom Kyte article "https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2667245192483" and he mentioned in this article ,"Pinning was necessary in 7.x especially when using MTS (shared server) because all memory for the UGA as well as the library cache came from the same place. The shared pool (the variable size of the SGA). Now, shared sql (sql/plsql/library stuff) is managed in an LRU -- least recently used -- with aging. The older you are in the pool, the less you've been used -- out you go. also, the shared pool was setup for small allocations, typically 4k in size or so." and in one oracle blog " https://blogs.oracle.com/ebstech/pinning-objects-to-improve-apps-performance" pinning is good for performance.
So am I missing something here.
2) In every oracle doc for V$DB_OBJECT_CACHE ,They mentioned for execution column in view V$DB_OBJECT_CACHE "EXECUTIONS Not used
See Also: "V$SQLAREA" to see actual execution counts"
So does that mean "exections" column value which we get from this view is wrong. Then How do we identifying Candidate Objects for Keeping in the Shared Pool.To identify candidates that should be kept in the shared pool, first run the following query:
select substr(owner,1,10)||'.'||substr(name,1,35) "ObjectName", type, sharable_mem,loads, executions, kept from v$db_object_cache where type in ('TRIGGER','PROCEDURE','PACKAGE BODY','PACKAGE') and executions >0 order by executions desc,loads desc,sharable_mem desc
Reference Doc: https://blogs.oracle.com/ebstech/pinning-objects-to-improve-apps-performance.