getMOSPatch.sh – Downloading Patches From My Oracle Support

This post originally appeared at the Pythian blog here.
How to download patches from My Oracle Support (MOS) directly to the server? This has bothered me since the ftp access was closed a few years ago. Of course, I’ve been given some options by Oracle, like, I could access MOS from the server using a browser (probably from a VNC desktop – thank you very much), or I could look up the patches on my workstation to download the WGET script from MOS, which I uploaded to the server, adjusted with the username and the password of my MOS account and then started the downloads. Not too convenient, is it?
Then, back in 2009 my teammate John published a blog post on Retrieving Oracle patches with wget. This eliminated the need to upload the wget script from MOS to the server and I only had to get the URLs of the patches and pass them to a shell function. While this was so much easier, I still needed to open the browser to find those URLs.
I think it’s time to get rid of browser dependency. So I’ve written a shell script getMOSPatch.sh that can be used to download patches directly to the server using only the patch number. Continue reading

Mining the AWR to Identify Performance Trends

This post originally appeared at the Pythian blog here.
Sometimes it’s useful to check how performance of a SQL statement changes over time. The diagnostic pack features provide some really useful information to answer these questions. The data is there, but it not always easy to retrieve it, especially if you want to see how the performance changes over time. I’ve been using three really simple scripts to retrieve this information from the AWR. These scripts help me answer the following questions:

  • How does the performance of a particular SQL change over time?
  • How do wait times of a particular wait event change over time?
  • How does a particular statistic change over time?

Continue reading

There’s Always Another Bug Hiding Just Around the Corner

This post originally appeared at the Pythian blog here.
We were using a 10.2.0.3 database, and it had been running without any issues for several years. What could possibly go wrong? Anything! Suddenly, we started getting “ORA-07445: exception encountered: core dump [qercoStart()+156] [SIGSEGV] [Address not mapped to object] ” a few times a minute in the alert log. A closer investigation revealed that one of the popular SQLs in the application couldn’t complete anymore. It looked like a bug, since only the SQL was failing. Continue reading

Which Partitions Does the SQL Statement Access?

This is a short blog post on how one can prove that a particular partition of the table or index is accessed by a specific query. Continue reading

Be Warned: cmclean.sql is Dangerous!

This post originally appeared at Pythian blog here.

I’m sure one of the most popular scripts for Apps DBAs on My Oracle Support is cmclean.sql from MOS Article ID 134007.1 “Concurrent Processing – CMCLEAN.SQL – Non Destructive Script to Clean Concurrent Manager Tables”. DBAs usually use the script to clean up stale data from concurrent processing tables (FND_CONCURRENT_%) after incidents like a crash of the database or concurrent processing node. This script sets correct completion phase and status codes for terminated concurrent requests and sets correct control codes for terminated concurrent manager processes. Despite the assuring “Non Destructive” claim in the title of the MOS Article there is a possibility to lose concurrent request schedules when cmclean.sql is executed. Continue reading

Performance Settings of Concurrent Managers

This post originally appeared at the Pythian blog here.

This is the second article in a series about internals and performance of concurrent managers. In this post, we’ll take a look at three important settings that affect the performance of the concurrent managers: number of processes, “sleep seconds”, and “cache size”.  This article might be a bit on the theoretical side, but it should provide a good understanding of how these settings actually affect the behavior and performance of concurrent managers. Most of the statements in this article build off of information from my previous post:  The Internal Workflow of e-Business Suite Concurrent Manager Process. It may be helpful to take a look at it before continuing with this one.

Life cycle of a Concurrent Request

The interesting thing about tuning concurrent managers is the fact that we don’t tune a particular query or a running process, but we actually tune the pending time of concurrent requests. The goal of the tuning is to make sure concurrent requests start executing soon enough after the time they have been scheduled for. Let’s take a look at the life cycle of a concurrent request:

The Life cycle of a Concurrent Request (CR)

The Life cycle of a Concurrent Request (CR)

Based on the diagram above, the pending time of the request is the interval between the time the request was scheduled to start and the time it actually started. This time can be split in two parts:

  1. Pending for Conflict Resolution Manager (CRM) – Here the CRM checks the incompatibility rules effective for the pending concurrent request against other running requests. The CRM allows the request to execute only when all incompatible requests have completed.
  2. Pending for Concurrent Manager (CM) – This is the time spent waiting for an available concurrent manager process. It also includes the time the CM process takes to fetch the request from FND_CONCURRENT_REQUESTS table and to start executing it. “Pending for CM” is the interval that can be tuned by altering the number of manager processes, “sleep seconds” and the “cache size” settings. Continue reading

The Internal Workflow of e-Business Suite Concurrent Manager Process

This post originally appeared at the Pythian blog here.

Concurrent processing is one of the key elements of any e-Business Suite system. It provides scheduling and queueing functionality for background jobs and it’s used by most of the applications modules. As many things depend on concurrent processing it’s important to make sure the configuration is tuned for your requirements and hardware specification.

This is the first article in a series about performance of concurrent processing. We’ll take a closer look at the internals of concurrent managers, the settings that affect their performance and the ways of diagnosing performance and configuration issues. Today we’ll start with an overview of the internal workflow of a concurrent manager process. Enjoy the reading!

Continue reading

The Easy Way of Finding Similar SQL Statements

This post originally appeared at the Pythian blog here.

As we all know proper use of bind variables in SQL statements is a must to make transaction processing applications scalable. So how do we find the queries that don’t use bind variables and have to be parsed each time they are executed? There is number of ways, but this article is all about the most effective way I know. If you have a better one – let me know please! Continue reading

R12.2 online patching – What are the hidden costs?

This post originally appeared at the Pythian blog here.

One of the hot topics at the UKOUG 2011 Technology and E-Business Suite Conference last December was the upcoming release of Oracle e-Business Suite R12.2. The new release will bring us lots of new features, usability improvements and new versions of technology stack components (Oracle Database 11g R2 and Oracle Fusion Middleware 11g R1 as the application server), but the most important and impressive new feature of course will be online patching. Online patching is supposed to change the game completely. All owners of E-Business Suite environments know that patching requires downtime. Although it can be reduced with various techniques (e.g. staged APPL_TOP), some downtime is still required to apply a number of changes. Online patching will not eliminate downtime completely, but will reduce it significantly by using  “Edition Based Redefinition” (EBR) at the database level and using a secondary applications file system for online patching. In fact, all patching activity will be an online operation; downtime will be required only to switch from one version to another. Continue reading

Back to School: Elementary Physics for DBAs

This post originally appeared at the Pythian blog here.

“Hello World!” I guess that’s the most appropriate way to start my 1st blog post under pythian.com domain. I’m going to start slow, but hopefully will pick up speed and have at least couple of posts each month to share with you. I’ve been blogging at http://appsdbalife.wordpress.com until now and I haven’t decided yet what the future will be for my previous blog, I wouldn’t like it to become some kind of a zombie page that’s been long dead but still wandering around the internet world.
Enough intros, let’s get to business! I hope this blog post doesn’t get lost in the huge amount of posts related to OOW 2011.

A few days ago I was asked to estimate how much space needed to be added to the ASM diskgroup to handle the database growth for one year without additional need of adding disks. Obviously, to estimate the disk space that needed to be added I had to know what the DB size will be in one year from now. It looked like an easy task as I knew we were recording the size of the database every day. Continue reading

Follow

Get every new post delivered to your Inbox.