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.

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

Which segments are above the HWM in a tablespace ?

https://jonathanlewis.wordpress.com/tablespace-hwm/

Steps to encrypt oracle network data

https://kb.berkeley.edu/page.php?id=23274

oracle wait events explained by experts : asynch descriptor resize

Tanel Poder

The “direct path loader” (KCBL) module is used for performing direct path IO in Oracle, such as direct path segment scans and reading/writing spilled over workareas in temporary tablespace. Direct path IO is used whenever you see “direct path read/write*” wait events reported in your session. This means that IOs aren’t done from/to buffer cache, but from/to PGA directly, bypassing the buffer cache. This KCBL module tries to dynamically scale up the number of asynch IO descriptors (AIO descriptors are the OS kernel structures, which keep track of asynch IO requests) to match the number of direct path IO slots a process uses. In other words, if the PGA workarea and/or spilled-over hash area in temp tablespace gets larger, Oracle also scales up the number of direct IO slots. Direct IO slots are PGA memory structures helping to do direct IO between files and PGA. In order to be able to perform this direct IO asynchronously, Oracle also dynamically scales up the number of OS asynch IO descriptors, one for each slot (up to 4096 descriptors per process). When Oracle doesn’t need the direct IO slots anymore (when the direct path table scan has ended or a workarea/tempseg gets cancelled) then it scales down the number of direct IO slots and asynch IO descriptors. Scaling asynch IO descriptors up/down requires issuing syscalls to OS (as the AIO descriptors are OS kernel structures). I guess this is supposed to be an optimization, to avoid running out of OS AIO descriptors, by releasing them when not they’re not needed, but as that Metalink note mentioned, the resize apparently sucks on Linux. Perhaps that’s why other ports also suffer and have seen the same wait event. The “asynch descriptor resize” event itself is really an IO wait event (recorded in the wait class Other though), waiting for reaping outstanding IOs. Once this wait is over, then the OS call to change the amount of asynch IO descriptors (allocated to that process) is made. There’s no wait event recorded for the actual “resize” OS call as it shouldn’t block. So, the more direct IO you do, especially when sorting/hashing to temp with frequent workarea closing/opening, the more of this event you’ll see (and it’s probably the same for regular tablespace direct path IO too). This problem wouldn’t be noticeable if Oracle kept async io descriptors cached and wouldn’t constantly allocated/free them. Of course then you may end up running out of aio descriptors in the whole server easier. Also I don’t know whether there would be some OS issues with reusing cached aio descriptors, perhaps there is a good reason why such caching isn’t done. Nevertheless, what’s causing this wait event is too frequent aio descriptor resize due to changes in direct IO slot count (due to changes in PGA workarea/temp segment and perhaps when doing frequent direct path scans through lots of tables/partitions too). So, the obvious question here is what to do about this wait event? Well, first you should check how big part of your total response time this event takes at all? If it’s someting like 1% of your response time, then this is not your problem anyway and troubleshooting this further would be not practical – it’s just how Oracle works :) If it’s something like 20% or more of your response time, then it’s clearly a problem and you’d need to talk to Oracle Support to sort out the bug If it’s anything in between, make sure you don’t have an IO problem first, before telling that this is a bug. In one recent example I saw direct path reads take over a second on average when this problem popped up. The asynch descriptor resize wait event may well disappear from the radar once you fix the root cause – slow IO (or SQL doing too much IO). Remember, the asynch descriptor resize wait event, at least on Linux, is actually an IO wait event, the process is waiting for outstanding IO completion before the descriptor count increase/decrease can take place.

Source : http://blog.tanelpoder.com/2010/11/23/asynch-descriptor-resize-wait-event-in-oracle/

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/

PL SQL procedure to save a file from a Filesystem to BLOB column

DECLARE
   v_src_loc   BFILE := BFILENAME ('ORACLE_DIR', 'FILE_NAME');
   v_amount    INTEGER;
   v_b         BLOB;
BEGIN
   DBMS_LOB.OPEN (v_src_loc, DBMS_LOB.LOB_READONLY);
   v_amount := DBMS_LOB.GETLENGTH (v_src_loc);

      UPDATE BLOB_TABLE
         SET BLOB_COLUMN = EMPTY_BLOB ()
   RETURNING BLOB_COLUMN 
        INTO v_b;

   DBMS_LOB.LOADFROMFILE (v_b, v_src_loc, v_amount);
   DBMS_LOB.CLOSE (v_src_loc);
   commit;
END;
/

PL SQL procedure to extract a BLOB to a Filesystem

This PL SQL procedure allows you to extract a BLOB column to FS

DECLARE
   l_file       UTL_FILE.FILE_TYPE;
   l_buffer     RAW (32767);
   l_amount     BINARY_INTEGER := 32767;
   l_pos        NUMBER := 1;
   l_blob       BLOB;
   l_blob_len   NUMBER;
BEGIN
   SELECT YOUR_BLOB_COLUMN
     INTO l_blob
     FROM YOU_BLOB_TABLE;   

   l_blob_len := DBMS_LOB.getlength (l_blob);

   -- Open the destination file.
   l_file :=
      UTL_FILE.fopen ('ORACLE_DIR',
                      'FILE_NAME',
                      'wb',
                      32767);

   WHILE l_pos < l_blob_len
   LOOP
      DBMS_LOB.read (l_blob,
                     l_amount,
                     l_pos,
                     l_buffer);
      UTL_FILE.put_raw (l_file, l_buffer, TRUE);
      l_pos := l_pos + l_amount;
   END LOOP;

   -- Close the file.
   UTL_FILE.fclose (l_file);
END;