COST and RULE

[lenaf7]

Hi All,

I know I'm adding fuel to the fire... I have questions concerning RULE and COST Based.

I know that Oracle states that they are phasing out RULE; however, I have seen instances were RULE works much better in 8i than COST on certain applications. Can anyone tell my why this could be a possibiltiy?

Oracle creates a database; however, many different applications work on top of the database. You have Oracle Apps, Sebiel, SAP, PeopleSoft, various CRM packages and COST may not be the best way to go, depending on the application.

Also, I know that development cannot build an application based on COST or RULE, because COST is very dependent on the type of data that is contained in each company's environment.

So, can someone tell me exactly how COST and RULE actually works and if you can actually develop based on either?

Currently, I'm working on an application and I've seen that COST works better for Financials, but RULE works best for the HRMS application?

*First STATS are removed from any TEMP Tables
*Sort Area sizes are low
*DB block size is 8
*Running Parallel processing could decrease performance, instead of improving.
*Partitioning is an additional cost
*Histograms could improve, but if there are bind variables it may not be used.
*Forcing Cursor Sharing, I don't know if it would help.

Any other ideas?

Thanks in advance,
lenaf7@yahoo.com

[pando]

certainly there are some application that runs better in RULE than COST but certainly there are application that works with COST

The optimizer is a mistery to many of us, it does strange things for good or bad but in recent version it has improved a lot that is why Oracle is recommending use of COST and I doubt phasing out is an excuse I would rather think that it will be never be phase out because it is simple to keep it there since no there are no further development to improve it

COST can be slower also because your init.ora parameters not because it is slow because it is, with COST you use new features which some of them are affected by init.ora parameters for example hash joins cannot be used with RULE and if used with COST your performance is affected by hash_area_size as well so it is very hard to say what is good or bad, if you think one runs better with RULE then go for it, you can still use it

Running parallel processes can be slow or get you a worse performance in OLTP databases because of additional overhead of coordinating several processes, it can be effective mainly in DWH, DSS, DDS, OLAP databases since these are long running queries so the overhead is small compare with the total time

I dont know what do you mean partition is an additional cost

You MUST use bind variables in OLTP if you want to achieve scalability, cursor_sharing has some side effects but it is not too bad, in 9i they have introduced further improvements for cursor_sharing so we can use it now as well in DWH enviroments

(I dont understand what do you mean COST is very dependent on the type of data that is contained in each company's environment???)