Re: Confuzzled on OPTIMiZER_INDEX_COST_ADJ
To get the 10053 trace, you will need to reparse the query - you might have to flush the shared pool or perform other shared sql invalidations (such as I normally just modify the query to have comment /* test1 */ or /* test2 */ etc. in it. That way it's easy to search from possibly long 10053 trace for specific instances of your query. As Wolfgang stated, if you got only the QUERY string and query text in your trace then you're using either RBO, or your query isn't hard parsed which generates the full trace. If your statistics seem to be correct and optimizer mode is other than RULE and you still don't get the trace, try some hint such is first_rows, in your query to force CBO use. (of course this first_rows hint might change your execution plan itself, but is useful to test whether you get full 10053 trace at all). Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Confuzzled on OPTIMiZER_INDEX_COST_ADJ
Thomas, What is OPTIMIZER_INDEX_CACHING set to? This one also influences the CBO as well as a host of other parameters (including SORT_AREA_SIZE, DB_FILE_MULTIBLOCK_READ_COUNT, .. etc). As well, Histograms and other stats can influence FTS vs Indexed reads. For a complete list of parameters that influence the CBO, you can look up my paper at http://www.geocities.com/john_sharmila/links.htm or look at a 10053 trace... John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Thomas Jeff [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 17, 2003 1:55 PM To: Multiple recipients of list ORACLE-L Subject: Confuzzled on OPTIMiZER_INDEX_COST_ADJ We recently upgraded a production DB to 9.2.0.2The equivalent test tier was upgraded last month. After the production upgrade, one application immediately began experiencing performance issues for a given package where they did not encounter such problems in test. The problem was with one simple SQL statement within the package: SELECT * FROM PARTS WHERE PART_NO = :b1 In production, we are seeing full table scans for this statement while in test it's using an index. We checked stats, indexes, etc, and they are all the same. So I then compared the optimizer parameters and it turns out that in test, optimizer_index_cost_adj is set to 100, but in production it's set to 80.If I do an alter session set optimizer_index_cost_adj to 100 in prod, the statement runs exactly as in test, i.e, with index access. My understanding is that LOWER values of optimizer_index_cost_adj will bias the CBO towards index probes. So, this situation has me confused. What am I missing here? Thanks! Jeffery D Thomas DBA Thomson Information Services Thomson, Inc. Email: [EMAIL PROTECTED] Indy DBA Master Documentation available at: http://gkmqp.tce.com/tis_dba -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Jeff INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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.net -- Author: John Kanagaraj INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Confuzzled on OPTIMiZER_INDEX_COST_ADJ
John, OPTIMIZER_INDEX_CACHING is set to 0 in both databases, all other parameters are also the same. We also kept OPTIMIZER_FEATURES_ENABLE at 8.1.7 in both databases.I tried the 10053 trace but I'm not getting any results in the trace file -- just the query? Thanks. -Original Message- Sent: Wednesday, September 17, 2003 5:10 PM To: Multiple recipients of list ORACLE-L Thomas, What is OPTIMIZER_INDEX_CACHING set to? This one also influences the CBO as well as a host of other parameters (including SORT_AREA_SIZE, DB_FILE_MULTIBLOCK_READ_COUNT, .. etc). As well, Histograms and other stats can influence FTS vs Indexed reads. For a complete list of parameters that influence the CBO, you can look up my paper at http://www.geocities.com/john_sharmila/links.htm or look at a 10053 trace... John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Thomas Jeff [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 17, 2003 1:55 PM To: Multiple recipients of list ORACLE-L Subject: Confuzzled on OPTIMiZER_INDEX_COST_ADJ We recently upgraded a production DB to 9.2.0.2The equivalent test tier was upgraded last month. After the production upgrade, one application immediately began experiencing performance issues for a given package where they did not encounter such problems in test. The problem was with one simple SQL statement within the package: SELECT * FROM PARTS WHERE PART_NO = :b1 In production, we are seeing full table scans for this statement while in test it's using an index. We checked stats, indexes, etc, and they are all the same. So I then compared the optimizer parameters and it turns out that in test, optimizer_index_cost_adj is set to 100, but in production it's set to 80.If I do an alter session set optimizer_index_cost_adj to 100 in prod, the statement runs exactly as in test, i.e, with index access. My understanding is that LOWER values of optimizer_index_cost_adj will bias the CBO towards index probes. So, this situation has me confused. What am I missing here? Thanks! Jeffery D Thomas DBA Thomson Information Services Thomson, Inc. Email: [EMAIL PROTECTED] Indy DBA Master Documentation available at: http://gkmqp.tce.com/tis_dba -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Jeff INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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.net -- Author: John Kanagaraj INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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.net -- Author: Thomas Jeff INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Confuzzled on OPTIMiZER_INDEX_COST_ADJ
if you are getting just the query then that means that the query is being parsed by the rule based optimizer. Is the table analyzed? At 04:29 PM 9/17/2003 -0800, you wrote: John, OPTIMIZER_INDEX_CACHING is set to 0 in both databases, all other parameters are also the same. We also kept OPTIMIZER_FEATURES_ENABLE at 8.1.7 in both databases.I tried the 10053 trace but I'm not getting any results in the trace file -- just the query? Wolfgang Breitling Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Confuzzled on OPTIMiZER_INDEX_COST_ADJ
Jeff (sorry - called you Thomas before!) The 9.x optimizer 'peeks' at values in bind variables when generating plans. Maybe that has something to do with it... I really wouldn't know. It might have to do with Oracle versions as well - 9.2.0.4 sorted out a _lot_ of bugs/issues as compared to 9.2.0.2. To get the 10053 trace, you will need to reparse the query - you might have to flush the shared pool or perform other shared sql invalidations (such as generating stats on one of the objects involved). Cut and paste from a 9.2.0.4 10053 trace for a reparsed SQL (see the 'this is a reparse' string) John QUERY alter session set events '10053 trace name context forever, level 1' *** 2003-09-17 13:04:07.750 QUERY select 'this is a reparse' from dual *** PARAMETERS USED BY THE OPTIMIZER OPTIMIZER_FEATURES_ENABLE = 9.2.0 OPTIMIZER_MODE/GOAL = Choose _OPTIMIZER_PERCENT_PARALLEL = 101 HASH_AREA_SIZE = 512000 HASH_JOIN_ENABLED = FALSE HASH_MULTIBLOCK_IO_COUNT = 0 SORT_AREA_SIZE = 256000 OPTIMIZER_SEARCH_LIMIT = 5 PARTITION_VIEW_ENABLED = FALSE _ALWAYS_STAR_TRANSFORMATION = FALSE _B_TREE_BITMAP_PLANS = TRUE STAR_TRANSFORMATION_ENABLED = FALSE _COMPLEX_VIEW_MERGING = TRUE _PUSH_JOIN_PREDICATE = TRUE PARALLEL_BROADCAST_ENABLED = TRUE OPTIMIZER_MAX_PERMUTATIONS = 2000 OPTIMIZER_INDEX_CACHING = 0 _SYSTEM_INDEX_CACHING = 0 OPTIMIZER_INDEX_COST_ADJ = 100 OPTIMIZER_DYNAMIC_SAMPLING = 1 _OPTIMIZER_DYN_SMP_BLKS = 32 QUERY_REWRITE_ENABLED = TRUE QUERY_REWRITE_INTEGRITY = ENFORCED _INDEX_JOIN_ENABLED = TRUE _SORT_ELIMINATION_COST_RATIO = 5 _OR_EXPAND_NVL_PREDICATE = TRUE _NEW_INITIAL_JOIN_ORDERS = TRUE ALWAYS_ANTI_JOIN = CHOOSE ALWAYS_SEMI_JOIN = CHOOSE _OPTIMIZER_MODE_FORCE = TRUE _OPTIMIZER_UNDO_CHANGES = FALSE _UNNEST_SUBQUERY = TRUE _PUSH_JOIN_UNION_VIEW = TRUE _FAST_FULL_SCAN_ENABLED = FALSE _OPTIM_ENHANCE_NNULL_DETECTION = TRUE _ORDERED_NESTED_LOOP = TRUE _NESTED_LOOP_FUDGE = 100 _NO_OR_EXPANSION = FALSE _QUERY_COST_REWRITE = TRUE QUERY_REWRITE_EXPRESSION = TRUE _IMPROVED_ROW_LENGTH_ENABLED = TRUE _USE_NOSEGMENT_INDEXES = FALSE _ENABLE_TYPE_DEP_SELECTIVITY = TRUE _IMPROVED_OUTERJOIN_CARD = TRUE _OPTIMIZER_ADJUST_FOR_NULLS = TRUE _OPTIMIZER_CHOOSE_PERMUTATION = 0 _USE_COLUMN_STATS_FOR_FUNCTION = TRUE _SUBQUERY_PRUNING_ENABLED = TRUE _SUBQUERY_PRUNING_REDUCTION_FACTOR = 50 _SUBQUERY_PRUNING_COST_FACTOR = 20 _LIKE_WITH_BIND_AS_EQUALITY = TRUE _TABLE_SCAN_COST_PLUS_ONE = TRUE _SORTMERGE_INEQUALITY_JOIN_OFF = FALSE _DEFAULT_NON_EQUALITY_SEL_CHECK = TRUE _ONESIDE_COLSTAT_FOR_EQUIJOINS = TRUE _OPTIMIZER_COST_MODEL = CHOOSE _GSETS_ALWAYS_USE_TEMPTABLES = FALSE DB_FILE_MULTIBLOCK_READ_COUNT = 8 _NEW_SORT_COST_ESTIMATE = TRUE _GS_ANTI_SEMI_JOIN_ALLOWED = TRUE _CPU_TO_IO = 0 _PRED_MOVE_AROUND = TRUE *** BASE STATISTICAL INFORMATION *** Table statsTable: DUAL Alias: DUAL TOTAL :: CDN: 1 NBLKS: 1 AVG_ROW_LEN: 2 _OPTIMIZER_PERCENT_PARALLEL = 0 *** SINGLE TABLE ACCESS PATH TABLE: DUAL ORIG CDN: 1 ROUNDED CDN: 1 CMPTD CDN: 1 Access path: tsc Resc: 2 Resp: 2 BEST_CST: 2.00 PATH: 2 Degree: 1 *** OPTIMIZER STATISTICS AND COMPUTATIONS *** GENERAL PLANS *** Join order[1]: DUAL [DUAL] Best so far: TABLE#: 0 CST: 2 CDN: 1 BYTES: 0 Final: CST: 2 CDN: 1 RSC: 2 RSP: 2 BYTES: 0 IO-RSC: 2 IO-RSP: 2 CPU-RSC: 0 CPU-RSP: 0 -Original Message- From: Thomas Jeff [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 17, 2003 5:30 PM To: Multiple recipients of list ORACLE-L Subject: RE: Confuzzled on OPTIMiZER_INDEX_COST_ADJ John, OPTIMIZER_INDEX_CACHING is set to 0 in both databases, all other parameters are also the same. We also kept OPTIMIZER_FEATURES_ENABLE at 8.1.7 in both databases.I tried the 10053 trace but I'm not getting any results in the trace file -- just the query? Thanks. -Original Message- Sent: Wednesday, September 17, 2003 5:10 PM To: Multiple recipients of list ORACLE-L Thomas, What is OPTIMIZER_INDEX_CACHING set to? This one also influences the CBO as well as a host of other parameters (including SORT_AREA_SIZE, DB_FILE_MULTIBLOCK_READ_COUNT, .. etc). As well, Histograms and other stats can influence FTS vs Indexed reads. For a complete list of parameters that influence the CBO, you can look up my paper at http://www.geocities.com/john_sharmila/links.htm or look at a 10053 trace... John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Thomas Jeff [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 17, 2003 1:55 PM To: Multiple
RE: Confuzzled on OPTIMiZER_INDEX_COST_ADJ
That, or as John pointed out, the query is not parsed at all. Much more likely in your case. To make sure the sql gets parsed, run it as explain plan ... for Is there a histogram on part_no? At 05:14 PM 9/17/2003 -0800, you wrote: if you are getting just the query then that means that the query is being parsed by the rule based optimizer. Is the table analyzed? At 04:29 PM 9/17/2003 -0800, you wrote: John, OPTIMIZER_INDEX_CACHING is set to 0 in both databases, all other parameters are also the same. We also kept OPTIMIZER_FEATURES_ENABLE at 8.1.7 in both databases.I tried the 10053 trace but I'm not getting any results in the trace file -- just the query? Wolfgang Breitling Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
