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

From: Christopher Spence <cspence_at_FuelSpot.com>
Date: Tue, 07 Aug 2001 09:25:11 -0700
Message-ID: <F001.00362F53.20010807093554@fatcity.com>

If you are using 8.x + I would recommend looking at Statspack over utilbstat.
It comes installed with 816+ and available for download on 8.x+.

It is fairly easy to configure and a great utility.

"Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes."

Christopher R. Spence OCP MCSE MCP A+ RAPTOR CNA Oracle DBA
Phone: (978) 322-5744
Fax: (707) 885-2275

Fuelspot
73 Princeton Street
North, Chelmsford 01863

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

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

How often do you rebuild your indexes?
What does your utlb and utle report say?
Have you done any explain plans or tkprofs on your sql code? Sql may not be using indexes.
Try using a hint of rule on your sql code to see if it runs better that way
Do you have a lot of chaining going on.
Do you have enough rollback segments and are they sized right
what are the size of your redo logs and how often are you check pointing?
Do you have auditing turned on.? A lot of tracing going on?

Best would be to run utlb and utle reports. I would send you to www.perfmon.com to get a quick analysis but this site has been under construction for several days and I don't know of another site that will analyze this reports.

Someone else may know of another site that analyzes utlb/utle reports.

kathy

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

Sent: Tuesday, August 07, 2001 9:06 AM
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). Confidential This e-mail and any files transmitted with it are the property of Belkin Components and/or its affiliates, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipients or otherwise have reason to believe that you have received this e-mail in error, please notify the sender and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing or copying of this e-mail is strictly prohibited.
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Kathy Duret
INET: KathyD_at_belkin.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