Hi,
And of course, statistics don't help as much in completely ad hoc
situations,
because the SQL engine will have no statistics on queries that have never
been done.
Statistic information are collected for all SQL tables, physical files and
access path (keyed logical files and SQL indexes). In this way statistics
can help for ad hoc queries in the same way as they do for queries that
already have been executed.
Access plans (i.e. which index must used and which temporary objects must be
built to open the access path) are stored.
For SQL statements exectued by the SQE the access plans are stored in the
SQE Plan Cache and can be used system wide. (Contrary, access plans used by
the CQE must be built or validated for each job!) The next time the query
must be executed the existing access plan will be validated. Validating also
means consulting the statistics to check and confirm or replace the access
path used last time. An access plan must only be built by scratch the first
time the query is executed. Currently in Release V5R4 the SQE Plan Cache can
held up to 20,000 access plans and up to 3 access plans for each SQL
statement and takes 512 MB of disk space. Before V5R4 the SQE Plan Cache was
limited to 256 MB.
If the plan cache overflows the oldest, the longest not used access plans
will be deleted and replaced by the new ones. The SQL Plan Cache will be
cleared with IPL, that means after IPL all access plans must be built by
scratch, as long as they are not stored elsewhere, for example in the
program objects with embedded SQL or in SQL packages.
One thing I do worry about is exactly how much space is taken up to store
the information required to make the queries faster.
I'd love to see the statistics on what percentage of disk space is
required
to store the statistics for a file. If it's something on the order of a
tenth of a percent,
it's not a big deal. If it's 10% or more, it's a big deal!
Certainly not 10% of disk space. The space to store the statistics depends
on the number of access paths built over the physical files/tables and the
number of different key values. For each access path the different key
values and the number of rows that satisfy this values are stored, which is
much less space than an index or keyed logical file takes.
Statistics were introduced in release V5R2 with the SQE and most programmers
and administrators even know that statiscits are available and permanently
collected.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Joe Pluta
Gesendet: Saturday, December 29, 2007 17:36
An: 'Midrange Systems Technical Discussion'
Betreff: RE: members in a file
From: BirgittaHauser
What happens if you stop and start collection of SQL Statistics?
Statistics are used by the SQE optimizer to determine the optimal
index. The statistics contain information about how much rows in a
table meet the available key values in an index. For example if you
want to select all order from a specified client and around 80% of all
orders are from this client, the optimizer will favor a table scan
instead of using an index. If an other client will be selected an
index can be used.
(...snip...)
BTW the CQE optimizer can only use estimates. For example for an =
specified in a where condition 10% of the rows in a table are
expected/estimated.
And it is this collection of statistics that makes SQL so powerful. Without
it, a good programmer with knowledge of his database can still use ISAM
techniques to provide better performance than SQL because he will in effect
be able to make the decisions that the CQE cannot.
As statistics get better, SQL matches and then surpasses the low-level
programmer because its information is always correct while the programmer is
relying on his/her knowledge of the system, which may be out of date.
Similarly, though, if you stop the collection of statistics, the SQL
statistics will become stale and it will begin to make poorer decisions.
And of course, statistics don't help as much in completely ad hoc
situations, because the SQL engine will have no statistics on queries that
have never been done. But in general, SQL queries will just get better and
better.
One thing I do worry about is exactly how much space is taken up to store
the information required to make the queries faster. I'd love to see the
statistics on what percentage of disk space is required to store the
statistics for a file. If it's something on the order of a tenth of a
percent, it's not a big deal. If it's 10% or more, it's a big deal!
Joe
midrange.com
