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/