Oracle Scratchpad

Public Appearances

and I’m on standby to present “Indexing and the Impact of New Technologies” if a late cancellation appears.

1st/2nd Sept 2017 Cracow, Poland: I’ll be speaking at the Polish Oracle User Group event. The abstract I’ve submitted for my session (day 2 – see agenda) 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

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 methods can make a dramatic difference to performance, and how we can control them if it really becomes necessary.

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

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.