Index Efficiency 2

Some time ago I wrote a note on index efficiency that referenced, in one of the comments, an article on my old website about how you could use existing statistics to estimate the “correct” size of an index.

A little while ago, while applying the concept on a client site and implementing a mechanism that would allow the DBAs to do the same check as regularly as needed, I discovered a couple of flaws in the original code – so I had to spend a little time correcting it,and I’ve finally published the latest version here.

SQL Injection

One of the members of the Oak Table Network sent me a link to an article about Websites and SQL Injection a couple of days ago. It’s a simple little story and though I’m always a little suspicious of believing every article I read on the Internet, I can’t help thinking that this one is probably worth putting in front of anyone who has anything to do with publishing data on web sites reads it.

(I may be proved wrong by follow-up comments, of course).

Tom Kyte

It’s an old posting, but I came across this item a few days ago where Tom Kyte has listed URLs to several of the useful utilities that he has published on the Internet.

(I’m hoping the print_table() routine will appear on the list  some time soon. It’s disappeared from the link I used to have for it.)

Chinese Translation

A DBA from China has asked if he can translate some of my material for publication on his blog, and I’ve given him the go-ahead – provided he emails me each time he publishes a piece.  There’s a new link in the panel to the right that takes you to a catlogue of the pieces he’s done so far.

CBO Surprise 2

In an earlier article I gave a description of how splitting a single date ranges into a pair of date ranges with an OR would change the arithmetic and so run the risk (or introduce the benefit) of changing the execution plan.

At the time I made a couple of comments about other details that could be demonstrated by the same query – but postponed saying anything about them. This follow-up article addresses the omission.

Dynamic Sampling

If you read the manual pages about “dynamic sampling” it’s easy to get just a little lost in the detail; so this is a brief overview of the variations in the strategies used.

CBO Surprise

Someone sent me an email recently with the following observation:

“I’ve got a query involving a range scan on a date column. When I change the query to split the range into two consecutive ranges with an ‘OR’ the execution plan changes.”

Despite the title, this isn’t really a surprise, even though it might seem a little unexpected. The CBO assumptions for disjuncts (predicates with OR) means that the selectivity is almost guaranteed to change when you do this, and a change in selectivity could easily lead to a change in the execution plan. Here are the results from a model I created to reproduce, as closely as I could, the behaviour displayed by example plans that I was sent:

Time on Stats

In a blog that’s now three years old I pointed out the issue of the automatic stats collection job and the way it could affect performance by changing object-level statistics behind your back. There’s another reason it could have an annoying side effect, though – it actually takes time to collect statistics.

The automatic job is, by default, allowed to run from 10:00 pm to 6:00 am on weekdays and for even longer over the weekend – and the time it needs to complete can vary dramatically from day to day. So it’s nice to have a little script to cross-check when it started and how long it ran. All you have to do is query the view dba_scheduler_job_run_details:

TO Locks

Have you ever seen any unexpected ‘TO’ locks in your system ? These are ‘Temp Object’ locks and appear when you’ve been using global temporary tables (GTTs) – and possibly at other times, but I haven’t noticed them in other situations yet. But here’s a simple cut-n-paste, from a freshly started session, which results in a ‘TO’ lock appearing for no apparent reason (this is running 11g, which is why we also see an ‘AE’ (application edition) lock):

Performance myths

How legends are born: http://www.simple-talk.com/content/article.aspx?article=931

But it won’t always work: http://blogs.sun.com/brendan/entry/unusual_disk_latency

Lock Horror

Here’s an extract from a  simple script I’ve run from time to time over the last 10 years to create a little testbed to check up on “foreign key locking”. I haven’t run it for some time (my notes say the last version I ran it on until very recently was 10.1.0.2). All it does is create a pair of tables with a foreign key constraint between them and an index protecting that constraint from the foreign key locking problem:

Ignoring Hints – 2

Here’s a little puzzle that someone sent to me a couple of days ago – it’s a case where the optimizer seems to be ignoring a hint.

Why Undo ?

A recent thread on the OTN database forum asks the question:

“… since redo also has past and current information why can’t redo logs be used to retrieve that information? Why is undo required when redo already has all that information.”

The thread has generated some interesting replies – but to a large extent they describe how the undo and redo work rather than explaining why the designers at Oracle Corp. chose to implement undo and redo the way they did. Since I’m sitting in an airport (Zurich – where the coffee bar in the business lounge has a very large bowl of Lindt chocolate squares available) waiting for a plane I thought I’d use the time to share my thoughts on the why.

Shrink Tablespace

Here’s an example of a theme that appears on the OTN database forum from time to time (I haven’t included a link to it because it’s just one example of many similar questions):

“I have a user tablespace allocated for 3-4 schemas. As I urgently needed space on hard disk I had to remove one of the schema(drop user). Now this tablespace is shown as 70% filled. I want to reduce allocated space to it.”

SQL Server

A few days ago I did a presentation on SQL Server. This probably sounds a little strange given my status as an Oracle specialist – but the nice people at Microsoft asked me if I would contribute to one of their seminars so I downloaded and installed the 180 day free trial copy of the Enterprise version, then downloaded the “Books Online” manuals and started to play.

It was an interesting experience – and I think the audience (and organisers) found my presentation useful. The title was “What the Enterprise needs in an RDBMS” – and that’s something I do know about – and the presentation was about whether or not you could find everything you needed in SQL Server 2008, where you’d have to look in the manuals, and supplementary questions you’d have to ask.