because you generated histograms so it uses index now
when you change optimizer_* parameters you change the way CBO calculate the costs that´s why you see different costs however resposne time shouldnt be different if plan is the same
Printable View
because you generated histograms so it uses index now
when you change optimizer_* parameters you change the way CBO calculate the costs that´s why you see different costs however resposne time shouldnt be different if plan is the same
htanga, could you "set autotrace on exp stat" and post the analysis (not the data) for the two cases so that we can look at it.Quote:
Originally posted by htanga
"With exactly the same execution plan (test2 and 3) but different cost, we obtain different response time. How could it be possible if the plans are the same ?"
Why and How do we get different cost values for a single statement.
There are 2 important parameters most DBAs underutilzed.
One is OPTIMIZER_INDEX_CACHING and the other OPTIMIZER_INDEX_COST_ADJ.
The default value for OPTIMIZER_INDEX_CACHING is 0, that indicates to the CBO that 0 % of database blocks accessed using indexed access can be expected to be found in the Bffer Cache of the SGA. This implies that all index access will require a physical read from the I/O subsystem for every logical read from the buffer cache.
The default value of 100 ( for OPTIMIZER_INDEX_COST_ADJ) indicates to the CBO that indexed access is 100 % costly (i.e equally costly) as Full Table Scan access.
These default values are wildly unsuitable and unrealistic.
I would suggest change OPTIMIZER_INDEX_CACHING value to 90 and OPTIMIZER_INDEX_COST_ADJ to 10 or 20.
These 2 parameters can be changed at session level.
Try to play with these 2 parameters and run explain plan for a SQL statement.
With the reduced value for OPTIMIZER_INDEX_COST_ADJ, now you can see your optimizer will choose correct index. More over, you would see the total cost for the SQL will go down.
Oracle calculates the cost based on the following formual:
CALCULATED_LOGICAL_READS * (1 - (OPTIMIZER_INDEX_CACHING/100)) = CALCULATED_PHY_READS = COST.
So with default value 0 for OPTIMIZER_INDEX_CACHING, the above formual does not make any sense. Hence, set it to 90 or 95.