El TecnoBaúl de Kiquenet

Kiquenet boring stories

Archive for the ‘Oracle’ Category

Performance DataAccess Oracle and ODP.NET

Posted by kiquenet en 18 junio 2016

FetchSize (OracleDataReader)

A number of blogs and sites mention increasing FetchSize of OracleDataReader to improve performance when fetching big volumes of data (e.g. thousands of rows). There are some documented experiments with exact numbers on this like:


Table had a little more than 155K rows and was size of 31 MB. Yet, it took more than 5 minutes to complete for data adapter to fill data table.

The cause of the problem was the number of round trips client need to accomplish to get all the rows from database. If you can reduce the number of round trips, you can increase the fetch size so that in each turn command object will fetch more rows from database. Here is how it’s impletemented:

using (OracleConnection conn = new OracleConnection())
     OracleCommand comm = new OracleCommand();
     comm.Connection = conn;
     comm.FetchSize = comm.FetchSize * 8;
     comm.CommandText = "select * from some_table";

          OracleDataAdapter adap = new OracleDataAdapter(comm);
          System.Data.DataTable dt = new System.Data.DataTable();

Notice the line with blue font, fetch size of command object increased by 8 times its default which is 128 KB at ODP.Net OracleDataReader also has the FetchSize property. By increasing fetch size, you increase the cache size in memory to fetch rows.

What we gained is up to 96% performance improvement. Here are some timings with different fetch sizes:

Fetch Size             Timing (MI:SS.FF3)
Default (128 KB)       05:20.290
Default x 8 (1 MB)     00:52.941
Default x 32 (4 MB)    00:26.008
Default x 64 (8 MB)    00:12.409

Reference: http://metekarar.blogspot.com.es/2013/04/performance-improvement-for-odpnet.html

Strangely, unless the connection pooling is explicitly disabled (e.g. in the connection string), the increase/decrease of FetchSize stops having any effect. When the pooling is disabled though, it’s clear that the FetchSize can improve the performance (the more records, the bigger the effect).

It turns out that this unexpected behavior is limited to the following conditions: 1) The SELECT statement is exactly the same 2) The pooling is ON 3) The self-tuning is ON

Only in those conditions the first time the FetchSize is set, it gets somehow cached by ODP.NET and attempts to change it don’t work.

References Mark Williams

string with_pool =
  "User Id=hr;
   Data Source=oramag;

Connection Pooling Overview

Making specific connection pooling parameter recommendations is difficult, because workloads can vary greatly. One general recommendation is to have a sufficiently high minimum pool size. I have often seen connection pools drained to the minimum during a lull in activity. When activity picks up rapidly, the connection pool has to create connections quickly rather than use connections in the pool. In most cases, the application server reduces the number of connections and remains relatively idle. Thus, there is no reason to have a low minimum pool size.

Controlling Fetch Size

Retrieving data from the database tier to the middle tier (or client tier) is one of the most expensive operations with respect to performance. If the end user consumes a lot of data, you will want your application to minimize the number of round-trip data fetches.

By default, ODP.NET will read 64KB of data from a result set at a time. You change this value by setting the FetchSize attribute for an OracleDataReader object. However, rather than arbitrarily setting the fetch size, ODP.NET provides the ability to control the number of rows that are fetched from a result set per round trip. The two properties you use to do this are RowSize and FetchSize.

RowSize is a property of the OracleCommand object, and the value for this property is assigned by ODP.NET when the statement associated with the command object is executed. You explicitly set the value of the FetchSize property. For example, if you want to retrieve 100 rows at a time from the result set per round trip, you set the FetchSize property:

dr.FetchSize = cmd.RowSize * 100;

if you are tempted to fetch only a single row at a time to "save resources and increase performance" (I have sometimes heard this advice), you will actually be dramatically reducing, rather than increasing, runtime performance. In addition, for this set of data, there is not much performance increase when fetching more than 1,000 rows at a time. Of course, this is also a narrow data set (with only two small columns). Apply this technique to a result set representative of your environment to determine optimal values for setting your fetch size.

