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

SQL queries which continue to be routed to CQE in i5/OS V5R3 have the following attributes:
y
Tables with select/omit logicals over them
y
References to DDS logical files
y ALWCPYDTA(*NO) y LOB columns
y
DB2 Multisystem
y
Like/Substring predicates
y
NLSS/CCSID translation between columns
y
Sensitive cursor
i5/OS V5R3 SQE Performance Enhancements
Many enhancements were made in i5/OS V5R3 to enable faster query runtime and use less system
resource. Highlights of these enhancements include the following:
y
New optimization techniques including Lookahead Predication Generation and Constraint Awareness
y
Sharing of temporary result sets across jobs
y
Reduction in size of temporary result sets
y More efficient I/O for temporary result sets
y Ability to do some aggregates with EVI symbol table access only
y
Reduction in memory used during optimization
y
Reduction in DB structure memory usage
y
More efficient statistics generation during optimization
y
Greater accuracy of statistics usage for optimization plan generation
The DB2 performance enhancements in i5/OS V5R3 substantially reduced the runtime of many queries.
Performance improvements vary substantially due to many factors -- file size and layout, indexes and
statistics available -- making generalization of performance expectations for a given query difficult.
However, longer running queries which are newly routed to SQE in i5/OS V5R3, in general, have a
greater likelihood of significant performance benefit.
For the short running queries, those that run less than 2 seconds, performance improvements are nominal.
For subsecond queries there is little to no improvement for most queries. As the runtime increases, the
reduction in runtime and CPU time become more substantial. In general, for short running queries there is
less opportunity for improving performance. Also, the first execution of all the queries in these figures
was measured so that a database open and full optimization were required. Database open and full
optimization overhead may be higher with SQE, as it evaluates more information and examines more
potential query implementation plans. As this overhead is much more expensive relative to actual query
implementation for short running queries, performance benefits from SQE for the short running queries
are minimized. However, in OLTP environments the plan caching and open data path (ODP) reuse design
minimizes the number of opens and full optimizations needed. A very small percentage of queries in
typical customer OLTP workloads go through full open and optimization.
The performance benefits are substantial for many of the medium to long running queries newly routed to
SQE in i5/OS V5R3. Typically, the longer the runtime, the more potential for improvements. This is due
to the optimizer constructing a more efficient access plan and the faster execution of the access plan with
the SQE query engine. Many of the queries with runtimes greater than 2 seconds, especially those with
runtimes greater than 10 seconds, reduced their runtime by a factor of 2 or more. Queries which run
longer than 200 seconds were typically improved from 15% to over 100 times.
IBM i 6.1 Performance Capabilities Reference - January/April/October 2008
©
Copyright IBM Corp. 2008 Chapter 4 - DB2 Performance
47