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

From: Veronica Levin <vlevin_at_victoria.com.ni>
Date: Tue, 07 Aug 2001 09:09:58 -0700
Message-ID: <F001.00362ECB.20010807092631@fatcity.com>

I agree with you Kevin, but we have to live with developers, and here (I am sure it happens everywhere) they blame almost all performance problems to the DBA.... and most of us, DBA's, know that their sql code is the first thing they should look at...
I know that the code of the application that is running here is pretty deficient, and the worst part is that it was purchased like that and developers here don't like to mess with the original code of the application, so I guess it is up to me... Now punctual comments,
a) Size of tables, ordering, which is the driving table
>>>> Most of the tables are huge, so are their indexes....
b) Indexes, are the being used, running statements through explain plan will show you where the bottlenecks are...failing that TKPROF will identify potential problem areas with your database.
>>>> Indexes are being used, I've traced several processes...
c) Size of queries, joins etc.
>>>> The most important queries are big and do a lot of joins....

I have told them that... but as I said, they are not willing to make potential changes to the application...

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

-----Mensaje original-----
De: Thomas, Kevin [mailto:Kevin.Thomas_at_calanais.com] Enviado el: Martes, 07 de Agosto de 2001 10:37 a.m. Para: Multiple recipients of list ORACLE-L Asunto: RE: Optimizer Mode......how to choose the right one?

Hi there,

Unfortunately you can't just throw things like FIRST_ROWS at a select statement to make it run faster. The person who wrote the statement should have taken into account such things as:

Size of tables, ordering, which is the driving table
Indexes, are the being used, running statements through explain plan will show you where the bottlenecks are...failing that TKPROF will identify potential problem areas with your database.
Size of queries, joins etc.

Too many developers do not write code to be efficient, as long as it returns the correct values it doesn't matter whether it takes 2 minutes or 2 hours...

I've spent a number of hours/days/weeks/months tidying up poorly optimised code, it's an absolute nightmare for the DBAs and it doesn't look good on the developers.

Cheers,
Kev.
(a cheesed off developer...not a DBA!)

Kevin Thomas
Technical Analyst
Deregulation Services
Calanais Ltd.
Tel: 0141 568 2314
Fax: 0141 568 2366
http://www.calanais.com

-----Original Message-----
Sent: 07 August 2001 17:06
To: Multiple recipients of list ORACLE-L

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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Thomas, Kevin
  INET: Kevin.Thomas_at_calanais.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 - 11:09:58 CDT