Feed aggregator

Oracle Performance Monitoring on Windows

Edward Whalen - Mon, 2006-06-26 11:14

Performance monitoring is a regular task for most Oracle DBAs. For Oracle on Windows this is no exception. The only difference are the tools that are available to you. With Oracle on Windows you still have all of the tools available to you that are available on other platforms, such as the Automatic Workload Repository, but in addition, you can view Oracle statistics via the Windows Performance Monitor (perfmon).

There are several advantages to monitoring Oracle via perfmon. By using perfmon, you can collect Oracle data as well as OS data that might be important to your overall performance monitoring plan. By consolidating your data collection, you will find it easier to analyze data. In addition, perfmon data can be collected and analyzed via the Microsoft Operations Manager (MOM) product. MOM allows you to collect and analyze long term data as well as to configure alerting functions that can let you know that there is a problem.

By default, Oracle statistics are not collected for use by perfmon. By following the steps outlined here you can quickly and easily configure Oracle for perfmon data collection. By default, you still have many ways of monitoring Oracle. Among these are the following:

  • Oracle Automatic Workload Repository. The Automatic Workload Repository (AWR) is performance data that is automatically collected by Oracle for performance and trouble analysis.
  • Event Viewer Alerts. The event viewer alerts are events that are automatically logged directly into the Windows alert log.
  • Oracle Alert Log. The Oracle alert log is the log where Oracle logs status and error messages.
  • Trace Analyzer. Oracle sessions can be traced and that trace data analyzed via the Oracle trace analyzer.

The Oracle Counters for Windows Performance Monitor package is not installed by default. In order to install them when you install Oracle, select the custom install option. You can also install this option later via the Oracle installer. Select custom installation and select the Oracle for Windows Performance option. This will install this package.

Once Oracle Counters for Windows Performance Monitor has been installed, you must perform one more piece of setup. The Oracle performance counters are set up to monitor one Oracle instance. Information about this instance must be configured in the registry. In order to do this, from a command prompt run orafcfg.exe with a username, password and Oracle net service name as follows:

operfcfg –U system –P password –D orcl

This will update the registry. You should now be able to monitor Oracle via perfmon. Some of the things that you can monitor are:

  • The Oracle Buffer Cache. Here you can see the cache miss ratio.
  • Shared Pool Stats. This collection includes the data dictionary cache, and the library cache.
    Log Buffer. Provides information on log space requests.
  • Database Data Files. This object provides physical read and write per second counters.
  • DBWR stats. Provides information on the DB Writer processes.
  • Miscellaneous. Other statistics include dynamic space management, free lists and dynamic sorts.

By taking advantage of Oracle Counters for Windows Performance Monitor you can easily and efficiently monitor Oracle along with monitoring the OS. As I mentioned earlier, perfmon provides valuable performance data that is easily collected and analyzed. Some of the most important and first counters that I look at when performance monitoring a system are:

  • Processor: %Processor Time. This gives me a quick look at how busy the system is.
  • Physical Disk: Avg. Disk sec/Read, Avg. Disk sec/Write. This provides me with an overview of how well the I/O subsystem is doing.

When first looking at a system I am actually more interested in disk latencies than throughput. The Avg. Disk sec/Read and Avg. Disk sec/Write should be in the range of 5-15 ms (0.005 – 0.015). Anything higher than this indicates a problem. Once I have detected a problem, I can try to determine its cause.

Of all of the platforms I work on; Linux, AIX, HP/UX, and Solaris, I find that Windows provides the best performance monitoring tool. Perfmon is easy to use, quite intuitive and easy to view and save the results in a number of formats. Oracle Counters for Windows Performance Monitor is not an end-all, but in conjunction with AWR stats and tracing will help to provide a complete picture.

10,000 Visits and Counting

Eric S. Emrick - Thu, 2006-06-22 21:33
Well, today my blog registered its 10,000th visit. While that is probably an average couple of days for Tom Kyte and a few others, I am kind of proud of my meager slice of the Oracle blogging pie. Of course, I get oodles of traffic from Google. But, that's not too bad is it?

Seriously, it has been a fun 4+ months of blogging. I haven't been able to blog as much as I would have liked over the past 2 months. The fact that I still desire to blog is a good sign though. Time and other constraints in life always crop up. Anyway, it has been really fun and I hope some of you have enjoyed your visits. I have received some very interesting emails and questions from Oracle enthusiasts all over the world.

I want to send one special thanks out to Tom Kyte for recommending I start a blog (and placing me on his metablog even though I stole the naming style of his blog) and another to Doug Burns who featured my blog on his site several months back, giving my traffic a jump start.

Actually, Tom, my first choice for a blog name was The Emusing Blog :)

Grapes of Math Puzzle

