Recently I posted that I was teaching a class in Switzerland. I’m home now in good ol’ Virginia, over the jet lag and ready to blog.
Class went very well. I taught the Burleson Consulting 3 day Oracle RAC course, one of our most popular courses which I am called upon to teach frequently. This class has it all: theory, installation, internals, tuning, network setup, disk setup, and tips from the field.
But enough about that for the moment…I just got back from Switzerland!
Geneva
Unfortunately bad weather postponed my flight by a day, and so I missed a good amount of time in Geneva. However, the next day air traffic was a little better, and I was able to get the flight from Norfolk to Newark, then Newark to Geneva. From what I understand, Newark Int’l is one of the few US airports with direct flights to Geneva, so at the least it was very convenient.
The visit to Switzerland was outstanding. Upon landing in Geneva I was picked up by a cousin who works for the WTO. He took me to his flat in Versoix where I got to visit some family I had never met before.
I didn’t sleep on the overnight flight, but I was determined to fight the jet lag and see some of the city. One of my relatives took me into Geneva from the Pont-Céard train station so I could tour the city a bit. She attends college in Geneva so she had to go off to class, but gave me an important piece of advice: keep an eye on the river and you can find your way anywhere.
The whole day I pretty much wandered around Geneva, finding various shops and disgracing the name of international travel by having a coffee at Starbucks (I know, I know). Later my cousin caught up with me and took me several places I hadn’t been, including up what felt like a thousand stairs to the top of the north tower of Cathédrale St-Pierre (St. Peter’s Cathedral). Turns out it was only 157 extremely narrow and tall stairs. Once at the top, the view is breathtaking. The room offers a complete panoramic view of Geneva and the mountains as you can see from these pictures:
After the flight, walking all over the city, and the climb up the tower (going down wasn’t so bad), I was pretty much beat. After dinner my cousin drove me to Lausanne and dropped me off at the Hotel Victoria Lausanne, a beautiful hotel on Avenue de la Gare (Gare being the train station).
There are two things I learned about Lausanne that made it startlingly different from Geneva:
Less people knew English. I had to pick up a few more French phrases
Everywhere was either up a hill or down a hill
Lausanne is a city built on a mountain (actually three hills that are part of a mountain). It was my first trip to a multi-level city, where every destination required a climb up steep roads or long stairs. While it made for beautiful scenery, it also made for aching quads and a burning desire to quit smoking immediately.
While not teaching, I wandered Lausanne (up down, up down). Every time I thought I’d seen the city I’d climb a new hill or set of stairs and find a whole new area to explore. I found several great shops and hangout spots. One of the nights I had dinner with my client at the fabulous Café-Restaurant du Vieux Lausanne, an excellent restaurant featuring an assortment of fresh game.
On the final day of class I caught the train after work towards Geneva. The plan was to take a regional train to Coppet and catch another train from there to Pont-Céard, but it didn’t quite work out that way. When I arrived in Coppet there were two other trains and no sign explaining their destinations. They left before I could ask anyone and I was stranded at the Coppet train station. I called my cousin in Versoix who came to rescue me after a bit.
After dinner with my family he took me to the Mövenpick hotel near the airport so I wouldn’t have to take a long train ride early in the morning.
My flight left the next morning, a long drawn out flight behind a man who obviously forgot to wear deodorant for a few days running. But hey, no flight is perfect, right?
Training Overseas
When I told my family where I was going, one of the first things they asked was “Why fly you all the way from the USA?” Why indeed?
Sure, Oracle has offices in Europe, and of course there are some great Oracle minds in Europe as well. However, I think that an American DBA Consultant has two major things going for them:
Client volume. I don’t have any numbers on American vs. European Oracle clients, but I’m pretty sure that Oracle enjoys heavier use in the USA. What’s more, the niches of these Oracle shops are many and varied: military, banking, internet, media, software, hardware, etc. I’ve taught classes for clients who design video games, clients who design and write microcode for enterprise storage arrays, clients with some of the largest and most active databases out there, and many more. While Oracle is used all over the world, it has its roots here in the USA, and it has the client list to prove it.
Dollar value. Right now the value of the dollar makes a US consultant very appealing. What used to be pricey is now affordable due to exchange rate inequities.
The combination of broad expertise and low cost make it a win-win for everyone.
Dine In or Delivery?
Burleson Consulting specializes in onsite training; this means I fly to you and teach in your shop. In some situations I’ve had clients who book a hotel conference room for a classroom. But why go with that as opposed to in-class training from Oracle or another provider?
First off, I will say this: I have worked as a Sr. Instructor for Oracle University. It was a fun job and I got to work with some great people who taught Oracle daily to dozens of students. Oracle U. is a great place and good for foundation training.
One reason to go with on-site training is cost. I’ve had classes ranging from four students to twenty. Some may think it’s better to cram as many people as possible into a classroom, but it really depends on what you hope to get out of the class. More students means less time for real one-on-one time and an in-depth experience. Too few students can mean you fly through too quickly as there are not enough questions to keep the class moving. Even with only four students, you can’t beat the price.
Our 3 day RAC course is $9,000 for up to 20 students plus travel expenses for the instructor. The 5 day Oracle University course is $3,750 per student plus travel expenses for each student. Send four students to class and you’re talking $15,000 plus expenses for four. Send 10 students and you’re topping $40k with expenses, which could have bought roughly 4 in-house Burleson Consulting courses. For that price, you could even reward your DBAs with a cruise on which they can learn Oracle!
Another reason for on-site training is comfort. Just like a dining experience you have two choices: dine in or delivery? The pros and cons are much the same as well:
Dine In - You travel to an Oracle University near you and get the authentic Oracle experience. The white wire-bound notebook, the Oracle Corporation slide deck, the Oracle Instructor who delivers the course and administers the labs. The course you take will be written by dedicated courseware developers and will cover all of the topics necessary to gain a foundation in your chosen topic. Labs will be pre-created and ready to go to let you try what you’ve learned. However, as with dining there are some negatives as well:
You don’t know who your waiter will be. Let’s face it, going to class is luck of the draw. You will get an instructor that has been chosen to teach the class, not chosen specifically for your needs.
You don’t know the crowd. The rest of the class may not care about the things you do. In a packed class of 20 or more it is very easy for the class to get side-tracked on something that doesn’t help you, or for your question to get lost in a flurry of other questions.
Custom requests may not be feasible. Sometimes it’s difficult getting questions answered in a mixed classroom environment. Your instructor, while knowledgeable, may not have seen the situations you have. As such, they may not be able to answer your questions. Additionally it is not possible to go into long drawn-out scenarios or problems in a class.
Delivery - With a class delivered to your company door, you get a class made for you. The instructor (that’s me) is there for the needs of your company and no others. The course can shift and vary, going in the direction that is most important to you. No matter how large the question, no matter how drawn out the explanation, no matter what the scenario, you’re the one and only client and therefore you’ll get results.
One huge benefit of going with a company well known for both consulting and training is that you get an instructor who has been in the field. Many times when I’m asked a question in class, I can say “You know, I saw that same thing just last week.” We show up to class with fresh war stories, a plethora of advice, and a broad perspective. I’d much rather hear an instructor say “Oh, I had that problem and did this to fix it” than “A student of mine had that problem. I wonder how he’s doing?”
I honestly love the combination. As a consultant I get the best of both worlds: consulting in a wide variety of databases, and training with dedicated clients. Sometimes the two meet: mentoring is a huge part of my consulting practice (it is horrible when a consultant leaves a client wondering how something works), and Q&A and problem solving are part and parcel of the classroom experience. And in the end, it is the client who reaps the benefits.
Yet another benefit is that you get to choose your instructor. Instead of choosing a course and date, you choose the options that work best for you. Instead of luck of the draw, you get an instructor and a course that works exactly how you need it to work. When you have a mission critical project coming up or employees that must be trained to work on your environment, having these choices can be the difference between a great project and a failure. If you really like your instructor, you can hire them for consulting too (hint hint)!
Conclusion
The trip to Switzerland was a blast. I got to see Geneva and Lausanne and have a great time in both.
When it comes to training you have options, even with an ocean between you and your instructor. It’s a good idea to weigh your options carefully.
Remember YMMV - Your Mileage May Vary. Some people work better in a dedicated classroom environment, some work better in a familiar environment like their workplace. Both individuals and companies play a part in the decision making.
I need to learn more French for next time I head to southwestern Switzerland.
Thanks for reading, and I hope you’re enjoying the holidays!
Hello everyone! I’m writing from Lausanne, Switzerland where I am teaching a three day RAC course. Geneva and Lausanne are amazing places, highly recommended if you are traveling to Europe…great food, fun sights, and a huge array of international travelers.
This is my second trip to Europe this year (the other was to Florence for a RAC class), and I’m hoping this is indicative of a higher demand for training from Burleson Consulting overseas. International travel is a great perk for a DBA.
Once I get home I’ll be posting pictures and a better blog entry.
In the Steve Karam Philosophy, every DBA deserves a few things:
Access to Metalink
Loads of good coffee
At least two computers
A yearly cruise on which they can learn Oracle
There’s only one thing better than an Oracle class, and that’s an Oracle class taught by top notch instructors aboard a cruise ship sailing the western Caribbean. I’ve been on two such cruises, and they’re getting better every year.
Last year, I taught aboard the Carnival Glory in a three day RAC at Sea blitz. This year I was joined by John Garmany and Don Burleson in our Oracle Tuning TechBlast at Sea aboard the Royal Caribbean Liberty of the Seas, where we explored many advanced Oracle topics: everything from object storage to backups to the optimizer.
In March of 2009, we’ll be setting sail again with the Oracle TechBlast at sea on the new Independence of the Seas, a veritable floating city. One day of class will be held in the Ft. Lauderdale Grande Hotel and Yacht Club, with the two remaining days being at-sea days aboard the ship. Stops this year include Belize, Costa Maya, and Cozumel, with no class on port days! Topics include Oracle RAC, Tuning, Streams, Dataguard, and more.
It’s a great opportunity and a lot of fun. Bring your friends, your family, and enjoy joining a group of fellow Oracle professionals in one of the best training opportunities out there.
For those of you who know me, you know that I like to keep an air of professionalism both in my work and in my online communications. However, this was just too good to pass up…The Register has given us the best double entendre I’ve ever seen in an Oracle related article:
A colleague sent me an interesting note today. Specifically, Metalink Note 578455.1, which announces that Oracle 12g will not support raw volumes for Datafiles, controlfiles, redo logs, OR voting disks and OCR.
Those of us acquainted with RAC might be shouting “WHAT?!?!” at about this time. This is because without raw volumes, our only choices for OCR and voting disk placement seems to be a CFS (such as OCFS).
However, wording in the Metalink note indicates that ASM will be able to support the OCR and voting disk (”and other database files”) in 12g. The note hints at broader support for NFS in 12g as well.
So I was reading through some of my favorite xkcd comics, and I decided it might be fun to make my own comics about Oracle.
And so I’d like to introduce The Adventures of Ace, DBA. Ace is a Jr. DBA trying to make it in a tough corporate world. The comic is about his trials and tribulations, imaginings, and other fun experiences as a highly stressed DBA.
Bugs come in all shapes and sizes. Big bugs, little bugs, bugs that crash your database, bugs that make a query run oddly. Some believe there are even bugs in human existence.
There is a running joke that has been around forever regarding bugs vs. features. I think this picture explains it better than I ever could (artist unknown):
My Bug Story
So I was working with a client running Oracle 10.2.0.1 on Windows 2003. He informed me that he was having trouble getting DB Control to work. I logged in, took a look, and saw that even though the EM service started, and ‘emctl status dbconsole’ returned successfully, the page was not viewable.
I went through the usual litany. I checked out listeners, looked at IE security settings, made sure it was using a static IP instead of DHCP, tried to telnet to the port, no help at all. I re-configured using EMCA a couple times with different settings, and still, no EM.
Suffice it to say, this was a bit unusual. And so I turned to Metalink. The search terms were tough (’em no workie’?), but I stumbled upon this gem:
Note: 342916.1 Subject: Problem: Startup: Db Control 10.2.0.1 Fails To Start when the hostname begins with the letter “u”
No, I’m not kidding.
It turns out that there is a problem with backslashes in the file “$ORACLE_HOME\host_SID\sysman\config\emd.properties” when using a hostname beginning with the letter “U” for omsRecvDir . To fix the issue, all I had to do was configure EM (emca -config dbcontrol db), wait for the error that the service could not start, and change emd.properties, substituting forward slashes (’/') for backslashes (’\') in the omsRecvDir variable.
And behold, EM started and worked fine!
If you have any funny bug stories of your own, please feel free to share.
Let’s take a trip to the past…we weren’t actually there, but we can pretend. Let’s look into a conversation between a DBA, developers, and management at some time ‘way back when.’
Management: Our system has been performing poorly for months now. Developers: We didn’t change anything. DBA: I didn’t change anything. Management: We need a fix. Developers: We don’t have time to go through all our code and tune it. DBA: *frantically types: alter system set optimizer_index_caching = 35; alter system set optimizer_index_cost_adj = 25;* Fixed!
Fast forward…
Queries are not performing as expected. Sometimes a query will run fine, sometimes it runs horribly. Sometimes a combination of queries and users results in massive buffer busy waits or ‘latch: cache buffers chains’ waits. Nothing is working and so finally, a consultant is called.
You’ve probably guessed that I was the consultant. Move to the head of the class! In particular, there were three or four main offenders; queries that would sometimes perform well, then all of a sudden ‘turn horrible.’ The core of the problem was truly unstable execution plans.
Using ASH I was able to go through different times of day and compare reports from times where the system performed well with times the system performed horribly. Doing so allowed me to compare SQL_IDs between different times of day to see which queries seemed to be going through dramatic changes. Once a SQL_ID was found that experienced such a dramatic change, I used DBMS_XPLAN.DISPLAY_AWR to find all the different iterations of the query.
In case you’ve never used it, DBMS_XPLAN.DISPLAY_AWR is very useful. It allows you to input only a SQL_ID, and Oracle will show you the explain plans for that SQL_ID as recorded in the AWR. For instance:
select * from TABLE(dbms_xplan.display_awr(’95t7cmj84u4jz’));
The next step was to look through the results to find any inconsistencies large enough to result in huge differences in performance. In this situation, I found that ‘good’ explain plans were using decent indexes, NESTED LOOPs, and low overall bytes. The ‘bad’ explain plans were using index joins, where an index fast full scan would join with an index range scan into a view (e.g. index$_join$_003). These plans resulted in many more block touches and much more CPU used.
Using Grid Control, I looked at the timeline for each of the hash values of the SQL_ID and saw that the poorly performing plans lined up with their bad performance periods.
Of course, now the burning question: what can be done about this?
One solution is to set the _index_join_enabled parameter to FALSE, disabling the index joins completely. Based on query analysis it appears that for right now, the pros will far outweigh the cons. But what about queries where index joins perform more optimally? What if Oracle replaces the index joins with something even worse?
The better solution is to negate the need for OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING to be set to such extreme values (or to be set at all). Though these parameters can be very useful in the hands of a skilled DBA, they can also be very dangerous if improperly used. In this case, the CBO never would have chosen to use index joins if the OPTIMIZER_INDEX_COST_ADJ weren’t set so extremely low.
For testing purposes I took one of the problem queries and ran it with OPTIMIZER_INDEX_COST_ADJ = 100 and OPTIMIZER_INDEX_CACHING = 0. The query performed no index joins, but it did perform two full table scans and overall the results were less than satisfactory. Without those two parameters, the queries remained ‘unfixed’ as far as runtime is concerned.
So in the end, I have struck a compromise with the client. If another period of poor performance occurs, we can attempt setting “_index_join_enabled” to false to try to band-aid the band-aid of their performance problems. However (and that’s a big however), they need to take the time to work on a more permanent solution. For instance, using development to test and tune queries with OPTIMIZER_INDEX_CACHING = 0 and OPTIMIZER_INDEX_COST_ADJ = 100 (or at least a less extreme value).
Otherwise it’s a band-aid…then another…then another…until the true performance issue is so obfuscated it may never be found.
I’ve worked with another client that does somewhat the same thing. If they use a resource manager plan, their system will not become overloaded and things seem to work very well. Turn off the plan and the runqueue goes through the roof (in the hundreds) and cache buffers chains latches abound. Unfortunately whenever a problem happens, all one sees taking DB time is ‘resmgr: cpu quantum’. For them, throttling is a fix. I’d prefer a full tuning expedition that fixes the actual cache buffers chains issue.
Conclusions
There is nothing wrong with using OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ. However it is important to find a happy medium in your settings, and to know exactly what you are doing.
There is nothing wrong with using a resource manager plan. But are you doing it for balance or because your system is horrible without it?
_index_join_enabled might be able to negate one of the negative effects of an extremely low OPTIMIZER_INDEX_COST_ADJ setting
DBMS_XPLAN.DISPLAY_AWR helps find situations where the same query might result in drastically different explain plans
If you can get it approved, it is always best to follow up a quick fix with a more comprehensive solution (such as tuning the actual queries directly).
Good luck getting it approved. Instant gratification is hard for a company with poor performance to turn down. Broad scale query tuning and redesigns can be hard to get approved depending on the budget
I’m one of those types that always needs a project. If I run out of projects using the things I know, I take on a project that involves something I don’t know whatsoever.
For instance, building a filesystem in C that connects to Oracle and uses queries in order to create inode structures.
It’s not done by a long shot; hell, it’s not even presentable, but I’m still kind of proud of it since:
I didn’t know C when I started it
It’s a neat concept
If I can remember how I did it, it could be an interesting piece of software
I won’t pretend that I know how to do complex filesystem work. I did have some help.
FUSE - Filesystems In Userspace
The main component (other than C itself and the OCI) is a software package called FUSE. Basically it is an interface to the kernel that allows user created pass-through filesystems. Instead of creating a filesystem that maps only to disk, you can create directory structures, files, etc. that map to anything you can imagine. I have seen a Wikipedia Filesystem, a Zip Filesystem, and several others that are very interesting. One in particular struck me as a good idea: DBToy from thesaguaros.com. DBToy is a database browser for use with MySQL or Postgres. Using that tool as a guide, I was able to hack out a version for Oracle.
Example Usage
[root@615349 orafs]# ls -ltr
total 148
-rw-r--r-- 1 oracle dba 1542 Sep 23 2007 orafs.h
drwxr-xr-x 2 root root 4096 Sep 23 2007 old
-rw-r--r-- 1 oracle dba 804 Jun 15 00:13 makeit
-rw-r--r-- 1 oracle dba 5824 Jun 15 00:17 orafs.c
drwxr-xr-x 2 root root 4096 Jun 15 00:19 mount
drwxr-xr-x 5 root root 4096 Jun 15 01:21 backup
-rw-r--r-- 1 oracle dba 14070 Jun 15 01:21 ora_driver.c
-rw-r--r-- 1 root root 15084 Jun 15 01:21 ora_driver.o
-rw-r--r-- 1 root root 13972 Jun 15 01:21 orafs.o
-rwxr-xr-x 1 root root 29612 Jun 15 01:21 orafs
[root@615349 orafs]# ls -ltr mountdir
total 0
[root@615349 orafs]# ./orafs mountdir
[root@615349 orafs]# cd mountdir
[root@615349 mountdir]# ls -ltr
total 0
drwxr-xr-x 2 root root 0 Dec 31 1969 TSMSYS
drwxr-xr-x 2 root root 0 Dec 31 1969 SCOTT
drwxr-xr-x 2 root root 0 Dec 31 1969 OUTLN
drwxr-xr-x 2 root root 0 Dec 31 1969 HR
drwxr-xr-x 2 root root 0 Dec 31 1969 FLOWS_FILES
drwxr-xr-x 2 root root 0 Dec 31 1969 FLOWS_020100
drwxr-xr-x 2 root root 0 Dec 31 1969 DIP
drwxr-xr-x 2 root root 0 Dec 31 1969 DBSNMP
drwxr-xr-x 2 root root 0 Dec 31 1969 CTXSYS
drwxr-xr-x 2 root root 0 Dec 31 1969 ANONYMOUS
[root@615349 mountdir]# cd HR
[root@615349 HR]# ls -ltr
total 0
drwxr-xr-x 2 root root 0 Dec 31 1969 REGIONS
drwxr-xr-x 2 root root 0 Dec 31 1969 LOCATIONS
drwxr-xr-x 2 root root 0 Dec 31 1969 JOBS
drwxr-xr-x 2 root root 0 Dec 31 1969 JOB_HISTORY
drwxr-xr-x 2 root root 0 Dec 31 1969 EMPLOYEES
drwxr-xr-x 2 root root 0 Dec 31 1969 DEPARTMENTS
drwxr-xr-x 2 root root 0 Dec 31 1969 COUNTRIES
drwxr-xr-x 2 root root 0 Dec 31 1969 CDEMO81_EMP
drwxr-xr-x 2 root root 0 Dec 31 1969 CDEMO81_DEPT
drwxr-xr-x 2 root root 0 Dec 31 1969 CAR
[root@615349 HR]# cd EMPLOYEES
[root@615349 EMPLOYEES]# ls -ltr
total 0
-r--r--r-- 1 root root 0 Dec 31 1969 data.csv
[root@615349 EMPLOYEES]# cd ../COUNTRIES
[root@615349 COUNTRIES]# ls -ltr
total 0
-r--r--r-- 1 root root 0 Dec 31 1969 data.csv
[root@615349 COUNTRIES]# cd ../../..
[root@615349 orafs]# umount mountdir
But What Did It Do?
When I ran the orafs program, two things happened:
A connection to Oracle was made as SYSTEM
A FUSE filesystem was mounted on the mountdir directory using API calls
This allowed me to cd to the mountdir directory and use standard OS commands like ls -ltr. The first level directory is schema names. In each schema directory are directories for each table. In each table directory is a file called data.csv. It doesn’t actually contain anything yet, but hey, it’s a start. Just being able to navigate through Oracle’s data dictionary at the OS level is neat enough.
Also notice that to remove it, I had to use the umount command. This isn’t a fake mounting of any kind, it truly did get mounted at the OS level!
Nothing On Disk
One of the coolest features of FUSE is that nothing had to be written at all to disk. The data from the data dictionary was not written to disk at any time. The orafs program used no flat files or anything of the sort. When ls -ltr is called, it actually did a query on the fly. For instance, select table_name from dba_tables where owner = ?. My code passes results to FUSE, and FUSE makes it understandable to the OS.
Great, But Why?
Yeah, that’s the big question. The reason I even did it in the first place was that I thought it would be a neat concept. Then it was a challenge. Now it’s something for which I have absolutely no time!
But if I had things my way, here’s what I’d love to do with it:
Have 3 files in each table subdirectory: data.xml, data.csv, and ddl.sql. The data.xml and data.csv files would have all of the rows from the table arranged in the proper format. This table would not actually be queried until you opened the file in some way. Imagine, simply doing cp data.csv /home/oracle/table.csv to get a comma delimited dump of your data! ddl.sql would, of course, contain the DDL to regenerate the object.
Create symbolic links to child tables based on constraints
Use the creation date from DBA_OBJECTS and DBA_USERS to set the dates when browsing the directory
Allow command line args for login/password instead of hardcoding. I had this at one point but got rid of it due to coding issues
Figure out how to use malloc() and calloc() properly so I don’t waste memory
It’s a big list that I’ll probably never get through; however, it doesn’t hurt to have goals.
In fact, imagine some other uses for FUSE with Oracle (maybe I’ll tackle them one day):
Mount an ASM diskgroup, allowing ASMCMD-like commands for browsing, but also allowing cp commands to a normal filesystem using RMAN commands in the code
Mount a table that contains a BLOB and a column containing the file name of each row. You could actually see the BLOBs on disk with the proper filename, all dynamic from the DB without duplicating space usage
I may actually try to learn some of the FUSE bindings I’ve seen. FUSE-J for instance is a Java binding, which would probably make the coding MUCH easier. However, the thought of rewriting all the code I’ve done so far for orafs makes my head hurt.
Anyone good with C and OCI that would like to see the code? I’d be happy to share, maybe someone else can do something with it. After you fix all my horrible code, of course.
, a graphic novel about database technology.
