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 ...

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)
   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'