On 11-May-2016 13:06 -0500, Charles Wilt wrote:
On 11-May-2016 12:56 -0500, dlclark wrote:
We were having performance issues and we hired IBM to, among other
things, take a look at our SQL usage and to advise us the
correct/best way to use SQL. One of the things that came out in
the process was that our environment has nearly 600 "local" data
libraries with the same tables in every library plus we have a
"shared" data library. Library lists will have one "local" data
library and the one "shared" data library in them. "Local" users
don't switch library lists for their interactive sessions but some
of the jobs that run on their behalf have to switch library lists
to process all of the files. The resulting IBM recommendation to
us was to use static SQL for the "shared" data library but to use
dynamic SQL for the "local" data libraries. We also contracted for
SQL training and the trainer agreed regarding our situation with
the same table names in multiple data libraries.
So did switching to dynamic SQL help performance?
Seems a bit strange to me...
At worst, I'd expect a re-plan for a static statement to perform no
worse than building a plan for a dynamic statement.
The issue is how many times the query must [be reoptimized and thus]
have the access plan rebuilt and stored again. In the described
scenario, the use of the one program for the static SQL written against
one qualified table-reference but performed against table-references
with changing library qualifiers can be expensive in terms of the
resources utilized.
At best, I'd think a re-plan for a static statement to be better
than building one for a dynamic statement.
Contention and authority checks [and I hope\expect that the authority
violations were long since eliminated] to store the plan for static are
likely more costly than for dynamic; space management costs are probably
not consistent nor predictable for each. But accounting solely for the
AccPln rebuilds, the costs are probably nearly the same betwixt. Of
course the parsing and syntax checking is the conspicuous overhead for
dynamic, and then searching the plan cache for a matching plan, are all
beyond what is required for static -- but those actions are not
officially part of the plan-rebuild.
Perhaps Chuck or one of the others on here with more knowledge of
the internals will chime in.
A switch to dynamic has been helpful to some, for performance
reasons, in scenarios similar to that described. Note: I see in
followup replies, that the issue dlclark had resolved by switching to
dynamic, was a functional issue; i.e. the switch was made, to circumvent
a defect, not to improve performance. Nonetheless, I offer:
For static: Contention on a single resource is one potential issue.
Another issue is that every authorized winner in contention then gets to
replace a stale plan; conspicuously, replacement directly, if there is
no contention. Then every time the library name for a resource changes
[i.e. the table-reference changes], the plan is rebuilt, and either the
authorized invoker gets to replace the stale plan or the unauthorized
invoker merely discards the refreshed plan and therefore leaves a stale
plan; stale for that invocation, and for all but one invocation naming
just that one of the 600 libraries.
So let's presume that there is no contention, whereby the program is
run successively\serially with each run against a different library of
the 600; the plan is rewritten 600 times; i.e. the new plan as a rebuild
of the former and then physically written to [/disk/ in] the associated
space of the program. Each time that scenario repeats, that is
effectively a rebuild+rewrite->run repeated for every invocation of the
query\program; i.e. 600 more rebuild+rewrites. Conspicuously, the
benefits of static appear debased in such a [contrived] scenario.
For dynamic: The contention is the plan cache, only on the first run
of a query. Then on secondary runs, only the specific plan in the cache
can be in contention, but only if\when the plan must be rebuilt and thus
the refreshed plan rewritten.
So let's presume that there is no contention, whereby the program is
run successively\serially with each run against a different library of
the 600; a plan is built anew and stored for each run, physically
written to /disk/ in the plan cache, leaving 600 near-identical copies,
with just the library name of the table-reference(s) changing. Each
time that scenario repeats, that is effectively a read-only->run
repeated for every invocation of the query in the program; i.e. no more
rebuilds, nor more rewrites [none at least, for the library-name
changes]. Therefore, the more times these queries run, the more
tangible are the savings in CPU and disk writes.
Note: An alternate /solution/ for the static, mimicking the benefits
of a change to dynamic, yet while retaining the benefits of static, is
to create the 600 variants of the program. In doing so, each has its
own plan for the specific library name referenced\resolved in the static
SQL, rather than trying to share just one copy of the plan that must be
rebuilt each time the program runs against the file in a library other
than the one library for which the plan was most recently [re]created.
midrange.com
