Use Bind Variables in Application SQL for Oracle

If you don't already know, then you should almost always be using bind variables in all SQL statements used in any applications you write that run against Oracle. Bind variables are place holders within your SQL statements that get replaced by real data values at run time when the SQL statement is executed i.e. at run time the real data value is "bound" to the corresponding place holder variable in the SQL statement. So (more...)

Data Driven or Application Driven Development?

I'm the kind of person who believes that Data is really important and needs to be treated and dealt with properly. Of course we also need Applications that use that Data - one without the other is no use at all. But a battle I often have with clients now is that Data needs to be modelled and designed at the same stage as the Application is. Ignore the data model and database design and (more...)

JSON, Python & Trello

JSON is a simple data storage format intended to allow easy data interchange between programs and systems. Data is stored in a text format as objects of "name : value" pairs. And that's about it, other than objects can be nested (a value could be another whole object) and multiple objects can occur one after another in the data set (a list or array of such objects). This makes it slightly database like because you (more...)

Getting My Passion Back

I've been quiet for a long while on this blog because I had been working for a company that just did not get databases at all, and it has been a real energy sapper dealing with the consequences of all of the wrong decisions they kept making.  I'll spare you the gory details, but the biggest problems of many were the wholesale adoption of Agile Development (Scrum) with no training, support or planned cutover (more...)

Red Gate Source Control for Oracle

The background to this - I was "given" a free license to Red Gate Source Control for Oracle some months ago as part of some marketing and awareness activity Red Gate were doing. I've been busy with other things, so I've only now got around to trying to understand what the product does and see if it can be of any benefit to me. Hence this review.

Before I start my review I want to (more...)

Speeding up Imports

There are a number of techniques you can use to speed up an Oracle Import, some of which I'll describe here. This is not any attempt at a comprehensive list, just some of the main techniques I have used that can really speed up some parts of an import. I've seen a standard import come down from 2.5 hours to about 0.75 hours using these techniques.

The first thing to realise is that (more...)

Packaging Python With My Application

To try and keep a longer story as short as possible I needed to package up the Python run time environment to ship along with a Python based application I had written. And in this case the target platform was Windows, though the solution will also work for Linux or any other platform (however most Linux distributions will already have Python on them). I needed to ship Python itself with my application to guarantee that (more...)

RAC high block sharing causes high log sync times

Some time ago I dealt with a system that was experiencing high "log file sync" times, as well as other RAC related waits ("gc buffer busy" and "gc cr block busy"). Initially I assumed that the "log file sync" waits and the RAC Global Cache waits were independent of each other, but it turned out that they were interlinked. This post is mainly to make other people aware that on a RAC system there is (more...)

Oracle VM on VirtualBox

I needed to "play" with Oracle VM and learn how to use it for some upcoming work involving virtual machines. Oracle VM (not VirtualBox) is virtualization software that installs directly onto a system i.e. it installs onto "bare metal" and does not need a host operating system. The problem I had was that I had no "spare" unused system on which I could install Oracle VM. I really wanted to be able to install (more...)

Basic Performance Analysis using AWR data

I've previously covered a number of queries that can be combined together to pull out key statistics from the Oracle AWR data for each snapshot it makes, which you could use to analyse the performance of a system. One example would be to use this to look at how the (more...)

AWR Reporting #6 – SQL Statements

This is the last of the main set of posts on this topic. As for the last post, I'll try and keep this brief and post the SQL involved.

Another data set we can look at is on SQL statements i.e. statistics collected by AWR on individual SQL statements, (more...)

AWR Summary Reporting #5 – System Statistics

So far our AWR report query shows overall duration between two consecutive snapshots, database time (i.e. doing work), SQL execution time (may be lower), total wait time, wait time by class, and some common specific wait events. We know how busy the database was and how much time it (more...)

AWR Summary Reporting #4 – More Waits

Given the previous sub-query for total wait times including class level waits from the AWR snapshot data, we are going to extend that particular sub-query in order to add some extra wait event data items to it. This will give us a further breakdown of what the major wait events (more...)

AWR Summary Reporting #3 – Waits

So far I've shown the skeleton of a query that lists out some key activity data values from all the data captured by AWR in its regular snapshots. So far we've only extracted a few time based data values:
  • Duration - real clock time that occurred between the AWR 2 (more...)

AWR Summary Reporting #2

Following on from my previous post on doing summary reports on the data captured in the AWR. Here is some example output from using that query, and a useful statistic you can derive from the output data it produces.

The previous query just gave us 3 time values:

Summary Reporting on AWR Data 1

The addition of the Automatic Workload Repository (AWR) in Oracle 10g was a good thing, as its regular hourly snapshots collect and store a number of database wide activity statistics which allows historical reporting later on. However, the Oracle supplied standard AWR report (awrrpt) only provides detailed information (more...)

Mercurial for tracking script change history

On the one hand source code control can be a nightmare involving complicated tools and formal processes, but on the other hand the ability to track the history of changes to a file can be really useful. With complicated SQL queries and stored procedures, seeing what changed between each major (more...)

My Top Technical Tools

I find that during my technical work on Oracle and Performance Tuning there are a few key pieces of software that I keep coming back to again and again. They are not directly related to databases, and are really more like technical tools that help me do my work better. (more...)

Oracle 11gR2 on Arch Linux

The Oracle Database software is only "supported" on Red Hat Enterprise Linux and Oracle Enterprise Linux I believe. But it can be made to work on many other Linux distributions one way or another, as a lot of the stuff is the same or equally available on other Linux distributions. (more...)

Python for the DBA (1)

I really like the Python programming language for general purpose programming and quickly putting together little utilities. There are similarities to Perl in terms of being an interpreted language with powerful data processing capabilities, but there the direct comparisons end. Python is a more modern language than Perl, and has (more...)