This video and set of resources is a follow up to the webinar broadcast by Red Gate on 01 August 2012.
You may want to watch Oracle Cost-Based Optimizer Basics before you view this video
The video above is accompanied by the following downloadable resources:
- Read me
- Presentation slides – PDF version
- Presentation slides – PowerPoint version
- concurrent_execution_inserts_check_results.sql
- concurrent_execution_inserts_testcase.sql
- concurrent_execution_launch_slaves.sql
- daft_data_type_example.sql
- frequency_histogram_egde_case.sql
- histogram_limitations_testcase.sql
- implicit_datatype_conversion_nvarchar_scratchpad.sql
- insert_no_wait.sql
- xplan_extended_display_cursor.sql
- Download all resources (.zip)
Building on the previous Cost-Based Optimizer Basics webinar, in this almost “zero-slide” session we’ll explore different aspects of the Cost-Based Optimizer that haven’t been covered or only mentioned briefly in the ‘basics’ session.
This is a continuous live demonstration including topics like: Clustering Factor, Histograms, Dynamic Sampling, Virtual Columns, Daft Data Types and more. If you’ve ever asked yourself why a histogram can be a threat to database performance and why storing data using the correct data type matters regarding Execution Plans then this session is for you. It is recommended, although not required, to watch the recording of the ‘basics’ webinar first.
A live Q+A session with Randolf Geist follows the presentation.
2 Comments
Ivan Nosenko
02/08/2012
It seem that problems with height-balanced histograms (when for 5000 value was wrong estimete) can be eliminated if you increase buckets up to 255.
Am I right?
Randolf Geist
03/08/2012
Hi Ivan,
no, the problem would still be the same - it cannot be avoided by using a higher bucket size (the scripts are now available, you can try it yourself).
Independent from the number of buckets, you might be lucky, if you use sampling, that 5000 will become popular. But if you repeat the statistics collection a couple of times, the problem will show up again.
If you compute the statistics then even with a maximum bucket size of 254 5000 will still be unpopular.
So there are column data distribution patterns that simply don't work well with histograms generated by Oracle.
Hope this helps,
Randolf
No trackbacks yet.