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

From: Hsu, Anthony C., ,CPMS <anthony.hsu_at_cpms.osd.mil>
Date: Thu, 09 Aug 2001 12:14:01 -0700
Message-ID: <F001.00365920.20010809084452@fatcity.com>

Increase the "sort_area_size" parameter in the init.ora file to have more memory to play with. It is much quickere as it uses RAM, rather than a hard disk, but then again, your server might not have enough RAM to spare.

-----Original Message-----

Sent: Tuesday, August 07, 2001 1:43 PM
To: Multiple recipients of list ORACLE-L

If the transaction having problem is a SELECT statement and does SORT, then I think the sort_area_size is too small.

Can you show us the SQL?

-----Original Message-----

Sent: Tuesday, August 07, 2001 9:37 AM
To: Multiple recipients of list ORACLE-L

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

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

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