Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> [Q] Optimizer_mode and performance?

[Q] Optimizer_mode and performance?

From: L <leed_at_chele.cais.net>
Date: Fri, 1 Dec 2000 14:55:09 -0500 (EST)
Message-Id: <10697.123434@fatcity.com>


We have ORACLE 7.3.4.4 on SUN SPARC Solaris 2.6. Our ORACLE database running third party application on it. Recently, due to data growing (2i.5GB data on data tablespace), user starting to compliant performance slow on some SQL statements. The "optimizer_mode" we use are "choose" and I "analyze" the schema every week use following statements:

   exec dbms_utility.analyze_schema('USER1', 'COMPUTE',NULL,NULL,'FOR ALL INDEXED COLUMNS');
exec dbms_utility.analyze_schema('USER1', 'COMPUTE');

I turn on "tkprof" to trace the third party application (we DON'T have source code). I found some SQL statements run quickly under "rule" mode. After I change database "optimizer_mode" to "rule", users complaint other SQL statements run very slow. I report this problem to third party company. The engineer continue said following:

  1. Analyze schema (actually we did)
  2. Buy high speed CPU and high speed Hard disk RAID.

My questions are:

  1. Does my "analyze" statements collect NOT enought information?
  2. user better hardware to fix software problem is correct way?
  3. any other suggestion?
Received on Fri Dec 01 2000 - 13:55:09 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US