What have I been up to?

I know, I know I haven't released a new build of any of the three tools that currently make up my portfolio of software packages... However thats not to say I haven't been busy, currently Im working on

• Updates to the user interface of swingbench (exposing parameters hidden in the config file)
• Updating the doc (I know too late)
• Fixes to Trace Analyzer.

Im also helping out several groups who are putting swingbench through its paces and the output of this should make it a tighter code base.

Anyway I've big plans for 2007 and I hope you'll stick with me.


Trace Analyzer

So I haven't released much in the way of an update to swingbench lately mainly because work has been so busy...

However between meetings I put together a little program that parses Oracle trace files. Now I know TKProf does a fine job of this but I've never been really comfortable with having to continually rerun TKProf to change the ordering and filter out classes of statements. This came to a head just recently after looking through a big trace file and trying to figure out what SQL to work on first. I also thought that perhaps I could use a richer user interface to give a better overview on what has happened a particular run. So I started with the intention of figuring out how to parse the file and come up with some ideas on what to with the results... This turned out to be pretty trivial because of its structure and Java's regular expression support. With that taking much less time than expected I put them into a Java Swing JTable just to verify the results, which lead on to the next thing and then the next.... Needless to say the code is far from perfect but it does give a feel as to what could be achieved. If there is no interest I'll stop now and go back to finishing swingbench 2.3.

So In summary what does it do
• Parse trace files.
• Profile the data via a bar of the right had side of the scroll bar.
• Supports dynamic filtering and sorting of the data.
• Highlight the 5 worst performing pieces of SQL (elpased, cpu, physical etc)

What I'd like to add to it
• highlight concurrent SQL
• Explain Plans
• Display bind variables
• dump sql to flat files
• generate load files for swingbench

However unless theirs interest I probably won't bother..

You can download the code here

Leave your comments here.

Performance fix for datagenerator

Apologies for those that have already downloaded swingbench. I managed to engineer a bug into the latest build... I removed code responsible for array binding on inserts into the database. I've put it back in and, as you might expect, makes a significant difference to this new functionality. In the next couple of days I intend to document some simple findings illustrating the difference between the two approaches.

New build of datagenerator

I've uploaded the latest build of datagenerator. New features include
  • User definable number of threads

  • Data insertion directly into the database (Oracle)

  • Ordered generation of data (largest first)

  • Lots and lots of small bug fixes

Apologies for not updating swingbench recently but I've been struggling with the amount of "proper" work at the moment. I'll try and find some time over the next few week to include the updates I promised. Let me know if you encounter any problems in the code... now is the time to get them fixed.

Donations Link

I've posted a donation link on the web site (downloads). I don't mean to offend anyone but the hosting and domain bill has just rolled in again. Thanks if you can help out.

New build of swingbench 2.3, build 144

I've just uploaded a build of new swingbench. This fixes a whole load of internal bugs that shouldn't have been that obvious to most people. This should be the last before I release the changes to the front end. This hopefully should allow all swingbench parameters to be maintained from a graphical front end.

On the subject of I/O

One the things that constantly surprises me when talking with clients about hardware for a new database server is that I/O is always at the bottom of the list. Typically the list will look something like this (listed in order of perceived importance)
  • CPUs, have we enough. Fast as possible.

  • Memory, as much as we can put in the box. Oracle don't charge us for that

  • SAN, big as possible.

At this stage the purchase order is usually given the nod and the hardware supplier will ship yet another run of the mill box. Don't get me wrong. Many experienced DBAs have been through this process many times before and realise that not only is the list in the wrong order but its missing some critical components.
  • HBAs, need to specify these in proportion to the CPUs and attached storage

  • NICs, might need a lot of these i.e public, cluster interconnect, storage, management, backup. And typically in multiples for resillience or performance.

  • Backup, are we using the existing backup infrastructure?

I don't blame anyone for this way of thinking, its the way its always been. When discussing a new server the first question that people tend to ask is "So whats this monster packing? 16 CPUs!!!" followed by lots of very macho grunts and hollering. The standard licensing model (not just Oracles) doesn't help. It starts with premise of a CPU describing the power of a server, and to a large degree it does but misses the point of what a database is all about and that's information. Typically that information is held in ones and zeros on a bunch of spinning scrap metal. The real power of a database comes from its ability to aggregate, analyze and process those ones and zeros, turn it into information and push results out to interested parties. Paraphrasing a little "Its all about I/O stupid".

With this in mind I'm constantly surprised by the imbalance of I/O put into servers both disk and network. Its not unusual to see a 4 cpu server running with the latest generation Intel and AMD CPUs but with a single HBA and dual ported NIC. Whilst memory is cheap many of these servers still run 32 bit kernels. This typically means only a small proportion of the database is cached in memory be it in the SGA or file cache (don't me started on file cache). I'd make a rash guess that whilst the size of the memory in a typical database server has increased the average size of the SGA hasn't increased in line with this trend. To make matters worse the typical size of a database has got significantly bigger. This has to lead us to the conclusion that less of the database is cached and as a result a bigger proportion of its is located on disk. As I said this is just a guess but its backed up with real customer engagements. What would be of interest is to have performed an analysis over the last 10 years to see if the wait event for scattered and sequential reads had decreased or increased as a proportion of the total wait event in production databases.

What I'm driving at is the need to move I/O way up the agenda when sizing a server for databases. The number of CPUs needs to be married to the number of I/O channels available. It makes no sense to buy database licenses for a machine that will simply sit and wait on I/O, Its simply wasting money. Equally it makes no sense to stuff a 4 cpu machine full of HBAs for a database application that will perform index lookups on a index that fits comfortably in the cache. Adding HBAs later to an existing server isn't necessarily a simple option either especially for a mission critical application or one that has hard coded paths to disk.

The next obvious question is "well thats well and good but how do I size the ratio of HBAs to CPUs." and in a typically vague fashion I reply "well that depends". The type of application and the type of processor should heavily influence the decision. Certainly the CPU has been winning the race in terms of performance over the last few years and it needs a lot more I/O to keep it busy. But the equation also needs to be balanced with the amount of memory available on the box. A large SGA will certainly reduce the need to visit disk. The best advice I can give is to speak to your hardware supplier and find out what the current state of play is. Also check the latest TPC-C and TPC-H figures show. Whilst these are generally edging towards the extremes of performance it does show what a hardware supplier believed was needed to show their hardware in the best light.


Fix for DSS benchmark

I've included a fix for the DSS benchmark. The code used to label modules for services opened cursors without closing them properly. This prevented the benchmark from running for any period of time.

Fix for timezone support in datagenerator

I've included a fix for datagenerator that allows it to load the soe.xml and sh.xml file regardless of timezone the user is running in. I've also changed the default config file to a simple version to try and prevent this problem happening in the future.

New builds of swingbench and datagenerator

I've uploaded some new builds of datagenerator and swingbench to try and sort out some issues with national language support and timezones... i.e it only worked in the UK. Obviously if I could persuade everyone to change to UK time not only would it solve my issue, it would help solve a lot of other problems (never let it be said I have a insular view).

Nokia E61

Ive just recently managed to acquire a Nokia E61 and its fair to say Im very impressed. For those that don't know the E61 is a cross between a PDA and a phone. Its a little like a Crackberry in design with a minature keyboard instead of a number pad and a larger (wider) than normal display. Those that know me will say its just a fad Dominic goes through gadgets faster than a scotsman goes through iron brew. Well the truth off the matter is it does everything I need it to do well.

At the moment there are four main players in the portable OS market. Symbain, Microsoft, Palm and just recently Linux. It appears that Microsoft is winning the day certainly in Northern America. Even Palm who I never thought would put Pocket PC on one of their devices has rolled over and given into Microsoft. Symbian is doing well thanks primarily to Nokia in Europe and Sony in the East. Palm OS still holds its own thanks to the superb Treo device but I think its days are numbered as Palm gets closer and closer to Microsoft.

I think most people would argue the point who cares what OS a phone or PDA runs as long as it lets me make calls or check my email. Well it matters to me, my phone is probably the device I use second after the PC in a working day and I have real issues with Microsofts mobile offerings. They like they are simply a port of a OS with mobile/phone functionality bolted on as an after thought. Using the phone feels foreign its not at the heart of the OS, it sits at the side like the black sheep of family. Its a bland uninspiring experience, simple things are made harder than necessary and require a knowledge of how similar tasks would be done on Windows XP.

