Top 60 Oracle Blogs

Recent comments


jmeter – getting started


This blog post is just a start at documenting some of my experiences with jmeter. As far as load testing tools go, jmeter looks the most promising to me. It has an active community, supports many different databases and looks quite flexible as far as architecting different work loads goes.

The flexibility of jmeter also makes it hard to use. One can use jmeter for many other things besides databases so the initial set up is a bit oblique and there look to be many paths to similar results. As such, my understand and method for doing things will probably change considerably as I start to use jmeter more and more.

I’m installing it on a mac and using RDS instances.

installing jmeter

What you "liked" last year…

Well…when I say “liked”, what I mean is “the stuff you all clicked on a lot” last year. Whether you liked it or not will remain one of those great mysteries Smile

The top 6 posts from 2016 were:

Graphics for SQL Optimization

Dan Tow, in his book SQL Tuning, lays out a simple method of tuning SQL queries. The method is

  • Draw a diagram of each table in the query with Children above Parents
  • Draw join lines between each join (many-to-many, one-to-many)
  • Mark each table with a predicate filter and calculate the amount of table filtered out

Then to find a great optimal optimization path candidate

  1. Start at the table with the strongest predicate filter (the filter that returns the fewest % of the table)
  2. join down to children (if multiple children join to child with strongest predicate filter)
  3. If you can’t join to children, join up to parent

The basics are pretty simple and powerful. Of course there are many cases that get more complex and Dan goes into these complex cases in his book.

When local partitions….aren’t

Let’s say I’ve got a partitioned table, and because New Year’s eve is coming around, I certainly don’t want to be called out at 12:01am because I forgot to add the required partition for the upcoming year Smile.

Since 11g, I can sleep easy at night by using the INTERVAL partition scheme. Here’s my table

UKOUG 2016

Just a little video montage of the fun and learning from UKOUG.  A great conference every year.

Multi-table insert

An interesting question came through on AskTom recently.  The requirement was to perform a single pass through a source table, and load the data into three target tables.

Now that’s trivially achieved with a multi-table insert, but there was a subtle “twist” on this requirement.  Each of the three target tables may already contain some, none or all of the rows from the source table.  Hence the requirement was to “fill in the blanks”.

So here’s a little demo of one way we could achieve this.

Apple Upset – upgrading to iPhone 7

Upgrading is always stressful – be it a computer, an Oracle database or an iPhone. There’s always a good chance for lost data and lost time dealing with complications.

So yesterday I picked up a new iPhone 7 from Verizon. The pickup was seamless. I had signed up for an upgrade program when I got the iPhone 6, so now I just walked in, gave them my old iPhone 6 and they gave me an new iPhone 7. It’s bit scary giving up my old phone before restoring to my new phone, but I had a backup AND I asked Verizon to please not wipe my iPhone 6 for 24  hours incase there were upgrade errors. They normally wipe the phone immediately.

The day was off to a good start. It only took about 10 minutes to get the phone and I had taken a full backup of my iPhone 6 the day before and thought I’d plug in , restore back up and wow, that would be easy.

A 12.2 treat for the festive season

We’re being asked to store more and more data, yet keep backup windows, query performance and the like unchanged, no matter how much we store. As a result, more and more database shops are needing to partition their data. The problem is – partitioning data is a significant restructure of the data, which thus incurs a large outage and the accompanying planning and coordination.

Unless you’re on 12.2.

Here’s a demo where we can take an existing table and

  • convert it to partitioned on a range scheme, also using intervals so we don’t need to worry about ongoing range creation maintenance, and
  • take one of the existing indexes, and convert it to a local index to mirror the table partitioning scheme, and
  • take the other existing index, and globally partition it.

Advanced Replication

Long before Streams, long before Goldengate, if you want to keep data between sites synchronised in some fashion, or even allow sites to independently update their data, there was the Advanced Replication facility in Oracle.  An “extension” of the concept of simple materialized views (or snapshots as they were called then), you could design complete replicated environments across Oracle databases.

But it was a non-trivial exercise to do this.  You had to be familiar with replication groups, replication objects, replication sites, master groups, master sites,  master definition sites, deferred transactions, quiescing, updatable materialized views, replication catalogs, conflict resolution…the list goes on an on.

Why does my full table scan take 10x longer today ?!

Every so often a DSS query that usually takes 10 minutes ends up taking over an hour.  (or one that takes an hour never seems to finish)

Why would this happen?

When investigating the DSS query, perhaps with wait event tracing,  one finds that the query which is doing full table scans and should be doing large multi-block reads and waiting for “db file scattered read” is instead waiting for single block reads, ie “db file sequential read”.  What the heck is going on?

Sequential reads during a  full table scan scattered read query is a classic sign of reading rollback and reading rollback can make that minute(s) full table scan take hours.