jump to navigation

Friday Philosophy – Oracle Performance Silver Bullet August 5, 2011

Posted by mwidlake in Architecture, Friday Philosophy, performance.
Tags: , , ,
trackback

Silver Cartridge and Bullet

For as long as I have been working with Oracle technology {which is now getting towards 2 decades and isn’t that pause for thought} there has been a constant search for Performance Silver Bullets – some trick or change or special init.ora parameter {alter system set go_faster_flag=’Y’} you can set to give you a guaranteed boost in performance. For all that time there has been only one.

There are a few performance Bronze Bullets…maybe Copper Bullets. The problem is, though, that the Oracle database is a complex piece of software and what is good for one situation is terrible for another. Often this is not even a case of “good 90% of the time, indifferent 9% of the time and tragic 1% of the time”. Usually it is more like 50%:30%:20%.

Cartridge with copper bullet &spent round

I’ve just been unfair to Oracle software actually, a lot of the problem is not with the complexity of Oracle, it is with the complexity of what you are doing with Oracle. There are the two extremes of OnLine Transaction Processing (lots of short running, concurrent, simple transactions you want to run very quickly by many users) and Data Warehouse where you want to process a vast amount of data by only a small number of users. You may well want to set certain initialisation parameters to favour quick response time (OLTP) or fastest processing time to completion (DW). Favouring one usually means a negative impact on the other. Many systems have both requirements in one… In between that there are the dozens and dozens of special cases and extremes that I have seen and I am just one guy. People get their database applications to do some weird stuff.

Partitioning is a bronze bullet. For many systems, partitioning the biggest tables makes them easier to manage, allows some queries to run faster and aids parallel activity. But sometimes (more often than you might think) Partitioning can drop rather than increase query or DML performance. In earlier versions of Oracle setting optimizer_index_caching and optimizer_index_cost_adj was often beneficial and in Oracle 9/8/7 setting db_file_multiblock_read_count “higher” was good for DWs….Go back to Oracle 7 and doing stuff to increase the buffer cache hit ratio towards 98% was generally good {and I will not respond to any comments citing Connors magnificent “choose your BCHR and I’ll achieve it” script}.
You know what? There was an old trick in Oracle 7 you could maybe still look at as a bronze bullet. Put your online redo logs and key index tablespaces on the fastest storage you have and split your indexes/tables/partitions across the faster/slower storage as is fit. Is all your storage the same speed? Go buy some SSD and now it isn’t….

Cartridge with Wooden Bullet

Then there are bronze bullets that you can use that very often improve performance but the impact can be catastrophic {Let’s call them wooden bullets :-) }. Like running your database in noarchivelog mode. That can speed up a lot of things, but if you find yourself in the situation of needing to do a recovery and you last cold backup is not recent enough – catastrophe. A less serious but more common version of this is doing things nologging. “oh, we can just re-do that after a recovery”. Have you done a test recovery that involved that “oh, we can just do it” step? And will you remember it when you have a real recovery situation and the pressure is on? Once you have one of these steps, you often end up with many of them. Will you remember them all?

How many of you have looked at ALTER SYSTEM SET COMMIT_WRITE=’BATCH,NOWAIT’? It could speed up response times and general performance on your busy OLTP system. And go lose you data on crash recovery. Don’t even think about using this one unless you have read up on the feature, tested it, tested it again and then sat and worried about could possibly go wrong for a good while.

That last point is maybe at the core of all these Performance Bronze Bullets. Each of these things may or may not work but you have to understand why and you have to understand what the payback is. What could now take longer or what functionality have I now lost? {hint, it is often recovery or scalability}.

So, what was that one Silver Bullet I tantalizingly left hanging out for all you people to wait for? You are not going to like this…

