Using the dbms_shared_pool.markhot procedure to minimize contention on most pinned objects in the library

Sometimes when you have too much contention on the library, you should check the objects that are most pinned in the library cache and mark it as hot using dbms_shared_pool.markhot() procedure that creates multiple copies of the same object in the shared in order to minimize contention on theses objects.
the following query will help get these objects :

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 <= 30;

Source :

https://andreynikolaev.wordpress.com/2011/05/01/divide-and-conquer-the-true-mutex-contention/

combine flashback logs and archived logs to flashback a database

Sometimes i want to flashback our standby database to replay some applications that are performing not well in production, but sometimes i don't have enough flashback database log data to do FLASHBACK and oracle raises an "RMAN-06796". In this case i just have to determine my oldest flashback log and if have archived log older than the oldest flashback log i can register these archives using rman and flashback the database until the sequence corresponding to my criterias.

You can use this command to register older archived log in the catalog and this command to flashback the database.

Don't worry about UnOptimized reads in an oracle 11g AWR report

Every time you read an 11g AWR report you see a section called
SQL ordered by Physical Reads (UnOptimized) , an UnOptimized read in Oracle 11g is a read that is not found in the Database Smart Flash Cache. On Exadata an UnOptimized read is not found in the Database Smart Flash Cache or in the Exadata Cell Smart Flash Cache.

So don't worry if you see too much UnOptimized Read Reqs

Sources :

http://www.perftuning.com/unoptimized-reads-oracle-database-11gr2/

Should i enable automatic SGA tuning ?

Automatic SGA tuning let oracle decide when to move memory between db cache and other pools, but it's possible that sometimes when oracle tries to resize a pool and don't find enough free chunks in other pools the database appears to hang.

The following query help me to monitor the resize operations :

select component,oper_type,status,count(*) from (select

        component,

        oper_type,

        oper_mode,

        parameter,

        initial_size,

        target_size,

        final_size,

        status,

        to_char(start_time,'dd-mon hh24:mi:ss') start_time,

        to_char(end_time,'dd-mon hh24:mi:ss')   end_time

from

        v$sga_resize_ops) group by component,oper_type,status; 

This query help me to determine which component oracle could not shrink or grow.

If you get too many ORA-04031 errors with ASMM enabled, i recommend you to turn it off first by setting sga_target = 0.

You should set a lower limit for each pool, so that oracle will not try to shrink it below the limit.

Sources :

• https://jonathanlewis.wordpress.com/2006/12/04/resizing-the-sga/ 
• https://jonathanlewis.wordpress.com/2007/04/16/sga-resizing/ 
• http://www.oraclemagician.com/white_papers/SGA_resizing.pdf

Oracle Goldengate discard file reader and formatter

I use this wonderful PL SQL package by Luke Davies to read goldengate discard files and better diagnose replicat problems.