Feed aggregator

Training Class (Day 3)

Eric S. Emrick - Tue, 2006-08-22 20:36
Another day of training is in the books. What was on today's menu?
  1. Tuning the Shared Pool.
  2. Tuning the Buffer Cache.
  3. Automatic Shared Memory Management.
  4. Checkpoint and Redo Tuning.

Apparently, Oracle is migrating some of its serialization protection from latches to mutexes. For example, the structures previously protected by the Library Cache Pin latch are now protected by a mutex and evidenced by the cursor:pin S wait event. Actually there are several new mutexes and mutex related wait events new to 10g. For example:

- cursor:mutex indicates mutex waits on parent cursor operations and statistic block operations.

- cursor:pin events are waits for cursor pin operations (library cache pin now protected by mutex).

There are a couple interesting facts about Oracle and mutexes. A mutex get is about 30-35 instructions, compared to 150-200 instructions for a latch get. Also, a mutex is around 16 bytes in size, compared to 112 bytes for a latch in Release 10.2 (in prior releases, it was 200 bytes).

One of the appeals of the mutex, per the documentation, is the reduced potential for false contention. That is, a mutex can protect a single structure; often times stored with the structure it protects. However, latches often protect many structures (see cache buffers chain latch) and can yield what the documentation calls false contention. It is called false contention because "the contention is for the protection mechanism rather than the target object you are attempting to access." This all sounds really great, right? Well, maybe. If Oracle goes to more widespread use of mutexes instead of latches to protect target objects that would be a boatload more mutexes. I am sure the porters at Oracle are not intending to use mutexes exclusively in the future. But, I can see where contention in Oracle could be dramatically reduced at the cost of CPU cycles and memory. What would happen if Oracle protected each buffer with a mutex? While each mutex is less expensive with regard to memory and CPU than an individual latch, you will need considerably more mutexes for each replaced latch. 50 mutexes used to replace a single latch could run the CPU up considerably for the "same" application workload.

I have one final note on mutexes. As of version a SELECT against V$SQLSTAT and searches of child cursor lists are mutex protected.

I found the Tuning the Buffer Cache discussion somewhat interesting. Unless you have been hiding under a rock the past 4-5 years, I am sure you have heard the Oracle experts preaching the notion that ratios are not very helpful in diagnosing the health of a database. In particular, the buffer cache hit ratio is frequently tagged as meaningless. A smile came to my face when I read the following excerpt from the course material:

"A badly tuned database can still have a hit ratio of 99% or better...hit ratio is only one part in determining tuning performance...hit ratio does not determine whether a database is optimally tuned..."

Oracle is finally teaching what the experts have been saying for years!

I have been to several Hotsos events/training classes. They often talk about the need to include the buffer is pinned count statistic in the tally for logical reads. These operations are simply latch-reduced logical reads. Why doesn't Oracle integrate this information into their course material or documentation set? They still only claim that db block gets and consistent gets constitute logical reads. I monitored a process recently in one of my production environments and noticed the process did 2 buffer is pinned count logical reads for every 1 (db block gets + consistent gets). That is a substantial percentage of work owed to operations not officially categorized as a measure of work by Oracle.

Lastly, the on-topic impromptu discussions were fruitful. That always makes the training session more interesting :)

SAOUG Conference 2006

Andries Hanekom - Thu, 2006-08-10 02:15

Just got some good news, I have been accepted as a speaker at the South African Oracle User Group Conference 2006. I will be presenting my paper - "Oracle Applications Framework Development - The New Frontier", which is an introduction to the world of OAF development, personalization and extension. My presentation will be a 45 minute session scheduled for 9:55 AM Wednesday 27 September, so don’t miss it.

The conference is held at Sun City and runs from the 25th to the 27th of September, if you are interested in attending visit the SAOUG website at: http://www.saoug.co.za

Search Terms

Stephen Booth - Mon, 2006-08-07 16:59
Doug Burns recently posted in his journal a list of common search terms that people have used where his journal appeared in the results. I figured I'd post mine (according to Statcounter.com). Fortunately (or unfortunately) mine are not anywhere near as exciting as his.Perc. Search Term5.80% ora-074454.35% orapwd invalid username password2.90% exception signal: 11 oracle2.90% alter user sys2.90Stephen Boothhttps://plus.google.com/107526053475064059763noreply@blogger.com0