Back to the E61. The thing you notice about this device is its dimuntive size weight and thickness. Most devices that pack the same level of the functionality are usually brick size in terms of weight and size. The E61 has bluetooth, Wi-Fi, 3G support and a hold raft of additonal connectivity funtionality. The interface works well and benefits from bright and clear display. The small keyboard works as well as any other Ive tried, so well in fact this whole blog (and the one before) was written on it. The battery life of the device is surprisingly good, about 4 days between charges. The reason for this easy to find when you take the back off the cover... Its mostly the battery which takes up the space but this begs the question where do they put all of the over "stuff".

I had the choice of the Treo 650 and the E61 and I think I made right choice. Sure it dosent have a camera but then I dont need a low res camera and infact some of the places I have to work it means I dont have to hand my phone in at their reception. One area I had some concerns is software, Palm OS has a huge library of high quality well thought through applications and to be honest with you Symbian doesn't. However the default applications that ship with the Nokia E61 pretty much leave you with the question "what else would I need?". Well theres one piece of software I do need and thats a decent GPS package.

After looking around I decide on Route 66 it had reasonable reviews and it worked with the Nokia GPS reciever Id purchased. As a package it worked out much cheaper than the TomTom alternative. Installing Route 66 on the E61 has to rate as one of the most unpleasant experiences Ive had with a mobile device. After insisting on formating my mini SD card in the phone it then told me I needed at least 16MB of free space... I tried other storage devices with same result, clearly Route 66 need to do some work on the installer. Theres no excuse for this level of software quality. The solution was to manually copy the software onto the SD card and even then a hard restart was required. However once the software was installed it worked like a dream and demonstrates the power of the E61s processor.

With the GPS software installed I have about all I need from a single device. I recomend the E61 to anyone thinking of upgrading their phone and PDA.

Icon design

One of the problems when buiding a bespoke application is that you can never find a icon that reperesents exactly the action you need. Sure there are hundreds of sites on web that have "free" icons but these tend to be designed for the desktop. You can sometimes find sets that look very professional and you'd be proud to have them in your application. However you still have the issue that you don't only need icons to represent "file open" or "delete record" but also ones to represent the new action that is going make your application a best seller and the last think you need is a icon that sticks out like an ugly sore thumb.

So your left with a couple of choices. Go ahead and build that expensive icon set and hope that no one notices your child like attempts at graphic design. Or bite the bullet and commit your self to buiding the whole set your self. Now this not something you should attempt if you have no artistic aptitude or are short of patience. To be fair I've always been interested in graphic design but have never had the inclination or need to commit to buiding my own icon set. That was until I started working on datagenerator and swingbench. Im a one man team and I know many of you will be questioing the sense in spending time working on icons when it could better be invested in fixing bugs or working on new features, however one of my objectives when I started working on thes projects was to touch on a number of disciplines that my day to day job (core database) doesn't allow.

So once you've committed yourself to building your own bespoke set what tools do you use. Well theres no shortage of tools from bespoke icon editors to top end tools such as Adobe Illustrator and Photoshop, Paintshop pro etc. I've shyd away from icon editors in the past simply because I find them to restrictive. You find yourself spending to much time trying reproduce effects such as shadows and gradients which are pretty much the defacto standard on a modern desktop. I use two platforms these days, My Apple iMac and a Linux notebook. If I was designing icons and other graphics for a living I would invest the big bucks for a product like Adobe Illustrator... I wouldn't even hesitate, from my limited experience nothing comes close, however I dont do this for a living and it doesn't make any sense to spend a couple of thousand pounds for a dozen icons (although if someone has a spare license lying around...).

Luckily the open source market has a number of alternatives that provide a viable alternative. i dont have the time to list the various projects building superb tools to compete with the various commercial offerings but two stand out. The Gimp (worth using just to shout accross the office "I working on the Gimp") and Inkscape. The Gimp is primarily used as a raster or digital paint tool as is comaparable to Adobe Photoshop. Inkscape is a vector paint tool and is comparable to Adobe illustrator in terms of use if not functionality, it also has the added benefit of working directly in SVG. Working in a scalable format, such as SVG, is a real asset to icon design it means you can work on a large scale and then shrink or enlarge your design with very little loss in quality.

So Ive comitted to Inkscape and Im very impressed so far. It appears rock solid, has ports for MacOS and Linux, has tools for viewing your designs as they would appear as icons and has some genuinely inovative features. However it does have some flaws.... The documentation is very weak, some of the dialogues are confusing at best and its not a native port to MacOS (that really would set the cat amongst the pigeons).

