Posts Tagged ‘11g’

Mailbag: Advice for an Oracle 9i DBA

September 9, 2009 Leave a comment

Dear Iggy,

I’m M. S. [name withheld], 25 years old, Egyptian, graduated from the faculty of Computer Science & Information Systems in 2006, working in KSA.

First I want to thank you very much for your book; it’s really very helpful.

I’m OCP DBA 9i from 2006 but I never implemented Oracle. I know SQL, PL/SQL, Forms 9i basics & concepts.

Actually now I will be Oracle DBA 11g in a new project. Please give me your advice about where I can start.

Thanks a lot for reading.

M. S. [name withheld]

Dear M.,

I apologize for the delay in my reply. I recommend that you install Oracle Database 11g on your laptop and practice using it; the best way to learn is by doing. I also recommend practicing the exercises in the free, online 2 Day DBA guide; each chapter has a link to an Oracle By Example (OBE) online tutorial containing step-by-step instructions and screenshots.

Oracle database administration basics have not changed but Oracle Database 10g and 11g have many new features that you should study. Here are some free, online resources:

You can find a more detailed discussion of new features in the following books:

You should also consider upgrading your certification. You can upgrade your certification directly from Oracle 9i OCP DBA to Oracle 11g OCP DBA by taking exam 1Z0-055. Here are some Oracle-authorized practice exams.

Best of luck in your new assignment,


Subscribe to this blog by Email

Spotlight on Oracle: An Interview with Bert Scalzo

Bert Scalzo has worked with Oracle databases for well over two decades, starting with version 4. He has an extensive academic background, including a PhD in computer science. He has written or co-authored six books on Oracle: Oracle DBA Guide to Data Warehousing and Star Schemas, TOAD Handbook, TOAD Pocket Reference, Database Benchmarking: Practical Methods for Oracle & SQL Server, Advanced Oracle Utilities: The Definitive Reference and Oracle on VMware: Expert Tips for Database Virtualization.

Why Oracle? Enterprise Edition with Partitioning now costs $59,000 per CPU. The annual support fee is 22% of the purchase price. MySQL has partitioning and it’s free.

I always answer this question using a hopefully clever wordplay of an old Saturday Night Live skit: “Oracle been very, very good to me.” I chose databases and Oracle way back, and mostly stick with what I think I know. I’ve presented papers at the MySQL conference—but until their SQL optimizer matures, I’ll stick with Oracle’s proven track record, maturity, and scalability. But I’m watching MySQL, SQL Server and even PostgreSQL—because things can always change.

My experience with RAC has been bumpy. Is it just me? Is RAC a bane or a boon?

RAC is a great technology. But RAC requires teaching us “old dogs” some new tricks. We cannot simply apply SMP-based Oracle rules of thumb to RAC and expect the same results. I’ve seen numerous RAC “proof of concept” tests fail from this, and thus people sticking with their comfort zone. But I’ve yet to see a successful RAC deployment need to revert. Done right, RAC rocks. Yes it’s more complex and requires mastering new things. Just think of it as job security. I do, however, recommend Quest Software’s Spotlight for RAC, because it makes RAC architecture visualization and the optimization identification process more straightforward.

A company I know has been using Oracle 10g for a long time. Is it time for them to upgrade to Oracle Database 11g? Their database is very stable and performance is rock solid. They don’t have a support contract anymore and don’t seem to need one. The application was written in-house.

That’s a tough question. The technologist in me wants to say 11g—because I like to stay current. The pragmatic consultant in me says don’t fix what ain’t broke. But I never advise anyone to skimp on Oracle support—and no, I’m not an Oracle stockholder. MetaLink access alone can save many hours’ time and real dollars. While Internet searches are very powerful and can find useful stuff—critical wisdom and knowledge resides exclusively within MetaLink. It’s worth every penny. Furthermore, as long as Oracle offers new patch sets for your database, it’s worth being able to evaluate and deploy them.

In your latest book, Oracle on VMware, you make a case for “solving” performance problems with hardware upgrades. Is Oracle too hard to tune, then?

