Mark A. Williams

ODTUG Kaleidoscope 2008

Fri, 2008-05-09 15:17

It's ODTUG time again! It is a great opportunity to see lots of Oracle folks like Tom Kyte, Sue Harper, Joel Kallman, David Peake, Carl Backstrom, as well as a ton of other great people!

June 15-19, 2008
New Orleans, Louisiana

ODTUG Kaleidoscope 2008 – the annual conference of the Oracle Development Tools User Group, will be held this June 15-19, 2008 in New Orleans, Louisiana. Packed with technical sessions, four individual keynotes, three symposiums, a dedicated Hyperion track crafted by the masters, and a special gathering of Oracle Ace Directors, ODTUG Kaleidoscope offers the most comprehensive in-depth technical conference available for Oracle professionals. Unlike other conferences in this category, ODTUG Kaleidoscope strives to provide real world knowledge by real world technologists and provide a conference experience that will truly help Oracle developers take their role to the next level. For additional information and a complete agenda, visit www.odtugkaleidoscope.com.

Using Delegates to make a Synchronous Database Call Asynchronously

Sat, 2008-05-03 09:15

There are times when a tight coupling exists between user interaction with an application and database calls. That is, a user initiates an action that requires a database call, the database call executes, the call to the database returns, the user responds, and the cycle continues in fairly rapid succession. However, there are also times when a user may initiate an action that requires a lengthy database call. Of course, lengthy is a relative term and would be defined within your own environment. What is lengthy to one may not be lengthy to another. Something of a judgement call on that one.

If a lengthy database call is initiated from an application you may desire the capability to continue to do other work within that application while the database call executes. However, as current production versions of the Oracle Data Provider for .NET (ODP.NET) do not offer BeginXXX and EndXXX methods (such as BeginExecuteNonQuery() for example), you may think offering this functionality in your ODP.NET application is not possible or is difficult. Again, depending on your application specifics, offering such asynchronous behavior may give your application better responsiveness and increase the end-user experience positively.

One method of achieving this goal (calling a synchronous method asynchronously) is by using delegates. If you are not familiar with delegates, I suggest reviewing the Delegates topic in the online version of the C# Programming Guide on Microsoft's MSDN site. In addition to the standard reference information, there is also a tutorial (again located on MSDN) which can be found here. Of course these links are subject to change in the future.

In order to implement this technique you will need two methods (other than the Main method of course). One method will perform the synchronous database call and the other method will be invoked when that call is complete. A delegate is created for the method that performs the database call. This is the key to the technique. By creating a delegate for this synchronous method it becomes possible to invoke it asynchronously via the BeginInvoke() method exposed by the delegate. The delegate keyword actually causes the compiler to expand the declaration into a class which contains a BeginInvoke() method (among others).

The sample code below is a simple implementation of this technique using a console application. In this application a "do nothing" call to the database occurs. This call does nothing more than sleep inside the database for a specified period of time. As noted in the source code comments, the database user must have the "execute" privilege on the dbms_lock PL/SQL package for the code to work. Of course a real application would not normally connect to the database only to sleep for a period of time and return; however, this is a sufficient method for illustrating that the call is asynchronous in relation to the main thread.

The basic flow is as follows:

Create the delegate
Invoke the delegate to execute asynchronously from the main thread
Continue processing in the main thread while the database call is in progress
When database call is complete, invoke the OnCallComplete() method
Work in the main thread continues for a short period of time
Along the way prompt information is displayed and a final prompt is provided

It is necessary to cause the main thread to have a longer duration than the thread performing the database call because if the main thread ended before the database call thread the entire console application would terminate.

When the application is executed you should see output similar to the following:

Asynchronous call in progress.
Will sleep in database for 10 seconds.
    Doing work in Main thread...
Enter LongDatabaseCall
    Doing work in Main thread...
    Doing work in Main thread...
    Doing work in Main thread...
    Doing work in Main thread...
    Doing work in Main thread...
    Doing work in Main thread...
    Doing work in Main thread...
    Doing work in Main thread...
    Doing work in Main thread...
    Doing work in Main thread...
Exit LongDatabaseCall
Enter OnCallComplete
    Perform post-database call actions...
Exit OnCallComplete
    Doing work in Main thread...
    Doing work in Main thread...
    Doing work in Main thread...
Press ENTER to continue...

As you can see, the basic flow above is represented here and work in the main thread continues after the LongDatabaseCall method has been invoked via the delegate.

Here's the (hopefully) well-commented code to produce the above:

using System;
using System.Threading;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

namespace AsynchronousDemo
{
class Program
{
    // change as necessary for your environment
    // the user must have execute privilege on dbms_lock
    const string constr = "User Id=hr;" +
                          "Password=hr;" +
                          "Data Source=orademo;" +
                          "Enlist=false";

// the delegate for the function that makes the db call
internal delegate void LongCallDelegate(int sleepSeconds);

    static void Main(string[] args)
    {
      // the amount of time the call to the database will sleep
      int sleepSeconds = 10;

      // write marker text to console to indicate beginning of process
      Console.WriteLine("Asynchronous call in progress.");
      Console.WriteLine(" Will sleep in database for {0} seconds.", sleepSeconds.ToString());

// used to call the function
LongCallDelegate ldc = new LongCallDelegate(LongDatabaseCall);

      // call BeginInvoke to make call asynchronous
      // the method to invoke when the call has completed is passed
      // as the second parameter
      ldc.BeginInvoke(sleepSeconds, OnCallComplete, ldc);

      // cause main thread to "pause" while the database call is in progress
      // add an arbitrary number (5) to the sleepSeconds to help show that
      // the database call is "independent" of the main thread
      for (int i = 1; i < sleepSeconds + 5; i++)
      {
        Console.WriteLine("    Doing work in Main thread...");
        Thread.Sleep(1000);
      }

      // prevent console from automatically closing as when run from VS in debug mode
      Console.WriteLine("Press ENTER to continue...");
      Console.ReadLine();
    }

    static void LongDatabaseCall(int sleepSeconds)
    {
      // this is the function that makes the database call
      // it will be called from the delegate to make the call
      // asynchronous in relation to the main thread

// marker text
Console.WriteLine(" Enter LongDatabaseCall");

      // create and open connection to database
      OracleConnection con = new OracleConnection(constr);
      con.Open();

      // create command object
      // will call dbms_lock to sleep for x seconds in database
      OracleCommand cmd = con.CreateCommand();
      cmd.CommandText = "begin dbms_lock.sleep(:1); end;";

      // parameter for the sleep duration
      OracleParameter p_sleepSeconds = new OracleParameter("1",
                                                           OracleDbType.Decimal,
                                                           sleepSeconds,
                                                           ParameterDirection.Input);

// add parameter to the collection for the command object
cmd.Parameters.Add(p_sleepSeconds);

// make the database call
cmd.ExecuteNonQuery();

      // clean up
      p_sleepSeconds.Dispose();
      cmd.Dispose();
      con.Dispose();

      // marker text
      Console.WriteLine(" Exit LongDatabaseCall");
    }

    static void OnCallComplete(IAsyncResult ar)
    {
      // marker text
      Console.WriteLine(" Enter OnCallComplete");

// get the delegate from the IAsyncResult object
LongCallDelegate ldc = (LongCallDelegate)ar.AsyncState;

// Must call EndInvoke to pair with BeginInvoke
ldc.EndInvoke(ar);

      // marker text
      Console.WriteLine("    Perform post-database call actions...");
      Console.WriteLine(" Exit OnCallComplete");
    }
}
}

Generating Test Data

Tue, 2008-04-08 17:57

I'm an advocate of testing things - it can be a great way to discover different behaviors and learn how things work (or perhaps why they didn't work the way you thought they might!). When it comes to testing database applications, having some test data is certainly helpful. Ideally, I like to have a fully-loaded production database from which to draw this data into a test environment. Sometimes, however, this is not possible. In cases like these I use the "trick" described below to create my own test data.

When I need to generate test data I frequently call upon a seemingly little-known PL/SQL Package supplied with Oracle Database called "dbms_random". As you may guess this package can be used to generated random data. Rather than explain the package details (they are short and you can read them using the link), I'll just present a quick way to generate some data using SQL*Plus and the dbms_random package.

First, I'll need a table:

create table test_data
(
id number,
first_name varchar2(16),
last_name varchar2(24),
amount number(6,2),
purchase_date date
);

As you can see, this table is nothing to write home about, but it does mirror what a real-world table would look like in a lot of cases.

When I am creating test data I generally prefer the data to be reproducible. That is, if I execute the process more than once I like to get the same data each time. To accomplish this with the dbms_random package I call the "seed" procedure and provide an initial seed value:

exec dbms_random.seed(42);

Let's say I wanted to populate this table with 10,000 rows of data. I use the following to do this:

insert into test_data
select
rownum,
initcap(dbms_random.string('l',dbms_random.value(2,16))),
initcap(dbms_random.string('l',dbms_random.value(2,24))),
round(dbms_random.value(1,1000),2),
to_date('01-JAN-2008', 'DD-MON-YYYY') + dbms_random.value(-100,100)
from
(select level from dual connect by level <= 10000);

