Which objects are pinned most of the time in the library cache

If you want to reduce the "library cache : mutex X" concurrency event you have to find which objects are pinned most of the time in the library cache with this query

SELECT *
  FROM (  SELECT CASE
                    WHEN (kglhdadr = kglhdpar) THEN 'Parent'
                    ELSE 'Child ' || kglobt09
                 END
                    cursor,
                 kglhdadr ADDRESS,
                 SUBSTR (kglnaobj, 1, 20) NAME,
                 kglnahsh HASH_VALUE,
                 kglobtyd TYPE,
                 kglobt23 LOCKED_TOTAL,
                 kglobt24 PINNED_TOTAL,
                 kglhdexc EXECUTIONS,
                 kglhdnsp NAMESPACE
            FROM x$kglob                         -- where kglobtyd != 'CURSOR'
        ORDER BY kglobt24 DESC)
 WHERE ROWNUM <= 20;

Then you can use the dbms_shared_pool.markhot() to mark them as hot.

References

• https://juliandontcheff.wordpress.com/2013/02/12/reducing-library-cache-mutex-x-concurrency-with-dbms_shared_pool-markhot/
• https://andreynikolaev.wordpress.com/2011/05/01/divide-and-conquer-the-true-mutex-contention/
• http://omarfaruq.blogspot.fi/2012/07/concurrency-waits-library-cache-mutex-x.html
• https://jagjeet.wordpress.com/2011/12/12/library-cache-mutex-x/