Re: improving performance in a decision support database

You don't provide enough information to answer your question. There are so many possibilities that it is near impossible. I suggest the following:
1. Buy Tom Kyte's book (go to asktom.oracle.com) it is invaluable (about $50)
2. Install statspack and take some performance snapshots. 3. WAG - your sort area size is probably too small. But stats pack would reveal this (sorts to disk)
4. WAG - Coming from Sybase you might be using a lot of temporary tables in stored procs. If so Oracle does not need such a cruch and will run a lot slower if you are dynamically creating and dropping tables. 5. WAG You are probably doing searches like select ... from .. where name = upper('jim') and thus forcing a full table scan - (so you can have a case insensitive queries). Create a function based index. 6. Think of the training or consultant issue like this: People's time waiting for things to happen (in the app) is less valuable than spending some money to fix the problem. (take the cost of a consultant or training and divide it by (# of people waiting * time each one waits* # of times this is going to happen until it is fixed) It makes it look like those people's time is worthless.

Post the stats pack results.
Post the explain plan and tkprof of those things that are slow. Jim

"Ray Porter" <ray_porter_at_unc.edu> wrote in message news:3e54f2f3$1_2_at_news.unc.edu...
> 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:41:50 CST