Eric S. Emrick - Thu, 2006-06-22 21:29
First, let me say that I was really excited about the title I gave to this post. It hit me while mowing my lawn and made me stop and laugh - thankfully the neighbors were not watching (as far as I know). Anyway, I did a quick Google search on the title Grapes of Math to see how original it was. While I had not heard of the title, it has many hits on the Web. Oh well, so much for absolute originality.

Tom Kyte has a really good puzzle on his blog. I enjoy a good puzzle and submitted my response, which I believe is the solution to the puzzle. But, for my personal satisfaction and to address Mr. Ed's concerns, I wanted to prove that my response was the only possible correct answer, given some conditions I have derived from Tom's post, the problem (picture) itself and intuition. If you are interested in the puzzle, please visit Tom's blog and try to solve it for yourself and skip the remainder of this post.

You mathematicians will please forgive any seemingly barbaric notations or proof layout :) Cary, Jonathan, Tom or any other mathematician lurking about, please feel free to critique the proof if it is incorrect.

Grapes of Math equation generated from picture:
(10(banana)+apple)/pear = 10(grapes)+peach+(strawberry/pear)

Prove the only solution set (banana, apple, pear, grapes, peach, lemon, strawberry) for the Grapes of Math is (9,3,2,4,6,8,1) given the following conditions:


0) each fruit represents a distinct integer that must be in [0-9]. Negative integers don't really make much sense in this case - how do you ascribe a negative integer to any fruit but the pear?
1) the numerator concat(banana,apple) is in [00-99].
2) pear cannot be 1 because grapes * pear would equal grapes and it does not. grapes * pear = lemon.
3) pear cannot be 0 because x/0 is undefined for all integers x.
4) from 2) and 3) 9 >= pear > 1.
5) grapes cannot be 1 because grapes * pear would equal pear and it does not. grapes * pear = lemon.
6) grapes cannot be greater than 4 because that would yield a concat(banana,apple) that is > two digits, which cannot be (condition 1). For example, the integer portion of the quotient concat(grapes,peach) must be less than 50, based on 4).
7) from 5) and 6), 4 >= grapes > 1.
8) from 7) banana is in [4-9]. If the integer portion of the quotient (grapes) is 2, 3 or 4, then given 4) the numerator, concat(banana,apple), must be in [40-99] . The least the numerator could be is 40 given 4) and 7). The highest would be 99 by definition.
9) all fruits taste really yummy (This is for Mr. Ed)

Proof by Exhaustion (brute force method): Grapes of Math Puzzle

Case 1: grapes = 4

If grapes = 4 then banana can only be 8 or 9 because of 4).

Case 1.1: banana = 8

If grapes = 4 and banana = 8 then pear = 2 and lemon = 8. Lemon cannot equal banana by condition 0) and, thusly, banana != 8. Therefore, concat(banana,apple) is not in [80-89].

Case 1.2: banana = 9

If grapes = 4 and banana = 9 then pear = 2 and lemon = 8. Then by subtraction (banana - lemon ) = (9-8) = 1 = strawberry.

Case 1.2.1: apple = 0

If apple = 0 then peach = 5 and concat(grapes,peach) = 45 with no remainder. We know that there must be a remainder because strawberry is 1 in this case. Therefore, apple != 0 and concat(banana,apple) is not 90.

Case 1.2.2: apple = 1

If apple = 1 then apple = strawberry = 1. Therefore, apple != 1 and concat(banana,apple) is not 91.

Case 1.2.3: apple = 2

If apple = 2 then apple = pear = 2. Therefore apple != 2 and concat(banana,apple) is not 92.

Case 1.2.4: apple = 3

If apple = 3 then peach = 6 and concat(strawberry,apple) - concat(strawberry,pear) = strawberry = 1. Therefore, grapes = 4, banana = 9, pear = 2, lemon = 8, strawberry = 1 and apple = 3.

Therefore, concat(banana,apple) = 93 is a numerator solution.

Case 1.2.5: apple = 4

If apple = 4 then apple = grape = 4. Therefore, apple != 4 and concat(banana,apple) != 94.

Case 1.2.6: apple = 5

If apple = 5 then peach = 7 and pear = 4. But, pear is assumed to be 2 and cannot 2 != 4. Therefore, apple != 5 and concat(banana,apple) != 95.

Case 1.2.7: apple = 6

If apple = 6 then peach = lemon = 8. Therefore, apple != 6 and concat(banana,apple) != 96.

Case 1.2.8: apple = 7

If apple = 7 then peach = lemon = 8. Therefore, apple != 7 and concat(banana,apple) != 97.

Case 1.2.9: apple = 8

If apple = 8 then apple = lemon = 8. Therefore, apple != 8 and concat(banana,apple) != 98.

Case 1.2.10: apple = 9

If apple = 9 then apple = banana = 9. Therefore, apple != 9 and concat(banana,apple) != 99.

Therefore, for grapes = 4, the only solution for numerator concat(banana,apple) in [80-99] is 93.

Case 2: grapes = 3

If grapes = 3 then banana can only be 6 or 7 because pear > 1 from condition 4).

Case 2.1: banana = 6

If grapes = 3 and banana = 6 then pear = 2 and lemon = 6, and lemon = banana = 6. Therefore, banana != 6 and concat(banana,apple) is not in [60-69].

Case 2.2: banana = 7

If grapes = 3 and banana = 7 then pear = 2 and lemon = 6. This means apple can only be in [4-5] (cannot be 6 because lemon = apple = 6 violates condition 0).

Case 2.2.1: apple = 4

If apple = 4 then peach = 3, and peach = grapes = 3. Therefore, apple !=4.

Case 2.2.2: apple = 5

If apple = 5 then peach = 5. Therefore, apple != 5.

Therefore, concat(banana,apple) is not in [70-79].

Case 3: grapes = 2

If grapes = 2 then banana must be in [4-5] because of condition 4).

Case 3.1: banana = 4

If banana = 4 then lemon = banana. Therefore, banana != 4 and concat(banana,apple) is not in [40-49].

Case 3.2: banana = 5

If grapes = 2 and banana = 5 then pear = grapes = 2. Therefore, banana != 5 and concat(banana,apple) is not in [50-59].

Therefore, concat(apple,banana) is not in [40 - 59].

From grapes in [2-4], we have proved that only one solution (93) exists for the numerator concat(banana,apple) between 40 and 99. By condition 7), concat(banana,apple) is not in [00-39].


Therefore, 93 is the only solution for concat(banana,apple) in [00-99]. After exhausting all possible two digit values for concat(banana,apple) only one solution set (banana, apple, pear, grapes, peach, lemon, strawberry) was found:


and for Mr. Ed...

(9,3,-2,4,6,8,1) iff concat(grapes,peach) = -46

Solution set applied to equation of Grapes of Math:

(10(9)+3)/2=93/2=46 ½=10(4)+6+(1/2) quod erat demonstrandum

Note: lemon is absorbed in the equation, given the correctness of strawberry = 1.

Viva le Resistance, Viva Mozilla!

Andries Hanekom - Thu, 2006-06-22 09:00
Many of us are content to use IE for all our browser needs, but few know that there is an alternative. Mozilla Firefox is a open-source browser that some ten percent of the web uses, and which has been certified for use with Oracle EBS 11i.

I have been using Firefox for just more then a year, it is a very stable and well tested product that can be installed and immediately used as your main browser. There is also a number of plugin’s and extensions, Eddie Awad created a very useful plugin that allows you to search Metalink and Oracle Docs direct from your browser.

So, help stem the tide, go ahead, check it out.

Oracle Blooper

Eric S. Emrick - Wed, 2006-06-21 20:21
Well, instead of assimilating the suitable, albeit plentiful, Oracle manuals to cover the material required for the Oracle 7.3 -> Oracle 9i upgrade certification exam I opted to search for a single resource. I came across a third-party study tool that was recommended by Oracle and seemed to foot the bill. Very much to my surprise the material was disjointed, loaded with typographical errors and often flat out wrong.

I was willing to forgive the copious typographical mistakes per/page (which approached the Golden Ratio mind you) as I could deduce the intentions. I could stomach the disjointed word salads and sparse information. But I refused to read another page after encountering a heinously blatant, careless and nonsensical bit of misinformation. How could I possibly continue to use this material as a study reference if I could not trust the content? To the misinformation at hand, the material states the following verbatim:

PGA_USED_MEM - The process is using PGA memory.
PGA_ALLOC_MEM- The process has been allocated PGA memory.
PGA_MAX_MEM - The process has been allocated maximum memory.
PGA_GIBBERISH - The process has found gibberish in the PGA and wishes to purge. (OK, this was my invention)

I scratched my head. Re-read, scratched head some more. Finished beer and reached for another. Nothing seemed to alleviate my consternation. I was well aware of these attributes of v$process and was not so much concerned with the incorrectness, as I knew their meaning. It was the gross negligence that left my jaw drooping for a minute.

The values for these attributes are NOT Boolean as you well know. You don't query v$process and find a Y or N associated with the values for these attributes. The Oracle documentation defines these attributes in a very straightforward manner. Is there any other way?

PGA_USED_MEM number PGA memory currently used by the process
PGA_ALLOC_MEM number PGA memory currently allocated by the process (including free PGA memory not yet released to the operating system by the server process)
PGA_MAX_MEM number Maximum PGA memory ever allocated by the process

Simply stated, I was shocked that the author(s) and editor(s) put such little thought into the material and subsequent proof reading. Actually, I think the author's brain was tied behind his back while writing this material. If one aspires to put together training material and includes attribute definitions that are pre-defined for you in the Oracle documentation set, might I recommend taking a cursory glance at said documentation? You can't just feed me a heaping helping of documentation rubbish without expecting me to pitch the kindling into the nearest can - I know, I've seen me do it! Did I mention the material is several fold more expensive than any of Tom Kyte's or Jonathan Lewis' books? Lesson learned.

I/O Performance and Oracle

Edward Whalen - Mon, 2006-06-19 16:32
One of the most common performance problems that I encounter is an insufficient I/O subsystem. This is especially true when working in an Oracle (or SQL Server) environment, because of the Database Server’s sensitivity to high I/O latencies. With Oracle, the time it takes for each I/O operation is much more important than the actual throughput that you can achieve (MB/sec). Because of this, the I/O subsystem should be monitored, tuned and sized sufficiently.

One of the biggest problems that performance tuning consultants have encountered in the last few years is the introduction of larger and larger disk drives. This has led to database systems being created with larger and larger disk drives, but fewer of them. This has led to an epidemic of I/O performance problems. Let me explain further.

Most I/O in Oracle is random in nature. This is due to the fact that you have a system where many people are looking for many diverse pieces of data. In addition, structures such as indexes work by retrieving one block that points to another block and finally to the data block itself. Thus almost all I/O can be considered random.

The basic structure of the disk drive has not changed significantly in the last few years. Since disk drives have gone from 2 GB in size to 300 GB in size, the average seek time has dropped from 9 ms to 4.5 ms. This represents a 150 times increase in size, with a 2 times increase in performance. This has caused a problem where large databases are being configured on fewer and fewer disk drives.

To make things worse, some vendors are now selling Serial-ATA drives that have an average seek time of 8 ms or higher. This represents a 250 times larger drive with essentially no increase in performance. SATA drives should be avoided for Oracle storage if Fibre Channel or SCSI drives could be used.

At the same time, with Oracle on Windows (32-bit) databases are getting larger and larger with memory not increasing significantly. This has caused an upturn in I/O problems occurring with Oracle systems on Windows. The good news is that the solution to this problem is simple, add more disk drives.

How do you determine if you are having I/O problems? There are several methods. The Windows performance monitor (Perfmon) provides a wealth of I/O performance data. To me, the most significant I/O counters are Avg. Disk sec/Read and Avg. Disk sec/Write. These represent the disk latencies and are your first indication that you are having a problem. Disk latencies should be in the range of 5 ms – 15 ms for reads and 0 – 10 ms for writes (if a write cache is used). The performance counters for Windows are very accurate and very useful in determining if you have a performance problem.

In addition to the Windows Perfmon counters, Oracle AWR reports can also be quite useful in determining if you are having an I/O problem. Look for the top 5 wait events. If you see one or more of the Top 5 Timed Events in the System I/O wait class, this is an indication of an I/O problem. In addition, the I/O statistics also report average read ms and average write ms per data file and per tablespace. This information is very useful.

In summary, I/O is critical to the performance of your Oracle Database system. Don’t confuse size with performance. The performance capacity of your I/O subsystem depends on the number of disk drives, not the size of the drives. Performance and sizing are critically linked. An undersized I/O subsystem will perform poorly no mater how much CPU capacity and memory you have. If you are experiencing performance problems, check the I/O subsystem first.

Find the right JDeveloper Patch for OAF development

Andries Hanekom - Mon, 2006-06-19 08:54

I have read a number of questions regarding what JDeveloper version should be used for OAF development. After a recent upgrade of our development system from 11.5.9 to 11.5.10 CU2, I did some research on Metalink and found the following info regarding JDeveloper versions:

Current JDeveloper patches:

  3. Patch 4573517 - Oracle9i JDeveloper with OA Extension for 11.5.10 CU2

To determine which patch to use, you can simply check the framework version in your instance by using http://host:port/OA_HTML/OAInfo.jsp, then choose the matched one.

11.5.10K = patch 4045639
11.5.101CU = patch 4141787
11.5.102CU = patch 4573517
11.5.103CU = patch 4725670

It would be interesting to have a survey regarding what framework versions are being used for OAF development. Please post your current development version in a comment; I will publish the results at a later stage.

Checkout Metalink note 416708.1 for the up to date list of OAF releases.


Andries Hanekom - Mon, 2006-06-19 04:05
The main aim of this Blog is to share my experiences as an Oracle e-Business Suite (EBS) developer. I will also from time to time blog about any subject that made me laugh, cry, angry or I feel would be of interest.

Just to get everybody thinking here is a short list of EBS topics I will be covering:

  • Oracle Application Framework Development, Extension and Personalization
  • Database Performance (Development Specific)
  • SQL & PL/SQL Concurrent Development
  • Open Interfaces and API's
  • Forms, Reports, CUSTOM Library development and customization
  • Workflow Development and Customization
  • Development Tools (JDev, Forms, Reports, TOAD, etc.)