Starting at the bottom of the SQL text is a neat trick using "dual" and "connect by" to essentially create an "unlimited dual" for generating lots of rows. This trick was (I believe) originally put forth by Mikito Harakiri and I discovered it via an Ask Tom thread.

The remainder of the text is fairly straight-forward in its use of the dbms_random package. I use the "string" function to generate a random, lowercase value (which is subsequently passed to "initcap" to capitalize the first character of each string), the "value" function is used to create a random numeric value (which is passed to "round" to make it look like a purchase amount), and then I use a fixed-date to which I add (possibly a negative value) to create a set of valid dates within a range.

The first 10 rows of this data when selected from the table look like:

ID FIRST_NAME       LAST_NAME                    AMOUNT PURCHASE_DATE
--- ---------------- ------------------------ ---------- --------------------
1 Oqq              Mxsazbwyx                    521.33 22-MAR-2008 16:49:40
2 Jjgqrywtxbdn     Fwwbzshhkbqzb                921.47 04-OCT-2007 09:10:00
3 Zxflhufls        Mstwydowbaogeyyjiles         172.34 20-MAR-2008 10:22:05
4 Zjjxtyysitsog    Zxrzqeflxgo                  882.16 26-DEC-2007 18:56:44
5 Kjmuvbrqx        Hfu                          742.61 16-OCT-2007 14:35:27
6 Oywaibiyp        Angvlehlmeujfdlhdmtt          664.5 29-FEB-2008 12:50:40
7 Uhwyvla          Nhbwcv                       168.99 27-DEC-2007 22:29:59
8 Kpdiqafanbvzt    Phjeqwelyugrmahybocwbhvp     813.81 01-MAR-2008 09:15:59
9 Tvezuvrgnzzqkpq Pjyygoqx                     880.09 21-NOV-2007 00:42:07
10 Olchylbeft       Nflaxjqfkmkgt                847.71 07-DEC-2007 16:53:23

10 rows selected.

Can this technique always be used? No, probably not. For example, the names are not exactly what you might call "proper" names. However, I do find that this technique can be useful when I need to create some data to perform some testing with. Perhaps it will be helpful to you too if you experiment with it and find the right combination of values to use in your tests.

Applications and Data == Importance Envy?

Sun, 2008-04-06 10:34

I was recently reading an interesting posting (and comments) over on Doug Burns' Blog:

http://oracledoug.com/serendipity/index.php?/archives/1394-Theres-Hope-for-us-all.html

Doug's blog is a great resource and I encourage you to check it out if you have a few spare minutes.

I found this interesting because the "debate" around which is more important -- the application or the data -- raises its head every now and then. Now, my being a DBA in my "day job" may automatically bias me in this context; however, I was a developer before I was a DBA. This, of course, does not mean I am bias-free, but I do hope that I am not so closed-minded that I may see both sides of a discussion.

When I hear this question (which is more important? the application? or the data? OK, so that is three questions, but you understand.) I immediately think of a question I was posed long ago in school - which is more important, the heart or the blood? Of course there is not a perfect correlation between biology and applications and data, but I think that is not an unreasonable analogy. While not exactly easy, it is possible to find other mechanisms to transport the blood, but it is not so easy to replace the blood itself.

Clearly both applications and data are important. However, rather than necessarily declare one to be more important than the other, my inclination is to view them in terms of life span. Often I hear something along the lines of: An application is used to access the data. To me this implies that there may be more than one application or a series of applications over time whereas the data itself is not really viewed as having multiple incarnations. If I may borrow a quote from The Kinks: "Rocks bands will come and rock bands will go, but Rock 'n Roll will go on forever!" (From a live performance of "All Day and All of the Night" as I recall).

Experimenting with Connection Pooling

Thu, 2008-02-28 18:19

Connection pooling can be a great way to increase performance and scalability in your ODP.NET applications; however, it is also a feature that can be confusing to figure out as there are several parameters used in the connection string that control the behavior of the feature. These parameters are all fully documented in the documentation that ships with the data provider. In brief, the primary parameters used to control connection pooling are (see documentation for additional parameters):

Pooling - Enables or disables the connection pooling feature
Min Pool Size - Specifies the minimum number of connections that can be in the pool
Max Pool Size - Specifies the maximum number of connections that can be in the pool
Incr Pool Size - Specifies the increment value (the number of connections) to be added if the connection pool needs to grow in size
Decr Pool Size - Specifies the maximum number of connections that may be removed from the pool in a single "downsizing" operation
Connection Lifetime - Specifies the amount of time that a connection must be active after which the connection will not be returned to the pool (i.e. it will be disposed)
Connection Timeout - Specifies the amount of time that the provider will wait for an available connection from the pool

