Analyze this!

There’s a question on the Database General forum on OTN containing the following observation:

I have some chained rows in some of my tables. I have exported, truncated, import back the data in the table. Then collect the stats. But I have the same Chained_count. Nothing has changed.

If you export, truncate the table, then re-import the data, you expect to get rid of any migrated rows – but if a row is too big to fit in a single block, or has more than 254 columns, (either of which will be reported as a chained row) it’s not going to change after exporting and importing; so the first question to ask is: “What’s the definition of the table ?” optionally followed by: “Can you run some SQL to check for very long rows.”

Strategic Hints

I try to avoid hinting SQL if possible as it is very hard to do well, but there are a few hints that give an overview of how a query should operate without trying to control the detail of what the optimizer does. These are the hints that I call the “strategic hints” (possibly “query block hints” would be a better name – but there may be hints at the query block level that I wouldn’t qualify as strategic), and an example came up on the comp.databases.oracle.server newsgroup recently that looks like an ideal example of how a couple of them could be used.

Summary Stats

As I write this post my page view counter has just clicked past the “magic million” marker – so I thought it was time to update the previous summary of what’s been of most interest to visitors:

If we look at the complete lifetime of the blog, the top five are:

Cartesian Merge Join	14,982
Bind Variables	11,474
dbms_xplan in 10g	10,719
NOT IN	10,661
Index Rebuild 10g	8,811

But restricting view to just the last year this changes a little to:

NOT IN	7,351
Index Rebuild 10g	6,636
Cartesian Merge Join	6,626
dbms_xplan in 10g	5,959
SQL Profiles (10g)	5,010

Of course – while 1,000,000 sounds like quite nice landmark figure to me – I can always put things into perspective by remembering that one of my clients had already reached a rate of 1,000,000 page hits in five minutes several years ago; and then there’s a video on youtube.com of Susan Boyle performing for “Britain’s Got Talent” that clocked up more than 42 million views in less than a week.

Histogram change

When you upgrade from 10.2.0.3 to 10.2.0.4 or 11g, watch out for SQL that depends on the existence of frequency histograms.

In 10.2.0.3 (and earlier versions of Oracle) if you have a frequency histogram on a column, and then use a predicate of the form:

where colX = {value not found in histogram}

then the optimizer would calculate a cardinality of one for this predicate (having recorded a density of 1/(2 * num_rows) in the data dictionary (see comments 21 and 22 of this note on dbms_xplan).

Locking SQL

A question that comes up fairly regularly on OTN Database Forum and the comp.databases.oracle.server newsgroup is one that asks some variant of:

“When a session has some rows locked in a table, how can I find the statement that locked those rows ?”

Virtual Columns

A recent post on comp.databases.oracle.server asked:

I have read various articles on virtual columns? I still do not understand their advantages other than they save some disk space, one can put logic of virtual columns in a trigger which will save information in a real column. Real column will need some additional disk space, but it will save some cpu time when one is doing selects on “virtual” columns.

Model

When the model clause appeared a few years ago, it went on my list of “interesting, could be useful, must take a closer look some time, not urgent”.

Since then I’ve not come across any good reason for needing the feature, although other people have clearly found it very useful in their environments.