Does index compression affect performance in oracle ?

• https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8806017524595
• www.aaoug.org/pres/Index%20Compression.ppt
• https://richardfoote.wordpress.com/2008/02/17/index-compression-part-i-low/
• https://richardfoote.files.wordpress.com/2008/02/index-compression-part-i.pdf

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/

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

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

Index Clustering Factor explained by TOM KYTE

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

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/

who is locking my tables ?

SELECT owner || '.' || object_name obj,
         oracle_username || ' (' || s.status || ')' oruser,
         os_user_name osuser,
         machine computer,
         l.process unix,
         '''' || s.sid || ',' || s.serial# || '''' ss,
         r.name rs,
         TO_CHAR (s.logon_time, 'yyyy/mm/dd hh24:mi:ss') time
    FROM v$locked_object l,
         dba_objects o,
         v$session s,
         v$transaction t,
         v$rollname r
   WHERE     l.object_id = o.object_id
         AND s.sid = l.session_id
         AND s.taddr = t.addr
         AND t.xidusn = r.usn
ORDER BY obj,osuser, ss

Simple shell script to purge WRH\$_SQL_PLAN until a specified date and reclaim space on SYSAUX tablespace

nbparam=$#

case $nbparam in
      0) echo "Usage = sh purge_sql_plan.sh <until_date>"
         exit 1;;
      *);;
   esac

export date=$1

sqlplus 'CONN_STRING' <<aa
set echo on timing on
DECLARE
   min_date   DATE;
BEGIN
   SELECT MIN (timestamp) INTO min_date FROM SYS.WRH\$_SQL_PLAN;

   WHILE min_date < TO_DATE ('$date', 'DD/MM/YYYY')
   LOOP
      DBMS_OUTPUT.put_line ('Date : ' || min_date);

      DELETE FROM SYS.WRH\$_SQL_PLAN
            WHERE timestamp <= min_date;

      COMMIT;
      min_date := min_date + 1;
   END LOOP;
END;
/

ALTER TABLE SYS.WRH\$_SQL_PLAN move parallel tablespace sysaux;
ALTER TABLE SYS.WRH\$_SQL_PLAN noparallel;
alter index SYS.WRH\$_SQL_PLAN_PK rebuild parallel;
alter index SYS.WRH\$_SQL_PLAN_PK noparallel;

exit;
aa

Best way to maximize cursor reuse in oracle

Reusing a cursor means compiling a SQL statement just once, but executing it more than once. When a SQL statement is compiled, the database checks the syntax and chooses a query path. By doing this only once, but executing this statement several times, you can obtain an improvement in performance.

It is good practice to limit the number of times a cursor has to be parsed the optimal number of parses is one, of course. One option to achieve this ideal would be to preparse every possible cursor your application might ever execute. That way, every cursor would already be waiting in the shared pool when your application starts up. However, this approach would be extremely difficult to maintain in a large application or in one that allows ad hoc queries. Thus, it is better to take the hit the first time a cursor executes and then ensure that it is reused whenever possible later on.
Oracle uses a complex algorithm to decide whether a cursor that is about to be executed may reuse an already compiled version from the shared pool.

• One of the best ways to take advantage of automatically reformatting cursors to promote reuse is to put them into PL/SQL.The PL/SQL compiler reformats the mismatched cursors, thus producing a single cursor in the shared pool. On the surface, this may seem to be a very small improvement, but implemented across an application, it can produce big performance gains because the shared pool latches have fewer cursors to keep track of.
• PL/SQL can virtually eliminate all hard parses just by moving the cursors into a stored procedure.
• The PL/SQL compiler promotes reuse by being more forgiving regarding the structure of cursors.
• Once a cursor is compiled into a PL/SQL procedure, package, or function, it is automatically considered valid and parsed as long as the procedure, package, or function remains valid.
• PL/SQL compiler makes some extra effort to facilitate cursor reuse by checking for small differences like extra whitespace, uppercase versus lowercase, and line breaks.
• Another factor to consider when planning for cursor reuse is the use of literal values.
• A setting is available for the CURSOR_SHARING parameter : FORCE. And it does just that force cursors to be shared strictly on the basis of the text they contain after translating literals into bind variables. This method blindly performs literal substitution and forces the CBO to create an execution plan based on it. This is not always optimal, because the CBO might make a better decision if it knew the explicit value.

