Pythian Group

Hello folks, it’s great to be back from hiatus. This is the 183rd edition of Log Buffer (arguably the best edition of Log Buffer yet!), the weekly review of database blogs.

The last time I wrote this was just under 2 years ago!!! WoW. Things have changed. Sun bought MySQL, Oracle bought Sun. Those were bombshell deals. At least you can rest assured that some things can be constant. I still eat my daily serving of broccoli (among other healthful “things”). I urge you all to go the fridge and grab some veggies prior to sitting down for this week’s… ahem… digest.

Starting with Oracle, Pythian’s own Alex Fatkulin illustrates a bug (?…likely) that could lead to logically corrupted data. Doug Burns provides an update on his Hotsos 2010 trip with some serious thumbs-up action pointing in Tanel Poder’s direction and his affinity toward SQL*Plus.

Over at Mark Rittman’s Oracle Weblog, Venkatakrishnan J talks about Oracle Warehouse Builder 11gR2 – Importing Essbase Cubes using ODI Knowledge Modules – Part 1. This should drive the MySQL folks bonkers (in a good way) since he points to an example by David Allen to import MySQL Metadata over and vice versa.

Oracle Virtualization Blog’s Adam Hawley let everyone know about a Best Practices around Oracle VM with RAC RAC SIG webcast. It was on March 18th, but it was recorded and should be available online. I’m interested in this stuff so I included it here. I hope you can enjoy it too.

I had the honour to recently provide a training session at a customer site on tuning methods and tools. A key topic of the discussion was related to Oracle statistics and the CBO. It was timely that the Oracle Scratchpad’s Jonathan Lewis posted a series created by Doug Burns all about stats. Nice.

Has anyone ever told you (or maybe you deduced it on your own) that leaving out where clauses is a bad thing. Well, leave it to Charles Hooper on his Oracle Notes to prove to us that sometimes, they are actually more efficient for solving certain types of problems. In another excellent post titled Physical Reads are Very Fast, Why is this SQL Statement Slow Charles also dives into why a slow query is “actually” slow. There’s an interesting discussion taking place. More performance tuning goodness was posted by Joel Goodman discussing some interesting behaviour with Oracle Index Leaf Blocks contention. Tell RAC to Leave Your Leaves Alone! Kerry Osborne illustrates the use of an interesting hidden parameter (_high_priority_processes) to resolve “log file sync” issues.

Over on the other side of the fence in the MySQL world, Jay Pipes @ Join-Fu gives us some background on the MySQL Transaction Log. Vadim Tkachenko with the MySQL Performance Blog has a number of posts on Percona 9.1 as well as a list of related sessions at the 2010 MySQL conference. Check it out.

On a more somber note (and by somber I mean legal, we all hate legalese don’t we?), Giuseppe Maxia over at The Data Charmer discusses Protocol, the GPL, and how Bazaar can help. He also has some good takeaways from the Linux MySQL distros meeting in Brussels. Baron Schwartz at xaprb has a new tool he’d like the MySQL folks to take for a spin. Try mk-query-advisor, a new Maatkit tool. It uses heuristics to find problems in SQL. Please use it and give feedback!

Are you interested in MySQL Clustering? I am. Andew Morgan has a new post introducing a tutorial to Build MySQL Cluster 7.1 from source – including MySQL Cluster Connector for Java. With Alex Fatkulin maybe running into an as yet possibly, sort of , kinda non-discovered bug, it’s only fair we also get some vision into the dark side on the MySQL front. Shlomi Noach states But I DO want MySQL to say “ERROR”!.

Brian Aker invites readers to participate at this year’s O’Reilly MySQL User’s Conference where they will be doing their first ever Ignite talk series.

Lastly, Stewart Smith has a very good set of posts illustrating Stored Procedures/Functions for Drizzle. Check this out (from Stewart’s post).

drizzle> select libtcc("#include <string.h>\n#include <stdlib.h>\nint foo(char* s) { char *a= 0x199c610; strcpy(s, a); return strlen(s); }") as result;

+--------------+
| result       |
+--------------+
| Hello World! |
+--------------+
1 row in set (0.01 sec)

