Index Clustering Factor explained by TOM KYTE

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

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/

Disavantages for enabling row movement on oracle tables

It will necessarily consume processing resources on your machine while running (it will 
read the table, it will delete/insert the rows at the bottom of the table to move them 
up, it will generate redo, it will generate undo).

If you had densely packed blocks with lots of "pretty much empty blocks", and the densely 
packed blocks were organized by index key value -- spraying them about the top of the table could 
hurt the clustering factor.
 
Do you have any application that expects rowid to be constant for a row?  If so - then it is wrong
 
 
  
 
 

You should start the X-Window manager only when you really need it

I tend to do a lot of routine tasks from the command line. Most commercial Linux distributions, however, default to starting an X-Window manager following system initialization. That's a lot of system resources to have running.

To change this behavior, edit the /etc/inittab file and locate the line that reads: id:5:initdefault and change it to id:3:initdefault.The system will start with a command line login, and when the need arises to run a window manager, it's easy to simply type startx

What is a log file sync ?

Log file sync is a client wait event.  It is the wait event your clients wait on when 
they say "commit".  It is the wait for LGWR to actually write their redo to disk and 
return back to them.  You can "tune" this by making lgwr faster (no raid 5 for example) 
and committing less frequently and generating less redo (BULK operations generate less 
redo than row by row do)

The other one is a background wait. LGWR is waiting for forgrounds to finish a current 
copy affecting the data LGWR is about to process.

HOWEVER, that said, tuning either of these will have no noticable affect on your systems 
performance whatsoever!  It certainly looks like "enqueue" is your wait and that is all 
about application design - those are heavyweight locks induced by the application logic 
itself.  You would be best served by looking at the *application* not at the "system" at 
this point. 

More infos here

cached I/O vs direct I/O

cached I/O

direct I/O

1. Application issues a read request
2. Kernel looks for requested data in file buffer cache
3. Requested data not present in file buffer cache
4. Kernel reads data from disk
5. Read data is cached in file buffer cache
6. Read data is copied from file buffer cache to applicatio buffer

1. Application issues a read request
2. Kernel initiates a disk request
3. Requested data transfered from disk to application buffer

AIO Tuning Recommendations

Parameters for tuning asynchronous I/O

• minservers The minimum number of aioservers that are started for asynchronous disk I/O. The default value is 1
• maxservers The maximum number of aioservers that are started for asynchronous disk I/O. The default value is 10. Since each aioserver uses memory, this number should not be much larger than the
  expected amount of simultaneous asynchronous disk I/O requests
• maxreqs Maximum number of asynchronous disk I/O requests that can be stored in the queue. The default value is 4096

Tuning Recommendations

• minserver : 2 or Number of CPUs -1, whatever is larger
• maxserver : Two times the number of datafiles
• maxreqs : 12288
• for large systems the number of aioserver processes may become very large and the maximum number of processes per user has to be adopted

Strategies and techniques to resolve 'log file sync' waits

Commit is not complete until LGWR writes log buffers including commit redo recods to log files. In a
nutshell, after posting LGWR to write, user or background processes waits for LGWR to signal back
with 1 sec timeout. User process charges this wait time as 'log file sync' event.

Root causes of 'log file sync', essentially boils down to few scenarios :

• Disk I/O performance to log files is not good enough. Even though LGWR can use
  asynchronous I/O, redo log files are opened with DSYNC flag and buffers must be
  flushed to the disk (or at least, written to disk array cache in the case of SAN) before
  LGWR can mark commit as complete.
• LGWR is starving for CPU resource. If the server is very busy, then LGWR can starve
  for CPU too. This will lead to slower response from LGWR, increasing 'log file sync'
  waits. After all, these system calls and I/O calls must use CPU. In this case, 'log file
  sync' is a secondary symptom and resolving root cause for high CPU usage will reduce
  'log file sync' waits.
• Due to memory starvation issues, LGWR can be paged out. This can lead to slower
   response from LGWR too
• LGWR is unable to complete writes fast enough due to file system or unix buffer
  cache limitations.
• LGWR is unable to post the processes fast enough, due to excessive commits. It is quite
  possible that there is no starvation for cpu or memory and I/O performance is decent enough. Still, if
  there are excessive commits,  then LGWR has to perform many writes/semctl calls and this can
  increase 'log file sync' waits. This can also result in sharp increase in 'redo wastage' statistics'
• With Private strands, a process can generate few Megabytes of redo
  before committing. LGWR must write generated redo so far and processes must wait for 'log file sync'
  waits, even if redo generated from other processes is small enough
• LGWR is suffering from other database contention such as enqueue waits or latch contention.
  For example, we have seen LGWR freeze due to CF enqueue contention.

Finding and understanding root cause is essential to resolve a performance issue. 

• If I/O bandwith is an issue, then doing anything other than improving I/O bandwidth is not
  useful. Switching to file systems providing better write throughput is one option. RAW devices are
  another option. Reducing # of log file members in a group is another option as it reduces # of write
  calls. But, this option comes with a cost.
• If CPU starvation is an issue, then reducing CPU starvation is the correct step to resolve it.
  Increasing priority of LGWR is a work around
• If commit rate is higher, then decreasing commits is correct step but, in few case, if that is not
  possible, increasing priority of LGWR (using nice) or increasing priority class of LGWR to RT might
  provide some relief.
• Solid State Disk devices also can be used if redo size is extreme. In that case, it is also
  preferable to decrease redo size.
• If excessive redo size is root cause, redo size can be reduced using various techniques

Ways to Improve IO Speed

• Put redo on dedicated disk 
• Use Raw Device or Direct IO  
• Consider Ram Disks 
• Alternate disks for redo and archiving of redo  

vmstat relevant columns with descriptions

Column	Description
kthr	Kernel thread state changes per second over the sampling interval.
r	Number of kernel threads placed in run queue.
b	Number of kernel threads placed in the Virtual Memory Manager (VMM) wait queue (awaiting resource, awaiting input/output).
p	The number of threads waiting on raw I/Os (bypassing journaled file system (JFS)) to complete.
fi/fo	Number of file pages paged in/out per second.
cpu	Breakdown of percentage usage of CPU time. For multiprocessor systems, CPU values are global averages among all processors. Also, the I/O wait state is defined system-wide and not per processor.
us	Average percentage of CPU time executing in the user mode.
sy	Average percentage of CPU time executing in the system mode.
id	Average percentage of time that CPUs were idle and the system did not have an outstanding disk I/O request.
wa	CPU idle time during which the system had outstanding disk/NFS I/O request(s). If there is at least one outstanding I/O to a disk when wait is running, the time is classified as waiting for I/O. Unless asynchronous I/O is being used by the process, an I/O request to disk causes the calling process to block (or sleep) until the request has been completed. Once an I/O request for a process completes, it is placed on the run queue. If the I/Os were completing faster, more CPU time could be used.
pc	Number of physical processors consumed. Displayed only if the partition is running with shared processor.
ec	The percentage of entitled capacity consumed. Displayed only if the partition is running with the shared processor.

Aix Virtual Memory Management internals