SSD

There’s never enough time to read everything that’s worth reading, so even though Guy Harrison’s blog is one of the ones worth reading I find that it’s often months since I last read it. Visiting it late last night, I found an interesting batch of articles spread over the last year about the performance of SSD – the conclusions may not be what you expect, but make sure you read all the articles or you might end up with a completely misleading impression:

• Using flash disk for redo on Exadata
• Using SSD for redo on Exadata – pt. 2
• Exadata smart flash logging
• Exadata smart flash logging – outliers

Don’t forget to read the comments as well. For other notes Guy has written about SSD, here’s a URL for his SSD tag.

Indexing 12c

Another little detail that Hermann Baer mentioned in his presentation yesterday was the ability to create multiple indexes with the same column definition – something which currently gets you Oracle error “ORA-01408: such column list already indexed.” 

No details, and there’s always the “safe harbour” slide of course – the one which says seomthing about the presentation being only an indication of current thinking and nothing is guaranteed to appear.

Having said that, this looks like an interesting option for those (possibly rare) occasions when you want to change a unique index into a non-unique index (for example, to change a unique constraint to deferrable). Rather than having to drop the index and create a new one – leaving the table unindexed while the index builds, you appear to have the option to: “create new index online”, “drop old index”. Moving a primary key constraint from one index to the other might not be so easy, of course, especially if there are foreign keys in place – but this certainly looks like a helpful step.

Details to follow when 12c becomes available.

Partitioning 12c

Most useful presentation of OOW so far, from Hermann Baer of Oracle on improvements in partitioning features in 12c – and there are lots of useful ones, including:

Online move of a partition – so easy to compress a partition when it has reached its final “read-only” state

multiple partition maintenance in one operation – e.g. merge 3 monthly partitions into one quarterly partition, or split one partition into 24 (think about “how do I partition a non-partitioned table”, and 12c has just made it easier and quicker – exchange it into an empty partitioned table, then do one massive split).

partial indexing – define which partitions of a partitioned table should be included in the indexes you create on the table – and the optimizer also knows that different partitions need different plans (an enhancement of “table expansion”.

interval-reference partitionining – you can create an interval partitioned table, and user ref-partitioning to create child (and further decendent) tables, and their partitions will automatically be generated, truncated, and dropped as the parent is extended, truncated or dropped (needs enabled foreign key constraints).

Lots more details – and lots of stress-testing to be done – but I’m off to hear “the optimizer lady” talk about hints.

Reminder:

Oracle has a “safe harbour” slide at the start of all presentations about future developments pointing out that the information presented is an indication of direction, but not guaranteed to make it into production.

Row sizes 2

In an earlier post I showed you how you could generate SQL to analyze the distribution of row sizes in a table. In the introduction to the code I made a comment about how it failed to “allow for nulls at the end of rows”; a feature of Oracle storage that isn’t commonly known is that a set of consecutive null columns at the end of a row take up no storage space, while any null columns followed by a non-null column take up one byte (holding the value 0xFF) per column so that Oracle can “count its way” through the null columns to the non-null column. Consider this example:

Row sizes

A recent question on Oracle-L (a performance problem creating indexes that turned into an issue with migrated rows) prompted me to revisit a piece of code I first wrote about 20 years ago for an Oracle 6 system, and first published in November 2000. It addresses the question of analysing row-lengths in detail and, if you look at the example I’ve linked to, you’ll see that this could be very useful when you’re trying to work out suitable settings for pctfree and why you’re suffering from row migration.

The script is strictly limited to “simple” tables, by which I mean heap tables with columns that are basic data types and don’t include such things as Longs, LOBs, nested tables and all the other nasty things that usually break simple utilities. All it does is estimate the length of each row, then aggregating by row length. The estimate doesn’t allow for nulls at the ends of rows or columns longer than 254 bytes – technically the former don’t need length bytes and the latter use 3 bytes for the length when the column is more than 250 bytes. I don’t use the script often, and haven’t got around to including all the column types it won’t work for – that’s left as an exercise for the reader.

Minimum stats

Occasionally I come across complaints that dbms_stats is not obeying the estimate_percent when sampling data and is therefore taking more time than it “should” when gathering stats. The complaint, when I have seen it, always seems to be about the sample size Oracle chose for indexes.

There is a simple but (I believe) undocumented reason for this: because indexes are designed to collate similar data values they are capable of accentuating any skew in the data distribution, which means a sample taken from a small number of leaf blocks can be highly misleading as a guide to the whole index – so Oracle aims for a minimum sample size for gathering index stats.

I’ve found remnants of a note I wrote on comp.databases.oracle.server in December 2004 which claims that this limit (as of Oracle 9.2) was 919 leaf blocks – and I have a faint memory of discovering this figure in an official Metalink (MOS) note. I can’t find the note any more, but it’s easy enough to set up a test to see if the requirement still exists and if the limit is still the same. Here’s a test I ran recently on 11.2.0.3 using an 8KB block size:

Private Redo

Following a question on the Oracle Core Addenda pages, here’s a little script to tell you about the sizes of the public and private redo threads currently active in the instance. It’s a minor variation of a script I published in Appendix D (Dumping and Debugging), page 237 to show the addresses of current activity in the various log buffers:

Courses

I’m working on creating two new courses – one of them about Oracle structures (tables, indexes etc.) the other about Oracle mechanisms (undo, redo etc.). The aim of the courses is to help people do the right thing, avoid the wrong thing, and recognise the difference; the latter, as you might guess, is based loosely on my last book. The courses will probably be ready some time around March next year.

One of the problems of creating the content for courses like this is getting the balance between what people really need to know,  what they already know, and what they would really like to know (or know better); so I thought I’d throw the idea open to the public and ask the question: what would you like to learn (more) about if you came on one of these courses.

Generally I’m hoping for response like: “how Oracle does X”, “Why Oracle does Y”, “What’s the difference between A and B”, “When is P better than Q”, “Why would you use feature F “, but it would be particularly helpful if you could also include a comment about why you think the point ought to be included.

 

Histogram Generation

This really could be published in the Errata and Addenda of “Cost Based Oracle – Fundamentals”, but it’s more convenient to publish the whole thing here and just add a link to the Errata pages.

In chapter 7, on page 156, I gave an example of the type of SQL that Oracle runs (in the dbms_stats package) to generate a histogram. A sample of the code, and the plan from the 9.2.0.8 tkprof output, is listed below:

FBI Delete

A recent post on Oracle-l complained about an oddity when deleting through a function-based index.

I have a function based index but the CBO is not using it. The DML that I expect to have a plan with index range scan is doing a FTS. Its a simple DML that deletes 1000 rows at a time in a loop and is based on the column on which the FBI is created.

Although execution plans are mentioned, we don’t get to see the statement or the plan – and it’s always possible that there will be some clue in the (full) plan that tells us something about the code that the OP has forgotten to mention. However, function-based indexes have a little history of not doing quite what you expect, so I thought I’d take a quick look at the problem, starting with the simplest possible step – do function-based indexes and “normal” b-tree indexes behave differently on a delete. Here’s the data set I created for my test:

DDL triggers

One of the partitioning features that Oracle introduced relatively recently was the “interval partition”, for example:

create table transactions (
	account_id		number(8)	not null,
	transaction_date	date		not null,
	transaction_type	varchar2(2)	not null,
	transaction_id		varchar2(10)	not null,
	amount			number(10,2)	not null,
	padding			varchar2(100)
)
partition by range (transaction_date)
interval (numtoyminterval(1,'MONTH'))
(
   partition p200801 values less than (to_date('01-FEB-2008','DD-MON-YYYY'))
)
;

When I insert data into this table for a partition that doesn’t yet exist, Oracle will work out which partition it should be and create it automatically before doing the insert. The benefit of this trick, of course, is that the DBAs and developers don’t have to write any code to add partitions in anticipation of time passing and new data appearing.

Null – again

Here’s a (camouflaged) constraint definition I came across a little while ago in a production system:

create table t1 (
	v1 varchar2(10),
	constraint c1 check (v1 = 'A' or v1 = null)
);

Quick question – will the following insert statement work or return an error ?

insert into t1 values('B');

Online Rebuild

I’ve commented in the past about the strange stories you can find on the internet about how Oracle works and how sometimes, no matter how daft those stories seem, there might be something behind them. Here’s one such remark I came across a little while ago – published in two or three places this year:

“An index that enforces referential integrity cannot be rebuilt online.”

There are a couple of problems with this statement – first, of course, indexes don’t enforce referential integrity, though they may help to enforce uniqueness, and the so-called “foreign key” index may avoid a locking issue related to referential integrity: that’s splitting hairs a little bit, though, and we can probably guess what the author means by “indexes enforcing referential integrity”.  (An example demonstrating the problem would have been useful, though – it would have saved me from writing this note, and it might save other people from jumping to the wrong conclusion and taking unsuitable action as a consequence.)

So here’s a simple test (run under 11.2.0.3):

Bugs

Last week I published some code that demonstrated how a fast refresh of an aggregate materialized view could fail because of the internal use of the sys_op_map_nonnull() function. In some ways it was an easy problem to explain because all you had to do was trace the call to refresh and see what was going on. Today’s bug is completely different – it’s virtually impossible to see the connection between the failure and its cause. Here (as a cut-n-paste) is an example of what happens when the bug appears:

execute dbms_stats.gather_schema_stats(user)
*
ERROR at line 1:
ORA-01760: illegal argument for function
ORA-06512: at "SYS.DBMS_STATS", line 13336
ORA-06512: at "SYS.DBMS_STATS", line 13682
ORA-06512: at "SYS.DBMS_STATS", line 13760
ORA-06512: at "SYS.DBMS_STATS", line 13719
ORA-06512: at line 1

Surprises

I’ve given examples in the past of how you can be suprised by Oracle when a mechanism that has “always worked” in the past suddenly just doesn’t work because some unexpected internal coding mechanism hits a boundary condition. One example I gave of this was rebuilding indexes online – where a limitation on the key size of index organized tables made it impossible to do an online rebuild of an index on a heap table because of an ORA-01450 (maximum key length) error that was raised against the (index-organized) journal table that  Oracle creates internally to support the rebuild.

Slightly more recently the topic of the sys_op_map_nonnull() function came up in some comments about the ora_hash function – and I made a passing comment about the function appearing in materialized view refreshes and then never followed it up. Fortunately I got an email from Jared Still and Don Seiler a few days ago which contained a nice short demonstration of the problem so, with their permission, I’ve published it below.