In a recent thread on one of the Oracle Forums, someone asked the question:
So, in general, if systems statistics are in effect, would you or would you not make any adjustments to the optimizer_index_cost_adj and optimizer_index_caching parameters?
Under what circumstance?
This is my reply:
A few days ago I received an email about system statistics. I decided it was worth replying to, provided I wrote my response up as a series of questions and answers on the blog. (I don’t tend to respond to individual questions – it’s not an effective use of my time – so there has to be a good reason for replying).
I received an email today containing the following question:
For packaged applications, like the Oracle EBS, some indexes tend to develop a high Clustering Factor over time, like the one shown below:
BTREE LEAF DISTINCT CLUSTERING INDEX TABLE TABLE INDEX NAME LEVEL BLOCKS KEYS FACTOR NUM ROWS NUM BLKS NUM ROWS ---------- ----- ------ ---------- ---------- ---------- -------- ---------- XXX_PK 3 778150 77,842,100 17,163,350 77,842,100 865,805 77,043,200We have some queries that perform range scans on this index and run slower because they have to visit too many blocks from the table. How should one deal with this type of index?
This question raises a couple of important points that are worth reviewing.
In chaper 2 of Cost Based Oracle – Fundamentals, I made the following comment about system statistics.
“… you could simply calibrate your hardware (or at least the I/O subsystem) for absolute performance figures …”
Those of you who have kept up with the corrections and addenda to “Cost Based Oracle” that I have published from time to time on my website will be familiar with the name of Alberto Dell’Era, who took some of my notes as a starting point and went a lot futher with the investigation of join cardinality.
Alberto has now published his latest research into the method that Oracle uses for calculating the cardinality on joins between columns that have histograms in place.
The URL takes you to an introductory page that leads to a 25 page pdf file, and a zip file with some sample SQL scripts. It’s an enormous step forward in understanding how Oracle uses histograms.
When the Tuning Advisor suggests that you accept a SQL Profile, what is it offering you. If you want to find out, the following SQL seems to be the appropriate query to run before you accept the profile:
(more…)
When you upgrade from 9i to 10g (or higher) you may find lots of execution plans suddenly “go wrong”. There are three main reasons for this. The first is that 10g automatically enables CPU costing, and if you weren’t using it in 9i this is likely to make a difference – often an improvement but you may be unlucky. The second is that there are numerous new optimisation strategies in 10g, and some of them may not be good for your particular data patterns, or your particular types of query. (more…)
I received an email recently from someone who had just upgraded the Oracle 11i Business Suite (11.5.9) from 9.2.0.6 to 10.2.0.2. After the upgrade, the following SQL statement (shown here with its original format – not according to my standards) started failing with Oracle error: ORA-01722: invalid number.
(more…)
[Further Reading on Statspack]
I see that Doug Burns has just published an example of “reason 2″ for using Statspack.
The reason for mentioning this particular posting is not specifically its reference to Statspack, it’s for the throwaway comment that Doug uses to explain how he was rapidly able to address the problem highlighted by Statspack:
(more…)
If you take a look at view user_tab_columns (or equivalent) you will find that its definition includes columns low_value and high_value, which give the currently known low and high value for the data. For example:
(more…)
Posting from the UKOUG (UK Oracle User Group) conference. A couple of useful details from Tom Kyte’s technical keynote on “Things which might be in 11g but we’re not making any promises and you can’t hold us to it”.
The optimizer will be extended to allow us to collect some statistical information about correlated columns. This should help the optimizer to deal with combining predicates like: “Orders made in the last two weeks” with “Orders that have not yet been delivered” – if they’re recent orders, they’re more likely to be undelivered. I’m looking forward to seeing how far the Optimizer team has got with handling this rather difficult problem.
Partition handling: one of the current irritations for partitioning is that you have to disable referential integrity between partitioned tables if you want to drop old partitions. (Drop child partition Jan2001, followed by an attempt to drop the “obviously matching” parent partition Jan2001 currently results in Oracle error “ORA-02266: unique/primary keys in table referenced by enabled foreign keys”). 11g will give us the ability to declare that the partitioning of the child table is dependent on the partitioning of the parent table, and therefore guaranteed to be in-synch with the parent table. Apart from handling the drop partition problem, this should also help to avoid accidents that manage to disable partition-wise joins.
Here’s an idea I had some time ago, but have never implemented on a production system. I’d be interested to know if anyone has tried it.
Virtual columns are useful because they can give Oracle statistics about values derived from stored columns. So, for example, you might like to have a virtual column defined as salary + nvl(commission,0) so that the optimizer can estimate resonably accurately how many employess have salary + nvl(commission,0) > 100000.
(more…)
[Updated 28th November 2011] – just after the fifth anniversary – to mark this as the first post of the Oracle Scratchpad.
A recent post on one of the OTN Database General forum pages asked about the effect of having the parameter optimizer_index_cost_adj set when you enable system statistics (also known as CPU costing).
Theme: Rubric. Blog at WordPress.com.