I dare you.

On to SQL Server….

Scary DBA SQL MVP Grant Fritchey discusses Undocumented Virtual Column: %%lockres% and also recaps the SNESSUG March Meeting.

Jamie Thomson gives keyboard junkies some tips to Kill your temp tables using keyboard shortcuts : SSMS. Anyone interested in distributed queries should read Buck Woody’s Using linked servers, OPENROWSET and OPENQUERY.

Finally, Aaron Bertrand says with conviction “Yes, you can benefit from both data and backup compression”.

Having now exhausted my supply of munchies, it is time for me to retire. I bid you all a fantastic week. Keep your data safe, folks.

Shakir

Argh! Of all the frustrating, partially-completed features Oracle has released, this is the most frustrating. Did I mention this frustrating feature is frustrating when you get bitten by it? Why? Because you only need it when you actually really, really need it.

What am I referring to? FLASHBACK QUERY on a table that lives in a database with a large UNDO_RETENTION specified with lots and lots of UNDO_TABLESPACE space.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     604800
undo_tablespace                      string      UNDOTBS_1
SQL> 

So, what do we have here? UNDO_RETENTION to set somewhere in the 7-days range. How much space do we have in the tablespace? Read the rest of this entry . . .

Welcome to the 89^th edition of Log Buffer, the weekly review of database blogs.

Welcome, welcome everyone.

In writing this week’s Log Buffer, I’ve had a chance to sit down and read some excellent posts on all sorts of platforms. The depth and breadth of what’s available to house and retrieve data is astonishing.

Many of you who have read my posts will know that I’m a fan of vegetables. They are something most of us don’t eat enough of. Come on DBAs! I think we need to make a collective effort to get healthy. We need you to keep all these systems alive. I say this because I have a new found appreciation for the work we do day in and day out.

Six months ago my wife and I said hello to our baby girl for the first time. I don’t say this to elicit any type of congratulations, but to illustrate something entirely different. If you have ever been to a hospital for any reason — to celebrate, to hope, or to say goodbye — you know the sheer complexity of the vast numbers of systems that need to interact. Daily, these systems save lives and help bring new ones to this world. I saw first-hand how the work I do on a everyday keeps the wheels turning.

Some of our customers run systems used by hospitals and I saw them in action. In a simple world, treating people can be done without technology, but this is an issue of scale, and our involvement directly affects the sheer masses of people whose lives are better because of our behind-the-scenes support. It’s true here, and it’s true for the most serious, most mission-critical systems, to the least critical and most trivial systems. The work done by DBAs from all platforms should be recognized for what it is.

I’m proud of what I do for a living and happy that I get to work in an industry filled with so many savvy folks. Oracle, Microsoft, MySQL, Postgres, IBM, and countless other organizations, and the people involved in them have together created an industry filled with opportunities and challenges, and above all, they have together elevated our ability to communicate and share. It’s in this spirit that Log Buffer was created, so let us proceed!

Since I’m an Oracle guy, we’ll let Oracle go first this time.

Read the rest of this entry . . .

I’ve found lately that munching on carrots with French dressing is more satisfying than broccoli. Maybe it’s the tang-and-crunch combination. In any case, I was crunching away yesterday while thinking about how to answer a question one of our newer start-up clients asked me.

No one has ever come out and formally asked me for a document that states “Best Practices to Scale Application X”. It is an unusual demand, since it’s something many of us at Pythian have implemented, but it’s been more of an ad hoc, iterative process — and rightly so, since architectures must be so organic, and so tailored to the application. What’s more, no one has ever brought us on board so early in the game that we have a hand in actually — gasp! — doing the design and data-model from the get-go. Woo hoo!

Now, a little background. I have built and maintained a few systems. Some of them even supported over 100k concurrent users. These databases didn’t run RAC either (although I do support two very high profile RAC environments now). So, having been in the trenches and knowing what it takes to make a DB move, I got to thinking about some of the basic fundamentals. There are always rules of thumb, right? This is what you need to know to start with building a scalable high-performance system based on stuff that I’ve seen. Obviously, this assumes a database-centric app. Let’s start with the first ten principles.

