Oracle Discoverer Query Prediction functionality and Performance

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: SELECT QS_ID, QS_COST, QS_ACT_CPU_TIME,        QS_ACT_ELAP_TIME, QS_EST_ELAP_TIME,        QS_CREATED_DATE, QS_OBJECT_USE_KEY,        QS_NUM_ROWS,QS_STATE FROM [EUL_USER].EUL5_QPP_STATS WHERE  QS_COST IS NOT NULL AND    QS_OBJECT_USE_KEY = :OBJECTUSEKEY ORDER BY QS_CREATED_DATE DESC 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 (10.1.2.1) 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. Great! 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: SELECT QS_ID, QS_COST, QS_ACT_CPU_TIME,        QS_ACT_ELAP_TIME, QS_EST_ELAP_TIME,        QS_CREATED_DATE, QS_OBJECT_USE_KEY,        QS_NUM_ROWS,QS_STATE FROM [EUL_USER].EUL5_QPP_STATS WHERE  QS_COST IS NOT NULL AND    QS_OBJECT_USE_KEY = :OBJECTUSEKEY ORDER BY QS_CREATED_DATE DESC 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 (10.1.2.2) 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. Aviad

Agile PLM 9.2.2.1 – Part III – Application Node Installation

This is the 3rd (and last..) post about Oracle Agile 9.2.2.1 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 9.2.2.1 – Part I - Agile PLM 9.2.2.1 – Part II – Database Node Installation The Agile Application node installation composed from the following steps: - Complete all application installation pre requisites - Oracle Application Server 10.1.2.0.2 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 Prerequisites 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 10.1.2.0.2 installation, otherwise you might get into port conflict between IIS and Oracle AS 10.1.2.0.2. Oracle Application Server 10.1.2.0.2 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 (10.1.2.0.2) 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. Aviad