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).
Received on Tue Aug 07 2001 - 13:29:06 CDT
