Also make sure you are using bind variables. Don't close the cursor, but
just rebind and reexecute. The sort area size is an init.ora parameter and
not a disk parameter. You can also change it in a session.
Jim
--
Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
with family. Remove the negative part, keep the minus sign. You can figure
it out.
"Ray Porter" <ray_porter_at_unc.edu> wrote in message
news:3e54fe93$1_1_at_news.unc.edu...
> 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 - 19:46:12 CST