In ODP.NET each connection in the pool is represented by a physical connection to the database. There is a one-to-one relationship. If there are 4 connections in the pool there will be 4 corresponding connections in the database. This is another way of saying that ODP.NET connection pooling does not implement multiplexing (or sharing) of connections. Another important characteristic is that a connection pool is associated with a client process. This means that if you implement connection pooling in an application and there are 8 copies of that application simultaneously running at any given time, there will be 8 connections pools - one for each instance of the application process.

Because a connection pool is associated with a specific instance of an application it can possibly be a bit difficult to test the impact and operation of the various connection string parameters related to connection pooling. I've put together a simple project that uses threads to test connection pooling. This allows the single application process to create multiple connections (and thus multiple entries in the connection pool) to the database.

The sample code invokes a supplied PL/SQL procedure in the DBMS_LOCK package to sleep for a period of time (hard-coded as 30 seconds in the below code). The allows the connection to be "held active" and helps with testing the impact of the Connection Lifetime parameter. In order for this to work, the database user must be granted execution permission on the PL/SQL package. For example (as a DBA user via SQL*Plus):

grant execute on dbms_lock to orademo;

After starting the specified number of threads (and connections) the application holds the connection as active for the determined period of time, and then disposes of the connection object placing it back into the connection pool (depending on how you set the parameters!). In order to monitor the connections in the database, I executed the following query as a DBA user via SQL*Plus:

SELECT   SID,
         SERIAL#,
         USERNAME,
         STATUS,
         to_char(LOGON_TIME, 'DD-MON-YY HH24:MI') LOGON_TIME
FROM     V$SESSION
WHERE    LENGTH(USERNAME) > 1
ORDER BY 3, 4, 1;

The output from the application is as follows:

Thread 1 started...
Thread 2 started...
Thread 3 started...
Thread 4 started...
Thread 5 started...
Thread 6 started...
Thread 7 started...
Thread 8 started...
Thread 1 completing...
Thread 2 completing...
Thread 3 completing...
Thread 4 completing...
Thread 5 completing...
Thread 6 completing...
Thread 7 completing...
Thread 8 completing...

Paused after threads complete.
Monitor connections using tool of choice.
Be sure to wait several minutes (approx. 6) for clean-up to occur.

Press Enter when finished.

It is important to execute the monitoring query while the application is "paused" otherwise the application will terminate and the connection pool will be destroyed.

Here is the output of the monitoring query after the application has reached the "pause point":

SQL> SELECT   SID,
2           SERIAL#,
3           USERNAME,
4           STATUS,
5           to_char(LOGON_TIME, 'DD-MON-YY HH24:MI') LOGON_TIME
6 FROM     V$SESSION
7 WHERE    LENGTH(USERNAME) > 1
8 ORDER BY 3, 4, 1;

       SID    SERIAL# USERNAME                       STATUS   LOGON_TIME
---------- ---------- ------------------------------ -------- ---------------
       135          2 ORADEMO                        INACTIVE 28-FEB-08 18:22
       136          2 ORADEMO                        INACTIVE 28-FEB-08 18:22
       137          2 ORADEMO                        INACTIVE 28-FEB-08 18:22
       138          4 ORADEMO                        INACTIVE 28-FEB-08 18:22
       139          6 ORADEMO                        INACTIVE 28-FEB-08 18:22
       142          3 ORADEMO                        INACTIVE 28-FEB-08 18:22
       143          3 ORADEMO                        INACTIVE 28-FEB-08 18:22
       164          6 ORADEMO                        INACTIVE 28-FEB-08 18:22
       134          2 SYSTEM                         ACTIVE   28-FEB-08 18:22

9 rows selected.

This shows the 8 connections from the 8 threads (adjustable) in the application and my SQL*Plus connection.

If you simply let the application sit paused for a period of time, the connections will automatically be cleaned up by ODP.NET (again, depending on the parameter values, but the values as provided in the below code allow this to occur). On my system it took approximately 6 minutes for the connection pool to be "cleaned" (i.e. reduced to the minimum of one connection based on the values I used in the connection string).

[ after waiting for about 6 minutes ]

SQL> /

       SID    SERIAL# USERNAME                       STATUS   LOGON_TIME
---------- ---------- ------------------------------ -------- ---------------
       164          6 ORADEMO                        INACTIVE 28-FEB-08 18:22
       134          2 SYSTEM                         ACTIVE   28-FEB-08 18:22

