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