Enhancing Oracle Reports - create a custom type

Usually, the report types (destination types) provided by Oracle Reports are sufficient. You can create your report as a file, in the cache, send it to a printer etc. See the list of standard types in the Oracle Documentation.

It is also possible to create your own destination, but the documentation is a bit vague about this, to put it mildly. Once you know where to look though, it is quite easy to accomplish.

First of all, you will need to modify the reportsserver configuration file. You can do this through Enterprise Manager, or modify the file directly ($ORACLE_HOME/reports/conf/<repserv>.conf). You must add a destination tag, just like there are many others in this file. It would be sufficient to add something like this:
  <destination destype="myDest" class="myDestClass"/>

And now, your reportserver will not start, or at least give you an error that the specified class does not exist. This is the hard part. You must create a Java Class that extends the Destination Class. So, you find yourself a Java Programmer (shouldn't be that hard to find, these days) and let him create your own class. In this class, you can do (almost) anything you can imagine. You will have the XML provided by the reports engine avaiable and you will need some imagination as to what you want to do with this report.
After the class is created, compiled and packaged, you add it to the classpath of the reports server. Easiest way to do this is to use Enterprise Manager. After that, the reports server will be able to find the new class and start the engine.

You could for example create an RTF document from the standard XML using a stylesheet and then mail it to one or more recipients. You could send it to a printer and e-mail it both in the same class. Any requirement you might have for your reports is now available. It will require your effort to implement all your requirements though.

This is a good way to enhance the output options of reports, it is just too bad that it is not so very well documented...
When you start working with this, there will undoubtedly be some other obstacles, but if you have any questions, you know where to find me (i.e. right here ;-)

Using too many indexes

When writing about the use of indexes (or rather, the lack of) it soon occurred to me that when you tell the developers to use indexes, some of them really take that too far. I once saw a 18-column table with a total of 20 indexes on it. That might be useful in a certain DWH database, but this was in a OLTP database.

But why is it not good to have a lot of indexes? Is there such a thing as too many indexes? From the optimizer point of view, it just means a few extra access paths to consider, that should not be too difficult. On the other hand, from a DML perspective, when you insert a record or update certain columns, all related indexes must be modified as well. So, instead of inserting one row (1 I/O operation), you could end up with inserting one row and inserting 20 index entries (21 I/O operations!).

So yes, you can certainly have too many indexes.

What to do? Well, the obvious thing to do is to get rid of all the unnecessary indexes. But not knowing which indexes are useful, you might accidentally drop the wrong ones and end up with terrible performance for your application.

The best course of action would be to let the database determine what indexes are being used and which indexes are candidates for deletion. You do this with the following statement:

    ALTER INDEX index MONITORING USAGE;

This starts monitoring if the index is being use by any statement in the database. You can let this monitoring run as long as you like. I would advise to use a representative period of time, so you can be sure that each and every part of your application has been used (every statement out there should have run at least once). You should consider special cases like an end-of-month report that will run. It would be a shame to drop any indexes and then discover that this crucial report takes two days to complete…

After you have monitored long enough, you should check the V$OBJECT_USAGE table. This table contains the rows for the monitored indexes and contains a column “USED”. If this is set to TRUE, the index was used during to monitoring period. If not, the column value will be FALSE.

Now you can determine if there are unused indexes in your datamodel. These are the candidates for deletion. Please remember that index usage is not restricted to query performance. You should not drop primary key or unique ket indexes, just because they are not used in your monitored workload. They have a different (but important) reason for being there.

After you complete your analysis, you can disable monitoring:

    ALTER INDEX index NOMONITORING USAGE;

This way, you keep the minimum number of indexes while retaining optimal performance. You (or rather, the database) will save a lot of effort updating indexes that will most likely never be used and thus optimizing your performance just that bit more.

Also see the Oracle documentation for some more details…