Re: improving performance in a decision support database

Thanks, Andrew. I'll forward your suggestions to our DB group. Our DB instances are being created by the people in our Systems group (most with no DB experience) while the databases are being administered by our DB group. Every one in the DB group comes from a mainframe, IDMS background with no past experience with a true RDBMS (thanks to the long time our Sybase application was up and running, 10 years, I actually have more RDBMS experience than anyone on our DB team). Systems, since they have no db experience or real knowledge of Oracle, is usually creating the databases/instances by accepting most defaults. The DB team is trying to tweak things after db/instance creation. Systems retains control of space allocation, etc. I have no access to most of the configuration settings at all. We had Sybase on site for about 10 years and it was very easy to administer (most software developers, including me, were their own DBAs).

> You are going to get lots and lots of feedback on a question like this.

I hope so. Everyone here is learning how to manage Oracle on the fly with little additional training. NC is facing it's second year of multi-billion dollar budget deficits so funding for additional training or materials is difficult to come by.

> But before that, read and understand the Oracle Concepts manua
> (available on OTN). Have you looked at:

I plan on searching through the OTN today anyway for some installer issues. I'll take a look at the manual you mention while there. Can it be downloaded?

> - Size (number of) db block buffers
> - Multi block read count
> - Block size (you did not take the default 2k did you?)

See above. Since Systems created the db and instance, many of the defaults were initially accepted. I'm getting DB to tweak some of these settings now but I'm having to make specific suggestions or submit proof that performance is not up to our Sybase standards since they're all learning Oracle as they go.

> - Indexes -- do you have enough and are those you have appropriate?

The indexes are identical to the ones we had in Sybase that worked very well. I realize that we may need additional indexes to get the most out of Oracle.

> - Statistics -- are you stats complete? Did you gather stats using the
> dbms_stats package or using analyze statement? -- Do you have stats on
> your idexes too? -- Are your stats current.

DB is supposed to run stats on all tables weekly. I refresh the Oracle database via a direct load from mainframe extracts every night. I run analyze on all the indexes at that point.

> - Have you enabled parallel query execution?

Don't know. I'll have to check with DB.

> - Have you analyzed the query execution plans for some of your most
> troublesome queries to determine if you can improve the query structure?

We've started doing that with a few of the queries. For example, we just discovered yesterday that the name search query I mentioned in my original post is doing a table scan rather than using the defined index.

> - If this is a DW, have you created any materialized views?

No. Can you explain a little more.

> - If this is a DW, are you using a star schema? If so, look at the star
> schema hints.

Our support person in the DB group has mentioned this but she wasn't really sure what it was or if it would help.

>
> I am sure others will add to this list -- These are a few places to look
> that I came up with in less than one minute. You and your team
> DESPERATELY need to take a courses on Oracle database administration and
> performance tuning.

I've had one a couple of Oracle courses for application developers and one DBA course but it really didn't cover performance tuning. Since software developers here are no longer supposed to be involved in db management, I suspect that no such training would be offered even if there were no budget crunch. With the budget crunch, getting our DB folks that training could be tough. However, I do agree that it certainly is needed. Unfortunately, this is one of those situations where upper management has mandated a change but is providing little or no support for the change.

Ray Received on Thu Feb 20 2003 - 09:22:31 CST