No—Oracle is a great database with extensive flexibility. Instead, it’s the cost of tuning (whether for database, operating system, and/or application) that has become somewhat cost ineffective. Let me give an example. A decade ago, tuning the SGA memory allocation and usage was critical—and it still is today. But with dirt-cheap memory, servers that can accommodate tons of memory, and Oracle automatic memory management—just buy more memory. It’s often far cheaper to add 16 GB RAM than pay two days’ expert database consultation fees. Yes, there are some scenarios where simply adding hardware might make a database bottleneck situation worse. But the odds and cost are now such that it’s a worthwhile first step—since you can always tune if that fails. I’m just preaching to try cheap and easy solutions first.

I make a good living adding hints to Oracle queries to improve performance. Does the optimizer really need a hint or is it something the developer or DBA is doing wrong in the first place?

That’s a loaded question. People generally fall into one of two camps: hints are okay and hints are the devil’s spawn. So picking one side or the other means 50% of the people will disagree with me, and trying to find a happy middle ground will mean 100% of the people think I’m nuts. Personally, I’m not a huge fan of hints—I’d rather rely on 11g’s outstanding plan management features (profiles and baselines). For me, even 10g’s stored outlines are often preferable. But I nonetheless often have to use hints to find the needle in the SQL optimization haystack for whatever reason. When I do, I rely on Quest Software’s SQL Optimizer. Okay, now I sound like a sales guy—I’ve mentioned two products from the company I work for. But we used to do a booth drawing where we displayed a SQL statement on a banner and asked people to guess how many rewrites could be done. No ones’ guesses ever came close to the 39,000 actual rewrites possible. Any tool that helps to generate and filter through all those SQL coding possibilities and performance ramifications is worth a look. It even suggests structural changes to further extend that research. Do note that I did not mention my tool—Toad. So while I may have mentioned some Quest tools, I did not mention my favorite and my baby. I hope that this helps to retain some of my credibility!

Is 24×7 really possible? Personally, I’ve never seen a site that was truly 24×7, and I’m inclined to believe that it’s a myth. Is MAA the answer? If it is, it sure looks expensive. Is there a cheaper alternative?

This is an easy question—heck, no. Nothing is life is absolute. There are few universal truths. So 24×7 is not possible—not even with RAC, Data Guard, and redundant hardware. Remember, the Titanic was unsinkable—and look what publicizing that got them. But we technologists work for the business people—they are our customers. Bear in mind that we (technologists) are just overhead. The customer (who is always right) says they are a 24×7 business. So we have to find an SLA that can satisfy their demands/needs. Look at Internet hosting companies. They generally advertise something like 98.5% uptime. That’s what we too should strive to offer. Then based on their reply, we know what hardware, software, Oracle options, etc., we need to deploy to meet those requirements. Then monitor and improve as needed.

My manager keeps nagging me to get certified. Certification requires the purchase of at least one instructor-led training course. For the price of a five-day training course, I can buy dozens and dozens of good books. I tried to convince my manager to give me a week off to spend at the beach, reading a good book like Effective Oracle by Design by Tom Kyte, but he wouldn’t bite. My manager doesn’t take my opinion seriously but he’ll listen to you. Is he right about this or am I?

If you look at my resume, I don’t really have any current Oracle certifications. So my answer may seem like sour grapes—but I’m not a fan of certifications. When I got my vision corrected (i.e., Intra-Lasik), I looked for someone who had done it a lot and that pro athletes and other doctors went to. Thus my personal selection criteria were competence and acknowledged experience—not cost or academic credentials. I believe the same in our industry. I have a PhD, but that means nothing to people who hire me. It’s what I’ve done and what I know that counts—not the paper on the wall. But meet your manager halfway, agree to attend the next expert training session for certification held in Hawaii or on a cruise ship. Don’t laugh, such events do exist. As for Tom Kyte, you are spot on—his books and blogs are pure gold.

Thanks for answering all my cheeky questions today. I’m always interested in buying a good Oracle book. Do you have some personal favorites that you can recommend?

There are tons of people writing Oracle books these days—and four whom I’ve worked with personally and respect (i.e., I learned a lot). For anything PL/SQL related, Steven Feuerstein is the man. For general DBA texts, Mike Ault is a safe bet. For RAC specific, Murali Vallath is second to none. And for anything performance or tuning related I look to Guy Harrison. In fact, Guy has a new book coming out next year that will become the best single reference book for any Oracle professional. I also like Cary Millsap—especially for his Method R trace file tuning approach. But I’ve not had the privilege of working with Cary; I’ve just attended his sessions and classes, and have had numerous lunches with him. There are lots of other great Oracle authors—so don’t restrict yourself to my short list.

