RE: Optimizer Mode......how to choose the right one?

From: Veronica Levin <vlevin_at_victoria.com.ni>
Date: Tue, 07 Aug 2001 13:20:44 -0700
Message-ID: <F001.003635AF.20010807132641@fatcity.com>

what would you do with an application that has 3 GB of data and 6 GB of indexes????
developers keep telling me that they can't get rid of any index because they use them all..... how can I prove wich indexes are never used??

Saludos,
Veronica Levin Enriquez
Administrador AIX
Compañía Cervecera de Nicaragua

-----Mensaje original-----
De: Christian Trassens [mailto:ctrassens_at_yahoo.com] Enviado el: Martes, 07 de Agosto de 2001 01:26 p.m. Para: Multiple recipients of list ORACLE-L Asunto: Re: Optimizer Mode......how to choose the right one?

NEVER change an optimizer since the application have some time working with it. I should leave it in CHOOSE and then analyze what are doing the transactions.

Look up the worst events through v$system_Event or from time to time through v$session_wait. Issue an utlbstat/utlestat or statspack. Then when you know which is the worst wait. Maybe "db file scattered read" or most of the times "enqueue" or "latch free" and sometimes a huge amount of "db file sequential read" because of bad indexes.

Look up the indexes. Doing that consider their clustering_factor, their blevel and the most important thing their selectivity. ETC.....

• Veronica Levin <vlevin_at_victoria.com.ni> wrote:
  > Morning listers!
  >
  > I am having performance problems with this database,
  > transactions ar running
  > very slow and I am not sure if I have choose the
  > right optimizer mode...
  >
  > AIX 4.2.1, Oracle 7.3.4, 30 GB Database, 1 GB Real
  > Memory,
  > 500 MB SGA, 70 concurrent users, mostly OLTP
  > transactions.
  >
  > I have tunned init parameters the best I can we the
  > resources I have:
  >
  > db_files = 70
  > db_writers = 4
  > db_file_multiblock_read_count = 16
  > db_block_buffers = 57600
  > db_block_size = 8192
  > shared_pool_size = 157286400
  > processes = 200
  > dml_locks = 1500
  > log_buffer = 655360
  > sequence_cache_entries = 800
  > sequence_cache_hash_buckets = 89
  > log_checkpoint_interval = 80000
  > optimizer_mode=CHOOSE
  > session_cached_cursors = 300
  > sort_area_size=1048576
  > hash_area_size=262144
  > hash_multiblock_io_count=4
  > hash_join_enabled=TRUE
  > always_anti_join=HASH
  > job_queue_processes=8
  >
  > 35 rollback segments, inital 1MB, next 1MB, optimal
  > 30 MB
  > 8 multiplexed redologs, 30 MB each
  >
  > I tried FIRST_ROWS, analyzing the tables, but users
  > claimed that performance
  > was getting worse, so I chaged it to Choose. Always
  > analyzing the tables....
  >
  > but, everytime I analyze the tables, performance
  > gets worse.
  > Is this a normal behavior?
  >
  > Any advice will be welcome!
  >
  > thanks is advance,
  >
  > Saludos,
  > Veronica Levin Enriquez
  > Administrador AIX
  > Compañía Cervecera de Nicaragua
  > --
  > Please see the official ORACLE-L FAQ:
  > http://www.orafaq.com
  > --
  > Author: Veronica Levin
  > INET: vlevin_at_victoria.com.ni
  >
  > Fat City Network Services -- (858) 538-5051 FAX:
  > (858) 538-5051
  > San Diego, California -- Public Internet
  > access / Mailing Lists
  >
  

  ---

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

  ---

Eng. Christian Trassens
Senior DBA
Systems Engineer
ctrassens_at_yahoo.com
ctrassens_at_hotmail.com
Phone : 541149816062

---

Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Christian Trassens
  INET: ctrassens_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Veronica Levin
  INET: vlevin_at_victoria.com.ni

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).

Received on Tue Aug 07 2001 - 15:20:44 CDT