From cspence@FuelSpot.com Wed, 08 Aug 2001 08:37:12 -0700 From: Christopher Spence Date: Wed, 08 Aug 2001 08:37:12 -0700 Subject: RE: Optimizer Mode......how to choose the right one? Message-ID: MIME-Version: 1.0 Content-Type: text/plain Title: Message I by no means say CBO doesn't make mistakes.  It by far does, but the original statement stays accurate, order of tables only matters with RBO (if comes up with tie) and ordered hint.    There is nothing wrong with getting it in a good order, I just wanted to stress that it only truely matters during those two conditions.  That's all, not trying to be adversary.   CNA is Novell Admin, I haven't done it in a while, I originally started as a Novell Admin. Raptor is a firewall product I spent about 4 years working with.   "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-----From: Koivu, Lisa [mailto:lisa.koivu@efairfield.com] Sent: Wednesday, August 08, 2001 11:08 AMTo: 'ORACLE-L@fatcity.com'; 'cspence@FuelSpot.com'Subject: RE: Optimizer Mode......how to choose the right one? Ahh, but Christopher, therein lies the problem.  The CBO makes mistakes and sometimes needs the gentle nudging of the ORDERED hint.  I've seen a query go from hours to 2 seconds after some analysis of the execution plan that was failing miserably. Love the sig.  You are a CNA?  A Raptor?  Am I missing something? Lisa Koivu Vikings Fan (and DBA) Ft. Lauderdale, FL, USA -----Original Message----- From:   Christopher Spence [SMTP:cspence@FuelSpot.com] Sent:   Wednesday, August 08, 2001 10:27 AM To:     Multiple recipients of list ORACLE-L Subject:        RE: Optimizer Mode......how to choose the right one? The driving table order does not matter when using CBO, it will detirmine the order based on cost, not placement. The order only matters when dealing with the RBO (when it hits a tie, otherwise it will change it, but this happens often), or when using the ordered hint.  "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: Wednesday, August 08, 2001 4:30 AM To: Multiple recipients of list ORACLE-L >>Order of the tables only matters when using the ORDERED hint or using >>rbo AND there is a tie ranking. But surely the very nature of the CHOOSE hint is to select the best method to use whether it's to follow the RBO or the CBO. Therefor ensuring that you have the driving table defined in the correct place within the query will have a benefit whether the database selects to use the RBO or CBO... IMHO. Kev. __________________ 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 18:36 To: Multiple recipients of list ORACLE-L When using first rows, you force the cost based optimizer, in which the order of the tables does not matter. Order of the tables only matters when using the ORDERED hint or using rbo AND there is a tie ranking. "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:37 PM 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: a) Size of tables, ordering, which is the driving table 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. c) 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@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@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@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@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: Christopher Spence   INET: cspence@FuelSpot.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@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@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@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: Christopher Spence   INET: cspence@FuelSpot.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@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).