Thursday, June 12, 2008

As you may know, there is a switch on the Privileges screen called Collect Query Statistics.

Most organizations leave this turned on. When it is turned on, every time an end user logs in and runs a worksheet, statistics about that run are collected into the table called EUL5_QPP_STATISTICS. Thus, there will be one row in the statistics tables for every execution of every worksheet in every workbook. Even if an end user creates only an ad-hoc query and doesn't save it to the database the statistics will still be captured.

If your organization has lots of users executing lots of worksheets every day you can imagine that this table will get quite large. Another thing that happens is that over time the extents on the indexes, of which there are 3, grow rapidly to many thousands. What this means is that performance is going downhill.

Here are the 3 indexes:

EUL5_QS1_I

EUL5_QS2_I

EUL5_QS_PK

You therefore want to consider purging your statistics from time to time. Many organizations that I have worked with like to keep at least the last six months of statistics, although some only keep 90 days.

What you may not be aware of is the fact that Oracle supply a SQL script that can check the statistics and, optionally, delete those which are over a number of days old. The script also provides a report on how many entries there are in the table, in buckets of 10 day increments, like this:

You can find the script here on your Admin machine:[ORACLE_HOME]\discoverer\utilIt is called: EULSTDEL.SQL

To run the script, use this workflow:

Log in to SQL Plus as the owner of the EUL

Type this @c:\oracle\bitoolshome_1\discoverer\util\eulstdel.sql and press Enter

You will be prompted to enter a parameter. This parameter is the number of days of statistics you want to keep. If you want to keep all of your statistics, enter a large number or just run the SQL at the end of this posting. If you enter 0 the script will delete all of your statistics because it actually deletes rows which have a created date less than SYSDATE minus this number. Therefore, be careful!

The SQL will run and produce the report, optionally deleting your old statistics

Note: in step 2 of the above workflow I have included the most-commonly user location for the Discoverer Admin tool. If yours is not in this location you will need to change this line.

Having deleted the statistics, you will more than likely want to ask your friendly DBA to rebuild the 3 indexes listed above, and again below. I have seen terrific improvements in performance when this has been done.

From time to time I am asked to provide scripts that help administrators manage their Discoverer system. One such example is below.

I was asked if I knew of a way for a company to know how many users they had using Discoverer so that they can make sure they are compliant with their licensing. The following script will do just that, plus a little more:

Note: there is a restriction to running this script in that it will only tell you about users who have their own workbooks and when they were last ran. If you are using Discoverer connections you will not be able to use these scripts because the workbook will not necessarily be owned by the user who owns the connection or, if using SSO, by the user who is logged in. I am still researching if there is a way to derive this information.

The date parameter is there to check users who have been on the system from that date. This way you can exclude reports that were run a long time ago. You can drop it altogether if you like. If you do this you will see the total number of users you have had running reports. Of course, this assumes that you are collecting query statistics and that you haven't purged them - which will be the subject of another posting.

The report will tell you who has run a report in Discoverer, how many reports the user has executed, when the user first logged in and when the user last logged in to run a report. Using this information you can determine how many licenses you need or just to see who are the most frequent Discoverer users on your system.

To accomplish the latter just add another clause. Let's say you only want to see users who have run at least 25 reports. All you need to do is this:

SELECT DISTINCTUPPER(QS.QS_DOC_OWNER) USERNAME,COUNT(QS_ID)REPORTS_RUN,MAX(QS.QS_CREATED_DATE) LAST_USED,MIN(QS.QS_CREATED_DATE) FIRST_USEDFROM EUL5_QPP_STATS QSWHEREQS.QS_CREATED_DATE > '01-JAN-2006' ANDQS.QS_DOC_OWNER IS NOT NULLGROUP BY UPPER(QS.QS_DOC_OWNER)HAVING COUNT(QS_ID) >= 25ORDER BY 1;Finally, let's say you want to extend the SQL again to include only users who have run a report within the last month yet still include users who have run at least 25 reports since 2006. This dynamic script will do that:SELECT DISTINCTUPPER(QS.QS_DOC_OWNER) USERNAME,COUNT(QS_ID)REPORTS_RUN,MAX(QS.QS_CREATED_DATE) LAST_USED,MIN(QS.QS_CREATED_DATE) FIRST_USEDFROM EUL5_QPP_STATS QSWHEREQS.QS_CREATED_DATE > '01-JAN-2006' ANDQS_DOC_OWNER IS NOT NULLGROUP BY UPPER(QS.QS_DOC_OWNER)HAVINGCOUNT(QS_ID) >= 25 ANDMAX(QS.QS_CREATED_DATE) >= TRUNC(SYSDATE,'MM')ORDER BY 1;

Tuesday, June 10, 2008

Update posted on 30th July 2008:CP8, which was issued on 11th July 2008 has fixed this issue from CP7. Therefore if you have installed CP7 and CP8 or higher is available for your platform you should apply the most recent patch that you can. The CP8 patch, by the way, is 7111816. If you have not installed CP7 you should install CP8 or the most recent cumulative patch instead. Click here for more information regarding the patches you can install.

I hope you have been following along with my postings concerning staying up to date with cumulative patches. Following the application of CP7 you may find that your users complain that lists of values no longer expand in Discoverer Plus when they click the little plus key alongside an item in the Available Items pane.

A new preference setting controls the number of items that can be displayed. The new setting appears to be defaulting to 0, thus preventing any items from displaying in the list of values. If you have access to MetaLink take a look at document 601996.1. This is the readme for CP7 (patch 6778560). Scroll down to Special Instruction 17. It appears to indicate that the preference is only needed if you have more than 1000 items in the list of values.

I have found this not to be the case and that, certainly for all the Discoverer servers that I have upgraded, the preference always needs to be set, even if there are less than 1000 items in any of my lists of values.

If you are experiencing a loss of the list of values, to rememy this, do the following:

If Discoverer Plus is running, exit from Plus

If Discoverer middle tier OPMN is running, it needs to be stopped as follows:a. Launch cmd window in Windows or connect to Unix / Linux as ORACLE userb. Navigate to $ORACLE_HOME\opmn\binc. Type opmnctl stopall and press Enterd. Leave this window open

Navigate to $ORACLE_HOME\discoverer\utilIn Windows: ORACLE_HOME will usually be C:\Oracle\BIHome1

Edit the file called pref.txt – it may just be called pref if you are doing this inside Windows Explorer

Add the following 2 lines to the bottom of the file (use copy and paste):

Note: if you already have a Generic Properties section that was added for the inclusion of a previous bug fix you should add the new preference in that section. Most installations will not have this section so adding it to the bottom of the list of preferences is the logical place.

Save the file

Still in $ORACLE_HOME\discoverer\util: execute the file called applypreferences (it will be called applypreferences.bat in Windows and applypreferences.sh in Unix / Linux)

Navigate back to $ORACLE_HOME\opmn\bin

Type opmnctl startall and press Enter

Discoverer Plus should now display your lists of values.

Note: The preference and value of 100 we just set allows up to 100 items to appear in a LOV. You need to alter this value if your site has lists of values that have more than 100 items. The whole point of this new preference is to remove the restriction of 1000 items like there used to be in previous versions of Discoverer.

Michael Armstrong-Smith

Michael is co-owner, CIO and principal consultant of Armstrong-Smith Consulting. He is the author of the Oracle Discoverer 10g Handbook. Armstrong-Smith specialize in everything Business Intelligence, including data warehouse and curriculum design. Michael is also the founder and owner of this blog and has over 25 years experience in applications development. He has presented at many conferences and is one of the founder members of Oracle's advisory board for business intelliegence. Appointed to be an Oracle ACE in 2006, Michael has been working in the Business Intelligence arena for over 12 years. Please contact me if you would like help with your BI installtion or are planning or implementing any BI initiative.

Join my LinedIn Group

Rod West

Rod has been using Oracle databases since 1985 and is principal consultant at Cabot Consulting in the United Kingdom. He specializes in Oracle Applications 11i / 12i as well as Oracle Discoverer