Oracle Scratchpad

Public Appearances

8th June 2017 Reading, UK: I’ve volunteered to speak at the UKOUG Database & RAC Cloud, Infrastructure & Availability SIG that is taking place at Thames Valley Park. I’ll be speaking on two topics: “Histograms in 12c” and “Partitioning Enhancements in 12cR2” as possible topics.

Histograms in 12c:

The optimizer needs good statistics to produce good plan, but when there are any types of skew in the data you may need histograms to help the optimizer.

Before 12c histograms could be expensive to create and, if your data patterns were unlucky, could cause as many problems as they solved. Things are much better in 12.

Historically Oracle gave us frequency histograms and height-balanced histograms, so we start this presentation with a brief look at their respective costs, benefits and threats, and the special cases where better strategies are needed. We then move on to looking at the enhancements to histograms that appear in 12c showing how an extension to the “approximate NDV” mechanism from 11g allows frequency (and the new, Top-N frequency) histograms to be built far more accurately and efficiently, and how the new “hybrid” histogram gives us more information with less risk using no more effort than the original height-balanced histogram.

Partitioning Enhancements in 12cR2

Oracle Corp. keeps enhancing the way in which we can partition data, aiming to make it easier to define, use and maintain partitioned data.

In this presentation we will touch on a few of the features that appeared in the first release of 12c plug gaps in the functionality of 11g and reduce maintenance effort; then we’ll move on to the new features of 12.2 that are likely to be of most use to designers and DBAs – particularly in the task of converting non-partitioned objects into partitioned objects, and cleaing up existing partitioned objects.

21st June 2017 Glasgow, UK: I’ll be speaking at OUG Scotland 2017. My session will be about “Reading Parallel Execution Plans” and I’m on standby to present “Indexing and the Impact of New Technologies” if a late cancellation appears.

Parallel execution plans are harder to read than serial plans because you really need to understand the impact of the order of operation, distribution mechanisms chosen, and (in recent versions of Oracle) the timing of the generation and use of Bloom filters.

In this presentation we examine the basics of how parallel execution slaves work, and the way in which this can result in a massive difference between the apparent join order of an execution plan and the actual order of rowsource generation of that plan.

We learn about “Table Queues” and “Data Flow Operations” and how they help us follow the order of operation, how the different distribution method can make a dramatic difference to performance, and how we can control them if it really becomes necessary.

We then examine the “DFO Tree” that shows us how parallel queries can use far more PX servers than expected, and make it harder to determine order of operation.

1st/2nd Sept 2017 Cracow, Poland: I’ll be speaking at the Polish Oracle User Group event. The abstract I’ve submitted for the session has the title: “Just Don’t Do It”.

I’ll be working through a number of examples – taken from the OTN database forum, or from (camouflaged) client systems – of ways in which excess work can be eliminated. The options vary from basic configuration to simple code changes, from rephrasing the requirement to restating the logic, and being aware of the way that you may have missed features that are perfect matches for your requirement.

13th/14th Sept 2017 Zurich, Switzerland: I’ll be speaking at the Trivadis Performance Days 2017. I’ve got two sessions of 80 minutes each: “Getting Rid of Indexes” and “Reading Parallel Execution Plans”. The full list of speakers is extraordinary, and it’s going to be two days well worth attending.

Getting Rid of Indexes

When there’s little you can do about the table definitions, the SQL, or the coding strategy all you’ve got left to play with to improve performance is the indexing.

For many people the first question that springs to mind with indexes is “What index can I add?”, but often the more important questions are “Which indexes should I drop?” and “Which indexes should I change?”

In this session we will see how indexes cause problems, how to spot indexes that are probably a waste of space and time, how maximise the benefit of indexes and how to minimise the risk of dropping or modifying index definitions.

Reading Parallel Execution Plans

See Glasgow event above.

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Powered by WordPress.com.