Of course, there is a cost if the fetch size is arbitrarily large. More client-side memory and processor cycles will be needed to store and manage a larger amount of data. The goal is to find a high-performing balance between the number of round trips and the amount of data retrieved per trip.

Statement Caching

You enable the statement caching feature by setting the Statement Cache Size connection string parameter. The default value of this parameter is 0, meaning that statement caching is disabled by default. You set this parameter to the number of statements you plan to keep cached during your application’s lifetime. For example, if you plan to cache 16 statements, you will set this value to 16. ODP.NET will then cache the 16 most recently used statements. The 17th most recently used statement is aged out of the cache by the last 16 unique statements.

With statement caching enabled, ODP.NET will automatically cache any statement you execute. If you have enabled statement caching and you do not want to place a statement in the cache, set the AddToStatementCache property on OracleCommand to false:

string constr =
  "User Id=hr;
   Data Source=oramag;
   Statement Cache Size=1";


create or replace procedure get_sales (p_refcur out sys_refcursor) is


  — open the cursor using the passed in ref cursor
— sys_refcursor is a built in type

  open p_refcur for   select  *   from    sales;


 // connection string — be sure to adjust for your environment
public const string constr = "User Id=sh; Password=sh; Data Source=otndemo; Pooling=false; Enlist=false";

      // enable extended sql tracing
      // this can be used to verify the number of rows fetched
      // uncomment to create trace file in user_dump_dest directory
      // EnableExtendedTrace(con);

static void EnableExtendedTrace(OracleConnection con)
    // create, setup, and execute command to enable extended sql trace
    OracleCommand cmd = con.CreateCommand();

    cmd.CommandText = "alter session set events ‘10046 trace name context forever, level 4’";

    // clean up

      // do the fetch test
      // passing 0 for "rows" will use default fetch size of 64k
      FetchTest(con, 100);
      FetchTest(con, 1000);
      FetchTest(con, 10000);
      FetchTest(con, 0);

static void FetchTest(OracleConnection con, int rows)


      // used to track execution duration
      DateTime timeStart;
      DateTime timeEnd;
      double totalSeconds;

      // create and setup command to call stored procedure
      OracleCommand cmd = con.CreateCommand();
      cmd.CommandText = "get_sales";
      cmd.CommandType = CommandType.StoredProcedure;

      // create the ref cursor parameter and add to command parameter collection
      OracleParameter prm = new OracleParameter("p_refcur", OracleDbType.RefCursor, ParameterDirection.Output);

      // get the data reader
      OracleDataReader dr = cmd.ExecuteReader();

      // There are 3 steps in getting the m_rowSize property value…
      // Step 1 – get the data reader type
      Type type = dr.GetType();

      // Step 2 – get the "m_rowSize" field info
      FieldInfo fi = type.GetField("m_rowSize", BindingFlags.Instance | BindingFlags.NonPublic);

      // Step 3 – get the value of m_rowSize for the dr object
      int rowSize = (int)fi.GetValue(dr);

      // if rows is greater than 0 use to set fetch size,
      // otherwise use default size (64k)

      if (rows > 0)
        // set the fetch size on the dr object using the row size
        dr.FetchSize = rowSize * rows;
      // capture test start time
      timeStart = DateTime.Now;

      // simply loop forcing the entire result set to be fetched
      while (dr.Read())      {      }

      // capture test end time
      timeEnd = DateTime.Now;

      // calculate total seconds for this test
      totalSeconds = timeEnd.Subtract(timeStart).TotalSeconds;

      // display time used for test
      Console.WriteLine("Number of rows = {0,5}: {1,8:###.0000} total seconds, fetch size = {2,9:###,###,###} bytes.", rows, totalSeconds, dr.FetchSize);

      // clean up



From Alex Keh (Oracle)
This video describes ODP.NET best practices for performance:

The slides are here:

The code samples for many of the performance patterns are available in the following tutorials:

