Re: 3GB RAM usage by Oracle

> Most databases have performance problems for very simple reasons such > as . . . failure to build appropriate indexes, failure to gather appropriate statistics.

Um, but that's EXACTLY what our Aussie friends were complaining about, the broad-brush approach to database-wide tuning that we undertake BEFORE tuning any specific SQL statements:

http://www.dba-oracle.com/art_dbazine_911.htm

• Fix missing CBO statistics
• Replace an obsolete statistics gathering method
• Initialize missing Oracle instance parameters
• Add missing indexes
• Implement cursor_sharing=force
• Implement the KEEP pool for small-table full-scanned tables
• Change the CBO optimizer parameters
• Add additional SGA RAM
• Employ materialized views
• Create bitmap indexes
• Add freelists

> Not that tuning memory configurations isn't a consideration but long
> after dealing with the basics. And almost always it is the basics that
> have been ignored.

I agree wholeheartedly, but many shops don't, and that's the point I've been making here!

I especially agree about badly-designed databases, and that's a perfect example of the "throw hardware at the issue" solution.

When the schema design is messed-up, even materialized views may fail to correct the performance issues. Management DOES NOT want to hear about an expensive re-design of the database for several reasons:

1 - Blame - Admitting that a crap database was implemented under their watch.

2 - Downtime - The time to re-implement a table re-design can mean DAYS of downtime

3 - Cost - If the crap implementation costs $200k, what do you think the chances are that they would spend the money OVER AGAIN to re-design the system properly? Zero.

These are the shops that want a fast, save-face approach to improving performance, and like it or not, it happens every day.

Just last week I had a client who was having a huge CPU bottleneck, and the root cause was excessive parsing and REALLY bad SQL. They chose to spend $50k for faster processors (15 minutes to fix) rather than spend $100k to tune 2,000 SQL statements (6 weeks to fix).

Now does that make sense? Well, it depends on how you look at it. From a management perspective it made sense as they saved $50k and got a fast, unobtrusive solution to a complex problem.

Was it elegant, heck no!

Was it the "right" thing to do? You be the judge. . . Received on Sat Aug 28 2004 - 20:07:33 CDT