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:jeff.thomas_at_thomson.net]
>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:jeff.thomas_at_thomson.net]
>>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: jeff.thomas_at_thomson.net
>>
>>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: jeff.thomas_at_thomson.net
>>
>>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_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).
>>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: John Kanagaraj
> INET: john.kanagaraj_at_hds.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_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).
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Thomas Jeff
> INET: jeff.thomas_at_thomson.net
>
>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_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).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: John Kanagaraj
INET: john.kanagaraj_at_hds.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_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).
Received on Wed Sep 17 2003 - 20:14:40 CDT
