Tony’s Oracle Tips

January 3, 2011

UKOUG national conference 2010

Filed under: Uncategorized — tonyhasler @ 5:16 pm

As I am sure the vast majority of readers of this blog will know, “Blog” stands for “Web Log” and reflects the fact that most blogs are diaries of one form or another.  Mine is one of a minority of blogs in the world (though probably not a minority fo Oracle blogs) that are used as a way of publishing purely technical material, rather than personal experiences.

This entry is a bit different in that, although the material is largely technical,it does indeed reflect some personal experiences.  Apologies to those of you who find the change of tone disappointing; it is not a sign of things to come.

The first, and most important, thing I have to discuss about this years conference concerns a question Wolfgang Breitling asked me during my “Stabilising Statistics” talk.  This presentation (material available for download here) focussed on improving execution plan stability without the use of stored outlines or SQL baselines.  One of the items I discussed was how execution plans changed as a result of bind variable values moving beyond the minimum and maximum values recorded in column statistics.  This question was of considerable interest to Wolfgang as he had himself presented a paper in 2009 on the same subject entitled “Seeding Statistics”.

Wolfgang’s original technique was to set the number of distinct values to 2 and the minimum and maximum values the same!  Of course this cannot happen in real life: if the minimum and maximum are the same then there is only one value.  Wolfgang reported that he got good, stable plans with this approach but my experiments suggested that setting the minimum value of the column to a very low value and the maximum to a very high value achieved better results.

Wolfgang’s question was this:  “What happens if you set the minimum and maximum values to NULL”?  Well, I knew that I had tried it and that the results were not satisfactory but I didn’t recall the specifics of my incomplete analysis.  I promised to blog an answer.  Well it turns out that the topic is quite complicated and Wolfgang and I have collaborated on some research over the holiday period and I will publish the results soon.

The second experience I wanted to share with you was an enlightening conversation I had with a few new friends in the pub.  The topic was Oracle security.  This important topic is something that I find rather dry and have not thought about as much as other aspects of Oracle database technology – such as performance.  I was talking to Martin Nash, Neil Johnson, Ben Thompson and others.  I suggested that if your data was sensitive in nature you had to buy Database Vault as DBAs would otherwise be able to see your data.  It was reminded that privileges such as ALTER DATABASE, ALTER USER, and the SELECT_CATALOG role did not allow a DBA to read any business data at all.  In fact, as long as you control the rare occasions when a database needs to be bounced (that does require SYSDBA) your DBAs should be able to do most if not all of their work without having access to any business data!  Limiting DBA privileges may not be as complete a solution to role segregation as Database Vault but when I thought about it I realised that it certainly goes further than I had previously realised.

Last but not least I want to mention the other event that I was involved in at the conference:  a debate with Jonathan Lewis entitled “Does Oracle Ignore Hints”?  One thing that I learned from Jonathan during the preparation for my debate was that the technical analysis in this entry was completely wrong.  I have posted a correction here.

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: