Re: Discoverer on a rule based instance

In article <349AB4F4.A583649F_at_teleport.com>,   Tom Ricciardi <tomr_at_teleport.com> wrote:
>
> 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?
 

> Thomas N. Ricciardi

Thomas, Discoverer queries may need CBO and the database may be set to RBO. By setting a registry entry, a CBO hint will be placed in the sql query. This is the standard using Noetix views on Oracle Financials. By the way I use Disco30 to query the data dictionary for permissions, space, and report exceptions in color highlights. The entry is: \\HKEY_CURRENT_USER\Software\Oracle\Discoverer\Database\UseOptimizerHints = 1

Glen

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet