interview question

Printable View

Show 40 post(s) from this thread on one page

05-06-2003, 06:26 PM
BJE_DBA

Quote:

Originally posted by grjohnson
I've had only MINOR (if any at all) benefit from tweaking these values. I'd be looking else where first.

Elsewhere first? Thats not making any sense. I was out of places to look. This was billions of rows, I already partitioned the tables and indexes, used bitmap indexes, even did COMPUTE statistics on this monster table to give it every chance ( all rows i.e. not ESTIMATE ), tried histograms, applied hints only to have the Manager of Software dev say they dont want hints in their code for single predicate queries. I traced the optimizer and discovered the discrepancy in costs and contacted Oracle. They suggested upgrading so I moved it all to a test system and upgraded only it did not work there either.

So here is a list of where I looked 1st:

Partitioning Tables and Indexes
Bitmap indexes
Compute Statistics
Histograms
Hints
Oracle Upgrade
Trace
Which ones am I missing ?

optimizer_index_cost_adj worked and its a good thing because I had already looked everywhere else.
05-06-2003, 10:28 PM
SANJAY_G

Quote:

Originally posted by BJE_DBA
optimizer_index_cost_adj worked and its a good thing because I had already looked everywhere else.

Now I know why Donald Burleson calls it silver bullet.
http://www.dba-oracle.com/oracle_tips_cost_adj.htm
05-07-2003, 12:27 AM
hrishy

Quote:

Originally posted by pando
I trust this fact because I did it in practice. Had a query running using nested loop, increased sort_area_size it changed to sort merge join

I think automatic PGA is only advisable in OLTP...?
Chris if you turn on event 10053 it shows you all the parameters that affects CBO when calculating the cost

Hi Pando

if you see the plan for a query and the plan is fine..and you want to have a look at all the parameters that made the optimizer choose a particular plan then you would use event 10053 am i right ?

I also see that BJE_DBA has a predominantly OLAP tyope of database

regards
Hrishy
05-07-2003, 01:11 AM
pando

Quote:

Originally posted by BJE_DBA
discovered the discrepancy in costs

I think comparing number of costs is pointless

if you cannot use HINTS then use outlines
05-07-2003, 04:02 AM
grjohnson

Quote:

Originally posted by BJE_DBA
Elsewhere first? Thats not making any sense.
So here is a list of where I looked 1st:

Partitioning Tables and Indexes
Bitmap indexes
Compute Statistics
Histograms
Hints
Oracle Upgrade
Trace
Which ones am I missing ?

optimizer_index_cost_adj worked and its a good thing because I had already looked everywhere else.

init.ora paramters (DB_FILE_MULTIBLOCK_READ_COUNT), you did forget that. Glad you worked it out on your own.
05-07-2003, 04:18 AM
pando

Code:

10053 trace file *************************************** PARAMETERS USED BY THE OPTIMIZER ******************************** OPTIMIZER_FEATURES_ENABLE = 9.2.0 OPTIMIZER_MODE/GOAL = Choose _OPTIMIZER_PERCENT_PARALLEL = 101 HASH_AREA_SIZE = 1048576 HASH_JOIN_ENABLED = TRUE HASH_MULTIBLOCK_IO_COUNT = 0 SORT_AREA_SIZE = 524288 OPTIMIZER_SEARCH_LIMIT = 5 PARTITION_VIEW_ENABLED = FALSE _ALWAYS_STAR_TRANSFORMATION = FALSE _B_TREE_BITMAP_PLANS = TRUE STAR_TRANSFORMATION_ENABLED = TRUE _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 = 0 _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 = TRUE _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 = FALSE _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 = 16 _NEW_SORT_COST_ESTIMATE = TRUE _GS_ANTI_SEMI_JOIN_ALLOWED = TRUE _CPU_TO_IO = 0 _PRED_MOVE_AROUND = TRUE
05-07-2003, 12:23 PM
BJE_DBA

Quote:

Originally posted by grjohnson
init.ora paramters (DB_FILE_MULTIBLOCK_READ_COUNT), you did forget that. Glad you worked it out on your own.

You must be kidding me. Yes I had a 2 Terabyte database but never thought about DB_FILE_MULTIBLOCK_READ_COUNT and Max_IO ;)

What you dont realize is - of course that is set to the appropriate setting but when the optimizer chooses to scan the fact table partitions ( 1 or more ) DB_FILE_MULTIBLOCK_READ_COUNT is no match for an index unless you have a large number of channels which we did not.

Reduce I/O before relying on DB_FILE_MULTIBLOCK_READ_COUNT to outperform an index for 3% of the rows.

Pando,

Why would I ignore what the optimizer is doing? You look at the trace and see it calculate a cost for every possible execution plan before settling on the lowest one. Because the cost calculated for the single bitmap was listed as 'Rejected' and the cost for Full-Scan was not 'rejected' it was because it had computed the cost of Full-Scan first and then computed the single bitmap and rejected it based on access-path cost.

That is exaxctly why the optimizer tweak fixed the problem because the costs were upside down and the bias reversed that.

I suppose your saying you can ignore it by creating an outline which has the effect of a server-side hint. The outline sounds promising, but no doubt has its own trade-offs and I would not utilize it in ignorance of how the optimizer came to the conclusion that it did.
05-07-2003, 12:27 PM
BJE_DBA

Quote:

Originally posted by SANJAY_G
Now I know why Donald Burleson calls it silver bullet.
http://www.dba-oracle.com/oracle_tips_cost_adj.htm

Sanjay,

Great link - I think Don is saying exactly what I am trying to state here -

"Even in Oracle9i, the CBO sometimes falsely determines that the cost of full-table scan is less than the cost of an index access."
05-07-2003, 05:41 PM
grjohnson

Quote:

Originally posted by BJE_DBA
You must be kidding me. Yes I had a 2 Terabyte database but never thought about DB_FILE_MULTIBLOCK_READ_COUNT and Max_IO ;)

Yes, yes, I sense your sarcasm, maybe I didn't write well enough for you to sense mine. :p
05-07-2003, 05:49 PM
BJE_DBA

Quote:

Originally posted by grjohnson
Yes, yes, I sense your sarcasm, maybe I didn't write well enough for you to sense mine. :p

Good one

:D

Show 40 post(s) from this thread on one page