Problem with PUTTY connection to SUSE9

Wijaya Kusumo - Fri, 2006-08-04 06:22
When I tried to connect to SUSE9 via Putty from my windows 2000, for some reasons I kept getting the Access denied message. Since connecting from Cygwin is ok, then the problem should be on the Putty. After searching for a while, here is how I fixed the connection issue: 1. Update my Putty to the latest version: 0.58. 2. Open Putty. Load the Saved Session to the SUSE server. 3. In the Category

Instructive Presentation on Logical I/O Mechanics

Eric S. Emrick - Tue, 2006-08-01 17:40
If a picture says a thousand words than a good animation can say ten thousand. Check out this offering by Julian Dyke. His presentations relating to Oracle mechanics still reign supreme in my book. Once you see a mechanical concept "in motion" you simply don't forget it. What a great didactic device. Anyway, I just wanted to pass this along. Enjoy.

Training Class (Day 2)

Eric S. Emrick - Wed, 2006-07-26 08:38
The second day of training was much better than the first. I suspected it would get better based on the material to be covered. The topic set de jour was:

  1. Metrics, Alerts and Baselines.
  2. Using Statspack.
  3. Using Automatic Workload Repository.
  4. Reactive Tuning.

Having limited exposure to 10g in any true production environment, I found 75% of these topics interesting (Statspack chapter was not valuable to me). I really like what Oracle has accomplished with 10g with regard to the gathering and reporting of statistics and metrics (the rates of changes for given statistics). About 5 years ago I wrote a utility for 9i that allowed me to compare Oracle-captured statistics and wait event durations to similar reference points. This utility, I dubbed AppSnap (written in PL/SQL), captured the statistics and wait event durations each hour and calculated and stored the deltas in a separate tablespace. This permitted me to compare what is considered "typical" load to current load and evaluate the deviations rather quickly. I wrote a Unix shell script reporting tool called Instance Health that reports each hour the deltas as they relate to what I call peer hours. For example, each hour a report is generated as a text file and stored in a log directory. The most previous delta is compared to the same hour of day for the past 30 days, the same hour of day and day of week for the past 12 weeks and against all hours for the past 30 days. This has proved to be very valuable for detecting systemic anomalies after application upgrades, etc.

Okay. Now Oracle has come along with 10g and provides the same functionality (albeit not free). I appreciate the graphical conveyance of this type of analysis provided by Enterprise Manager. Shoot, Oracle even calculates the variance within the sampled timeframe for each metric. This is really cool because you can easily write a query that can ascertain if some metric is statistically anomalous (i.e. +-3 standard deviations). At first glance, some of the AWR reports are not very intuitive. But, the more you stare at them the more sense they appear to make. The Active Session History reporting is also a very nice feature (once again, not free).

If you already have considerable work experience with AWR/ASH/ADDM then this class probably won't provide you much value. The course does go into the mechanics of the data capturing and touches rather superficially on the reporting capabilities. So there is a good chance you probably have more knowledge about these products than this class affords. However, if you are like me and have yet to dig in your heels on a 10g production environment this class could serve as a very nice primer.

Well, I am off to day 3 of this training class.

Oracle Workflow Notifications & OAF

Andries Hanekom - Wed, 2006-07-26 03:39

If you wanted to display HTML formatted content in an Oracle Workflow Notifications before the release of FWK.H you had to use one of the following options:

  • PLSQL Documents - 32K size limit
  • PLSQLCLOB Documents – CLOB

PL/SQL development of HTML notifications that contains tables and complex layouts can be difficult and very time consuming. It requires the developer to have a very good knowledge of HTML and makes the implementation of a standard “look and feel” very difficult.

With the implementation of FWK.H we are able to leverage the power of the OAF to create multifaceted Workflow notifications. Also called JRAD notifications, FWK embedded regions is nothing more then a normal OAF region displayed in the body of a Oracle Workflow notification.

