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 stats Table: 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 recipients of list ORACLE-L >>Subject: Confuzzled on OPTIMiZER_INDEX_COST_ADJ >> >> >>We recently upgraded a production DB to 9.2.0.2 The >>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). > -- 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).
