I’ve been telling you for years to transform your short all-AMP queries into single-AMP queries, whenever you can. I’ve even given you pointers on using stored procedures, join indexes and smart application design to achieve that goal.
But when it comes to random AMP sampling, I’m asking you to ignore all that, and give some thought to converting your random AMP sampling from one to all-AMPs.
In this article I’ll give you my perspective on the advantages of spreading this behind-the-scenes statistic-gathering effort across all AMPs in the configuration, and I'll suggest when it can help you. I’m not recommending that everyone, or even most of you, make this change. But don’t brush it off until you’ve given it a fair listen. First, here’s a brief refresher on what random AMP samples are.
The optimizer builds an execution plan for each SQL statement that enters the parsing engine. The act of collecting statistics on the underlying tables and columns ensures that reliable and current demographic information about the objects referenced in the SQL is available to the optimizer so that those plans can be as well-performing as possible.
If no statistics have been collected, the optimizer will make a rough estimate of a table’s demographics by using dynamic samples from one or more AMPs (one being the default). These samples are collected automatically each time the table header is accessed from disk and are embedded in the table header when it is placed in the dictionary cache.
The AMP that is sampled is determined by the table ID of the table for which demographics are being sought. The assumption behind random AMP sampling is that pulling a small sample from one AMP is fast enough to be unnoticeable, and will provide enough information to generally characterize the table.
Random AMP sampling produces simple, limited demographic information. Most importantly, it comes up with an estimate of the total rows in the table. Secondly, it samples rows from each non-unique secondary index (NUSI) subtable and extrapolates total distinct values (and from that, an estimated number of rows per value) for each NUSI that has been defined on the table. It ignores non-indexed columns completely and does not try to draw sophisticated conclusions. In addition, random AMP sampling doesn’t build interval histograms, as full statistics collection does.
Estimated values derived from random AMP samples are used not only for table row counts, but for other purposes as well, including join cardinality and aggregation estimates.
Random AMP sampling is the fastest of all the statistics collection options. One cylinder index on the targeted AMP is examined, as opposed to reading base table rows directly. The cylinder index carries a row count for each of the table’s data blocks, as well as the number of data blocks in the cylinder. These counts, along with a count of the number of cylinders that the table spans, are used to extrapolate the number of rows for that table on that AMP. The estimate of row counts for the entire table is determined to be equal to the number of rows from the sampled AMP multiplied by the number of AMPs in the configuration.
Statistics derived from random AMP sampling are never kept permanently in data dictionary tables. Rather they are held within the table header in the dictionary cache until pushed out by something more current that needs cache space, or until the normal 4-hour purge process, or until the dictionary cache is spoiled for that table. As a result, as changes are made to the underlying data, the sampling will always be reasonably fresh.
A recent enhancement to random AMP sampling allows more than one AMP to be sampled. By changing an internal field in the DBSControl record called Ran-domAMPSampling, it can be requested that sampling be performed on 2 AMPs, 5 AMPs, all AMPs on a node, or all AMPs in the system.
When using these options, random sampling uses the same techniques as single-AMP random AMP sampling, but more AMPs participate. I don’t advise you to consider 2- or 5-AMP random AMP sampling, because in most cases the added overhead will not provide enough value. All-AMP sampling is the preferred approach if you desire a broader scope, particularly when the primary index has an uneven distribution of values.
All-AMP random AMP sampling has been improved in Teradata 12 in a way that significantly reduces message overhead. Rather than relying on multiple messages and all-AMP aggregations, all-AMP sampling uses the “Last Done” channel mechanism. A channel is a software construct associated with the BYNET that simplifies communication among all the resources working on the same job. Channels replace messaging with signaling, and allow the database to accumulate simple information with lower latencies and minimal network traffic.
Using this technique, the parsing engine (PE) makes a single broadcast to all AMPs. Each AMP polls its results on the channel, after performing the basic “single-AMP sampling” routine that is described above. The last AMP to post a response sends a message with the results back to the PE.
There are two key advantages of all-AMP random samples:
All-AMP random AMP sampling is on by default for the following constructs, in order to get more accurate dynamic statistical information in the absence of full statistics collection.
In a recent engineering investigation that contrasted the time it takes for single-AMP random AMP sampling against the time it takes for the all-AMP activity, all-AMP random AMP samples were found to add only 15% more time onto the process. Admittedly, the testing was performed on a relatively small configuration. While it is not expected that the time for all-AMP sampling will increase significantly on your system beyond this 15%, a configuration with a very large number of AMPs could experience increased overhead. Keep in mind that even though more work will be required when larger numbers of AMPs are present, the sampling will be performed in parallel across all AMPs.
Although it comes with slightly more effort, the results of all-AMP sampling will remain in the dictionary cache for up to 4 hours, just as is true for single-AMP sampling. Random AMP sampling of any kind is not expected to happen frequently, and its results are cached and shared among many plan-producing queries that arrive on the same PE.
We are investigating moving from Single AMP Sampling to AllAMPSampling and I am trying to work out a method to allow us to do this easily!
Are you aware of the views provided by Dieter?
If so, could we look at the variation between what is returned in the OneAMPSampleEst and the AllAMPSampleEst compared with the NumRows to see how often there is large descrepency, to make that determination?
If that might be the case, could we also use these Est(imate) columns to determine if we even need the stats collected we have if all these values are within a small percentage of each other. Two examples shown below:
NumRows OneAMPEst ALLAMPEst CollectDuration
1,608,977,669 1,618,420,224 1,610,245,524 00:55:56.31
578,902,658 1,317,215,544 1,287,298,808 00:01:40.32
So in the first case, we probably don't need to collect stats on this column at all, as both OneAMP and AllAMP estimates match the NumRows. In the second case, it would seem that if we used ALLAMPSampling, then we would be providing better statistics to the optimiser!
Are my assumptions correct, or do these values mean something else entirely?