All comments, anonymous or otherwise, fully appreciated.

Luca Canali and ASM Metadata Internals

Radoslav Rusinov - Wed, 2006-06-14 13:12
In a recent technical paper Luca Canali explains how to find the mapping between physical storage, ASM allocation units, and database files by querying the undocumented X$ tables.By using the information retrieved from X$KFFXP table he succeeded to:Retrieve a file directly from ASM disksMonitor how the extents in a datafile are allocated across ASM disksMonitor how a datafile is spread across ASMRadoslav Rusinovhttp://www.blogger.com/profile/18163031714036680150noreply@blogger.com0

Christo Kutrovsky's Blog

Radoslav Rusinov - Sun, 2006-06-11 12:23
If you’ve missed to notice – Christo Kutrovsky started his own professional blog dedicated mainly to the Oracle database.Christo Kutrovsky is an Bulgarian Oracle professional currently working in <?xml:namespace prefix = st1 />Canada as a Senior Database/System Administrator for The Pythian Group.His blog is a part of the corporate blog of The Pythian Group.The blog is only two-months old but Radoslav Rusinovhttp://www.blogger.com/profile/18163031714036680150noreply@blogger.com0

Oracle High Availability on Windows using Fail Safe

Edward Whalen - Sat, 2006-06-10 13:13

Oracle on Windows supports the same Oracle Disaster Recovery (DR) and High Availability (HA) features as other platforms, such as Oracle RAC and Data Guard. In addition to these, Oracle on Windows supports Oracle Fail Safe. These are all excellent solutions with different usages, costs and performance.

Oracle RAC is a high availability solution that allows multiple Oracle instances to access the same database. This provides for both high availability and additional performance, since all nodes are active. Oracle RAC is an excellent product, but somewhat complicated to install and manage and can be expensive.

In addition to Oracle RAC is Oracle Data Guard. Data Guard is really more of a Disaster Recovery (DR) solution than a High Availability solution, but it can be very useful in the event of a system failure. Data Guard is a fantastic product which I have installed many times. Data Guard is one of those products where the base installation can be fairly straight forward, but there are so many options and modes of operation that it can quite quickly become very complicated.

The final solution in this discussion is Oracle Fail Safe. Fail Safe is a Windows only product that makes Oracle Microsoft Cluster Services (MSCS) aware. Failsafe is available for all Windows platforms (32-bit, x64 and Itanium) and is downloadable from the Oracle web site. Fail Safe is a plug in to MSCS to allow the Oracle instance and listener to be managed under the control of MSCS. This allows the Oracle instance to fail over in the event of a failure of one of the nodes.

The MSCS architecture is very straight forward. Two systems share the same disk, of which only one system controls at a time. In the event of a failure (determined by the heartbeat mechanism), the instance currently running the Oracle instance and controlling the storage is replaced by the standby system. This allows the Oracle database to be back up and running in a matter of minutes, thus improving availability.

Oracle Fail Safe has the advantage over RAC in that it is much easier to implement and administer. Most Windows shops already have implemented MSCS and are very familiar with it. In addition, Oracle Fail Safe is a core feature of Oracle9i and Oracle10g for Windows and no additional license is needed.

With Oracle Fail Safe you can fail over the following Oracle components:

  • Oracle Database Server.
  • Oracle Listener.
  • Oracle Intelligent Agent.

The failover is seamless and works very well. In addition, the management tools allow you to configure Fail Safe to automatically fail back a node at a scheduled time, thus allowing you to configure different hardware resources for the primary and standby servers.

I have worked on multiple Oracle Fail Safe installations. In a Windows environment where you want to have assurance of High Availability at a low entry point cost, Oracle Fail Safe might be for you.

Configuring Oracle Fail Safe is easy once the Microsoft Cluster Services have been installed. The following steps are done in order to install Oracle Fail Safe.

  1. Install Windows on both servers.
  2. Install MSCS (if applicable) and create the cluster.
  3. Install Oracle Database Software on each node’s local disks, using the same Oracle Home directory.
  4. Create the Oracle database on one node using the shared disk for storage.
  5. Install Oracle Fail Safe on each node in the cluster.
  6. Configure the cluster using the Fail Safe manager.

You will find that Fail Safe is an easy to use, inexpensive way to provide high availability in a Windows environment.

Oracle RAC on Windows

Edward Whalen - Wed, 2006-06-07 18:42
I have often been asked if Oracle RAC runs on Windows. The answer is a resounding “Yes”. Oracle RAC has been available on Windows as long as Oracle RAC has been around. With a few exceptions, Oracle RAC functions the same on Windows as it does on any other platform.

