Oracle Observations

December 18, 2011

The UKOUG performance tuning round table.

Filed under: Performance — bigdaveroberts @ 2:59 pm

At the tuning round table there were three experts: Jonathan Lewis, Maria Coulgan and Greg Rhan.

Hopefully Jonathan Lewis needs no introduction, Maria Colgan has worked at oracle in the server technology performance team and Greg Rahn works in the real world performance group, specialising in data warehouses and parallel query.

The first question was how the merge command speeded up processing.

The answer was that it produces 2 arrays and does bulk inserts and bulk updates.

However it was stated that depending on the actual data, array processing with exceptions might be a more effective solution.

In a discussion on sub-query un-nesting it was observed that giving the oracle optimiser a nested sub-query gives oracle the opportunity to un-nest the query, if you un-nest the query yourself Oracle has less potential optimisations to consider.

However, when Oracle un-nests a query, it doesn’t always produce the same plan that would be generated if the optimiser was presented with an already un-nested query!

In a discussion of AutoDoP (Degree of Parallelism), it was noted that it doesn’t consider the cost of the insert. I.e. a CTAS might select the wrong degree of parallelism for the select if the insert cannot cope with the rate that data is generated by the query!

AutoDoP can also be confused by compressed data.

It was suggested that the lack of persistence in Oracle’s implementation of cardinality feedback was a problem.

The response was: ‘watch this space’, presumably indicating that functionality in this area is still evolving and this is one of the things that might change!

One interesting thing that I had not considered was the possibility of forcing the optimiser version used to execute a query can be forced using a hint.

Standard edition was then considered, primarily because Standard edition included stored outlines, which are now deprecated but can’t be replaced by SQL plan management because this is currently an enterprise only feature!

It was suggested that while stored outlines were deprecated, there were currently no plans to de-support this feature.

Slightly off topic the question was asked: What is your favourite new feature in standard edition?

The two answers offered were:

1. Extended statistics
and
2. Pending statistics

On the subject of flash cache, it was suggested that using flash cache was only of potential value when you had maxed out the ram in the machine. i.e. the overhead of managing data in memory was much lower than the overhead of managing data in flash cache through the disk i/o code path.

Advertisements

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

Create a free website or blog at WordPress.com.

%d bloggers like this: