About once a week, our team gets a request from the server admins to provide some information on why a database server is running slowly. This typically begins a painstaking process of finding the main processes on the OS side and tracing them back to database sessions (and possibly even to Oracle Apps Concurrent Requests).

As we all know, writing SQL to do this tie-back-when-the-issue-is-happening is not a good practice — there is always the chance to mis-type something or to forget a join and have the data come back as unusable for good decision-making. Not to mention that there is usually a time constraint involved when diagnosing high server load, etc.

We started fairly small with a solution to our problem and simply joined the v$process view to the v$session to get the OS process ID tied out to a database session. Once you have that information from v$session, you can start to going in other views such as v$sort_usage, v$sql, v$session_wait, v$transaction, and so on. Some of these additional views may have relevant information for your specific issue.

We had an additional issue in our case — the databases that sit on our servers run Oracle Applications, so simply tying to a database session provides only half of the picture. You also need to be able to pull information from the fnd_concurrent_requests table to be able to see if the OS process may be generated from a Concurrent Request within Oracle Apps.

Attached to this post (see bottom for link) is a general diagram that the team came up with for relating our tables together, taking RAC into consideration. (more…)

I decided to reprise my commentary on Oracle RAC and the gv$ views after reading Patrick’s comments on my previous post. It is always encouraging to know that someone is kind enough to read your work and provide insightful feedback - many thanks to him!

I can use a script now to find the locks in a RAC environment, but until this point I couldn’t have told you how the script actually works. Frankly, the documentation that I found on Metalink is dry and boring for such an important (and sometimes entertaining) subject as locks.

There are two questions that I wanted to answer here: Can you use the gv$ views with a non-RAC environment? What do the WHERE clauses in a good block-checking script do?

First, can you use the gv$ views to check for locks when you have a single-instance, non-RAC database? The reason this question is prevalent in my mind is that we just completed an 11.5.9 application clone (with RAC enabled on the source environment but not on the target) for a customer who has been busy purging data from the new environment. When a performance issue arose, one of the first things that we did was to see if there were any locks. We employed the same script that had been developed to tell us if there were locks on our RAC-enabled instances — and the script returned no records. At the time, I thought that perhaps the gv$ views would not be populated in a non-RAC database. I tested this by executing the following SQL statements on the non-RAC database:

select sid, id1, id2 from v$lock minus select sid, id1, id2 from gv$lock;

select sid, serial# from v$session minus select sid, serial# from gv$session;

(more…)

According to the wikipedia page,

“The rack is a medieval torture . . . which induces excruciating pain as the victim’s joints slowly dislocate.”

Per the Oracle website,

“Oracle RAC is a cluster database with a shared cache architecture that overcomes the limitations of traditional shared-nothing and shared-disk approaches to provide highly scalable and available database solutions for all your business applications.”

Which is more painful, you might ask? I cannot say for certain, as I have never been subjected to the torture of a medieval rack, but I have experienced some pain at the hands of the Oracle RAC. My first encounter was about five months ago when I first became an “official” DBA. Being eager to jump into solving problems in my new job (as that’s what most DBAs do, solve problems), I relished the chance to get my hands dirty and work on a “real” DBA task — a database lock.

Even though I had never been officially titled a DBA before, I was somewhat familiar with the concepts as I have been working around them for years (and still chose to join their ranks, if that tells you anything). Theoretically, I knew exactly what a database lock was, but I had no clue how to practically diagnose or kill one off.

Checking with a few knowledgeable co-workers, I was directed to a set of common database diagnostic scripts affectionately known as the “Pythian Kit”. (more…)

I’ll preface this post with a note that the story itself is not really work- or DBA-related, but the lessons learned certainly are. I consider myself fairly conscientious when it comes to internet security and backing up my most important files (pictures of kids, music, etc), and I am diligent about taking at least a weekly backup of those files so that if (when) something catastrophic happens, I am ready. Once again, as my short life as a DBA has proven, theory and practicality rarely meet.

Monday night, I needed a stress reliever. My family and I recently moved to Ottawa from Wichita and it has been a five-month-long ordeal. I used to be an avid gamer, but with changing jobs, selling the house, moving, family issues, etc., I haven’t had a lot of time to kick back and relax. I decided to visit one of my favorite websites, www.armchairgeneral.com, to see if they had any good reviews of new games to play. One in particular caught my eye, called “Mount and Blade”. Looks like an interesting twist on your standard medieval-based RPG — the combat is in first person. The game is still in beta, but can be downloaded by anyone wanting to play it. So I downloaded it from a link on the game’s website that pointed me to CNet.

Not the wisest decision in my life, as about 10 minutes later my virus scan software (AVG) warned of viruses and Trojan horses. I quarantined everything that it found, but it wasn’t enough — I was officially infected for the first time in four years. I tried for an hour that night to undo the damage, but didn’t have much success. This bug was nasty — it even went so far as to detect that I tried to go back to a previous System Restore Point and it erased them. My colleagues at Pythian took an immediate interest in my dilemma and suggested several helpful tools (like Process Explorer) that I also tried with limited success.

(more…)

If you check out the “Butterfly Effect” on Wikipedia, you’ll find a rather interesting reference to, “sensitive dependence on initial conditions in chaos theory.” Fascinating use of phrase that probably doesn’t mean much to normal people until it happens to you. I could give you lots of theoretical examples, but perhaps a real-life one will make more sense.

Last week, a client of Pythian’s came to us with an environment that had recently been upgraded from Oracle Applications 11.5.9 to 11.5.10. In the past, Pythian has not supported the Oracle Applications environment for this client, but that is one of the strengths of Pythian — we have DBAs with a broad range of knowledge and expertise to support just about anything thrown at us. The emails from the client users suggested a myriad of performance issues, ranging from forms being slow to POs not being processed.

I can sympathize with the client, as I used to be one (that’s how I came to work for Pythian — but it’s also another story that I shall relate sometime). Performance issues in Applications can be tricky at best, as there are so many diverse factors to consider. Not having supported Oracle Apps for this client before means that we were starting with a clean slate and just looking for things out of the ordinary based on experience with other clients, sort of “searching for a needle in a haystack.”

The first thing was to just login to the application and take a quick walk about the area — did anything stand out? What was the first impression? It took a bit of time to get connected initially, but the forms seemed to come up without any undue issues. One of the early comments in the email we had received mentioned that POs weren’t being processed by the workflows. Maybe a trip to “View Requests” was in order . . .

(more…)