Re: improving performance in a decision support database

Thanks, Jim. I provided as much information as I had since I don't have access to a lot of the system information anymore.

> 1. Buy Tom Kyte's book (go to asktom.oracle.com) it is invaluable (about
> $50)

Agreed. I'll have to buy any books I get myself. Getting our db group to take any suggestions might be difficult.

> 3. WAG - your sort area size is probably too small. But stats pack would
> reveal this (sorts to disk)

Agreed. In Sybase, we had a temporary work space that was very large. The corresponding space in Oracle was initially set to the very small default. I've been gradually persuading db to increase this size but all they've really been willing to do so far is increase the size of our secondary extents. The initial extent and the total size are still small compared to what we had in Sybase. I'm working on it but it involves both our db and systems groups since systems will have to approve any increase in the total size.

> 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.

Nope. We didn't create any temporary tables in Sybase and aren't doing so in Oracle.

> 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.

We aren't doing this exactly since the data coming from the mainframe has upper case in those fields where it is needed for sorting/searching. In the GUI, I'm converting the criteria to upper case before passing it to the SQL statement. However, as mentioned above we do have some queries that use wild card searches and Oracle isn't using the defined index (Sybase did). I'll ask our db support person about 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.

I agree 100% but I don't get to make those decisions.

Thanks,
Ray Received on Thu Feb 20 2003 - 10:12:06 CST