Interview conducted by Iggy Fernandez for the February 2009 issue of the NoCOUG Journal (Click here for the PDF version)

Subscribe to this blog by Email

Fresh Perspectives: An Interview with Karen Morton

August 16, 2009 3 comments

For over 20 years, Karen Morton has worked in information technology, starting out as a mainframe programmer and then growing and changing with the times to do development, DBA, and data architect work. She has been using Oracle since the early 1990s and began teaching others how to use Oracle over a decade ago. She has authored several Oracle training course books, including leading the development of the Hotsos and Method R SQL Optimization curriculum. Karen is an Oracle ACE and a member of the OakTable Network. She blogs at

Why Oracle? Enterprise Edition with Partitioning now costs $59,000 per CPU. The annual support fee is 22% of the purchase price. MySQL has partitioning and the price is right: it’s free.

Oracle has led the industry for the past two decades and I don’t see that changing. My world revolves around Oracle, so I can’t really speak to the differences with MySQL as far as functionality, reliability, scalability, and performance. Yes, Oracle is expensive. Is it worth the cost to your business? Only your business can decide that. I hear the old adage “you get what you pay for” floating through my mind as I write this. Honestly, I think that the cost of something (i.e., higher cost equates to better) as a measure of its ability to satisfy your business needs is less true today than it has ever been. Open source products have made big gains in the market and they do that for a reason: they meet the demands of the businesses that implement them, and they have an attractive TCO (total cost of ownership).

While I think Oracle is the winner for the large enterprise environment, I think it is only prudent that each business evaluate their needs and their cost limitations and choose what best fits for them. I also think that if open source vendors continue to improve, Oracle may one day have to seriously consider pricing model changes in order to maintain their market share. As the gap between free or very low-cost open source databases and Oracle closes, cost may win more often than not.

A company I know has been using Oracle 9i for a long time. Is it time for them to upgrade to Oracle Database 11g? What are the risks? What are the rewards? Their database is very stable and performance is rock solid. They don’t have a support contract anymore, don’t seem to need one, and can’t afford to pay for one. The application was written in-house.

Well, I think you answered the question for me by saying they don’t have a support contract, don’t seem to need one and can’t afford one even if they did. In this case, it sounds like a case of “if it ain’t broke, don’t fix it.” They have stable performance using what they’ve got and no apparent need for any features 11g might provide. Until they can justify a need, then they’re doing the best they can for their business by staying with 9i. The risks of upgrading, particularly the possibility of execution plan instability that might cause performance issues, isn’t worth it when they are happy and rock solid where they are.

We spend a lot of time tuning databases. Why does it have to be that way? Is Oracle difficult to tune? Are we building the databases or applications wrong?

The whole idea that tuning is difficult is a very pervasive belief. Many people think it is more magic than science and believe if you don’t know how to tweak some 200-plus instance parameters in just the right way, you’re doomed to failure (which translates to having to hire an expensive consultant to tune things for you). In a 2003 Oracle white paper entitled “The Self-Managing Database: Automatic Performance Diagnosis,” authors Graham Wood and Kyle Hailey state that one of the main reasons the many advisors were developed starting in 10g was to prove that tuning was in large part about following a logical, methodical approach. And since computers were pretty good at doing just that, the goal of the advisors was to simplify the process of diagnosing problems and make it seem less difficult.

So, we’ve now got access to a set of advisors that can help diagnose problems quicker and easier than ever before. However, I think that when problems do occur, the best defense is a good offense. In other words, we (the DBAs and developers who manage the database and write applications) must have the knowledge and skills to know how to quickly and efficiently diagnose problems when they occur. Gaining the knowledge and skills will also help ensure that we don’t make many of the big performance mistakes to start with. I really believe that knowledge is power and that tuning doesn’t have to be as hard as it is often made out to be.

In his latest book, Oracle on VMware, Dr. Bert Scalzo makes a case for “solving” performance problems with hardware upgrades. What is your opinion about this approach?

If you have performance problems that have been proven, through careful analysis, to have their root cause in substandard hardware, then the solution may be to upgrade. The investment made to make sure personnel are adequately trained to properly diagnose and repair performance problems can benefit your company in the long run much more so than taking what appears to be a quick fix by throwing hardware at the problem in the short run.

