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.

execute to parse ratio explained by TOM KYTE

If the number of parse calls is near the number of execute calls, then this ratio drifts towards zero (as yours is). As the number of execute calls increases (while holding parse calls constant), this number drifts towards 100%. That means you have parsed a statement ONCE and executed it MANY TIMES (that is good, that is best) .


Source : https://asktom.oracle.com/pls/asktom/fp=100:11:0::::P11_QUESTION_ID:1594740500346667363

Parse CPU to Parse Elapsd % : which value is good ?

Low Value for this ratio is an indicator of latching problem. Investigate the Latch Sections in AWR and Statspack report for contention on library cache and shared pool latches.

Ideally Parse Elapsed must be equal to Parse CPU, i.e., only CPU time is used for parsing. In that case the ratio is 100%. If wait time is more then the ratio will be less.

Source : https://blogs.oracle.com/myoraclediary/entry/what_is_parse_cpu_to

pl sql procedure to reclaim space in a datafile

This procedure can quickly help to reclaim space above the HWM in a datafile

CREATE OR REPLACE PROCEDURE reclaim_datafile_space (fileid NUMBER)
IS
   taille       NUMBER;
   block_size   INTEGER;
BEGIN
   SELECT VALUE
     INTO block_size
     FROM V$PARAMETER
    WHERE NAME = 'db_block_size';

   SELECT CEIL ( (highblock * block_size + block_size) / 1024)
     INTO taille
     FROM (  SELECT file_id, MAX (block_id + blocks) highblock
               FROM dba_extents
              WHERE file_id = fileid
           GROUP BY file_id);

   EXECUTE IMMEDIATE
      'alter database datafile ' || fileid || ' resize ' || taille || 'K';
END;
/

It is based on Tanel Poder script trim_database 

Index Clustering Factor explained by TOM KYTE

http://www.oracle.com/technetwork/issue-archive/2012/12-sep/o52asktom-1735913.html