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:
- Spend time on data modelling. It is easier to tune against good schemas; they are simpler and less error-prone.
- Pay attention to cursor and session management, Use the middle tier to protect the database.
- Do proper capacity planning. If possible, use intelligent extrapolation from existing systems. Either way, test and validate.
- 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.
- 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.
