Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Performance Problem

Re: Performance Problem

From: Stephane Faroult <>
Date: Mon, 25 Aug 2003 13:14:33 -0800
Message-ID: <>

"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


  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.


Stephane Faroult
Oriole Software
Please see the official ORACLE-L FAQ:
Author: Stephane Faroult

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (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).
Received on Mon Aug 25 2003 - 16:14:33 CDT

Original text of this message