----- Original Message -----
Still, some of the queries in Siebel SEEM to run better using CBO.
Ferenc: No doubt this is true, but overall the entire suite comes to a
grinding halt for reasons in your next statement.
Is there any way to mix and match approaches?
Ferenc: A rule of thumb I give myself is that if Tony Aponte says
something, I sit up and listen, he is one of the best Oracle DBA's I have yet
met. I see a post here about plan stability. I think Tony has the only
'approved' partitioned table amongst Siebel customers, and the way he did it
was just so slick, it HAD to be approved, and as you know, partitioned tables
are a cost-based feature. Thoug I must say that wih query plan stability, all
we are really doing in CBO is forcing RBO hints, well kind of, you know what I
mean.
To use RBO by default, but perhaps CBO might make sense for certain
queries, and perhaps some
AD-HOC?
If you are talking about ad-hoc queries as in sql*PLus, I don't see any
harm in experimentation. If you mean a user using the application to generate
the ad-hoc query from hell, I don't need to state the repercussions on this
list, we've all been there, and of course it is always us stupid DBA's who
can't configure their databases properly to meet the impossible demands of a
user who sees all databases equal. Nuff sed !
We've got consultants
who've told me that Siebel 'abuses' Oracle software in
it's excessive use
of outer joins, that Siebel defiles the notions of proper relational
design
with 200+ column tables, and otherwise does not "play nice" with the
normal
rules of relational database management as implemented in Oracle 9i.
Ferenc: Yes, there are outer joins everywhere, and this is handled so
well by RBO. 200+ columns ? Try 1000+ columns. There are things called
extension tables, which were meant for holding the physical dimension of the
1:M relationships, but the flexibility of the application has allowed these to
be used everywhere. Try keying a query on a large entity off an extension
table and all hell breaks loose, see my write-up for details.
Normal rules of rdbms ? There is such a thing ? Just like SAP and PSOFT,
all RI is in the applicatin repository, and contained in the data tables.
There are no PK or FK constraints, whcih woul make a migration towards CBO
somewhat easier, but as it happens, the DB independence crusade states that
DB2 and SQL Server do not implement PK and FK in the same way that Oracle does
(wow, Sherlock Holmes must have been here), so therefore it cannot, will not
be used.
I've also heard that Siebel runs better with DB2 and
SQLServer, which may be options for us.
Ferenc: Not
necessarily. Siebel's largest customer is IBM, nothing confidential about
that. Also no secret that IBM uses DB2. Yet, there are several customers
around the world that run several thousand (I have encountered 6,000 myself)
concurrent users on Siebel, and it works fine. My point is that the larger the
implementation, the larger your tables, the less of a mercy factor there is,
especially for those ad-hoc queries.
