Meanwhile at the Ship and Shovell

"Ah," the barmaid said, "my favourite round." The order is a pint of Fursty Ferret, a pint of Badger Ale, a pint of Amstel and a pint of cider. But why would a barmaid have a favourite round?

Well, the Amsel and cider use automatic pumps. The barmaid places a glass under each tap and sets them running. Ferret and Badger, being proper ales, are served from hand pumps. Again, she places a glass under the two taps and, grasping a handle in each hand, draws both pints simultaneously. With the result that all four pints are ready together, in the minimum elapsed time.

Parallel processing, you can't beat it.

Log Buffer: #104: a Carnival of the Vanities for DBAs

Today, 4th July, is Independence Day. I know this because Tech Republic has sent me an e-mail of special Independence Day offers. Only not that special, as the list seems to be the same list of offers they mailed for Father's Day. At least that made sense: after all, nothing says "You're the best dad in the world" quite like a gift of the Administrator's Guide to TCP/IP But what sort of patriot celebrates Independence Day by settling down with IT Professional's Guide to Policies and Procedures, Third Ed instead of fireworks, corn dogs and "light tasting" beer? Probably the sort of patriot who reads Log Buffer, so I'd better get on with it.

Staying with the Independence Day theme Curt Monash picks up on a humourous press release from data warehouse appliance vendor Dataupia. It's in the form of a Declaration of Data Independence and is probably funnier if you're American.

In the UK there have been rumours that the government is planning a giant database to track all our telephone and internet activity. On the BCS blog David Evans skips the ethical dimensions and looks at some of the practical considerations. However, the most pertinent point is made by Matthew in the comments: "How many days after the launch of the Big Brother Database ... do you think it will be before someone loses a disk or backup tape full of its contents?"

I'm just an Oracle person, which according to Max Kanat-Alexander means I suffer from Oracle-itis. Apparently symptoms include not being able to recognise the difference between NULL and an empty string, and thinking that one thousand items is a sensible limit for an IN clause. Kevin Closson posted a suitably withering response in his series on things which doth crabby make.

Anyway, doing the Log Buffer has given me - with the assistance of David Edwards and Google - with some exposure to other databases and other ways of doing things. For instance, Leo Hsu and Regina Obe wrote about inheriting tables in PostgreSQL. This is quite a neat idea.

"lets say you developed a timesheet app for an organization and each department insisted on having their own version of the app and each along with the basic fields needed to track some additional ones of their own. Then higher forces came in and said I need to know what everyone is doing, but I don't need to see all that other crap they keep track of.. Two options come to mind - create a bunch of views that union stuff together or institute a round-up-the-children-and-adopt-them program."

In Oracle the only option would be the view (possibly of the materialized kind). Off the top of my head I can't recall a case where I could have used this but it's definitely the sort of capability it's nice to have in your back pocket.

Another intriguing idea which has no parallel in Oracle is the MySQL Sandbox. This is a framework for testing features of different versions of MySQL without jeopardising our primary environment. Its developer, Giuseppe Maxia, The Data Charmer announces that MySQL Sandbox 2.0 has been released.

Regardless of which database you use performance is always an issue. Hubert Lubacewski has a offers a technique for identifying who is is trashing the performance of your PostgreSQL database. Arjen Lentz posts a MySQL script for finding useless indexes. The problems are the same, but the metrics are very different from the ones I'm used to in Oracle: "The query returns all indexes in a db where the cardinality is lower than 30% of the rows, thus making it unlikely that the server will ever use that index." Peter Zaitsev on the MySQL Performance Blog discusses the importance of identifying where the bottlenecks are. There's no point in a web developer tweaking CSS or JavaScript if the real problem lies in the database access layer: "get real numbers for your application before you decide." Ken Downs , the Database Programmer, has some general SQL advice on designing your web application's data model. Mr Oracle Index himself, Richard Foote, gives us his 3 Steps To Performance Tuning.

Transaction management is one of those things which varies considerably from product to product. Many Oracle practioners still think MySQL doesn't have transaction management. This is a canard Pythian's own Keith Murphy lays to rest by writing on transactions in InnoDB. In a related post covering transaction basics says he may write further pieces on "the major storage engines and their transactional characteristics". I presume he means the different MySQL storage engines but I think there's scope for a series which covers all the different database products.

For instance, nested transactions in SQL Server strikes me as asking for trouble. Which is why Kalen Delaney rails against the loss of the Sysprocesses.open_tran column in the SQL Server 2005 metadata.

"Sysprocesses contains a columns called open_tran which reflects the transaction nesting of each session. If a session issues four BEGIN TRAN commands, with no COMMITs or ROLLBACKs, their session will have an open_tran value in sysprocesses of 4. Any open_tran value greater than 0 might mean that a transaction is holding locks and blocking other processes, or it might be keeping the transaction log from being cleared. If you ever notice open_tran values in higher than 2 or 3, it's a pretty good indication that a developer doesn't know much about SQL Server transaction management."

