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