Discoverer polls 1 and 2

So that I can provide a better service to the industry, from time to time I will be conducting polls of the Discoverer commulity on various topics.

The results from the first 2 polls can be seen in the panel to the right. As you can see they make very interesting reading.

Monitoring and removing old statistics

This is a posting for Discoverer administrators.

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\util

It is called: EULSTDEL.SQL

To run the script, use this workflow:

1. Log in to SQL Plus as the owner of the EUL
2. Type this @c:\oracle\bitoolshome_1\discoverer\util\eulstdel.sql and press Enter
3. 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!
4. 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.

• EUL5_QS1_I
• EUL5_QS2_I
• EUL5_QS_PK

By the way, if you just want to see your statistics, here is the SQL:

SELECT
TRUNC(SYSDATE-QS_CREATED_DATE,-1) Days_Old,
COUNT(*) No_of_stats
FROM EUL5_QPP_STATS
GROUP BY TRUNC(SYSDATE-QS_CREATED_DATE,-1)
ORDER BY 1 DESC

How many users

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:

SELECT DISTINCT
UPPER(QS.QS_DOC_OWNER) USERNAME,
COUNT(QS_ID)REPORTS_RUN,
MAX(QS.QS_CREATED_DATE) LAST_USED,
MIN(QS.QS_CREATED_DATE) FIRST_USED
FROM EUL5_QPP_STATS QS
WHERE QS.QS_CREATED_DATE > '01-JAN-2006' AND
QS_DOC_OWNER IS NOT NULL
GROUP BY UPPER(QS.QS_DOC_OWNER)
ORDER BY 1;

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 DISTINCT
UPPER(QS.QS_DOC_OWNER) USERNAME,
COUNT(QS_ID)REPORTS_RUN,
MAX(QS.QS_CREATED_DATE) LAST_USED,
MIN(QS.QS_CREATED_DATE) FIRST_USED
FROM EUL5_QPP_STATS QS
WHERE
QS.QS_CREATED_DATE > '01-JAN-2006' AND
QS.QS_DOC_OWNER IS NOT NULL
GROUP BY UPPER(QS.QS_DOC_OWNER)
HAVING COUNT(QS_ID) >= 25
ORDER 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 DISTINCT
UPPER(QS.QS_DOC_OWNER) USERNAME,
COUNT(QS_ID)REPORTS_RUN,
MAX(QS.QS_CREATED_DATE) LAST_USED,
MIN(QS.QS_CREATED_DATE) FIRST_USED
FROM EUL5_QPP_STATS QS
WHERE
QS.QS_CREATED_DATE > '01-JAN-2006' AND
QS_DOC_OWNER IS NOT NULL
GROUP BY UPPER(QS.QS_DOC_OWNER)
HAVING
COUNT(QS_ID) >= 25 AND
MAX(QS.QS_CREATED_DATE) >= TRUNC(SYSDATE,'MM')
ORDER BY 1;

LOVs in Plus following upgrade to CP7

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:

1. If Discoverer Plus is running, exit from Plus
2. 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 user
   b. Navigate to $ORACLE_HOME\opmn\bin
   c. Type opmnctl stopall and press Enter
   d. Leave this window open
3. Navigate to $ORACLE_HOME\discoverer\util
   In Windows: ORACLE_HOME will usually be C:\Oracle\BIHome1
   
4. Edit the file called pref.txt – it may just be called pref if you are doing this inside Windows Explorer
5. Add the following 2 lines to the bottom of the file (use copy and paste):
   
   [Generic Properties]
   genericLovSizeSoftLimit = 100 # items allowed in LOV
   
   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.
   
   
6. Save the file
7. 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)
8. Navigate back to $ORACLE_HOME\opmn\bin
9. 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.