Return-Path: Received: from ensim.rackshack.net (root@localhost) by orafaq.net (8.11.6/8.11.6) with ESMTP id gALHbwe31054 for ; Thu, 21 Nov 2002 11:37:58 -0600 X-ClientAddr: 209.68.248.164 Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by ensim.rackshack.net (8.11.6/8.11.6) with ESMTP id gALHbvP31049 for ; Thu, 21 Nov 2002 11:37:58 -0600 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id GAA66315; Thu, 21 Nov 2002 06:21:18 -0800 (PST) Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 00508C87; Thu, 21 Nov 2002 05:49:26 -0800 Message-ID: Date: Thu, 21 Nov 2002 05:49:26 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: "Jamadagni, Rajendra" Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: "Jamadagni, Rajendra" Subject: RE: converting to the cost based optimizer Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPartTM-000-2b0b55b4-bdd6-46f5-874f-d6c9157cfc9c" ------=_NextPartTM-000-2b0b55b4-bdd6-46f5-874f-d6c9157cfc9c Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C29164.7AFC08D0" ------_=_NextPart_001_01C29164.7AFC08D0 Content-Type: text/plain; charset="iso-8859-1" Me thinks (based on my conversion from RBO->CBO) over last two months ... ) one major factor is achieving success is as follows 1. Train your developers/designers 2. See point one 3. Lather - rinse - Repeat Some people just don't get it ... lot of people (in my limited experience) think that CBO is the new RBO ... so if it worked perfect in RBO, it will work better in CBO. Some also think that there are some 'hidden' parameters that DBAs can set and all of a sudden my application will be flying (at scramjet speeds). We just ran into a problem with Spatial queries in 9201 database ... it chokes with making a HJ ... so now we are putting in a on-logon trigger for that specific user to do a alter session and set HJ_enabled to false. Some things that we found ... 1. In 9201 there is a bug related to query_unnesting (yeah the same one they discovered in 8071 and claimed to have fixed in 8174). we ended up billing customers 1.5Million instead of 150K (luckily it was a test run and was fixed before the actual run was done). 2. The spatial problem as mentioned above 3. Carefully (I mean very carefully) decide on db_file_multiblock_read_count value ... 3.1 As it affects CBO to take different path 3.2 If you are using RAC, it also affects Global cache traffic the bug is (claimed to be fixed in 9202). 4. You will see some queries drive like 5mph in the left lane ... after careful analysis we have found much success with optimizer_index_cost_adj parameter. Trust me I experimented about 15 times before deciding a value of 10; 5. Decide BEFORE YOUR TESTING PHASE STARTS on what your statistics collection be, 5.1 old 10% is out, 5.2 oracle recommends 25% 5.3 rather dbms_stats.auto_sample seems to be the current recommendation 5.4 if you'd like to do 100% i.e. COMPUTE. Once you decide STICK TO IT NO MATTER WHAT because if you change that CBO will think otherwise. 6. Use DBMS_STATS ... don't use ANALYZE ... also when doing gather_table_stats make sure you specify CASCADE => TRUE (I can't imagine why it is FALSE by default). 7. Based on my emails earlier this year and a very good explanation from Tim Gorman and a couple of others on this list, we decided to put hints ONLY in places where CBO is consistently making wrong decisions. 8. If you want to use OUTLINES, make sure that the SQL HAS TO BE EXACT ... else it won't work. 9. One important lesson I learned in tuning CBO queries is to TUNE THE SQL WITH BIND VARIABLES ... CBO at times chooses different path based on bind values or hard-coded values. there is more ... but it is enough to scare someone off ... personally I am very comfortable with CBO, but it is the RBO tunes application that has problems with CBO and my colleagues and I are working hard to make them (the application and CBO) like each other. Okay time to get me off my soapbox and do some work ... and yes I have tapped the collective brain power on this list ... and the wisdom never fails to amaze me. Thank you all.. Raj ______________________________________________________ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! ------_=_NextPart_001_01C29164.7AFC08D0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: converting to the cost based optimizer

Me thinks (based on my conversion from RBO->CBO) = over last two months ... ) one major factor is achieving success is as = follows

1. Train your developers/designers
2. See point one
3. Lather - rinse - Repeat

Some people just don't get it ... lot of people (in = my limited experience) think that CBO is the new RBO ... so if it = worked perfect in RBO, it will work better in CBO. Some also think that = there are some 'hidden' parameters that DBAs can set and all of a = sudden my application will be flying (at scramjet speeds).

We just ran into a problem with Spatial queries in = 9201 database ... it chokes with making a HJ ... so now we are putting = in a on-logon trigger for that specific user to do a alter session and = set HJ_enabled to false.

Some things that we found ...
1. In 9201 there is a bug related to query_unnesting = (yeah the same one they discovered in 8071 and claimed to have fixed in = 8174). we ended up billing customers 1.5Million instead of 150K = (luckily it was a test run and was fixed before the actual run was = done).

2. The spatial problem as mentioned above
3. Carefully (I mean very carefully) decide on = db_file_multiblock_read_count value ...
   3.1 As it affects CBO to take different = path
   3.2 If you are using RAC, it also = affects Global cache traffic
       the bug is = (claimed to be fixed in 9202).
4. You will see some queries drive like 5mph in the = left lane ...
   after careful analysis we have found = much success with
   optimizer_index_cost_adj parameter. = Trust me I experimented
   about 15 times before deciding a value = of 10;
5. Decide BEFORE YOUR TESTING PHASE STARTS on what = your statistics collection be,
   5.1 old 10% is out,
   5.2 oracle recommends 25%
   5.3 rather dbms_stats.auto_sample seems = to be the current recommendation
   5.4 if you'd like to do 100% i.e. = COMPUTE.
   Once you decide STICK TO IT NO MATTER = WHAT because if you change that CBO will think otherwise.
6. Use DBMS_STATS ... don't use ANALYZE ... also = when doing gather_table_stats
   make sure you specify CASCADE =3D> = TRUE (I can't imagine why it is FALSE by default).
7. Based on my emails earlier this year and a very = good explanation from Tim Gorman
   and a couple of others on this list, we = decided to put hints ONLY in places where
   CBO is consistently making wrong = decisions.
8. If you want to use OUTLINES, make sure that the = SQL HAS TO BE EXACT ... else it won't work.
9. One important lesson I learned in tuning CBO = queries is to TUNE THE SQL WITH BIND VARIABLES ...
   CBO at times chooses different path = based on bind values or hard-coded values.

there is more ... but it is enough to scare someone = off ... personally I am very comfortable with CBO, but it is the RBO = tunes application that has problems with CBO and my colleagues and I = are working hard to make them (the application and CBO) like each = other.

Okay time to get me off my soapbox and do some work = ... and yes I have tapped the collective brain power on this list ... = and the wisdom never fails to amaze me. Thank you all..

Raj
______________________________________________________
Rajendra Jamadagni      =         MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't = reflect that of ESPN Inc.
QOTD: Any clod can have facts, but having an opinion = is an art!

------_=_NextPart_001_01C29164.7AFC08D0-- ------=_NextPartTM-000-2b0b55b4-bdd6-46f5-874f-d6c9157cfc9c Content-Type: text/plain; name="ESPN_Disclaimer.txt" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="ESPN_Disclaimer.txt" ********************************************************************This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*********************************************************************2 ------=_NextPartTM-000-2b0b55b4-bdd6-46f5-874f-d6c9157cfc9c-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jamadagni, Rajendra INET: Rajendra.Jamadagni@espn.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- 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).