I think that when personnel are properly trained and knowledgeable, they will not only be able to quickly, efficiently, and cost effectively find and fix problems when they arise, but they will not make the mistakes that cause the problems in the first place.

I believe the truth is that if hardware isn’t your real problem, you’ll eventually have no other choice than to address the actual root cause, because hardware upgrades can only be a Band-aid on the wound for so long. I’ve seen too many people try this approach only to pay hundreds of thousands of dollars for hardware they don’t really even need and still have performance problems. When they finally do address the real root cause and fix it, they often find that they could have bypassed the entire expenditure on hardware and saved more time and money if they’d just done it right in the first place.

I make a very good living adding hints to Oracle queries to improve performance. Does the optimizer really need a hint or is it something the developer or DBA is doing wrong in the first place?

I think hints are phenomenal testing tools and are sometimes the only viable avenue for getting a query to perform as you desire. But I also think that if you understand the Oracle optimizer and why it makes choices like it does, then you can often find a way to rewrite queries to get the results you want without a hint.

So no, I don’t think the optimizer needs a hint as a rule. The bottom line is that you have to do what is necessary to get the performance you need. My first goal would be to do everything I could with my SQL to give the optimizer the best chance possible to get it right without a hint. If I still can’t get the execution plan I want/need, then I’ll use a hint. However, I’ll document what I believe is the reason why the hint is needed so that its use will be understood by those that follow me.

My manager keeps nagging me to get certified. Certification requires purchase of at least one instructor-led training course. For the price of a five-day training course, I can buy dozens and dozens of good books. I tried to persuade my manager to give me a week off to spend at the beach, reading a good book like Effective Oracle by Design by Tom Kyte, but he wouldn’t bite and keeps nagging me about certification. My manager doesn’t take my opinion seriously but he’ll listen to you. Is he right about this or am I?

Personally, I think certifications can only effectively answer one question: Can a person study and memorize well enough to pass the required tests? There are people out there who attend some kind of certification boot camp for a couple of weeks and take all the tests and pass. There are others who buy exam study guides and take sample tests until they basically have most everything they’ll need to know memorized so they can pass the tests. And there are people out there who have years of experience that don’t even pick up a book or spend a very small amount of time to prep that pass the tests.

I really don’t think certification is a measure of experience or ability. I wonder which employee a manager really wants managing their database: one with a certification but no proven experience or one without a certification but years of experience and a great track record.

If a manager is willing to pay for your certification, get it. If having a certification is the difference between you being chosen for a job over someone else equally qualified, then get it. But I think it’s a mistake to use certification as a measuring stick for experience or an indicator of someone’s ability to handle real-world, tough situations. Certification may get you in the door, but it’s how you apply the knowledge you have and your ability to quickly diagnose and repair problems that will keep you there.

Will there be any jobs left for me in five years? Should I move to Bangalore? Are the Indian shops any good? How should I adapt? One of my previous employers suffered wave after wave of layoffs. I personally know many IT professionals who have switched careers: a PeopleSoft engineer became a police officer, a project manager became an insurance agent. Should I plan on a career change? I’ve always wanted to be a beekeeper.

My experience has been that outsourcing may provide a low-cost way to quickly build an application, but it is not a solution for optimally performing and easily maintainable applications for the long haul. IT staff will still be needed here in the database trenches, where they will have to provide on-going real-time support for applications. Since lower costs and speed of delivery are often the only criteria placed upon foreign outsourced vendors, that leaves lots of room for in-house staff to be needed to monitor, fix, and improve what arrives from these outsourced shops.

What that means to us is that there may be fewer jobs available in the short term at least. So, it becomes imperative that people are on top of their game as far as their knowledge and skill sets are concerned. I know of one company who outsourced their entire development staff and sent the work overseas. They had more performance problems than they could count, very disgruntled users, and ended up spending more time and money in the end. Within one year, they’d hired back about 50% of the original staff to try and fix the work that was delivered from the overseas vendor. Within two years, they’d hired back 95% of the original staff (in terms of the number of developers). They found that if they’d just stayed fully staffed to start with, they would’ve actually saved more money!

I don’t think anyone can make a blanket statement about the quality, or lack thereof, of outsourcing. But, I believe that well-trained in-house personnel can get the job done better and with less need for re-work than people who are thousands of miles away with no personal stake in the company that hires them.

Personally, I think if you love what you do and enjoy being good at it, then it’s not time for a job change. It’s time to work harder, learn more, and prove your value to your employer. But, if you’re not doing something that makes you glad to get up every day and go to work, then that’s the best reason to change jobs that I know of. Perhaps for those who aren’t in IT because they love it, when times are tight and lay-offs occur, it may be time for them to re-evaluate where their heart is and move on to something else.

And, by the way, a friend of mine was a beekeeper and that’s one job I can easily say that I’d never want to have! I’m terrified of (not to mention allergic to) the little creatures!

It costs us almost $30,000 per year to produce and distribute the Journal and a little more than that to organize four conferences. We have about 500 members and a little less than 200 attendees at each conference. Our membership levels have been stagnant for a long time. Has Google made us obsolete? I begged my previous company to buy a corporate membership but, except for my manager, not a single person from that company ever showed up at our conferences. Should we close shop? What could we do better to attract more members?

I think that user groups play a very important part in the Oracle community. With economic conditions being what they are now, many companies want to keep spending on continuing education to a minimum. User groups like NoCOUG provide that local lower-cost option and not only help educate but provide great networking opportunities as well. I certainly don’t think they are becoming obsolete. I think they’re more important than ever. The issue is making sure people understand the value they get from having a good user group in their area.

First, there are cost-cutting strategies to help the organization be viable without need for as much financial backing. I’ve seen some user groups across the country reduce quarterly training day events down to just once or twice a year. I’ve seen reductions in general meetings from once a month to once a quarter. I’ve seen printed newsletters and periodicals converted to on-line formats. I’ve seen short online training events replace on-site training events. Google hasn’t made user groups obsolete, but Google has shown us the power of getting quick access to information online and in a hurry.

Second, it’s my belief that people are always willing to pay for things they believe provide meaningful value. Even when you can get some information online, the quality of that information isn’t guaranteed. One of the great things about user groups is that they provide a place to get good information from reputable sources. Even when the user group doesn’t sponsor an in-person event with a speaker of note, the group can post links to qualified information on the Web and save members from having to weed out a ton of questionable content and get them straight to the good stuff.

Third, meeting with people who share common experiences with you is one of the best reasons to belong to a user group, in my opinion. It gives you an opportunity to learn from others who have already faced problems you’re currently mired in and to be able to leverage their experience to help you find solutions for you. And, as we’ve already discussed, many of us are facing layoffs and outsourcing trends that make our jobs less secure than we once thought. Having a community of colleagues that can help you in searching out new job opportunities is critical, and user group connections can often provide the lead to your next job when you need it.

In the end it’s all about giving people what they want and what they need in the most convenient and low-cost way possible. I certainly hope this is the time when user groups really rise and shine versus decline. There are few other opportunities to get so much for so little than what can be gained through a great local user group like NoCOUG.

Thanks for answering all my cheeky questions today. I’m always interested in buying good Oracle books. Do you have some personal favorites that you can recommend?

Cost-Based Oracle FundamentalsJonathan Lewis.

Troubleshooting Oracle PerformanceChristian Antognini.

The Art of SQLStephane Faroult.

Optimizing Oracle PerformanceCary Millsap and Jeff Holt.

Anything and everything from Tom Kyte.

I’ve got a huge library of books that I’ve really found to be helpful, but these are the first few that come to mind.

Interview conducted by Iggy Fernandez for the May 2009 issue of the NoCOUG Journal (Click here for the PDF version)

Note: Here’s the full quote from Dr. Scalzo’s book: “Person hours cost so much more now than computer hardware even with inexpensive offshore outsourcing. It is now considered a sound business decision these days to throw cheap hardware at problems. It is at least, if not more, cost effective than having the staff [sic] tuned and optimized for the same net effect. Besides, a failed tuning and optimization effort leaves you exactly where you started. At least the hardware upgrade approach results in a faster/better server experiencing the same problem that may still have future value to the business once the fundamental problem is eventually corrected. And, if nothing else, the hardware can be depreciated, whereas the time spent tuning is always just a cost taken off the bottom line. So, with such cheap hardware, it might be a wiser business bet to throw hardware at some solutions sooner than was done in the past. One might go so far as to make an economic principle claim that the opportunity cost of tuning is foregoing cheap upgrades that might fix the issue and also possess intrinsic value. Stated this way, it is a safe bet that is where the business people would vote to spend.”

Subscribe to this blog by Email

%d bloggers like this: