Thursday, May 29, 2014 Linux bash script to compute values for the # recommended HugePages/HugeTLB configuration

# 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
# 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]*"`
   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`
# 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." ;;
# End

Wednesday, May 28, 2014

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:
  lat  INTEGER;
  iops INTEGER;
  mbps INTEGER;
   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);
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 ...

Monday, May 26, 2014

Be careful when you resize a datafile in a Data Guard configuration

If you resize a system datafile on a primary database in a Data Guard configuration, the media recovery process on the standby will shutdown and stop applying redo ...

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)
   is_used   VARCHAR (3);
   cnt       NUMBER;
   is_used := 'NO';

     INTO cnt
          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# =
          AND T.owner# = Tu.USER#
          AND i.type# NOT IN (4, 8, 9)
          AND BITAND (io.flags, 128) <> 128
          AND = owner
          AND = index_name
          AND = table_name
          AND DECODE (BITAND (i.flags, 65536), 0, 'NO', 'YES') = 'YES'
          AND DECODE (BITAND (ou.flags, 1),  0, 'NO',  NULL, NULL,  'YES') =

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

   RETURN is_used;
      RETURN is_used;
      -- Consider logging the error and then re-raise

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'