SQL_TRACE, TKProf and Explain Plan

Oracle provides extensive tracing and interpretation tools to assist analysts diagnose and resolve sub-optimal performance issues. This article covers the most important of these and points to detailed reference articles.

Tracing Related Initialization Parameters

Prior to tracing, there are a number of parameters that need to be set so that the trace information is complete. These parameter should be set up in the "init.ora" file for the particular instance (SID) where you wish to use SQL Trace although they can also be set individually at the session level.

Parameter:TIMED_STATISTICS

Enable/Disable the collection of timed statistics, such as CPU and elapsed times.

• TRUE - Enable timing
• FALSE - Disable timing (Default value).

Note that much of the tracing information that can be gathered is rendered useless if TIMED_STATISTICS is set to False and timings are not collected.

Parameter:MAX_DUMP_FILE_SIZE

Specifies the maximum size of trace files in operating system blocks. The default value for this was 10000 OS blocks in 8i version, and limited only by the space available in 9i version. If your trace file is truncated then you will see a message similar to:

*** DUMP FILE SIZE IS LIMITED TO 12345 BYTES***

and the size of this parameter should be increased.

Parameter:USER_DUMP_DEST

Specifies the destination for the trace file. The default value for this parameter is the default destination for oracle dumps on your operating system.

These parameters can be dynamically altered using alter system/alter session commands (Note that USER_DUMP_DEST can only be modified at the system level).

For example TIMED_STATISTICS can be enabled/disabled dynamically by using the following SQL statement:

ALTER SYSTEM/SESSION SET TIMED_STATISTICS = TRUE/FALSE;

SQL_TRACE

SQL_TRACE is the main method for collecting SQL Execution information in Oracle collecting a wide range of information and statistics that can be used to tune SQL operations.

Enabling SQL_TRACE

The SQL Trace facility can be enabled/disabled for an individual session or at the instance level. If the initialisation Parameter SQL_TRACE is set to TRUE in the init.ora of an instance, then all sessions will be traced.

SQL_TRACE can be set at the instance level by using the initialisation parameter SQL_TRACE:

Parameter:SQL_TRACE

Enable/Disable SQL Trace instance wide.

• TRUE - Enable statistics to be collected for all sessions.
• FALSE - Disable statistics to be collected for all sessions.

SQL_TRACE can also be enabled/disabled at the system/session by issuing the following SQL statement:

ALTER SYSTEM/SESSION SET SQL_TRACE = TRUE/FALSE;

Trace can also be enabled/disabled on other sessions (as well as your own) using the DBMS_SUPPORT package.

See Note:62160.1 Tracing Sessions in Oracle7/8 for details.

For more information on how to setup tracing, refer to:

Note:15160.1 Setting SQL Trace in the Oracle Tools.

There is also extensive information in the

Oracle9i Database Performance Tuning Guide and Reference Chapter 10: Using SQL Trace and TKProf

Trace Files

Oracle will generate trace (.trc) files for every session where the value of SQL_TRACE=TRUE and write them to the USER_DUMP_DEST destination. If tracing has been enabled for the instance then individual trace files will be generated for each session, unless otherwise disabled. Note that the generated files may be owned by an operating system user other than your own so the necessary privileges will need to be put in place before they can be formatted with TKProf.

Explain Plan

The Explain Plan command generates information that details the execution plan that will be used on a particular query. It uses a precreated table (PLAN_TABLE) in the current shema to store information about the execution plan chosen by the optimizer.

The plan table is created using the script utlxplan.sql. This script is typically found under the Oracle Home in the rdbms/admin directory.

On Unix its location will be:

$ORACLE_HOME/rdbms/admin On WindowsNT/2000:

%ORACLE_HOME%\rdbms\admin

This script creates the output table, called PLAN_TABLE, for holding the output of the Explain plan Command. Note that the exact structure of the plan table can change with different release as new features are introduced.

Populating the Plan Table

The plan table is populated using the explain plan command:

SQL> EXPLAIN PLAN for select * from emp where empno=1000;

This command inserts the execution plan of the SQL statement into the plan table. It is also possible to adds the name tag to the explain information by using the set statement_id clause.

Displaying the Execution Plan

Once the table has been populated, the explain information needs to be retrieved and formatted. There are a large number of scripts available to format the plan table data. Some of the most popular are noted below:

Supplied Scripts:

$ORACLE_HOME/rdbms/admin/utlxpls.sql: script to format serial explain plans $ORACLE_HOME/rdbms/admin/utlxplp.sql: script to format parallel explain plans

Articles:

<Note:31101.1> Obtaining Formatted Explain Plan Output

<Note:39294.1> Formatted Select of PLAN_TABLE for EXPLAIN PLAN command

<Note:39341.1> Automatic Explain Plan

<Note:1019631.6> SCRIPT: SCRIPT TO SIMPLIFY THE USE OF EXPLAIN PLAN

Interpretation of Explain Plan

This is a complex topic and is covered in detail in the following article:

<Note:46234.1> Interpreting Explain Plan

There is also extensive information in the

Oracle9i Database Performance Tuning Guide and Reference Chapter 9: Using EXPLAIN PLAN

AUTOTRACE

The autotrace facility in SQL*Plus allows analysts to view the execution plan and some useful statistics for a SQL statement within a SQL*Plus session. This option was introduced with 7.3 version of Oracle.

Autotrace needs to be initiated in the SQL*Plus session prior to executing the statement. The Autotrace command is:

SET AUTOTRACE [OPTIONS] [EXPLAIN/STATISTICS]

For a detailed explanation of AUTOTRACE functions see:

<Note:43214.1> AUTOTRACE option in 7.3As

with the EXPLAIN PLAN command, to obtain an execution plan the PLAN_TABLE must be created in the user's schema prior to autotracing.

Example

SQL> set autotrace traceonly explain

SQL> select * from dual;Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DUAL'

To enable viewing of STATISTICS data, the autotracing user must have access to dynamic performance tables. To achieve this, grant PLUSTRACE role to the user.

The PLUSTRACE role is created by the plustrce.sql script.

On Unix the location is:
$ORACLE_HOME/sqlplus/admin
On WindowsNT/2000:
%ORACLE_HOME%\sqlplus\admin

This script must be run by the SYS user. A DBA user can then grant the role to the users who wish to use the the AUTOTRACE option.

Refer to:

<Note:1055431.6> ORA-01919 usingAUTOTRACE in SQL*Plus

Extensive reference information regarding the autotrace facility can be found in the

SQL*Plus Users Guide and Reference Release 9.0.1

TKProf

The TKProf facility accepts as input a SQL trace file and produces a formatted output file. For the full syntax of TKProf see the Oracle Server Tuning Manual. If TKProf is invoked with no arguments, an online help is displayed.

Basic Syntax of TKProf

TKPROF filename_source filename_output EXPLAIN=[username/password] SYS=[YES/No] TABLE=[tablename]

For more information on using TKProf see:

<Note:32951.1> TKPROF Interpretation

<Note:29012.1> Quick Reference TKPROF usage

<Note:41634.1> TKPROF and Problem Solving

There is also extensive information in the

Oracle9i Database Performance Tuning Guide and Reference Chapter 10: Using SQL Trace and TKProf

Concurrent Managers

The concurrent managers in the Oracle e-Business suite serve several important administrative functions. Foremost, the concurrent managers ensure that the applications are not overwhelmed with requests, and the second areas of functions are the management of batch processing and report generation.

This article will explore tools that are used by experienced administrators to gain insight and improved control over the concurrent management functions. We will explore how the concurrent managers can be configured via the GUI, and also explore scripts and dictionary queries that are used to improve the functionality of concurrent management.

The Master Concurrent Managers

There is a lot of talk about "the" concurrent manager in Oracle Applications. Actually, there are many Concurrent Managers, each governing flow within each Oracle Apps areas. In addition there are "super" Concurrent Managers whose job is to govern the behavior of the slave Concurrent Managers. The Oracle e-Business suite has three important master Concurrent Managers:

• Internal Concurrent Manager — The master manager is called the Internal Concurrent Manager (ICM) because it controls the behavior of all of the other managers, and because the ICM is the boss, it must be running before any other managers can be activated. The main functions of the ICM are to start up and shutdown the individual concurrent managers, and reset the other managers after one them has a failure.
• Standard Manager — Another important master Concurrent Manager is called the Standard Manager (SM). The SM functions to run any reports and batch jobs that have not been defined to run in any specific product manager. Examples of specific concurrent managers include the Inventory Manager, CRP Inquiry Manager, and the Receivables Tax Manager.
• Conflict Resolution Manager — The Conflict Resolution Manager (CRM) functions to check concurrent program definitions for incompatibility rules. However, the ICM can be configured to take over the CRM's job to resolve incompatibilities.

Now that we understand the functions of the master Concurrent Managers, let's take a quick look at techniques that are used by Oracle Apps DBAs to monitor the tune the behavior of the Concurrent Managers.

Tuning the Concurrent Manager

All successful Oracle Apps DBAs must understand how to monitor and tune each of the Concurrent Managers. This article will explore some of the important techniques for monitoring and tuning the Oracle Apps Concurrent Manager processes. The topics will include:

• Tuning the Concurrent Manager
  - Tuning the Internal Concurrent Manager
  - Purging Concurrent Requests
  - Troubleshooting Oracle Apps performance problems
  - Adjusting the Concurrent Manager Cache Size
  - Analyzing the Oracle Apps Dictionary Tables
• Monitoring Pending Requests in the Concurrent Manager
• Changing the dispatching priority within the Concurrent Manager

Tuning the Internal Concurrent Manager (ICM)

The ICM performance is affected by the three important Oracle parameters PMON cycle, queue size, and sleep time.

• PMON cycle — This is the number of sleep cycles that the ICM waits between the time it checks for concurrent managers failures, which defaults to 20. You should change the PMON cycle to a number lower than 20 if your concurrent managers are having problems with abnormal terminations.
• Queue Size — The queue size is the number of PMON cycles that the ICM waits between checking for disabled or new concurrent managers. The default for queue size of 1 PMON cycle should be used.
• Sleep Time — The sleep time parameter indicates the seconds that the ICM should wait between checking for requests that are waiting to run. The default sleep time is 60, but you can lower this number if you see you have a lot of request waiting (Pending/Normal). However, reducing this number to a very low value many cause excessive cpu utilization.

All of the concurrent managers, with the exception of the ICM and CRM, can be configured to run as many processes as needed, as well as the time and days a manager can process requests. However, the number of processes needed is dependent on each organization's environment.

An Applications DBA must monitor the concurrent processing in order to decide how to configure each manager. For a fresh install of the applications, initially configure the standard manager to run with five processes, and all the other managers with two processes. After the applications have been in operation for a while, the concurrent managers should be monitored to determine is more operating system process should be allocated.

Purging Concurrent Requests

One important area of Concurrent Manager tuning is monitoring the space usage for the subsets within each concurrent manager. When the space in FND_CONCURRENT_PROCESSES and FND_CONCURRENT_REQUESTS exceed 50K, you can start to experience serious performance problems within your Oracle Applications.

When you experience these space problems, a specific request called "Purge Concurrent Requests And/Or Manager Data" should be scheduled to run on a regular basis. This request can be configured to purge the request data from the FND tables as well as the log files and output files on accumulate on disk.

Adjusting the Concurrent Manager Cache Size

Concurrent manager performance can also be enhanced by increasing the manager cache size to be at lease twice the number of target processes. The cache size specifies the number of requests that will be cached each time the concurrent manager reads from the FND_CONCURRENT_REQUESTS table. Increasing the cache size will boost the throughput of the managers by attempting to avoid sleep time.

Analyzing Oracle Apps Dictionary Tables for High Performance

It is also very important to run the request Gather Table Statistics on these tables:

• FND_CONCURRENT_PROCESSES
• FND_CONCURRENT_PROGRAMS
• FND_CONCURRENT_REQUESTS
• FND_CONCURRENT_QUEUES.

Run the request "Analyze All Index Column Statistics" on the indexes of these tables. Since the APPLSYS user is the owner of these tables, so you can also just run the request Analyze Schema Statistics for APPLSYS.

To troubleshoot performance, a DBA can use three types of trace.

A module trace, such as PO or AR, can be set by enabling the module's profile option Debug Trace from within the applications.

Second, most concurrent requests can be set to generate a trace file by changing the request parameters. To enable trace for a specific request, log in as a user with the System Administrator responsibility. Navigate to Concurrent -> Program -> Define. Query for the request that you want to enable trace.

Another popular way to troubleshoot the Concurrent Managers is to generate a trace file. This is done by setting the OS environment variable FNDSQLCHK to FULL, and running the request from the command line.

Monitoring Pending Requests in the Concurrent Managers

Occasionally, you may find that requests are stacking up in the concurrent managers with a status of "pending". This can be caused by any of these conditions:

1. The concurrent managers were brought down will a request was running.
2. The database was shutdown before shutting down the concurrent managers.
3. There is a shortage of RAM memory or CPU resources.

When you get a backlog of pending requests, you can first allocate more processes to the manager that is having the problem in order to allow most of the requests to process, and then make a list of the requests that will not complete so they can be resubmitted, and cancel them.

To allocate more processes to a manager, log in as a user with the System Administrator responsibility. Navigate to Concurrent -> Manager -> Define. Increase the number in the Processes column. Also, you may not need all the concurrent managers that Oracle supplies with an Oracle Applications install, so you can save resources by identifying the unneeded managers and disabling them.

However, you can still have problems. If the request remains in a phase of RUNNING and a status of TERMINATING after allocating more processes to the manager, then shutdown the concurrent managers, kill any processes from the operating system that won't terminate, and execute the following sqlplus statement as the APPLSYS user to reset the managers in the FND_CONCURRENT_REQUESTS table:

update fnd_concurrent_requestsset status_code='X', phase_code='C'where status_code='T';

Changing Dispatching Priority within the Concurrent Manager

If there are requests that have a higher priority to run over other requests, you can navigate to Concurrent --> Program --> Define to change the priority of a request. If a priority is not set for a request, it will have the same priority as all other requests, or it will be set to the value specified in the user's profile option Concurrent:Priority. Also, you can specify that a request run using an SQL optimizer mode of FIRST_ROWS, ALL_ROWS, RULE, or CHOOSE, and this can radically effect the performance of the SQL inside the Concurrent request.

If several long running requests are submitted together, they can cause fast running requests to have to wait unnecessarily. If this is occurring, try to schedule as many long running requests to run after peak business hours. Additionally, a concurrent manager can be created to run only fast running requests.

Oracle supplies several useful scripts, (located in $FND_TOP/sql directory), for monitoring the concurrent managers:

afcmstat.sql : Displays all the defined managers, their maximum capacity, pids, and their status.

afimchk.sql : Displays the status of ICM and PMON method in effect, the ICM's log file, and determines if the concurrent manger monitor is running.

afcmcreq.sql : Displays the concurrent manager and the name of its log file that processed a request.

afrqwait.sql : Displays the requests that are pending, held, and scheduled.

afrqstat.sql : Displays of summary of concurrent request execution time and status since a particular date.

afqpmrid.sql : Displays the operating system process id of the FNDLIBR process based on a concurrent request id. The process id can then be used with the ORADEBUG utility.

afimlock.sql : Displays the process id, terminal, and process id that may be causing locks that the ICM and CRM are waiting to get. You should run this script if there are long delays when submitting jobs, or if you suspect the ICM is in a gridlock with another oracle process.

Oracle Applications 11i File system

An Oracle Applications Release 11i system utilizes components from many Oracle products. These product files are stored below a number of key top-level directories on the database and application server machines. Depending on how you chose to install Applications, these product directories may be located on a single machine (the simplest case) or on multiple machines (the most common type of deployment).

Operating system environment settings indicate the location of the various files in the file systems of the database and application server machines.

This topic discusses the association between these environment settings and the corresponding files and directories.

Note: is the name of your system determined through Rapid Install at the time of installation. For example, PROD.

• The DATA or DATA_TOP directory is located on the database server machine, and contains the system tablespaces; redo log files, data tablespaces, index tablespaces, and database files.
• The DB directory is located on the database server machine, and contains the ORACLE_HOME for the Oracle9i database.
• The APPL or APPL_TOP directory contains the product directories and files for Oracle Applications.
• The ORA directory contains the ORACLE_HOMEs for the Applications technology stack components.
• The COMN or COMN_TOP (or COMMON_TOP) directory contains directories and files used across products.

DATA Directory

The DATA file system contains the data (.dbf) files of the Oracle database. Rapid Install installs the system, data, and index files in directories below several mount points on the database server. You can specify the directory names of the mount points on the database server during installation.

DB and ORA Directories

Oracle Applications supports employing an Applications database of one version, while linking Applications programs using the tools from a second or third version of the database. This multiple ORACLE_HOMEs configuration allows new features of the database to be supported, while maintaining compatibility with earlier releases. Release 11i utilizes three ORACLE_HOMEs:

1. The 9.2.0 ORACLE_HOME (Applications database home) is located in the DB directory. It contains the files needed for running and maintaining the Oracle Applications database.
2. The 8.0.6 directory contains the ORACLE_HOME for the Developer 6i products (Forms, Reports, and Graphics). The product libraries in the 8.0.6 ORACLE_HOME are used to relink Oracle Applications executables.
3. The iAS directory, also under the ORA, contains the ORACLE_HOME for Oracle9i Application Server.

APPL Directory

Oracle Applications files are stored in the APPL directory, which is known as the APPL_TOP directory.
The APPL_TOP directory contains:

1. The core technology files and directories.
2. The product files and directories (for all products).
3. The main applications environment file, called .env on UNIX, and .cmdon Windows.
4. The consolidated environment file, called APPS.env on UNIX, and APPS.cmd on Windows.

Product Directories

Each product has its own subdirectory under APPL_TOP. The subdirectories are named in accordance with the product’s standard abbreviation, such as gl for Oracle General Ledger. Within each product directory is a subdirectory that is named using the base Oracle Applications release number, such as 11.5.0. This directory contains the various subdirectories for the product files.

COMN Directory

The COMN or COMMON_TOP directory contains files used by many different Oracle Applications products, and which may also be used with third-party products.

ADMIN Directory

The admin directory, under the COMMON_TOP directory, is the default location for the concurrent manager log and output directories. When the concurrent managers run Oracle Applications reports, they write the log files and temporary files to the log subdirectory of the admin directory, and the output files to the out subdirectory of the admin directory.

HTML Directory

The OA_HTML environment setting points to the html directory. The Oracle Applications HTML-based sign-on screen and Oracle HTML-based Applications HTML files are installed here. The html directory also contains other files used by the HTML-based products, such as JavaServer Page (JSP) files, Java scripts, XML files, and style sheets. Rapid Install and the AD utilities copy the HTML-based product files from each _TOP directory to subdirectories in the html directory.

JAVA Directory

The JAVA_TOP environment setting points to the java directory. Rapid Install installs all Oracle Applications JAR files in the Oracle namespace of this JAVA_TOP directory. The java directory also holds third-party Java files used by Oracle Applications, as well as other zip files.

PORTAL Directory

The portal directory contains the Rapid Install Portal files. The Rapid Install Portal is a web page that provides access to post-install tasks that may be necessary for your installation, plus server administration scripts, installation documentation, and online help. Using a browser, you can view the Rapid Install Portal after you run Rapid Install.

TEMP Directory

The temp directory is used for caching by some products such as Oracle Reports.

UTIL Directory

The util directory contains the third-party utilities licensed to ship with Oracle Applications. These include, for example, the Java Runtime Environment (JRE), Java Development Kit (JDK), and the Zip utility.

SCRIPT Directory

The scripts directory contains application tier control scripts such as adstrtal.sh and adstpall.sh, which are located in the subdirectory.

Oracle Application Concepts

The Oracle Applications Architecture, as shown in figure 1 below, is a framework for multi-tiered, distributed computing that supports Oracle Applications products. In this model, various servers are distributed among multiple levels, or tiers.

A server is a process or group of processes that runs on a single machine and provides a particular functionality, often referred to as a service. For example, the HTTP server is a process that listens for and processes HTTP requests, and the Forms server is a process that listens for and processes requests for activities related to Oracle Forms.

A tier is a logical grouping of services, potentially spread across more than one physical machine. The three-tier architecture that comprises an Oracle E-Business Suite installation is made up of the database tier, which supports and manages the Oracle database; the application tier, which supports and manages the various Applications components, and is sometimes known as the middle tier; and the desktop tier, which provides the user interface via an add-on component to a standard web browser.

The Desktop Tier

The client interface is provided through HTML for the newer HTML-based applications, and via a Java applet in a Web browser for the traditional Forms-based interface. In Oracle Applications Release 11i, each user logs in to Oracle Applications through the E-Business Suite Home Page on a desktop client web browser. The E-Business Suite Home Page provides a single point of access to HTML-based applications, Forms-based applications, and Business Intelligence applications.

a) Forms Client Applet

The Forms client applet is a general-purpose presentation applet that supports all

Oracle Applications forms-based products, including those with customizations and extensions. The Forms client applet is packaged as a collection of Java Archive (JAR) files. The JAR files contain all Java classes required to run the presentation layer of Oracle Applications forms.

b) Oracle JInitiator

The Forms client applet must run within a Java Virtual Machine (JVM) on the desktop client. The Oracle JInitiator component allows use of the Oracle JVM on web clients, instead of the browser’s own JVM. JInitiator is implemented as a plug-in (Netscape) or ActiveX component (Microsoft Internet Explorer). JInitiator is only invoked when a user chooses to access functions that require it, such as running a form. If JInitiator has not been installed, the Web browser prompts the user to download the required installation executable.

The Forms client applet and commonly used JAR files are downloaded from the Web server at the beginning of the client’s first session. Less commonly used JAR files are downloaded as needed. The JAR files are cached in a directory specified when JInitiator was installed.

The Application Tier

The application tier has a dual role: hosting the various servers that process the business logic, and managing communication between the desktop tier and the database tier. This tier is sometimes referred to as the middle tier. Oracle9i Application Server (9iAS) provides the technology used on the application tier. Six servers comprise the application tier for Oracle Applications:

a) Web server

The Oracle HTTP server (powered by Apache) acts as the Web server. It processes the requests received over the network from the desktop clients, and includes additional components such as:

- Web Listener
- Java Servlet Engine
- JavaServer Pages (JSP)

The Web listener component of the Oracle HTTP server accepts incoming HTTP requests (for particular URLs) from client browsers.

b) Forms Server

The Forms server hosts the Oracle Applications forms and associated runtime engine that support the professional interface. It is an Oracle Developer 6i component that mediates the communication between the desktop client and the Oracle database server, displaying client screens and initiating changes in the database according to user actions.

The Forms server caches data and provides it to the client as needed, for example when scrolling through multiple order lines that exceed the limitations of a single screen.

c) Reports Server

The Reports server is automatically installed on the same node as the Concurrent Processing Server and its reports are contained in the same directory as the concurrent processing reports. However, reports generated by the Reports server are monitored and administered separately from concurrent processing reports.

d) Discoverer Server (optional)

The Discoverer server comprises Oracle Discoverer 4i, a key component of the Oracle9i Application Server (9iAS). Discoverer 4i is tightly integrated with Oracle E-Business Suite Release 11i, allowing E-Business Suite users to employ Discoverer to analyze data from selected business areas in Financials, Operations, Human Resources, Purchasing, Process Manufacturing, Activity Based Management, and other products. The Discoverer server complements the Reports server, by allowing performance of ad hoc queries and analysis of the resulting query output. It also allows users to perform projections based on possible changes to the business environment or other strategic factors.

e) Concurrent Processing Server

There are various reporting programs and data updating programs in Oracle Applications that need to run either periodically, or on an ad hoc basis. These programs, which operate in the background while users continue to work on other tasks, may contain a very large number of data-intensive computations, and are run using the concurrent processing architecture. To ensure that resource-intensive concurrent processing operations do not interfere with interactive operations, they are run on a specialized server, the Concurrent Processing server.

Processes that run on the concurrent processing server are called concurrent requests. When you submit such a request, either through HTML-based or Forms-based Applications, a row is inserted into a database table specifying the program to be run. A concurrent manager then reads the applicable requests in the table, and starts the associated concurrent program. The Internal Concurrent Manager service monitors the database table for new requests, controls the other concurrent managers, and determines when a request should be processed and which concurrent manager should carry it out.

f) Admin Server

The Admin (Administration) server is located on the node where you maintain the data model and data in your Oracle Applications database. You carry out the following operations from this server:

- Upgrading Oracle Applications

This process is conducted only when you are upgrading to a new release from an earlier one, such as 10.7 or 11.0. You use the AutoUpgrade utility (adaimgr) to upgrade Oracle Applications.

- Applying database patches to Oracle Applications

In general, Applications patches consist of files and scripts that update the file system and database objects. You use the AutoPatch utility (adpatch) to perform these updates. AutoPatch may alsobe used to apply cumulative patches such as mini-packs (for example, 11i.AD.H) and maintenance packs (for example, 11.5.10).

Some features, such as Multiple Reporting Currencies, require regular maintenance to ensure that updates are propagated to the additional tables and schemas they use. The AD Administration utility (adadmin) enables you to carry out this and various other file system and database maintenance tasks.

The Database Tier

The database tier contains the Oracle database server, which stores all the data maintained by Oracle Applications. The database also stores the Oracle Applications online help information. More specifically, the database tier contains the Oracle data server files and Oracle Applications database executables that physically store the tables, indexes, and other database objects for your system.

In general, the database server does not communicate directly with the desktop clients, but rather with the servers on the application tier, which mediate the communications between the database server and the clients.

Oracle Applications Login

Before you connect to Oracle Applications you must know the URL of the Oracle Applications server you want to connect to. Clicking on the URL would lead you to the Oracle Applications Login Page as shown in figure 1 below.

Oracle Applications Login Page

Choosing Responsibility

To connect to the Oracle Applications, you must have an Oracle Apps login ID created by the System Administrator of the Oracle Applications instance.

Enter the appropriate Login ID and password and click login. If the login is successful it will lead you to the responsibility page as shown in figure 2 below. This page will show all the responsibilities that have been assigned to your Login ID by the System Administrator.

List of Responsibilities

Choosing Function / Opening Form

Once you click on a responsibility, it will show you the menus and functions associated with the responsibility. Click on the Sys Admin, Vision Services (USA) responsibility. The page will look like as shown in the figure 3 below.

Menus and Functions attached to a responsibility

Each function under a menu opens a form when clicked upon. The forms are basically an interface between you and the applications for various functionalities. These forms are run on Oracle Jinitiator. Once you click on a function, the jinitiator is invoked and it then opens the respective form. If you are login for the first time, Oracle Applications automatically downloads and installs the jinitiator for you. It, therefore, takes a longer time to open the form for the first time. Once the jinitiator is installed, it opens an intermediate window (figure 4) before the form is opened. Please do not close this window as it will close the Oracle Applications session. Wait till the form window opens up automatically. The intermediate page would go to the background.

Intermediate Window

Forms in Applet Mode

Keyboard Shortcuts

The same process can be used to query data on any form in Oracle Applications. You can also use the keyboard shortcuts for entering and running the query. For example, F11 key puts the form in the query mode and Ctrl + F11 runs the query. You can get the list of the complete key board shortcuts by clicking on Help->Keyboard Help as shown in the figure 14 below

Oracle E- Business Suite - Industry Applications

Oracle E-Business Suite

Oracle E-Business Suite is the industry's only complete and integrated set of enterprise applications, working together seamlessly to streamline every area of your business—from sales, service, and marketing, through financials and human resources, to supply chain and manufacturing. Oracle E-Business Suite is your fastest path to high-quality enterprise intelligence, bringing your company a true 360-degree view of your finances, your customers, and your supply chains, so you can make faster, better decisions and grow profitability in a competitive marketplace.

Application software typically automates only departmental business processes. Oracle E-Business Suite is different; it automates all parts of your business. From developing, marketing, selling, ordering, planning, procuring, manufacturing, fulfilling, servicing, and maintaining, to handling finance, human resources, and project management—Oracle E-Business Suite provides a comprehensive and integrated offering. In the past, you had to choose between an integrated suite and “best of breed” for rich functionality. With Oracle, you can now have an integrated suite built on unified information architecture—with the functionality you need in each individual application. These applications connect business processes within and across departmental, geographical, and line-of-business domains. With Oracle E-Business Suite’s depth of product functionality and breadth of product offering, you can take your business further by automating processes across the enterprise.

Oracle E-Business Suite 11i.10 offers over 2,100 new capabilities, half of which meet specific industry needs, including:

Financial Services: SOP documentation and auditing for compliance with Sarbanes-Oxley and other regulations

Healthcare: Medication administration, patient encounter-specific financial information, integrated patient care and operational intelligence.

Manufacturing/High Technology: Option-dependent sourcing, automated spare parts return and repair processing, international drop shipments, distribution planning .

Enterprise Resource Planning

What is ERP?

Enterprise Resource Planning or ERP is an industry term for integrated, multi-module application software packages that are designed to serve and support multiple business functions. An ERP system can include software for manufacturing, order entry, accounts receivable and payable, general ledger, purchasing, warehousing, transportation and human resources. Evolving out of the manufacturing industry, ERP implies the use of packaged software rather than proprietary software written by or for one customer. ERP modules may be able to interface with an organization's own software with varying degrees of effort, and, depending on the software, ERP modules may be alterable via the vendor's proprietary tools as well as proprietary or standard programming languages.

Brief History of ERP

The focus of manufacturing systems in the 1960's was on Inventory control. Most of the software packages then (usually customized) were designed to handle inventory based on traditional inventory concepts. In the 1970's the focus shifted to MRP (Material Requirement Planning) systems that translated the Master Schedule built for the end items into time-phased net requirements for the sub-assemblies, components and raw materials planning and procurement.

In the 1980's the concept of MRP-II (Manufacturing Resources Planning) evolved which was an extension of MRP to shop floor and Distribution management activities. In the early 1990's, MRP-II was further extended to cover areas like Engineering, Finance, Human Resources, Projects Management etc i.e. the complete gamut of activities within any business enterprise. Hence, the term ERP (Enterprise Resource Planning) was coined.

Why is it Necessary?

By becoming the integrated information solution across the entire organization, ERP systems allow companies to better understand their business. With ERP software, companies can standardize business processes and more easily enact best practices. By creating more efficient processes, companies can concentrate their efforts on serving their customers and maximizing profit.
Market Leaders

The top five ERP vendors, SAP, Oracle Corporation, Peoplesoft, Inc. (now Oracle Corp.), JD Edwards & Company, and Baan International, account for 64 percent of total ERP market revenue. These vendors continue to play a major role in shaping the landscape of new target markets, with expanded product functionality, and higher penetration rates. SAP dominates the $6.7 billion ERP applications market in Europe with 39% market share. Oracle and PeopleSoft come second and third respectively, followed by SAGE Group and Microsoft Business Solutions.

The Future of ERP

Industry analysts expect that every major manufacturing company will buy the software, which ranges in cost -- with maintenance and training -- from hundreds of thousands of dollars for a small company to millions for a large company. AMR Research of Boston says consolidation among the major players will continue and intensify. ERP vendors are expected to put more effort into e-commerce, CRM and SCM initiatives, with leaders redirecting between 50% and 75% of their R&D budget to these projects.

According to Gartner research group, the rapid evolution of ERP has already lead to a new corporate must-have, ERP II, which is supposed to help businesses gain more competitive edge in the future. The major difference is that ERP II involves collaborative commerce, which enables business partners from multiple companies to exchange information posted on eCommerce exchanges.