Back to Pythian where Sheeri Cabra reviews MONyog, a GUI monitoring tool for MySQL. Overall she is favourably impressed: "MONyog is the best out-of-the-box GUI monitoring tool for MySQL that I have seen.” Although she does have reservations about its logging. Personally I think the name is a mistake: it sounds too much like something out of H P Lovecraft.

Some Oracle stuff now. Chen Shapira, the not-so-simple DBA puts her Statistics degree to good use by building a custom aggregation function that will return a random salary using Oracle's Data Cartridge extensibility features.

"The main challenge was to make the aggregation truly random....Suppose I have three rows. The way aggregation works, I first take two rows and flip a coin to pick one. Now I have a current value - and I have to take the third row and decide if I want to keep the current value or the new one. I can’t flip the coin again - because if the third row has 50% chance to be selected, this means the first and second rows only have 25% chance each. Not fair. So I need to give the third row 1/3 chance, and the current value 2/3."

On Oracle Base, Tim Hall demontrates the long-overdue support for case-sensitive passwords which Oracle have introduced in 11g.

Oracle has acquired the IKAN tool CWD4ALL and they're going to use it to give SQL Developer a decent modelling support capability. I would have though there would be more excitement about this in the blogosphere (certainly the ODTUG Designer listerserver has been cock-a-hoop) but only Dietmar Aust seems to have picked it up. Perhaps hardly anybody cares about modelling, in which case TOAD's marketshare is safe.

Last week I was judging abstracts for the UKOUG 2008 Conference, and there were three submissions for sessions on best practices in programming with ApEx. Alex Gorbachev (Pythian, again) shows why these talks are necessary with an example of poor SQL taken from the official Oracle documentation.

Lot's of people are asking questions. SQLDenis asks rhetorically Sybase IQ Is A Columnar Database, Why Should I Care?

"What does this mean? This mean that the data is stored in columns and not in rows. Inserts are slower that a traditional row based database but selects are many times faster (up to 50 times). The good thing about this technology is that the SQL looks the same, the only difference is that the data is stored in a different way."

Robert Hodges at The Scale-Out Blog wants to know, what's your favorite database replication feature? Call me shallow, but it's not a topic to which I'm given much thought. I can tell you my five all-time top favourite cover versions instead.

Meanwhile Jon Emmons poses the question Ever wonder what your DBAs really do?. It turns out there's more to the job than drinking coffee, swearing at developers and losing the backup tapes. Who knew?

Of course, DBAs have plenty of reasons to swear at developers. In Extreme Makeover - Database Edition CrazyDBA shows us his scars from a SQL Server version upgrade:

"Saturday morning, migrating from "old prod" to "new prod". We finish up during the afternoon. On Sunday evening (yes, more than 24 hours later), we are notified that the system is not performing properly. We double check things on our end and everything seems to be working, well, except that the duration for some queries have gone from three seconds on "old prod" to twelve minutes on "new prod". Ouch. Our team investigates a bit further and escalates the issue to the (sleeping) onsite team, who pick up their research on Monday morning.

What do we do first on Monday morning? Well, we go to the new test system and run the query. It takes eight minutes. Turns out development is slow as well. Surely someone noticed this during testing, right?"

Er, wrong.