That set Ive started work and its pretty straight forward to put together some consistent icons. Ill post the results shortly... don't laugh


New build of datagenerator

I've uploaded a new build of datagenerator (46). The update to this build is primarily to include new debug code to try and diagnose a issue with reading the default config files shipped with datagenerator. This prevents users from reading the data inside of the xml file. This appears to be a national language issue since the data is shipped in UK format. If you have this issue, uncomment the debug line in the shell script or bat file that starts datagenerator and post the output to me via the comments page.

New features for swingbench 2.4

It wont come as a surprise to people that know me I have a hard time focusing on work thats nearly done. And so finishing a release of swingbench is the hardest thing I have to do... Im always tempted to start thinking about the next relase in prefence to sorting out the final last issues and documenting all of the new features and functionality...

With this in mind Im interested to know what features people would like to see in the next release... Some ideas might be
  • Load generated from trace files

  • More benchmarks

  • more sophisticated clusteroverview

Anyway let me know what you think... while I concentrate on finishing 2.3

Minor Swingbench update to 2.3, build 125

Hi just a minor patch update to fix a few minor updates. Yup I do make changes based on feedback. Thats assuming I have time... Things are slowing down just a little at work as people head off on holiday so Im hoping to get 2.3 finished soon.. just the UI changes now... I'll be at Oracle HQ in the US next week so Im not likely to be able to respond to much email. For any of my work colleagues that need to get in touch I'll be on the mobile...
Back to the minor fixes
  • Sorted out the save, save buttons so they should work as expected.

  • Now a Transactions per second option for charbench(if this is useful I'll include it inside of swingbench and minibench)

  • Benchmark name now include on window title for swingbench


DSS benchmark uploaded

I've just uploaded a new build of swingbench and datagenerator to the website. They can be downloaded here. The reason for the joint release is that they both provide support for a new DSS benchmark. I've described how to install and run the benchmark here. The benchmark still needs work and testing to determine some standard metrics but it hangs together. Let me know what you think here.

New DSS benchmark available (or nearly).

Over the last few days I've been putting together swingbench's first DSS benchmark. It uses datagenerator to create the schema and then a new config file to generate the load. The only reason I haven't posted it yet is that our cluster is currently in pieces and is slowly being put back together so I'haven't had a chance to test it on a large scale machine. Im certain of a few things. It should eat I/O and CPU. I haven't included any materialized views to improve perfomance simply because in the past I've been asked for a benchmark to test the I/O sub system, I think this is it. It currently needs a little work to get it to scale to large sizes but I'll post instructions. The ultimate aim is to deliver three sizes 100GB, 500GB and 1TB. There's no reason why they couldn't be bigger its just that I'll lack the resource to test it. If you'd like to give it a spin drop me a line and I'll send you the code. Regardless I hope to post the code by the end of the week.

Datagenerator update to build 31

I've been slowly rounding off some of the rough edges in datagenerator. In this release I've fixed a few bugs and provided a wizard to size tables up in a simpler fashion (hint its the new button on the tool bar). I've also included the ability to store data in side of the config file. This is really useful if you have to take reference data from a reversed engineered schema. To demonstrate this I reverse engineered the oracle "sales history" schema and included the reference data for "TIMES","PRODUCTS" etc. The whole schema including reference data can now be shipped in a single file. I've included this file in this build (sh.xml in bin/winbin directory). This means you should be able to generate a multi terabyte "sh" schema.... This functionality will fome the basis of a new DSS benchmark in the next few weeks for swingbench. Let me know if you encounter any problems.

Oh and I fixed a stupid scripting bug the windows bat file to lauch datagenerator...

Swingbench update to build 117

Nothing amazingly new in this build other than some fixes. These relate to

  • Opening new config files left the old configuration in memory

  • The default config in windows was invalid (and no one mailed me... shame)

  • Fix to work around an Oracle connection pooling bug

  • No reports generated unless a System username/password was included in the config file

Im currently working on, mostly in my spare time, the creation of a swingbench DSS benchmark and the update of the UI to support all of the parameters currently hidden in the config file. Let me know if you encounter problems with this build.

New build of swingbench 2.3 available

I've just uploaded a new build of swingbench 2.3. It includes a number of commonly requested features. These include

  • The ability to collect database stats (from v$sesstat) to provide an overview of the resources used in a benchmark run. Let me know what other stats you'd like collected

  • Time on the x-axis of the Transaction and CPU overview charts

To enable database stats collection you'll need to edit the config file and include the following new elements (in red)



The results will be included in the output as xml.

IllegalMonitorState Exception in Datagenerator on Itanium.

I've encountered a weird bug... not sure if its my fault or the the JVMs. Basically when running the latest version of datagenerator on a multi CPU Itanium box I get this exception thrown after a (seemingly random) period of time. This only happens on an Itanium machine. The other variable here is that because its Itanium I have to use the JRockit 1.5 JVM. The code seems to work fine on every other multi CPU environment I've tried... Has anyone else had this problem?

New build of swingbench

I've released a new build of swingbench... nothing earth shattering but I have included some new functiionality

  • clusteroverview has new command line options

  • clusteroverview can now be run automatically and for a specified period of time

  • Clear button on events panel in swingbench

  • Events in panels now appear in time descending order

There's a bunch of other stuff too...

Walkthrough for datagenerator available

I've just published a walkthrough for datagenerator here. It should help in figuring out what datagenerator is capable of. This will probably be the way I publish all documents in the future...

Minor update to Datagenerator

I've fixed a few minor issues with the build I put up last night. The new release has a build number to enable you to more easily track what's new. I also fixed a minor bug with the environment variables in the windows build.

Datagenerator 0.3 release

I've just uploaded datagenerator 0.3 to the downloads page. Its a big step forward. Apart from being multithreaded and generally much faster it includes a wizard that allows you to reverse engineer a schema... currently it only reverse engineers the tables and primary keys but I'll include more functionality over time. I've used it to build vey large schemas for the order entry benchmark much faster than I was perviously able to. In the next couple of days I'll post a script and instruction on how to do this.. In the mean time enjoy

Linux distro choice

So I've given up on the hope that I could stick with Red Hat 4 AS on my notebook. No matter how many tweaks I make I can't get away from the fact that I need a kernel (and associated infrastructure) to make best use of the technologies like ACPI and my nice hi-res screen. I've stuck it out as long as I can... I've tried to keep my notebook patched up to latest updates so as to provide as close as possible a working environment used by the customers I'm asked to help out with. But it makes no sense. My notebook is clearly not a server, never will be. It has a single processor, uses a SATA drive only has 2GB of memory and no 64 bit capabilities. So on the odd ocasion I've tried to debug a complicated problem that a customer has encountered I've failed. No surprises, I really need a proper disk array, a lot more memory and at least a dual core CPU. So I've made the decision to upgrade to a bleeding edge distro either Fedora core 5 or Ubuntu with a 2.6.16 kernel (OCFS as part of the kernel build). I'll use VMware to provide a RHEL 4/SLES 9 environment....

If Im honest the primary reason Im even considering this is notebook suspend/hibernate. I used to love this functionality in my old notebook (APM). I don't think I rebooted it in about 6 months on one occasion, no need close the lid it hibernated, opened it up it resumed, Oracle was up for most of this 6 month stint (in a suspended form). I've feel that the introduction of ACPI has set back the Linux notebook world a long way... Not thats it not an improvment, its just Linux has struggled to provide a meaningful out of the box experience... Its getting there, every kernel release moves it forward but its still away to go. Im not expecting an Apple like experience... just something close.

Comments in blog

You should now be able to leave comments in my blog... be nice.

SQL Developer goes production.

SQL Developer has gone production. Congratulations to the entire development team. I've been using it every day now and it feels solid and performs well and I continue to find out new tricks and features each day. If you've not given it ago, try it...

Update to Swingbench 2.3 Beta

Further fixes for connection pooling and new support for the order entry schema to be build without partitioning.

Swingbench 2.3 Beta available

I've just posted swingbench 2.3 to the downloads page. This build still needs some work but it has some genuinely useful features.

These include
  • Update to frontend of "minibench"

  • New simple "Stress Test" benchmark for both Oracle and TimesTen

  • TimesTen support

  • User defineable length of benchmark run

  • Better statistics in reports output

  • Benchmark comparison tool

  • Improved connection pooling and fAN support (via swingconfig file only)

  • Minor changes to swingconfig.xml

  • Build numbers for each release

  • User selectable timings i.e. measurements in seconds, centiseconds, millseconds, microseconds etc.

The new runtime functionality and bmcompare utility allow for large complex benchmark runs and comparisons between individual runs. The new minibench UI provides a richer experience for those wishing to run the swingbench environment in graphical mode but without the overhead of the full blown interface.

Documentation (online and pdf) to follow shortly.


Swingbench 2.3 should be ready shortly features a new tool called bmcompare which allows you to compare runs of benchmarks... Its pretty simply currently but I like it. Its simplifies the job of brining all of the data together. It will be especially useful for some of the users of swingbench how use swingbench as a regression test tool.


If you haven't tried this tool out I strongly recomend you pop over here. Its a massive step forward for database developers/DBA who really have felt a little neglected by Oracle over the last few years. I've had countless complaints about how SQLPlus/vi/notepad are still used by many as their development tools of choice and how it really isn't good enough. Well I've know about th tool for a while now and have had to keep quite but Im glad the cats out of the bag and its got such positive reviews... especially because of its price... free.

It features much of the functionality you'd expect in a top end development tool plus features that many of its competitors charge top dollar for. The best piece of news is that its an extensible framework and plugins have started to pop up all of the place... One of my particular favourites is at fourthelephant. Perhaps a little over the top for a text man like myself but I appreciate the work that must have gone into it.

They've inspired me to think about putting one together myself... The API is pretty simple and so it shouldn't be too taxing.... Any ideas? drop me a line and I'll see what I can do.

So whats new in Swingbench 2.3

I've been putting the final touches on the 2.3 release of swingbench. So far the features that have made it in are

  • TimesTen support

  • Timer support. This allows you to run the benchmark for a given time

  • User selectable timings i.e. measurements in seconds, centiseconds, millseconds, microseconds etc.

  • Code modification to support 1.5

Im still also hoping to include a utility that will provide side by side comparison of benchmark runs. For example this will allow you to run six benchmarks and compare the differences between each one. So you could increase the user count by 50 percent each run and then determine what difference this makes to the server. It will be pretty simple at first but Im hoping it will become more sophisticated over time.

Things that aren't likely to make it but will be in 2.4

  • Data warehosuing benchmark

  • Update to clusteroverview


Update to Swingbench to support 1.4 JVM

Apologies but I've posted another minor change to swingbench to support the 1.4 JVM. You should only download a new build if you don't have access to a 1.5 JVM.

rlwrap : Command line editing in sqlplus

I imagine alot of people in the Linux comunity already use this. However if you new to Linux and struggling with recalling text on the commandline from within sqlplus, rlwrap might be the tool for you... its just a wrapper you put around a command line program and provides command line editing.... I've been using it for the last few months and couldn't live without it

You can find the tool here

What next for swingbench?

Im at a sort of a crossroads with swingbench and Id like some feedback on what course I should follow next. There are a number of features I've been promising for a while

  • A new data warehousing benchmark, this also requires I get datagenerator into a more complete state.

  • Better multiple load generator support, clusteroverview hasn't really received much attention in the recent point releases

  • Better reporting, I've always planned to integrate a mini statspack so that each run results in a more complete break down on not just the results but what happened to the database

  • A html front end, I could do this either by integrating it into Enterprise manager (not as hard as you think) or writing a bespoke JSP front end.

  • HTTP load generation support, I've been promising this for a while and have written the engine and have nearly got the code in a state where I could drop it in. But I've a feeling the market is a wash with HTTP load generators.

  • None of the above and just tighten up the code.

If you have strong feelings on any of these drop me a comment.

Server side failover

I've had a number of emails recently with requests for help with the new server side failover functionaility in 10g release 2. This functionality is described in the Oracle10g release 2 documentation but I've been told its not really obvious.

Let start by explaining what it is. Server side failover allows the sysadmin/dba to configure the profile of connection availablity on the server using a service. Users are effectively unaware of what will happen in the advent of a node in the cluster failing. Previously in (9i and 10g) users needed an entry in their local tnsnames file to describe which nodes they could failover to and which nodes were used to load balance connections on. Unless you used a remote naming service to maintain the connection information every time you added or removed nodes from a cluster it meant an update to potentially hundreds or thousands of tnsnames files.

This was simplified with easy connect in 10g release 1 which allowed the creation and connection to a service specified on the server. For the first time users only needed to connect to a nominated "listener node" and know the name of the service, for example imagine we have a nominated server inside of our organisation called "oracleservice", this of course is the name used for our virtual ip that will float between our cluster of listeners. In 10g release 1 we could create a service called "orderentry" using either dbca or srvctl that would allow our users to connect to it using a connect string of the form

sqlplus soe/soe@//oracleservice/orderentry

This greatly simplifies Oracle network maintenance. In some cases it could mean the removal of tnsnames files from the client or application server. It has other advantages for the DBA as well. If some business event occurs that requires the provisioning of a new application or a new resource profile for a short period of time the DBA can provision it in seconds and trivially remove it when it is no longer required.

Sadly in Oracle10g release 1 this functionality didn't support Transparent Application Failover (TAF), this meant that DBAs still needed to maintain tnsnames files contain a description of what nodes a service could failover on. The good news is that in Oracle10g release 2 this all changed. DBA's could set up a service specifying TAF and the Oracle OCI layer would use this definition provided by the server to describe the load balancing and failover profile.

Implementing this functionality is pretty trivial but there is a step that might catch you out. So lets go through it step by step

To set up the service you can use either Oracle DBCA, the DBMS_SERVICE package, Enterprise Manager or srvctl. The choice is entirely dependent on what you have running. DBCA or Enterprise manager provide the simplest mechanism but you will still have to run the final step using the dbms_service package to tell the database about its failover profile.

I'll use the DBMS_SERVICE package and srvctl for the sake of brevity. In the following example I have a database called db10g2 with two instances db10g21 and db10g22. Im going to create a service called "orderentry" that will provide transparent application failover between the two instances.

The first step is to create the service using srvctl

srvctl add service -d db10g2 -s orderentry -r "db10g21,db10g22" -a "db10g21,db10g22" -P BASIC

and check on its status

$ > srvctl status service -d db10g2 -s "orderentry"
Service orderentry is not running.

So we'll have to start the service first

$ > srvctl start service -d db10g2 -s "orderentry"

if we now use sqlplus connecting as system/sys we can see the service.

SYSTEM@db10g21 > select SERVICE_ID, NAME, NETWORK_NAME, failover_method from dba_services;

id Name Network Name Failover
--- ------------------ ------------------------- ------------
3 orderentry orderentry

The thing to note is that the service hasn't got a failover profile associated with it. So we'll have to modify it using the DBMS_SERVICE package

SYS@db10g21 > get t1.sql
1 begin
3 service_name => 'orderentry',
6 failover_retries => 180,
7 failover_delay => 5);
8* end;
SYS@db10g21 >

if we now select the service information again

id Name Network Name Failover
--- ------------------ ------------------------- ------------
3 orderentry orderentry BASIC

We can now test the service using sqlplus.

sqlplus soe/soe@//node1/orderentry

SQL*Plus: Release - Production on Wed Jan 18 14:11:47 2006

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

Connected to:
Oracle Database 10g Enterprise Edition Release - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SOE@//node1/orderentry >

So all we need to do now is to fire up swingbench and use the service we've created.

[oracle@node1 bin]$ ./charbench -cs //node1/orderentry -dt oci -uc 30 -a
Author : Dominic Giles
Version : 2.2

Results will be written to results.xml.
Users : 30 TPM : 272 Nested TPM : 0

If we log onto the database we can see that the connections have being balanced across the two nodes

SYS@db10g21 >;

1 select instance_name, count(1) usercount, nvl(username,'INTERNAL') user_name,
2 failover_type, failover_method
3 from gv$session s, gv$instance i
4 where s.inst_id = i.inst_id
5 group by instance_name, username, failover_type, failover_method
6* order by username, instance_name
SYS@db10g21 > /

Instance No. of Users Username Fail Over Type Fail Over Method
---------- ------------ ---------- ------------------ ------------------
db10g21 6 SYS NONE NONE
db10g22 6 SYS NONE NONE

so lets shut down of the instances

SQL*Plus: Release - Production on Wed Jan 18 15:28:22 2006

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

Connected to:
Oracle Database 10g Enterprise Edition Release - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SYS@db10g22 > shutdown abort;
ORACLE instance shut down.
SYS@db10g22 >

And re-query the session profile

Instance No. of Users Username Fail Over Type Fail Over Method
---------- ------------ ---------- ------------------ ------------------

There's a lot more thats possible using the service approach to database connection but I'll discuss that in another blog.


Minor update to swingbench 2.2

I've just updated swingbench 2.2 with a few minor bug fixes.. It sorted out one or two issues with the wizards used to create the benchmark schemas.