2 rows selected.

This shows the connection pool has been "downsized" to the minimum number (one) I specified.

I encourage you to experiment with the different parameters and values to see how they operate and what impact they have on your system.

The Code

using System;
using System.Threading;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

namespace Miscellaneous
{
class Program
{
    static void Main(string[] args)
    {
      // change connection string as appropriate and experiment with different values
      const string constr = "User Id=orademo; " +
                            "Password=oracle; " +
                            "Data Source=orademo; " +
                            "Enlist=false;" +
                            "Pooling=true;" +
                            "Min Pool Size=1;" +
                            "Max Pool Size=8;" +
                            "Incr Pool Size=1;" +
                            "Decr Pool Size=8;" +
                            "Connection Lifetime=60;" +
                            "Connection Timeout=5";

// set to number of threads / connections to use
const int numberOfThreads = 8;

      // create arrays for class instances and events
      ConnectionThread[] threadArray = new ConnectionThread[numberOfThreads];
      ManualResetEvent[] doneEvents = new ManualResetEvent[numberOfThreads];

      // populate arrays and start threads
      for (int i = 0; i < numberOfThreads; i++)
      {
        // initialize each event object in the array
        doneEvents[i] = new ManualResetEvent(false);
        // create a new instance of the ConnectionThread class
        ConnectionThread ct = new ConnectionThread(i + 1, constr, doneEvents[i]);
        // assign the new instance to array element
        threadArray[i] = ct;
        // Queue the thread for execution and specify the method to execute
        // when thread becomes available from the thread pool
        ThreadPool.QueueUserWorkItem(ct.ThreadPoolCallback);
      }

// wait until all threads have completed
WaitHandle.WaitAll(doneEvents);

      // keep application from terminating while monitoring connections in database
      // if the application / process terminates all connections will be removed
      Console.WriteLine();
      Console.WriteLine("Paused after threads complete.");
      Console.WriteLine("Monitor connections using tool of choice.");
      Console.WriteLine("Be sure to wait several minutes (approx. 6) for clean-up to occur.");
      Console.WriteLine();
      Console.Write("Press Enter when finished.");
      Console.ReadLine();
    }
}

public class ConnectionThread
{
    // private class members
    private int _threadNumber;
    private string _constr;
    private ManualResetEvent _doneEvent;

    // parameterized constructor
    public ConnectionThread(int threadNumber, string connectionString, ManualResetEvent doneEvent)
    {
      _threadNumber = threadNumber;
      _constr = connectionString;
      _doneEvent = doneEvent;
    }

    // this will be called when the thread starts
    public void ThreadPoolCallback(Object threadContext)
    {
      Console.WriteLine("Thread {0} started...", _threadNumber);

// do some database work that holds the connection open
DoWork();

Console.WriteLine("Thread {0} completing...", _threadNumber);

      // signal that this thread is done
      _doneEvent.Set();
    }

    public void DoWork()
    {
      // create and open connection
      OracleConnection con = new OracleConnection(_constr);
      con.Open();

      // command to do the database work (simply hold connection open for 30 seconds)
      // NOTE: execute privilege must be granted to user on the dbms_lock package
      OracleCommand cmd = con.CreateCommand();
      cmd.CommandText = "begin dbms_lock.sleep(30); end;";

// execute the anonymous pl/sql block to does nothing but sleep to hold the connection
cmd.ExecuteNonQuery();

      // clean up and return connection to pool (depending on connection string settings)
      cmd.Dispose();
      con.Dispose();
    }
}
}

Experimenting with Connection Pooling

Thu, 2008-02-28 13:17

Connection pooling can be a great way to increase performance and scalability in your ODP.NET applications; however, it is also a feature that can be confusing to figure out as there are several parameters used in the connection string that control the behavior of the feature. These parameters are all fully documented in the documentation that ships with the data provider. In brief, the primary parameters used to control connection pooling are (see documentation for additional parameters):

Pooling - Enables or disables the connection pooling feature
Min Pool Size - Specifies the minimum number of connections that can be in the pool
Max Pool Size - Specifies the maximum number of connections that can be in the pool
Incr Pool Size - Specifies the increment value (the number of connections) to be added if the connection pool needs to grow in size
Decr Pool Size - Specifies the maximum number of connections that may be removed from the pool in a single "downsizing" operation
Connection Lifetime - Specifies the amount of time that a connection must be active after which the connection will not be returned to the pool (i.e. it will be disposed)
Connection Timeout - Specifies the amount of time that the provider will wait for an available connection from the pool