Rick Heiges asks It's Q3 - where is SQL Server 2008? To make him happy (and Mr CrazyDBA even happier), according to Jason Massie there's a rumour that SQL Server 2008 is due to ship next week (or this week if you're reading after the weekend).

From the new releases to some ancient history. Willie Favero comments on an article about DB2's 25th birthday from Information Week. It's interesting to see what counted as a new feature in those days: "You could dynamically add tables or change tables without taking the system down. It doesn't take much imagination now to see this was a huge leap forward," recalled Don Haderle, chief architect of DB2.

Back to the future. Over at the IT Toolbox Lewis Cunningham has his head in the clouds. Or rather Cloud. This is a neat summation of all the main players in Cloud databases. Cloud computing is a rather attractive idea, but I think there is some way to go before it is a practical solution for business. Web access is still far from pervasive or guaranteed, and as the Register pointed out this week, there are still some kinks in the business model to iron out. Lewis describes Amazon as the 800lb gorilla in the cloud space (stratosphere?) and the Register also has a good piece explaining Amazon's interest in the technology.

In his Data Migration blog Johny Morris (no, not that one) invites us to consider the benefits of meetings, in this case Data Quality Rules meetings: "Use them not just instrumentally to solve the issues in front of you but also to build the team that jointly will have uncover all the knowledge hidden in the organisation." Exactly the sort of benefit which will be hard to realise when we are all working in the Cloud and never visit the office.

Finally, nothing to do with databases but I'm sure relevant to us all (at least those who are still office bound), Suzanne Thornberry at Tech Republic writes about the health risks IT professionals run. These include such things as eye strain, bobblehead syndrome and seated immobility thromboembolism (SIT), which is like DVT only worse. So stop reading this and go do something more healthy instead!

Postscript

The Log Buffer is a community activity facilitated by Pythian. Find out more.

Better late than never

There is a new white paper on OTN called Upgrading from Oracle Database 9i to 10g: What to expect from the Optimizer. I am working on a 9i project which is considering upgrading to 10g (quite possibly the last such in the world) so I think we'll find it useful. Of course, it might also be helpful to people who have already done the upgrade and are experiencing performance problems in their new environment.

UKOUG 2007: Ruby Tuesday

Tuesday was a long day for me. I was awake at 5:00am. On Monday I was awake at 5:30am. If it wasn't for the fact that I'm still in the same timezone I would swear I was jet-lagged. It isn't "jet-lag" either - I mainly drank water at the bloggers' meet-up last night. Anyway, it gave me the chance to run through my presentation before breakfast.

The first session of Tuesday was Sue Harper's overview of SQL Developer's new features. These transpired to be the new features in the coming 1.5 release rather than the latest production release. The upcoming release is special because its features are ones suggested and voted on by SQL Developer users on the SQL Developer Exchange site. The team deferred the planned 2.0 release because its manifest contained no features which users were clamouring for, at least not according to the SQL Dev Exchange rankings. So if there's some functionality you want to see added to the product, suggest it. If enough other users agree with you there is probably a reasonable chance you might get it. Power to the people!

Most of these new features are enhancements to existing functionality - a new SQL formatter, better code insight. This is because the SQL Dev team is quite small and has only one real developer. The team piggy-backs on the work of the JDeveloper team, so to a large extent the feature-set in SQL Dev depends on what is in the other tool. That is why the next release will feature the ability to see queue tables but not to create or alter them. In the long run we can hope that the JDev modelling features will be included in SQL Dev.

Database vs middle tier

My chairing of Toon Koppelaar's session got off on the wrong foot. Of course I should have checked how to pronounce his name before I introduced him - Dutch names tend to sound like a cough - but as it turned out it was the name I thought I knew which I had in fact got wrong. Toon (pronounced tone) was generous. Apparaently he gets call "Tune" quite a lot.

Toon's talk was another waltz around the question of where business logic properly belongs. Toon distinguishes three times of logic:

• data logic - directly manipulating the data;
• UI logic - directly affecting the user's interaction with the system;
• business logic - everything else.

Toon rehearsed the familiar arguments. We have moved almost full-circle from text-based stateless apps in the seventies and early eighties through client server apps to the current fad for graphical stateless apps. The main difference between the green screen dumb terminals of yore and today's browser-based apps is responsiveness: the new apps do try to respond to the user in the client layer. This comes at a price: the presentation layers have a large number of moving parts, drawn from an exploding range of widget toolkits and frameworks. Keeping up with all these different bits requires a significant knowledge investment, which in turn leads to teams of specialists overseen by architects (you have to hear Toon say "architect" to realise that he means it as an insult).

But the real problems arise when the business logic is written in Java or .Net or one of these other fancy-dan languages. These applications tend to be very chatty - Toon quoted an example where a single search spawned 7000 separate calls. They also tend to date very quickly, which makes them hard to maintain and hard to migrate. Most applications are "Window on Data" - the user retrieves data and updates data. This is precisely what databases are meant to do. Consequently the underlying database design is paramount. The DBMS and PL/SQL have been the single stable constant in system architectures for more than fifteen years now. So put your business logic in the database (PL/SQL and views) and just change the front-end layer to keep up with the Next Big Thing.

The interesting thing is that these talks - due to the nature of the conference - are usually singing to the converted. However, I noticed an ex-colleague, who is a Java head, in the audience. I chatted to him afterwards. He was spitting feathers. Strawman arguments... business logic is object-oriented....PL/SQL is too hard to maintain. In the end I had to calm him down by proposing that he present his opinions at a SIG. He said he'd think about it. I have heard this a number of times now. Both sides try to bludgeon the other side into admitting defeat. The trouble is finding people who are equally experienced in both database and middle-tier architectures. Almost everybody is a specialist on one side of the fence or the other. There are good reasons why this tends to be the case but it does make it difficult to hold a discussion in which everybody understands the issues involved.

So I think it comes down to numbers. Front-end developers generally outnumber database engineers in any organisation, so most of the application will be written in the programming paradigm which suits them. This will lead to the business logic in the middle tier, because that's what they understand. Whether this is the correct solution is a different matter.

11g for developers

Well at least I managed to pronounce Tom Kyte's name correctly. In this session Tom covered the key new features for developers. As with the other session, I'll list the features he mentioned, and just expand on a couple of points which were news to me:

• Client result cache
• server result cache
• PL/SQL result cache
• virtual columns
• the long awaited PIVOT clause
• finer grained dependency analysis
• Advanced compression
• PL/Scope
• Method 4 dynamic SQL
• compound triggers
• change to WHEN OTHERS exceptions (see below).

The more I hear about the result caching features the more I think my project ought to move to 11g. Currently we are on 9i with only the vaguest plans to migrate to 10g. For the last few months I have been, in effect, implementing something like result caching, only not as efficient. A crusade for the new year. Something I had not picked up on before is that the PL/SQL result cache only applies to functions, not procedures, and also doesn't work with functions which have OUT or IN OUT parameters.

Bryn Llewellyn, the PL/SQL product manager, offered Tom the choice of three new features. Tom asked to have the WHEN OTHERS exception banned; regular readers of AskTom will know that the use of this exception without an accompanying RAISE or RAISE_APPLICATION_ERROR is one of his bugbears. In the end they compromised: WHEN OTHERS without a RAISE or RAISE_APPLICATION_ERROR will throw a compilation warning. But only if the compilation is set to the correct level. I rather suspect the sort of developers who code such silent bugs in their exception handlers probably don't use the compiler flags.

The duplicity of duplicate rows

Hugh Darwen started the third talk in his series of SQL's biggest mistakes with a discussion of the Bellman's line from the Hunting of the Snark, "What I tell you three times is true." He followed this up with Ted Codd's observtion that "If something is true saying it twice doesn't make it any more true."

Duplicate rows in a resultset are duplicitous because they can represent two different problems. One is that the table contains duplicated data. The other is that the SQL has generated duplicate rows, for instance through an imperfect join. Either way it is an offense against relational theory, which requires that a relation consist of unique records. The possibility of valid SQL generating duplicate rows is a fundamental flaw in relational theory, which wasn't anticipated by Ted Codd. There are workarounds. We can introduce artificial uniqueness through sequences or or on the fly row number generation. We can suppress duplication through the use of DISTINCT or UNION, or we can ensure we include all the key columns in our select statements and include differentiating tags in UNION ALL statements.

No solution is entirely satisfactory. Synthetic keys do not address the underlying problem of duplicate data in the table. That really needs to be tackled by a decently normalised data model and building unique constraints on the natural keys. Removing duplication from the resultset has performance implications. We wouldn't want to pay the overhead for applying DISTINCT to every query but how else can we guarantee uniqueness in the resultset. Hugh suggested this problem was down to the database vendors but I think that is a bit cheeky. DISTINCT inevitably requires additional work on the part of the database and that has to cost something. It can be optimized (with the kind of hashing algorithm Oracle introduced for GROUP BY in 10gR2) but as Scotty was wont to observe, "Ye cannae break the law of physics".

Incidentally, Hugh owned up to introducing the NATURAL JOIN syntax into the ANSI standard. He acknowledged that it was a controversial addition. Indeed. Some people I know regard it as a mistake; perhaps not on a par with NULLs but a mistake none the less. Personally I think it's a heffalump trap. It may be a boon for the experienced SQL practitioner but it's just a bug waiting to happen for the unwary. And if my experience on the OTN forum is at all representative the clueless outnumber the clueful, and they appear to breed faster. It's not the way we'd like it to be but it's the way it is. Of course this is just the sort of pragmatic approach which often enrages the theoreticians (or at least Fabian Pascal).

Hugh rounded off his presentation with the observation that fundamental mistakes are often irrevocable. Furthermore they don't often reveal themselves until it's too late. So maybe it's time for a Truly Relational Database. This call for for arms was delivered with a knowing smile - most of us in the audience are Oracle wranglers first and foremost. So he asked the question of himself, why does he keep doing this? Because somebody has to, otherwise the importance of theory will disappear off the practitioners' radar altogether.

Understanding statspack

Jonathan Lewis also started his presentation with a quote:

"------ is the art of looking for trouble, finding it everywhere, diagnosing it wrongly and applying unsuitable remedies."

This turns out to be Sir Ernest Benn on politics, but as Jonathan rightly says it acts as a neat summation of the state of Oracle tuning.

All database performance problems mean one of two things:

• the database is working too hard; or
• it is waiting for someting.

Waits are due either to the inherent slowness of a particular step or because there is a queue for a particular resource. There are only issues:

1. disk I/O;
2. CPU consumption;
3. network delays;
4. application contention (locks);
5. internal contention (latches)

It's almost always the SQL at fault:

SQL ordered by gets		CPU
SQL ordered by reads		Disk (CPU)
SQL ordered by executions		CPU (network)
SQL ordered by parse calls		CPU (internal contention)
SQL ordered by version counts		CPU, internal contention
SQL ordered by CPU time		CPU
SQL ordered by elapsed time		application contention

The problem with Statspack is that it just gives us a list of symptoms. It can be hard to distinguish the flow of the events which underlie the bald statistics and even harder to determine possible causes. What sort of database activity might generate lots of logfile sync waits? So Jonathan advises us to play with Statspack. Run a query many times; check the Statspack output. Change the statement and repeat the operation. Stress Oracle and see what happens.

The other important thing is to keep an archive of Statspack results. Quite often performance problems arise because something has changed. The current output may flag up a problem but it might not be enough to diagnose it. Having a history of Statspack output will allow us to spot trends, repeating patterns or sudden changes in behaviour.

.	.	occurrence
.	.	rare	frequent
impact	huge	easy to see	easy to see
.	tiny	irrelevant	statistically visible

This tied in nicely with Robyn Sands's observation that performance tuning is about reducing variability.

Tuesday evening

In the evening we had the SIG Focus Pubs. This is supposed to be an opportunity for delegates to meet others with similar interests. I'm afraid I took it as an opportunity to find presenters for my next SIG (27th February 2008 - save the date!) I did chat with somebody who is still happily using client/server Forms 6i. Well, it's table, quite richly featured, highly productive and it works - at least for the time being. This person expressed the hope that Forms 6i would run on Vista. Somehow I doubt it (and so did a former Forms guru of my acquaintance). Apart from anything else, archaic software like Forms is exactly the sort of thing which will display very slowly on Vista's new rendering architecture.

Afterwards a few of us went for a mealy at a rather upmarket curry house called Lazeez (hence "ruby Tuesday"). The food was very nice but there were no side dishes. An innovation too far. Anyway, my thanks to Joraph, who picked up the tab.

UKOUG 2007: Monday, Monday

The first session I chaired was Martin Widlake talking on "Avoiding Avoidable Disasters and Surviving Survivable Ones" He took as his sermon the text What could possibly go wrong? Well, for starters, there could be a problem with the USB drive so that the wireless slide controller doesn't work. Martin disappeared to try and find a replacement, which left me with the possibility of having to vamp for an hour if he didn't come back. Fortunately he did return, albeit without a replacement gadget, so his presentation was more static than useful usual [see Comments - Ed]. Fortunately the ideas were lively. I was particularly taken with the Formica Table. This would be a forum where "not bad" DBAs answered questions which fitted 95% of all scenarios; sort of an Oak Table For The Rest Of Us.

His main theme was that projects tend to fail because decision-makers aren't realistic in their risk assessments. So projects are planned on the basis of everything going right. Staff are told to work extra hours and weekends without any recognition that tired people make mistakes, and fixing mistakes costs time. Or additional people are recruited which just increases the number of communication channels, to the point that the overhead of keeping everybody in the loop becomes excessive.

Martin advises us to install disaster tolerant hardware, because it gives us more interesting problems to solve. Of course we shouldn't really switch to clustering technology just for the sake of it. But if we think we are eventually going to need RAC we should move to it now. That way we'll have learned to live with it and smoothed all the wrinkles before the technology has become critical to the system.

There were some entertaining war stories. One concerned a failed powerpack in a cluster. A sysadmin noticed and helpfully substituted the powerpack from another machine. When he connected the first node without a hitch but the second node promptly fried that power pack too. So he called an electrician. In order to get at the problem the electrician had to climb a ladder. The ladder slipped and the flailing electrician grabbed at the nearest thing to hand, the power rail, which collapsed and took out the leccy for the entire server room. We can't plan for such things, we can merely acknowledge that such stuff will happen.

The solutions are the obvious ones: realistic planning, smaller units of delivery, delivering something on a regular basis. One neat idea for solving the communication problem came from somebody who works for Unilever. They use Jabber to post small messages to a central message board, so everybody can see what everybody else is doing in real time. At last a business use for Twitter.

An Industrial Engineer's Approach to DBMS

Problems with the AV set-up seem to have become a theme in the sessions I've chaired. Cisco's Robyn Sands turned up with a Mac but without the requisite dongle which would allow her to plug it into the hall's projector. So she ended up having to drive her presentation from a PDF on a loaned Windows machine. She handled the transition to an unfamiliar OS but it was an unlucky start to her session.

Industrial engineering concerns itself with "design, improvement and installation of integrated systems of people, material, equipment and energy", which is a pretty good definition of the role of a DBA too. Industrial engineers focus on efficiency, value and methodology; they are the accountants of the engineering world. The application of IE methods to DBMS has the aim of producing a consistent application. For instance, every database in an organisation should be consistent: same installed components, same init parameters, same file locations, with transportable tablespaces and reusable code/scripts/methods. This results in safer and more flexible systems. The installation process is a flowchart; in effect the database instance is deployed as an appliance.

Another IE practice is value analysis. This says that a cost reduction adds value to a system just as much as adding a new feature. Which brings us to Statistical Process Control . Every process displays variability: there is controlled variability and uncontrolled variability. We need to use logging to track the elapsed time of our processes, and measure the degree of variability. Benchmarking is crucial because we need to define the normal system before we can spot abnormality. Abnormal variability falls into three categories:

• special case;
• trend;
• excess variation.

Once we have explained the special cases we can file and forget them. Trends and excess variation both have to be investigated and fixed. The aim is achieving a consistent level of service rather than extreme performance. If you can accurately predict the Mean Response Time then you understand your system well.

Robyn described a project she had worked on which focused on systems reliability. The goal was to reduce or eliminate recurring issues with a view to reducing outages - and henceout-of-hours calls - to increase the amount of uninterrupted sleep for DBAs and developers. A worthy end. The problem is simply that a DBA or developer woken at three in the morning will apply the quickest possible fix to resolve the outage but there was no budget to fix the underlying problem when they got back into the office. Usually old code is the culprit. There's lots of kruft and multiple dependencies, which make the programs brittle. The project worked to identify the underlying causes of outages and fix them. The metric they used to monitor the project's success was the number of out-of-hours calls: over the course of the year these fell by orders of magnitude.

Robyn finished her presentation with some maxims:

• Rules of thumbs are not heuristics.
• Discipline and consistency lead to agility.
• Reduce variation to improve performance.
• No general model applies to all systems.
• Understand what the business wants.
• Model and benchmark your system accurately.
• Understand the capabilities of you system.

The licensing round table

This event was billed as "Oracle's Right To Reply". Unfortunately there wasn't an Oracle representative present and even when one was rustled up they could only take away our observations to pass them on. This confirmed an observation from Rocela's Jason Pepper that Oracle employees are expressly forbidden from discussing licencing unless they are an account manager. This can lead to situations where advice from Support or Consulting leads to customers having exposure to increased licences.

The issues aired were the usual suspects. Why isn't partitioning part of the Enterprise Edition licence? Why aren't the management packs available for Standard Edition? Why isn't there a single, easily locatable document explaining pricing policy? How can we have sensible negotiations when the account managers keep changing? There was one area which was new to me. There is a recent initiative, the Customer Optimization Team, whose task is to rationalise a customer's licences. Somebody asked the pertinent question: what is the team's motivation - to get the best value for customer or to sell additional licences for all the things which the customer is using without adequate licences? Perhaps we'll get answers. I shall watch my inbox with bated breath.

Index compression

This was a bonus session I hadn't been meaning to attend but it was worthwhile. Philip Marshall from Joraph presented his research into the effects of compression, because these are not well documented in the manual. Compression works by storing the distinct values of the compressed columns and then linking to each instance of that value, which obviously imposes a small overhead per row. So the space saved on storing the compressed column is dependent on both the length of the column and the number of instances of those values. The overhead means that compressing an index with small columns which have high variability could result in very small savings or even a larger index.

Also we need to remember that the apparent space saving could be due to the act of rebuilding the index rather than compressing it. This matters because (as we all should know) the space savings from rebuilding can be quickly lost once the index is subjected to DML. Furthermore there is a cost associated with uncompressing an index when we query its table. This is can be quite expensive. The good news is that the CPU cost of uncompressing the columns is incurred by the index read only: so it is usually only a small slice of the whole query. Still it's a cost we should avoid paying if we aren't actually getting a compensating saving on space. Also compression does not result in more index blocks being cached. More blocks will be read in a single sweep, but the unused blocks will be quickly discarded.

I thought this presentation was a classic example of the Formica Table approach. A focused - dare I say compressed? - look at a feature which probably most of us have contemplated using at some time without really understanding the implications. It was the kind of presentation which might just as easily have been a white paper (I will certainly be downloading the presentation to get the two matrices Philip included) but there is already so much to read on the net that a paper would have just got lost.

11g for DBAs

This was the first of a tie-less Tom Kyte's two selections from the 11g chocolate box. I think the 11g features have been sufficiently rehearsed over the last few months that I have decided to skip the details. So here is just a list of the new features Tom thinks DBAs most need to pay the attention to.

• Encrypted tablespaces
• Active Dataguard
• Real Application Testing
• Enhancements to Data Pump (EXP and IMP are now deprecated)
• Virtual columns
• Enhancements to partitioning
• Finer grained dependency tracking
• the xml version of the alert log
• invisible indexes

Of that list encrypted tablespaces, Active Dataguard, Real Application Testing and partitioning are (or require) chargeable extras. In the earlier round table Ronan kept reminding us that we must distinguish between licensing and pricing: we have to accept that Oracle has 47% of the database market so lots of CTOs and CFOs must think it offers value for money. Which is a fair point, but it is hard to see a compelling reason why a Standard Edition DBA would choose to upgrade. Actually the enhancements for developers are very attractive, but alas we don't carry as much sway.

One notable thing in Tom's presentation occurred when he was demonstrating the new INTERVAL operation for partitioning. The new partitions had a year of 2020, but the dates were supposed to be from this year. It turns out Tom had been tinkering with his demo code and had removed an explicit date conversation without checking the default date format. It's nice to know even the demi-gods fall prone to such things ;)

OOW2K7: The Real World Performance Group

The Real World Performance Group is not Peter Gabriel jamming in a muddy field with a bunch of Senegalese drummers. It is a small unit on Oracle's development side, dedicated to understanding database server performance and investigating customers' problems. I included Andrew Holdworth's session on Database performance trends in my schedule having never heard of the group. After that presentation I went to the Group's Round Table session and Andrew's other session the following day, neither of which I had scheduled. He was that good.

The whole series of sessions switched on a number of light bulbs, joining up some of the dots from earlier presentations I had attended. The resulting illumination made me realise I had spent the a large chunk of the last six months barking up the wrong tree. Basically, the sort of session which justifies the whole idea of going to conferences. That's why I've forgone my usual pithy reports in favour of a full-blown dump of my notes from the three sessions. Where the same topic came up more than once I have aggegated all the material in the section which seems most appropriate.

Database performance trends

Andrew Holdsworth opened his session with the observation that most sub-optimal applications are running well enough, because of improvements in CPU speeds and the availability of cheap RAM, which means we can have bigger DB caches. However, in larger databases disk I/O becomes more significant: an SQL statement which requires disk reads for one percent of the data will run one hundred times slower than a query which can be wholly satisfied from memory. Interconnects to remote RAC clusters perform much better than disk I/O.

Most database applications out there are very small. Frequently there are multiple databases on a single server (the most he has seen was forty-nine databases on one host). This is not a problem because the applications are generally used quite lightly, with barely one SQL statement running on the box at any given time. The main challenge is to prevent a single rogue statement stealing all the resources from the other applications. However, there are also a relative handful of really big systems which are rigorously designed and with a single database deployed per host. These applications are owned by significant Oracle customers and have high visibility within the company. Consequently such databases drive the direction of Oracle's development program. These are the sort of databases which demand tuning, because their performance issues cannot be resolved simply by faster CPUs.

The basic practices for good performances are:

1. Spend time on data modelling. It is easier to tune against good schemas; they are simpler and less error-prone.
2. Pay attention to cursor and session management, Use the middle tier to protect the database.
3. Do proper capacity planning. If possible, use intelligent extrapolation from existing systems. Either way, test and validate.
4. Use database features correctly and appropriately. Always start with the default values and change them only with good reason. (This point was expanded in another session.
5. Design for predictable perform. Strive for SQL plan stability, but be prepared for dealing with increasing data volumes, which may demand new execution plans.

Andrew devoted a lot of time discussing benchmarks, as the group spend a lot of time running them. Of TPC benchmarks he asked, "Who likes TPC benchmarks? Who believes them?" A vast amount of work goes into them: the time spent optimizing a server for a TPC benchmark is an order of magnitude greater than a customer would spend on tuning their database. Price performance figures are suspect. There is no redundancy (because in the context of a benchmark that inflates the cost without offering any performance advantage), so the configurations are unreliable even dangerous.

They also see a lot of client benchmarks. About nine in ten are dysfunctional: ill-conceived, incomplete and unrealistic. They are time-consuming and expensive to run, and stressful for all concerned: if you inflict a poor benchmark on the RWPG team be sure that they will respond by picking on your cluelessness. Common failings are:

• an invalid mix of transaction types;
• no "think time" for the emulated users;
• poor data sets;
• in particular, the use of random numbers to seed the data sets so the tests are not repeatable;
• not allowing for the resources used by the application server;
• data warehouse tests which fire off 2000 concurrent parallel queries on a twenty CPU box;
• data warehouse tests which don't distinguish between small, medium or large queries.

His advice is to run it in-house before sharing it with Oracle Support.

His final point was about priorities. The four layers of a database system are, in increasing order of expense: server, interconnects, I/O fabric, storage. Often the cheapest element - the server - is chosen first, and insufficient attention is paid to balancing all the hardware. Storage and network technology has not kept up with CPU speeds: by comparison with them server costs are a rounding error.

The round table

Members of the audience wrote questions on index cards which were passed to the panel. The panel filtered questions according to some strict rules of engagement. The topics had to be of relevance to the largest possible chunk of the audience. On-stage debugging was expressly forbidden. There was some overlap with the preceding and subsequent presentations, so here are a few points which weren't covered in depth elsewhere.

How much SGA is too much? There is no upper bound. Just don't exceed the physical RAM. With DSS we want sorts and joins to write as little as possible to the temporary tablespace so set the PGA to a high value. Aim to have 4-8GB of SGA per core.

The 11g resultset cache uses CPU and RAM. This offers a more favourable price performance than disks, which are just getting bigger without getting better.

The AUTO_SAMPLE_SIZE option with DBMS_STATS is an iterative process. So using it is likely to produce a more accurate sample size than us just picking a percentage value, as we tend not to validate our choice.

There is a problem with bind variable peeking, if the first instances of a query happens to use unrepresentative values. We can solve this through seeding the library cache at start-up by running our key queries with representative bind values.

The answer is always in the data.

Making appropriate use of database technology

The team's original idea for this session had been to cover every database feature. Realising that would have required a four day course (at least) in the end Andrew Holdsworth decided to focus on three key areas:
Parallel Query - lots of people pay for the licences but don't use it properly.
The Optimizer - the part of the database customers find most frustrating.
Session and Connection management - a new but increasing problem.

The code for parallel query has improved considerably over the years but customers are not using it because CPUs have got so fast. The trend is to substitute processor brawn for good design. This is not a sensible strategy with DSS databases, because the chance of resource contention is much greater if the queries take too long to run.

Making Parallel Query work well places a premium on capacity planning. There has to be sufficient bandwidth to support the required throughput down the entire hardware stack. If the server has only got two 100MB HBAs PQ is impossible. Don't just focus on the server; CPUs are the cheap bit ... apart from the licences (AH's joke).

A thumb nail guide to capacity planning. To support a throughput 100MB per second per core (based on the de facto standard unit of a server with four quad-core processors):

4 CPUs x 4 cores x 100MB =	1.6GB of I/O
4 CPUs x 4 cores x 100MB =	1.6GB of interconnect
# of disks =	1.6GB/s / 20MB/s per disk = 80 disks

Note: this doesn't include redundancy for high availability (data warehouses are not allowed to be down anymore).

"Death by indexes". Indexes are great for OLTP but not for DSS which generally rely on aggregation which in turn requires large amounts of data. Indexed reads which accesses only 0.1% of the data performs worse than a full table scan. Lots of indexes will lead to low CPU utilisation. A better approach is to use PQ to haul in large amounts of data quickly and use the powerful CPUs to winnow it: 5ms is a lot of computation time. Partitioning the object to take advantage of query pruning will increase the percentage of relevant rows retrieved, which will further reduce the need for indexes. Use hash partitions for fast joining.

Designing for PQ:

• Take the database's default for the Degree of Parallelism.
  
• Use the resource manage to define the user's DoP.
  
• Make the number of hash partitions a power of two.
  
• Also, make it a multiple of the DoP.
  
• Allow for the concomitant impact on loading, data dictionary management, stats gathering.

The Optimizer is the most maligned, most misunderstood and most misused part of the database. This is in no small part due to Oracle's failure to explain and document it properly. Consequently people tune by Google or witchcraft, which usually means setting underscore parameters. This is a bad idea. Setting init.ora parameters has a global impact: fix one query and break three others. We're setting the values blindly - often underscore parameters have magic values. Most bad execution plans are due to bad stats. The quality of the stats is under our control. We know our data. Whereas the execution of the optimizer is under Oracle's control and it is rocket science. Tweaking the settings of underscore parameters is fiddling with the bit we don't understand instead of fixing the bit we do.

Optimizer basics

• Run with the default parameter values.
  
• Don't make global changes.
  
• Hints do not guarantee stability over time.
  
• Get the statistics gathering right.
  
• Accept change will happen.
  
• TEST!
  
• When upgrading, accept that your database's previous good performance may just have been luck rather than a testament to your tuning expertise ;)

Gathering stats: start with default values. AUTO everything. Remember, the aim is to get good cardinality estimates.

Table stats are easy. Don't sweat them. An increase in 10% of the rows is unlikely to mean your stats really are stale: think order of magnitude changes.

Columns stats are the key to good performance - size and number of columns, histograms, indexes. Use your knowledge of the data model to figure out whether you have uniform or skewed distributions. Columns with uniform distributions don't need histograms; watch out for bind variable peeking with skewed data. Minimum and maximum values are the ones most susceptible to change (especially for columns populated by sequences) so maybe refreshing the high and low values after each data load is sufficient.

The key to query tuning is to check the correctness of the cardinality estimates. Use DBMS_XPLAN.DISPLAY_CURSOR to see the estimated cardinality alongside the actual cardinality. No need for a 10053 trace (which doesn't give us the real cardinality values anyway).

Connections stress servers, especially SMP, causing latch contention and using lots of CPU. There is a tendency for Java and .Net programmers to be very bad at managing sessions (and cursors). This is because their memory model means they tend to take stuff and not give it back. Connection storms occur when a connection pool floods the server. Connection storms are caused by a problem in the database server which prevents it from satisfying the app server's demands for new connections The app server responds by spawning further sessions. This can escalate quickly to tens of thousands of sessions, which will kill the database server.

Best practice for connection management:

• 5 - 10 connections per core (I/O dependent);
  
• minimise logons and logoffs;
  
• avoid parsing;
  
• set parameter for minimum and maximum connections per server to the same value.

Final thoughts

I was a bit concerned that I found so much of value in these sessions. Perhaps I had been living in a cave for too long? However, I met up with Doug Burns afterwards and he said that he was going to have rewrite his presentations and whitepapers. As he is Mr Parallel Query I found this quite reassuring.

Andrew Holdsworth observed, when talking about the recommendations for parallel query, we can try this stuff for ourselves but they do it for a living.