Re: Discoverer on a rule based instance

Tim:

You are correct that Discoverer 3.0 works best "out of the box" against an Oracle database with cost-based optimization turned on. Additionally for Discoverer, you will want to configure various init.ora parameters to support queries returning large datasets (large db_block_size, hash_join_enabled set to TRUE, and several others).

But I doubt that it is a good idea to run ad-hoc queries directly against your OLTP tables. I guess in Oracle terms 30 GB is not an exceptionally huge database, depending on the hardware you have under it. Still, I would predict that you will suffer poor performance, even if your database as meticulously tuned as your SQL ( ie. joins and constraints in Oracle, all foreign keys indexed, index and data tablespaces on different physical disks, etc). The preferred route for Discoverer 3.0 would be to create a new database instance, optimize it for data warehousing, and build star schemas to hold your reporting data.

Alternatively, you might be able to obtain workable query performance against the OLTP system by using the Summary Table feature in Discoverer Administration Edition. You can easily build summary tables to contain the columns returned most often in response to your user's queries, and have the summary be refreshed automatically -- a nice management capablity of this tool. The End User Edition somehow "knows" when to use the summary tables instead of the base tables. I have seen orders-of-magnitude increases in query performance this way.

I took the liberty of cross-posting this reply to comp.databases.olap -- you may get some good feedback from that group as well.

Tim Witort wrote:

> We have an OLTP 7.2 database about 30 GB in size. It is set up
> in rule-based optimization to accommodate the meticulously tuned
> SQL in our workstation front end and our reports.
>
> Now we are going to start using Discoverer 3.0 against this
> database and I suspect that Discoverer really wants any database
> it is working against to be in cost-based optimization so that
> it can just send its SQL statements to the RDBMS and not worry
> about how they are formatted.
>
> So, has anyone used Discoverer on an RDBMS in rule based mode?
>
> If performance from Discoverer is going to be awful in rule
> based mode, I'm not sure it's worth trying to move the database
> into cost based mode and incur possible problems with existing
> systems and the overhead of doing periodic ANALYZEs on the
> tables and indexes. And there is no way for us to go and
> put zillions of hints into our existing SQL statements in
> our applications and reports to keep them rule based either.
>
> Any insights out there?
>
> thanks,
> -- TRW
> --
> |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
> | Tim Witort || He is not silent. He is not |
> | trwATmedicalertDOTorg || whispering. We are not quiet |
> | || we are not listening. |
> | Pin: TZ, only one :^( || - Out of the Grey |
> |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

--
Thomas N. Ricciardi