Thursday, July 10, 2014

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

Wednesday, July 2, 2014

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

Saturday, June 7, 2014

shell script to delete all not used oracle semaphores and shared memory

Here, save and try this script (kill_ipcs.sh) on your shell:
#!/bin/bash

ME=`whoami`

IPCS_S=`ipcs -s | egrep "0x[0-9a-f]+ [0-9]+" | grep $ME | cut -f2 -d" "`
IPCS_M=`ipcs -m | egrep "0x[0-9a-f]+ [0-9]+" | grep $ME | cut -f2 -d" "`
IPCS_Q=`ipcs -q | egrep "0x[0-9a-f]+ [0-9]+" | grep $ME | cut -f2 -d" "`


for id in $IPCS_M; do
  ipcrm -m $id;
done

for id in $IPCS_S; do
  ipcrm -s $id;
done

for id in $IPCS_Q; do
  ipcrm -q $id;
done

Thursday, May 29, 2014

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

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

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