Meeting with MGA consultants for database tuning

Meeting with MGA, preliminary for consultation

Materialized views - Mark: materialized views are large.  Logs grow.  Most big companies wouldn't use them.  Except for overnight refresh. Too large an impact on source database.

Them: Could use triggers and log tables changes.  Log tables don't have much.   Could use some product they sell.  Replicator I think.

Us:  Our source system issues are stable all now.  Most of our mviews have only nightly refreshes.  Not bothering our source database.

Most hibernate queries expect single column primary keys.  We have to use additional views of the mviews to achieve this.  Also used the mviews to transform data for Sakai.  Also some denormalizing here.  However, is our test environment which is like Prod in every way except fewer rows of data, the explain plans are better.  Prod doesn't use indexes it should.  Test does.

Them:  You need to run stats and (something about) parameters. We can give you scripts that keep the stats up.  If you have difference in stats or parameters between your production and test databases, will execute explain plan differently.  (I think he said) you can copy the stats from your test database to your prod database to tell Oracle what to do also.

Them:  There are many database parameters. (Seemed like he rattled off at least 1.5 dozen.) All these need to be correct.  Oracle does lots of self tuning and will make changes in parameter settings. You have to monitor the database constantly for this because Oracle doesn't self tune well. You have to find the best  set of parameters that gives the best performance.  Analyze these manually.  Can't let Oracle do this itself.

Them:  May have to use trial and error to get best parameter configuration.

Us:  Do this per query?  -- (not sure of the answer here.)

Brian: We run queries that go against views that run against mviews. 

Them:  Could be using summary tables instead.  Then too views may help where sql can get too complex.  Complex queries can slow down Oracle

Us:  indexes not optimal.  Sometimes Oracle uses them, sometimes not.

Them:  something to do with cost based optimizing.  (I didn't get this really.)

Them:  We promote simplification of data access.  You want the least amount of sql, to give you the highest performance.  Analyze what needs to be stored and then create a good data model for target database. 

Things we (MGA) can do for you:
?    Review of your data model
?    Review parameters set in the database
?    Make sure the database access paths are reasonable - indexing, other means
?    Review the queries
?    Transfer knowledge
?    Write a report
?    Do an initial health check of the system

Us:  send us a quote?  Yes.
Us: we want someone here on the premises, not tuning our databases remotely.

Them:  We can do the tuning on the premises or remotely.  On the premises costs more.

Decision:  Send them stuff (documentation, list of queries, etc) earlier than their coming here.  No direct access to our databases (Patrick).

Them: Onsite rate more than remote rate.  We're located in San Francisco and Australia.  (Still don't know their rate.  They have a heavy sell going on.)

After the conference call
Debi: Look at later jdbc driver - we should.  Why are our queries parsing so much?  Shouldn't be since using bind variables.

Query re-write is enabled - Prabhu.

Patrick: we can do a lot of these things for you.  You don't need MGA to give you a healthy check or figure out the best parameter configuration for your database.  They gave us some tips on doing this.  Let us follow up.

Patrick and Debi: Prabhu is working on getting the SmartSite database into the RAC.  He'll be done on Tuesday and ready for you to begin testing.  Let him finish this, then turn our attention to tuning.  We can do the things MGA mentioned but do them ourselves.

Follow up telephone conversation with Debi: We are in talks with MGA to purchase their Eagle Eye product.  Part of the purchase is for them to show us how to run the database healthy check.  Your SmartSite database can be one of the databases we use for this.  We can do this for you.

We (SmartSite) want a near term approach for now - low hanging fruit that will give is big performance boost that we can do all now. 

Let's review the proposal they sent and see which of the things they propose for quick gains on our database performance we cannot do.  Hire MGA to do just those things.

Question I didn't ask but we should consider: if we go into production with the RAC for the fall, how do we retreat if it fails?  Failover to one node though this isn't necessarily easy to do. See Mothra.