Read the rest of this entry . . .

Have you ever heard the one about throwing hardware at a software problem?

In one of my previous blog posts, I mentioned something along the lines of—well I’ll just cut and paste . . .

In my experience, the solution to most problems (the ones the caller refers to as “it’s running slow”) are not rooted in hardware, because hardware problems generally cause things to not run at all. It always baffles me when developers and architects prescribe hardware upgrades to make things run faster, because about 80% of the performance-related problems and subsequent solutions I’ve dealt with were resolved by tuning the application.

Well yes, I know you can buy new hardware, and it’s easier. But when it comes to hardware, how many of you have a ten-node RAC cluster running Enterprise Edition with 8GB of RAM on each node, running off a massive SAN?

I’ve been on so many systems that have been running for years–poorly–the way they were, and in a week we can take them apart and have them running without a hitch. We’ve even managed to fix problems that turned out to be the business case to go from RAC back down to a single instance. How much did those customers save on licensing costs?

Back to the example at hand. I have this nifty RAC system that supports some very public and very mission-critical apps, and one day (it was Sunday night) it starts choking. We’re getting enqueues. Slowly they start climbing. Ten nodes came to a crashing halt. I have now seen a ten-node RAC cluster come to crashing halt and completely lock up.

Why, you ask? Read the rest of this entry . . .

I’ve done a few patches now — on Windows, Linux, Solaris and on many platforms for both 9i and 10g, some with physical standbys and others without. The lesson that I’ve taken away from them is that rolling back is twice the fun of actually applying the patch.

Last week, we discovered a bug related to the rollback of the Java DST patch for 9206 on Linux x86. If you’re in the unfortunate position of needing to roll this patch back, you won’t be able to. It turns out that the patch instructions omit telling you to also back up the classes.bin file in addition to
$ORACLE_HOME/javavm/admin/libjox9java_util.so.

When you rollback, you need to restore both of these files, and then do a create or replace java system. Otherwise you risk hitting a dreaded ORA-03114 End of file on communication channel.

In my case, when I followed the patch instructions, the Java test case given to us by Oracle still returned a 1. Those of you familiar with DST patching will have seen this, but for those who haven’t, here are the scripts you need to check for
a successful Java implementation:

Read the rest of this entry . . .

I always get a chuckle… well not always… but often, when I read anything to do with Microsoft vs. “the flavour of the month”. In this case, it looks like the flavour of the month is Oracle. I’m sure most of you in this space have already read much of this, but if you haven’t, here’s the link, and here’s the white paper that started it all.

The articles simple imply that SQL Server is more secure than Oracle. They go on to count the number of vulnerabilities identified over the last few years. It’s scary stuff and I’m sure we’re all running out now to ask our architects and DBAs how quickly we can port over to SQL Server.

I say I get a chuckle with these things because I actually read one of the use-cases from the author.

First off, I need to make a confession. I like Microsoft. They generally make the only software I find practical for my home use. And the keyboard and mice they make are unmatched. The XBox is great too. I also really like Linux and Solaris. Frankly, all the database that I work on, work best on them. Frankly, Oracle also makes the best database software for what it’s built for, and for what most of our clients use it for.

All of these companies are leaders in one way or the other and they have their own faults. I always get that chuckle when I read about Windows flaws. What do researchers expect? I liken it to Michael Jordan. See, I played high school basketball, and I dreamt of making the NBA someday, but then I went to university and common sense kicked in. I still play regularly. And what strikes me is this, when you’re the best player in the league, the opposing team always puts up their best defender against you. MJ became the best in the league, a god–among–ballers if you will, because the other teams consistently made him improve his game. It logically follows that if you have a dominant position in anything, everyone will come after you and expose every flaw in your armour, no matter how insignificant, or complex, or impractical that vulnerability is.

Obviously Microsoft has to work harder to fix its vulnerabilities on the OS front — it’s the undisputed champ. Well, I think the same can be said for Oracle. I think the more important question to be asked is, how many people are actually at risk of being exploited?

