When people ask for help on (for example) OTN, they are often asked to supply further information – sometimes in the form of requests for results from SQL queries. If you are ever in this position, you may find that you don’t understand what the query does, or why the information is useful – nevertheless you can still do something to make it as easy as possible for your potential saviour to help you.
Here’s an example to show you how NOT to do it:
Here’s a question on OTN from a SQL Server user that should prompt a few interesting ideas. Re-arranged and paraphrased it goes something like this:
In SQL Server I can write the following code:
DECLARE @Counter INT SELECT @Counter = 0 UPDATE TempDB.dbo.TransactionDetail SET @Counter = AccountRunningCount = @Counter + 1 FROM TempDB.dbo.TransactionDetail WITH (TABLOCKX)What I want to do is more like this:
DECLARE @Total INT = 0 UPDATE StringOutput set @Total = SumOfLength = @Total + ColLength OPTION (MAXDOP 1)How do I do something similar in Oracle ?
A recent question on OTN asked how you could model a case where Oracle had the choice between a “perfect” index for a range scan and an index that could be used for an index skip scan and choose the latter path even though it was clearly (to the human eye) the less sensible choice. There have been a number of wierd and wonderful anomalies with the index skip scan and bad choice over the years, and this particular case is just one of many oddities I have seen in the past – so I didn’t think it would be hard to model one (in fact, I thought I already had at least two examples somewhere in my library – but I couldn’t find them).
Take a data set with two columns, call them id1 and id2, and create indexes on (id1), and (id2, id1). Generate the id1 column as a wide range of cyclic values, generate the id2 set with a small number of repetitive values so that a large number of physically adjacent rows hold the same value. The clustering_factor on the (id1) index will be very large, the clustering_factor on the (id2, id1) index will be relatively small because it will be controlled largely by the repetitive id2 value. Here’s the data set:
(more…)
I came across a nice bit of code on OTN recently that someone had written several years ago (in 2007, in fact) to demonstrate the different ways in which the optimizer would handle “select count({something}) from table;”. If you want to copy and repeat the test code, you may need to adjust it slightly – it references a type vc2s, which I changed to dbms_stats.chararray, and it references a plan table called toad_plan_table, which I replaced with references to the standard plan_table (getting rid of the truncate as I did so).
The code simply executes a call to explain plan for different statements, then extracts the projection information for the “sort aggregate” line of the execution plan (examining the operation and options columns to do so). The thing that particularly surprised me was that there was a difference between the following two queries:
(more…)
We’ve reached that time of year (Autumn, or Fall if you prefer the American term) when I’m reminded that tending a garden is like tending an Oracle database.
This is a picture of the oak tree on my front lawn, taken about 4 hours ago. Looking at it now it shows hardly any sign of the coming winter and little of the colour that let’s you know it’s preparing to drop its huge volume of leaves, but yesterday morning I spent the best part of an hour raking up leaves that had dropped over the course of the previous week.
Over the next six weeks, I’ll be out be out with my leaf rake every few days to clean up the mess – and I’ll look down at the mess that’s on the ground, then look up at the mess that’s waiting to join it, and then I’ll do just enough work to make the lawn look just good enough to keep my wife happy for a few more days until I get sent out to do it all over again.
You can spot the analogy, of course – it’s important to think about how much effort it’s worth spending to get to an end result which is good enough for long enough. There’s no point in spending a huge amount of effort getting a fantastic result that is going to be obliterated almost immediately by the next problem that gets dumped in your lap. When the tree is nearly bare, I’ll do a thorough job of clearing the leaves, until then, 95% is easy enough, and good enough.
Footnote: avid arboriculturalists might wonder why the tree is lop-sided – being a little light on the side towards the road – it’s the sort of thing that happens when a tree gets hit by a lorry.
For anyone looking for information on 12c, there are several posts about OpenWorld at the dbi Services blog (see links at right). In particular there’s a summary post about the “pluggable database” describing how you could plug a database into a “container” database, clone it inside the container database, then unplug it and put it somewhere else.
When I heard about the feature, it crossed my mind that there were two “obvious” targets for this technology that Oracle had in mind – in no particular order:
Currently “real” consolidation means lots of work to change multiple databases into multiple schemas in a single database, and worrying about public synonyms; running multiple copies of the same application in the same database demonstrates the most extreme example of how pluggable databases bypass the problem. Just think how nice it would be, as a service provider, to keep a single “empty” Peoplesoft pluggable database in your container database which you clone whenever you sign up with a new customer. And, as a customer, if you want to change your service provider, perhaps you could insist that you supplier unplugs your Peoplesoft database so that you can plug it in at your new service provider.
Yesterday I went to London to visit Pewterers’ Hall, the base of The Worshipful Company of Pewterers (est. ca. 1450), to give a presentation at the Server Tech special interest group of the UK Oracle User Group.
I was on as the last event of the day, and the title of my presentation was “The Pessimist’s view of Exadata” – which, I have to say, starts with a slide where I point out that I think Exadata is a wonderful piece of kit. Unfortunately I over-ran my time, but I had a reasonable stopping point so I stopped nearly on time, but said that if anyone wanted to stay on after the close of business I’d go through the rest of the slides.
Ten people were able to stay on, and asked several questions about use and strategy – and that sort of enthusiasm really makes me feel I’ve done something worthwhile. And then something like this happens – and really gives me a massive boost.
After the success of the last Oracle/SQL Server discussion, James Murtagh of Red Gate has arranged another online discussion – this time about the different ways in which temporary tables are implemented and used. As before I’ll be doing the Oracle bit and Grant Fritchey will be doing the SQL Server bit.
Update: Now that the event is over, you can listen to the recording at this URL.
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:
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.
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.
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.
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.
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:
(more…)
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.
(more…)
A question I asked myself recently was this:
Which is the worst offence when publishing an article about some feature of Oracle:
I don’t think it’s an easy question to answer and, of course, it’s not made any easier when you start to consider the number of cases for which a feature does or doesn’t work (how many cases is “some cases”), and the frequency with which different cases are likely to appear.
(more…)
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:
(more…)
Theme: Rubric. Blog at WordPress.com.
