It should stop identifying ideal indexes and stop calculating access path
costs based on non existing indexes. This will definitely reduce
optimization cost.
SQE Optimization orders the existing indexes depending on the columns
specified in the join clauses, where conditions, group by clauses (and
sometimes in the ORDER BY) clauses.
Than those indexes are evaluated by interviewing the staticstics.
Optimization stops as soon as an index which is more "expensive" is found.
But depending on the join, where, group by and order by conditions, the
optimizer can estimate what the best index would be and this index advice is
stored within the SYSIXADV table.
Statistics collections and re-optimization (AQP) are separate jobs that are
running very deep within the operating system and do not affect the current
query use.
Even if a temporary index must be built (and the query is executed by the
SQE), it is only built once and can be used by all other SQL statement
executed by the SQE. Those temporary indexes remain existent until the last
access plan that uses this temporary index disappears from the SQE plan
cache (not later than the next IPL). If a temporary index is built by the
CQE it is created on the job level and gets deleted as soon as the job ends.
If the same query is executed by the CQE in different jobs, in each of these
jobs a temporary index must be built.
IMHO you sould check whether and how often FULL OPEN (complete optimization)
must be performed and how often PSEUDO OPENs are used.
Every time a FULL OPEN must be performed, the ODP (Open Data Path) must be
(re)created and cannot be reused.
The most time consuming part of the optimization process is NOT estimating
and advising any indexes but building the ODP (creating the necessary
temporary objects, linking them together and populating them with data).
SQL itself tries to keep the ODP open as long as possible, but there are
several factors that will delete ODPs (for example ending an Module with
Embedded SQL with CLOSQLCSR=*ENDMOD or running an embedded SQL program in
the *NEW activation group, when performing dynamic SQL the ODP could be
closed etc.)
If the ODP can be reused, only a PSEUDO OPEN will be performed, i.e. no
statement parsing, no index estimation, no statistics interview will be
performed.
The temporary objects already created for the reusable ODP can be (re)used,
so only the data within the temporary objects must be updated.
One goal in SQL performance optimization is reducing the FULL OPENS. Mostly
PSEUDO OPENS are x-times faster than FULL OPENs.
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 [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von
satya tiruveedhi
Gesendet: Tuesday, 01.9 2015 21:44
An: midrange-l@xxxxxxxxxxxx
Betreff: Re: Index advisor and system cross-reference file
@Andrew Lopez,
By turning off Index advisor, I am expecting optimizer to work with existing
indexes even if they are not ideal indexes. It should stop identifying ideal
indexes and stop calculating access path costs based on non existing
indexes. This will definitely reduce optimization cost.
Thanks...
Satya...
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
