MIMS 4.3 and Oracle Statistics 1

[csearle]

I am new to the MIMS world, and would like to know if MIMS is recommended to run with Oracle 8.1.6 in Rule or Cost optimization. We have quite a lot of direct SQL against the tables for external reporting. Right now we do not run ANALYZE or ESTIMATE STATISTICS on the tables or indexes, and wonder if it would help or destroy the MIMS environment (use of hints in the code?), and/or help the direct SQL queries.
Thank you for any assistance.
C.

 

[Glen]

A few years ago, we made a switch from rule to cost under Oracle 7.3 and MIMS 3.13. It caused major performance problems for MIMS (not a good idea at the time). Be careful and make sure to build a production load performance test into your project plan (as well as a political plan for a backout) if you decide to make the change. Glen Colbert
gcolbert@mslden.com

 

[csearle]

Thank you for the response. Can I safely assume that Mincom has not changed MIMS DB access significantly from 3.13 to 4.3.1.2, and that analyzing the Oracle tables would destroy performance (assuming no rewriting of MIMS code)? We seem to have as much direct SQL network access as we do MIMS application TP traffic. Do most admins recommend creating a separate reporting server and/or data warehouse for the ad-hoc queries? Our transaction table (900 I believe) has 22 Milion rows, and I would believe the majority of direct queries against it are not optimized. Any suggestions?
Thank you in advance.
C.

 

[gbell]

If I was supporting a Production instance with 22 million rows in MSF900 and I was able to move reporting to another dedicated instance, refreshed daily, I would jump at the opportunity.

 

[Glen]

We have 18 Million MSF900 rows (which translates to probably 50 Million total rows when you factor in the 900_A, 900_B, 900_C etc rows). A significant portion of our users access the database using third party tools (mostly Infomaker, some ODBC, some VB) as well.

Properly thought out database views which prevent full table scans, while performing the required joins for the are a real good idea.

Glen Colbert
gcolbert@mslden.com