A case in point here. It’s the same as the use-case I mentioned above. I’m a developer and a DBA. I’ve written a couple of apps myself. Will anyone ever be able to use this exploit against me? I doubt it. The reason I doubt it is that security isn’t built only in the database, it’s built at the firewall, it’s built into the app, it’s built into the DB. Can you imagine how far a person has to infiltrate into my system before this exploit becomes practical?

At least Oracle came through with the logical response to the threat of having its security vulnerabilities unleashed on the masses: “(We) do not credit security researchers who disclose the existence of vulnerabilities before a fix is available. We consider such practices, including disclosing ‘zero day’ exploits, to be irresponsible as they can result in needlessly exposing customers to risk of attack.”

I like this. This shows more commons sense than that displayed by self-trumpeting security analysts threatening the company and putting users at risk. Yee-haw.

I once went to a basketball camp with Leo Rautins, one of the few Canadians to have made it to the NBA and played against Jordan and Larry Bird. (I played against him, and he was awesome, which is kinda sad, because in the NBA, he was actually the worst–performing Canadian player. So it was obvious why I needed to move my career in another direction.) He once told me that the best basketball player in the world is probably rotting away in prison (a reference to the huge numbers of African–American men incarcerated, hugely disproportional to the actual number of African–Americans in the US).

I don’t want to get into a philosophical discussion, since demographics, social policy, stats and crime are some of my hobbies and outside the scope of this article. But the point he was making is that if you don’t see something, how do you know it exists? And by extension, if fewer people are looking, does that mean something isn’t there? There are no scouts scouting American prisons, but he personally knew people with exceptional skill that were playing the penitentiary leagues. The problem is that these people existed but were unknown because no one was looking there.

The same is true with software. If no one is looking for vulnerabilities on a platform, does that inherently mean it is more secure? Is a Mac more secure because it’s better built, or is it more secure because there are fewer threats against it?

I know there’s a risk with these flaws, just as there are risks with using IE. But I still surf using it. Hell, I still use Windows 98 at home on an old AMD k62-450 machine that runs just as well today as it did five years ago. I haven’t been “identity thieved” yet. I have resigned myself to the fact that it’s just a matter of time, and if someone really wants to get to me, they’ll find a way no matter how non-secure or secure my database is. What all these security experts fail to mention is that the vast majority of security exploits are conducted by insiders.

Finally, it comes down to broken trust. That’s something you can’t guard against. Chuckle chuckle… sigh.

I’m a simple guy. I like things simple. I once had a consulting gig with a client who wanted to do something that I thought was quite simple.

But before I tell you what this simple thing is, I respectfully ask that you be seated.

The client entrusted me with the migration of one of their mission critical apps running 8i on HPUX to — drum role please — 9i … on … IBM Mainframe running os390 (z/os). For those who aren’t familiar with this battle–tested industrial–strength hardware, read here. For your added pleasure, read this article about the the mainframe world that came out today on CNN. You can see that it is indeed alive and well.

I had never worked on a mainframe before. Heck, I’m a young lad, and I’ve been brought up to laugh at these old hunks of metal. I was quickly put through mainframe initiation, which is a little odd, because you need to learn that os390 is a Dr. Jekyl/Mr. Hyde kinda apparatus. It’s something like two OSes running simultaneously together (as opposed to simultaneously on the same box but separately). It’s just odd and it took me a while to wrap my mind around it. But I did, and now I am a member–for–life of the reclusive club of mainframers. We wave at each other as we pass on the street.

In any case, the current production environment was composed of HPUX boxes running 8i and 9i. I can’t imagine how expensive the Oracle licensing must have been, but based on the sheer number of machines, I know Oracle was a happy supplier. Which brings me to the central point — unless you’re the one actually paying the bills, you don’t really realize that Oracle is quite expensive. While you do get what you pay for in terms of robust infrastructure, there are ways to lower the cost.

For this client, we were able to shave $600,000 (not just the Oracle license, but also the external costs of maintaining the environment) by migrating just one HPUX machine over to the mainframe with maybe 30 or more to go. All of them were scheduled to move over to the one mainframe with one Oracle license and one environment to manage.

I’m not here to convince you that mainframe is the solution to the world’s Oracle licensing cost problems, but if you’re in the “we have 30 machines each running Oracle” space, then I think it makes sense for you to seriously consider it. Add to that the benefits of all the control you get over the environment, it makes for a compelling story.

P.S.: In case any of you are planning such a migration, one thing I should point out about it: if you use import, then there’s a brutal bug that creates the initial extent of every object in the database with a minimum of 1MB. So, if you have scads of tiny objects taking up a few kilobytes, be prepared to account for this in your space requirement calculations.

P.P.S.: Trivia: Did you know that the government of Canada runs the busiest Oracle–on–mainframe environment in the world? It does!

Cheers,
Shakir

Or, How To Become an Export/Import Migration Superstar!

We’ve already established that I like broccoli. I get the taste from my dad who takes it with a glob a salad dressing and munches on it. It beats munching on Doritos. He and I both also do this with cucumbers. I don’t get it, but it tastes good and I know I’ll get a real kick out of it if I see my kids doing this someday too. In any case, one thing my dad doesn’t do is work with Oracle, and sadly, because of this, he’ll never get to add the Export/Import superstar trophy to his collection.

Over the last weekend I had the pleasure (I say pleasure because this was actually a very smooth operation) to do an 8i -> 10g, Solaris -> Linux migration. Talk about going in head–first. Now, whenever I run into a situation like this, we generally recommend a two–step process so that we can iron out bugs and be able to isolate causes. However, we were under serious time constraints, and we decided to just go with the following simple plan and move straight there. I had plenty of salad dressing to keep me company overnight.

Step 1 – export
Step 2 – import data
Step 3 – import everything else

That was the plan. We tried this a few times and when I finally had all the indexes etc. where I wanted them on the new server, and since we did some minor storage rejigging, I actually took a norows there and used that on the object import.

Now for the fun part. Typically, if you stick with a standard export/import, it’ll take you say… 10 hours. That’s what it usually took me. 30GB of data and 30GB of indexes. Now, you may ask, what makes me a superstar? Well, the fact that I dropped this down to about 5 hours, that’s what. This assumes you have big pages set up and Oracle able to address the space for a massive SGA.

The tricks:

1. Assuming a full export, use direct=y if you’re not using any predicates in the export.
2. Set your buffer to be big (10MB at least).
   Now the good stuff:
3. Alter all your constraints novalidate prior to export if you can bring the app down, and if you can take a consistent export. This helps in reinstating the constraints instantly without forcing Oracle to validate constraints on massive tables
4. I set workarea_size_policy=manual
5. Set sort_area_size=6GB (yes, 6 GB). Combined with 4, it let Oracle build indexes with more space in memory for all the sorts without the need to spill to disk.
6. Set massive online redo logs at 2GB each, 2 members each, 6 groups.

After the import, I reset everything back down to “normal”. The beauty of this migration was that the the client gave us a significant window of downtime. I cannot stress how much this actually reduces the complexity of the whole operation, and reduces the time needed to verify that everything works. Yay! for clients who provide realistic migration windows, and yay! for less complexity and effort (which equates to cost for all you business–y people) in migration windows.

I’m happy, and the client is happy, and I still have some cucumbers and broccoli left for breakfast.

P.S.: To add to the list of export/import deficiencies and 10.2.0.2 problems — you should note that function–based indexes need to be created manually.

In the last month, we have been hit with two clients’ large-scale failures. The first involved network issues; the second, disk failures. Fun fun.

We have one production and two standby environments. In the case of the first failure we went into our trusty dataguard config and performed what can only be described as a minor miracle.

First we connected to the standby that was to become the primary.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/xxxxx@rcatstdby2
Connected.
DGMGRL> show configuration;

Configuration
Name: rcat
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
rcat_aussw10_db - Physical standby database
rcat_nflsw01_db - Primary database
rcat_aussw20_db - Physical standby database

Current status for "rcat":
^C

We had to control-c out because it hung: it could not connect to nflsw01 and we knew that at least dataguard thought there was a problem. Then we took the steps that separate the men from the boys.

Read the rest of this entry . . .