For those of you who are not familiar with Oracle RAC, let me give you a brief overview. Oracle RAC (Real Application Clusters) has been around since Oracle9i. It is a follow-on product to Oracle Parallel Server (but much better). In essence, Oracle RAC is a clustering product that allows Oracle on multiple servers to access the same database.

This is unlike a typical failover cluster in that all nodes are active and accessing the same data. In a failover cluster only one node can access the database at a time. With RAC it is all nodes all the time.

Oracle defines an instance to be the memory and processes necessary to access the Oracle database. In a RAC cluster, there are multiple instances accessing the same database. RAC accomplishes this via a sophisticated locking mechanism that keeps data consistent among the nodes. Because of the way RAC does its locking and cache consistency, the performance of the cluster interconnect is critical. The cluster interconnect must be Gigabit Ethernet as a minimum.

Setting up RAC on Windows is pretty much the same as setting up RAC on other platforms with a few exceptions. Because of the way Windows does its networking, it is important to have the Public (talks to the world) interface listed first in the advanced network properties.

All nodes in the cluster must be able to access all other nodes without having to type in a password. This can be accomplished via several methods. The preferred method is to install and configure Oracle and RAC using a domain account. This account must be available on all nodes in the cluster and it must have local administrator rights. The second method is to use the local Administrator account, but make sure that the password is identical on all nodes in the cluster.

Because all nodes in the cluster access the same database, they must all be able to see the same storage. There are several methods supported under Windows for sharing storage. They include the use of raw devices, Oracle ASM (Automatic Storage Management) and the Oracle Cluster Filesystem (OCFS). Oracle ASM and raw devices are available on all platforms that support RAC, but OCFS is only available on Windows and Linux. I have implemented RAC clusters on Windows using all of these methods, and they all work well, but raw devices can be very difficult to administer. I prefer to either use ASM or OCFS. With OCFS and OCFS-2, Oracle supports the use of a shared Oracle Home directory so that there is only one copy of the Oracle binaries.

Oracle RAC is an important feature to Oracle since it provides a scalable solution that allows smaller systems to provide greater overall performance to the cluster. This opens the door for commodity hardware and operating systems such as Microsoft Windows to provide higher performance systems by combining the power of multiple systems into one cluster. Oracle currently supports up to an 8 node cluster with Oracle 10g R2 on Windows, however, the largest RAC cluster on Windows that I have been involved with has been 6 nodes. The most common clusters that I have seen are 4 or 2 node clusters.

I recommend using the largest system that you can, so that as you need more nodes you can add them in one at a time. It is easier to add a node than to add more resources to an existing node. Don’t forget to allocate plenty of memory for Oracle. The RAC cluster is scalable, but you will eventually run out of bandwidth on the interconnect if you add too many nodes (depending on what you are doing).

In general, Oracle RAC is a great product that provides for both scalability and failover capabilities. Microsoft Windows is a great platform to run Oracle RAC on.
Edward Whalen is the Chief Technologist and Founder of Performance Tuning® Corporation and has been working with Oracle for over 15 years. He is currently working on his 9th book; “SQL Server 2005 Administrator’s Companion”. He holds the distinction of being one of the few people to write books on both Oracle and MS SQL Server and writes for both Oracle Press and Microsoft Press.

Oracle supports UC Berkeley lab to work on OSS systems

Omar Tazi - Thu, 2006-06-01 18:00
Oracle along with IBM Corp., Hewlett-Packard Co., NNT Multimedia Communications Laboratories Inc., and Nortel Networks, are pledging annual contributions of up to $170,000 for the next five years. This financial help will help the RAD lab to innovate and make its creations freely available under the open source Berkeley software distribution license. In addition, these companies are going to make some resources availble to act as consultants. In 2005 the RAD lab was launched with a $7.5M donation from Google Inc., Microsoft Corp. and Sun Microsystems Inc.

The projects will be focused for the most part on Artificial Intelligence based systems to help maintain large distributed computing systems used by data-intensive Internet businesses.

More on this in the press release.

Oracle's Ajax-enabled contribution coming up

Omar Tazi - Thu, 2006-06-01 17:49
On May 17th during Oracle’s general session at JavaOne 2006, Thomas Kurian announced that Oracle is about to open source yet another exciting technology. Indeed, a year ago Thomas announced at JavaOne that we were going to open source our ADF Faces components which are server-side user interface components based on the JavaServer Faces standard. Thomas also announced that same year that we were going to make Eclipse better by leading a couple of initiative. Well, Oracle delivered. Today we have open sourced ADF Faces and we are leading three different Eclipse plug-ins which are Dali (O/R mapping design time for EJB30 and JPA), JSF tooling and co-leading the BPEL design time with IBM. As for the ADF Faces components (excluding the rich client or Ajax-enabled components) they have been donated to the Apache software foundation. The project is currently waiting to graduate out of incubation and more information can be found here:

So what’s exciting about these Ajax-enabled JSF components anyway?

Oracle has enhanced its already extensive JSF component library ADF Faces, with a set of rich and interactive components that will be part of the ADF Faces Rich Client donation to the open source community. All these components leverage extensively the technique referred to as Ajax. Ajax is a Web development technique for creating interactive web applications. The intent is to make web pages feel more responsive by asynchronously exchanging data with the server, so that the page does not have to be entirely reloaded each time the user triggers an event. Ajax applications are typically more responsive and provide richer interactivity.

Oracle has already donated 100+ server-side (or thin-client) components to the Apache community. Additionally, Oracle will be donating a new set of rich Ajax-enabled components, which will bring the total number of donated components to 150+ JSF components.

The list below is a subset of the JSF rich component library that Oracle decided to contribute to the OSS community:

1- Table
The new table comes with the same functionality already provided by the current ADF Faces table component, plus some extra features that will dramatically enhance the end-user experience. The new table component comes with full support for asynchronously fetching data from the underlying services using the XMLHttpRequest object. The table provides scrolling through records, sorting, and single and multi-select out of the box, as well as built-in support for swapping columns at runtime.

2- Pop-up Menu
One of the coolest and most requested features in a rich and interactive end-user environment is the ability to right-click and display a popup menu at runtime. The new rich-client version of ADF Faces provides a popup component that can be attached to components such as a table. This will allow application developers to provide end-user actions via a popup menu that otherwise would have to be hard-coded in JavaScript. Now the developer experience is purely JSF/Java while the end-user gets the desired "thick-client" behavior in the browser.

3- Accordion
This is a common component in most desktop applications and helps the application developer optimize the use of real estate on the client side. The end-user can click on an accordion and display its content. The new ADF Faces component library comes with two different accordions: one that only displays one accordion at the time and one that can display multiple accordions at a time. So, now application developers will have the same type of functionality in the browser as they have in their desktop applications.

4- Tree
For most application developers the hardest Web widget to implement is a rich Tree widget. ADF Faces comes with a Tree widget that has built-in support for asynchronously communicating with the underlying services. When interacted with, the Tree component will not re-render the entire page, which enhances the end-user experience.

5- Menu
ADF Faces also comes with a "regular" menu component. This component can be used by application developers to create toolbars similar to what is used in desktop applications. The menu is leveraging Ajax and provides DHTML dropdown menus etc. From the application developer’s view there is no JavaScript needed to enable this JSF component - only JSF and Java.

With this new rich client component library application developers will be able to leverage Ajax to its fullest without writing a single line of JavaScript to get the rich desktop user experience on the browser. We are not sure yet if this donation is going to end up in Apache it is not up to us it is completely up to the Apache MyFaces community. Stay tuned!

Parsing XML in PL/SQL DOM and extractValue

Scott Schwab - Thu, 2006-06-01 06:16
I pulled this post from an PL/SQL-XML forum post I wrote a couple of weeks ago. I don't know if it answered the original question, but I thought it good enough to repost here.-----------------------------------------------------------------------------------------I have extracted data from XML in two waysUsing DOM to pull text values out by a value type document, with a list of , I have done theScott Schwabhttp://www.blogger.com/profile/14014003619353346507noreply@blogger.com0

Employees with No Usernames or entry in WF_ROLES

Jo Davis - Wed, 2006-05-31 23:39
And to list out all the active employees, their usernames and whether they have an entry in WF_ROLES:

select ppf.employee_number, ppf.full_name, fu.user_name, wr.status
from per_people_f ppf,
fnd_user fu,
wf_roles wr
where ppf.person_id =fu.employee_id (+)
and ppf.person_id =wr.orig_system_id (+)
and ppf.current_employee_flag='Y'

Run this to check everyone in the approval hierarchy is set up correctly if you are having any problems with testing transactions in Approval Manager and get the dreaded...
ORA-20001: The approver identified by the following parameters is invalid: originating system PER originating-system ID PER_ID. Please delete or replace this approver wherever they occur in AME data, including approval groups, list-modification conditions, and substitution actions. This approver does not have an entry in wf_roles. (ORIG_SYSTEM_ID=)

Oracle on Windows Server 2003 x64 Edition

Edward Whalen - Fri, 2006-05-26 08:58
Oracle has been around on Windows Server for many years (more than a decade – since NT). In that time there have been many changes to Oracle as features have been added and improved. However, in my opinion, the most significant improvement to Oracle on Windows was introduced about a year ago without much fanfare or publicity. This is the introduction of Oracle 10g on Microsoft Windows Server x64.

The introduction of Oracle on Windows Server x64 has overcome a limitation that has existed since the introduction of Oracle on Windows. The number of user sessions is limited with Oracle on Windows due to the way Oracle is architected on Windows and the basic architecture 32-bit chipset. Before I get into that, let’s review the 32-bit architecture and Oracle architecture.