Beyond matching the text of a cursor, there are several other factors influencing cursor reusefor example, optimizer statistics and Globalization Support (previously National Language Support, or NLS) setting mismatches. In such cases, it's not enough to simply match ASCII values.

Note: The cursor_sharing=similar option has been deprecated in Oracle 11g and will be removed in version 12 per MOSC Note 1169017.1
References

• http://docs.oracle.com/cd/E38689_01/pt853pbr0/eng/pt/tpcr/task_ReusingaCursor-c07aed.html
• http://www.vsevolod.com/magz/ORplSQLl/0596005873/oracleplsqldba-chp-2-sect-1.html
• Oracle PL/SQL for DBA By Arup Nanda, Steven Feuerstein

Why you should use oracle advanced compression

• Explosion in Data Volumes
• As data volume expands performance often declines
• Disk costs money
• Transparent to applications, SQL, and PL/SQL
• Use spare CPU cycles to decrease the bytes written and read
• Transparent to applications, SQL, and PL/SQL
• May improve performance by requiring the transfer of  fewer bytes from disk through the network, into the CPU,  to be stored in the buffer cache
• Increase the amount of data stored on existing disk
• Compression is performed at the block level
• Data Guard Network Compression
• Data Pump Compression
• Fast RMAN Compression
• OLTP Table Compression

References

• http://www.morganslibrary.com/pres/ougf_advcomp_2009.pdf

get_hugepages_settings.sh Linux bash script to compute values for the # recommended HugePages/HugeTLB configuration

#!/bin/bash
#
# hugepages_settings.sh
#
# Linux bash script to compute values for the
# recommended HugePages/HugeTLB configuration
#
# Note: This script does calculation for all shared memory
# segments available when the script is run, no matter it
# is an Oracle RDBMS shared memory segment or not.
# Check for the kernel version
KERN=`uname -r | awk -F. '{ printf("%d.%d\n",$1,$2); }'`
# Find out the HugePage size
HPG_SZ=`grep Hugepagesize /proc/meminfo | awk {'print $2'}`
# Start from 1 pages to be on the safe side and guarantee 1 free HugePage
NUM_PG=1
# Cumulative number of pages required to handle the running shared memory segments
for SEG_BYTES in `ipcs -m | awk {'print $5'} | grep "[0-9][0-9]*"`
do
   MIN_PG=`echo "$SEG_BYTES/($HPG_SZ*1024)" | bc -q`
   if [ $MIN_PG -gt 0 ]; then
      NUM_PG=`echo "$NUM_PG+$MIN_PG+1" | bc -q`
   fi
done
# Finish with results
case $KERN in
   '2.4') HUGETLB_POOL=`echo "$NUM_PG*$HPG_SZ/1024" | bc -q`;
          echo "Recommended setting: vm.hugetlb_pool = $HUGETLB_POOL" ;;
   '2.6' | '3.8') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
    *) echo "Unrecognized kernel version $KERN. Exiting." ;;
esac
# End

A simple way to determine the maximum I/O requests per second and megabytes of I/O per second that can be sustained by your storage subsystem

The I/O calibration feature of Oracle Database is accessed using the DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure. This procedure issues an I/O intensive read-only workload, made up of one megabyte of random of I/Os, to the database files to determine the maximum IOPS (I/O requests per second) and MBPS (megabytes of I/O per second) that can be sustained by the storage subsystem.

The I/O calibration occurs in two steps:

• In the first step of I/O calibration with the DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure, the procedure issues random database-block-sized reads, by default, 8 KB, to all data files from all database instances. This step provides the maximum IOPS, in the output parameter max_iops, that the database can sustain. The value max_iops is an important metric for OLTP databases. The output parameter actual_latency provides the average latency for this workload. When you need a specific target latency, you can specify the target latency with the input parameter max_latency (specifies the maximum tolerable latency in milliseconds for database-block-sized IO requests).
• The second step of calibration using the DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure issues random, 1 MB reads to all data files from all database instances. The second step yields the output parameter max_mbps, which specifies the maximum MBPS of I/O that the database can sustain. This step provides an important metric for data warehouses.

The calibration runs more efficiently if the user provides the num_physical_disks input parameter, which specifies the approximate number of physical disks in the database storage system.

Due to the overhead from running the I/O workload, I/O calibration should only be performed when the database is idle, or during off-peak hours, to minimize the impact of the I/O workload on the normal database workload.

To run I/O calibration and assess the I/O capability of the storage subsystem used by Oracle Database, use the DBMS_RESOURCE_MANAGER.CALIBRATE_IOprocedure:

SET SERVEROUTPUT ON
DECLARE
  lat  INTEGER;
  iops INTEGER;
  mbps INTEGER;
BEGIN
-- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (<DISKS>, <MAX_LATENCY>, iops, mbps, lat);
   DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 10, iops, mbps, lat);
 
  DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
  DBMS_OUTPUT.PUT_LINE ('latency  = ' || lat);
  dbms_output.put_line('max_mbps = ' || mbps);
end;
/

When running the DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure, consider the following:

• Only run one calibration at a time on databases that use the same storage subsystem. If you simultaneously run the calibration across separate databases that use the same storage subsystem, the calibration will fail.
• Quiesce the database to minimize I/O on the instance.
• For Oracle Real Application Clusters (Oracle RAC) configurations, ensure that all instances are opened to calibrate the storage subsystem across nodes.
• For an Oracle Real Application Clusters (Oracle RAC) database, the workload is simultaneously generated from all instances.
• The num_physical_disks input parameter is optional. By setting the num_physical_disks parameter to the approximate number of physical disks in the database's storage system, the calibration can be faster and more accurate.
• In some cases, asynchronous I/O is permitted for data files, but the I/O subsystem for submitting asynchronous I/O may be maximized, and I/O calibration cannot continue. In such cases, refer to the port-specific documentation for information about checking the maximum limit for asynchronous I/O on the system.

At any time during the I/O calibration process, you can query the calibration status in the V$IO_CALIBRATION_STATUS view. After I/O calibration is successfully completed, you can view the results in the DBA_RSRC_IO_CALIBRATE table.

More infos here ...

Is my index used ?

This oracle function will help you quickly check if an index is used or not, you have to enable his monitoring before

CREATE OR REPLACE FUNCTION is_index_used (owner         VARCHAR,
                                               table_name    VARCHAR,
                                               index_name    VARCHAR)
   RETURN VARCHAR
IS
   is_used   VARCHAR (3);
   cnt       NUMBER;
BEGIN
   is_used := 'NO';

   SELECT COUNT (*) CNT
     INTO cnt
     FROM SYS."_CURRENT_EDITION_OBJ" io,
          SYS."_CURRENT_EDITION_OBJ" T,
          sys.ind$ i,
          sys.USER$ iu,
          sys.USER$ tu,
          sys.object_usage ou
    WHERE     io.owner# = iu.USER#
          AND i.obj# = io.obj#
          AND io.obj# = ou.obj#(+)
          AND T.obj# = i.bo#
          AND T.owner# = Tu.USER#
          AND i.type# NOT IN (4, 8, 9)
          AND BITAND (io.flags, 128) <> 128
          AND iu.name = owner
          AND io.name = index_name
          AND T.name = table_name
          AND DECODE (BITAND (i.flags, 65536), 0, 'NO', 'YES') = 'YES'
          AND DECODE (BITAND (ou.flags, 1),  0, 'NO',  NULL, NULL,  'YES') =
                 'YES';

   IF cnt > 0
   THEN
      is_used := 'YES';
   ELSE
      is_used := 'NO';
   END IF;

   RETURN is_used;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN is_used;
   WHEN OTHERS
   THEN
      -- Consider logging the error and then re-raise
      RAISE;
END;
/

I recommend to wait minimum one week after enabling monitoring before you check it with a simple call like this ...

select INDEX_NAME,is_index_used(OWNER,TABLE_NAME,INDEX_NAME)  from dba_indexes where owner = 'FOMI' and table_name = 'ACCOUNTS'

How to configure the IBM storage subsystem to achieve the best performance and availability possible

• Designate a hot spare disk drive in each expansion drawer.
• Create a RAID 10 array across as many disk drives as possible
• Create a logical drive that is two-thirds the capacity of the RAID 10 array
• Set the stripe size to 512 KB for each logical drive. The 512 KB stripe size provided
  the best results in IBM testing using the ORION utility
• Change cache settings on the logical drive
• Disable dynamic cache read prefetch.
• Map the logical drive to the host system

If you are considering or implementing an Enhanced Remote Mirroring solution, segment the data structure to the smallest size necessary. Small segments limit the amount of data to be transferred across the WAN links

Do not multiplex to the same disk drive

Multiplexing to the same disk drive introduces twice the amount of I/O to the disk drive. Also, no
redundancy exists in case of failure or corruption of the disk drive

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