Oracle SQL High Performance tuning
Oracle Performance Survival Guide
Troubleshooting Oracle Performance


Bind variables

Array Select

Array insert



Connection Pool (CP)

    Ensure you have enough connections in CP – better to have many than too few.
    OS-authenticated CP available with ODP.NET 11g
    Keep a steady state of CP – never destroy or create large number of connections
    Close/Dispose connections explicitly – don’t rely on garbage collector
    You can monitor CP performance counters using ODP.NET and higher

Bind Variables

    Always use Bind Variables. Using Bind Variables will prevent reparsing of frequently executed statements. Literal value changes in commands force a reparse. Reparsing is fairly expensive CPU operation and requires shared pool locks.

Statement Caching

    Using statement caching retains parsed statement in shared pool.
    Cursor stays open on client side and Metadata remains on client
    Best used/works with Bind Variables
    Caching works with and caches the last 10  executed statements.
    Developer can choose which statement to cache.

Statement Cache Size=0 (no caching)

Statement Cache Size=1 (caching)

With ODP.NET cache size dynamically changes at runtime and provides automatic optimization/self-tuning. Self-tuning is enabled by default and no code changes are required.


Data Retrieval

You can control how much data is retrieved from the database per round-trip. Too much data can cause excessive client-side memory used and too little may cause additional round-trips. You can use OracleCommand.RowSize and OracleDataReader.FetchSize to control the result. FetchSize can be set as multiple of RowSize and RowSize can be dynamicall populated after statement execution.

FetchSize = RowSize X 1
FetchSize = RowSize X 100

Mass Data Movement with Arrays

    PL/SQL associative arrays can be used to pass large amounts of data between .NET and DB of the same data type.
    If you are executing the same statement multiple times, you could use a parameter array binding.

Statement Batching

    You can execute multiple commands in one DB round-trip using OracleDataAdapter.UpdateBatchSize.
    You can use anonymous PL/SQL for disparate or similar statements

ODP.NET DataTypes

    Try and avoid unnecessary datatype conversions between .NET and the DB
    Use OracleParameter.Dbtype

    public void CreateOracleDbParameter()
        OracleParameter parameter = new OracleParameter();
        parameter.ParameterName = "pDName";
        parameter.DbType = DbType.String;
        parameter.Value = "ENGINEERING";
        parameter.SourceColumn = "DName";

Use REF Cursors

With REF Cursors, you can retrieve data as needed and control data retrieved via FetchSize. You can fill a DataSet with just a portion of the REF cursor result. You can pass REF cursors back as input stored procedure parameters. Use OracleRefCursor class.

Oracle Performance Tuning in Visual Studio

    You can tune ad-hoc SQLs in query window

    Tune bad SQL using Oracle Performance Analyzer – requires:

        SYSDBA privilege

        database license for Oracle Diagnostic Pack

        database license for Oracle Tuning Pack


These are built into Oracle Database 10g and are invaluable for diagnosing Performance issues.

AWR – Automatic Workload Repository

    Evolution of statspack

    builtin repository

    captures performance stats at regular intervals

ADDM – Automatic Database Diagnostic Monitor

    Analyses AWR stats and generates recommendations

AWR and ADDM nodes are now available in Visual Studio.




using(OracleConnection conn = new OracleConnection(@"DataSource=source;User Id=username;Password=password;"))
        OracleCommand cmd = new OracleCommand();
        cmd.Connection = conn;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "StoredProcedureName";
        cmd.Parameters.Add("REF_CURSOR_SAMPLE",OracleType.RefCursor).Direction =  ParameterDirection.Output;
        OracleDataReader reader = cmd.ExecuteReader();
        FieldInfo fieldInfo = reader.GetType().GetField("m_rowSize", BindingFlags.Instance | BindingFlags.NonPublic);
        int cmdRowSize = (int)fieldInfo.GetValue(reader);
        reader.FetchSize = cmdRowSize * noOfRowsExpected;
             // do your logic here