Incorporating an embedded FWK region in a Workflow notification consists of the following steps:

  1. Create an OA Component Region containing the Headers, items and tables you want to display in the body of you notification. Developing and implementing this region is done in the exact same way you would when creating a region for an OAF custom page. Remember to create a dedicated Application Module (AM) for your region that will contain all the View Object (VO) required for your layout.
  2. In core applications, create a SSWA jsp function where the WEB HTML call is: OA.jsp?page=/companyabc/oracle/apps/xx/module /webui/XxRegionNameRN
  3. Create a new Workflow attribute:


XX_PO_DOCUMENT_RN_NTFN is the custom function created in core apps and &PO_HEADER_ID is a workflow attribute I am passing back as a parameter to the region's controller to initialize my VO.

4. Add the new attribute to your message:


Save your workflow and initialize a new instance, the notification body should now be populated by the OAF region. No messy HTML coding, quick, easy and you can reuse the region in your OAF custom pages.

VNI-2002 File operation error when submiting jobs through Enterprise Manager

Neil Jarvis - Tue, 2006-07-25 09:39
If you’ve been able to submit jobs through Enterprise Manager and then find that they are failing with VNI-2002 file operation error check out $OH/network/agent and see if you have inadvertently created a temp directory. I did, to store some files for backup. Either remove this directory or rename it. Then restart your agent and you should find your jobs now run

Training Class (Day 1)

Eric S. Emrick - Mon, 2006-07-24 20:29
As I mentioned in my last post, I am attending an Oracle training class this week. The class is Oracle Database 10g: Performance Tuning. Having been to the Oracle 10g New Features for Database Administrators class last year, I was hoping for a substantially more in-depth look at 10g instance tuning in this class.

The first day was just okay for me. I learned a few things but I felt the class dragged a bit: 30 minutes getting to know each other and too many unrelated tangents or related, yet gratuitous, topic embellishments. I don't mind an occasional anecdotal deviation as it relates to the topic at hand, but those that are completely off topic really slow down the course. You know when you are reading a story and everything seems to flow nicely (proper balance of dialogue and narrative), then you run into a couple pages of narrative? It really puts the brakes on the interest.

I tend to evaluate courses on:
  1. The conveyed knowledge of the instructor.
  2. The presentation of the subject matter.
  3. The quality of the course material.
  4. The value of the impromptu discourse along the way (icing on the cake stuff).

Based on the first day, I feel the instructor has a good command of the subject matter and adds value to the class based on relevant experience. I have been to several Oracle classes where the instructor did nothing more than read the slides verbatim and/or appeared to have little relevant experience. Aside from the introductions, we spent the remainder of the day on two topics: a performance tuning overview and 10g statistics/wait events. The study material that accompanies the course is rather good; I have taken the liberty to skip ahead and get into the meat of the course material. I am looking forward to the class tomorrow as I feel we will be digging our heels in a bit more (or at least I hope).

Given the sparse amount of substantive material covered on the first day, I don't have any really interesting takeaways. I'll give the first day of class a B.

Microsoft and Oracle Work Together

Edward Whalen - Mon, 2006-07-24 15:12

In all the years that I have worked with both Microsoft and Oracle I have never seen so much interoperability and cooperation. This week Oracle announced support for People Tools on SQL Server 2005. See the announcement at http://www.oracle.com/corporate/press/2006_jul/peopletools-848.html. This is just one in many examples of how the interoperability between Oracle and Microsoft products is getting better. Other examples include the ability to integrate Oracle logins with Windows Active Directory and the extensive support of Oracle products on Microsoft Windows.

Oracle was the first database software available for the Windows NT platform and was also the first database software available for the 64-bit editions of the Windows platform. Oracle supports not only their database software, including RAC, on Windows, but also People Tools, Oracle eBusiness Suite software, and the Oracle Fusion Middleware products. Oracle even supports Microsoft clustering with Oracle Fail Safe.

Recently a new type of interactivity has started occurring. Oracle Grid Control monitors the Windows OS, but now does much more. Oracle is now providing Grid Control Plug-ins for the following Microsoft products:

  • Active Directory
  • BizTalk Server
  • Commerce Server
  • IIS
  • Internet Security and Acceleration (ISA)
  • Microsoft .NET Framework
  • Microsoft SQL Server

These Plug-ins allow Oracle Grid Control to monitor and provide alerting functions, so that in the event of a failure, you will be notified via email, pager, etc. Oracle has expanded Grid Control in order to provide a full service management and monitoring utility.

