Re: Performance Problem

"Burton, Laura" wrote:
>
> We currently have an application we are trying to speed up. In
> researching rule/cost based optimizers, I read that the cost based
> optimizer was the way to go (although rule had its moments) because that
> is where Oracle would be focusing any upgrades, enhancements, etc.
>
> So I analyzed all tables and indexes. It brought our application to a
> stand still!! I then deleted the statistics and the application ran
> like before...slow. I know that I must have missed something although
> it seemed so straight forward. I verified that all tables were analyzed
> because I read that this would cause an extra step if all the tables
> were not analyzed.
>
> The database is Oracle 8.0.5. This weekend I will be upgrading to
> 8.1.7. The operating system is NT 4.0. Does anyone know something that
> could point me in the right direction? Thank you for your help.
>
> Laura
>

Laura,

  An important question is whether you can tweak the SQL code or not. In any case, the main question is to find out which are the (probably very few) slow statements, which you may find out by peeking at the SGA (V$SQLAREA is a good place to have a look - look for the highest buffer_gets values). Slowness is often a question of choosing (wrongly) hash joins over nested loops or the reverse. Setting the optimizer goal (first rows vs all rows), as well as fiddling with a couple of parameters (look at any parameter containing 'cost' or 'adj' in its name) can hopefully tilt the CBO in the right direction. Rewriting in a suggestive enough way the queries still is my favorite option though (no side effect). As a last resort solution, you still have stored outlines up your sleeve.
 In any case, upgrading to 8.1.7 can only improve thing and is definitely the first step to take.

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).