Tim

Diagnosing performance problems on Oracle... Why so difficult?

Since introducing Oracle to our live environment to support a major system, we’ve been seeing a variety of problems – resolution for which can only really be described as a black art.

Why does Oracle make life so difficult for those new to the platform to find out what it’s actually doing?

We’re currently running a 2-node Oracle 11g RAC cluster on 64-bit Windows 2003 server; which seems to be the first hurdle that has to be climbed…  Although Oracle are quick to state that Oracle-on-Windows (OoW )is a first class platform, and on the whole one that seems to perform just fine, something that’s become more apparent as time has gone on is that finding other people who have investigated & resolved performance issues with OoW is somewhat of an upward struggle. This extends into patching for example.

We recently identified a problem in the RAC clusterware that has been patched for all other platforms, but for some reason wasn’t ported to OoW. This came to light during an amusing conversation with Oracle’s support team who were avidly advising us to apply this patch, but were unable to provide us with a direct link to the patch to download. Only after getting them to go check the available downloads on Metalink did they realise that the particular patch wasn’t available for 11.0.x on Windows! Linux, *nix or Solaris – no problem…

Patching aside, we’ve recently been struggling with a major performance problem on our database which periodically resulted on both RAC servers essentially locking up at 100% CPU load across all cores, with the applications grinding to a standstill thanks to endless amounts of escalating locks within the database.

After what felt like many fruitless days of battling with this issue alongside working with the software vendor to determine whether the application issues we were seeing were a cause or effect of the problem, we started examining exactly what Oracle was doing at the point of an issue occurring. Eventually, with the aid of Oracle’s support team to analyse many Gb’s of traces captured with the system struggling, we were able to determine that the database was having great difficulty caching & reusing execution plans – and once identified, altered a system parameter to force Oracle to reuse execution plans. Problem seemingly solved!

Presenting symptoms:

• High CPU usage across both cluster nodes
• Unstable RAC environment with frequent node crashes, possibly due to excessively high CPU loads, forcing us to run on 1 RAC node.
• Rapid lock-state escalation resulting in all sessions being blocked & application crashes
• High % of lock wait states
• High % “Library Cache Latch” wait states
• High IO wait states
• Retention of 10’s of 1000’s of versions of query execution plans

Our first attempt at diagnosing this focused largely on the IO side of things, as storage for Oracle was not originally dedicated to Oracle but was located on a shared SAN diskgroup with some other applications. This was causing a variety of IO problems within the database as performance of the DB’s LUNs would fluctuate depending on other activities on the SAN. We decided to rule this out completely, so decided to provision a new set of SAN enclosures & disks dedicated to providing Oracle with sufficient storage, over dedicated spindles, arranged into RAID 10 for best performance. Oracle’s ASM proved its worth when the new disk became available, allowing us to keep the database fully available for users during migration of around 2.5Tb to new disks. Although the new storage helped dramatically by reducing IO contention & related wait states, it left us with the lock & Library Cache Latch wait states which between them still were able to bring the system to its knees.

To cut a long story short, it seems that the query code emitted from our applications was not lending itself to efficient re-use within Oracle as it doesn’t currently utilise bind variables, so at the peak of our problems we could query V$sqlarea and see 1000’s of versions of our top 10 queries being retained – despite them being identical bar substitution of a few parameters.

The key symptoms in all of this were actually the high % of “Library Cache Latch” wait states and the high number of execution plan versions being retained in the DB’s shared memory pool.

Inspecting v$sqlarea revealed that Oracle was not able to reuse execution plans as we were seeing retention of 1000’s of versions of plans for specific queries, along with high numbers of open & loaded versions of those plans indicating that nearly every query was being individually parsed to determine how best to execute it.

To determine this, try running something like the query below to retrieve your version counts from v$sqlarea:

select version_count, loaded_versions, open_versions, executions, sql_text
from v$sqlarea orderby version_count desc 

In our case we could see 6000 or so versions of one query, with 4-5000 loaded versions, 1-2000 open versions etc. These numbers were somewhat higher than expected… should ideally have a handful of versions, <10 loaded / open versions etc.

Every one of those execution plan versions gets checked when Oracle is presented with a new query to see if they can be reused, so given that every query was being parsed… all those plan versions were being checked for virtually every transaction the DB was processing. This is what seems to have produced 100% CPU load & the “Library Cache Latch” waits, and as a result the corresponding high numbers of Lock waits.

To test our theory we tried flushing the shared memory pool during one period when the system was experiencing locking issues:

ALTER SYSTEM FLUSH SHARED_POOL;

And were able to see an instant reduction in both CPU load, retained plan versions & lock waits.

To prevent this from happening and to get Oracle to re-use queries whenever possible, Oracle Support advised us to set the CURSOR_SHARING system parameter to “FORCE”, which under certain circumstances can improve re-use of execution plans.

ALTER SYSTEM SET cursor_sharing='FORCE' SCOPE=BOTH;

After altering the setting & flushing the shared pool, we saw an immediate reduction in CPU load along with an instant reduction in the numbers of queries being cached & a corresponding drop in contention / wait states due to all the various types of lock state.  Since introducing the setting we’re now seeing a maximum of 5-10 open versions of our most frequently hit queries despite some of them having been executed 4-500,000 times. We’ve also been able to resume operations with both cluster nodes online as neither node is now running at much more than 30-40% CPU.

This chart shows the difference in wait states within the database from before & after the CURSOR_SHARING configuration was adjusted...

Just goes to show that Oracle performance issues can be dependent on anything and sometimes wait states from something seemingly unconnected to the issues you’re seeing can actually be directly symptomatic of the underlying issue plaguing your database!

by Tim

Oracle 11g RDBMS.. Never thought I'd say this, but I'm slowly being converted

A few years ago some colleagues and I ended up in a relatively heated debate around the relative merits of Oracle vs Microsoft’s SQL Server in terms of which product was the “best” database.

At the time, I seem to recall I was more on the side of SQL Server than Oracle – probably because I’d been all but indoctrinated into the world of Microsoft  through my developer’s background with limited exposure to other enterprise-level solutions, and at the time our largest database was probably 15-25gb or thereabouts.

These days, however, things are very different & the concept of a 25gb database being “large” has been somewhat blown out of the water by our newest system; which currently GROWS at 20-25gb a day….

When we were debating the technologies’ relative merits, I think we were probably focusing on an extremely small subset of the two products and perhaps not paying much attention to the differences in their underlying architectures or the suite of ancillary software they include.

Both databases have their advantages and disadvantages, but only Oracle offers ASM (Automatic Storage Management).

Consider a scenario..

You have a database which is around 2.5Tb in size, supporting a busy system which has a problem with contention on its underlying storage system.

The fix for the contention problems is to add more disks into the SAN supporting your database (or perhaps add another SAN) & migrate all 2.5Tb off your current storage system onto the new environment.

Downtime on the application needs to be avoided as the system has to be available for use nearly 24/7, and it’s not possible to secure a sufficiently large maintenance window to take it offline to move the data.

I’ve spent some time wondering how I’d do this with SQL Server, and in the end came up with the idea of adding new data files to the database provisioned from the new storage; and then trying to shuffle everything around to move data from the old files to the new. Where I got somewhat stuck was in how to move all the data in a timely fashion without downtime… and ended up concluding that at some point, the DB would need to go offline to move or remove the last data files. Most guidance on this subject suggests simply abandoning the idea of doing this online & requires the DB to be taken offline to use Detach & Attach to physically move the files. Wouldn’t fancy this much with 2.5Tb of data…. Nor would I particularly relish trying to get a 24-hour downtime window agreed to ensure the files could actually be moved in time!

With Oracle on the other hand, this potential headache becomes a pain-free operation which can happen with the application fully up & running, with little or no performance impact on the system. Steps include: provision new storage to your Oracle servers; add the new disk/s to your ASM diskgroup & instruct it to remove the old disks; sit back, wait for it to finish rebalancing (moving data); remove old disks.
Much, much better.

Yes, Oracle may be more expensive than SQL to acquire & tends to need more specialised DBA skills to properly manage & support; but if your application needs to stay up 24/7, your choices are a little limited..

Would I want to try managing 2.5Tb+ on SQL Server? Personally, no.

by Tim

Data Recovery - When is a disk unrecoverable, what do you do next?

As you may have been reading in previous posts, we’ve been trying to get some data recovered from a failed laptop harddisk.

If the marketing material put out by leading data recovery companies is taken at face value, you end up with the belief that nearly anything can be recovered from… be it a disk that has just rolled over and failed with a hardware fault through to some sort of incredible incident leaving a smouldering & charred disk that needs to be prised out of the containing laptop, through to basic file deletion or accidental-formatting errors.

Reality however, would appear to be slightly different to this concept..

For a start, all data recovery companies are not created equal. Some claim to have “world class” facilities along with cleanrooms maintained at the highest standard yet don’t have much more than a website & rented office to their names. Others have the facilities, but claim to recover from any sort of problem yet are flummoxed by simple hardware failures.

Engaging a datarecovery company seems to be loosely akin to trying to find your way safely through an uncharted minefield while blindfolded & without any form of guidance, mapping or help. Not exactly a recipe for success, even on a possibly good day.

Assuming you feel brave enough to select & engage such a company you then are plunged into a waiting game where you feel obliged to give them time to work on the disk, but yet think you should be calling them every few hours to see if they’re any closer to recovering your data.

When they eventually deliver the news that a disk is or isn’t recoverable, if the data can’t be recovered you invariably start wondering how they can recover data from a disk that’s been roasted, thrown off a building or crushed etc – yet they’ve seemingly unable to recover data from something that’s suffered what looked like a pretty standard mechanical failure… albeit one that apparently had the ability to damage some hidden surface of the disk rendering it unrecoverable….

Just when you’ve started to come to terms with this concept they have to change the game again; throwing in to the conversation that “oh, but we have a special R&D lab that might be able to help…” and offering free of charge assessment to see if the disk  is viable for this.  Cue another dilemma; if the answer’s yes, and they’re able to produce a list of files that might be recoverable – do you go ahead with recovery, possibly picking up a £5k bill?

How valuable is your data? Is it cheaper to go down the recovery route than recreate it?

We’re progressing a 2-pronged approach. The disk has gone off for “Advanced” assessment and we’ve started the process of rounding up as much data from old backups & alternative sources as we can.

The single biggest issue we have is that many InDesign files were lost on the disk – representing a gaping hole in our collection of publications, adverts, posters etc for which we had finished PDF files but no source materials. Faced with this we started looking at our options and after a tedious search we’ve been amazed to find a tool that allows us to convert a PDF file back into a passable InDesign document. PDF2ID produces a pretty good InDesign rendition of a PDF along with extracting high resolution images and preserving as much of the formatting as possible.  This represents the means to give us a considerable leg-up in terms of recreating the lost documents, as although the InDesign files need reformatting & tidying up at least the content’s all on the right pages & and in a fully-editable format.

All in all there’s a rocky road ahead, which unfortunately includes yet another potential decision point around whether our data is worth the £2-5k that the R&D based recovery service might be able to recover the data for….!

by Tim

Data recovery... Why is life never as easy as it should be? An update!

Why is it that computer equipment, and especially any form of computer equipment that stores data, always only ever fails at an inopportune moment?

Laptops for work never break the day before you go off on holiday leaving plenty of time for them to be fixed while you don’t need them; digital cameras never fail once you’ve finished using them to take pictures of something important; and harddisks never, ever fail when they’re sat on a shelf or desk, blank, without anything important on them.

We were unfortunate enough to have a laptop harddisk fail which, just as it was needed and while it contained a substantial amount of documents, PDFs, InDesign files and stock photos that for some currently unknown reason were not being properly backed up to another device.

The disk started to make lots of nasty sounding noises – metallic clicking, rasping and grinding sounds… none of which you really want to ever hear emanating from the direction of a hard disk – so after a quick check of the obvious, we determined it had a bit of a problem and removed it from the host computer.

The next stop saw us embarking on what’s turned out to be an unexpectedly difficult challenge of finding someone who’s able to take a look at the failed disk, fix whatever’s failed, and hopefully recover the data.

We started looking around for a data recovery company using the same method as I’d expect most people would… Google.

Searches for “hard disk data recovery” and “laptop data recovery” etc all returned 10000’s of hits, most of which seemed to be links to a variety of small outfits who seemed to collectively promise to fix any form of fault known to man but yet didn’t actually seem to either be properly established companies & typically lacked any form of actual clean-room facility. Not really the sort of outfit you’d want to entrust anything to – yet alone a disk containing loads of stuff that we wouldn’t mind seeing again. How on earth are you supposed to be able to determine who you can and can’t trust to recover your data!?

In the end we bombarded most of the top 4 vendors found in our search with a number of questions to see whether they were able to help and ended up selecting one of them based largely on their quick & precise responses and a reasonable cost… along with a no-win-no-fee type guarantee around diagnostic costs so that if they were unable to recover the data all we’d end up paying was the cost of return shipping to get the disk back.

A quick phone call later and a courier collection was booked for the following day.

We duly packaged the disk up as instructed and sent it on its way… hoping to hear something positive within 24 hours or so.

24 hours passed… and the silence was deafening.

Another 24 hours passed without word from the company, so I called them for an update to be told something along the lines of “yup, we’ve had a look… it looks like it’s suffered a mechanical failure with failed read/write heads, and we need to source parts to repair”. Fair enough I suppose, so we sat back to wait a little longer.

A week after we’d dispatched the drive and after a few more phone calls similar to the above, it was starting to become fairly evident that the company we’d selected were struggling with this disk, a thought that was confirmed when they called to say that despite replacing the read/write heads they’d deemed the device to have a corrupted “service area” and as such was beyond their ability to recover.

Back to the drawing board…

More Googling quickly revealed that there’s a fairly short list of companies who have the ability to recover from Hitachi disks with corrupted service areas so we called what appeared to be the top two companies offering this capability and quickly picked what looked like the front runner in terms of capability.

Our original vendor was happy to send the drive directly onto the new company so this was arranged and the disk dispatched.

Within 12 hours of its arrival, the second company had inspected it and interestingly diagnosed it as having failed read/write heads – suggesting perhaps that either the first vendor’s claims of repairing the disk either weren’t strictly correct, or they’ve swapped the replaced heads out for the original failed ones.

We’re now back in the waiting game….

by Tim

Data recovery....

Data recovery… how on earth do you determine which company you can actually trust to recover data from a dead harddisk without damaging it further??

One of our laptops has run into a bit of a problem and we're now the rather worried owner of a harddisk that's not being recognised by the machine's OS, makes nice clicking/grinding noises rather than spinning up, and contains far, far too much data to simply write off as lost.

by Tim