The Intel x86 architecture was introduced in 1978 as a 16-bit processor. This was replaced in 1986 with a 32-bit version. I don’t think that anyone expected the 32-bit architecture to hang around this long. The 32-bit architecture allowed for up to 4 GB of virtual address space and 4 GB of RAM. This was later improved to allow 64 GB of RAM with PAE, but the virtual memory (process address) limits remain at 4 GB.

This remained in effect (for the PC server platform) until the introduction of the AMD Opteron and Intel EM64T processors. The EM64T and Opteron processors can access up to 256 Terabytes of virtual memory ( 2^48 bytes). The architecture allows this limit to be increased to a maximum of 16 exabytes (2^64 bytes). The EM64T and Opteron processors can currently address up to 1 Terabyte of physical memory ( 2^40 bytes). The architecture allows this limit to be increased to a maximum of 4 petabytes (2^52 bytes).

Oracle is made up of many independent processes (or threads) that perform independent functions. The server processes are created when a user connects into the Oracle instance and does work on behalf of the user such as retrieving data from the SGA, or reading data from the data files, modifying data in the SGA and presenting data back to the user. The background processes do work on behalf of the Oracle instance and consist of things such as the DB writer, Log writer, archiver, system and process monitors and many others. These are independent entities that make the Oracle RDBMS work.

In most operating systems the above mentioned entities are processes. In Oracle for Windows these entities are threads. The difference between a process and a thread is how memory is used. In essence, a thread is a subcomponent of a process. A process has its own memory and registers and acts somewhat independently. With the Intel x86 architecture a process can address up to 2 GB of memory, or 3 GB if the /3GB flag is used upon Windows startup. The operating system loads the process’s memory and registers into the CPU and start the process running. Whenever the process’s time slice is up or it puts itself to sleep waiting on an I/O or other resources the CPU unloads the process, loads another process’s memory and registers and starts it up. This is known as a process or context switch. The number of context switches per second can be seen in perfmon under the system object. A context switch is a very expensive operation.

In order to make this more efficient, Oracle was developed to use the Windows thread architecture. A thread is a subcomponent of a process. With a thread, multiple entities can use the same process, thus avoiding context switches. The process allocates the memory and each thread has a pointer into the same memory. The downside of using threads is that all Oracle entities, such as the server processes and background processes share the same 3 GB address space. This has caused an issue with memory, since each Oracle server process and background process can consume significant memory. The end result is that the number of Oracle user sessions can be limited on Windows. Our experiments have shown connection failures at around 300 – 400 users.

If you were to look into the task manager on a Windows system running Oracle you would see a process called oracle.exe. If you expand the columns to include thread count you would see that oracle.exe is made up of a number of threads (20-30, depending on what options you are using).

So, as you can see, the decision to use Windows threads for Oracle has turned out to be somewhat of a liability, since the virtual memory address space limitation has caused the number of supported sessions to be somewhat limited. However, the upside is that the reduction of contexts switches provides a performance improvement.

With the switch from Oracle 32-bit to Oracle 64-bit the virtual memory limitation is no longer an issue. Thus the liability has been turned into an asset. With no more session limitations and the additional advantages that you get from Oracle on Windows such as Active Directory integration, ease of use and stability it has become a great platform for Oracle. Our tests in the lab have seen no connectivity problems well over 1,000 sessions.

I am often asked by customers what platform they should choose for Oracle. I feel that there are many viable platforms. You should look at what you are comfortable with. If you are a Windows shop and are comfortable with Windows and have significant Windows expertise, then this is the platform that you should choose. Adding a foreign OS into a Windows shop just to run Oracle can be a costly mistake.

In the next few weeks I will be elaborating on some of the additional features that makes Oracle on Windows a great platform to use.

The Flashback Database Feature

Radoslav Rusinov - Thu, 2006-05-25 11:14
IntroductionThe Flashback Database is one of the most powerful new features in Oracle 10g coming as a part of Backup & Recovery enhancements.In this post you can find the result of my research and shared experience about this feature.What is the Oracle Flashback Database?Flashback Database is a part of the backup & recovery enhancements in Oracle 10g Database that are called Flashback Radoslav Rusinovhttp://www.blogger.com/profile/18163031714036680150noreply@blogger.com0

QUTE unit-testing Tip #1

Scott Schwab - Tue, 2006-05-23 11:44
I have been using Qute for a while to test my PL/SQL.  Someday soon I plan to write up a how-to for my fellow users, but till then I thought I would just post a quick hint now and then   Q) You built a Unit-Test, and now are adding Test-Cases.  Darn the table you want to check a value of does not appear in the Outcomes "Check contents of test element ..." pull down.  How can you add it?   A) Scott Schwabhttp://www.blogger.com/profile/14014003619353346507noreply@blogger.com0


Subscribe to Oracle FAQ aggregator