A Discoverer Performance Dilemma

Mark Rittman

• 5 min read

I've got an interesting dilemma with a piece of consulting I'm doing this week. We're working with charity up in London who wants to add some Discoverer reporting to their OLTP application. They'd got licences for Discoverer but never got around to deploying it, and so our task was to do a few days of EUL and report development, then spend some time training the team on how we did it. Their resources are limited and so the scope of the work is specifically to add reporting to their existing application, not develop a data mart or data warehouse and report off of that. They're also wary of making changes to the OLTP application to make reporting easier, as it works "just fine" at the moment and again resources are an issue. It seems a fair request to me - not all reporting has to be off of specially set up reporting databases, it's perfectly reasonable to want to add a bit of reporting to your line-of-business application, in a way it's what Discoverer was originally designed for.

The application was originally developed using Oracle Designer and makes a lot of use of "domain value" lookup tables, a three column table with domain key, code and description which acts as a central lookup table for all tables that store just ID values. To take an example, the customer table might have IDs for gender, age group, requirement, outcome and so on, whilst our central domain lookup table would have a set of rows containing all gender values, all age groups, all requirement types and so on, like this: 

Domain Key   Code      Description
----------   --------  -----------
GEN          M         Male
GEN          F         Female
AGEGROUP     1         0-10
AGEGROUP     2         10-16
AGEGROUP     3         17-60
REQTYPE      1         Advice
REQTYPE      2         Counselling
..etc

This works quite nicely for them as the application can dynamically add new types of attributes, and new attribute values, but it's a real issue when we come to querying and accessing the data as:

  • The value column is dependent on the domain key column, which screws up the optimiser - it can't accurately predict the selectivity of a "code" value as it needs to consider what "domain key" value is also being used;
  • The same table is being used all the time for key lookups, which will cause contention for the same block if everyone is referring to it all the time, and
  • (this is more down to the normalized design) a simple query involves about 20 joins back to this same table to retrieve attribute descriptions

There's lots of other stuff like this as well - outer joins that are required in order to return rows when part of the information is missing, and a data model that's fairly complicated to understand and navigate because it's primarily there to provide a flexible, extensible environment to do their transaction processing.

Now I've read lots of articles why this sort of approach can even be an issue for OLTP applications, not just DSS ones; the issue over the dependent columns can lead to too many full table scans being run, or indexes being used when the predicate isn't actually selective enough, and the contention for that single domain values table is going to at least cause lots of buffer busy waits and at worst start serializing the application. However it's the Discoverer bit I'm thinking about now, and in particular - how can I speed up their reports whilst causing them the least amount of development and additional administration work? As an added bit of fun they're still on Oracle 8.1.7 (soon to go to 9.2.0.5) and currently using the Rule-Based Optimizer.

My thinking so far has been along these lines:

  1. Starting with a simple one or two folder (aka Table) query, retrieve the explain plan, run timings and then gradually add in more complexity, to see at what point the query starts to slow down. What I expect to happen is that joining to a particular type of table - say the domain value lookup one - will suddenly slow down the query, and this is where the effort should concentrate. In other words, find out where the issue is before charging in and making loads of changes.
  2. Assuming the domain value lookup table is the issue, split out the different domains so that there is instead a gender lookup table, an age bands lookup table, an requirements type lookup table and so on. This should be easier for the optimiser to deal with and will spread the demand for key lookup values over several database blocks. To minimise the amount of development work and administration, create these tables as materialized views based off of the domain values table, and refresh them on commit - new values are rarely added to it won't be much of an overhead.
  3. Move them to the Cost-Based Optimiser as soon as possible, put in place statistics gathering, gather histograms (assuming #1 is done) and try and use some of the new feature that make joining large tables more efficient - hash joins, star transformations and so on.
  4. Create indexes where columns are regularly used as part of conditions, bearing in mind of course that it's an OLTP system and there's going to a cost in maintaining them.
  5. Perhaps create denormalized versions of the key tables used for reporting that add back in the attribute descriptions, again through materialized views, and run reports off of these rather than the normalized OLTP tables. This will remove the need for lots of key lookup joins, plus we can resolve the missing values problem that currently requires the use of outer joins.
  6. Once they move to 9.2.0.5, move any frequently used tables to the KEEP pool, make sure they stay in the buffer cache.
  7. Consider rebuilding the lookup tables as Index-Organized Tables, reduce the LIOs for a key lookup from 2 to 1.
  8. Make sure we aren't negating any indexes through things like UPPER() being applied automatically to conditions, Default Date Hierarchies being used to make selections based on Year, Month and so on - if we find these the usual solution is to create some additional function-based indexes that match these derivations.
  9. For the big tables that are queried, consider partitioning them based on branch code or date of enquiry, so that if a full table scan is needed, at least most of the partitions should get pruned and less data will need to be worked with.

That's it for the time being. None of these should need much maintenance - once the branch partitions are created they won't need much adding to, the materialized views take care of themselves, the IOTs only need to be created and the statistics can be gathered as part of a batch job - so hopefully it won't involve much additional administration. I don't know yet whether they'll go for it, but I do hope so as I'm keen to see them get the most out of the work they're putting in. If we get anywhere with it I'll report back on the results, and if anyone's done anything similar (within the same constraints) I'd be interested in feedback. Remember - the reports need to run fast, the data really isn't suitable for MI-style reporting, but any improvements we put in have to have little development or administration impact.

Sign up for more like this.

Enter your email
Subscribe