Better At Oracle

A collection of tutorials, code and tools to help you get better using Oracle


July 28, 2016

JDBC Bind By Name is slow for wide tables

Using the JDBC setStringAtName method when binding a lot of columns can be much slower by binding by position with the setString method


April 09, 2016

Cursor Leak For Invalid SQL Not In open_cursors

A cursor leak caused by an invalid SQL statement does not show an open cursor in v$open_cursor


July 18, 2014

Manually create an ASM database

The steps required to manually create a database on an ASM system.


July 17, 2014

How to create a cold back and restore to a new server

Some instructions on how to create a cold backup of an Oracle database and restore it to a new server.


July 17, 2014

Upgrade an 11gr2 database to a newer patch set

A quick note on how to apply a patchset to an Oracle 11gr2 database.


July 17, 2014

Move a non asm database into asm

Steps to move a non ASM database into ASM on the same host


March 07, 2014

Using Collect For One To Many Result Sets

In a reporting context, a problem that comes up fairly often is having a master table, such as Orders than joins to a detail table, such as Order_Line_Items in a one to many relationship.


January 09, 2014

Performing SQL Trace on Applications Using Connection Pools

Many applications connect to the database using a connection pool, and if you need to run a SQL trace on the application, it is slightly more tricky than a process that uses a single database session.


May 15, 2013

The Performance Cost Of Clobs

If you need to store strings longer than 4000 bytes, then one option is to use CLOBs, but what is the performance cost of doing so?


March 06, 2013

More on Unit Testing PLSQL with Ruby

Previously I explored how I created a simple test framework using Ruby, but I skipped discussing more complicated test scenarios which require some pre-test set up. This post will explore some ideas on how to tackle these sort of tests


January 10, 2013

ORA-04068 when compiling a function

More information on the error ORA-04068 existing state of packages has been discarded when compiling stand alone functions.


October 09, 2012

Unit Testing PLSQL with Ruby

Expanding on the previous Unit Testing post, this article explores in more detail how to write basic unit test for PLSQL with Ruby


October 03, 2012

Unit Testing PLSQL

I do a lot of programming with Ruby, and a lot of Oracle PLSQL development, and one thing that is hard to miss between the two communities is the attitude toward unit testing.


September 18, 2012

Hard Parsing can bring your app down

A real world example of a production database that got taken down by a simple procedure that introduced a hard parsing problem.


August 22, 2012

TOP N query with an IN List iterator

A subtle problem to watch out for when coding TOP N queries against large result sets


June 12, 2012

Returning a Ref Cursor as a column in a query

An interesting but possibly dangerous technique that may be useful in coding up PLSQL APIs whereby a new ref cursor can be returned for each row in a query.


June 11, 2012

Nested Array tables in SQL and Statistics

It is pretty well known, that you can bind an array of values into a query using PLSQL, and have Oracle treat it like a table, but how does the optimizer handle the lack of statistics?


May 17, 2012

Collapsing continuous ranges into single rows

Every now and then a problem appears that seems impossible to solve in a single SQL query, but with Oracle Analytic functions, SQL can be more powerful that you think.


April 24, 2012

Avoiding ORA-04068 existing state of packages has been discarded

If you need to modify tables while the application is online, especially in 10g, give careful thought to how the code is organised to avoid unexpected ORA-04068 errors in the application.


April 17, 2012

Passing arrays of record types between Oracle and Java

Bringing together two previous articles to demonstrate how to pass arrays of oracle record types into Oracle from Java


April 17, 2012

Passing Record Types between Oracle and Java

An example of how to pass Oracle record types between Java and Oracle


March 19, 2012

If exists update else insert

A common pattern is to take take a set of values and update an existing record in the database if it exists or insert a new record if it does not. Simple, but often done wrong.


February 07, 2012

WTF - The star like schema

An exploration of an application design I encountered recently that made very little sense to me and the importance of benchmarking your code.


February 01, 2012

Dumping Blocks

Notes on how to dump block contents for debugging purposes


January 10, 2012

Temporary tables

Some notes about temporary tables


December 09, 2011

Passing Arrays between Java and Oracle Procedures

All of the steps and working code required to pass arrays from Java to Oracle and back.


December 08, 2011

Array (Batch) Inserts with JDBC

Inserting records in batches is much faster than inserting the same data one row at a time. This article explains how.


December 08, 2011

Less Soft Parsing in Java

How to transparently reduce soft parsing overhead in Java applications with a few connection settings


December 07, 2011

Connect to Oracle from Java

Simple code to connect to Oracle from Java


September 02, 2011

11g Wait On Pending DML

You have a table that is continuously being inserted and you want to copy new records to a different table or system, or take some action on each new row, but you better not forget about consistent reads.


August 25, 2011

11g Reference Partitioning

Partitioned tables with a foreign key relationship have often led to broken normalisation and problems dropping partitions until Oracle introduced Reference Partitioning in 11g.


August 15, 2011

How do I bind a variable IN list?

A common question on the forums, and one that is frequently implemented wrong makes for an interesting discussion


July 19, 2011

Where are the DDL and Build Scripts?

Just losing one days work for a team is much more painful than coding and versioning DDL migration scripts, and if done carefully there are powerful side effects, such as selectively removing parts of a release, developer sandboxes and eventually continuous integration, automated testing and all that other good stuff the rest of the software development world enjoys.


July 14, 2011

Limiting query results - Top n and Window Queries

How do I find the 10 newest rows - this question seems to come up time and time again, and as far as I can tell, just about everyone implements it wrong when they first start out.


July 13, 2011

Referential Integrity - Performance

All these constraints are well and good, but they sure don't come for free. All this checking must cost something in CPU time, but how much?


July 06, 2011

Referential Integrity - Primary and Foreign Keys

Should you use a Primary Key or just a Unique Index, and what on earth is a Foreign Key?


June 30, 2011

Referential Integrity - uniqueness, nulls and checks

This will not be the last application that uses this database, you can be sure of that. Applications come and go, but the data lives on forever. No matter how good and fancy your application, it isn't going to please your users if the INTEGRITY of the data is not 100%.


June 23, 2011

SQL Trace and TKPROF

SQL Trace is an incredibly powerful tool, and allows you to get all sorts of information about a single query, or set of queries. When it is enabled, it is like turning on SQL debug mode, so how does it work?


June 14, 2011

Needless selects from DUAL

Of all the things I have seen done wrong over the years, selecting functions needlessly from DUAL is probably the most frequent.


June 12, 2011

Runstats

Written by Tom Kyte, the runstats package can be used to compare the performance of two pieces of code by comparing the number of latches required by each approach.


June 10, 2011

Getting a query Explain Plan

Assuming you have a query, getting the execution plan for it is easy. Log into SQLPLUS as usual and then use the 'explain plan for' command to generate the explain plan.


June 10, 2011

Using Autotrace

Autotrace is a tool built into SQLPlus that helps a developer evaluate how expensive a piece of SQL is at execution time. It is probably the easiest method of getting information about an SQL statement.


June 06, 2011

Introduction to the Developers Benchmarking Toolkit

Like most software, there is always more than one way to do something, and each way has its own set of compromises and limitations. So how do you know which method to use? You benchmark of course, which leads to the point of this introduction and upcoming topics.


June 06, 2011

Manually creating a new database on 11gR2

Notes on how to create a new database from the command line, without using the DBCA (Database Configuration Assistant)


June 06, 2011

Installing Oracle 11gR2 on Centos 5.6

Notes on installing Oracle 11gR2 on Cent OS 5.6


May 26, 2011

Edition Based Redefinition - Cheat Sheet

A summary of the key points and commands necessary for online upgrades


May 26, 2011

Edition Based Redefinition 3 - Cross Edition Triggers

When you need to change the definition of an existing database column, or fundamentally change the structure of a live table it can usually be done online with careful design and cross edition triggers.


May 25, 2011

Edition Based Redefinition 2 - Online Schema Changes

Not all application changes are as simple as just a code change - sometimes the underlying database schema needs changed too. So what are the options there?


May 16, 2011

Edition Based Redefinition 1 - Hot Patching PLSQL

Fixing a bug in a live PLSQL application without an outage has always been a tricky problem in Oracle, until 11gR2 when a new feature changed things completely ...