On the other side of the fence, Microsoft does not explicitly create Microsoft Operations Manager (MOM) management packs for non-Microsoft products, but has opened this up to the 3rd party vendors. There are several management packs for Oracle products available today. This allows the extensibility of MOM to be used with Oracle and any OS that you happen to run Oracle on.

This week I happened to be working on my upcoming “SQL Server 2005 Administrator’s Companion” book and was going through the editorial process on replication. With SQL Server 2005 replication, you can replicate data into a SQL Server 2005 database from an Oracle publisher. This allows SQL Server reporting servers and batch servers to get their data directly from Oracle using replication. In addition, it is possible to set up an Oracle subscriber to use a SQL Server publisher, thus using Oracle for reporting and SQL Server for OLTP. It is also possible to perform distributed queries between Oracle and SQL Server databases by configuring the Oracle database as a linked server within SQL Server.

There has always been cooperation between Oracle and Microsoft for using Oracle on the Windows platform, but the management cooperation has just begun. In the next few years you will most likely shop around and decide whether you like the Microsoft tools or the Oracle tools better and then implement that tool throughout your entire enterprise.

How to create the Order Entry schema after a database has been created using scripts

Neil Jarvis - Thu, 2006-07-20 11:30
If you wish to create the oe schema at a later date after creating a database you will have to run the script ‘oe_main.sql’ in $OH/demo/schema/order_entry.

oe_main.sql asks for
1. the OE user's password. The user OE will be create by the script.
2. OE’s default and temporary tablespaces.
3. The HR user password. The HR user has to have been created. So run hr_main.sql in human_resources first. The HR user is created because there are some shared objects between HR and OE.
4. And finally, the SYS password and log_path

The script creates the OE user and grants privileges then creates the objects. Final the subschema is created.

The hr_main.sql in $OH/demo/schema/human_resources asks for
1. The HR password. The user is created here
2. The default and temporary tablespaces
3. The sys password and log_path

Run this script before oe_main.sql

Please Note: This note is applicable for 9i. File locations may vary

Distributed Computing

Stephen Booth - Tue, 2006-07-18 05:02
This morning someone passed me a link to "The Eight Fallacies of Distributed Computing". Looks pretty accurate. I'd say that many of them are doubly true of RAC.Stephen Boothhttps://plus.google.com/107526053475064059763noreply@blogger.com0

OAF and Oracle Workflow

Andries Hanekom - Tue, 2006-07-18 02:52
Most of the custom development work I have done on OAF has required me to interact with Oracle Workflow, specifically to handle approvals and interfaces. The class oracle.apps.fnd.framework.webui.OANavigation provides Java wrappers for Oracle Workflow Engine's PL/SQL APIs.

The class is actually intended for use when creating and managing OA Framework Page Flows that are defined by Oracle Workflow, but can also be used to interface with Oracle eBS Workflows.

Example Code - Launching a custom Oracle eBS Workflow from a OAF page:

import oracle.apps.fnd.framework.webui.OANavigation;
import java.math.BigDecimal;

public void launchCustomWorkFlow(OAPageContext pageContext)
String wfItemType = "CUSTOM";
String wfProcess = "CUSTOM_PROCESS";
String wfItemKey = "1001-1";

OANavigation wfClass = new OANavigation();

// Create Workflow Process
wfClass.createProcess(pageContext, wfItemType, wfProcess, wfItemKey);

// Set Number Attribute: ITEM_INTERFACE_ID
new BigDecimal(1));

// Set Text Attribute: ITEM_NAME
wfClass.setItemAttrText(pageContext, wfItemType, wfItemKey,

// Start Workflow Process
wfClass.startProcess(pageContext, wfItemType, wfProcess, wfItemKey);


Eric S. Emrick - Mon, 2006-07-17 19:19
Well, this weekend I replaced my "archaic" Generation 4 IPod with a snazzy new Generation 5 Video IPod. I chose the black facing this time for a change of pace.

I really like the IPod product - versatile, convenient and just loads of entertainment/educational potential for my ~2 hours of public transit commute each day.

With more and more news and entertainment mediums adding video Podcasts this little device has become a cool way to pass the commuting time. I enjoy reading on my commute, but watching an interesting show or funny movie can pass the time at record clips. I won't be surprised if I ask the engineer to make another loop so I can finish watching my movie before heading into work :)

I wonder when Mr. Kyte will be Podcasting some material...

I'll be in Oracle training next week attending the Oracle Database 10g: Performance Tuning (Database) class. It should be a really good course. I'll be blogging some of the interesting takeaways from the class, so stay "tuned"!

Oracle Certified What?

Stephen Booth - Mon, 2006-07-17 10:20
Just recieved the following email via one of the Oracle DBA lists I'm on:Hello, I am XXX from Singapore.I am also an Oracle Certified Professional(OCP).Could I know size of any database and what's the size of SGA forthat Database and how long it take to complete Logical Backup andPhysical Backup.I will be awiting for ur reply.Thanks in AdvanceRegardsXXXXNames have been removed to protect the Stephen Boothhttps://plus.google.com/107526053475064059763noreply@blogger.com0

Getting started with OAF development

Andries Hanekom - Tue, 2006-07-11 02:45

I have recently received a number of queries related to OAF development, particularly regarding the quickest way to get started. I will handle these questions in a two part post, in the first I will aim to provide you with the basic software and documentation requirements to get started. In the second post we will have a look at a couple of common OAF extension tasks.

OAF development can be split into two major categories namely:

  • Personalization refers to the ability to declaratively alter the UI to suit user or business needs.
  • Extensibility refers to the ability to programmatically extend an application's functionality.

Personalization is the quickest and easiest way of altering the OAF UI, changes are protected from future upgrades and most personalization tasks can be performed by functional consultants or even trained users. If you have any requirement to alter the OAF UI, personalization should be your first stop. The following document will be of great help in understanding and implementing OAF personalization:

Metalink Doc ID: 236618.1 - OA Framework Personalization and Extensibility Guide: Version 5.7+

Extensibility is used when you are required to implement changes to functionality not provided by personalization, or when you are required to develop add-on/new functionality to the OAF UI.

The following steps will get you up to speed with the correct software and documentation:

  • Follow post: Find the right JDeveloper Patch for OAF development, once you determine the correct JDeveloper version for your development environment you can proceed to download the JDev patch from Metalink.
  • Once downloaded, follow the installation step set out in the OAEXT_README.txt document, located in the root directory of the patch file.
  • Oracle included excellent documentation with the JDeveloper patch, after installation you can locate the documentation index here: JDEV_INSTALL_DIR/jdevdoc/index.htm
  • Follow “You are Customer, Consultant or Support Representative” in Chapter 1: Setting Up your Development Environment of the OA Framework developers guide, make sure you complete all setup steps successfully.

Congratulations you have successfully installed and configured your new development tool, now it’s time to get into the documentation. I suggest you complete Chapter 1, 2 and 3 of the OA Framework developers guide.

You can then proceed with the Oracle Applications Framework ToolBox Tutorial, it is an excellent step by step introduction to OAF development and extension, and will introduce you to all the key concepts required for OAF customization.

Keep tuned for part 2: Include a new column - Extending a standard LOV view object.

Oracle for Windows Hardware Guide

Edward Whalen - Mon, 2006-07-10 17:11

I’d like to stray a little bit from the pure software aspects of Oracle on Windows to focus on the hardware and OS perspective in this blog. The Oracle software is very important, and the version of Windows 2003 is also very important, but if you do not have the proper hardware running this software, you might be headed for trouble. In this installment I will be describing the hardware components necessary to run Oracle and Oracle RAC on Windows as well as discussing some of the new technologies that are available today.

When putting together a system to run Oracle, you must consider not only the amount and type of hardware, but the brand of hardware. In addition, it is important to properly size the system. All of these tasks lead you to putting together an optimal system for your environment.

I’m not going to recommend that you purchase a particular brand of hardware, but I would recommend that you purchase your hardware from a company that you recognize. Not only is the quality of the hardware important, but the quality of the support as well. Many vendors go the extra step and test their products with Oracle on Windows in order to provide an optimal solution. It is especially important when implementing Oracle RAC that you choose a vendor that can provide a tested solution. These vendors will often supply you with white papers or other docs on how to optimally configure their system for Oracle RAC on Windows.

With Oracle RAC, the storage selection is as important as the selection of the actual servers. The storage system must provide the ability to be shared, be robust and be able to support a sufficient number of disk drives to achieve the performance that you need. In addition, the storage should be configured in order to avoid a single point of failure. In this installment of my blog I will discuss the servers, the storage and the infrastructure.


When choosing a Windows server to run Oracle there are several features that you should look for. The CPU is the brains of the computer and should be selected for the type of processor, the cache size and its speed. Because of the memory requirements of the dedicated and shared server processes, the larger the better is usually the case with the cache on the CPUs. The more CPUs there are in the system, the more important the CPU cache is. If you have a choice between a faster CPU and more cache, with Oracle it is usually better to go with more cache.

The type of CPU offers quite a selection now. AMD has an excellent processor with the Opteron line, and Intel has done a great job with the EM64T processor. They are both available with single or dual cores and both are x64 processors. I only recommend the Itanium 2 processors for specific applications where a large system with many processors is required due to the cost differential between the x64 systems and the Itanium systems. In more cases, the x64 (Opteron or EM64T) processors will work great for you.


Storage is one of the most crucial components of your Oracle system. The storage subsystem must be robust, high performing and expandable. Today there are many options to choose from. In the past I would have only recommended SAN storage for an Oracle RAC system, but recently I have had very good experience with both NAS and iSCSI solutions. This in conjunction with Microsoft’s release of its iSCSI support for Microsoft Storage Server 2003 R2, iSCSI is not much more interesting.

When selecting a storage subsystem make sure to minimize the single points of failure. This is done by creating as many redundant components as possible. In addition, not only the size of the disk drive is important, but the number of disk drives is crucial. A state of the art disk drive should be configured to do no more than 125 IOs Per Second (IOPS). Running disk drives hotter than this will cause latencies to increase, thus causing system performance problems.


The network infrastructure is an important piece of your entire system, especially with network storage. If you are using Network Attached Storage (NAS) or iSCSI you should dedicate a network specifically for storage. By sharing the storage network with other network functions such as the RAC interconnect or general network connectivity you could create higher latencies and thus reduced performance. Since the speed of the network is critical to your storage performance (in a NAS or iSCSI environment) you should not use anything less than Gigabit speed networks.

Network storage should always be available locally, that is, there should not be any active components between the database server and its storage; such as routers, firewalls, etc. The only components that should reside between the database server and its storage is one or more network switches. Active components such as routers and firewalls can add too much latency.


In summary, it is important to think about the hardware that you will be deploying in terms of both stability (supportability) and performance. Many of the performance engagements that I am brought in on are caused by an undersized system. If you are using network attached storage, make sure that you have a sufficient network infrastructure to handle it.

Quotation marks changing in web forms, from being pasted from MS Word

Neil Jarvis - Mon, 2006-07-10 08:43
Ever wondered why those funny quotes which Microsoft Word produces are translated as backwards ?. in web forms

If you write a quote in word, type something and then close with another quote, Word magically pairs the two quotes. One quote the right way up the other upside down. If you then copy & paste this text into an Oracle web form you may find, after saving, those wonderful quotes have turned into something else.

The problem here is the NLS_LANG parameter in the Application Server. If your AS is windows go into the windows registry and locate the NLS_LANG parameter of the Oracle home the AS is using. If your NLS_LANG is


You will have to change it to


I should note that it MUST be ENGLISH_UNITED KINGDOM otherwise if you use anything else you may find your ‘£’ will becomes ‘$’

Jdev tip of the day

Wijaya Kusumo - Fri, 2006-07-07 02:42
This screen pop up when I started my Jdev Interesting.... Has anyone ever seen "other" useful tips? :)

Another SYSDBA password entry

Stephen Booth - Thu, 2006-07-06 09:33
Simon Kelsey commeted on one of my earlier entries on this subject that x$kzsrt may be involved:SQL> select * from v$fixed_view_definition where view_name='GV$PWFILE_USERS';VIEW_NAME------------------------------VIEW_DEFINITION--------------------------------------------------------------------------------GV$PWFILE_USERSselect inst_id,username,decode(sysdba,1,'TRUE','FALSE'), decode(sysoper,1,'Stephen Boothhttps://plus.google.com/107526053475064059763noreply@blogger.com1


Subscribe to Oracle FAQ aggregator