Look at what your application is doing and look at the very best that your hardware can do. Do you want 10,000 IOPS a second and your storage consists of less than 56 spindles? Forget it, your hardware cannot do it. No matter what you tune or tweak or fiddle with. The one and only Performance Silver Bullet is to look at your system and your hardware configuration and work out what is being asked and what can possibly be delivered. Now you can look at:

  • What is being asked of it. Do you need to do all of that (and that might involve turning some functionality off, if it is a massive drain and does very little to support your business).
  • Are you doing stuff that really is not needed, like management reports that no one has looked at in the last 12 months?
  • Is your system doing a heck of a lot to achieve a remarkably small amount? Like several hundred buffer gets for a single indexed row? That could be a failure to do partition exclusion.
  • Could you do something with physical data positioning to speed things up, like my current blogging obsession with IOTs?
  • You can also look at what part of your hardware is slowing things down. Usually it is spindle count/RAID level, ie something dropping your IOPS. Ignore all sales blurb from vendors and do some real-world tests that match what you app is or wants to do.

It’s hard work but it is possibly the only Silver Bullet out there. Time to roll up our sleeves and get cracking…

{Many Thanks to Kevin Closson for providing all the pictures – except the Silver Bullet, which he only went and identified in his comment!}

About these ads

Comments»

1. Mike Cox - August 5, 2011

Martin, you forget to mention the ‘golden’ bullet, that if you design your application/database/architecture correctly for the expected load/throughput/usage you won’t have any problems, I’m also old enough to remember a time when we used to do this !

mwidlake - August 5, 2011

Hi Mike,

You are right of course – and I remember learning how to do that with you :-) Or, probably more accurately,from you.

CRUD analysis, data flow diagrams, logical-to-physical mappings, volume and IO rate analysis… I still carry out most of these when I can, when I am allowed the time. And guess what? Those systems are generally the simplest, most reliable and most performant systems I see. But so rarely is time given to do them any more.

Maybe in another decade they will come back into fashion.

2. kevinclosson - August 5, 2011

That looks an awful lot like a 6.5×55 Swedish Mauser cartridge.

mwidlake - August 5, 2011

I bow to your superior knowledge on that one, Kevin :-) All I know is putting one of those into your database is not going to make it go faster (though I’ve seen a few systems that should have been put out their misery with bullets…)

Do you have any pictures of “copper” bullets I could brighten up the post with?

3. kevinclosson - August 5, 2011

Copper? Of course. I’ll dig up a Lapua Naturalis or Barnes XXX, snap a photo and send it…hold it… do you want a photo of the cartridge or the bullet after it passes through something “dense?”
:-)

mwidlake - August 5, 2011

Ohhh! The latter sounds much more interesting Kevin. The bullet after going through something “dense” !

4. Bernard Polarski - August 8, 2011

in Oracle 13 the parameter becomes:

alter system set go_faster_than_light=’Y’

Oracle corps has bent the law of universe to its will and pushes the limits of physics !

mwidlake - August 8, 2011

:-)

5. Graham - August 9, 2011

So the only real ‘Silver bullet’ is a combination of hard work, planning, testing, understanding the system and common sense…does that really count?

I feel cheated :)

mwidlake - August 9, 2011

Ahhh, you great wus Graham. Knuckle under and get on with it! :-)

But yes, I’m afraid there is no effortless, guaranteed performance boosting trick, at least one that I know of.

But I do promise you(*) that if you do spend time working to understand your system, you are guaranteed to make gains.

(*”You” being the plural you. Graham I know always works to understand his systems. Doesn’t always manage it, but he tries…. ;-) )

6. jgarry - August 11, 2011

That’s not a bullet. This is a bullet. Kind of a silvery color, too.

7. mwidlake - August 11, 2011

Nice. You guys are really into your armaments :-)

Dom Brooks - August 18, 2011

Bullet? I thought it was your lipstick…

8. Billy - April 22, 2014

If it was spam don’t you think it would not be raising a good point and instead posting links telling you how you should buy viagra because you have issues?

mwidlake - April 22, 2014

You have a point Billy so I have removed the original comment and my response for now.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 163 other followers

%d bloggers like this: