Intel AS/400 RISC Server - Manual (Page 50)

more information may be used in the query plan costing phase than was available to the optimizer
previously. The optimizer may now use newly implemented database statistics to make more accurate
decisions when choosing the query access plan. Also, the enhanced optimizer may more often select
plans using hash tables and sorted partial result lists to hold partial query results during query processing,
rather than selecting access plans which build temporary indexes. With less reliance on temporary indexes
the SQE optimizer is able to select more efficient plans which save the overhead of building temporary
indexes and more fully take advantage of single-level store. The optimizer changes were designed to
create efficient query access plans for the enhanced database engine.
SQE Query Engine
The database engine is the part of the database implementation which executes the access plan produced
by the query optimizer. It accesses the data, processes it, and returns the SQL query results. The new
engine enhancements, the SQE database engine, employ state of the art object oriented implementation.
The SQE database engine was developed in tandem with the SQE optimization enhancements to allow for
an efficient design which is readily extendable. Efficient new algorithms for the data access methods are
used in query processing by the SQE engine.
The basic data access algorithms in SQE are designed to take full advantage of the System i single-level
store to give the fastest query response time. The algorithms reduce I/O wait time by making use of
available main memory and aggressively reading data from disk into memory. The goal of the data
read-ahead algorithms is that the data is in memory when it is needed. This is done through the use of
asynchronous I/Os. SQL queries which access large amounts of data may see a considerable
improvement in the query runtime. This may also result in higher peak disk utilization.
The effects of the SQE enhancements on SQL query performance will vary greatly depending on many
factors. Among these factors are hardware configuration (processor, memory size, DASD
configuration...), system value settings, file layout, indexes available, query options file QAQQINI
settings, and the SQL queries being run.
SQE Database Statistics
The third area of SQE enhancements is the collection and use of new database statistics. Efficient
processing of database queries depends primarily on a query optimizer that is able to make judicious
choices of access plans. The ability of an optimizer to make a good decision is critically influenced by the
availability of database statistics on tables referenced in queries. In the past such statistics were
automatically gathered during optimization time for columns of tables over which indexes exist. With
SQE statistics on columns without indexes can now be gathered and will be used during optimization.
Column statistics comprise histograms, frequent values list, and column cardinality.
With System i servers, the database statistics collection process is handled automatically, while on many
platforms statistics collection is a manual process that is the responsibility of the database administrator
. It
is rarely necessary for the statistics to be manually updated, even though it is possible to manage statistics
manually. The Statistics Manager determines on what columns statistics are needed, when the statistics
collection should be run and when the statistics need to be refreshed. Statistics are automatically
collected as low priority work in the background, so as to minimize the impact to other work on the
system. The manual collection of statistics is run with the normal user job priority.
The system automatically determines what columns to collect statistics on based on what queries have run
on the system. Therefore for queries which have slower than expected performance results, a check
IBM i 6.1 Performance Capabilities Reference - January/April/October 2008
©
Copyright IBM Corp. 2008 Chapter 4 - DB2 Performance
50