Re: RULE or COST based???

Stephan wrote:
> Let me tell you a little story.
> I started developing a forms application on Ora 7.3.4.
> There was 1 simple instance whit NO settings voor RULE/CBO or other.
> I just created the tables, did NO analyze and developed Oracle FORMS
> against it.It worked well and fast.
>
> The application was ready and implemented at different customers, each with
> there own database.
> (versions ranging 7.3.4 to 8i).
> (from our instance export to a dump file, created a new instance at
> customers Oracle databases
> and imported the dump there)
> No problem.All customers experience(d) good response of database.
>
> One customer, running Oracle 8.0 on Unix, did create the instance for us.
> We also imported the dump and now the response is slower and slower every
> day at that instance.
> I did make a dump of customers database and imported in a 8i NT Oracle
> database in our office.
> An what happens? The forms are slow!
> I did do some SQL_TRACE and TKPROF and EXPLAIN_PLAN.
> I saw many FULL .. SCANS so I decided to remove ALL statitiscs (dbms_utility
> ... 'delete') from
> database. The Forms now run FAST, except form some queries.
>
> So my question: did I develop RULE BASED?
> Do you have suggestions according to above story?
>
> Thanks
> Stephan
>
>

Well now, as I see it, there are way, way too many variables in this little tale to give a definitive answer. Without specifications like hardware differences between customers (the fast ones all ran on Crays or souped-up SGIs; the slow ones ran on Pentium 2s or low-end Sparc stations), it's really hard to come to any conclusion. And what about the different sizes of SGAs with different amounts of memory on the hosts? If you have a bunch of small tables and a huge buffer size, then full table scans are appropriate. If you have all of your data files on one disk at one customer site and spread out OFA style at another's, then you'll see vast differences in speed. If you use different flavors of RAID, you'll see differences. If some are client-server, some local host, some n-tier, you'll see differences.

Even though CBO wins hands-down in almost any "typical" environment, the RBO will beat the pants off of CBO in other, very specific environments.

If your Forms run fast after you've removed all statistics, then in that one, and only one, specific case, RBO is superior. However, I doubt you could apply that to a generalized case. There are some pretty clever software engineers at Oracle who are working very hard to make the CBO outperform RBO in a typical environment. At least that's what Larry (and I'm not talking about Leisure Suit Larry here) told me to say. :) Received on Fri Oct 11 2002 - 11:37:42 CDT