– Downloading Patches From My Oracle Support

November 12, 2013 1 comment

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 that can be used to download patches directly to the server using only the patch number. Read more…

Mining the AWR to Identify Performance Trends

November 4, 2013 2 comments

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?

Read more…

There’s Always Another Bug Hiding Just Around the Corner

August 15, 2013 Leave a comment

This post originally appeared at the Pythian blog here.
We were using a 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. Read more…

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. Read more…

Categories: Tips Tags: , , , ,

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. Read more…

Performance Settings of Concurrent Managers

April 30, 2013 Leave a comment

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. Read more…

The Internal Workflow of e-Business Suite Concurrent Manager Process

March 22, 2013 Leave a comment

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!

Read more…


Get every new post delivered to your Inbox.