Troubleshooting Oracle Performance

ODP.NET. The default fetch size of ODP.NET (65,536) is defined in bytes, not rows. You can change this value through the FetchSize property provided by the  OracleCommand and OracleDataReader classes. The following code snippet is an example of how to set the value for fetching 100 rows.

Notice how the RowSize property of the OracleCommand class is used to compute the amount of memory needed to store the 100 rows. The C# program in the RowPrefetching.cs file provides a complete example:

sql = "SELECT id, pad FROM t";
command = new OracleCommand(sql, connection);
reader = command.ExecuteReader();
reader.FetchSize = command.RowSize * 100;
while (reader.Read())
    id = reader.GetDecimal(0);
    pad = reader.GetString(1);
    // process data

As of ODP.NET version,  you can also change the default fetch size through the following registry entry (<Assembly_Version> is the full version number of Oracle.DataAccess.dll)




  pkg_emp.get_emps (l_emp_cur);
  l_cur_no := DBMS_SQL.TO_CURSOR_NUMBER (l_emp_cur);
  DBMS_SQL.DESCRIBE_COLUMNS(l_cur_no, l_col_cnt, l_rec_tab);

  FOR i IN 1..l_col_cnt
     DBMS_OUTPUT.PUT_LINE(‘Column ‘ || l_rec_tab(i).col_name || ‘ max length of ‘ || TO_CHAR(l_rec_tab(i).col_max_len));



      <add name="DllPath" value="N:\ORACLE\ORA11odacR5"/>
      <add name="FetchSize"             value="65536"/>
      <add name="PromotableTransaction" value="promotable"/>
      <add name="StatementCacheSize"    value="0"/>
      <add name="TraceFileName"         value="%TEMP%\odpnet.trc"/>
      <add name="TraceLevel"            value="0"/>
      <add name="TraceOption"           value="0"/>
  <!– Here we’re telling .NET framework two things:
       – which version of Oracle.DataAccess.dll we want to be used
       – and from where exactly it should load the assembly.
       Any version of Oracle.DataAccess.dll between and
       will be replaced by
       Note that publicKeyToken is "hash" dedicated to Oracle Corp. but might
       change in the future. We checked the token against GAC.
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
        <publisherPolicy apply="no" />
        <assemblyIdentity name="Oracle.DataAccess"
                          culture="neutral" />
        <bindingRedirect oldVersion=""

:: RunOraDUAL.bat
:: Oracle11g [] Instant Client with ODAC
:: Batch routine to launch OraDUAL.exe from newtork drive.
:: ———————————————————
title Oracle11g Instant Client – ODAC
start OraDUAL.exe
:: End

Posted in .NET, DataAccess, Oracle, Performance | Etiquetado: , , , | 2 Comments »

TroubleShooting Oracle–PLSQL: Locks

Posted by kiquenet en 30 noviembre 2015

Issue: Can’t compile a stored procedure when it’s locked

ETL process (dtexeui.exe, DtsDebugHost.exe) call many times to a Package, like it is being used, restricting me from compiling it (the package). 

Monitor de Sesiones de PLSQL Developer

Consultas útiles:

select * from DBA_DDL_LOCKS where name like ‘%PNMKT_CLIENTES%’

select    * from    v$session x where machine like ‘%DS0366%’

Question:  In our development environment we need to re-compile packages as we change our code. Once in a while a session will hold onto a package, like it is being used, restricting me from compiling it.   Is there a way to find out what SID is holding on to the package in order for me to kill it?

Answer:  You can query v$session and join into v$sql to see session and SQL information.  This will show the SID for the session that is holding a PL/SQL package:

   v$session x, v$sqltext y
   x.sql_address = y.address
and  machine like ‘%DS0366%’
–   y.sql_text like ‘%PNMKT_CLIENTES%’;

Esta query te dice los objetos que están bloqueados, de tablas y filas:

decode(L.TYPE,’TM’,’TABLE’,’TX’,’Record(s)’) TYPE_LOCK,
decode(L.REQUEST,0,’NO’,’YES’) WAIT,
concat(‘ ‘,s.PROGRAM) PROGRAM,
FROM v$lock l,dba_objects o,v$session s
AND l.TYPE in (‘TM’,’TX’)

Esta otra te dice si existe algún paquete pillado (no bloqueado):

select /*+ CHOOSE */ a.sid, a.serial#, A.INST_ID,a.username, a.username "DB User",
a.osuser, a.status, a.terminal, a.type ptype, b.owner, b.object, b.type, a.USERNAME "DB User"
from gv$session a, gv$access b
where a.sid=b.sid and a.inst_id = b.inst_id
and b.type<>’NON-EXISTENT’
and (b.owner is not null) and (b.owner<>’SYSTEM’) and (b.owner<>’SYS’)

Si el STATUS te aparece como ACTIVE, ese paquete si lo intentas compilar te dará TIMEOUT y no podrás.


— view all currently locked objects:
SELECT username U_NAME, owner OBJ_OWNER,
object_name, object_type, s.osuser,
  0, ‘Not Blocking’,
  1, ‘Blocking’,
  2, ‘Global’) STATUS,
    0, ‘None’,
    1, ‘Null’,
    2, ‘Row-S (SS)’,
    3, ‘Row-X (SX)’,
    4, ‘Share’,
    5, ‘S/Row-X (SSX)’,
    6, ‘Exclusive’, TO_CHAR(lmode)
FROM gv$locked_object v, dba_objects d,
gv$lock l, gv$session s
WHERE v.object_id = d.object_id
AND (v.object_id = l.id1)
AND v.session_id = s.sid
ORDER BY username, session_id;
— list current locks
SELECT session_id,lock_type,
FROM dba_lock l
WHERE lock_type
NOT IN (‘Media Recovery’, ‘Redo Thread’);

— list objects that have been
— locked for 60 seconds or more:

SELECT SUBSTR(TO_CHAR(w.session_id),1,5) WSID, p1.spid WPID,
SUBSTR(s1.username,1,12) "WAITING User",
SUBSTR(s1.osuser,1,8) "OS User",
SUBSTR(s1.program,1,20) "WAITING Program",
s1.client_info "WAITING Client",
SUBSTR(TO_CHAR(h.session_id),1,5) HSID, p2.spid HPID,
SUBSTR(s2.username,1,12) "HOLDING User",
SUBSTR(s2.osuser,1,8) "OS User",
SUBSTR(s2.program,1,20) "HOLDING Program",
s2.client_info "HOLDING Client",
o.object_name "HOLDING Object"
FROM gv$process p1, gv$process p2, gv$session s1,
gv$session s2, dba_locks w, dba_locks h, dba_objects o
WHERE w.last_convert > 60
AND h.mode_held != ‘None’
AND h.mode_held != ‘Null’
AND w.mode_requested != ‘None’
AND s1.row_wait_obj# = o.object_id
AND w.lock_type(+) = h.lock_type
AND w.lock_id1(+) = h.lock_id1
AND w.lock_id2 (+) = h.lock_id2
AND w.session_id = s1.sid (+)
AND h.session_id = s2.sid (+)
AND s1.paddr = p1.addr (+)
AND s2.paddr = p2.addr (+)
ORDER BY w.last_convert DESC;

References :


Oracle Tuning Power Scripts

http://it-ebooks.info/search/?type=title&q=Oracle Tuning

Posted in Oracle | Etiquetado: , | Leave a Comment »

DBLink Oracle for Insert Select

Posted by kiquenet en 10 octubre 2014


DBLink to PRE is defined in DEV.

DBLink not support BLOB field.

Now, SQL Insert Select for get rows from PRE to DEV:

insert into  SCHEMA01.LOGS (IDPRO, DateLog, res, idserv, user, machine, etiq, pet, LOG)
SELECT 110940, DateLog, res, 53705, user, machine, etiq, pet, EMPTY_BLOB()  FROM SCHEMA01.LOGS@PRE
where etiq is not null  AND machine LIKE ‘OPERATORS%’ and RESULT = ‘KO’

Insert into SCHEMA01.CODES Select * from SCHEMA01.CODES@PRE

Posted in Oracle | Etiquetado: , , | Leave a Comment »

Configuración de Oracle y TNSNames.ora

Posted by kiquenet en 11 enero 2012

Probar la conexión con un servidor Oracle lo más fácil es probar a través de ping, para ver si se llega a la máquina.

Pero el ping puede estar "capado" por seguridad para algunas máquinas.

Tendríamos que probarlo de alguna forma con el puerto de Oracle.

También podemos utilizar el comando TNSPING

TNSPING y la IP o nombre del servidor, local o remoto comprueba si hay conexión con dicha BBDD y si se conecta te mostrará lo siguiente:

Administrador>tnsping localhost

TNS Ping Utility for 32-bit Windows: Version – Production on 21-FEB-2
011 13:41:26

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Archivos de parßmetros utilizados:

Adaptador HOSTNAME utilizado para resolver el alias
Realizado correctamente (0 mseg)

Con esto, aparte de probar la conexión, vemos donde está el fichero sqlnet.ora, que parece que se pierde mucho ( esta última parte sacada de este blog http://sixservix.com/blog/david/2010/02/23/tnsnames/)

Finalmente, si la conexión no va habrá que buscar otras opciones:  un firewall qeu impide el paso, la BBDD no está levantada….no está conectado el cable….etc…

Uno de los problemas más habituales a la hora de trabajar con la base de datos Oracle y todos sus productos asociados es averiguar donde está el fichero TNSNAMES.ORA que configura los alias de las cadenas de conexión a distintas bases de datos. O, mas exactamente cuál -de los trescientos que se puede acabar teniendo en una máquina- es el fichero tnsnames.ora que configura realmente la conexión.

Afortunadamente, cuando se trabaja con java no se utilizan estos ficheros de configuración externos. Se pueden guardar y utilizar las adenas de conexión y usarlas directamente con el driver JDBC de tipo 4 de Oracle, pero  siguen siendo necesarios para usar cualquier cliente o programa que utilice el driver nativo de la base de datos como el PL/SQL Developer o el Toad.

La teoría dice que la variable de entorno TNS_ADMIN es la que configura la ruta donde se encuentra el fichero de configuración tnsnames.ora, pero la cosa se empieza a complicar cuando también se involucra a la variable de entorno PATH.

Cualquier instalación de un producto de Oracle -desde Weblogic a Discoverer- suele manipular la variable de entorno PATH, lo que puede provocar, desde el sorpresivo cambio de la versión de JDK de java que se esta utilizando… hasta el cambio de tnsnames.ora que configura el acceso a base de datos Oracle del sistema.

Para acabar de rematar la faena, Oracle utiliza una búsqueda jerárquica de ficheros de configuración y, además, esta jerarquía es distinta según el sistema operativo utilizado. Hasta puede llegar a utilizar configuración guardada en otra fichero de configuración: sqlnet.ora, lo que puede volver loco al programador mas duro.

Hay un truco para solucionar este problema y averiguar el fichero que configura las conexiones a Oracle: el comando tnsping.

El comando tnsping sirve, originalmente, para hacer un ping que compruebe la conexión a una base de datos remota mediante la siguiente sintaxis:

tnsping alias_de_base_de_datos

Pero, además, tnsping indica la ruta física del fichero de configuración y la propia instalación de Oracle hace que este disponible en el path del sistema, así, se puede utilizar para averiguar donde esta el fichero de configuración de las cadenas de conexión de nuestro sistema, haciendo ping a una base de datos existente o ficticia:


Para .NET y ODP.NET, que hacen uso también de tnsnames.ora:

En algunos casos, reinstalar ODP.NET de nuevo.



Etiquetas de Technorati: ,,,,

Posted in .NET, Oracle | 1 Comment »