Oracle Scratchpad

June 18, 2010

I wish (2)

Filed under: Oracle,SQL Server,Wishlist — Jonathan Lewis @ 6:13 am BST Jun 18,2010

Continuing my little list of things in SQL Server that I’d like to see in Oracle:

1 Index statistics: when SQL Server collects index statistics, it also collects stats not only on the full index definition, but also on every “prefix” of the index, i.e. if you have a 4-column index, you will get stats for the first two columns and first three columns, as well as the full index stats.

Of course, you can emulate this in 11g with extended statistics – and you might argue that it’s better to make it a deliberate choice. Given the new ‘approximate NDV’ feature in 11.2 it might not be an expensive option to implement.

Here’s a related observation –  one that I haven’t tested to its limits, though, so you might was to check how well it works. The “approximate NDV” isn’t used for calculating the number of distinct keys in an index, but there have been occasions when I have noticed that dbms_stats.gather_index_stats(), and related calls,  will use the number of distinct values of the underlying column as the number of distinct keys in a single column index – and that’s a massive performance benefit. As a follow-up, then, if you create extended stats on the full combination of columns of a multi-column index, Oracle sometimes seems to use the same shortcut for a multi-column index  – but I haven’t investigated when this works and when it doesn’t. There may be a timing element involved.

2 Deprecated Features: one of the “events” that you can track with SQL Server’s Profiler is “Deprecated Events”. This allows you to collect details of when deprecated features are used in your code and plan ahead to changing the code rather than having a rush on the next upgrade. I’m not sure how well it would work on a large, busy system – the compromise between reporting the event too often and failing to report it might be a hard to manage – but it would be  nice to have an occasional warning in the alert log with (say) some of the client,  module or procedure information from v$session.

Update Feb 2013

According to the final section (Effect of auto sample size on index stats gathering) of this note from the Optimizer Group, a call to gather index stats should use the NDV on a suitably defined column group to avoid the cost of taking a large sample from the index – provided the table stats are sufficiently up to date.

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

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,267 other followers