Aviad Elbaz

Subscribe to Aviad Elbaz feed
Updated: 9 hours 58 min ago

Oracle Discoverer Query Prediction functionality and Performance

Tue, 2008-01-22 12:21

Lately we noticed that our Discoverer reports runs very slow.
Actually, the problem wasn’t the Discoverer reports query but the query prediction that for some reason took so long.

What is Query Prediction in Discoverer?

“Discoverer includes functionality to predict the time required to retrieve the information in a Discoverer query.
The query prediction appears before the query begins, enabling Discoverer users to decide whether or not to run the query.
This is a powerful facility that enables Discoverer users to control how long they wait for large reports.”
(from Oracle doc’)

The query prediction is the elapsed time while the following message appeared on the bottom left of Discoverer Desktop window: "Determining query time estimate".

For each report we tested, we found query prediction runs 30%-50% (!!!) from the report’s total run time.

Next phase was to start a SQL trace on Discoverer session to see what actually happens when running a Discoverer report.

This is the relevant section from the SQL Trace:


As you can see, the query prediction functionality tries to retrieve statistics information from EUL5_QPP_STATS and it takes 35 seconds. (total time for this report is 55 seconds).

The query prediction based, among other things, on the query prediction statistics table – EUL5_QPP_STATS.
This table records query prediction statistics while running Discoverer reports.

There is no logic by estimating query run time longer than the report’s query itself…

Since the query prediction functionality is important to our users we avoid from disable this functionality (by setting the QPPEnable to 0).
Furthermore, I found that we have statistics data in this table from 7 years ago… 
There is no meaning to hold these statistics…

I tried to find information about purging the EUL5_QPP_STATS and I found this: “How to delete old query prediction statistics” in Oracle® Business Intelligence Discoverer Administration Guide 10g Release 2 (

There is a SQL script at [ORACLE_ HOME]\discoverer\util\eulstdel.sql – that deletes all query prediction statistics that were created before a specified date.

I executed this sql on my database, gave 90 days as a parameter and it deleted 460,000 (from 468,000) rows.
I ran a Discoverer report again, but still query prediction takes too long, same like before.
I checked the explain plan and the cost of the above SQL and it remains the same.
I tried to gather statistics on EUL5_QPP_ENABLE table and rebuild its indexes but cost become higher… (More than 103, something like 800…).

I had no choice but rebuild the EUL5_QPP_ENABLE table (by export, drop table and import).

After recreation of EUL5_QPP_STATS table I ran a Discoverer report again and query prediction takes insignificant time, almost nothing…  :-)

This is from the trace I took after:


The elapsed time for this sql reduced to 0.05 seconds!! (was 35 sec’ before)

Sql cost reduced from 103 to 31!

I checked this issue on Discoverer Desktop 10g ( but it is relevant to the web tools (Discoverer viewer and Discoverer Plus) as well, since the query prediction functionality exist in these tools like in the client version.

You are welcome to leave a comment.


Categories: APPS Blogs

Agile PLM – Part III – Application Node Installation

Wed, 2008-01-09 08:48
This is the 3rd (and last..) post about Oracle Agile installation.
In this post we will see the Agile application node installation step by step including all Agile application required components.

Previous related posts:
- Agile PLM – Part I
- Agile PLM – Part II – Database Node Installation

The Agile Application node installation composed from the following steps:
- Complete all application installation pre requisites
- Oracle Application Server Installation
- Oracle Applications Server Patch
- Agile Application Installation
- Agile Viewer Installation
- Deploy Agile application on Oracle Application Server
- Verify Installation
- Configure IIS as a Proxy Server for Agile PLM
- Configure File Manager with IIS
- Verify File Manager installation


1) Copy Platform directory from Disk2 to Disk1 to the setup.exe level
2) Make sure Microsoft IIS (Internet Information Services) is installed on this box.

*** It is important to install MS IIS before proceeding with the Oracle AS installation, otherwise you might get into port conflict between IIS and Oracle AS

Oracle Application Server Installation

1) Run installer - setup.exe (from Disk1 directory)

2) Oracle Home destination:
a. Name: oracleas1
b. Path: d:\OraHome_1

3) Language: Choose the appropriate languages.

4) Check the Administrative privileges

5) At Select Configuration Options window, leave the upper 2 options checked only

6) Port configuration: Automatic

7) Fill in the Instance name and ias_admin password:

8) Install

9) Exit

10) Shutdown Oracle AS:
a. Open a cmd window
b. cd oraHome_1\bin
c. emctl stop iasconsole
d. opmnctl stopall

Oracle Applications Server Patch Installation

1) Open a cmd window
a. set ORACLE_HOME=d:\OraHome_1
b. cd [Installation Dir]\Windows\patches\oas101202\OPatch
c. opatch apply d:\ [Installation Dir]\Windows\patches\oas101202\OPatch\3992805

2) Type "Y" (for : Is this system ready …?)

Agile Application Installation

1) From Disk1: cd [Installation Dir]\Windows
2) Execute setup.exe

3) Accept the license agreement
4) Enter license & username

5) Select Applications Server + File Manager + Web Proxies

6) Location to install Agile application: D:\agile\Agile9221

7) Select Oracle Application Server 10g (

8) Select Standalone Installation

9) Enter Oracle Application Server Home directory: d:\OraHome_1

10) Click on Use Existing

11) Choose: No, use a Database for authentication

12) Hostname: agileapp.[domain]

13) Web Server information: agileapp.[domain]:80

14) Agile viewer information: agileapp.[domain]:5099

15) Database details:
a. Agile Database Host Name: agiledb
b. Agile Database Port: 1521
c. Agile Database SID: agile9
d. Agile Database User: agile

16) Virtual path: Agile

17) At File Manager User Authentication window select: Use Internal user account

18) File Manager Virtual Path: Filemgr

19) Agile File Manager window: agileapp.[domain]:80

20) Agile File Manager Storage Location: e:\agile\agile9221\files

21) Select to create product icons in an new Program Group called: Agile

22) Install…

23) Restart the system

Agile Viewer Installation

1) From Agile Viewer installation directory execute: setup_win.exe

2) Accept the license agreement
3) Enter User name and License key
4) Check the Agile Viewer only

5) Select New Install

6) Location: d:\Agile\Agile9221

7) Select Regular Agile Viewer

8) Enter hostname & port: agileapp.[domain]:5099

9) Done

Deploy Agile application on Oracle Application Server

1) cd OraHome_1\opmn\bin
a. Stop all Oracle AS processes - opmnctl stopall
b. Start all Oracle AS processes - opmnctl startall
c. cd d:\agile\agile9221\agileDomain\bin
d. Execute command: DeployAgile

2) Verify deployment
a. cd \OraHome1\dcm\bin
b. dmctl listapplications

Verify Installation

1) Run in browser the following url: http://agileapp:7777/Agile/PLMServlet
2) Connect with admin user

Configure IIS as a Proxy Server for Agile PLM

1) Navigate to: Control Panel -> Administrative tools -> Internet Information Services (IIS) Manager

2) Right click on “Default Web Site” (under Web Sites) -> properties
3) Select the “Home Directory” tab
4) In the “Execute permissions” list, select “scripts and executables”

5) Select the “ISPAI filter” tab -> add
a. Filter Name: oproxy
b. Executable: D:\Agile\Agile9221\AgileProxies\oracle_proxy.dll

6) Right click on Default Web Site-> new -> Virtual directory

7) Alias: oproxy

8) Path: d:\agile\agile9221\AgileProxies

9) Check the read and execute options

10) Finish.
11) Navigate to “Web service extension” -> select: “all unknown ISAPI extensions and Click “Allow”

12) Navigate to: Control Panel -> Administrative tools -> Services
13) Restart the “IIS Admin Service”
14) Run in browser: http://agileapp/Agile/PLMServlet (without port 7777)
15) Logon with admin user to verify IIS configuration.

Configure File Manager with IIS

1) Edit d:\agile\agile9221\Tomcat\conf\server.xml
2) Look for the port after the following text:
!-- Define a Coyote/JK2 AJP 1.3 Connector on port 8009 --

3) Edit file jk2.properties -> channelSocket.port=8009 (the previous port)
4) Navigate to: Control Panel -> Administrative tools -> IIS Manager
5) Go to ISPAI Filter tab -> add
a. name: Jakarta IIS Connector
b. Executable: D:\Agile\Agile9221\AgileProxies\isapi_redirect.dll
6) Right click on default web site-> new -> Virtual directory
a. Alias : Jakarta
b. Path: d:\agile\agile9221\agileproxies
7) Check the read and executable options
8) Restart IIS Admin Service again.

Verify File Manager installation

1) Startup tomcat server by: d:\agile\agile9221\tomcat\bin\catalina start
2) Open the following url in browser to check Java installation on client: http://agileapp/JavaClient/start.html

3) In order to use the Agile java client we should install Java JRE 1.5.x
4) Open the following url again: http://agileapp/JavaClient/start.html
5) Click on Launch
6) Login with admin user.
7) Navigate to: Server setting -> locations
Verify all locations (especially under the File Manager tab)

Now when the Agile application node installed, the Agile system are ready for use.
If you have an initial dump file to export, you can do it now with agile9imp.bat script.

For more information:
Installing Agile PLM for OAS

You are welcome to leave a comment for any issue or additional information.


Categories: APPS Blogs

Agile PLM – Part II – Database Node Installation

Mon, 2007-12-24 01:51
Following my last post about Agile PLM – Part I, in this post I’ll show, step by step, how to install Agile PLM system.

The latest version of Agile PLM is, but it's too much new to install it for production..

I installed the Agile PLM on 2 nodes configuration:

1) Agiledb – Oracle Database server Node
C:\ drive – OS only
D:\ drive – Database 10g software and Datafiles

2) Agileapp – Agile Application + IIS + Oracle AS + Agile File Manager Node
C:\ drive – OS only
D:\ drive – Agile applications + Oracle AS
E:\ drive – File Vault (managed by the File Manager)

** Both servers installed with Windows 2003 Server OS.

This post will describe all phases of database node installation, and next post I’ll describe the application node installation.

The Agile Database node installation composed from the following steps:
- Complete all database installation pre requisites
- Oracle Database installation
- Oracle Datbase companion installation
- Agile Database creation
- Listener configuration

Database Node Installation - Pre Requisites

1) If server is DHCP configured you should setup a loopback (10g installation requirements)
a. Control panel -> Add hardware

b. Choose: Yes, I have already connected the hardware

c. Choose: Add a new hardware device (the last item on list)

d. Choose: Install the hardware that I manually select from a list

e. Choose: Network adapters

f. Choose at the left side: Microsoft at right side choose: Microsoft loopback adapter

g. Edit the server host file at c:\windows\system32\driver\etc\hosts
Add the following line: agiledb.domain agiledb

h. Navigate to network connections: Start -> settings -> Network Connections
i. Right Click on Local Area Connection – Microsoft Loopback Adapter -> properties

ii. Select TCP/IP -> properties

iii. Edit the ip address to
iv. Edit the subnet mask to

2) Restart the server

Oracle Database installation

1) Run database installer – setup.exe (from database directory)
2) Select Basic installation – Standard Edition
a. Oracle Home Location: d:\oracle\product\10.2.0\db_1
b. Uncheck the Create Starter Database

3) Review prerequisite checks

4) Click on Install…

5) Exit

Database Companion Installation

1) Run the installer – setup.exe (from companion directory)

2) Select Oracle Database 10g Products

3) Specify Home details:
a. Name: OraDb10g_home1
b. Path: D:\oracle\product\10.2.0\db_1

3) Review prerequisite checks

4) Install…

5) Exit

Agile Database Creation

1) Copy the “Platform” directory from Agile Disk2 directory to Agile Disk1 directory to the setup.exe level

2) Run [Disk1]:\windows\setup.exe

3) Accept license agreement
4) Enter the license key and user name.

5) Select : Database Server only

6) Enter the location to install the selected Agile components:

7) Select Oracle 10g Database Server

8) Install

9) Destination folder for database customization files: d:\Agile9Tmp

10) Select “Medium” Database size
For more information regarding each possibility - small, medium, large... - read the Capacity Planning Guide

11) Select the previous installed oracle home

12) Oracle SID: agile9

13) Enter passwords for Sys and System and user name and password for Agile schema.

14) Click some next’s…

15) Agile installation will create the database

16) Done

Configure Database Listener

1) Navigate to: Start Menu -> Programs -> Oracle - OraDb10g_home1 -> Configuration and Migration Tools -> Net Manager

2) Create new Listener, Name: Listener

3) Select Database Services from the drop down list
a. Click on Add database
b. Global Database Name: Agile9
c. Oracle Home Directory: d:\oracle\product\10.2.0\db_1
d. SID: agile9

4) File -> Save Network Configuration
5) Restart the Listener
a. Lsnrctl stop
b. Lsnrctl start

That’s all for the Agile Database node.
Next post I’ll show the Agile Application node installation.

For more information:
Capacity Planning Guide
OracleAgile Database Installation Guide

You are welcome to leave a comment.
Categories: APPS Blogs

Agile PLM - Part I

Thu, 2007-12-20 02:27
In May this year, Oracle has acquired Agile, a leading provider of Product Lifecycle Management (PLM) software solutions.

In this post I will explain about the various components of Agile PLM system, and the following posts will be dedicated to Agile PLM installation.

The components of Agile PLM application are:
1) Agile Database
2) Agile Application Server
3) Agile File Manager
4) Agile Web Proxies
These components should be installed in the above order.

Agile Database
Used to hold all Agile data.
It is recommended to install the database and application server on separated machines.

Agile Application Server
The Agile Application Server can be run on Oracle Application Server 10g or BEA WebLogic Server.
The Application Server is the main component of the Agile system, all services and business logic reside on it.
The Agile application deployed on the Application Server.
All users (Java/Web clients) connect to the Application Server in 2 possible ways:
- Directly
- Indirectly, via Wev proxy Server.

Agile File Manager
The File Manager manages all Agile files in the file system (File Vault).
The File Manager runs on Tomcat.

Agile Web Proxies Server
The Agile web client allows connection to both internal and external users.
Agile web client uses Microsoft IIS (Internet Information Services) or Apache web server.
The Agile web client connects to an Agile Application Server the same way like other Agile Application Server clients.

Next post I will show, step by step, how to install Agile PLM 9.2.21 on two nodes configuration.

Categories: APPS Blogs

Data Auditing in Oracle Applications - Audit Trail

Wed, 2007-11-28 02:01
Following my last post about Audit users in Oracle Applications, in this post I’m going to talk about the Audit Trail - the data auditing feature in Oracle Applications.
The AuditTrail enable us to know who, when and what was changed on each table we would like to audit.
For example: Some of the profiles in Oracle Applications are very critical and important for normal activity of the system (like MO: Operating Unit).
We might be interested to know who changed the value of critical profiles and the Audit Trail certainly can help us.

Now I’ll show a step by step demo how to start Audit Trail on a table, I will go on with my previous example on the profiles table.
The table we would like to audit is FND_PROFILE_OPTION_VALUES.

1) Logon to system with System Administrator responsibility.

2) Make sure the profile “AuditTrail:Activate” is set to Yes

3) Check the audited table's owner
select owner
from dba_tables
where table_name like 'FND_PROFILE_OPTION_VALUES';

The owner is APPLSYS.

4) Check that audit is enabled for APPLSYS user
Go to: Security -> AuditTrail -> Install -> Query for APPLSYS

5) Define new Audit Group
Since the audit enabled on groups of tables, we should define a new Audit Group which will contain the FND_PROFILE_OPTION_VALUES table.

Find the table’s application name by this query:
select fav.application_name
from fnd_application_vl fav, fnd_tables ft
where fav.application_id = ft.application_id
and ft.table_name = 'FND_PROFILE_OPTION_VALUES';

The query result: Application Object Library

Navigate to: Security -> AuditTrail -> Groups

Application: Application Object Library
Audit Group: AC FND Profile Values Audit
Group State: Enable Requested

At this level, the columns that will be audited are the columns of the primary key or the first unique index on the audited table.
You can add columns to be audited as much as you want.

6) Check/Add column to audit
Navigate to: Security -> AuditTrail -> Tables

Add column PROFILE_OPTION_VALUE to be audited.

7) Run the “AuditTrail Update Tables” concurrent to enable the audit
Navigate to: Requests -> Run -> Single Request -> choose request name: “AuditTrail Update Tables” -> Submit

All definitions we made until here will not take effect until we execute this concurrent.
This concurrent will create all objects (triggers and procedures) required for audit, and views to retrieve the audited data.

This request should be executed each time we make changes in audit definitions, to generate new audit objects.

To make sure it works, we can check if all audit objects were created:

All audit objects names will be the first 26 characters of the audied table + suffix (_A, _AC, _AD...).
We can also run report “AuditTrail Report for Audit Group Validation” to make sure all audit objects created successfully.

Now let’s make a test to see how it works:
To test the audit on the profile's table, we will change a value for a profile (any profile) and check the shadow table (fnd_profile_option_value_a) for an audit inormation.

Navigate to: Profile -> System , and update the profile “FND: Diagnostics” (just for testing... you can choose any profile) to Yes.

To see the audited data of the last change we can run this query:

The change was audited as expected...

As you can see, the “U” in the audit_transaction_type column indicates an update and the profile_option_value contain the value before update.

The audit_transaction_type could be:
1) U - update
2) D - delete
3) I - insert

How to disable Audit Trail?

The AuditTrail could be stopped by 3 ways:
1) Disable Prepare for Archive
2) Disable Interrupt Audit
3) Disable Purge Table

I've tried the third only… It deletes the data from the shadow table and drops all the audit objects from database.

1) Login to application and choose the System Administrator responsibility.
2) Security -> AuditTrail -> Groups
3) Query for your group
4) Update the Group State field to: “Disable – Purge Table”.
5) Run the “AuditTrail Update Tables” concurrent to make the changes.

That’s all about auditing…

You can read more at "Oracle Applications System Administrator’s Guide - Security Release 11i" - Chapter 5 - User and Data Auditing

To get my posts directly to your email, you can register for email subscription by using the box at the right side bar.

You are welcome to leave a comment.


Categories: APPS Blogs